Informix Guide to SQL: Syntax Introduction

For a comprehensive list of new database server features, see the release notes. This section lists new features relevant to this manual.

This manual describes new features in Version 8.3 of Enterprise Decision Server. The features fall into the following areas:

• Performance enhancements
• New SQL functionality
• Version 8.3 features from Dynamic Server 7.30

This manual describes the following performance enhancements to Version 8.3 of Enterprise Decision Server:

• Insert cursors with simple large objects
• Coarse-grain index locks
• Updates with subquery in SET clause
• Index on aggregates

This manual describes the following new SQL functionality in Version 8.3 of Enterprise Decision Server:

• CASE statement in Stored Procedure Language (SPL)
• Creating a table with RANGE fragmentation
• DELETEUSING statement to delete rows based on a table join
• Globally detached indexes
• Load and unload simple large objects to external tables
• MIDDLE function
• Referential integrity for globally detached indexes
• TRUNCATE statement

This manual describes the following features from Version 7.3 of Dynamic Server in Version 8.3 of Enterprise Decision Server:

• Ability to retain update locks
• ALTER FRAGMENT attach with remainders
• ALTER TABLE to add or drop a foreign key constraint
• ALTER TABLE to add, drop, or modify a column
• Constraints on columns other than the fragmentation column
• COUNT function
• DBINFO provides all Version 7.3 information and adds the coserver id and dbspace name
• Deferred constraints for all constraint types
• Deferred referential-integrity constraints
• Insert from SPL functions
• NVL and DECODE functions
• REPLACE, SUBSTR, LPAD, and RPAD functions for string manipulation
• RENAME COLUMN statement
• TO_CHAR and TO_DATE functions for date conversion
• Triggers
• UPDATE SET clause subqueries
• UPPER, LOWER, and INITCAP functions for case-insensitive search
• Memory-resident tables
• Violations table

This manual describes new features in Version 9.2 of Dynamic Server. The features fall into the following areas:

• Extensibility enhancements
• Performance improvements
• Special features
• Version 9.2 features from Version 7.30 of Dynamic Server

This manual describes the following extensibility enhancements to Version 9.2 of Dynamic Server:

• General enhancements to SQL:
• Embedded newline characters in quoted strings
• Nested dot expressions for row types
• Triggers on SELECT statements
• Enhancements to smart large objects:
• Round-robin fragmentation for smart large objects
• ALTER TABLE for smart large objects
• Enhancements to collections:
• Collection constructors that use arbitrary expression elements
• Collection-derived tables
• Collection subqueries
• Enhancements to row types:
• Serial types in row types
• GRANT, REVOKE UNDER on row types
• Enhancements to user-defined routines (UDRs):
• GRANT, REVOKE on UDR external languages
• ALTER FUNCTION, PROCEDURE, ROUTINE statements
• User-defined aggregates

This manual describes the following performance improvements to Version 9.2 of Dynamic Server:

• For SQL:
• Parallel statement-local variables (SLVs)
• SQL statement cache
• For UDRs:
• Expensive-function optimization
• Parallel UDRs
• User-defined statistics routines

This manual describes the following special features of Version 9.2 of Dynamic Server:

• Long identifiers:
• 128-character identifier
• 32-character user names
• Ability to retain update locks

This manual also describes features first released in Version 7.30. These features fall into the following areas:

• Reliability, availability, and serviceability:

ALTER FRAGMENT ATTACH, DETACH enhancements

• Performance:
• Optimizer directives
• Select first n rows
• SET OPTIMIZATION statement enhancements
• Memory-resident tables
• Application migration:
• UPPER, LOWER, and INITCAP functions for case-insensitive search (for built-in types)
• REPLACE, SUBSTR, LPAD, and RPAD functions for string manipulation (for built-in types)
• UNION operator in CREATE VIEW statement
• CASE expression
• NVL and DECODE functions
• TO_CHAR and TO_DATE date-conversion functions (for built-in types)
• EXECUTE PROCEDURE syntax to update triggering columns
• New arguments to the dbinfo() function to obtain the hostname and version of the database server