DB2 手动修改索引统计信息时： SQL1227N Reason Code = "8"

最新推荐文章于 2023-11-21 12:02:27 发布

原创最新推荐文章于 2023-11-21 12:02:27 发布 · 1.5k 阅读

0 ·

本内容遵循CC 4.0 BY-SA版权协议

标签

#DB2 #索引 #统计信息

DB2 专栏收录该内容

181 篇文章

订阅专栏

本文介绍在DB2数据库中遇到的关于手动修改索引统计信息时出现的错误SQL1227N及其解决方法。通过实例演示了如何正确更新索引的INDCARD字段以避免该错误。

问题：

在手动修改某个索引的统计信息时，报错 SQL1227N, Reason Code = "8"：

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 "1" for column "INDCARD" is out of range for
its target column, has an invalid format, or is inconsistent in relation to
some other statistic. Reason Code = "8". SQLSTATE=23521

问题分析：

可能的原因之一是统计信息与对应表的不一致。

重现：

$ db2level
DB21085I Instance "e97q6c" uses "64" bits and DB2 code release "SQL09076" with
level identifier "08070107".
Informational tokens are "DB2 v9.7.0.6", "s120629", "IP23414", and Fix Pack
"6".
Product is installed at "/opt/IBM/db2/V9.7.6".

$ db2 "create table t1(id int, name char(20))"
DB20000I The SQL command completed successfully.

$ db2 "insert into t1 values(1,'ab'),(2,'bc'),(3,'cd'),(1,'ef'),(4,'bc'),(5,'bc')"
DB20000I The SQL command completed successfully.

$ db2 "create index idx1 on t1(id)"
DB20000I The SQL command completed successfully.

$ db2 "create index idx2 on t1(name)"
DB20000I The SQL command completed successfully.

$ db2 "select NLEAF, NLEVELS, INDCARD, substr(INDNAME,1,30) as INDNAME from syscat.indexes where TABNAME='T1'"

NLEAF NLEVELS INDCARD INDNAME
-------------------- ------- -------------------- ------------------------------
-1 -1 -1 IDX1
-1 -1 -1 IDX2

2 record(s) selected.

$ db2 "runstats on table E97Q6C.T1 and detailed INDEXES ALL"
DB20000I The RUNSTATS command completed successfully.

$ db2 "select NLEAF, NLEVELS, INDCARD, substr(INDNAME,1,30) as INDNAME from syscat.indexes where TABNAME='T1'"

NLEAF NLEVELS INDCARD INDNAME
-------------------- ------- -------------------- ------------------------------
1 1 6 IDX1
1 1 6 IDX2

2 record(s) selected.

$ db2 " update SYSSTAT.INDEXES set INDCARD=5 where INDNAME='IDX1' and tabname= 'T1' "
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 "5" for column "INDCARD" is out of range for
its target column, has an invalid format, or is inconsistent in relation to
some other statistic. Reason Code = "8". SQLSTATE=23521

$ db2 " update SYSSTAT.INDEXES set INDCARD=6 where INDNAME='IDX1' and tabname= 'T1' "
DB20000I The SQL command completed successfully.

$ db2 " update SYSSTAT.INDEXES set INDCARD=188 where INDNAME='IDX1' and tabname= 'T1' "
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 "188" for column "INDCARD" is out of range for
its target column, has an invalid format, or is inconsistent in relation to
some other statistic. Reason Code = "8". SQLSTATE=23521

解决：
先修改表的统计信息，再修改索引的统计信息，使其保持一致
$ db2 "update SYSSTAT.TABLES SET CARD=188 where tabname='T1'"
DB20000I The SQL command completed successfully.

$ db2 " update SYSSTAT.INDEXES set INDCARD=188 where INDNAME='IDX1' and tabname= 'T1' "
DB20000I The SQL command completed successfully.

$ db2 "select NLEAF, NLEVELS, INDCARD, substr(INDNAME,1,30) as INDNAME from syscat.indexes where TABNAME='T1'"

NLEAF NLEVELS INDCARD INDNAME
-------------------- ------- -------------------- ------------------------------
1 1 188 IDX1
1 1 6 IDX2

2 record(s) selected.



----------------------------------------------------------------------
在以下的版本中测试，得到完全相同的结果：

[db2b /home/db2users/e101q3a]$ db2level
DB21085I This instance or install (instance name, where applicable: "e101q3a")
uses "64" bits and DB2 code release "SQL10013" with level identifier
"0204010E".
Informational tokens are "DB2 v10.1.0.3", "s130918", "IP23509", and Fix Pack
"3".
Product is installed at "/opt/IBM/db2/V10.1.3".