Troubleshooting table states during data redistribution

Before starting a redistribution operation, ensure that all tables in the database partition group are in full access mode and are in normal state.

Symptoms

If table states cause the redistribution to fail, the error message indicates that the database partition group cannot be redistributed or that the operation is not allowed. For example, SQL02436N, SQL6056N, and SQL0668N messages can be symptoms of this problem.

Note: If the error message lists a table name, it might not be the only problematic table in the database partition group. By troubleshooting the table states for all of the tables in the database partition group, you can avoid multiple unsuccessful redistribution attempts.

Diagnosing the problem

User response:
  1. Determine which tables are in an inoperative state (SYSCAT.TABLES.STATUS='X').
    Issue the following query: 
    SELECT TABNAME 
   FROM SYSCAT.TABLES AS TABLES, SYSCAT.TABLESPACES AS TABLESPACES
   WHERE TABLES.TBSPACE = TABLESPACES.TBSPACE AND TABLES.STATUS = 'X' 
   AND TABLESPACES.DBPGNAME = 'IBMDEFAULTGROUP'
    where IBMDEFAULTGROUP is the database partition group name.
  2. Determine which tables are in the "set integrity pending" state (SYSCAT.TABLES.STATUS='C').
    Issue the following query:
    SELECT TABNAME 
   FROM SYSCAT.TABLES AS TABLES, SYSCAT.TABLESPACES AS TABLESPACES
   WHERE TABLES.TBSPACE = TABLESPACES.TBSPACE AND TABLES.STATUS = 'C' 
   AND TABLESPACES.DBPGNAME = 'IBMDEFAULTGROUP'
    where IBMDEFAULTGROUP is the database partition group name.
  3. Determine which tables are in a normal state, but are not in full access mode.
    Issue the following query:
    SELECT DISTINCT TRIM(T.OWNER) || \'.\' || TRIM(T.TABNAME)
AS NAME, T.ACCESS_MODE, A.LOAD_STATUS
FROM SYSCAT.TABLES T, SYSCAT.DBPARTITIONGROUPS N,
SYSIBMADM.ADMINTABINFO A
WHERE T.PMAP_ID = N.PMAP_ID
AND A.TABSCHEMA = T.OWNER
AND A.TABNAME = T.TABNAME
AND N.DBPGNAME = 'IBMDEFAULTGROUP'
AND (T.ACCESS_MODE <> 'F' OR A.LOAD_STATUS IS NOT NULL)
AND T.STATUS='N'
    where IBMDEFAULTGROUP is the database partition group name. If this query takes a long time to execute, terminate the query, issue the RUNSTATS command on all of the tables involved in this query, and then reissue the query.

Resolving the problem

User response:
  1. Take the required corrective action:
    1. For each table in an inoperative state, use the LOAD QUERY command to determine the specific table state.
      • For tables in "load in progress" state, wait until the load operation has finished. You can use the LOAD QUERY command to monitor the progress the load operation.
      • For tables in "load pending" state, restart or terminate the previously failed load operation by issuing the LOAD command with RESTART or TERMINATE command parameters.
      • For tables in "read access only" state, use 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 the previously failed load operation. If a load operation is currently not in progress, issue the SET INTEGRITY statement with the IMMEDIATE CHECKED option. This action validates the constraints in the newly loaded portion of the table.
      • For tables in "reorg pending" state, perform a classic reorg operation to make the table accessible.
      • For tables in "not load restartable" state, issue a LOAD TERMINATE or LOAD REPLACE command.
      • For tables in "unavailable" state, drop the table or restore it from a backup.
    2. For the tables in "set integrity pending" state, execute the SET INTEGRITY statement with the IMMEDIATE CHECKED option.
    3. For the tables that are not in full access mode, execute the SET INTEGRITY statement with the IMMEDIATE CHECKED option on the dependent immediate materialized query and staging tables.
    Note: You can also choose to defer resolving the problem and temporarily omit the tables from the redistribution operation by specifying the EXCLUDE parameter in the REDISTRIBUTE DATABASE PARTITION GROUP command. In this case, the redistribution operation completes successfully, but the database partition group is only partially redistributed. As a result, redistributed tables in the database partition group could use a different partitioning map than tables that have not been redistributed. Also, if collocation between redistributed and non-redistributed tables existed before the redistribute operation, the collocation property between these tables is temporarily disabled. Query performance might not be optimal until a full redistribution occurs.
  2. If a previous redistribution operation failed, resubmit it using either the CONTINUE or ABORT option. CONTINUE completes the previously aborted redistribution operation and ABORT undoes the effects of the previously aborted operation.