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

SQL Statements

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

Additional Restrictions on the ATTACH Clause in Dynamic Server

Additional Restrictions on the ATTACH Clause in XPS

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

What Happens to Indexes?

What Happens to BYTE and TEXT Columns?

What Happens to Triggers and Views?

What Happens with the Distribution Scheme?

Round-Robin Distribution Scheme

Expression Distribution Scheme

Hybrid Fragmentation Distribution Scheme (XPS)

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 Next Serial Value in a Typed Table (IDS)

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

Explicit DB-Access Transactions

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

Only Database Server Specified

Database Server and Database Specified

Only Database Specified

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

Example of Creating a User-Defined Aggregate

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

Integer Data Types

Big-Endian Format

Defining NULL Values

TEXT and HEX External Types

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

Using the SPECIFIC Clause to Specify a Specific Name

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

How Indexes Affect Primary-Key, Unique, and Referential Constraints

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

Effects of Unique Constraints on Sort Order Options

Bidirectional Traversal of Indexes

Restrictions on the Number of Indexes on a Set of Columns

Using an Operator Class (IDS)

USING Access-Method Clause (IDS)

FILLFACTOR Option

Providing a Low Percentage Value

Providing a High Percentage Value

Storage Options

Storing an Index in a dbspace

Storing an Index Fragment in a Named Partition (IDS)

Storing an Index in a dbslice (XPS)

Storing Data in an extspace (IDS)

Creating an Attached Index with the IN TABLE Keywords (IDS)

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

Adding a Unique Index When Duplicate Values Exist in the Column

Specifying Modes for Duplicate Indexes

How the Database Server Treats Disabled Indexes (IDS)

LOCK MODE Options (XPS)

Generalized-Key Indexes (XPS)

SELECT Clause for Generalized-Key Index

FROM Clause for Generalized-Key Index

WHERE Clause for Generalized-Key Index

The ONLINE Keyword (IDS)

CREATE OPAQUE TYPE

Declaring a Name for an Opaque Type

INTERNALLENGTH Modifier

Fixed-Length Opaque Types

Varying-Length Opaque Types

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

Using the SPECIFIC Clause to Specify a Specific Name

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

Constraint Names That the Database Server Generates

Choosing a Constraint-Mode Option (IDS)

Restrictions with the Multiple-Column Constraint Format

Using Large-Object Types in Constraints

Using the FOREIGN KEY Constraint

Examples of the Multiple-Column Constraint Format

Defining Check Constraints Across Columns

Defining Composite Primary and Foreign Keys

Using the WITH CRCOLS Option (IDS)

Using the IN Clause

Storing Data in a dbspace

Storing Data in a Partition of a dbspace (IDS)

Storing Data in a dbslice (XPS)

Storing Data in an extspace (IDS)

Using the WITH ROWIDS Option (IDS)

Fragmenting by ROUND ROBIN

Fragmenting by EXPRESSION

The USING Opclass Option (IDS)

User-Defined Functions in Fragment Expressions (IDS)

Using the REMAINDER Keyword

Fragmenting by HASH (XPS)

Serial Columns in HASH-Distribution Schemes

Fragmenting by HYBRID (XPS)

Range Definition

Range IN Clause

Using the REMAINDER Keyword

Simple Range-Fragmentation Strategy

Column-Major-Range Allocation

Row-Major-Range Allocation

Independent-Range Allocation

Alternative to Full Logging

Precedence and Default Behavior

Using Large-Object Data in Typed Tables

Using the UNDER Clause

Restrictions on Table Hierarchies

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

Trigger Events with Cursors

Privileges on the Trigger Event

Performance Impact of Triggers

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

FOR EACH ROW Actions

Actions of Multiple Triggers

Guaranteeing Row-Order Independence

RERERENCING Clauses

REFERENCING Clause for Delete

REFERENCING Clause for Insert

REFERENCING Clause for Update

REFERENCING Clause for Select (IDS)

Correlated Table Action

Triggered-Action List

Action Statements

Using Correlation Names in Triggered Actions

When to Use Correlation Names

Qualified Versus Unqualified Value

Re-Entrancy of Triggers

Re-Entrancy and Cascading Triggers

Rules for SPL Routines

Privileges to Execute Trigger Actions

Creating a Trigger Action That Anyone Can Use

Cascading Triggers

Constraint Checking

Preventing Triggers from Overriding Each Other

External Tables

Logging and Recovery

INSTEAD OF Triggers on Views (IDS)

The Action Clause of INSTEAD OF Triggers (IDS)

Restrictions on INSTEAD OF Triggers on Views (IDS)

Example of an INSTEAD OF Trigger on a View

Related Information

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

CREATE XADATASOURCE

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

Using FOR UPDATE with a List of Columns

Locking with an Update Cursor

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

Using an Insert Cursor with Hold

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

Using a SELECT Cursor with a Collection Variable

Insert with a Collection-Derived Table

Using the ONLY Keyword (IDS)

Considerations When Tables Have Cascading Deletes

Restrictions on DELETE When Tables Have Cascading Deletes

Locking and Logging Implications of 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 the VALUE Clause After a DESCRIBE

Using the VALUE Clause After a FETCH

Fetching a NULL Value

Using LENGTH or ILENGTH

Describing an Opaque-Type Column (IDS)

Describing a Distinct-Type Column (IDS)

GET DIAGNOSTICS

Using the SQLSTATE Error Status Code

Class and Subclass Codes

Support for the ANSI/ISO Standard for SQL

List of SQLSTATE Codes

Using SQLSTATE in Applications

Statement Clause

Using the MORE Keyword

Using the ROW_COUNT Keyword

Using the NUMBER Keyword

EXCEPTION Clause

Using the RETURNED_SQLSTATE Keyword

Using the CLASS_ORIGIN Keyword

Using the SUBCLASS_ORIGIN Keyword

Using the MESSAGE_TEXT Keyword

Using the MESSAGE_LENGTH Keyword

Using the SERVER_NAME Keyword

The Contents of the SERVER_NAME Field

The DATABASE Statement

Using the CONNECTION_NAME Keyword

When the CONNECTION_NAME Keyword Is Updated

When the CONNECTION_NAME Is Not Updated

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 WITH (PRIVILEGES) Option

The WITH (PRIVILEGES, ROLES) Option

The RESTRICT and CASCADE Options

Specifying CASCADE Mode

Specifying RESTRICT Mode

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

Example of Opening an Insert Cursor

Reopening an Insert Cursor

Opening a Collection Cursor (IDS)

Specifying a System Descriptor Area

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

Example of Specifying a Pointer to an sqlda Structure

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

Replacing PUBLIC with Specified Users

Restricting Access to Specific Columns

Effect of the ALL Keyword

Effect of the ALL Keyword on UNDER Privilege (IDS)

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

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

Selecting Columns

Selecting Constants

Selecting Built-In Function Expressions

Selecting Aggregate Function Expressions

Selecting User-Defined Function Expressions

Selecting Expressions That Use Arithmetic Operators

Selecting ROW Fields (IDS)

Declaring a Display Label

INTO Clause with Indicator Variables

INTO Clause with Cursors

Using the INTO clause in the SELECT statement

Using the INTO clause in the FETCH statement

Preparing a SELECT ... INTO Query

Using Array Variables with the INTO Clause

Warnings in ESQL/C

Aliases for Tables or Views

Table Expressions (XPS)

Usability and Performance Considerations

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)

Using Collection Variables with SELECT

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

Iterator Functions (IDS)

Queries that Join Tables

ANSI-Compliant Joins (IDS)

ANSI Table Reference

Creating an ANSI Join

ANSI Joined Tables

ANSI CROSS Joins

ANSI INNER Joins

ANSI LEFT OUTER Joins

ANSI RIGHT OUTER Joins

ANSI FULL OUTER Joins

Using the ON Clause

Specifying a Post-Join Filter

Using a Join as the Dominant or Subordinate Part of an Outer Join

Additional Examples of Outer Joins

Informix-Extension Outer Joins

Using a Condition in the WHERE Clause

Relational-Operator Condition

BETWEEN Condition

IS NULL Condition

LIKE or MATCHES Condition

EXISTS Subquery

ALL, ANY, SOME Subqueries

Specifying a Join in the WHERE Clause

Two-Table Joins

Multiple-Table Joins

Informix-Extension Outer Joins

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)

Specifying Delimiters

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

Starting and Stopping the Violations and Diagnostics Tables

Error Options for Filtering Mode

Effect of Filtering Mode on the Database

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

Compiling Without the -xopen Option

Setting the TYPE Field in X/Open Programs

Using DECIMAL or MONEY Data Types

Using DATETIME or INTERVAL Data Types

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

Turning the Option Off In the Middle of a Transaction

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

Restrictions on Matching Entries in the SQL Statement Cache

Turning the Cache OFF

Requiring Re-Execution Before Cache Insertion

Enabling or Disabling Insertions After Size Exceeds Configured Limit

Prepared Statements and the Statement Cache

SET TRANSACTION

Comparing SET TRANSACTION with SET ISOLATION

Informix Isolation Levels

Using the Read Uncommitted Option

Using the Read Committed Option

Using the Repeatable Read and Serializable Options

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

Violations and Diagnostics Tables with Default Names

Violations and Diagnostics Tables with Explicit Names

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

The TABLE Keyword

The Table Specification

The Storage Specification

The AM_TRUNCATE Purpose Function

Performance Advantages of TRUNCATE

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)

Updating Statistics for Columns

Examining Index Pages

Using the DROP DISTRIBUTIONS Option

Using the DISTRIBUTIONS ONLY Option to Suppress Index Information

Altered Tables that are Referenced Indirectly in SPL Routines

The Scope of WHENEVER

SQLERROR Keyword

SQLWARNING Keyword

NOT FOUND Keywords

CONTINUE Keyword

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