DB2 can reject a query against a Maximo table with this error:
SQL0668N Operation not allowed for reason code "7" on table "MAXIMO.ITEM".
SQLSTATE=57016Reason code 7 means that the table is in reorg-pending state. This commonly follows a table alteration such as dropping a column or changing a column definition. DB2 has recorded the catalog change, but it requires a classic table reorganization before every operation can use the table normally.
One query succeeding does not prove that the table is usable. IBM documents cases in which some access paths work while another query returns SQL0668N.
Plan an outage first: a classic
REORG TABLEconsumes working space, obtains locks and can restrict access to the table. Stop Maximo application servers and other processes that can use the affected tables, including integration consumers and scheduled jobs. Confirm that you have a current database backup, sufficient free space and a tested recovery plan. Run this as a DB2 administrator during a maintenance window.
Find every table in reorg-pending state
Connect to the Maximo database and query SYSIBMADM.ADMINTABINFO. Replace MAXIMO if the application uses a different schema:
SELECT
TABSCHEMA,
TABNAME,
REORG_PENDING
FROM
SYSIBMADM.ADMINTABINFO
WHERE
REORG_PENDING = 'Y'
AND TABSCHEMA = 'MAXIMO'
ORDER BY
TABSCHEMA,
TABNAME;Checking the whole schema matters after a package, customization or Database Configuration change. The table named in the first error might not be the only one waiting for a reorganization.
Do not treat every SQL0668N the same way. The reason code identifies the pending state; this procedure is specifically for reason code 7.
Reorganize one table
For the MAXIMO.ITEM example, run the classic reorganization through DB2's administrative procedure:
CALL SYSPROC.ADMIN_CMD('REORG TABLE MAXIMO.ITEM');Use the schema and table returned by the inventory query. ADMIN_CMD commits at the beginning of the utility operation, so do not run it as part of a transaction that contains unrelated work.
The account needs suitable authority, such as DBADM, SQLADM, SCHEMAADM for the schema, or CONTROL on the table. Release application locks before starting. If the command reports a lock, space or temporary-tablespace problem, fix that cause rather than repeatedly submitting the reorganization.
Generate commands for several tables
If several Maximo tables are pending, this query generates one call for each table:
SELECT
'CALL SYSPROC.ADMIN_CMD(''REORG TABLE ' || TRIM(TABSCHEMA) || '.' || TRIM(TABNAME) || ''');' AS REORGSQL
FROM
SYSIBMADM.ADMINTABINFO
WHERE
REORG_PENDING = 'Y'
AND TABSCHEMA = 'MAXIMO'
ORDER BY
TABSCHEMA,
TABNAME;This query only prints commands. Copy the results into a separate script, inspect every schema and table name, and run the calls one at a time. Do not execute generated database-maintenance SQL without reviewing it first.
Large tables can take considerable time and working space. Record the start and result of each command so a failed batch does not leave you guessing which tables completed.
Validate the recovery
Run the inventory query again. It should return no pending tables for the Maximo schema:
SELECT
TABSCHEMA,
TABNAME,
REORG_PENDING
FROM
SYSIBMADM.ADMINTABINFO
WHERE
REORG_PENDING = 'Y'
AND TABSCHEMA = 'MAXIMO'
ORDER BY
TABSCHEMA,
TABNAME;Collect current optimizer statistics for the reorganized tables using the DB2 maintenance procedure approved for the environment. IBM recommends RUNSTATS after a table reorganization. Then restart Maximo, test the operation that originally failed and review both the DB2 diagnostic log and Maximo logs.
If a table immediately returns to reorg-pending state, identify the DDL or deployment process changing it before running another reorganization. Repeatedly clearing the state only hides the unfinished database-change process.
The tested Maximo and DB2 releases are unknown. Verify the commands against your installed versions before running them.