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.
Was this topic helpful?
Document Information
Modified date:
16 June 2018
UID
swg21991415