DB2 Backup Size – How to Determine Database Size from Backup

db2

I want to add validation for free space before restore a db2 database.
I have the *.001 backup file (compressed).

How do I check the estimate db size of that backup?

I can't estimate because the compression ratio is very diverse. db2ckbkp doesn't have that data.

Best Answer

It is relatively complicated to do, but it's possible.

If your database has only DMS tablespaces that contain table data, you can use db2ckbkp -T <backup_file> alone to get this information:

   SYSCATSPACE
              tbspInImage: T

                       ID: 0
                    flags: 7102
                   flags2: 400
              extent_size: 4
            prefetch_size: 8
                  version: 5294
                  flavour: 6
                    state: 0
      statechangeobjectid: 0
        statechangepoolid: 0
                  LifeLSN: 000000000000000C
           LoadPendingLSN: 0000000000000000
          LoadRecoveryLSN: 0000000000000000
                 BeginLSN: 0000000000000000
                   EndLSN: 0000000000000000
               StordefLSN: 0000000000000000
          Full Backup LSN: 0000000FE0EAC00F
          Last Backup LSN: 0000000FE0EAC00F
         Full Backup Time:  519BCFCE = "20130521124934"
         Last Backup Time:  519BCFCE = "20130521124934"
               TotalPages: 87824
             UseablePages: 87816
              reorgPoolID: 0
               reorgObjID: 0
           poolReorgCount: 0
          # of containers: 2
            current_group: 0
                cont_csum: 1311788850
      current_map_entries: 1
                page_size: 4096
                 map_csum: 4294967292
     tsp rfwd encountered: 16

            Container CB
                             Type: 6
                       TotalPages: 43912
                      UsablePages: 43908
               # of OS rsvd bytes: 512
                    Page 0 offset: 16384
                       Tag offset: 512
                    Extent offset: 0
                             Name: /db2/data1/db2inst1/NODE0000/SAMPLE/T0000000/C0000000.CAT


            Container CB
                             Type: 6
                       TotalPages: 43912
                      UsablePages: 43908
               # of OS rsvd bytes: 512
                    Page 0 offset: 16384
                       Tag offset: 512
                    Extent offset: 0
                             Name: /db2/data2/db2inst1/NODE0000/SAMPLE/T0000000/C0000001.CAT

Using TotalPages and page_size from the main section of the output, you can calculate the total space. (You can ignore the Container CB sections that give details for each container in the tablespace).

If you have SMS tablespaces that store table data (i.e. not just system- or user-temporary tablespaces), you'll see tablespaces in this output that show TotalPages: 0.

To calculate the amount of data used by these SMS tablespaces, you will need to dump out all of the information from the backup image using db2ckbkp -a. You will get lines like this:

BufAddr  PoolID Token Type     Offset   FileSize ObjectSize   OrigSize Object Name
-------- ------ ----- ---- ---------- ---------- ---------- ---------- -----------
00000000:     0    10   16       1042     167680     107757     524288 "Tablespace: 0, Table: SQL00010"

OrigSize shows the amount of uncompressed data in this buffer. The line above shows data from an SMS tablespace; DMS tablespaces will also show lines like this, but the ObjectType will show DMS.TABLESPACE.DATA.

So, if you look for lines where ObjectType starts with "Tablespace:, you can add up the OrigSize of these lines, and find the additional storage space that all SMS tablespaces will use.

Related Question