Question

DB2 中查询语句超过了表限制怎么办

后台给出的提示为’sqlcode=-101
(-101 SQL语句超出了已确定的DB2限制：例如，表的数目太多，语句中的字节太多 )

请问在数据库调整的方面是否可以进行数据库配置上的设置？

Answer 1

语句太长、太复杂，超出了DB2的限制，重新SQL语句吧。
建立临时表，把数据放入临时表中。

db2 => ? sql101

SQL0101N The statement is too long or too complex.

Explanation:

The statement could not be processed because it exceeds a system limit
for either length or complexity, or because too many constraints or
triggers are involved.

If the statement is one that creates or modifies a packed description,
the new packed description may be too large for its corresponding column
in the system catalogs.

Federated system users should also check to see if the statement:
* Exceeds either a federated server system limit or a data source
system limit for length or complexity.
* Violates some other data source specific limit.

The statement cannot be processed.

Note: Where character data conversions are performed for applications
and databases running under different codepages, the result of the
conversion is exceeding the length limit.

User response:

Either:
* Break the statement up into shorter or less complex SQL statements.
* Increase the size of the statement heap (stmtheap) in the database
configuration file.
* Reduce the number of check or referential constraints involved in the
statement or reduce the number of indexes on foreign keys.
* Reduce the number of triggers involved in the statement.
* Federated system users: determine which data source is failing the
statement (see the Troubleshooting Guide for procedures to follow to
identify the failing data source) and determine the cause of the
rejection. If the rejection is coming from the federated server,
increase the size of the statement heap (stmtheap) in the database
configuration file.

sqlcode: -101

sqlstate: 54001

Answer 2

这个估计是你采取的SQL不是很规范吧，建议你查看下SQL逻辑。没有具体的SQL现在确实不好分析是什么情况。或者将SQL分拆使用临时表分步处理

Answer 3

建个临时表放进去就哦了，在原来的表没法改限制了，页大小是确定的

Answer 4

From IBM, 升级db2版本

Problem conclusionFirst Fixed in DB2 Version V9.7 fix pak 4

追问

发生错误的前提。。。已经是9.7的版本了。

追答

pack 4...

Answer 5

用临时表。