I want to check a backup DB2 image if it contain any tablespace using non-automatic storage.

I had tried to use db2ckbkp commands. But it seems it cannot tell me if any tablespace using non-automatic storage.

db2ckbkp -Tablespace NEWDB.0.DB2_01.DBPART000.20170317153802.001

                  tbspInImage: T

                           ID: 7
                        flags: 0x101
                       flags2: 0x0
                  extent_size: 32
                prefetch_size: 16
                      version: 4
                      flavour: 9
                        state: 0x0
          statechangeobjectid: 0
            statechangepoolid: 0
                      LifeLSN: 0000000000062B2C
               LoadPendingLSN: 0000000000000000
              LoadRecoveryLSN: 0000000000000000
                     BeginLSN: 0000000000000000
                       EndLFS: 0
                   StordefLSN: 0000000000000000
              Full Backup LSN: 0000000000000000
              Last Backup LSN: 0000000000000000
             Full Backup Time:  00000000 = "19700101080000"
             Last Backup Time:  00000000 = "19700101080000"
                   TotalPages: 0
                 UseablePages: 0
                  reorgPoolID: 0
                   reorgObjID: 0
               poolReorgCount: 0
              # of containers: 1
                current_group: 0
                    cont_csum: 3582768349
          current_map_entries: 0
                    page_size: 4096
                     map_csum: 0
         tsp rfwd encountered: 16
             storage group ID: -1
      source storage group ID: -1
                     data tag: 0
    recovery storage group ID: -1
   recovery storage group LSN: 0000000000000000

                Container CB
                                 Type: 0x0
                           TotalPages: 0
                          UsablePages: 0
                   # of OS rsvd bytes: 0
                        Page 0 offset: 0
                           Tag offset: 0
                        Extent offset: 0
                                 Name: C:\Users\anson.wong\Desktop\ABC\nonAutoTS

I found a command from DB2 RESTORE which could generate a script file containing information about tablespace using non-automatic storage. But I prefer handling it in memory without the need to write a file.

db2 RESTORE DATABASE NEWDB FROM "C:/Users/Desktop/ABC" redirect GENERATE SCRIPT generatedScript.txt

Tablespace name                          = NONAUTOTS
Tablespace ID                            = 7
Tablespace Type                          = System managed space   
Using automatic storage                  = No      

Thank you.


You can look at the line:

         storage group ID: -1

If the storage group ID is -1, then the tablespace is not using automatic storage. Don't consider source storage group ID and recovery storage group ID.

Check (this works on Linux/UNIX, but not Windows - sorry):

db2ckbkp -T SAMPLE.0.db2inst1.DBPART000.20170322021205.001 | \
   grep '  storage group ID: -1' > /dev/null && \
   echo "Found tablespace(s) not using automatic storage"

This applies to DB2 versions that have storage groups – DB2 10.1 and newer.

For DB2 9.7 (this will work with DB2 10.1 and later, too), you could extract the storage path(s) for the database and see if any container names do not start with the storage path(s).

You could also consider the container name; automatic storage tablespaces will have a name that looks like:



  • <storage_path> is a database's storage path (see db2ckbkp -S)
  • <instance> is the db2 instance (like db2inst1)
  • <database> is the name of the database (in CAPS)
  • n is a digit
  • xxx is an extension that may be CAT, TMP, LRG or UTM (possibly others).
  • Thank you for you reply. It would be great if storage group method applied to all db2 versions. However, I have to due with different db2 versions. The recognizing path method seems more complicated than bit wise AND operation on flag. – Pika Mar 24 '17 at 2:17

Finally, I could judge if a backup image contains any tablespace not using automatic storage from db2ckbkp command by using a special number 0x1000 with Flags value

db2ckbkp -Tablespace NEWDB.0.DB2_01.DBPART000.20170317153802.001

// Here are some snippets:

tbspInImage: T
ID: 7
flags: 0x101

Bitwise AND operation
AND 0x1000
// The zero means that this tablespace is non-automatic storage tablespace.

tbspInImage: T
ID: 0
flags: 0x3102

Bitwise AND operation
AND 0x1000
// The non-zero value means that this tablespace is automatic storage tablespace.

With reference to IBM Support

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.