How to calculate uncompressed Db2 for z/OS table sizes for Db2 Analytics Accelerator

Steps

Db2 for z/OS compression overview

Db2 for z/OS provides the option to compress a table during table creation or later. Compression is enabled with the COMPRESS YES parameter on the table space level, during CREATE TABLESPACE or ALTER TABLESPACE statements.

CREATE TABLESPACE tablespace_name … COMPRESS YES …

ALTER TABLESPACE tablespace_name … COMPRESS YES …

Db2 catalog information of interest:

In general, it is recommended that you schedule REORG, RUNSTATS, and STOSPACE runs to ensure that compression statistics on the table spaces and in the Db2 for z/OS catalog are current.

There is a common misunderstanding regarding the calculation. For instance, the SPACE column in the SYSIBM.SYSTABLEPART table shows the number of kilobytes of DASD storage allocated to the table space partition, as determined by the last execution of the STOSPACE utility or RUNSTATS utility. However, this may be misleading for a calculation of the uncompressed table size. Firstly, this is the allocated space and not the real data size in the table space; secondly, if it is a compressed table space, this is the value of the compressed data.

Calculation setup

The following example provides step-by-step information about how to calculate the uncompressed data:

Figure 1: Simple database / table space / table setup

Figure 2: CUTB is loaded with 4607 records, and the VSAM cluster looks as follows:

Figure 3: The CUDB.CUTS table space uses 45 tracks on the DASD, and this is the value you find in the Db2 catalog. 45 Tracks is approximately 2,160 KB. The PAGESAVE ratio is 82%.

Calculation steps

Step 1: The real ‘Used space’ value, in bytes, is stored in the HI-U-RBA column of the VSAM cluster, which is a highly used relative byte address – the actual space occupied by the VSAM data set.

The HI-U-RBA value is made visible through the LISTCAT TSO command:

<LISTC ENT(/) ALL>

It is used as shown below:

In the command output, you view the HI-U-RBA value:

Step 2: Find the PAGESAVE value in the SYSIBM.SYTABLEPART table for the table space. As shown in figure 3 above, this value can easily be found with a Db2 catalog query.

Step 3: The formula for the actual uncompressed size of the table space is:

(HI-U-RBA / (100 - PAGESAVE)) * 100

In the above example, the HI-U-RBA value is 118,784 bytes, which is the compressed and the used space for the table space.

Then the values are applied to the formula as follows:

(118784 / (100 – 82)) x 100 = 659,911 bytes, which is the uncompressed size of the table.

Therefore, the size of the compressed table is 118,784 bytes, and the uncompressed size of that object is 659,911 bytes.

Step 4:

• Apply the formula in step 3 to the table spaces that are defined with COMPRESS YES. Then calculate the total uncompressed table size for compressed tables.
• Calculate the sum of all HI-U-RBA values of all table spaces that are defined with COMPRESS NO, which gives you the total uncompressed table size for uncompressed tables.
• The total of both calculations above gives you the total uncompressed table size in Db2 for z/OS.