The open cursor problem (106)

You may have already encountered a strange problem when trying to alter a table. Even though you have explicitly locked a table in exclusive mode, you are not able to alter it. The following example demonstrates this issue:

Listing 8. Non-exclusive access on a table

Output from dbaccess -e stores_demo <script.sql>: -------------------------------------------------- begin; Started transaction. lock table customer in exclusive mode; Table locked. alter table customer add (mycol integer); 242: Could not open database table (informix.customer). 106: ISAM error: non-exclusive access.

This behavior occurs because a select cursor was opened on table customer by someone else. The cursor is not placing any locks on individual rows; otherwise we would have not been able to lock the table exclusively, but it prevents IDS from changing the partition information.

To solve the problem, identify the session that opened a cursor on table customer:

1. Determine the hexadecimal partition number of table customer:
   • Select hex(partnum) from systables where tabname = "customer".
2. If the partition number of this table is zero, it is a fragmented table. You need to execute the following SQL statement ito find the partition numbers of the individual fragments:
   

   Select st.tabname, dbinfo("dbspace", sf.partn), hex(sf.partn) from systables st, sysfragments sf, where st.tabid = sf.tabid and sf.fragtype = "T"and st.tabname = "customer".

3. Take the hexadecimal partition number and search for it in all currently opened tables: 
   

   onstat -g opn | grep -i <hex_partnum>

4. Take the rstcb column, which is a shared memory address of the respective user thread, and search for it using theonstat -u command.
   

   onstat -u | grep <rstcb_without_leading_0x>

After you identify the respective database session, you can terminate it with the onmode -z <sessid> command.

If you're running on IDS version 7.31.xD5, 9.40 or 10, you can also take advantage of the environment variable IFX_DIRTY_WAIT. This environment variable can be set in the engine or in the client environment. IFX_DIRTY_WAIT specifies the number of seconds that a DDL statement will wait for existing dirty readers to finish accessing the table that is about to be altered. If the specified number of seconds has expired, IDS returns the same error to the application that would be sent if the IFX_DIRTY_WAIT variable was not set.

http://www.ibm.com/developerworks/data/library/techarticle/dm-0701herber/index.html

http://www-01.ibm.com/support/docview.wss?uid=swg21174239