One significant challenge DBAs face is validating the access path of SQL from test to production environments. Even if the SQL looks great when run in a test environment, it may perform poorly once it's moved to the production system. Oftentimes this is because the code simply doesn't use the same access path in production that it does in test.
The DB2 optimizer uses metrics stored in the catalog based on the runstats utility. To conduct testing in a non-production environment, you must have these same statistics. Third-party vendors have helped by providing tools that copy the statistics from one subsystem to another. This means we can prototype the SQL statement and come close to duplicating the access path. However, this is not full proof, since vendor solutions can't calculate the IBM hardware statistics that also figure into the optimizer's metrics:
- Number of CPUs
- Speed of CPUs
- RID POOL size
- SORT POOL size
- Buffer pool size
Now IBM is introducing its own production modeling function, which takes into account these hardware considerations. It's available via APAR PM26475.
To capture the metrics needed from the production system, apply the APAR and then execute this SQL statement:
SET CURRENT DEGREE='ANY';
EXPLAIN ALL SET QUERYNO=6475 FOR
SELECT * FROM SYSIBM.SYSDUMMY1;
SELECT HEX(SUBSTR(IBM_SERVICE_DATA,25,2)) AS CPU_COUNT,
HEX(SUBSTR(IBM_SERVICE_DATA,69,4)) AS CPU_SPEED,
HEX(SUBSTR(IBM_SERVICE_DATA,13,4)) AS RIDPOOL,
HEX(SUBSTR(IBM_SERVICE_DATA,9,4)) AS SORT_POOL_SIZE
FROM PLAN_TABLE WHERE QUERYNO=6475;
Here's the output in SPUFI:
CPU_COUNT CPU_SPEED RIDPOOL SORT_POOL_SIZE
000A 0000001C 000000FA 009C4000
The APAR walks you through the process of storing this data and activating the GLOBAL PROFILE used by your test system's EXPLAIN function. With this solution in place, the access path you put into production should be the same one you created in test.
Recent Comments