ITPub博客

首页 > Linux操作系统 > Linux操作系统 > db2 错误小结-1

db2 错误小结-1

原创 Linux操作系统 作者:47328983 时间:2013-02-06 11:57:40 0 删除 编辑
把这次数据迁移和修改表结构时遇到的问题小结下,做个参考:
1.对于执行中的报错,可以在db2命令行下运行命令 : db2=>? SQLxxx 查看对应的报错原因及解决方法。
2. 错误小结:
 
  •  SQL0206N  "SQL_COU_ALL" is not valid in the context where it is used.  SQLSTATE=42703
            SQL_COU_ALL不存在
    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 "" on table
                "".
         Explanation:
      Access to table "" is restricted. The cause is based on the
      following reason codes "":
      7
           The table is in the reorg pending state. This can occur after
             an ALTER TABLE statement containing a REORG-recommended
             operation.
    User response:
    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
     表处于pending state,需要重组该表。
  • ALTER TABLE xxx.FM_BORRO     ALTER  COLUMN DOCUMENT_TYPE_1       SET NOT NULL
     报错:  SQL20054N  The table "xxx.FM_BORROW" is in an invalid state for the operation. Reason code="23".  SQLSTATE=55019
    db2 => ? SQL20054N
    SQL20054N  The table "" is in an invalid state for the
          operation. Reason code="".
    Explanation:
    The table is in a state that does not allow the operation. The
    "" 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.
      说明:当对表结构进行更改时,也可能导致表状态异常。比如,以下操作可能会导致表处于reorg-pending状态。
    (1)        alter table alter set data type
    (2)        alter table alter set not null
    (3)        alter table drop column
    (4)        ……   
    出现reorg pending的根源是当表结构变化后影响了数据行中的数据格式,这时需要对表做reorg。可能的错误号是:
    01.SQL0668N  Operation not allowed for reason code "7" on table "SDD.ST_INCRE008".  SQLSTATE=57016
    03.SQL20054N  The table "" is in an invalid state for the operation. Reason code="7".
    复制代码每一个表在不进行重组(Reorg)的前提下,只允许进行3次结构上的修改。三次更改后必须对表进行重组。
    REORG TABLE "xx"."FM_BORROW"
      ALLOW NO ACCESS
      KEEPDICTIONARY;
  •   ALTER TABLE xxx.FAQ  ALTER COLUMN    FAQ_UNIT_NAME   SET DATA TYPE   VARCHAR(800)
      报错 SQL0670N  The row length of the table exceeded a limit of "8101" bytes. (Table space "SHJD_DATA".)  SQLSTATE=54010
    SQL0670N  The row length of the table exceeded a limit of ""
          bytes. (Table space "".)
    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.
    说明:该表所有字段长度之和大于当前数据库页大小(8K)
  • ALTER TABLE xxx.BP_TMPDATA_1_TB_1903     ALTER COLUMN         RATE         SET DATA TYPE   DECIMAL(6,4) 
       报错 SQL0190N  ALTER TABLE "BP_TMPDATA_1_TB_1903" specified attributes for column "RATE" that are not compatible with the existing column.  SQLSTATE=42837
    说明: BP_TMPDATA_1_TB_1903 现有数据的精度超过了 DECIMAL(6,4)  ,比如100.00
  • 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
    检查服务器的配置情况如下:
    验证存在的DB2数据库
    db2 list db directory
    db2 list db directory show detail
    验证实例使用的通讯协议,查看DB2COMM变量
    db2set -all
    查看数据库管理器的配置,查看SVCENAME(特指tcpip协议)
    db2 get dbm cfg
    查看/etc/services中,有无与上面对应SVCENAME的端口,例如:
    db2cDB2 50000/tcp
            验证远程服务器实例配置
             db2 list node directory
            db2 list node directory show detail
               ping hostname来验证通讯
              使用telnet hostname port来验证是否能连到实例
              用DB2提供的PCT工具来检测一下

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/241379/viewspace-753977/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-03-07

  • 博文量
    111
  • 访问量
    332084