Besides the enhancements that are described in section Extensibility Enhancements, the following additional changes to the IBM Informix dialect of the Structured Query Language (SQL) have been implemented in Version 9.4 of Dynamic Server.
The CREATE TRIGGER statement has been enhanced to support INSTEAD OF triggers on views. You can define an INSERT, UPDATE, or DELETE event on a specified view that activates the trigger. Rather than directly performing the triggering DML event, the database server executes the Action clause of the INSTEAD OF trigger. This feature provides a mechanism for updating the underlying tables of views that include columns from more than one table; such views were not updatable in earlier releases of Dynamic Server.
For more information, see the IBM Informix Guide to SQL: Syntax.
The syntax rules for the SELECT statement have been enhanced.
For more information on these features, see the IBM Informix Guide to SQL: Syntax.
The ORDER BY clause now can include column names or expressions that do not appear in the select list of the projection clause. The following query, for example, is now valid:
SELECT stock_num, manu_code FROM stock ORDER BY unit_price
Earlier releases had required that unit_price also appear in the Projection clause.
As noted in section Using an Iterator Function in the FROM Clause of a SELECT Statement, iterator functions are now valid in the FROM clause of the SELECT statement.
Functional indexes are UDRs that accept column names as arguments, and whose return values are specified as index keys in the CREATE INDEX statement. In previous Dynamic Server releases, the number of columns was restricted to no more than 16.
In Version 9.4, however, the number of columns that can be arguments to a functional index is language-dependent. For UDRs written in the C language, a functional index can have up to 102 key parts. A functional index defined in the SPL or Java languages can have up to 341 key parts.
For more information, see the IBM Informix Guide to SQL: Reference.
The DESCRIBE statement now recognizes the OUTPUT keyword. The new dynamic SQL statement, DESCRIBE INPUT, can provide information about the retrieved columns and dynamic parameters of prepared DML statements.
For more information on these features, see the IBM Informix Guide to SQL: Syntax.
The DESCRIBE statement in previous releases of Dynamic Server could not provide information about input parameters of the WHERE clause of prepared INSERT or SELECT statements. It could provide limited support for UPDATE parameters if the IFX_UPDDESC environment variable were set. In this release, you can specify the INPUT keyword in the DESCRIBE statement to return information about each input parameter of a prepared DML statement, including the data type, identifier, and length (in bytes).
The client system that executed a dynamic SQL application can use the DESCRIBE OUTPUT statement (or simply DESCRIBE, because the OUTPUT keyword is optional) to obtain information about the output parameters of a prepared DML statement. (This is a CSDK feature, but it requires information that the database server did not make available to the client application in releases earlier than Version 9.4.)
In previous Dynamic Server releases, the database server sorted NCHAR and NVARCHAR values according to the localized collating sequence of the locale that the DB_LOCALE environment variable specified, if that locale defined a COLLATION; otherwise, all sorting operations followed the code set order.
In this release, the new SET COLLATION statement can specify the localized collation of another locale. For the rest of the session (or until the next SET COLLATION statement in the same session), sorting of NCHAR and NVARCHAR values ignores the DB_LOCALE setting. You can restore the default collating order by issuing the SET NO COLLATION statement. This feature enables the database server to use different localized collating orders on NCHAR and NVARCHAR data sets within a single database, if both collating orders can operate on the same character set.
Database objects (such as indexes, check constraints, and triggers) that perform collation use the collating order that was in effect when the object was created, rather than the order that is in effect at runtime, if these two collating orders are not the same.
For more information on the SET COLLATION statement, see the IBM Informix Guide to SQL: Syntax. For more information on the DB_LOCALE environment variable, see the IBM Informix GLS User's Guide. For more information on the NCHAR and NVARCHAR data types, see the IBM Informix Guide to SQL: Reference.
The LOAD and UNLOAD statements were previously restricted on most platforms to files no larger than 2 GB for LOAD and UNLOAD flat-file I/O operations. This restriction has been relaxed to 4 TB in Version 9.4.
For more information, see the IBM Informix Guide to SQL: Syntax.
In Dynamic Server releases earlier than Version 9.4, the SET TABLE and SET INDEX statements could specify whether one or more fragments of a table or of an index remain in a shared memory buffer, rather than be written to disk. These statements are no longer supported, because this functionality is now provided automatically by the database server. No error is issued, however, when applications include a SET Residency statement; the SET TABLE or SET INDEX statement is simply ignored.
For more information, see the IBM Informix Guide to SQL: Syntax.
In a user-defined routine (UDR), an OUT parameter corresponds to a value returned through a pointer. Earlier releases of Dynamic Server supported no more than one OUT parameter in UDRs, and any OUT parameter was required to appear as the last item in the parameter list. Version 9.4 drops these restrictions, supporting multiple OUT parameters anywhere in the parameter list of the UDR. This feature provides greater flexibility in defining UDRs, and removes the need to return collection variables in contexts where multiple returned values are required. JDBC client applications can use this feature to create multiple statement-local variables (SVLs) in the WHERE clause of a DML statement that invokes the UDR.
For more information on how to use OUT parameters in UDRs, see the IBM Informix User-Defined Routines and Data Types Developer's Guide. For more information on OUT parameter syntax, see the IBM Informix Guide to SQL: Syntax.
This release introduces new DML statements (CREATE SEQUENCE, ALTER SEQUENCE, RENAME SEQUENCE, DROP SEQUENCE) for sequence generators, database objects that multiple users can access concurrently to generate unique integers in the INT8 range.
The GRANT and REVOKE statements have been enhanced to support access privileges on sequence objects, and the CREATE SYNONYM and DROP SYNONYM statements can now reference synonyms for sequence objects in the local database. Two new operators, CURRVAL and NEXTVAL, can read or increment the value of an existing synonym. The system catalog includes a new syssequences table for information about sequence objects. Sequences are an efficient way to generate primary key values.
For more information on sequence object syntax, see the IBM Informix Guide to SQL: Syntax.
The syntax of the SELECT statement has been enhanced to support the ANSI/ISO syntax for cross joins, right outer joins, and full outer joins. The keywords CROSS, RIGHT, and FULL are now supported in the context of queries that join two or more tables. This feature provides greater compliance with the ANSI standard for SQL.
For more information, see the IBM Informix Guide to SQL: Syntax.
The UNION operator is allowed in subqueries of SELECT statements. The elements of a union are SELECT statements that can recursively contain other unions.
For more information, see the IBM Informix Guide to SQL: Syntax.
In previous releases, database columns of the LVARCHAR built-in opaque data type had an upper limit of 2048 bytes. Version 9.4 supports a size parameter in the declarations of LVARCHAR columns (or LVARCHAR variables of SPL), where size can be up to 32,739 bytes.
For backward compatibility, LVARCHAR objects declared with no size parameter can store up to 2048 bytes. This feature increases the storage capacity of the varying-length data types of Dynamic Server.
For more information, see the IBM Informix Guide to SQL: Reference.
IBM Informix Dynamic Server, Version 9.4, recognizes new SQL keywords that might affect migration of your applications. Although you can use almost any word as an SQL identifier, syntactic ambiguities can occur in contexts where the keyword is also valid. An ambiguous statement might not produce the desired results.
For information about workarounds for such ambiguities, see the IBM Informix Guide to SQL: Syntax.
The following SQL keywords are new in Dynamic Server, Version 9.4:
| COLLATION | FULL | RESTART |
| CROSS | INSTEAD | RIGHT |
If you are migrating from a Dynamic Server release earlier than Version 9.30, see the release notes to Version 9.30 for words that have been added to the list of SQL keywords since Version 9.21.
For a complete list of SQL keywords, see Appendix A of the IBM Informix Guide to SQL: Syntax, Version 9.4.
The new USETABLENAME environment variable can invalidate the use of synonyms in ALTER TABLE and DROP TABLE statements of SQL.
For more information about the USETABLENAME environment variable, see the IBM Informix Guide to SQL: Reference.
The section Enterprise Replication Enhancements describes additional new environment variables that can affect Enterprise Replication.
Home | [ Top of Page | Previous Page | Next Page | Contents | Index ]