How To
Summary
Calculating the uncompressed table size for Db2 for z/OS is sometimes required for Db2 Analytics Accelerator sizing, capacity planning of Db2 for z/OS infrastructure, and planning the future growth of the IBM Z platform. This article provides step-by-step instructions to calculate the uncompressed table size.
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:
Catalog Table | Column | Description |
SYSTABLEPART | COMPRESS | “Y” = compression used, “blank” = compression not used; can be at partition level |
SYSTABLEPART | PAGESAVE | % of pages saved 0 = No savings |
SYSTABLEPART | AVGROWLEN | Average row length with or without compression |
SYSTABLES | PCTROWCOMP | % of compressed rows within total number of active rows |
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.
Document Location
Worldwide
Was this topic helpful?
Document Information
Modified date:
30 April 2020
UID
ibm16203324