PATH AND ASU COST IN DEVELOPMENT.
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
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
The tables that are used by DB2 optimizer for choosing access path for
a program are.
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
Rexx exec that is given here produces a
flat file with all the relevant SQL statements that are required to
update the target catalog.
your development table qualifiers are different from production, make
sure you edit this flat file and do a global change.
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
If you want to know what tables are being touched by your program. You
can query SYSIBM.SYSPACKDEP or SYSIBM.SYSPLANDEP.