You receive the following error message when executing a Data Definition Language (DDL) statement: 106: ISAM error: non-exclusive access
This error can occur because other sessions are querying the same table with their isolation level set to 'dirty read'.
DDL commands change a table's structure and therefore require exclusive access to the table. However, this cannot be achieved if there are sessions currently accessing the table.
NOTE: Using the SET LOCK MODE command will not prevent this error from occurring.
The following products and operating systems are required in order to use the IFX_DIRTY_WAIT environment variable:
IBM Informix® Dynamic Server™
Resolving The Problem
There is an environment variable IFX_DIRTY_WAIT that is used to define to the number of seconds a DDL statement will wait for existing dirty readers to finish their access to the target table. When set, the variable also prevents new dirty readers from accessing the table.
The variable can be set in the Informix® Dynamic Server™ environment (before the database is started) or in the client environment. Setting it on the client side will override the setting on the server side.
- is a positive integer representing the number of seconds that your session will wait for a given dirty reader to finish accessing the target table. Ifthis amount of time is not enough time, the session returns the same error as it would without the variable being set.
- Using the UNIX Korn shell and setting the timeout value to 300 seconds, you would set the environment variable as follows:
- $ export IFX_DIRTY_WAIT=300
16 June 2018