IBM Support

Error 106: ISAM error: non-exclusive access when executing a DDL statement

Technote (troubleshooting)


Problem(Abstract)

You receive the following error message when executing a Data Definition Language (DDL) statement:


106: ISAM error: non-exclusive access

Cause


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.


Environment

The following products and operating systems are required in order to use the IFX_DIRTY_WAIT environment variable:

Product Name
Product Version(s)
Hardware Vendor
Operating System
IBM Informix® Dynamic Server™
  • 7.31.xD5 and higher
  • 9.30.xC3 and higher
  • 9.40.xC1 and higher
  • 10.00.xC1 and higher
  • 11.10.xC1 and higher
  • 11.50.xC1 and higher
  • 11.70.xC1 and higher
  • All
    Unix/Windows
    x can be U, H, or F depending on your operating system.

    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.


    Example:

      IFX_DIRTY_WAIT= n
        n
          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.
      Example:

        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

    Related information

    SQL Syntax Guide: Concurrent Access to Tables with Excl
    SQL Syntax Guide: Truncate statement (Table Specificati

    Document information

    More support for: Informix Servers

    Software version: 7.3, 9.4, 10.0, 11.1, 11.5, 11.70

    Operating system(s): AIX, DYNIX/ptx, HP-UX, IRIX, Linux, Platform Independent, Reliant UNIX, Solaris, Windows

    Reference #: 1174239

    Modified date: 26 June 2014