So I ran into an interesting issue last week. A customer couldn't alter a table. Non-Exclusive access. Sounds pretty normal right? I mean after all it's not like a DBA doesn't see this fairly often.
The Normal routine is for a DBA to run the following:
- Figure out the partnum for the table being altered, best way is to run the following SQL from the DB the table is in:
select hex(partnum) from systables where tabname="<tabname>"
Where <tabname> is whatever the table name is you are altering.
- using onstat -k |grep <partnum you got above> , find out who is holding the locks.
- contact user about locks, or if you are under a rush, just kill the offending user/session
However what happens when you still get non-exclusive access after doing the above?
You need to have two considerations then. #1, check for referential integrity issues, and #2 look for open cursors?
RI can be checked using dbaccess, or dbschema, but how do you check open cursors?
The easiest way is with
onstat -g opn
In the situation I wan into, we had several open cursors with transactions running against the child table holding RI.
This allowed us to identify where applications were forgetting to close the cursors. As soon as those cursors were closed, the alter table was successful.
From a development standpoint, this brings up a salient point to always remember, close your cursors.
For more info on onstat -g opn