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.

              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.

  • 1
    Just a note to anyone looking at this, this is for DB2 LUW and specifically V 9.7 FP 4 and LUW V 10.1 FP 2. (I know because I work with Peter.) :) Sep 26 '13 at 16:27

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.

  • What if you are on an LPAR so your memory and CPU are abstracted? How would you know? Sep 26 '13 at 20:46
  • 1
    If you overcommit CPU and especially RAM to a DB2 LPAR, you are already in big trouble, so the restore performance should be the least of your worries. If you don't, there's no difference.
    – mustaccio
    Sep 26 '13 at 20:53
  • 1
    Thanks for your detailed answer. We decided to follow your suggestions, since we are not fighting with very big databases anyway. Sep 27 '13 at 13:57

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.