在使用IBM DB2 Data Studio 3.1版本的时候,准备Browse Data 某个表格,就会弹出以下弹窗:
com.ibm.db2.jcc.am.SqlException: Operation not allowed for reason code "1" on table "DB2INST1.USER_PROFILE".. SQLCODE=-668, SQLSTATE=57016, DRIVER=4.13.111
Operation not allowed for reason code "1" on table "DB2INST1.USER_PROFILE".. SQLCODE=-668, SQLSTATE=57016, DRIVER=4.13.111
进入terminal,切换到db2inst1的账户:
[root@oc5873237464 elsa]# su db2inst1
查看错误代码SQL0668:
[db2inst1@oc5873237464 elsa]$ db2 ? sql0668
系统输出:
SQL0668N Operation not allowed for reason code "" on table "". Explanation: Access to table "" is restricted. The cause is based on the following reason codes "": 1 The table is in the Set Integrity Pending No Access state. The integrity of the table is not enforced and the content of the table may be invalid. An operation on a parent table or an underlying table that is not in the Set Integrity Pending No Access state may also receive this error if a dependent table is in the Set Integrity Pending No Access state. 2 The table is in the No Data Movement state. When in this state, operations that cause data movement are disallowed. Data movement operations include REDISTRIBUTE, update of database partitioning key, update of multidimensional clustering key, update of range clustering key, update of table partitioning key and REORG TABLE. 3 The table is in the Load Pending state. A previous LOAD attempt on this table resulted in failure. No access to the table is allowed until the LOAD operation is restarted or terminated. 4 The table is the Read Access state. This state can occur during on-line LOAD processing (LOAD INSERT with the READ ACCESS option), or after an on-line LOAD operation, but before all constraints have been validated in the newly appended portion of the table using the SET INTEGRITY statement. This state can also occur if the READ ACCESS clause was used when turning off integrity checking with the SET INTEGRITY statement. No update activity is allowed on this table. 5 The table is in the Load In Progress state. The LOAD utility is currently operating on this table, no access is allowed until the LOAD is finished. 6 Materialized query tables that reference a nickname cannot be refreshed in DB2 Enterprise Server Edition. 7 The table is in the reorg pending state. This can occur after an ALTER TABLE statement containing a REORG-recommended operation. 8 The table is in the alter pending state. This can occur when using the table in the same unit of work as an ALTER TABLE statement containing a REORG-recommended operation. 9 The table is in Redistribute Pending state. The REDISTRIBUTE utility is not completed on this table, no access is allowed until the REDISTRIBUTE is finished. 10 The table is the source table for an ongoing ADMIN_MOVE_TABLE operation. The attempted operation is restricted until the move is completed or canceled. 11 The table, which has a nonpartitioned index, has a new data partition that the operation is attempting to access in the same transaction as the add or attach operation that created the partition, but the transaction does not have the table locked in exclusive mode.
|
User response: 1 Execute the SET INTEGRITY statement with the IMMEDIATE CHECKED option on table "" to bring the table out of the Set Integrity Pending No Access state. For a user maintained materialized query table, execute the statement with the IMMEDIATE UNCHECKED option instead of the IMMEDIATE CHECKED option. 2 Execute REFRESH TABLE statement on the dependent immediate materialized query tables and staging tables of table "". The contents of these dependent immediate materialized query tables and staging tables can be incrementally maintained from the appended data of "" through previous LOAD INSERT operations and from the attached data of "" through previous ALTER TABLE statements with the ATTACH clause. 3 Restart or terminate the previously failed LOAD operation on this table by issuing LOAD with the RESTART or TERMINATE option respectively. 4 If the Read Access state was due to a LOAD, issue the LOAD QUERY command to check whether the table is in the process of being loaded. If yes, wait until the LOAD utility has completed, or if necessary, restart or terminate previously failed LOAD operation. If LOAD is currently not in progress, issue the SET INTEGRITY statement with the IMMEDIATE CHECKED option, to validate constraints in the newly loaded portion of the table. If the Read Access state was due to turning off integrity checking, issue the SET INTEGRITY statement with the IMMEDIATE CHECKED option. 5 Wait until the current LOAD operation has finished. You can use the LOAD QUERY command to monitor the progress of load. 6 Define a materialized query table using the MAINTAIN BY USER option. Then, use an INSERT statement with a subquery to populate the materialized query table. 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 8 Complete the unit of work, and re-issue the command. 9 If the REDISTRIBUTE utility is working, wait until it finishes working on the current table. You can use the LIST UTILITIES command to monitor the progress of the REDISTRIBUTE utility. If a previous REDISTRIBUTE operation failed and left the table in this state, issue the REDISTRIBUTE utility again with the CONTINUE or ABORT option and let it finish on this table. 10 Complete or cancel the table move operation and reissue the command. You can query the SYSTOOLS.ADMIN_MOVE_TABLE table for information about the status of the move operation. 11 Modify the application so that transactions that include add or attach partition operations are committed prior to the application accessing the new partitions where the target table has a nonpartitioned index. Alternatively, modify the application so that a table that has a nonpartitioned index and that is affected by the online add or attach operation is exclusively locked when a new partition is being accessed in the same transaction as the add or attach operation that created the partition. sqlcode: -668 sqlstate: 57007 Related information: ALTER TABLE statement Adding data partitions to partitioned tables Attaching data partitions Guidelines and restrictions on altering partitioned tables
|
查看reason code 1的解释:
1
The table is in the Set Integrity Pending No Access state. The
integrity of the table is not enforced and the content of the
table may be invalid. An operation on a parent table or an
underlying table that is not in the Set Integrity Pending No
Access state may also receive this error if a dependent table
is in the Set Integrity Pending No Access state.
查看reason code 1的解决方案:
1 Execute the SET INTEGRITY statement with the IMMEDIATE CHECKED option on table "" to bring the table out of the Set Integrity Pending No Access state. For a user maintained materialized query table, execute the statement with the IMMEDIATE UNCHECKED option instead of the IMMEDIATE CHECKED option.
|
于是输入命令如下: [db2inst1@oc5873237464 elsa]$ db2 SET INTEGRITY FOR USER_PROFILE FOREIGN KEY IMMEDIATE UNCHECKED DB20000I The SQL command completed successfully.
|
再次browse Data某表格,没有弹窗,问题解决。
题外话:
[db2inst1@oc5873237464 elsa]$ db2 SET INTEGRITY FOR SR.POOL FOREIGN KEY IMMEDIATE UNCHECKED
SQL3601W
The statement caused one or more tables to automatically be placed
in the Set Integrity Pending state.
SQLSTATE=01586
[db2inst1@oc5873237464 elsa]$ db2 ? sql3601w
SQL3601W
The statement caused one or more tables to automatically be
placed in the Set Integrity Pending state.
Explanation:
Tables have been placed in the Set Integrity Pending state to enforce
integrity constraints or to enforce the data relationship between the
base table, the descendent immediate materialized query tables, and the
descendent staging tables. Specifically, the following statements caused
at least one of the listed tables to be placed in the Set Integrity
Pending state.
*
ALTER TABLE ... ATTACH statement forced the target table of the
ATTACH into the Set Integrity Pending state
*
ALTER TABLE ... DETACH statement forced descendent immediate
materialized query tables or descendent staging tables into the Set
Integrity Pending state
*
SET INTEGRITY statement forced descendent foreign key tables,
descendent immediate materialized or descendent staging tables into
the Set Integrity Pending state
A detached partition that is a newly created detached table is not
accessible until its detached dependent materialized query tables and
staging tables are maintained.
User response:
To validate the integrity for tables in the Set Integrity Pending state,
execute the SET INTEGRITY statement with the IMMEDIATE CHECKED or
IMMEDIATE UNCHECKED option for these tables. To determine which tables
are in the Set Integrity Pending state, issue the following query:
SELECT TABSCHEMA, TABNAME, STATUS
FROM SYSCAT.TABLES
WHERE STATUS = 'C'
To determine which detached tables have been created by the ALTER TABLE
... DETACH statement and are not yet accessible, issue the following
query:
SELECT TABSCHEMA, TABNAME, TYPE
FROM SYSCAT.TABLES
WHERE TYPE = 'L'
sqlcode: +3601
sqlstate: 01586