SQL1227N during replaying db2look -m output
Posted: September 28, 2016 Filed under: DB2, DB2 10.5, Uncategorized | Tags: DB2 LUW Leave a comment
http://www-01.ibm.com/support/docview.wss?uid=swg21991415
db2look -m -r
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 it 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.