Modeling your production system statistics in a test subsystem

You can improve access path testing by updating the catalog statistics on your test system to be the same as your production system.

About this task

When you bind applications on the test system that uses production statistics, access paths are more likely to match for queries bound in each environment. However, corresponding access paths from test and production environments might differ for the following possible reasons:

The resources available in the test subsystem, such as the type and number of processors, or RID pool, sort pool, and buffer pool settings do not match those of the production environment. However, you can create profiles and set subsystem parameters on your test subsystem to better model your production environment.
Data in SYSIBM.SYSCOLDIST is mismatched. (This mismatch occurs only if some of the previously mentioned steps mentioned are not followed exactly).
The service levels are different.
The values of optimization subsystem parameters, such as STARJOIN, NPGTHRSH, and PARAMDEG (MAX DEGREE on installation panel DSNTIP8) are different. You can prevent this problem by setting these parameters in your test subsystem to match your production environment.
The use of techniques such as optimization hints and volatile tables are different.

If your production system is accessible from your test system, you can use Db2 PM EXPLAIN on your test system to request EXPLAIN information from your production system. This request can reduce the need to simulate a production system by updating the catalog.

You can also use Db2 Administration Foundation or Db2 Query Workload Tuner for z/OS® to compare access plan graphs and capture information about your Db2 environment.

Important: Consider the following information when using SPUFI:

If you use SPUFI to execute the following example SQL statements, you might need to increase the default maximum character column width to avoid truncation.
Asterisks (*) are used in the examples to avoid having the semicolon interpreted as the end of the SQL statement. Edit the result to change the asterisk to a semicolon.

Procedure

To model production statistics in your test environment:

Run RUNSTATS on your production tables to get current statistics for access path selection.

Retrieve the production statistics and use them to build SQL statements to update the catalog of the test system.

You can use queries similar to the following queries to build those statements. To successfully model your production system, the table definitions must be the same on both the test and production systems. For example, they must have the same creator, name, indexes, number of partitions, and so on.

Begin program-specific programming interface information.

Use the following statements to update SYSTABLESPACE, SYSTABLES, SYSINDEXES, and SYSCOLUMNS:

SELECT DISTINCT 'UPDATE SYSIBM.SYSTABLESPACE SET NACTIVEF=' 
CONCAT STRIP(CHAR(NACTIVEF))
CONCAT',NACTIVE='CONCAT STRIP(CHAR(NACTIVE))
CONCAT ' WHERE NAME=''' CONCAT TS.NAME
CONCAT ''' AND DBNAME ='''CONCAT TS.DBNAME CONCAT'''*'          
FROM SYSIBM.SYSTABLESPACE TS, SYSIBM.SYSTABLES TBL
WHERE TS.NAME = TSNAME
      AND TBL.CREATOR IN (table creator_list)
      AND TBL.NAME IN (table_list)
      AND (NACTIVEF >=0 OR NACTIVE  >=0);

SELECT 'UPDATE SYSIBM.SYSTABLES SET CARDF='  
CONCAT  STRIP(CHAR(CARDF))
CONCAT',NPAGES='CONCAT STRIP(CHAR(NPAGES))
CONCAT',NPAGESF='CONCAT STRIP(CHAR(NPAGESF))
CONCAT',PCTROWCOMP='CONCAT STRIP(CHAR(PCTROWCOMP))
CONCAT ' WHERE NAME='''CONCAT NAME
CONCAT ''' AND CREATOR ='''CONCAT CREATOR CONCAT'''*'  
FROM SYSIBM.SYSTABLES WHERE 
CREATOR IN (creator_list)
        AND NAME IN (table_list)
        AND CARDF >= 0;

SELECT 'UPDATE SYSIBM.SYSINDEXES SET FIRSTKEYCARDF='
CONCAT  STRIP(CHAR(FIRSTKEYCARDF))
CONCAT ',FULLKEYCARDF='CONCAT STRIP(CHAR(FULLKEYCARDF))
CONCAT',NLEAF='CONCAT STRIP(CHAR(NLEAF))
CONCAT',NLEVELS='CONCAT STRIP(CHAR(NLEVELS))
CONCAT',CLUSTERRATIO='CONCAT STRIP(CHAR(CLUSTERRATIO))   
CONCAT',CLUSTERRATIOF='CONCAT STRIP(CHAR(CLUSTERRATIOF)) 
CONCAT',DATAREPEATFACTORF='CONCAT STRIP(CHAR(DATAREPEATFACTORF))
CONCAT' WHERE NAME='''CONCAT NAME
CONCAT ''' AND CREATOR ='''CONCAT CREATOR CONCAT'''*'
FROM SYSIBM.SYSINDEXES 
WHERE TBCREATOR IN (creator_list) 
      AND TBNAME IN (table_list)
      AND FULLKEYCARDF >= 0;

SELECT 'UPDATE SYSIBM.SYSCOLUMNS SET COLCARDF='
CONCAT STRIP(CHAR(COLCARDF))  
CONCAT',HIGH2KEY= X''' CONCAT HEX(HIGH2KEY)
CONCAT''',LOW2KEY= X''' CONCAT HEX(LOW2KEY)
CONCAT''' WHERE TBNAME=''' CONCAT TBNAME CONCAT  ''' AND COLNO='
CONCAT STRIP(CHAR(COLNO)) 
CONCAT ' AND TBCREATOR =''' CONCAT TBCREATOR CONCAT'''*'
FROM SYSIBM.SYSCOLUMNS 
WHERE TBCREATOR IN (creator_list)
      AND TBNAME IN (table_list)
      AND COLCARDF >= 0;

End program-specific programming interface information.

Delete statistics from SYSTABSTATS on the test subsystem for the specified tables by using the following statement:
```
DELETE FROM (TEST_SUBSYSTEM).SYSTABSTATS
   WHERE OWNER IN (creator_list)
   AND   NAME IN (table_list);
```

Use INSERT statements to repopulate SYSTABSTATS with production statistics that are generated from the following statement:

SELECT 'INSERT INTO SYSIBM.SYSTABSTATS'
CONCAT '(CARD,NPAGES,PCTPAGES,NACTIVE,PCTROWCOMP'
CONCAT ',STATSTIME,IBMREQD,DBNAME,TSNAME,PARTITION'
CONCAT ',OWNER,NAME,CARDF) VALUES('
CONCAT  STRIP(CHAR(CARD))             CONCAT ' ,'
CONCAT  STRIP(CHAR(NPAGES))           CONCAT ' ,'
CONCAT  STRIP(CHAR(PCTPAGES))         CONCAT ' ,'
CONCAT  STRIP(CHAR(NACTIVE))          CONCAT ' ,'
CONCAT  STRIP(CHAR(PCTROWCOMP))       CONCAT ' ,'
CONCAT  '''' CONCAT CHAR(STATSTIME)   CONCAT ''' ,'
CONCAT  '''' CONCAT IBMREQD           CONCAT ''' ,'
CONCAT  '''' CONCAT STRIP(DBNAME)     CONCAT ''' ,'
CONCAT  '''' CONCAT STRIP(TSNAME)     CONCAT ''' ,'
CONCAT  STRIP(CHAR(PARTITION))        CONCAT ' ,'
CONCAT  '''' CONCAT STRIP(OWNER)      CONCAT ''' ,'
CONCAT '''' CONCAT STRIP(NAME)        CONCAT ''' ,'
CONCAT  STRIP(CHAR(CARDF))            CONCAT  ')*'  
FROM  SYSIBM.SYSTABSTATS
      WHERE OWNER IN (creator_list)
      AND   NAME IN (table_list);

End program-specific programming interface information.

Delete statistics from SYSCOLDIST on the test subsystem for the specified tables by using the following statement:
```
DELETE FROM (TEST_SUBSYSTEM).SYSCOLDIST
   WHERE TBOWNER IN (creator_list)
   AND   TBNAME IN (table_list);
```

Use INSERT statements to repopulate SYSCOLDIST with production statistics that are generated from the following statement:

SELECT 'INSERT INTO SYSIBM.SYSCOLDIST '
CONCAT '(FREQUENCY,STATSTIME,IBMREQD,TBOWNER'
CONCAT ',TBNAME,NAME,COLVALUE,TYPE,CARDF,COLGROUPCOLNO'
CONCAT ',NUMCOLUMNS,FREQUENCYF,QUANTILENO,LOWVALUE' 
CONCAT ',HIGHVALUE) VALUES(' 
CONCAT  STRIP(CHAR(FREQUENCY))            CONCAT ' ,'
CONCAT  '''' CONCAT CHAR(STATSTIME)       CONCAT ''' ,'
CONCAT  '''' CONCAT IBMREQD               CONCAT ''' ,'
CONCAT  '''' CONCAT STRIP(TBOWNER)        CONCAT ''' ,'
CONCAT  '''' CONCAT STRIP(TBNAME)         CONCAT ''','
CONCAT  '''' CONCAT STRIP(NAME)           CONCAT ''' ,'
CONCAT  'X''' CONCAT STRIP(HEX(COLVALUE)) CONCAT ''' ,'
CONCAT  '''' CONCAT TYPE                  CONCAT ''' ,'
CONCAT  STRIP(CHAR(CARDF))                CONCAT ' ,'
CONCAT  'X'''CONCAT STRIP(HEX(COLGROUPCOLNO)) CONCAT ''' ,'
CONCAT  CHAR(NUMCOLUMNS)                  CONCAT ' ,'
CONCAT  STRIP(CHAR(FREQUENCYF))           CONCAT ','
CONCAT  CHAR(QUANTILENO)                  CONCAT ',' 
CONCAT  'X''' CONCAT STRIP(HEX(LOWVALUE)) CONCAT ''',' 
CONCAT  'X''' CONCAT STRIP(HEX(HIGHVALUE)) CONCAT ''')*'
FROM  SYSIBM.SYSCOLDIST
      WHERE TBOWNER IN (creator_list)
      AND   TBNAME IN (table_list);

End program-specific programming interface information.