db2ckbkp - How to determine what tablespaces are temp-tablespace

Question

I am writing a script to restore databases from backup. The script should need as little input as possible to fulfill this task (in an efficient way). The restore command takes the parallelism parameter. The idea is to set this parameter to the number of tablespaces that are not temporary tablespaces.

I found the db2ckbkp command which will not only verifies the backup file, but also outputs lots of (useful) information.

Currently I was planning to run it with -t to get the tablespace information. I just have troubles to interpret the information printed. following the output that is printed for one of the tablespaces.

        USERSPACE1
              tbspInImage: T

                       ID: 2
                    flags: 3122
                   flags2: 400
              extent_size: 32
            prefetch_size: 32
                  version: 9
                  flavour: 6
                    state: 0
      statechangeobjectid: 0
        statechangepoolid: 0
                  LifeLSN: 00000000000005C2
           LoadPendingLSN: 0000000000000000
          LoadRecoveryLSN: 0000000000000000
                 BeginLSN: 0000000000000000
                   EndLSN: 0000000000000000
               StordefLSN: 0000000000000000
          Full Backup LSN: 0000000000000000
          Last Backup LSN: 0000000000000000
         Full Backup Time:  00000000 = "19691231180000"
         Last Backup Time:  00000000 = "19691231180000"
               TotalPages: 8192
             UseablePages: 8160
              reorgPoolID: 0
               reorgObjID: 0
           poolReorgCount: 0
          # of containers: 1
            current_group: 0
                cont_csum: 2004299914
      current_map_entries: 1
                page_size: 4096
                 map_csum: 4294967294
     tsp rfwd encountered: 16

            Container CB
                             Type: 6
                       TotalPages: 8192
                      UsablePages: 8160
               # of OS rsvd bytes: 512
                    Page 0 offset: 131072
                       Tag offset: 512
                    Extent offset: 0
                             Name: /data/devinsth/NODE0000/.../T0000002/C0000000.LRG

To determine whether it is an temp space or not, the flavour is not suitable, since it is 6 for all of the tablespaces. However the container type seems to be different for temp spaces. Type is 0 for temporary tablespaces and 6 for all other tablespaces.

When I use the -a parameter, I get another piece of information.

    00003A91: SYSCATSPACE
            ID:          0  Type:                 2 Datatype:           0
            Size:    29932  NumContainers:        1 tbspInImage:        1

    00003AC5: TEMPSPACE1
            ID:          1  Type:                 1 Datatype:          16
            Size:        1  NumContainers:        1 tbspInImage:        1

    00003AF9: USERSPACE1
            ID:          2  Type:                 2 Datatype:          32
            Size:       96  NumContainers:        1 tbspInImage:        1

    00003B2D: TS_DAT_SPF_4K
            ID:          3  Type:                 2 Datatype:          32
            Size:     7776  NumContainers:        1 tbspInImage:        1

    00003B61: TS_IND_SPF_4K
            ID:          4  Type:                 2 Datatype:          32
            Size:       96  NumContainers:        1 tbspInImage:        1

    00003B95: TS_LOB_SPF_4K
            ID:          5  Type:                 2 Datatype:          32
            Size:       96  NumContainers:        1 tbspInImage:        1

    00003BC9: TS_TMP_4K
            ID:          6  Type:                 1 Datatype:          16
            Size:        1  NumContainers:        1 tbspInImage:        1

    00003BFD: TS_USR_TMP_BAT_4K
            ID:          7  Type:                 1 Datatype:          64
            Size:        1  NumContainers:        1 tbspInImage:        1

Can I use the type from this table? It seems to be 1 for temporary tablespaces and 2 for all other tablespaces. I tried to find documentation on the internet that explained the output of db2ckbkp but wasn't successful. Any help in this matter is appreciated.

Answer 1

The tablespace content type (Datatype in your second example) is defined in sqlutil.h:

/* table space contents                                                       */
#define SQLB_TBS_ANY 0x0               /* All permanent data. Regular table   */
                                       /* space.                              */
#define SQLB_TBS_LARGE 0x20            /* All permanent data. Large table     */
                                       /* space.                              */
#define SQLB_TBS_LONG SQLB_TBS_LARGE   /* Kept for compatibility.             */
#define SQLB_TBS_SYSTMP 0x10           /* system temp data                    */
#define SQLB_TBS_USRTMP 0x40           /* user temp data                      */

So those with Datatype 16 (0x10) would be the system temporary tablespaces I should think.

However, I don't think using the number of non-system temporary tablespaces to determine restore parallelism makes much sense. I would suggest leaving it for DB2 to decide. If you still want to outsmart DB2, you might be better off using the number of CPU cores, I/O parallelism, or the number of containers as the basis of your adjustments.