Update statistics and getting configuration parameters in DB2 LUW using db2look

In one of our previous blog post, we covered how we can use db2look utility to generate DDL. Today we will see one more use of db2look utility to obtain the database level parameters and registry variables and generate SQL to update the statistics.

db2look is a very powerful utility which can be used for various purposes. When used with –f option for a specific database, it generates the configuration parameters at database level and database manager level along with registry variables. Not all configuration parameters will be generated but only parameters which affect the query optimizer will be generated. When used with –m option, it will generate SQL to update statistics information for each table in the database. When this information is updated in the test database, we can trick the optimizer to opt for a similar query plan as accessed in the production database. We have already covered similar functionality for Oracle (DBMS_STATS.SET_TABLE_STATS) and SQL Server UPDATE STATISTICS and Nightly stats collection. There are other blog posts as well which cover different aspects of statistics collection, stale stats, performance implications etc. – you can search for statistics on this site to get to those.

As mentioned earlier, we can run db2look utility on production database server to collect the configuration parameters and latest statistics information which in turn can be updated on test database server to replicate the issue without affecting production server.

Following is the command to generate the file for configuration parameters.

db2look -d <database_name> -f -o <output_file_name>

Following is the actual command followed by an output. You can run it from db2 command window.

D:\Program Files\IBM\SQLLIB\BIN>db2look -d decipher -f -o decipher_cfg.out
— USER is: DECIPHER
— Output is sent to file: decipher_cfg.out

Output will be as shown below.

CONNECT TO DECIPHER;

——————————————————–
— Database and Database Manager configuration parameters
——————————————————–

UPDATE DBM CFG USING cpuspeed 2.086187e-007;
UPDATE DBM CFG USING intra_parallel NO;
UPDATE DBM CFG USING comm_bandwidth 100.000000;
UPDATE DBM CFG USING federated NO;
UPDATE DBM CFG USING fed_noauth NO;

UPDATE DB CFG FOR DECIPHER USING locklist 1234;
UPDATE DB CFG FOR DECIPHER USING dft_degree 1;
UPDATE DB CFG FOR DECIPHER USING maxlocks 98;
UPDATE DB CFG FOR DECIPHER USING avg_appls 1;
UPDATE DB CFG FOR DECIPHER USING stmtheap 2048;
UPDATE DB CFG FOR DECIPHER USING dft_queryopt 5;

———————————
— Environment Variables settings
———————————

COMMIT WORK;

CONNECT RESET;

TERMINATE;

Since this is our test box and we have not set any registry variables using db2set command, so it didn’t generate script for any of the registry variables.

To generate the script for updating statistics, run db2look command again with –m option (mimic mode). You can either specify to generate statistics for entire database or for specific tables as well. Following is the command to generate update statistics script for table ‘TEST’.

D:\Program Files\IBM\SQLLIB\BIN>db2look -d decipher -m -t TEST -o test3.out

• -d is the database name
• -t is table name. One can specify more than one table as well.
• -o is output file name.

We can use –u option to select only those tables which are created by that user (Ex. –u decipher). Entire output is difficult to put here but following is the abbreviated output so that we know what db2look is generating.

— This CLP file was created using DB2LOOK Version 9.1
— Timestamp: 8/14/2007 1:20:19 PM
— Database Name: DECIPHER

CONNECT TO DECIPHER;

———————————————
— Mimic Tables, Columns, Indexes and Column Distribution
———————————————

— Mimic table TEST

RUNSTATS ON TABLE “DECIPHER”.”TEST”
WITH DISTRIBUTION ON COLUMNS (
TEST_ID NUM_FREQVALUES 10 NUM_QUANTILES 20,
TEST_NAME NUM_FREQVALUES 10 NUM_QUANTILES 20);

UPDATE SYSSTAT.TABLES
SET CARD=669,
NPAGES=7,
FPAGES=11,
OVERFLOW=0,
ACTIVE_BLOCKS=0
WHERE TABNAME = ‘TEST’ AND TABSCHEMA = ‘DECIPHER’;

UPDATE SYSSTAT.COLUMNS
SET COLCARD=669,
NUMNULLS=0,
SUB_COUNT=-1,
SUB_DELIM_LENGTH=-1,
HIGH2KEY=’1343′,
LOW2KEY=’4′,
AVGCOLLEN=4
WHERE COLNAME = ‘TEST_ID’ AND TABNAME = ‘TEST’ AND TABSCHEMA = ‘DECIPHER’;

COMMIT WORK;

CONNECT RESET;

TERMINATE;

In above example CARD and COLCARD indicates the number of rows in the table and distinct values for the column. As we can see, above is the complete script which makes connection to the database, updates required statistics information and disconnects from the database. We just need to run it from the command line or from the command window to update the statistics collected from the production database to the test database. This way we can simulate our test environment to a close proximity of production environment. One can update SYSTAT.TABLES or SYSSTAT.COLUMNS table directly provided user has privilege, but it is not advisable. db2look generated plethora of information so it is advisable to let it generate through db2look utility and then simply run the script created by utility to update the test database server.

But as mentioned in our previous blog entries, this is not an alternative of benchmark or a load test. In order to know how queries perform under load test, one has to create good amount of data which represents the production load and perform the stress test against the data.

For more details on db2look options, you can visit IBM website at
http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/core/r0002051.htm