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.

  • 1
    I assume this DB2 LUW. There was a related (but not the same) question with some background in the answer stackoverflow.com/questions/28586896/… Would the value of SQLUDMSTABLESPACEDATA (returned from "-p" option of db2ckbkp) help to estimate the size? – data_henrik Aug 25 '15 at 12:20
  • I'm not sure du -sk /db2/data/db2inst1/NODE0000/DBTEST return 32,514,488 and the value I get from -p is 1,765,436. – Nir Aug 25 '15 at 13:16
  • -p returns the number of pages and page size can be anything from 4k to 32k. If you know the "typical" page size you could estimate the factor. Databases with BLU Acceleration have most data in 32k pages, most OLTP databases have 8k pages these days. – data_henrik Aug 25 '15 at 13:43
  • Some tablespaces are in 8k and some in 32k, and I can't know how much is each. So I guess this option doesn't help me. – Nir Aug 25 '15 at 14:13

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:

              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.

| improve this answer | |

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.