1.對(duì)于執(zhí)行中的報(bào)錯(cuò)，可以在db2命令行下運(yùn)行命令 ： db2=>? SQLxxx 查看對(duì)應(yīng)的報(bào)錯(cuò)原因及解決方法。

2.錯(cuò)誤SQL0206N SQLSTATE=42703? 檢測(cè)到一個(gè)未定義的列、屬性或參數(shù)名。
? SQL0206N? "SQL_COU_ALL" is not valid in the context where it is used.? SQLSTATE=42703
????? db2 => ? "42703"???
????? db2 => ? SQL0206N
??
3.錯(cuò)誤SQL0668N code "7" SQLSTATE=57016?? 表處于pending state，需要重組該表。
? SQL0668N? Operation not allowed for reason code "7" on table "xxx.Z_BP_TMPBATCH_TB_HIS".? SQLSTATE=57016
???? db2 => ? SQL0668N code 7?
???? SQL0668N? Operation not allowed for reason code "<reason-code>" on table "<table-name>".
???? Explanation:說(shuō)明
?????? Access to table "<table-name>" is restricted. The cause is based on the following reason codes "<reason-code>":
?????? 7?? The table is in the reorg pending state. This can occur after an ALTER TABLE statement containing a REORG-recommended operation.
???? User response:用戶響應(yīng)?
?????? 7?? Reorganize the table using the REORG TABLE command.
??? For a table in the reorg pending state, note that the following clauses are not allowed when reorganizing the table:
??? *? The INPLACE REORG TABLE clause
??? *? The ON DATA PARTITION clause for a partitioned table when table has nonpartitioned indexes defined on the table

4.錯(cuò)誤SQL20054N code="23" SQLSTATE=55019 對(duì)表的修改次數(shù)達(dá)到3次，必須重組表
? ALTER TABLE xxx.FM_BORRO ALTER COLUMN DOCUMENT_TYPE_1 SET NOT NULL
? 報(bào)錯(cuò):SQL20054N? The table "xxx.FM_BORROW" is in an invalid state for the operation. Reason code="23".? SQLSTATE=55019
? db2 => ? SQL20054N
??? SQL20054N? The table "<table-name>" is in an invalid state for the operation. Reason code="<reason-code>".
? Explanation:
??? The table is in a state that does not allow the operation. The "<reason-code>" indicates the state of the table that prevents the operation.
??? 23?? The maximum number of REORG-recommended alters have been
???????? performed. Up to three REORG-recommended operations are allowed
???????? on a table before a reorg must be performed, to update the
???????? tables rows to match the current schema.
? User response:
??? 23??????? Reorg the table using the reorg table command.
??? 說(shuō)明：當(dāng)對(duì)表結(jié)構(gòu)進(jìn)行更改時(shí)，也可能導(dǎo)致表狀態(tài)異常。比如，以下操作可能會(huì)導(dǎo)致表處于reorg-pending狀態(tài)。
?(1)??????? alter table <tablename> alter <colname> set data type <new data type>
?(2)??????? alter table <tablename> alter <colname> set not null
?(3)??????? alter table <tablename> drop column <colname>
?(4)??????? ……??
?出現(xiàn)reorg pending的根源是當(dāng)表結(jié)構(gòu)變化后影響了數(shù)據(jù)行中的數(shù)據(jù)格式，這時(shí)需要對(duì)表做reorg?？赡艿腻e(cuò)誤號(hào)是：
?01.SQL0668N? Operation not allowed for reason code "7" on table "SDD.ST_INCRE008".? SQLSTATE=57016
?03.SQL20054N? The table "<table-name>" is in an invalid state for the operation. Reason code="7".
?復(fù)制代碼每一個(gè)表在不進(jìn)行重組（Reorg）的前提下，只允許進(jìn)行3次結(jié)構(gòu)上的修改。三次更改后必須對(duì)表進(jìn)行重組。
?REORG TABLE "xx"."FM_BORROW" ALLOW NO ACCESS KEEPDICTIONARY;

5.報(bào)錯(cuò) SQL0670N? SQLSTATE=54010 該表所有字段長(zhǎng)度之和大于當(dāng)前數(shù)據(jù)庫(kù)頁(yè)大小（8K）
? ALTER TABLE xxx.FAQ ALTER COLUMN FAQ_UNIT_NAME SET DATA TYPE VARCHAR(800)
? 報(bào)錯(cuò) SQL0670N? The row length of the table exceeded a limit of "8101" bytes. (Table space "SHJD_DATA".)? SQLSTATE=54010
? db2 => ? SQL0670N
??? SQL0670N? The row length of the table exceeded a limit of "<length>" bytes. (Table space "<tablespace-name>".)
? Explanation:
??? The row length of a table in the database manager cannot exceed:
?*? 4005 bytes in a table space with a 4K page size
?*? 8101 bytes in a table space with an 8K page size
?*? 16293 bytes in a table space with an 16K page size
?*? 32677 bytes in a table space with an 32K page size
??? The length is calculated by adding the internal lengths of the columns.Details of internal column lengths can be found under CREATE TABLE in the SQL Reference.
? User response:
??? 指定頁(yè)大小更大的表空間；消除表中的一列或多列

6.報(bào)錯(cuò) SQL0190N? SQLSTATE=42837 不能改變?cè)摿?，因?yàn)樗膶傩耘c當(dāng)前的列屬性不兼容
? ALTER TABLE xxx.BP_TMPDATA_1_TB_1903 ALTER COLUMN RATE SET DATA TYPE DECIMAL(6,4)
? 報(bào)錯(cuò) SQL0190N? ALTER TABLE "BP_TMPDATA_1_TB_1903" specified attributes for column "RATE" that are not compatible with the existing column.? SQLSTATE=42837
? 說(shuō)明： BP_TMPDATA_1_TB_1903 現(xiàn)有數(shù)據(jù)的精度超過(guò)了 DECIMAL(6,4)? ，比如100.00

7.報(bào)錯(cuò) SQL30081N SQLSTATE=08001?? 檢測(cè)到通信錯(cuò)誤? 無(wú)法與應(yīng)用程序服務(wù)器或其他服務(wù)器建立連接
? SQL30081N? A communication error has been detected.
??? Communication protocol being used: "TCP/IP". Communication API being used: "SOCKETS".?
??? Location where the error was detected: "10.0.0.200".? Communication function detecting the error: "selectForConnectTimeout".?
??? Protocol specific error code(s): "0", "*", "*".? SQLSTATE=08001
??? 檢查服務(wù)器的配置情況如下：
????? 驗(yàn)證存在的DB2數(shù)據(jù)庫(kù)
?db2 list db directory
?db2 list db directory show detail
????? 驗(yàn)證實(shí)例使用的通訊協(xié)議，查看DB2COMM變量
?db2set -all
????? 查看數(shù)據(jù)庫(kù)管理器的配置，查看SVCENAME（特指tcpip協(xié)議）
?db2 get dbm cfg
????? 查看/etc/services中，有無(wú)與上面對(duì)應(yīng)SVCENAME的端口，例如：
?db2cDB2 50000/tcp
????? 驗(yàn)證遠(yuǎn)程服務(wù)器實(shí)例配置
??????? db2 list node directory
??????? db2 list node directory show detail
????? ping hostname來(lái)驗(yàn)證通訊
????? 使用telnet hostname port來(lái)驗(yàn)證是否能連到實(shí)例
????? 用DB2提供的PCT工具來(lái)檢測(cè)一下

?

DB2 sql報(bào)錯(cuò)后查證原因與解決問(wèn)題的方法