SQL1477N

1 背景

查看表时报如下错误：

SQL1477N  For table "<table-name>" an object "<object-id>" in table space "<tbspace-id>" cannot be accessed.

2 问题处理

2.1 分析错误代码解析

[inst105@guizhou_hp-pop-10-150-57-13 ~]$ db2 ? SQL1477N


SQL1477N  For table "<table-name>" an object "<object-id>" in table
      space "<tbspace-id>" cannot be accessed.

Explanation: 

An attempt was made to access a table where one of its objects is not
accessible. The table may not be accessible because of one of the
following reasons:

*  The table had NOT LOGGED INITIALLY activated when the unit of work
   was rolled back.

*  The table is a partitioned created temporary table or declared
   temporary table and one or more database partitions failed since the
   temporary table was instantiated or declared.

*  ROLLFORWARD encountered the activation of NOT LOGGED INITIALLY on
   this table or a NONRECOVERABLE load on this table.

Access to this table is not allowed because its integrity cannot be
guaranteed.

User response: 

One of the following actions can be taken.

*  If the object is a table and it had NOT LOGGED INITIALLY activated,
   drop the table. If this table is required, re-create it.

*  If the object is a data partition, detach it from the table. If this
   data partition is required, add a new one.

*  If the object is a non-partitioned index, drop the index. If this
   index is required, create a new one.

*  If the table is a created temporary table, disconnect from the server
   and connect again to instantiate a new copy of the created temporary
   table.

*  If the table is a declared temporary table, drop the table. If this
   table is required, declare it again.

*  Otherwise, restore from a table space or database backup. The backup
   image must have been taken subsequent to the commit point following
   the completion of the non-recoverable operation (NOT LOGGED INITIALLY
   operation, or NONRECOVERABLE load).

The catalogs and administrative views can be used to determine what the
object is. To determine if the object is a table, use the following
query:

SELECT TABNAME
  FROM SYSCAT.TABLES
  WHERE TBSPACEID="<tbspace-id>" AND
    TABLEID="<object-id>"


If a table name does not appear as the result for this query you can
determine if the object is a partition by using the following query:

SELECT DATAPARTITIONNAME, TABNAME
  FROM SYSCAT.DATAPARTITIONS
  WHERE TBSPACEID="<tbspace-id>" AND
    PARTITIONOBJECTID="<object-id>"


To determine if the object is an index, use the following query:

SELECT INDNAME
  FROM SYSCAT.INDEXES
  WHERE TBSPACEID="<tbspace-id>" AND
    INDEX_OBJECTID="<object-id>"


To determine if the object is a created temporary table or a declared
temporary table, use the following query:

SELECT TABNAME
 FROM SYSIBMADM.ADMINTEMPTABLES
 WHERE TBSP_ID="<tbspace-id>" AND 
  INDEX_TAB_FILE_ID="<object-id>"


sqlcode: -1477

sqlstate: 55019


      Related information:
      ADMINTEMPTABLES administrative view and ADMIN_GET_TEMP_TABLES
   table function - Retrieve information for temporary tables

可能的原因是（已开启归档模式）：

• 单机环境：开启了不记日志（alter table TABNAME activate not logged initially）的操作之后，若显示地发出了rollback命令，或者SQL因为意外情况失败（比如锁超时、内存不足等）导致事务回滚。
• HADR的环境：如果PRIMARY端上有不记日志的操作或者load的时候指定了nonrecoverable选项，则备机上对应的表就不可访问了，若备机可读或者takeover以后，再次访问此表时就会报SQL1477N。

2.2 解决办法

第一种：如果有IBM服务可以通过电话（400 810 1818）获取服务密码，那么可以通过db2dart /MI将此对象标记为“无效”。

第二种：如果没有IBM服务，那么只能重建此对象，可以通过db2dart导出此对象的数据（不包含大对象，如LOB、XML等）。

第1步：导出表对象的DDL语句

[db2inst1]# db2look -d DBNAME -e -t TABNAME -o TABNAME.ddl

弟2步：通过db2dart将表数据导出

[db2inst1]# db2 "select tableid,tbspaceid from syscat.tables where tabname='TABNAME'"
[db2inst1]# db2dart DB2AFA /DDEL

第3步：删除或rename不可用的表

[db2inst1]# db2 “rename table TABNAME to TABNAME_BAK”

第4步：重建表对象

[db2inst1]# db2 -tvf TABNAME.ddl

第5步：导入数据

[db2inst1]# db2 “import from /path/TABNAME.del of del insert into TABNAME”

第6步：验证

[db2inst1]# db2 “select * from TABNAME fetch first 2 rows only”

3 模拟问题

#关闭自动提交
db2 => UPDATE COMMAND OPTIONS using c off
DB20000I  The UPDATE COMMAND OPTIONS command completed successfully.

#关闭记录日志功能
db2 => alter table t1 activate not logged initially
DB20000I  The SQL command completed successfully.

#插入一条数据
db2 => insert into t1 values(3,'name3')
DB20000I  The SQL command completed successfully.
db2 => select * from t1

ID          NAME                                                                                               
----------- ---------------------------------------------------------------------------------------------------
          1 name1                                                                                              
          2 name2                                                                                              
          3 name3                                                                                              

  3 record(s) selected.

#回滚
db2 => rollback
DB20000I  The SQL command completed successfully.

#SQL1477N报错再现
db2 => select * from t1

ID          NAME                                                                                               
----------- ---------------------------------------------------------------------------------------------------
SQL1477N  For table "INST105.T1" an object "4" in table space "2" cannot be 
accessed.  SQLSTATE=55019