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.
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.
Is 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
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
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.