Blog
DB2utor

Categories

February 14, 2012

Production Modeling Made More Accurate

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.