Failing update table in db2 with SQLCODE: -668, SQLSTATE: 57016, SQLERRMC: 7;

Question

I am using db2 9.5 i have created a column in table which is created successfully but i am not able to update table column and getting following error

[Error] Script lines: 1-1 -------------------------- DB2 SQL error: SQLCODE: -668, SQLSTATE: 57016, SQLERRMC: 7;DB2ADMIN.XCATENTRYEXT Message: Operation not allowed for reason code "7" on table "DB2ADMIN.XCATENTRYEXT".

Following the some blog/sites on google i found the REORG command as solution as mentioned in following link http://bytes.com/topic/db2/answers/508869-reorg-tablespace

i have tried the following queries to run on database to solve the problem.

Database["DB2"].ExecuteNonQuery("call SYSPROC.ADMIN_CMD ('REORG TABLE DB2ADMIN.XCATENTRYEXT index CATENTRY_ID INPLACE')")

REORG TABLE DB2ADMIN.XCATENTRYEXT index CATENTRY_ID INPLACE

REORG TABLE DB2ADMIN.XCATENTRYEXT 
REORG INDEXES I0000908 FOR TABLE DB2ADMIN.XCATENTRYEXT

but all queries have the same error in result like

 DB2 SQL error: SQLCODE: -104, SQLSTATE: 42601, SQLERRMC: Database;BEGIN-OF-STATEMENT;<variable_set>
 Message: An unexpected token "Database" was found following "BEGIN-OF-STATEMENT".  Expected tokens may include:  "<variable_set>".

I am stuck on this error, I am not even able to update any column of that particular table.

Mulch

Answer 1

It follows from the error message, that you somehow submit the entire string Database["DB2"].ExecuteNonQuery("call SYSPROC.ADMIN_CMD ('REORG TABLE DB2ADMIN.XCATENTRYEXT index CATENTRY_ID INPLACE')") as a SQL statement, which obviously is incorrect.

Simply issue these on the shell command line:

db2 connect to <your database name here>
db2 REORG TABLE DB2ADMIN.XCATENTRYEXT

Turncoat

Thank you so much issue resolved when i run this command on the shell. I was running this command with client so it was throwing error. – Mulch

Answer 2

It is possible to do REORG through an SQL statement:

CALL SYSPROC.ADMIN_CMD('REORG TABLE SCHEMA.TABLENAME');

Aquarelle

To check if table needs reorg in DB2, execute: select reorg_pending from sysibmadm.admintabinfo where tabname='YourTableNameHere'; if the result is Y that mean you have to excute @Aquarelle statment. – Liberati

Answer 3

If you are using tool like dbeaver , you can go to Schema --> table name --> right click --> select tools and you should see option for reorg table

Submit