Scenario: You would like to test a new index in an IBM DB2 10.1 database. Unfortunately your development system doesn’t have anywhere near the same number of records in the database table on which you are creating the new index.
Like many other RDBMSs, DB2 uses table and column statistics to influence the optimiser’s decision as to which access path to choose at execution time.
By replicating only the statistics, it’s possible to fool the optimiser into thinking the table has more records present, than it really does. This means that it’s likely to choose a different access path.
When performance tuning a database, it’s useful to use this method of fooling the optimiser, because you can emulate a larger table in a small development system with little actual data.
The process in DB2 is like this:
– Generate (or export) the statistics for a table in a production database system (PRD) schema DBA.
– Modify the export file.
– Upload the contents of the export file into a development database system (DEV) schema DBB.
– Test.
Step 1 – Export the statistics for a table in production.
Connect into the production database (DBA), then use the db2look command to create an export file containing the UPDATE commands for adjusting the statistics:
db2prd>
db2 connect to PRD
db2prd> db2look -d PRD -e -c -m -r -t DBA.TABLE1 -o table1_STATS.sql
The output will be written to the table1_STATS.sql file in the current directory.
Step 2 – Modify the export file.
You should edit the output file to remove all lines before the line “– Mimic table TABLE1”, failure to do this could mean dropping the TABLE1 table in development.
You must also edit the file and replace all instances of schema “DBA” with “DBB” to ensure that the correct development database schema is found.
The modified file will look like:
— Mimic table TABLE1
UPDATE SYSSTAT.TABLES
SET CARD=2341434,
NPAGES=14636,
FPAGES=14645,
OVERFLOW=9473,
ACTIVE_BLOCKS=0
WHERE TABNAME = ‘TABLE1’ AND TABSCHEMA = ‘DBB’;
UPDATE SYSSTAT.COLUMNS
SET COLCARD=1,
NUMNULLS=0,
…
Step 3 – Upload the statistics into the development database.
db2dev>
db2 connect to DEVdb2dev>
db2 -tf ikpf_STATS.sql …
DB20000I The SQL command completed successfully.
DB20000I The SQL command completed successfully.
SQL0100W No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. SQLSTATE=02000
SQL0100W No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. SQLSTATE=02000
You’re now ready to test.
To reset (re-gather) the statistics on the development database table, you simply need to re-collect statistics using the RUNSTATS command: “db2 RUNSTATS ON TABLE TABLE1 WITH DISTRIBUTION AND INDEXES ALL”.
