DB2异常：SQLCODE=-668

在使用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