DB2 LUW Fix Packs and db2look’s -createdb and -printdbcfg

The ability to extract the ‘command that was used to create a database’ comes handy during database migration time. The option in question is: -createdb in the command db2look. This is a blessing for that DBA (I am in that boat) who does not want to remember all the options in the ‘CREATE DATABASE .. ‘ command that were originally used to create the database.

Similarly, the option -printdbcfg in db2look generates ‘UPDATE DATABASE CFG…’ commands for the database configuration parameters. This gives the ability to not only apply those values to another database but to save parameters as part of a recovery plan. Both these enhancements were originally announced in Fix Pack 4 for DB2 10.1. Here is the link for IBM Knowledge Center that published these enhancements in db2look.

Scenario

I wanted to know if a database was using automatic storage or not. I tried using db2look’s -createdb command but got an error.

$db2look -d testdb -createdb
The option "-createdb" is not supported by db2look.

From ‘db2level’, the version of DB2 is: DB2 10.5 Fix Pack 3.

$ db2level
DB21085I  This instance or install (instance name, where applicable:
"db2inst1") uses "64" bits and DB2 code release "SQL10053" with level
identifier "0604010E".
Informational tokens are "DB2 v10.5.0.3", "s140203", "IP23544", 
and Fix Pack "3".
Product is installed at "/opt/IBM/db2/V10.5".

I tried if -printdbcfg would work although it has nothing to do with automatic storage.

$db2look -d testdb -printdbcfg

The option "-printdbcfg" is not supported by db2look.

I expected these options to work on a DB2 10.5 Fix Pack 3 database. After all, these options are supported starting in DB2 10.1 Fix Pack 4. My initial thought was that this could be a bug in DB2 10.5 Fix Pack 3. I was incorrect.

Fix Pack Versions Vs. Timeline

It is easy to be mislead by the DB2’s Fix Pack naming convention into thinking that the Fix Packs for higher versions of DB2 would have all the features of Fix Packs in the lower versions. Does DB2 10.5 Fix Pack 3 have all the features that DB2 10.1 Fix Pack 4 has? From the naming convention (DB2 10.5 is a later version than DB2 10.1), it appears that way.. Isn’t it? This might not be necessarily true!

From the release dates of Fix Packs, we notice that DB2 10.1.4 (DB2 10.1 Fix Pack 4) was released after DB2 10.5.3 (DB2 10.5 Fix Pack 3). Certain enhancements like db2look’s -createdb and -printdbcfg are part of DB2 10.1.4 but not part of DB2 10.5.3. Does this mean all features of DB2 10.5.3 are available in DB2 10.1.4? Not necessarily!! Here is an example: DB2’s BLU acceleration is available starting in DB2 10.5. This feature is not available in DB2 10.1 and any of it’s Fix Packs.

 DB2 Fix Pack timeline
So, when it comes to comparing  features in Fix Packs across DB2 Versions, there is no clear way to do this. However, when comparing Fix Packs within the same DB2 Version level, Fix Packs are cumulative. As DB2 10.5.4 and DB2 10.5.3 belong to the same DB2 Base Version, DB2 10.5.4 would have all features of (and more than) DB2 10.5.3.

Is Database Using Automatic Storage?

Going back to our question.. Is my database using AUTOMATIC STORAGE?

Prior to DB2 10.5.4

Prior to DB2 10.5.4 and DB2 10.1.4, one way to find out if the database is using automatic storage or not is by actually extracting DDL for all tablespaces and looking (grep) for “AUTOMATIC STORAGE”. Note that grep’s ‘-p’ option is not available in LINUX. It is only available on AIX.

Alternatively, you could use SYSTEM views like SYSIBMADM.MON_TBSP_UTILIZATION and go after the column TBSP_USING_AUTO_STORAGE. There are lot of ways to do this.

$db2look -d testdb -l | grep -ip "AUTOMATIC STORAGE"
-- No userid was specified, db2look tries to use Environment variable USER
-- USER is: DB2INST1
CREATE LARGE TABLESPACE "SYSTOOLSPACE" IN DATABASE PARTITION GROUP IBMCATGROUP
         PAGESIZE 32768 MANAGED BY AUTOMATIC STORAGE
         USING STOGROUP "IBMSTOGROUP"
         AUTORESIZE YES
         INITIALSIZE 32 M
         MAXSIZE NONE
         EXTENTSIZE 4
         PREFETCHSIZE AUTOMATIC
         BUFFERPOOL "IBMDEFAULTBP"
         DATA TAG INHERIT
         OVERHEAD INHERIT
         TRANSFERRATE INHERIT
         NO FILE SYSTEM CACHING
         DROPPED TABLE RECOVERY ON;

CREATE LARGE TABLESPACE "TBSP1" IN DATABASE PARTITION GROUP IBMDEFAULTGROUP
         PAGESIZE 32768 MANAGED BY AUTOMATIC STORAGE
         USING STOGROUP "IBMSTOGROUP"
         AUTORESIZE YES
         INITIALSIZE 32 M
         MAXSIZE NONE
         EXTENTSIZE 32
         PREFETCHSIZE AUTOMATIC
         BUFFERPOOL "IBMDEFAULTBP"
         DATA TAG INHERIT
         OVERHEAD INHERIT
         TRANSFERRATE INHERIT
         NO FILE SYSTEM CACHING
         DROPPED TABLE RECOVERY ON;

Alternately, you could take a ‘snapshot  for tablespaces’. Look for ‘Auto-resize enabled' in the snapshot output (Highlighted below) to find out if the tablespace uses AUTOMATIC storage or not.

$db2 "get snapshot for tablespaces on testdb" | grep -ip "Auto-resize enabled"

...............
.......
Tablespace name                            = PAVAN
  Tablespace ID                            = 6
  Tablespace Type                          = Database managed space
  Tablespace Content Type                  = All permanent data. Large table space.
  Tablespace Page size (bytes)             = 32768
  Tablespace Extent size (pages)           = 32
  Automatic Prefetch size enabled          = Yes
  Buffer pool ID currently in use          = 1
  Buffer pool ID next startup              = 1
  Using automatic storage                  = Yes
  Auto-resize enabled                      = Yes
  File system caching                      = No
  Tablespace State                         = 0x'00000000'
   Detailed explanation:
     Normal
..............
.....

An easier option is to look for the column ‘TBSP_USING_AUTO_STORAGE’ in ‘MON_GET_TABLESPACE’. If the column ‘TBSP_USING_AUTO_STORAGE’ has the value 1, then the tablespace is using AUTOMATIC storage.

db2 "SELECT varchar(tbsp_name, 30) as tbsp_name, tbsp_type, 
TBSP_USING_AUTO_STORAGE 
FROM TABLE(MON_GET_TABLESPACE('',-2)) AS t"

TBSP_NAME                      TBSP_TYPE  TBSP_USING_AUTO_STORAGE
------------------------------ ---------- -----------------------
SYSCATSPACE                    SMS                              0
TEMPSPACE1                     SMS                              0
USERSPACE1                     DMS                              1
SYSTOOLSPACE                   DMS                              1
PAVAN                          DMS                              1

  5 record(s) selected.

You would see that the tablespace type is DMS even though the above listed tablespaces use AUTOMATIC storage. This is because Automatic Storage Tablespace is a special case of DMS (Database Managed Storage).

DB2 10.5.4 and DB2 10.5.1

From DB2 10.5.4’s release notes and DB2 10.1.4’s release notes, db2look’s -createdb and -printdbcfg are both available in these Fix Packs.

Here is how I used the option -createdb in DB2 10.5.4 to check if the database is using AUTOMATIC STORAGE or not. Look at the CREATE DATABASE command.. It has AUTOMATIC STORAGE YES in it.

$db2look -d testdb -createdb
-- No userid was specified, db2look tries to use Environment variable USER
-- USER is: DB2INST1
-- This CLP file was created using DB2LOOK Version "10.5"
-- Timestamp: Sat Apr 18 23:31:14 2015
-- Database Name: testdb
-- Database Manager Version: DB2/AIX64 Version 10.5.4
-- Database Codepage: 1208
-- Database Collating Sequence is: SYSTEM_819
-- Alternate collating sequence(alt_collate): null
-- varchar2 compatibility(varchar2_compat): OFF
--------------------------------------------------------
CREATE DATABASE testdb
 AUTOMATIC STORAGE YES
 ON '/db2inst1/testdb/data1',
 '/db2inst1/testdb/data2',
 '/db2inst1/testdb/data3'
 DBPATH ON '/db2inst1/testdb/'
 USING CODESET UTF-8 TERRITORY US
 COLLATE USING SYSTEM_819_US
 PAGESIZE 32768
 DFT_EXTENT_SZ 32
 CATALOG TABLESPACE MANAGED BY SYSTEM
 USING ('/db2inst1/testdb/cat_tbsp_data')
 EXTENTSIZE 32
 OVERHEAD 6.725000
 TRANSFERRATE 0.320000
 NO FILE SYSTEM CACHING
 TEMPORARY TABLESPACE MANAGED BY SYSTEM
 USING ('/db2inst1/testdb/tmp_tbsp_data')
 EXTENTSIZE 32
 OVERHEAD 6.725000
 TRANSFERRATE 0.320000
 FILE SYSTEM CACHING
 USER TABLESPACE MANAGED BY AUTOMATIC STORAGE
 EXTENTSIZE 32
 NO FILE SYSTEM CACHING
 AUTORESIZE YES
 INITIALSIZE 32 M
 MAXSIZE NONE
;
CONNECT TO testdb;
COMMIT WORK;
CONNECT RESET;
TERMINATE;

URLs for release notes of Fix Packs mentioned in this blog post are below. There is notes on features available.

10.5.3 – http://www-01.ibm.com/support/docview.wss?uid=swg24036705
10.1.4 – http://www-01.ibm.com/support/docview.wss?uid=swg24037466
10.5.4 – http://www-01.ibm.com/support/docview.wss?uid=swg24038261

Conclusion

We have looked at two ways of knowing if a DB2 LUW database is using Automatic Storage or not. One with the latest options in ‘db2look’ and one without these options.

A key take away is that there is no direct way to compare DB2’s features in Fix Packs across two different DB2 Version levels. It is a known that Fix Packs in the same DB2 Version level are cumulative. It is a good practice to read through the DB2 Fix Pack documentation site for DB2 Fix Packs and their features.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s