IBM Support

Runstats may update unexpected HIGH2KEY and LOW2KEY statistic values which may cause SQL1227N

Technote (FAQ)


Question

Why is SQL1227N returned when replay db2look output?

Cause

It is an known limitation of current DB2 V9.7 and above versions' runstats.

Answer

If we run an example scenario below, at the end of script "db2 -tvf db2_SAMPLE.sql"

gets SQL1227N.
--- repro.sh ---
#!/bin/sh

db2 -v "drop db sample"
db2 -v "create db sample"
db2 -v "connect to sample"
db2 -v "drop table db2inst1.tab1"
db2 -v "create table db2inst1.tab1 ( col1 varchar(10) )"
db2 -v "insert into db2inst1.tab1 values (NULL),(' '),(' '),(' ')"
db2 -v "runstats on table db2inst1.tab1"
db2 -v "terminate"
db2 -v "connect to sample"
db2look -d sample -a -e -m -o db_SAMPLE.sql

db2 -v terminate
db2 -v "drop db sample"
db2 -v "create db sample"
db2 -v "connect to sample"
db2 -tvf db_SAMPLE.sql | tee test.log
# replay db2look output and gets SQL1227N at this point
db2 -v terminate
----------

Here is the SQL1227N message:
-----
UPDATE SYSSTAT.COLUMNS SET COLCARD=4, NUMNULLS=1, SUB_COUNT=-1, SUB_DELIM_LENGTH=-1,
AVGCOLLENCHAR=7, HIGH2KEY='', LOW2KEY=' ', AVGCOLLEN=12 WHERE COLNAME = 'COL1'
AND TABNAME = 'TAB1' AND TABSCHEMA = 'DB2INST1'
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL1227N The catalog statistic "" for column "HIGH2KEY" is out of range for
its target column, has an invalid format, or is inconsistent in relation to
some other statistic. Reason Code = "3". SQLSTATE=23521
-----
As above, UPDATE SYSSTAT.COLUMNS statement gets SQL1227N at col1 varchar(10).
It is caused by runstats, writes an unexpected HIGH2KEY and LOW2KEY statistic values.

Here is work arounds:

Work around #1
1. $ db2set DB2_STATISTICS=USCC:0
** this variable is available for V10.1 FP4 and above.
2. $ db2 terminate
3. $ db2start
4. $ repro.sh

Work around #2
1. Edit SQO1227N causing UPDATE statements' COLCARD=4 to 3 in db_SAMPLE.sql file.
2. Replay by running "db2 -tvf db_SAMPLE.sql"

Work around #3
1. Do not insert/import space(s) data, such as " ", " ".

Note:
  This behavior might be changed without notice in the future. We can confirm whether this technote
is valid or not by following sample scenario above.
Please contact your Sales Rep to submit a potential design change towards a future release.
Or please open a ticket, Request For Enhancement at https://www.ibm.com/developerworks/rfe/

Related information

RUNSTATS command

Document information

More support for: DB2 for Linux, UNIX and Windows
Compiler - Runstats

Software version: 9.7, 10.1, 10.5

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

Software edition: Advanced Enterprise Server, Advanced Workgroup Server, Enterprise Server, Workgroup Server

Reference #: 1979066

Modified date: 15 March 2016


Translate this page: