10

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.

halfer
  • 19,824
  • 17
  • 99
  • 186
WCS Developer
  • 111
  • 1
  • 1
  • 6

3 Answers3

21

It is possible to do REORG through an SQL statement:

CALL SYSPROC.ADMIN_CMD('REORG TABLE SCHEMA.TABLENAME');
Denis.Kipchakbaev
  • 970
  • 15
  • 24
  • 1
    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 @Denis.Kipchakbaev statment. – Reema Oct 03 '22 at 07:33
16

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
mustaccio
  • 18,234
  • 16
  • 48
  • 57
  • 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. – WCS Developer Dec 05 '13 at 15:39
1

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

Nikhil Kamani
  • 850
  • 9
  • 12