SQL Statements
SET AUTOFREE
The SET AUTOFREE statement enables the AUTOFREE feature for cursors in an INFORMIX-ESQL/C application.
Syntax
Usage
The Automatic-FREE feature (AUTOFREE) is one of the ESQL/C optimization features that can minimize network traffic when an ESQL/C application fetches rows from a database server. When the AUTOFREE feature is enabled, ESQL/C saves a round trip of message requests because it does not need to send the FREE statement to the database server for execution. Instead, the database server automatically frees a cursor when it closes this cursor. If this cursor has an associated prepared statement, the database server also frees the prepared statement.
The SET AUTOFREE statement allows an ESQL/C application to:
Use the ENABLED option of the SET AUTOFREE statement.
Use the DISABLED option of the SET AUTOFREE statement.
If you do not specify either option, the default is ENABLED. The following SET AUTOFREE statement enables the AUTOFREE feature for all cursors in the application:
ENABLED Option
The ENABLED option of the SET AUTOFREE statement enables the AUTOFREE feature within the ESQL/C application. You can use the SET AUTOFREE statement in two modes:
This mode affects all cursors that are declared or opened after this SET AUTOFREE statement executes. The upper part of the syntax diagram represents the global-AUTOFREE mode.
This mode affects a particular cursor that is prepared or opened after this SET AUTOFREE statement executes. The lower part of the syntax diagram represents the cursor-AUTOFREE mode.
When you execute the SET AUTOFREE statement in either of these modes, the AUTOFREE feature only takes affect on a cursor if that cursor is declared or opened after this SET AUTOFREE statement executes.
Once you enable the AUTOFREE feature on a cursor, you cannot open the cursor a second time; the database server automatically frees the cursor when it closes it the first time. For more information, see "Implicit Closing of Cursors".
Using Global-AUTOFREE Mode
In global-AUTOFREE mode, the ENABLED option of SET AUTOFREE statement enables the AUTOFREE feature for all cursors that are subsequently declared or opened in the program. After the database server closes a cursor, it automatically frees this cursor only if the cursor has been declared or opened after this SET AUTOFREE statement executes.
The following SET AUTOFREE statement enables the AUTOFREE feature for all cursors that are subsequently declared or opened in the application:
If you omit the ENABLED or DISABLED option in the SET AUTOFREE statement, the AUTOFREE feature is enabled for all subsequent cursors by default. The following SET AUTOFREE statement also enables the AUTOFREE feature for all subsequently declared or opened cursors:
The following code fragment shows how the ENABLED option of the SET AUTOFREE statement automatic frees memory for all subsequent cursors:
In the preceding code fragment, the SET AUTOFREE statement enables the AUTOFREE feature for the following cursors:
However, this SET AUTOFREE statement does not enable the AUTOFREE feature for the curs1 cursor because this cursor is neither declared nor opened after the SET AUTOFREE statement executes.
Using Cursor-AUTOFREE Mode
In cursor-AUTOFREE mode, the ENABLED option of the SET AUTOFREE statement enables the AUTOFREE feature for only the cursor that you specify after the FOR keyword. After the database server closes the specified cursor, it automatically frees this cursor only if the cursor has been declared or opened after this SET AUTOFREE statement executes. You can specify the cursor by its cursor identifier or by a host variable that contains the cursor identifier.
The following SET AUTOFREE statement enables the AUTOFREE feature for the x1 cursor if it is subsequently declared or opened in the application:
If you omit the ENABLED or DISABLED option in the SET AUTOFREE statement, the AUTOFREE feature is enabled for the specified cursor by default. The following SET AUTOFREE statement also enables the AUTOFREE feature for the x1 cursor:
In the following code fragment, the SET AUTOFREE statement enables the AUTOFREE feature for the cursor named curs3:
In the preceding code fragment, the SET AUTOFREE statement enables the AUTOFREE feature only for the curs3 cursor. Even though the curs2 cursor is opened after this SET AUTOFREE executes, this cursor is not AUTOFREE-enabled because the SET AUTOFREE statement has specified only the curs3 cursor.
DISABLED Option
The DISABLED option of the SET AUTOFREE statement disables the AUTOFREE feature within the ESQL/C application. This option works with both modes of the SET AUTOFREE statement:
This mode affects all cursors that are declared or opened after this SET AUTOFREE statement executes. The upper part of the syntax diagram represents the global-AUTOFREE mode.
This mode affects a particular cursor that is prepared or opened after this SET AUTOFREE statement executes. The lower part of the syntax diagram represents the cursor-AUTOFREE mode.
Using Global-AUTOFREE Mode
In global-AUTOFREE mode, the DISABLED option of SET AUTOFREE statement disables the AUTOFREE feature for all cursors that are subsequently declared or opened in the program. However, the SET AUTOFREE DISABLED statement does not disable the AUTOFREE feature for any cursor that has already been opened.
The following example shows how to use the DISABLED option to disable automatic freeing of memory for all subsequent cursors:
Using Cursor-AUTOFREE Mode
In cursor-AUTOFREE mode, the DISABLED option of the SET AUTOFREE statement disables the AUTOFREE feature for only the cursor that you specify after the FOR keyword. You can specify the cursor by its cursor identifier or by a host variable that contains the cursor identifier.
When you specify the DISABLED option for a specific cursor, the database server automatically frees that cursor only. The following SET AUTOFREE statement disables the AUTOFREE feature for a cursor named x1:
One advantage of cursor-AUTOFREE mode is that you can use it to override a global setting for all cursors. For example, if you issue a SET AUTOFREE ENABLED statement to enable the AUTOFREE feature for all cursors in a program, you can issue a subsequent SET AUTOFREE DISABLED FOR statement to disable the AUTOFREE feature for a particular cursor.
In the following example, the first statement enables the AUTOFREE feature for all cursors, while the second statement disables the AUTOFREE feature for the particular cursor named x1:
Using the IFX_AUTOFREE Environment Variable
You can also enable or disable the AUTOFREE feature with the IFX_AUTOFREE environment variable, as follows:
If you do not set the IFX_AUTOFREE environment variable, the AUTOFREE feature is disabled. However, in each thread, a SET AUTOFREE statement overrides the value of the IFX_AUTOFREE environment variable.
The IFX_AUTOFREE environment variable works only with client applications such as those written in INFORMIX-ESQL/C. This environment variable has no effect on Informix database utilities such as DB-Access, dbload, dbimport, dbexport, and dbschema.
For more information on the IFX_AUTOFREE environment variable, see the Informix Guide to SQL: Syntax.
Implicit Closing of Cursors
If you do not close the cursor explicitly, and then you open it again, the database server implicitly closes the cursor before it can reopen it. If the cursor has the AUTOFREE feature enabled, this implicit close of the cursor triggers the AUTOFREE feature. The second open of the cursor generates an error message (cursor not found) because the database server has already freed the cursor.
References
See the DECLARE, OPEN, FETCH, CLOSE, FREE, and PREPARE statements in this manual. For another ESQL/C optimization, see the SET DEFERRED_PREPARE statement.
In the INFORMIX-ESQL/C Programmer's Manual, see the chapter on how to use dynamic SQL for information on the AUTOFREE feature.
SET CONNECTION
The SET CONNECTION statement reestablishes a connection between an application and a database environment and makes the connection current. You can also use the SET CONNECTION statement with the DORMANT option to put the current connection in a dormant state.
Syntax
Usage
You can use the SET CONNECTION statement to change the state of a connection in the following ways:
Making a Dormant Connection the Current Connection
The SET CONNECTION statement, with no DORMANT option, makes the specified dormant connection the current one. The connection that the application specifies must be dormant. The connection that is current when the statement executes becomes dormant. A dormant connection is a connection that has been established but is not current.
The SET CONNECTION statement in the following example makes connection con1 the current connection and makes con2 a dormant connection:
A dormant connection has a connection context associated with it. When an application makes a dormant connection current, it reestablishes that connection to a database environment and restores its connection context. (For more information on connection context, see page 1-103.) Reestablishing a connection is comparable to establishing the initial connection, except that it typically avoids authenticating the user's permissions again, and it saves reallocating resources associated with the initial connection. For example, the application does not need to reprepare any statements that have previously been prepared in the connection nor does it need to redeclare any cursors.
Making a Current Connection Dormant
The SET CONNECTION statement with the DORMANT option makes the specified current connection a dormant connection. For example, the following SET CONNECTION statement makes connection con1 dormant:
The SET CONNECTION statement with the DORMANT option generates an error if you specify a connection that is already dormant. For example, if connection con1 is current and connection con2 is dormant, the following SET CONNECTION statement returns an error message:
However, the following SET CONNECTION statement executes successfully:
Dormant Connections in a Single-Threaded Environment
In a single-threaded application (an ESQL/C application that does not use threads), the DORMANT option makes the current connection dormant. The availability of the DORMANT option in single-threaded applications makes single-threaded ESQL/C applications upwardly compatible with thread-safe ESQL/C applications.
Dormant Connections in a Thread-Safe ESQL/C Environment
As in a single-threaded application, a thread-safe ESQL/C application (an ESQL/C application that uses threads) can establish many connections to one or more databases. However, in the single-threaded environment, only one connection can be active while the program executes. In the thread-safe environment, there can be many threads (concurrent pieces of work performing particular tasks) in one ESQL/C application, and each thread can have one active connection.
An active connection is associated with a particular thread. Two threads cannot share the same active connection. Once a thread makes an active connection dormant, that connection is available to other threads. A dormant connection is still established but is not currently associated with any thread. For example, if the connection named con1 is active in the thread named thread_1, the thread named thread_2 cannot make connection con1 its active connection until thread_1 has made connection con1 dormant.
In a thread-safe ESQL/C application, the DORMANT option makes an active connection dormant. Another thread can now use the connection by issuing the SET CONNECTION statement without the DORMANT option.
The following code fragment from a thread-safe ESQL/C program shows how a particular thread within a thread-safe application makes a connection active, performs work on a table through this connection, and then makes the connection dormant so that other threads can use the connection:
If a connection to a database environment is initiated with the WITH CONCURRENT TRANSACTION clause of the CONNECT statement, an ongoing transaction can used by any thread that subsequently connects to that database environment. In addition, if an open cursor is associated with such a connection, the cursor remains open when the connection is made dormant. Threads within a thread-safe ESQL/C application can use the same cursor by making the associated connection current even though only one thread can use the connection at any given time.
For a detailed discussion of thread-safe ESQL/C applications and the use of the SET CONNECTION statement in these applications, see the INFORMIX-ESQL/C Programmer's Manual. 
Identifying the Connection
If the application did not use connection name in the initial CONNECT statement, you must use a database environment (such as a database name or a database pathname) as the connection name. For example, the following SET CONNECTION statement uses a database environment for the connection name because the CONNECT statement does not use connection name. For information about quoted strings that contain a database environment, see "Database Environment".
If a connection to a database server was assigned a connection name, however, you must use the connection name to reconnect to the database server. An error is returned if you use a database environment rather than the connection name when a connection name exists.
DEFAULT Option
Use the DEFAULT option to identify the default connection for a SET CONNECTION statement. The default connection is one of the following connections:
You can use SET CONNECTION without a DORMANT option to reestablish the default connection or with the DORMANT option to make the default connection dormant. See "DEFAULT Option" and "Implicit Connection with DATABASE Statements" for more information.
CURRENT Keyword
Use the CURRENT keyword with the DORMANT option of the SET CONNECTION statement as a shorthand form of identifying the current connection. The CURRENT keyword replaces the current connection name. If the current connection is con1, the following two statements are equivalent:
When a Transaction is Active
When you issue a SET CONNECTION statement without the DORMANT option, the SET CONNECTION statement implicitly puts the current connection in the dormant state. When you issue a SET CONNECTION statement (with the DORMANT option), the SET CONNECTION statement explicitly puts the current connection in the dormant state. In either case, the statement can fail if a connection that becomes dormant has an uncommitted transaction.
If the connection that becomes dormant has an uncommitted transaction, the following conditions apply:
When Current Connection Is to INFORMIX-OnLine Dynamic Server Prior to Version 6.0
If the current connection is to a version of the OnLine database server prior to 6.0, the following conditions apply when a SET CONNECTION statement with or without the DORMANT option executes:
References
See the CONNECT, DISCONNECT, and DATABASE statements in this manual.
In the INFORMIX-ESQL/C Programmer's Manual, see the discussions of the SET CONNECTION statement and thread-safe applications.
SET DATASKIP
The SET DATASKIP statement allows you to control whether Universal Server skips a dbspace that is unavailable (for example, due to a media failure) in the course of processing a transaction.
Syntax
Usage
Use the SET DATASKIP statement to instruct the database server to skip a dbspace that is unavailable during the course of processing a transaction.
You receive a warning if a dbspace is skipped. The warning flag sqlca.sqlwarn.sqlwarn6 is set to W if a dbspace is skipped. For more information about this topic, see the INFORMIX-ESQL/C Programmer's Manual.
When you SET DATASKIP ON without specifying a dbspace, you are telling the database server to skip any dbspaces in the fragmentation list that are unavailable. You can use the onstat -d or -D utility to determine if a dbspace is down.
When you SET DATASKIP ON dbspace, you are telling the database server to skip the specified dbspace if it is unavailable.
Use the SET DATASKIP OFF statement to turn off the dataskip feature.
When the setting is DEFAULT, the database server uses the setting for the dataskip feature from the ONCONFIG file. The Universal Server administrator can change the setting of the dataskip feature at runtime. See the INFORMIX-Universal Server Administrator's Guide for more information.
Under What Circumstances Is a Dbspace Skipped?
The database server skips a dbspace when SET DATASKIP is set to ON and the dbspace is unavailable. The database server cannot skip a dbspace under certain conditions. The following list outlines those conditions:
When you want to delete a parent row, the child rows must also be available for deletion. The child rows must exist in an available fragment.
When you want to insert a new child table, the parent table must be found in the available fragments.
When you perform an update that moves a record from one fragment to another, both fragments must be available.
When you try to insert records in a expression-based fragmentation strategy and the dbspace is unavailable, an error is returned. When you try to insert records in a round-robin fragment-based strategy, and a dbspace is down, the database server inserts the rows in any available dbspace. When no dbspace is available, an error is returned.
When you perform updates that affect the index, such as when you insert or delete records, or when you update an indexed field, the index must be available.
When you try to create an index, the dbspace you want to use must be available.
The first fragment is used to store the current serial-key value internally. This is not visible to you except when the first fragment becomes unavailable and a new serial key value is required, which happens during insert statements.
References
For additional information about how to set the dataskip feature in the ONCONFIG file and how to use the onspaces utility, see the INFORMIX-Universal Server Administrator's Guide.
SET DEBUG FILE TO
Use the SET DEBUG FILE TO statement to name the file that is to hold the run-time trace output of a stored procedure.
Syntax
Usage
This statement indicates that the output of the TRACE statement in the stored procedure goes to the file that filename indicates. Each time the TRACE statement is executed, the trace data is added to this output file.
Using the WITH APPEND Option
The output file that you specify in the SET DEBUG TO file statement can be a new file or existing file.
If you specify an existing file, the current contents of the file are purged when you issue the SET DEBUG TO FILE statement. The first execution of a TRACE command sends trace output to the beginning of the file.
However, if you include the WITH APPEND option, the current contents of the file are preserved when you issue the SET DEBUG TO FILE statement. The first execution of a TRACE command adds trace output to the end of the file.
If you specify a new file in the SET DEBUG TO FILE statement, it makes no difference whether you include the WITH APPEND option. The first execution of a TRACE command sends trace output to the beginning of the new file whether you include or omit the WITH APPEND option.
Closing the Output File
To close the file that the SET DEBUG FILE TO statement opened, issue another SET DEBUG FILE TO statement with another filename. You can then edit the contents of the first file.
Redirecting Trace Output
You can use the SET DEBUG FILE TO statement outside a procedure to direct the trace output of the procedure to a file. You also can use this statement inside a procedure to redirect its own output.
Location of the Output File
If you invoke a SET DEBUG FILE TO statement with a simple filename on a local database, the output file is located in your current directory. If your current database is on a remote database server, the output file is located in your home directory on the remote database server. If you provide a full pathname for the debug file, the file is placed in the directory and file that you specify on the remote database server. If you do not have write permissions in the directory, you get an error.
Example of the SET DEBUG FILE TO Statement
The following example sends the output of the SET DEBUG FILE TO statement to a file called debugging.out:
References
See the TRACE statement in this manual.
In the Informix Guide to SQL: Tutorial, see the discussion of stored procedures in Chapter 14.
SET DEFERRED_PREPARE
The SET DEFERRED_PREPARE statement enables or disables the Deferred-PREPARE feature for cursors in an INFORMIX-ESQL/C application program.
Syntax
Usage
The Deferred-PREPARE feature is one of the ESQL/C optimization features that can minimize network traffic when an ESQL/C application receives rows from a database server. When the Deferred-PREPARE feature is enabled, ESQL/C saves a round trip of message requests because it does not need to send the PREPARE statement separately to the database server for execution. Instead, ESQL/C sends the PREPARE and OPEN statements to the database server at the same time.
The SET DEFERRED_PREPARE statement allows an ESQL/C application to:
Use the ENABLED option of the SET DEFERRED_PREPARE statement.
Use the DISABLED option of the SET DEFERRED_PREPARE statement.
If you do not specify either option, the default is ENABLED. The following SET DEFERRED_PREPARE statement enables the Deferred-PREPARE feature for all prepared statements in the application:
ENABLED Option
The ENABLED option enables the Deferred-PREPARE feature within the ESQL/C application. The following SET DEFERRED_PREPARE statement enables the Deferred-PREPARE feature:
ESQL/C automatically defers execution of any prepared statement that is prepared after this SET DEFERRED_PREPARE statement executes.
When you enable the Deferred-PREPARE feature, the application then exhibits the following behavior:
The Deferred-PREPARE feature does not defer the execution of a PREPARE statement until an EXECUTE statement. This feature is meant to work primarily with PREPARE, DECLARE, OPEN sequences that operate with the FETCH or PUT statements. If you enable the Deferred-PREPARE feature before such a PREPARE/EXECUTE sequence executes, the EXECUTE statement generates an error.
A DESCRIBE statement must execute on a prepared statement after the associated cursor has been opened with an OPEN statement. The following sequence of statements is valid: PREPARE, DECLARE, OPEN, DESCRIBE. If you enable the Deferred-PREPARE feature before such a PREPARE/DESCRIBE/OPEN sequence executes, the DESCRIBE statement generates an error.
If a prepared statement contains syntax errors, the database server does not return error messages to the application until the application has declared a cursor for the prepared statement and opened the cursor.
DISABLED Option
The DISABLED option disables the Deferred-PREPARE feature within the ESQL/C application. The following SET DEFERRED_PREPARE statement disables the Deferred-PREPARE feature:
ESQL/C atomically resumes execution of any prepared statement when the PREPARE statement after this SET DEFERRED_PREPARE statement executes. None of the application restrictions listed in "ENABLED Option" applies when the Deferred-PREPARE feature is disabled.
Using the IFX_DEFERRED_PREPARE Environment Variable
You can also enable or disable the Deferred-PREPARE feature with the IFX_DEFERRED_PREPARE environment variable, as follows:
If you do not set the IFX_DEFERRED_PREPARE environment variable, the Deferred-PREPARE feature is disabled. However, in each thread, a SET DEFERRED_PREPARE statement overrides the value of the IFX_DEFERRED_PREPARE environment variable.
The IFX_DEFERRED_PREPARE environment variable works only with client applications such as those written in INFORMIX-ESQL/C. This environment variable has no effect on Informix database utilities such as DB-Access, dbexport, dbimport, dbload, and dbschema.
For further information on the IFX_DEFERRED_PREPARE environment variable, see the Informix Guide to SQL: Syntax.
References
See the DECLARE, DESCRIBE, EXECUTE, OPEN, and PREPARE statements in this manual. For another ESQL/C optimization, see the SET AUTOFREE statement.
In the INFORMIX-ESQL/C Programmer's Manual, see the chapter on how to use dynamic SQL for information on the Deferred-PREPARE feature.
SET DESCRIPTOR
Use the SET DESCRIPTOR statement to assign values to a system-descriptor area.
Syntax
Usage
The SET DESCRIPTOR statement can be used after you have described SELECT, EXECUTE FUNCTION, and INSERT statements with the DESCRIBE...USING SQL DESCRIPTOR statement. The SET DESCRIPTOR statement can assign values to a system-descriptor area in the following instances:
If an error occurs during the assignment to any field in a system-descriptor area, the contents of all identified fields are set to 0 or null, depending on the variable type.
Using the COUNT Keyword
Use the COUNT keyword to set the number of items that are to be used in the system-descriptor area (typically the items are in a WHERE clause). If you allocate a system-descriptor area with more items than you are using, you need to set the COUNT field to the number of items that you are actually using.
The following example shows the sequence of statements in INFORMIX-ESQL/C that can be used in a program:
VALUE Clause
Use the VALUE clause to assign values from host variables into fields for a particular item in a system-descriptor area. You can assign values for items for which you are providing a description (such as parameters in a WHERE clause), or you can modify values for items after you use the DESCRIBE statement to fill the fields for a SELECT or an INSERT statement.
The item number must be greater than zero and less than the number of occurrences that were specified when you allocated the system-descriptor area with the ALLOCATE DESCRIPTOR statement.
Setting the TYPE Field
Use the following codes to set the value of TYPE for each item.
These TYPE constants are the same values that the coltype column in the syscolumns system catalog table.
For code that is easier to maintain, use the predefined constants for these SQL data types instead of their actual integer value. These constants are defined in the sqltypes.h header file. However, you cannot use the actual constant name in the SET DESCRIPTOR statement. Instead, assign the constant to an integer host variable and specify the host variable in the SET DESCRIPTOR statement.
The following example shows how you can set the TYPE field in ESQL/C:
Compiling without the -xopen option
If you do not compile using the -xopen option, the regular Informix SQL code is assigned for TYPE. You must be careful not to mix normal and X/Open modes because errors can result. For example, if a particular type is not defined under X/Open mode but is defined under normal mode, executing a SET DESCRIPTOR statement can result in an error.
Setting the TYPE field in X/Open programs
In X/Open mode, you must use the X/Open set of integer codes for the data type in the TYPE field. The following table shows the X/Open codes for data types.
If you use the ILENGTH, IDATA, or ITYPE fields in a SET DESCRIPTOR statement, a warning message appears. The warning indicates that these fields are not standard X/Open fields for a system-descriptor area.
For code that is easier to maintain, use the predefined constants for these X/Open SQL data types instead of their actual integer value. These constants are defined in the sqlxtype.h header file. However, you cannot use the actual constant name in the SET DESCRIPTOR statement. Instead, assign the constant to an integer host variable and specify the host variable in the SET DESCRIPTOR statement.
Setting the DATA Field
When you set the DATA field, you must provide the appropriate type of data (character string for CHAR or VARCHAR, integer for INTEGER, and so on).
When any value other than DATA is set, the value of DATA is undefined. You cannot set the DATA field for an item without setting TYPE for that item. If you set the TYPE field for an item to a character type, you must also set the LENGTH field. If you do not set the LENGTH field for a character item, you receive an error.
Using LENGTH or ILENGTH
If your DATA or IDATA field contains a character string, you must specify a value for LENGTH. If you specify LENGTH=0, LENGTH sets automatically to the maximum length of the string. The DATA or IDATA field can contain a 368-literal character string or a character string derived from a character variable of CHAR or VARCHAR data type. This provides a method to determine the length of a string in the DATA or IDATA field dynamically.
If a DESCRIBE statement precedes a SET DESCRIPTOR statement, DESCRIBE automatically sets LENGTH to the maximum length of the character field that is specified in your table.
This information is identical for ILENGTH. Use ILENGTH when you create a dynamic program that does not comply with the X/Open standard.
Using DECIMAL or MONEY Data Types
If you set the TYPE field for a DECIMAL or MONEY data type, and you want to use a scale or precision other than the default values, set the SCALE and PRECISION fields. You do not need to set the LENGTH field for a DECIMAL or MONEY item; the LENGTH field is set accordingly from the SCALE and PRECISION fields.
Using DATETIME or INTERVAL Data Types
If you set the TYPE field for a DATETIME or INTERVAL value, you can set the DATA field as a literal DATETIME or INTERVAL or as a character string. If you use a character string, you must set the LENGTH field to the encoded qualifier value.
To determine the encoded qualifiers for a DATETIME or INTERVAL character string, use the datetime and interval macros in the datetime.h header file.
If you set DATA to a host variable of DATETIME or INTERVAL, you do not need to set LENGTH explicitly to the encoded qualifier integer.
Setting the Indicator Fields
If you want to put a null value in the system-descriptor area, set the following item-descriptor fields:
If you set the INDICATOR field to 0 to indicate that the data is not null, you must set the DATA field.
Use the same set of constants as for the TYPE field. The constants are listed on page 1-706.
Setting Opaque-Type Fields
The following item-descriptor fields provide information about a column that has an opaque type as its data type:
This integer value must correspond to a value in the extended_id column of the sysxtdtypes system catalog table.
This character value must correspond to a value in the name column of the row with the matching extended_id value in the sysxtdtypes system catalog table.
This integer value is the length, in bytes, of the string in the EXTYPENAME field.
This character value must correspond to a value in the owner column of the row with the matching extended_id value in the sysxtdtypes system catalog table.
This integer value is the length, in bytes, of the string in the EXTYPEOWNERNAME field.
For more information on the sysxtdtypes system catalog table, see Chapter 1 of the Informix Guide to SQL: Syntax.
Setting Distinct-Type Fields
The following item-descriptor fields provide information about a column that has an distinct type as its data type:
Set this field if the source type of the distinct type is an opaque data type. This integer value must correspond to a value in the source column for the row of the sysxtdtypes system catalog table whose extended_id value matches that of the distinct type you are setting.
This value is the data-type constant for the built-in data type that is the source type for the distinct type. The codes for the SOURCETYPE field are the same as those for the TYPE field (page 1-702). This integer value must correspond to the value in the type column for the row of the sysxtdtypes system catalog table whose extended_id value matches that of the distinct type you are setting.
For more information on the sysxtdtypes system catalog table, see Chapter 1 of the Informix Guide to SQL: Syntax.
Modifying Values Set by the DESCRIBE Statement
You can use a DESCRIBE statement to modify the contents of a system-descriptor area after it is set.
After you use a DESCRIBE statement on SELECT or an INSERT statement, you must check to determine whether the TYPE field is set to either 11 or 12 to indicate a TEXT or BYTE data type. If TYPE contains an 11 or a 12, you must use the SET DESCRIPTOR statement to reset TYPE to 116, which indicates FILE type.
References
See the ALLOCATE DESCRIPTOR, DEALLOCATE DESCRIPTOR, DECLARE, DESCRIBE, EXECUTE, FETCH, GET DESCRIPTOR, OPEN, PREPARE, and PUT statements in this manual for further information about using dynamic SQL statements.
For further information about the system-descriptor area, see the INFORMIX-ESQL/C Programmer's Manual.
SET EXPLAIN
Use the SET EXPLAIN statement to obtain a measure of the work involved in performing a query.
Syntax
Usage
The SET EXPLAIN statement executes during the database server optimization phase, which occurs when you initiate a query. For queries that are associated with a cursor, if the query is prepared and does not have host variables, optimization occurs when you prepare it; otherwise, it occurs when you open the cursor.
When you issue a SET EXPLAIN ON statement, the path that the optimizer chooses for each subsequent query is written to the sqexplain.out file. The SET EXPLAIN ON statement remains in effect until you issue a SET EXPLAIN OFF statement or until the program ends. The owner name (for example, owner.customer) qualifies table names in the sqexplain.out file.
If the file already exists, subsequent output is appended to the file. If the client application and the database server are on the same computer, the sqexplain.out file is stored in your current directory.
When the current database is on another computer, the sqexplain.out file is stored in your home directory on the remote host. If you do not have a home directory on the remote host, the program stores sqexplain.out in the directory from which the database server was started.
SET EXPLAIN Output
The SET EXPLAIN output file contains a copy of the query, a plan of execution that the database-server optimizer selects, and an estimate of the amount of work. The optimizer selects a plan to provide the most efficient way to perform the query, based on such things as the presence and type of indexes and the number of rows in each table.
The optimizer uses an estimate to compare the cost of one path with another. The estimated cost does not translate directly into time. However, when data distributions are used, a query with a higher estimate generally takes longer to run than one with a smaller estimate.
The estimated cost of the query is included in the SET EXPLAIN output. In the case of a query and a subquery, two estimated cost figures are returned; the query figure also contains the subquery cost. The subquery cost is shown only so you can see the cost that is associated with the subquery.
In addition to the estimated cost, the output file contains the following information:
The optimizer chooses the best path of execution to produce the fastest possible table join using a nested-loop join or sort-merge join wherever appropriate.
The SORT SCAN section indicates that sorting the result of the preceding join or table scan is necessary for a sort-merge join. It includes a list of the columns that form the sort key. The order of the columns is the order of the sort. As with indexes, the default order is ascending. Where possible, this ordering is arranged to support any requested ORDER BY or GROUP BY clause. If the ordering can be generated from a previous sort or an index lookup, the SORT SCAN section does not appear.
The MERGE JOIN section indicates that a sort-merge join, instead of the nested-loop join, is to be used on the preceding join/table pair. It includes a list of the filters that control the sort-merge join and, where applicable, a list of any other join filters. For example, a join of tables A and B with the filters A.c1 = B.c1 and A.c2 < B.c2 lists the first join under "Merge Filters" and the second join under "Other Join Filters."
The DYNAMIC HASH JOIN section indicates that a hash join is to be used on the preceding join/table pair. It includes a list of the filters used to join the tables together.
A dynamic hash join uses one of the tables to construct a hash index and adds the index for the other table into the hash index. This is referred to as the build phase. If DYNAMIC HASH JOIN is followed by the (Build Outer) in the output, then the build phase is occurring on the first table; otherwise it occurs on the second table, preceding the DYNAMIC HASH JOIN. In the following example, the build phase occurs on table username.a:
The following output examples represent what you might see when a SET EXPLAIN ON statement is issued using INFORMIX-Universal Server.
The first two examples contain two entries for a multiple-table query and show the SORT SCAN and MERGE JOIN lines. Note that in both cases, if SORT MERGE was not chosen, the second table would have been scanned using an autoindex path. An autoindex path is an index constructed automatically at execution time by the database server. It is removed when the query completes.
The following example shows the SET EXPLAIN output for a simple query and a complex query from the customer table:
The following example shows the SET EXPLAIN output for a multiple-table query:
SET EXPLAIN Output with Fragmentation and PDQ
When the table is fragmented, the output shows which table or index is scanned. Fragments are identified with a fragment number. The fragment numbers are the same as those contained in the dbspace column in the sysfragments system catalog table. If the optimizer must scan all fragments (that is, if it is unable to eliminate any fragment from consideration), the optimizer indicates this with ALL. In addition, if the optimizer eliminates all the fragments from consideration, that is, none of the fragments contain the queried information, the optimizer indicates this with NONE. For information on how Universal Server eliminates a fragment from consideration, see the INFORMIX-Universal Server.
When PDQ is turned on, the output shows whether the optimizer used parallel scans. If the optimizer used parallel scans, the output shows PARALLEL; if PDQ is turned off, the output shows SERIAL. If PDQ is turned on, the optimizer indicates the maximum number of threads that are required to answer the query. The output shows # of Secondary Threads. This field indicates the number of threads that are required in addition to your user session thread. The total number of threads necessary is the number of secondary threads plus 1.
The output indicates when a hash join is used. The query is marked with DYNAMIC HASH JOIN, and the table on which the hash is built is marked with Build Outer.
The following example shows the SET EXPLAIN output for a table with fragmentation and PDQ priority set to low:
The following example of SET EXPLAIN output shows a table with fragmentation but without PDQ:
The following example of SET EXPLAIN output shows a table with hash join (fragmentation, and PDQ priority set to ON). The hash join is created when you create an equality join between two tables that are not indexed.
The following example of SET EXPLAIN output shows a table with fragmentation, with PDQ priority set to LOW, and an index that was selected as the search method:
Using SET EXPLAIN With SET OPTIMIZATION
If you SET OPTIMIZATION to low, the output of SET EXPLAIN displays the following uppercase string:
If you SET OPTIMIZATION to high, the output of SET EXPLAIN displays the following uppercase string:
SET EXPLAIN Output With Table Inheritance
The SET EXPLAIN statement returns information about the table inheritance that a query uses. Suppose you a super table, super_tab, and two subtables, sub_tab11 and sub_tab21. In addition, you have a subtable sub_tab22 that is derived from sub_tab21. The following SQL statements create this table inheritance:
Suppose further that you now run the following query (with SET EXPLAIN set to ON):
The following example shows the SET EXPLAIN output for this query:
Reference
In the INFORMIX-Universal Server Performance Guide, see the discussion of SET EXPLAIN and the optimizer discussion.
SET ISOLATION
Use the SET ISOLATION statement with INFORMIX-Universal Server to define the degree of concurrency among processes that attempt to access the same rows simultaneously.
The SET ISOLATION statement is an Informix extension to the ANSI SQL-92 standard. If you want to set isolation levels through an ANSI-compliant statement, use the SET TRANSACTION statement instead. See the SET TRANSACTION statement on page 1-742 for a comparison of these two statements.
Syntax
Usage
The database isolation level affects read concurrency when rows are retrieved from the database. Universal Server uses shared locks to support four levels of isolation among processes attempting to access data.
The update or delete process always acquires an exclusive lock on the row that is being modified. The level of isolation does not interfere with rows that you are updating or deleting. If another process attempts to update or delete rows that you are reading with an isolation level of Repeatable Read, that process will be denied access to those rows.
Cursors that are currently open when you execute the SET ISOLATION statement might or might not use the new isolation level when rows are later retrieved. The isolation level in effect could be any level that was set from the time the cursor was opened until the time the application actually fetches a row. The database server might have read rows into internal buffers and internal temporary tables using the isolation level that was in effect at that time. To ensure consistency and reproducible results, close open cursors before you execute the SET ISOLATION statement.
Informix Isolation Levels
The following definitions explain the critical characteristics of each isolation level, from the lowest level of isolation to the highest.
|
Isolation Level
|
Characteristics
|
|---|
Dirty Read
| Provides zero isolation. Dirty Read is appropriate for static tables that are used for queries. With a Dirty Read isolation level, a query might return a phantom row, which is an uncommitted row that was inserted or modified within a transaction that has subsequently rolled back. No other isolation level allows access to a phantom row. Dirty Read is the only isolation level available to databases that do not have transactions.
|
Committed Read
| Guarantees that every retrieved row is committed in the table at the time that the row is retrieved. Even so, no locks are acquired. After one process retrieves a row because no lock is held on the row, another process can acquire an exclusive lock on the same row and modify or delete data in the row. Committed Read is the default level of isolation in a database with logging that is not ANSI compliant.
|
Cursor Stability
| Acquires a shared lock on the selected row. Another process can also acquire a shared lock on the same row, but no process can acquire an exclusive lock to modify data in the row. When you fetch another row or close the cursor, Universal Server releases the shared lock.
If you set the isolation level to Cursor Stability, but you are not using a transaction, the Cursor Stability isolation level acts like the Committed Read isolation level. Locks are acquired when the isolation level is set to Cursor Stability outside a transaction, but they are released immediately at the end of the statement that reads the row.
|
Repeatable Read
| Acquires a shared lock on every row that is selected during the transaction. Another process can also acquire a shared lock on a selected row, but no other process can modify any selected row during your transaction. If you repeat the query during the transaction, you reread the same information. The shared locks are released only when the transaction commits or rolls back. Repeatable Read is the default isolation level in an ANSI-compliant database.
|
Default Isolation Levels
The default isolation level for a particular database is established when you create the database according to database type. The following list describes the default isolation level for each database type.
The default level remains in effect until you issue a SET ISOLATION statement. After a SET ISOLATION statement executes, the new isolation level remains in effect until one of the following events occurs:
Effects of Isolation Levels
You cannot set the database isolation level in a database that does not have logging. Every retrieval in such a database occurs as a Dirty Read.
You can issue a SET ISOLATION statement from a client computer only after a database has been opened.
The data obtained during blob retrieval can vary, depending on the database isolation level. Under Dirty Read or Committed Read levels of isolation, a process is permitted to read a blob that is either deleted (if the delete is not yet committed) or in the process of being deleted. Under these isolation levels, an application can read a deleted blob when certain conditions exist. See the INFORMIX-Universal Server Administrator's Guide for information about these conditions.
When you use DB-Access, you see more lock conflicts with higher levels of isolation. For example, if you use Cursor Stability, you see more lock conflicts than if you use Committed Read.
If you use a scroll cursor in a transaction, you can force consistency between your temporary table and the database table either by setting the isolation level to Repeatable Read or by locking the entire table during the transaction.
If you use a scroll cursor with hold in a transaction, you cannot force consistency between your temporary table and the database table. A table-level lock or locks that are set by Repeatable Read are released when the transaction is completed, but the scroll cursor with hold remains open beyond the end of the transaction. You can modify released rows as soon as the transaction ends, but the retrieved data in the temporary table might be inconsistent with the actual data.
References
See the CREATE DATABASE, SET LOCK MODE, and SET TRANSACTION statements in this manual.
In the Informix Guide to SQL: Tutorial, see the discussion of isolation levels in Chapter 7.
SET LOCK MODE
Use the SET LOCK MODE statement to define how the database server handles a process that tries to access a locked row or table.
Syntax
Usage
You can direct the response of the database server in the following ways when a process tries to access a locked row or table.
The SET LOCK MODE statement is available on computers that use kernel locking. To determine whether your computer uses kernel locking, check the directory that holds the database files. If the directory contains files with the extension .lok, your system does not use kernel locking, and the SET LOCK MODE statement is unavailable.
WAIT Keyword
The database server protects against the possibility of a deadlock when you request the WAIT option. Before the database server suspends a process, it checks whether suspending the process could create a deadlock. If the database server discovers that a deadlock could occur, it ends the operation (overruling your instruction to wait) and returns an error code. In the case of either a suspected or actual deadlock, the database server returns an error.
Cautiously use the unlimited waiting period that was created when you specify the WAIT option without seconds. If you do not specify an upper limit, and the process that placed the lock somehow fails to release it, suspended processes could wait indefinitely. Because a true deadlock situation does not exist, the database server does not take corrective action.
In a networked environment, the DBA uses the ONCONFIG parameter DEADLOCK_TIMEOUT to establish a default value for seconds. If you use a SET LOCK MODE statement to set an upper limit, your value applies only when your waiting period is shorter than the system default. The number of seconds that the process waits applies only if you acquire locks within the
current database server and a remote database server within the same transaction.
References
See the LOCK TABLE, UNLOCK TABLE, SET ISOLATION, and SET TRANSACTION statements in this manual.
In the Informix Guide to SQL: Tutorial, see the discussion of SET LOCK MODE in Chapter 7.
SET LOG
Use the SET LOG statement to change your database server logging mode from buffered transaction logging to unbuffered transaction logging or vice versa.
Syntax
Usage
You activate transaction logging when you create a database or add logging to an existing database. These transaction logs can be buffered or unbuffered.
The default condition for transaction logs is unbuffered logging. As soon as a transaction ends, the database server writes the transaction to the disk. If a system failure occurs when you are using unbuffered logging, you recover all completed transactions.
You gain a marginal increase in efficiency with buffered logging, but you incur some risk. In the event of a system failure, the database server cannot recover the completed transactions that were buffered in memory.
The SET LOG statement changes the transaction-logging mode to unbuffered logging; the SET BUFFERED LOG statement changes the mode to buffered logging.
The SET LOG statement redefines the mode for the current session only. The default mode, which the Universal Server administrator sets using ON-Monitor, remains unchanged.
The buffering option does not affect retrievals from external tables. For distributed queries, a database with logging can retrieve only from databases with logging, but it makes no difference whether the databases use buffered or unbuffered logging.
An ANSI-compliant database cannot use buffered logs. 
References
See the CREATE DATABASE and START DATABASE statements in this manual.
SET OPTIMIZATION
Use the SET OPTIMIZATION statement to specify a high or low level of database server optimization.
Syntax
Usage
You can execute a SET OPTIMIZATION statement at any time. The optimization level carries across databases but applies only within the current database server.
After a SET OPTIMIZATION statement executes, the new optimization level remains in effect until you enter another SET OPTIMIZATION statement or until the program ends.
The default database server optimization level, HIGH, remains in effect until you issue another SET OPTIMIZATION statement. The LOW option invokes a less sophisticated, but faster, optimization algorithm.
The algorithm that a SET OPTIMIZATION HIGH statement invokes is a sophisticated, cost-based strategy that examines all reasonable choices and selects the best overall alternative. For large joins, this algorithm can incur more overhead than desired. In extreme cases, you can run out of memory.
The alternative algorithm that a SET OPTIMIZATION LOW statement invokes eliminates unlikely join strategies during the early stages, which reduces the time and resources spent during optimization. However, when you specify a low level of optimization, the optimal strategy might not be selected because it was eliminated from consideration during early stages of the algorithm.
The following example shows optimization across a network. The central database (on computer 1) is to have LOW optimization; the western database (on computer 2) is to have HIGH optimization. If the western database were on the same computer as central, it would have LOW optimization.
CONNECT TO 'central';
SET OPTIMIZATION low;
SELECT catalog.*, description, unit_price, unit,
unit_descr, manu_name, lead_time
FROM catalog, stock, manufact
WHERE catalog.stock_num = stock.stock_num
AND stock.manu_code = manufact.manu_code
AND catalog_num = 10025
CLOSE DATABASE;
CONNECT TO 'western@rockie';
SET OPTIMIZATION low;
SELECT catalog.*, description, unit_price, unit,
unit_descr, manu_name, lead_time
FROM catalog, stock, manufact
WHERE catalog.stock_num = stock.stock_num
AND stock.manu_code = manufact.manu_code
AND catalog_num = 10025
Optimizing SPL Routines
In earlier Informix products, the term stored procedure was used for both SPL procedures and SPL functions. In Universal Server, the term SPL routine is used for both SPL procedures and SPL functions.
For SPL routines that remain unchanged or change only slightly, you might want to set the SET OPTIMIZATION statement to HIGH when you create the routine. This optimization level stores the best query plans for the routine. Then SET OPTIMIZATION to LOW before you execute the routine. The routine then uses the optimal query plans and runs at the more cost-effective rate.
References
In the INFORMIX-Universal Server Performance Guide, see the discussion of optimizing queries.
SET PDQPRIORITY
The SET PDQPRIORITY statement allows an application to set the query priority level dynamically within an application.
Syntax
Usage
Priority set with the SET PDQPRIORITY statement overrides the environment variable PDQPRIORITY. However, no matter what priority value you set with the SET PDQPRIORITY statement, the ONCONFIG configuration parameter MAX_PDQPRIORITY determines the actual priority value that the INFORMIX-Universal Server uses for your queries.
For example, assume that the DBA has set the MAX_PDQPRIORITY parameter to 50. A user enters the following SET PDQPRIORITY statement to set the query priority level to 80.
When it processes the user's query, Universal Server uses the value of the MAX_PDQPRIORITY parameter to factor the query priority level set by the user. Universal Server silently processes the query with a priority level of 40. This priority level represents 50 percent of the 80 percent of resources specified by the user.
Meaning of SET PDQPRIORITY Parameters
The parameters that the SET PDQPRIORITY statement can use are shown in the following table.
(1 of 2)
|
Parameter
|
Meaning
|
|---|
DEFAULT
| Uses the value that is specified in the PDQPRIORITY environment variable, if any. DEFAULT is the symbolic equivalent of -1.
|
LOW
| Signifies that data is fetched from fragmented tables in parallel. Universal Server uses no other forms of parallelism. LOW is the symbolic equivalent of 1.
|
OFF
| Indicates that PDQ is turned off. Universal Server uses no parallelism. OFF is the symbolic equivalent of 0. OFF is the default setting if you do not specify the PDQPRIORITY environment variable or the SET PDQPRIORITY statement.
|
HIGH
| Signifies that the database server determines an appropriate value to use for PDQPRIORITY. This decision is based on several things, including the number of available processors, the fragmentation of the tables being queried, the complexity of the query, and so on. Informix reserves the right to change the performance behavior of queries when HIGH is specified in future releases.
|
percent-of-resources
| Indicates a query priority level and indicates the percent of resources a database server uses in order to answer the query. Resources include the amount of memory and the number of processors. The higher the number, the more resources the database server uses. Although usually the more resources a database server uses indicates better performance for a given query, using too many resources can cause contention among the resources and remove resources from other queries, which results in degraded performance. Range = -1, 0, 1 to 100.
|
References
For information about the PDQPRIORITY environment variable, see the Informix Guide to SQL: Syntax. See the INFORMIX-Universal Server Administrator's Guide for information about the ONCONFIG parameter MAX_PDQPRIORITY.
SET ROLE
Use the SET ROLE statement to enable the privileges of a role.
Syntax
Usage
Any user who is granted a role can enable the role using the SET ROLE statement. A user can enable only one role at a time. If a user executes the SET ROLE statement after a role is already set, the new role replaces the old role.
All users are, by default, assigned the role NULL or NONE (NULL and NONE are synonymous). The roles NULL and NONE have no privileges. When you set the role to NULL or NONE, you disable the current role.
When a user sets a role, the user gains the privileges of the role, in addition to the privileges of PUBLIC and the user's own privileges. If a role is granted to another role, the user gains the privileges of both roles, in addition to those of PUBLIC and the user's own privileges. After a SET ROLE statement executes successfully, the role remains effective until the current database is closed or the user executes another SET ROLE statement. Additionally, the user, not the role, retains ownership of all the objects, such as tables, that were created during a session.
A user cannot execute the SET ROLE statement while in a transaction. If the SET ROLE statement is executed while a transaction is active, an error occurs.
If the SET ROLE statement is executed as a part of a trigger or stored procedure, and the owner of the trigger or stored procedure was granted the role with the WITH GRANT OPTION, the role is enabled even if the user is not granted the role.
The following example sets the role engineer:
The following example sets a role and then relinquishes the role after it performs a SELECT operation:
References
See the CREATE ROLE, DROP ROLE, GRANT, and REVOKE statements in this manual.
SET SESSION AUTHORIZATION
The SET SESSION AUTHORIZATION statement lets you change the user name under which database operations are performed in the current Universal Server session. This statement is enabled by the DBA privilege, which you must obtain from the DBA before the start of your current session. The new identity remains in effect in the current database until you execute another SET SESSION AUTHORIZATION statement or until you close the current database.
Syntax
Usage
The SET SESSION AUTHORIZATION statement allows a user with the DBA privilege to bypass the privileges that protect database objects. You can use this statement to gain access to a table and adopt the identity of a table owner to grant access privileges. You must obtain the DBA privilege before you start a session in which you use this statement. Otherwise, this statement returns an error.
When you use this statement, the user name to which the authorization is set must have the Connect privilege on the current database. Additionally, the DBA cannot set the authorization to PUBLIC or to any defined role in the current database.
Setting a session to another user causes a change in a user name in the current active database server. In other words, these users are, as far as this database server process is concerned, completely dispossessed of any privileges that they might have while accessing the database server through some administrative utility. Additionally, the new session user is not able to initiate an administrative operation (execute a utility, for example) by virtue of the acquired identity.
After the SET SESSION AUTHORIZATION statement successfully executes, the user must use the SET ROLE statement to assume a role granted to the current user. Any role enabled by a previous user is relinquished.
Using SET SESSION AUTHORIZATION to Obtain Privileges
You can use the SET SESSION AUTHORIZATION statement either to obtain access to the data directly or to grant the database-level or table-level privileges needed for the database operation to proceed. The following example shows how to use the SET SESSION AUTHORIZATION statement to obtain table-level privileges:
References
See the CONNECT, DATABASE, GRANT, and SET ROLE statements in this manual.
SET TRANSACTION
Use the SET TRANSACTION statement to define isolation levels and to define the access mode of a transaction (read-only or read-write).
Syntax
Usage
You can use SET TRANSACTION only in databases with logging.
You can issue a SET TRANSACTION statement from a client computer only after a database has been opened.
The database isolation level affects concurrency among processes that attempt to access the same rows simultaneously from the database. INFORMIX-Universal Server uses shared locks to support four levels of isolation among processes that are attempting to read data as the following list shows:
The update or delete process always acquires an exclusive lock on the row that is being modified. The level of isolation does not interfere with rows that you are updating or deleting; however, the access mode does affect whether you can update or delete rows. If another process attempts to update or delete rows that you are reading with an isolation level of Serializable or (ANSI) Repeatable Read, that process will be denied access to those rows.
Comparing SET TRANSACTION with SET ISOLATION
The SET TRANSACTION statement complies with ANSI SQL-92. This statement is similar to the Informix SET ISOLATION statement; however, the SET ISOLATION statement is not ANSI compliant and does not provide access modes. In fact, the isolation levels that you can set with the SET TRANSACTION statement are almost parallel to the isolation levels that you can set with the SET ISOLATION statement, as the following table shows.
Another difference between the SET TRANSACTION and SET ISOLATION statements is the behavior of the isolation levels within transactions. The SET TRANSACTION statement can be issued only once for a transaction. Any cursors that are opened during that transaction are guaranteed to get that isolation level (or access mode if you are defining an access mode). With the SET ISOLATION statement, after a transaction is started, you can change the isolation level more than once within the transaction. The following examples show the SET ISOLATION and SET TRANSACTION statements, respectively:
SET ISOLATION
SET TRANSACTION
Isolation Levels
The following definitions explain the critical characteristics of each isolation level, from the lowest level of isolation to the highest.
Default Isolation Levels
The default isolation level for a particular database is established according to database type when you create the database. The default isolation level for each database type is described in the following table.
The default isolation level remains in effect until you issue a SET TRANSACTION statement within a transaction. After a COMMIT WORK statement completes the transaction or a ROLLBACK WORK statement cancels the transaction, the isolation level is reset to the default.
Access Modes
Access modes affect read and write concurrency for rows within transactions. Use access modes to control data modification.
You can specify that a transaction is read-only or read-write through the SET TRANSACTION statement. By default, transactions are read-write. When you specify that a transaction is read-only, certain limitations apply. Read-only transactions cannot perform the following actions:
You can execute stored procedures in a read-only transaction as long as the procedure does not try to perform any restricted statement.
Effects of Isolation Levels
You cannot set the database isolation level in a database that does not have logging. Every retrieval in such a database occurs as a Read Uncommitted.
The data that is obtained during blob retrieval can vary, depending on the database isolation levels. Under Read Uncommitted or Read Committed isolation levels, a process is permitted to read a blob that is either deleted (if the delete is not yet committed) or in the process of being deleted. Under these isolation levels, an application can read a deleted blob when certain conditions exist. See the INFORMIX-Universal Server Administrator's Guide for information about these conditions.
If you use a scroll cursor in a transaction, you can force consistency between your temporary table and the database table either by setting the isolation level to Serializable or by locking the entire table during the transaction.
If you use a scroll cursor with hold in a transaction, you cannot force consistency between your temporary table and the database table. A table-level lock or locks set by Serializable are released when the transaction is completed, but the scroll cursor with hold remains open beyond the end of the transaction. You can modify released rows as soon as the transaction ends, so the retrieved data in the temporary table might be inconsistent with the actual data.
References
See the CREATE DATABASE, SET ISOLATION, and SET LOCK MODE statements in this manual.
In the Informix Guide to SQL: Tutorial, see the discussion of isolation levels and concurrency issues in Chapter 7.
|
