I am getting this error when I ran:
alter table tablename add column columnname varchar(1) default 'N';
DB2 SQL Error: SQLCODE=-911, SQLSTATE=40001, SQLERRMC=68
How to solve it?
Add a comment
|
4 Answers
The alter statement wants to get an X lock on this row in SYSIBM.SYSTABLES. There is an open transaction that has this row/index value in an incompatible lock state. This lock that caused the timeout could even be from an open cursor that reads this row with an RS or RR isolation level. Terminate any other SQL currently trying to query SYSTABLES and any utilities that may be trying to update SYSTABLES like reorg and runstats then try the alter again.
1 Comment
Bill Peck
Run "db2 get snapshot for locks on <db name>" and look for locks on the SYSTABLES table. The locks in the output are organized by the application so it should be easy to spot which application needs to be interrupted or terminated so that your alter statement will run.
See DB2 Info center (I picked the one for DB2 10, most likely this error code is the same in other versions, but doublecheck!).
Seems there is a transaction open on your table, that prevents your alter command from execution.
2 Comments
Naga
how do I check which transaction is open in DB2? I am very new to any DB kind of work. Please advice
david a.
Looking around Stackoverflow, it seems to me this answer: stackoverflow.com/questions/7957887/… might help you (I coudln't put together more myself anyway).
after you have Altered a table you need to Reorg: reade up on it here:
Comments
Run the runstats script, which is a DB2 script, at regular intervals and set the script to gather RUNSTATS WITH DISTRIBUTION AND DETAILED INDEXES ALL.
In addition to running the runstats scripts regularly, you can perform the following tasks to avoid the problem: Use REOPT ONCE or REOPT ALWAYS with the command-line interface (CLI ) packages to change the query optimization behavior. In the DB2 database, change the table to make it volatile. Volatile tables indicate to the DB2 optimizer that the table cardinality can change significantly at run time (from empty to large and vice versa). Therefore, DB2 uses an index to access a table rather than a table scan.
