IBM Support

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

Troubleshooting


Problem

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

    [{"Product":{"code":"SSGU8G","label":"Informix Servers"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":"Not Applicable","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF025","label":"Platform Independent"},{"code":"PF008","label":"DYNIX\/ptx"},{"code":"PF010","label":"HP-UX"},{"code":"PF015","label":"IRIX"},{"code":"PF016","label":"Linux"},{"code":"PF026","label":"Reliant UNIX"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"}],"Version":"10.0;11.1;11.5;7.3;9.4;11.70","Edition":"","Line of Business":{"code":"","label":""}}]

    Document Information

    Modified date:
    16 June 2018

    UID

    swg21174239

    Page Feedback