Check if table is locked in Informix

Question

I'm reading out installed DB's from the sysmaster table on an Informix DB via ODBC. My problem is that when the DBA are doing an DB Import that table is locked out until it's done and this can take hours. I want to handle this situation in my code by not trying to connect to that DB when this is being done.

Is there a way of reading out a table's status whether or not it's locked via an SQL query?

Edit: I'm catching ODBC exception (-2146232009) in my code to handle the locked table, but I don't really like the idea of handle this with an exception.

Solution

The sysmaster:syslocks table should contain the information you need. It is actually a view, but if it were a table, the statement might look like:

CREATE TABLE "informix".syslocks
(
    dbsname     CHAR(128),
    tabname     CHAR(128),
    rowidlk     INTEGER,
    keynum      SMALLINT,
    type        VARCHAR(4),
    owner       INTEGER,
    waiter      INTEGER
);

On a very idle system where I was poking around the sysmaster database, I ran:

SELECT * FROM SysLocks;

and got the output:

sysmaster   sysdatabases    516 0   S   31
sysmaster   sysdatabases    516 0   S   32
sysmaster   sysdatabases    516 0   S   33  
sysmaster   sysdatabases    513 0   S   37

This should give you the information you need. You'll probably need to do some experimentation to make sure you get it right (finding out exactly what locks DB-Import sets, or has set, on the database it is working on).

There is another view, the SysLockTab view, that contains more detailed, less user-oriented information. And there are whatever underlying tables that these views are built on that you could access instead. These should give you any information missing from the SysLocks view.