PRODUCTION ACCESS PATH AND ASU COST IN DEVELOPMENT.


 It is common knowledge that the cost of fixing a problem grows exponentially as it progresses along the development cycle into production. The problem of performance is even harder to detect  during the development phase. 

Usually the access paths chosen by DB2 optimizer are way off the mark in development. This might be due to any of the following reasons.

1. Data in development is a fraction of what is in production.
2. Runstats are not run in development on a regular basis and wrong or default filter factors are assumed.
3. Data in development is not refreshed from production on a regular basis and Runstats are not run.
4. Buffering strategy in development is drastically different from production.
5. CPU serial number ( CPU performance ) in development is drastically different from production.


The easiest way to solve problems caused due to reasons 1 through 3 is to copy relevant statistics from production to the development catalog tables.





The tables that are used by DB2 optimizer for choosing access path for a program are.

1. SYSIBM.SYSTABLESPACE
2.  SYSIBM.SYSCOLUMNS
3. SYSIBM.SYSCOLDIST
4.  SYSIBM.SYSINDEXES
5. SYSIBM.SYSTABLES
6. SYSIBM.SYSTABSTATS

Copying statistics manually is notoriously time consuming, especially if it needs to be done on a regular basis. This process can be implemented using Rexx, or any other procedural language.

The Rexx exec that is given here produces a flat file with all the relevant SQL statements that are required to update the target catalog.

If your development table qualifiers are different from production, make sure you edit this flat file and do a global change.

If your development database names and tablespace name are different from production, make sure you edit this flat file and do a global change.

It is assumed here that you have PLAN_TABLE  and DSN_STATEMNT_TABLE at yor shop.

The resulting flat file that contains the relevant SQL statements can be run against your development subsystem with SPUFI or DSNTEP2 or DSNTIAD.

If you want to know what tables are being touched by your program. You can query SYSIBM.SYSPACKDEP or SYSIBM.SYSPLANDEP.