Enterprise Edition Home | Express Edition Home | Previous Page | Next Page

Contents

In This Introduction

About This Manual

Software Dependencies

Assumptions About Your Locale

Demonstration Databases

New Features in Dynamic Server, Version 10.0

New Features in Dynamic Server, Version 10.00.xC3

4 4

New Features in Dynamic Server, Version 10.00.xC4

New Features in Extended Parallel Server, Version 8.5

Documentation Conventions

Typographical Conventions

Feature, Product, and Platform Markup

Syntax Diagrams

Example Code Conventions

Additional Documentation

IBM Informix Information Center

Installation Guides

Informix Error Messages

IBM Informix Dynamic Server Version 10.0 and CSDK Version 2.90 Documentation Set

Compliance with Industry Standards

IBM Welcomes Your Comments

Overview of SQL Syntax

In This Chapter

How to Enter SQL Statements

Using Syntax Diagrams and Syntax Tables

Using Related Information

How to Enter SQL Comments

Examples of SQL Comment Symbols

Non-ASCII Characters in SQL Comments

Categories of SQL Statements

Data Definition Language Statements

Data Manipulation Language Statements

Data Integrity Statements

Cursor Manipulation Statements

Dynamic Management Statements

Data Access Statements

Optimization Statements

Routine Definition Statements

Auxiliary Statements

Client/Server Connection Statements

Optical Subsystem Statements (IDS)

ANSI/ISO Compliance and Extensions

ANSI/ISO-Compliant Statements

ANSI/ISO-Compliant Statements with Informix Extensions

Statements that are Extensions to the ANSI/ISO Standard

In This Chapter

ALLOCATE COLLECTION

ALLOCATE DESCRIPTOR

WITH MAX Clause

ALTER ACCESS_METHOD

Restrictions on the ALTER FRAGMENT Statement

ALTER FRAGMENT and Transaction Logging

Determining the Number of Rows in the Fragment

General Restrictions for the ATTACH Clause

Using the BEFORE, AFTER, and REMAINDER Options

Combining Nonfragmented Tables to Create a Fragmented Table

Attaching a Table to a Fragmented Table

Altering Hybrid-Fragmented Tables (XPS)

Effect of the ATTACH Clause

Detach with BYTE and TEXT Columns

Detach That Results in a Nonfragmented Table

Detach That Results in a Table with Hash Fragmentation (XPS)

WITH ROWIDS Option (IDS)

Converting a Fragmented Table to a Nonfragmented Table

Changing an Existing Fragmentation Strategy on a Table

Defining a Fragmentation Strategy on a Nonfragmented Table

Fragmenting Unique and System Indexes

Detaching an Index from a Table-Fragmentation Strategy (IDS)

Adding a New Dbspace to a Round-Robin Distribution Scheme

Adding a New Partition to a Round-Robin Distribution Scheme (IDS)

Adding Fragment Expressions

Using the BEFORE and AFTER Options

Using the REMAINDER Option

Keywords That Introduce Modifications

TO CLUSTER Option

TO NOT CLUSTER Option

LOCK MODE Options (XPS)

ALTER PROCEDURE

Keywords That Introduce Modifications

Altering Routine Modifiers Example

INCREMENT BY Option

RESTART WITH Option

MAXVALUE or NOMAXVALUE Option

MINVALUE or NOMINVALUE Option

CYCLE or NOCYCLE Option

CACHE or NOCACHE Option

ORDER or NOORDER Option

Using the ADD ROWIDS Keywords (IDS)

Using the DROP ROWIDS Keywords (IDS)

Using the ADD CRCOLS Keywords (IDS)

Using the DROP CRCOLS Keywords (IDS)

Using the BEFORE Option

Using NOT NULL Constraints with ADD

Constraint Definition

REFERENCES Clause

Restrictions on Referential Constraints

Default Column for the References Clause

Using the ON DELETE CASCADE Option

Locks Held During Creation of a Referential Constraint

How Dropping a Column Affects Constraints

How Dropping a Column Affects Triggers

How Dropping a Column Affects Views

How Dropping a Column Affects a Generalized-Key Index

Altering BYTE and TEXT Columns

Altering the Next Serial Value

Altering the Structure of Tables

Modifying Tables for NULL Values

Adding a Constraint That Existing Rows Violate (IDS)

How Modifying a Column Affects a Generalized-Key Index (XPS)

How Modifying a Column Affects Triggers

How Modifying a Column Affects Views

PUT Clause (IDS)

Adding a Primary-Key or Unique Constraint

Recovery from Constraint Violations (IDS)

Precedence and Default Behavior

Altering Subtables and Supertables

BEGIN WORK and ANSI-Compliant Databases

BEGIN WORK WITHOUT REPLICATION (IDS, ESQL/C)

Example of BEGIN WORK

Closing a Select or Function Cursor

Closing an Insert Cursor

Closing a Collection Cursor (IDS)

Using End of Transaction to Close a Cursor

Issuing COMMIT WORK in a Database That Is Not ANSI Compliant

Issuing COMMIT WORK in an ANSI-Compliant Database

Privileges for Executing the CONNECT Statement

Connection Identifiers

Connection Context

The Implicit Connection with DATABASE Statements

WITH CONCURRENT TRANSACTION Option

Restrictions on dbservername

Specifying the Database Environment

Restrictions on the Validation Variable Parameter

Restrictions on the User Identifier Parameter

Use of the Default User ID

CREATE ACCESS_METHOD

CREATE AGGREGATE

Extending the Functionality of Aggregates

Parallel Execution

Source and Target Data Types

Explicit and Implicit Casts

CREATE DATABASE

Logging Options

Designating Buffered Logging

ANSI-Compliant Databases

CREATE DISTINCT TYPE

Privileges on Distinct Types

Support Functions and Casts

Manipulating Distinct Types

CREATE DUPLICATE

Supported Operations

CREATE EXTERNAL TABLE (XPS)

Using the SAMEAS Clause

Using the EXTERNAL Keyword

Manipulating Data in Fixed Format Files

Using the NOT NULL Constraint

Using the CHECK Constraint

Using Formatting Characters

CREATE FUNCTION

Privileges Necessary for Using CREATE FUNCTION

DBA Keyword and Privileges on the Created Function

Overloading the Name of a Function

DOCUMENT Clause

WITH LISTING IN Clause

Example of Registering a C User-Defined Function

Example of Registering a UDR Written in the Java Language

Ownership of Created Database Objects

CREATE FUNCTION FROM

Index-Type Options

UNIQUE or DISTINCT Option

Index-Key Specification

Using a Column or Column List as the Index Key

Using a Function as an Index Key (IDS)

Creating Composite Indexes

Using the ASC and DESC Sort-Order Options

Using an Operator Class (IDS)

USING Access-Method Clause (IDS)

FILLFACTOR Option

Providing a Low Percentage Value

Providing a High Percentage Value

Storage Options

FRAGMENT BY Clause for Indexes

Restrictions on Fragmentation Expressions

Fragmentation of System Indexes

Fragmentation of Unique Indexes (IDS)

Fragmentation of Indexes on Temporary Tables

Index Modes (IDS)

Specifying Modes for Unique Indexes

Specifying Modes for Duplicate Indexes

How the Database Server Treats Disabled Indexes (IDS)

LOCK MODE Options (XPS)

Generalized-Key Indexes (XPS)

The ONLINE Keyword (IDS)

CREATE OPAQUE TYPE

Declaring a Name for an Opaque Type

INTERNALLENGTH Modifier

Opaque-Type Modifier

Defining an Opaque Type

STRATEGIES Clause

Strategy Specification

Indexes on Side-Effect Data

Default Operator Classes

CREATE PROCEDURE

Using CREATE PROCEDURE Versus CREATE FUNCTION

Relationship Between Routines, Functions, and Procedures

Privileges Necessary for Using CREATE PROCEDURE

DBA Keyword and Privileges on the Procedure

Procedure Names in Extended Parallel Server

Procedure Names in Dynamic Server

DOCUMENT Clause

Using the WITH LISTING IN Option

Registering a User-Defined Procedure

Ownership of Created Database Objects

Using sysbdopen( ) and sysdbclose( ) Stored Procedures (XPS)

CREATE PROCEDURE FROM

Default Directory That Holds the File

CREATE ROUTINE FROM

CREATE ROW TYPE

Privileges on Named Row Data Types

Inheritance and Named ROW Types

Creating a Subtype

Type Hierarchies

Procedure for Creating a Subtype

Field Definition

Restrictions on Serial and Simple-Large-Object Data Types

CREATE SCRATCH TABLE

CREATE SEQUENCE

INCREMENT BY Option

START WITH Option

MAXVALUE or NOMAXVALUE Option

MINVALUE or NOMINVALUE Option

CYCLE or NOCYCLE Option

CACHE or NOCACHE Option

ORDER or NOORDER Option

Synonyms for Remote and External Tables and Views

PUBLIC and PRIVATE Synonyms

Synonyms with the Same Name

Chaining Synonyms

Logging Options

Using NULL as a Default Value

Using a Literal as a Default Value

Using a Built-in Function as a Default Value

Restrictions on Using the Single-Column Constraint Format

Using the NOT NULL Constraint

Using the UNIQUE or DISTINCT Constraints

Differences Between a Unique Constraint and a Unique Index

Using the PRIMARY KEY Constraint

Restrictions on Referential Constraints

Default Values for the Referenced Column

Referential Relationships Within a Table

Locking Implications of Creating a Referential Constraint

Example That Uses the Single-Column Constraint Format

Using the ON DELETE CASCADE Option

Using a Search Condition

Restrictions When Using the Single-Column Constraint Format

Declaring a Constraint Name

Choosing a Constraint-Mode Option (IDS)

Restrictions with the Multiple-Column Constraint Format

Using the FOREIGN KEY Constraint

Examples of the Multiple-Column Constraint Format

Using the WITH CRCOLS Option (IDS)

Using the IN Clause

Using the WITH ROWIDS Option (IDS)

Fragmenting by ROUND ROBIN

Fragmenting by EXPRESSION

Fragmenting by HASH (XPS)

Fragmenting by HYBRID (XPS)

Range Definition

Range IN Clause

Using the REMAINDER Keyword

Alternative to Full Logging

Precedence and Default Behavior

Using Large-Object Data in Typed Tables

Using the UNDER Clause

Privileges on Tables

Default Index Creation Strategy for Constraints

System Catalog Information

CREATE TEMP TABLE

CREATE Temporary TABLE

Using the TEMP Option

Using the SCRATCH Option (XPS)

Naming a Temporary Table

Using the WITH NO LOG Option

Column Definition

Single-Column Constraint Format

Multiple-Column Constraint Format

Temporary Table Options

Storage Options

Where Temporary Tables are Stored

Differences between Temporary and Permanent Tables

Duration of Temporary Tables

Defining a Trigger Event and Action

Restrictions on Triggers

Trigger Modes (IDS)

Trigger Inheritance in a Table Hierarchy (IDS)

Triggers and SPL Routines

INSERT Events and DELETE Events

Defining Multiple Update Triggers

SELECT Event (IDS)

Circumstances When a Select Trigger is Activated

Stand-alone SELECT Statements

SELECT Statements Within UDRs in the Select List

UDRs That EXECUTE PROCEDURE and EXECUTE FUNCTION Call

Subqueries in the Select List

Select Triggers in Table Hierarchies

Circumstances When a Select Trigger is Not Activated

Guaranteeing Row-Order Independence

RERERENCING Clauses

Correlated Table Action

Triggered-Action List

Using Correlation Names in Triggered Actions

Re-Entrancy of Triggers

Rules for SPL Routines

Privileges to Execute Trigger Actions

Cascading Triggers

External Tables

Logging and Recovery

INSTEAD OF Triggers on Views (IDS)

Subset of SELECT Statements Valid in View Definitions

Naming View Columns

Using a View in the SELECT Statement

WITH CHECK OPTION Keywords

Updating Through Views

3 3

CREATE XADATASOURCE

3 3

CREATE XADATASOURCE TYPE

SQLCA.SQLWARN Settings Immediately after DATABASE Executes (ESQL/C)

EXCLUSIVE Keyword

DEALLOCATE COLLECTION

DEALLOCATE DESCRIPTOR

Using the FOR READ ONLY Option

Using the FOR UPDATE Option

Subset of INSERT Statement Associated with a Sequential Cursor

Creating a Sequential Cursor by Default

Using the SCROLL Keyword to Create a Scroll Cursor

Using the WITH HOLD Keywords to Create a Hold Cursor

Subset of SELECT Statement Associated with Cursors

Examples of Cursors in Non-ANSI Compliant Databases

Examples of Cursors in ANSI-Compliant Databases

Select with a Collection-Derived Table

Insert with a Collection-Derived Table

Using the ONLY Keyword (IDS)

Considerations When Tables Have Cascading Deletes

Using the WHERE Keyword to Introduce a Condition

Using the WHERE CURRENT OF Keywords (ESQL/C, SPL)

Using the USING or FROM Keyword to Introduce a Join Condition (XPS)

Deleting Rows That Contain Opaque Data Types (IDS)

Deleting Rows That Contain Collection Data Types (IDS)

Data Types in Distributed DELETE Operations (IDS)

SQLSTATE Values in an ANSI-Compliant Database

SQLSTATE Values in a Database That Is Not ANSI-Compliant

The OUTPUT Keyword

Describing the Statement Type

Checking for the Existence of a WHERE Clause

Describing a Statement with Runtime Parameters

Using the SQL DESCRIPTOR Keywords

Using the INTO sqlda Pointer Clause

Describing a Collection Variable (IDS)

Describing the Statement Type

Checking for Existence of a WHERE Clause

Describing a Statement with Dynamic Runtime Parameters

Using the SQL DESCRIPTOR Keywords

Using the INTO sqlda Pointer Clause

Describing a Collection Variable

Specifying the CURRENT Keyword

When a Transaction is Active

Disconnecting in a Thread-Safe Environment

Specifying the ALL Option

DROP ACCESS_METHOD

Modifying User-Defined Functions (XPS)

Dropping External Functions

The ONLINE Keyword (IDS)

Dropping an External Procedure (IDS)

Modifying SPL Routines (XPS)

Dropping an External Routine

The RESTRICT Keyword

Effects of the DROP TABLE Statement

Specifying CASCADE Mode

Specifying RESTRICT Mode

Dropping a Table That Contains Opaque Data Types (IDS)

Tables That Cannot Be Dropped

DROP XADATASOURCE

DROP XADATASOURCE TYPE

Scope of Statement Identifiers

Restrictions with the INTO Clause

Replacing Placeholders with Parameters

Saving Values In Host or Program Variables

Saving Values in a System-Descriptor Area

Saving Values in an sqlda Structure (ESQL/C)

The sqlca Record and EXECUTE

Returned SQLCODE Values with EXECUTE

Supplying Parameters Through Host or Program Variables

Supplying Parameters Through a System Descriptor

Supplying Parameters Through an sqlda Structure (ESQL/C)

EXECUTE FUNCTION

How the EXECUTE FUNCTION Statement Works

INTO Clause with Indicator Variables (ESQL/C)

INTO Clause with Cursors

Alternatives to PREPARE ... EXECUTE FUNCTION ... INTO

Dynamic Routine-Name Specification of SPL Functions

The jvpcontrol Function (Java)

Using the MEMORY Keyword

Using the THREADS Keyword

The IFX_REPLACE_MODULE Function (IDS, C)

EXECUTE IMMEDIATE

EXECUTE IMMEDIATE and Restricted Statements

Restrictions on Allowed Statements

Examples of the EXECUTE IMMEDIATE Statement

EXECUTE PROCEDURE

Causes of Errors

Using the INTO Clause

Dynamic Routine-Name Specification of SPL Procedures

sqlj.install_jar

sqlj.replace_jar

sqlj.remove_jar

sqlj.alter_java_path

sqlj.setUDTextName

sqlj.unsetUDTExtName

FETCH with a Sequential Cursor

FETCH with a Scroll Cursor

How the Database Server Implements Scroll Cursors

Specifying Where Values Go in Memory

Using the INTO Clause

Using Indicator Variables

When the INTO Clause of FETCH is Required

Using a System-Descriptor Area (X/Open)

Using sqlda Structures

Fetching a Row for Update

Fetching from a Collection Cursor (IDS)

Checking the Result of FETCH

Error Checking FLUSH Statements

Using the COUNT Keyword

Using the VALUE Clause

Using LENGTH or ILENGTH

Describing an Opaque-Type Column (IDS)

Describing a Distinct-Type Column (IDS)

GET DIAGNOSTICS

Using the SQLSTATE Error Status Code

Statement Clause

EXCEPTION Clause

The Contents of the SERVER_NAME Field

The Contents of the CONNECTION_NAME Field

Effect of the ALL Keyword

Privileges on Tables and Synonyms

Privileges on a View

USAGE Privilege

UNDER Privilege

Usage Privilege in Stored Procedure Language

Alter Privilege

Select Privilege

Granting a Role to a User or to Another Role

Granting a Privilege to a Role

Granting a Default Role

Granting the EXTEND Role (IDS)

Definition of Fragment-Level Authorization

Effect of Fragment-Level Authorization in Statement Validation

Duration of Fragment-Level Privileges

Specifying Fragments

Omitting the AS grantor Clause

Specifying Columns

Using the AT Clause (ESQL/C, IDS, and SPL)

Inserting Rows Through a View

Inserting Rows with a Cursor

Inserting Rows into a Database Without Transactions

Inserting Rows into a Database with Transactions

Considering Data Types

Inserting Values into Serial Columns

Inserting Values into Opaque-Type Columns (IDS)

Inserting Values into Collection Columns (IDS)

Inserting Values into ROW-Type Columns (IDS)

Data Types in Distributed INSERT Operations (IDS)

Using Expressions in the VALUES Clause

Inserting NULL Values

Truncated CHAR Values

Subset of SELECT Statement

Using External Tables (XPS)

Number of Values Returned by SPL, C, and Java Functions

Inserting into a Row Variable (ESQL/C, IDS, SPL)

Using INSERT as a Dynamic Management Statement

Loading Simple Large Objects

Loading Smart Large Objects (IDS)

Loading Complex Data Types (IDS)

Loading Opaque-Type Columns (IDS)

Concurrent Access to Tables with Exclusive Locks

Databases with Transactions

Databases Without Transactions

Locking Granularity

Target Table Restrictions

Handling Duplicate Rows

Considerations When Using the MOVE TABLE Statement

Moving Tables with Constraints

Renaming the Table

Preserving Table Privileges

The RESTRICT and CASCADE Options

Physically Moving Data to Another Database

Opening a Select Cursor

Opening an Update Cursor Inside a Transaction

Opening a Function Cursor

Reopening a Select or Function Cursor

Errors Associated with Select and Function Cursors

Opening an Insert Cursor

Opening a Collection Cursor (IDS)

Specifying a System Descriptor Area

Specifying a Pointer to an sqlda Structure (ESQL/C)

Using the WITH REOPTIMIZATION Option

Relationship Between OPEN and FREE

DDL Operations on Tables Referenced by Cursors

Sending Query Results to a File

Displaying Query Results Without Column Headings

Sending Query Results to Another Program

Scope of Statement Identifiers

Obtaining SQL Identifiers from User Input

Restricted Statements in Multistatement Prepared Objects

DDL Operations on Tables Referenced in Prepared Objects

Using Constant Values in INSERT

Naming Program Variables in INSERT

Naming Program Variables in PUT

Specifying a System-Descriptor Area

Specifying an sqlda Structure

RENAME DATABASE

RENAME SEQUENCE

When to Use REVOKE Before GRANT

Effect of the ALL Keyword

Usage Privilege

Under Privilege

Alter Privilege

Select Privilege

Revoking a Default Role

Revoking the EXTEND Role (IDS)

Effect of CASCADE Keyword on UNDER Privileges (IDS)

REVOKE FRAGMENT

Specifying Fragments

The FROM Clause

Revoking Privileges on One Fragment

Revoking Privileges on More Than One Fragment

Revoking Privileges from More Than One User

Revoking Privileges Without Specifying Fragments

SAVE EXTERNAL DIRECTIVES

External Optimizer Directives

Enabling External Directives for a Session

The directive Specification

The ACTIVE, INACTIVE, and TEST ONLY Keywords

The query Specification

The Order of Qualifying Rows

Using the SKIP Option (IDS)

Using the FIRST Option

3 3

The LIMIT Keyword (IDS)

Using SKIP, FIRST, LIMIT, or MIDDLE as a Column Name

Using the SKIP Option with the FIRST Option (IDS)

Using the MIDDLE Option (XPS)

Allowing Duplicates

Data Types in Distributed Queries (IDS)

Expressions in the Select List

Declaring a Display Label

INTO Clause with Indicator Variables

INTO Clause with Cursors

Preparing a SELECT ... INTO Query

Using Array Variables with the INTO Clause

Aliases for Tables or Views

Table Expressions (XPS)

Restrictions on External Tables in Joins and Subqueries

Application Partitioning: The LOCAL Keyword (XPS)

Sampled Queries: The SAMPLES OF Keywords (XPS)

The ONLY Keyword (IDS)

Selecting from a Collection Variable (IDS)

Selecting from a Row Variable (IDS, ESQL/C)

Iterator Functions (IDS)

Queries that Join Tables

ANSI-Compliant Joins (IDS)

Using the ON Clause

Informix-Extension Outer Joins

Using a Condition in the WHERE Clause

Specifying a Join in the WHERE Clause

Relationship of GROUP BY and Projection Clauses

NULL Values in the GROUP BY Clause

Using Select Numbers

Ordering by a Column or by an Expression

Ordering by a Substring

Ascending and Descending Orders

Nested Ordering

Using Select Numbers

Ordering by Rowids

ORDER BY Clause with DECLARE

Placing Indexes on ORDER BY Columns

Syntax That is Incompatible with the FOR UPDATE Clause

Using the FOR READ ONLY Clause in Read-Only Mode

Syntax That Is Incompatible with the FOR READ ONLY Clause

Results When No Rows are Returned

Restrictions with INTO Table Clauses in ESQL/C

INTO TEMP Clause

Using SELECT INTO to Create a New Permanent Table

Using the WITH NO LOG Option

INTO EXTERNAL Clause (XPS)

INTO SCRATCH Clause (XPS)

Restrictions on a Combined SELECT

Duplicate Rows in a Combined SELECT

UNION in Subqueries

SET ALL_MUTABLES

Globally Affecting Cursors with SET AUTOFREE

Using the FOR Clause to Specify a Specific Cursor

Associated and Detached Statements

Closing Cursors Implicitly

Specifying a Collating Order with SET COLLATION

Restrictions on SET COLLATION

Collation Performed by Database Objects

Making a Dormant Connection the Current Connection

Making a Current Connection the Dormant Connection

Dormant Connections in a Single-Threaded Environment

Dormant Connections in a Thread-Safe Environment

Identifying the Connection

CURRENT Keyword

When a Transaction is Active

SET CONSTRAINTS

SET Database Object Mode

Privileges Required for Changing Database Object Modes

Object-List Format

Modes for Constraints and Unique Indexes

Modes for Triggers and Duplicate Indexes

Circumstances When a Dbspace Cannot Be Skipped

Using the WITH APPEND Option

Closing the Output File

Redirecting Trace Output

Location of the Output File

SET Default Table Space

SET Default Table Type

SET DEFERRED_PREPARE

Example of SET DEFERRED_PREPARE

Using Deferred-Prepare with OPTOFC

Using the COUNT Clause

Using the VALUE Clause

Setting the TYPE or ITYPE Field

Setting the DATA or IDATA Field

Setting the LENGTH or ILENGTH Field

Setting the INDICATOR Field

Setting Opaque-Type Fields (IDS)

Setting Distinct-Type Fields

SET ENCRYPTION PASSWORD

Storage Requirements for Encryption

Specifying a Session Password and Hint

Levels of Encryption

Protecting Passwords

SET ENVIRONMENT

BOUND_IMPL_PDQ Environment Option (XPS)

CLIENT_TZ Environment Option (XPS)

COMPUTE_QUOTA Environment Option (XPS)

IMPLICIT_PDQ Environment Option (XPS)

MAXSCAN Environment Option (XPS)

TMPSPACE_LIMIT Environment Option (XPS)

TEMP_TAB_EXT_SIZE and TEMP_TAB_NEXT_SIZE Options (XPS)

OPTCOMPIND Environment Option (IDS)

Local Scope of SET ENVIRONMENT

Using the AVOID_EXECUTE Option

Using the FILE TO Option

Using the WITH APPEND Option

Default Name and Location of the Output File on UNIX

Default Name and Location of the Output File on Windows

Complete-Connection Level Settings

Complete-Connection Level Settings

Using the Dirty Read Option

Using the Committed Read Option

Using the Cursor Stability Option

Using the Repeatable Read Option

Default Isolation Levels

Using the RETAIN UPDATE LOCKS Option

Complete-Connection Level Settings

SET OPTIMIZATION

HIGH and LOW Options

FIRST_ROWS and ALL_ROWS Options (IDS)

Optimizing SPL Routines

SET PDQPRIORITY

Allocating Database Server Resources

Using a Range of Values

Residency and the Changing Status of Fragments

Setting the Default Role

SET SCHEDULE LEVEL

SET SESSION AUTHORIZATION

SET STATEMENT CACHE

Precedence and Default Behavior

Turning the Cache ON

Turning the Cache OFF

Requiring Re-Execution Before Cache Insertion

Enabling or Disabling Insertions After Size Exceeds Configured Limit

3 3

Prepared Statements and the Statement Cache

SET TRANSACTION

Comparing SET TRANSACTION with SET ISOLATION

Informix Isolation Levels

Default Isolation Levels

Effects of Isolation Levels

SET Transaction Mode

START VIOLATIONS TABLE

Relationship to SET Database Object Mode Statement

Effect on Concurrent Transactions

Stopping the Violations and Diagnostics Tables

Using the MAX ROWS Clause (IDS)

Using the MAX VIOLATIONS Clause (XPS)

Specifying the Maximum Number of Rows in the Diagnostics Table (IDS)

Specifying the Maximum Number of Rows in the Violations Table (XPS)

Privileges Required for Starting Violations Tables

Structure of the Violations Table

Examples of START VIOLATIONS TABLE Statements

Relationships Among the Target, Violations, and Diagnostics Tables

Initial Privileges on the Violations Table

Example of Privileges on the Violations Table

Using the Violations Table

Example of a Violations Table

Structure of the Diagnostics Table (IDS)

Initial Privileges on the Diagnostics Table

Using the Diagnostics Table

STOP VIOLATIONS TABLE

Example of Stopping a Violations and Diagnostics Table

Example of Dropping a Violations and Diagnostics Table

4 4

4 4

The TABLE Keyword

4 4

The Table Specification

4 4

The Storage Specification

4 4

The AM_TRUNCATE Purpose Function

4 4

Performance Advantages of TRUNCATE

4 4

Using the ONLY and TABLE Keywords

After the TRUNCATE Statement Executes

When You Might Use the TRUNCATE Statement

Unloading Character Columns

Unloading Simple Large Objects

Unloading Smart Large Objects (IDS)

Unloading Complex Types (IDS)

Using the ONLY Keyword (IDS)

Updating Rows Through a View

Updating Rows in a Database Without Transactions

Updating Rows in a Database with Transactions

Locking Considerations

Single-Column Format

Using a Subquery to Update a Column

Updating a Column to NULL

Updating the Same Column Twice

Multiple-Column Format

Using a Subquery to Update Column Values

Using an SPL Function to Update Column Values (XPS)

Updating ROW-Type Columns (IDS)

Updating Collection Columns (IDS)

Updating Values in Opaque-Type Columns (IDS)

Data Types in Distributed UPDATE Operations (IDS)

Subset of FROM Clause

SQLSTATE Values When Updating an ANSI-Compliant Database

SQLSTATE Values When Updating a Non-ANSI Database

Using the WHERE CURRENT OF Clause (ESQL/C, SPL)

Updating a Row Variable (IDS, ESQL/C)

UPDATE STATISTICS

Scope of UPDATE STATISTICS

Using the ONLY Keyword (IDS)

3 3

Updating Statistics for Columns

Examining Index Pages

Using the DROP DISTRIBUTIONS Option

Using the DISTRIBUTIONS ONLY Option to Suppress Index Information

3 3

Altered Tables that are Referenced Indirectly in SPL Routines

The Scope of WHENEVER

SQLERROR Keyword

SQLWARNING Keyword

NOT FOUND Keywords

CONTINUE Keyword

In This Chapter

Referencing TEXT and BYTE Variables

Redeclaration or Redefinition

SITENAME or DBSERVERNAME

Subset of Complex Data Types (IDS)

Declaring Collection Variables (IDS)

Declaring ROW Variables (IDS)

Declaring Opaque-Type Variables (IDS)

Declaring Variables LIKE Columns

Declaring Variables as the PROCEDURE Type

Declaring Variables for BYTE and TEXT Data

Using the TO Keyword to Define a Range

Using an Expression List as the Range

Mixing Range and Expression Lists in the Same FOR Statement

Using a SELECT ... INTO Statement

Using Hold Cursors

Updating or Deleting Rows Identified by Cursor Name

Using Collection Variables (IDS)

Conditions in an IF Statement

Subset of SPL Statements Allowed in the IF Statement List

SQL Statements Not Valid in an IF Statement

Using a SELECT Statement in a LET Statement

Calling a Function in a LET Statement

Placement of the ON EXCEPTION Statement

Continuing Execution After an Exception Occurs

RAISE EXCEPTION

Returning Values from Another Database (IDS)

Executing the SYSTEM Statement on UNIX

Executing the SYSTEM Statement on Windows

Setting Environment Variables in SYSTEM Commands

TRACE PROCEDURE

Displaying Expressions

Example Showing Different Forms of TRACE

Looking at the Traced Output

Data Types and Expressions

In This Chapter

Scope of Segment Descriptions

Use of Segment Descriptions

Collection Subquery

Comparison Conditions (Boolean Expressions)

Quotation Marks in Conditions

Relational-Operator Condition

BETWEEN Condition

IS NULL Condition

LIKE and MATCHES Condition

Stand-Alone Condition

EXISTS Subquery

ALL, ANY, and SOME Subqueries

BOOLEAN and Other Built-In Opaque Data Types (IDS)

Character Data Types

Numeric Data Types

Large-Object Data Types

Time Data Types

Distinct Data Types

Opaque Data Types

Collection Data Types

DATETIME Field Qualifier

Rules for the Target Data Type

Examples of Cast Expressions

The Keyword NULL in Cast Expressions

Using Dot Notation

Using Subscripts on Character Columns

Using Rowids (IDS)

Using Smart Large Objects (IDS)

CASE Expressions

DECODE Function

CURRENT_ROLE Operator

DEFAULT_ROLE Operator

DBSERVERNAME and SITENAME Operators

CURRENT Operator

Non-default Time Zones for CURRENT or TODAY (XPS)

Literal DATETIME

Literal INTERVAL

NEXTVAL and CURRVAL Operators (IDS)

Literal Row (IDS)

Literal Collection (IDS)

ROW Constructors

Collection Constructors

Algebraic Functions

CARDINALITY Function (IDS)

DBINFO Function

Encryption and Decryption Functions

DECRYPT_CHAR Function

DECRYPT_BINARY Function

ENCRYPT_AES Function

ENCRYPT_TDES Function

GETHINT Function

Exponential and Logarithmic Functions

Length Functions

IFX_REPLACE_MODULE Function (IDS, C)

Smart-Large-Object Functions (IDS)

Trigonometric Functions

String-Manipulation Functions

Case-Conversion Functions

IFX_ALLOW_NEWLINE Function

User-Defined Functions

Types of Aggregate Expressions

Subset of Expressions Valid in an Aggregate Expression

Including or Excluding Duplicates in the Row Set

Overview of COUNT Functions

COUNT(*) Function

COUNT DISTINCT and COUNT UNIQUE Functions

COUNT column Function

Comparison of the Different COUNT Functions

VARIANCE Function

Error Checking in ESQL/C

Summary of Aggregate Function Behavior

User-Defined Aggregates (IDS)

INTERVAL Field Qualifier

Literal Collection

Element Literal Value

Nested Quotation Marks

Literal DATETIME

Casting Numeric Date and Time Strings to DATE Data Types

Literal INTERVAL

Integer Literals

Fixed-Point Decimal Literals

Floating-Point Decimal Literals

Literal Numbers and the MONEY Data Type

Literals of an Unnamed Row Type

Literals of a Named Row Type

Literals for Nested Rows

Restrictions on Specifying Characters in Quoted Strings

The DELIMIDENT Environment Variable

Newline Characters in Quoted Strings

Using Quotes in Strings

DATETIME and INTERVAL Values as Strings

LIKE and MATCHES in a Condition

Inserting Values as Quoted Strings

Relational Operator

Using Operator Functions in Place of Relational Operators

Collating Order for U.S. English Data

Support for ASCII Characters in Nondefault Code Sets (GLS)

Literal Numbers as Operands

Other Syntax Segments

In This Chapter

Comparing Arguments to the Parameter List

Subset of Expressions Valid as an Argument

Arguments to UDRs in Remote Databases

Related Information

Collection-Derived Table

Accessing a Collection Through a Virtual Table

Restrictions with the Collection-Expression Format

Row Type of the Resulting Collection-Derived Table

Accessing a Collection Through a Collection Variable

Using a Collection Variable to Manipulate Collection Elements

Accessing a Nested Collection

Accessing a Row Variable

Related Information

Using the @ Symbol

Using a Path-Type Naming Notation

Using a Host Variable

Database Object Name

Specifying a Database Object in an External Database

Routine Overloading and Naming UDRs with a Routine Signature (IDS)

Owners of Objects Created by UDRs

External Routine Reference

VARIANT or NOT VARIANT Option

Example of a C User-Defined Function

Use of Uppercase Characters

Use of Keywords as Identifiers

Support for Non-ASCII Characters in Identifiers

Delimited Identifiers

Potential Ambiguities and Syntax Errors

Using the Names of Built-In Functions as Column Names

Using Keywords as Column Names

Using ALL, DISTINCT, or UNIQUE as a Column Name

Using INTERVAL or DATETIME as a Column Name

Using rowid as a Column Name (IDS)

Workarounds that Use the Keyword AS

Using AS with Column Labels

Using AS with Table Aliases

Fetching Cursors that have Keywords as Names

Fetching Cursors that have Keywords as Names

Using CURRENT, DATETIME, INTERVAL, and NULL in INSERT

Using NULL and SELECT in a Condition

Using ON, OFF, or PROCEDURE with TRACE

Using GLOBAL as the Name of a Variable

Using EXECUTE, SELECT, or WITH as Cursor Names

SELECT Statements in WHILE and FOR Statements

SET Keyword in the ON EXCEPTION Statement

Related Information

Optimizer Directives

Optimizer Directives as Comments

Restrictions on Optimizer Directives

Access-Method Directives

Join-Order Directive

Join-Method Directives

Optimization-Goal Directives (IDS)

Explain-Mode Directives

Rewrite Method Directive (XPS)

Related Information

Using Quotation Marks

Accessing Information from the System Catalog Tables

ANSI-Compliant Database Restrictions and Case Sensitivity

Setting ANSIOWNER for an ANSI-Compliant Database

Default Owner Names

Purpose Options

Purpose Options for Access Methods

Purpose Functions, Methods, Flags, and Values

Purpose Options for XA Data Source Types

Limits on Returned Values

Subset of SQL Data Types

Returning a Value from Another Database (IDS)

Using the REFERENCES Clause to Point to a Simple Large Object

Named Return Parameters (IDS)

Cursor and Noncursor Functions

Routine Modifier

Adding or Modifying a Routine Modifier

Modifier Descriptions

PERCALL_COST (C)

VARIANT and NOT VARIANT

Related Information

Routine Parameter List

Subset of SQL Data Types

Using the LIKE Clause

Using the REFERENCES Clause

Using the DEFAULT Clause

Specifying OUT Parameters for User-Defined Routines

Specifying INOUT Parameters for a User-Defined Routine (IDS)

Shared-Object Filename

C Shared-Object File

Java Shared-Object File

Related Information

Restrictions on the Owner Name

Restrictions on the Specific Name

Statement Block

Subset of SPL Statements Valid in the Statement Block

SQL Statements Not Valid in an SPL Statement Block

Nested Statement Blocks

Restrictions on SPL Routines in Data-Manipulation Statements

Transactions in SPL Routines

Support for Roles and User Identity

Appendix A. Reserved Words for IBM Informix Dynamic Server

Appendix B. Reserved Words for IBM Informix Extended Parallel Server

Appendix C. Accessibility

Enterprise Edition Home | Express Edition Home | [ Top of Page | Previous Page | Next Page | Contents | Index ]