Wednesday, 17 May 2017

Operation not allowed for reason code “7” on table in DB2

This issue generally occurs when we altered/modified already existing table in IBM DB2.

We have to execute below Query when we get this error.

CALL sysproc.admin_cmd('REORG TABLE TABLE_NAME');

Below are the situations when we can ReOrg the table in DB2.

  • A high volume of insert, update, and delete activity on tables accessed by queries. If many rows are inserted, there may not be enough free space to keep them in the clustered sequence. If many rows are deleted, the table will still have the space allocated and a REORG can free the unnecessary space.
  • Significant changes in the performance of queries that use an index with a high cluster ratio. Some applications access groups of rows using the clustered index and may not perform well if the table becomes unclustered.
  • Executing RUNSTATS to refresh statistical information does not improve performance.
  •    In some cases, a RUNSTATS Utility can collect the current statistics and resolve performance problems. If a table has become unclustered or if it contains a large amount of free space, the REORG Utility may be needed to improve access efficiency.
  • The REORGCHK command indicates a need to reorganize your table.
  • If Row compression is being implemented for a table, the REORG Utility can be used to build the compression dictionary and compress the data rows.
Note:  alter table "APPS"."DEPARTMENTS" alter column DEPARTMENT_ID set not null; 


No comments:

Post a Comment