I'm getting an error when trying to update a table. The SQL statement is:
UPDATE dda_accounts SET TYPE_SK = TYPE_SK - 10 WHERE TYPE_SK > 9;
The error I get is:
SQL Error [57007]: Operation not allowed for reason code "7" on table
"BANK_0002_TEST.DDA_ACCOUNTS".. SQLCODE=-668, SQLSTATE=57007, DRIVER=4.27.25
SQLSTATE 57007 says that there's something incomplete after an ALTER TABLE was executed.
I found this resolution, but it's not clear if it can be fixed or the only way to recover the table is using a backup.
Running a select statement works, only the update fails. What is the way to fix this table?
You need to REORG the table to recover, see this page for details.
When you get an error like this, lookup the SQL066N code with the reason code "7".
This shows:
The table is in the reorg pending state. This can occur after an ALTER TABLE tatement containing a REORG-recommended operation.
Be aware the the previous alter table (that put the table into this state of reorg needed) might have happened quite some time ago, possibly without your knowledge.
If you lack the authorisation to perform reorg table inplace "BANK_0002_TEST.DDA_ACCOUNTS" , then contact your DBA for assistance. The DBA may choose to also reorg indexes at the same time, and to perform runstats (docs) on the table following completion of the reorg, and to check whether anything else needs rebinding.