- 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
Administrating and Developing with Informix
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:
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
Hi all, another PSA today.
Still this one is pretty cool, because the lab will be talking about application development with Data Studio and Informix. Even if you don't use Data Studio, and even if you don't plan to, I highly recommend attending. The more interest a call like this generates, the more calls like this (i.e. Application Development) will occur.
So the new version of Informix is fast approaching, and IBM has a webcast on that very topic. Below you will find a summary, and the link to sign up.