IBM Support

SQL1227N during replaying db2look -m output

Technote (FAQ)


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.

Related information

A Japanese translation is available

Document information

More support for: DB2 for Linux, UNIX and Windows
DB2 Tools - db2look

Software version: 9.7, 9.8, 10.1, 10.5

Operating system(s): AIX, HP-UX, Linux, Solaris, Windows

Reference #: 1991415

Modified date: 11 October 2016


Translate this page: