IBM Support

SQL1227N during replaying db2look -m output

Question & Answer


Question

I would like to copy the statistics information on a production system into a test system in order to create access plans that are similar to those that would be used on the production system. Both systems have large amount of data, so I use db2look -m -r option to avoid running runstats since it takes long time. However sometimes I got SQL1227N errors when replaying the db2look -m -r output. How can I avoid the error?

Answer

The "-r" option is used to generate db2look output without runstats command output. However, current db2look code cannot reset SYSSTAT.COLUMNS and SYSSTAT.INDEXES when -r is applied. As a result, SQL1227N sometimes occur when applying the mimic when COLCARD etc, and CARD for the table is inconsistent. In db2look up to version in V10.5 please use db2look -m without -r option or insert resetting SYSSTAT.COLUMNS and SYSSTAT.INDEXES manually in db2look output with "-m" and "-r". This limitation will be changed in releases later than V10.5. In most cases db2look "-m" without "-r" option should not cause SQL1227N error, but still the error can occur when the statistics on the source system is inconsistent. Here are examples:

Why COLCARD is sometimes bigger than CARD after RUNSTATS?
http://www.ibm.com/support/docview.wss?uid=swg21985376

Runstats may update unexpected HIGH2KEY and LOW2KEY statistic values which may cause SQL1227N
http://www.ibm.com/support/docview.wss?uid=swg21979066

As documented in above technotes, you can bypass the error by setting following undocumented registry variable even if the statistics are inconsistent:

db2set DB2_STATISTICS=USCC:0
recycle the instance

This registry variable can be used from 9.7FP9/10.1FP4/10.5.

[{"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":"DB2 Tools - db2look","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF010","label":"HP-UX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"}],"Version":"9.8;9.7;10.1;10.5","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
16 June 2018

UID

swg21991415