SQL1477N For table "
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
