DB2 Backup – Check if Backup Image Contains Tablespace Not Using Automatic Storage

db2db2-luw

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

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

Best Answer

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:

NONAUTOTS
tbspInImage: T
ID: 7
flags: 0x101

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

SYSCATSPACE
tbspInImage: T
ID: 0
flags: 0x3102

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

With reference to IBM Support

Related Question