CODEWITHSUNDEEP
informix
Liju Mathew
Liju Mathew

Reputation: 899

Informix: create table <table name> as select * from <old table> locked the DB, how to unlock it?

I was doing some R&D on table field alterations. So, I needed a clone of an table.

I ran the command "create table <table name> as select * from <old table>" and it worked.

However, when I ran the second time, I cancelled the command in between and after that I am getting the below error.

$ select count(*) from my_table_copy;
SQL -211: Cannot read system catalog (systables).
ISAM -154: ISAM error: Lock Timeout Expired
SQLSTATE: IX000 at /dev/stdin:1

When I tried to fetch the DB through Open Admin, there also am getting the error:

256 : Database query failed: -

Error: -244 [Informix][Informix ODBC Driver][Informix]Could not do a physical-order read to fetch next row. sqlerrm(systables) (SQLExecute[-244] at

How to resolve this?

Thanks,

Upvotes: 0

Views: 955

Answers (1)

Alexandre Marini
Alexandre Marini

Reputation: 230

You must be getting these lock errors, because engine is rolling back your clone table transaction.

Check with "onstat -x" if there is a transaction with an R on the flags column. The est. rb_time column shows an estimate of recovery complete process.

My suggestion? If you don't need exactly the same actual data on the new table, you can put a "SET ISOLATION TO DIRTY READ;" right before your create table command.

Upvotes: 2

Related Questions