IBM Informix SQL

Quick Syntax Guide

IBM Informix 4GL, Version 4.1

IBM Informix SQL, Version 4.1
IBM Informix ESQL/C, Version 5.0
IBM Informix ESQL/COBOL, Version 5.0
IBM Informix SE, Version 5.0
IBM Informix NET, Version 5.0
IBM Informix OnLine, Version 5.2
IBM Informix OnLine/Optical, Version 5.0
IBM Informix STAR, Version 5.0

November 2002
Part No. 000-9123
 Note:
Before using this information and the product it supports, read the information in the
appendix entitled “Notices.”

This document contains proprietary information of IBM. It is provided under a license agreement and is
protected by copyright law. The information contained in this publication does not include any product
warranties, and any statements provided in this manual should not be interpreted as such.

When you send information to IBM, you grant IBM a nonexclusive right to use or distribute the information
in any way it believes appropriate without incurring any obligation to you.

© Copyright International Business Machines Corporation 1996, 2002. All rights reserved.

US Government User Restricted Rights—Use, duplication or disclosure restricted by GSA ADP Schedule
Contract with IBM Corp.

ii IBM Informix SQL Quick Syntax Guide

 Table of
Contents

Table of Contents

Introduction
In This Introduction . . . . . . . . . . . . . . . . . 3
About This Manual . . . . . . . . . . . . . . . . . . 3
Syntax Conventions . . . . . . . . . . . . . . . . 3

Chapter 1 SQL Statements

ALLOCATE DESCRIPTOR . . . . . . . . . . . . . . . 1-5
ALTER INDEX . . . . . . . . . . . . . . . . . . . 1-5
ALTER TABLE . . . . . . . . . . . . . . . . . . . 1-6
BEGIN WORK . . . . . . . . . . . . . . . . . . . 1-11
CHECK TABLE . . . . . . . . . . . . . . . . . . . 1-11
CLOSE . . . . . . . . . . . . . . . . . . . . . . 1-12
CLOSE DATABASE . . . . . . . . . . . . . . . . . 1-12
COMMIT WORK . . . . . . . . . . . . . . . . . . 1-12
CREATE AUDIT FOR . . . . . . . . . . . . . . . . . 1-13
CREATE DATABASE . . . . . . . . . . . . . . . . . 1-13
CREATE INDEX . . . . . . . . . . . . . . . . . . . 1-14
CREATE PROCEDURE . . . . . . . . . . . . . . . . 1-15
CREATE PROCEDURE FROM . . . . . . . . . . . . . . 1-17
CREATE SCHEMA AUTHORIZATION . . . . . . . . . . 1-17
CREATE SYNONYM . . . . . . . . . . . . . . . . . 1-18
CREATE TABLE . . . . . . . . . . . . . . . . . . . 1-19
CREATE VIEW . . . . . . . . . . . . . . . . . . . 1-22
DATABASE . . . . . . . . . . . . . . . . . . . . 1-22
DEALLOCATE DESCRIPTOR . . . . . . . . . . . . . . 1-23
DECLARE . . . . . . . . . . . . . . . . . . . . . 1-24
DELETE FROM . . . . . . . . . . . . . . . . . . . 1-25
DESCRIBE . . . . . . . . . . . . . . . . . . . . . 1-25
DROP AUDIT FOR . . . . . . . . . . . . . . . . . . 1-26
 DROP DATABASE . . . . . . . . . . . . . . . . . 1-26
DROP INDEX . . . . . . . . . . . . . . . . . . . 1-26
DROP PROCEDURE . . . . . . . . . . . . . . . . . 1-27
DROP SYNONYM . . . . . . . . . . . . . . . . . 1-27
DROP TABLE . . . . . . . . . . . . . . . . . . . 1-27
DROP VIEW . . . . . . . . . . . . . . . . . . . . 1-28
EXECUTE . . . . . . . . . . . . . . . . . . . . 1-28
EXECUTE IMMEDIATE . . . . . . . . . . . . . . . . 1-29
EXECUTE PROCEDURE . . . . . . . . . . . . . . . 1-29
FETCH . . . . . . . . . . . . . . . . . . . . . . 1-30
FLUSH . . . . . . . . . . . . . . . . . . . . . . 1-31
FREE . . . . . . . . . . . . . . . . . . . . . . 1-31
GET DESCRIPTOR . . . . . . . . . . . . . . . . . 1-32
GRANT . . . . . . . . . . . . . . . . . . . . . 1-33
INFO . . . . . . . . . . . . . . . . . . . . . . 1-35
INSERT INTO . . . . . . . . . . . . . . . . . . . 1-36
LOAD FROM . . . . . . . . . . . . . . . . . . . 1-38
LOCK TABLE . . . . . . . . . . . . . . . . . . . 1-39
OPEN . . . . . . . . . . . . . . . . . . . . . . 1-39
OUTPUT TO . . . . . . . . . . . . . . . . . . . . 1-40
PREPARE . . . . . . . . . . . . . . . . . . . . . 1-40
PUT . . . . . . . . . . . . . . . . . . . . . . . 1-41
RECOVER TABLE . . . . . . . . . . . . . . . . . . 1-41
RENAME COLUMN . . . . . . . . . . . . . . . . . 1-42
RENAME TABLE . . . . . . . . . . . . . . . . . . 1-42
REPAIR TABLE . . . . . . . . . . . . . . . . . . . 1-42
REVOKE . . . . . . . . . . . . . . . . . . . . . 1-43
REVOKE . . . . . . . . . . . . . . . . . . . . . 1-44
ROLLBACK WORK . . . . . . . . . . . . . . . . . 1-44
ROLLFORWARD DATABASE . . . . . . . . . . . . . 1-44
SELECT . . . . . . . . . . . . . . . . . . . . . 1-45
SET CONSTRAINTS . . . . . . . . . . . . . . . . . 1-50
SET DEBUG FILE TO . . . . . . . . . . . . . . . . . 1-50
SET DESCRIPTOR . . . . . . . . . . . . . . . . . . 1-51
SET EXPLAIN . . . . . . . . . . . . . . . . . . . 1-52
SET ISOLATION TO . . . . . . . . . . . . . . . . . 1-52
SET LOCK MODE TO . . . . . . . . . . . . . . . . 1-53
SET . . . . . . . . . . . . . . . . . . . . . . . 1-53
SET OPTIMIZATION . . . . . . . . . . . . . . . . . 1-53

iv IBM Informix SQL Quick Syntax Guide

 START DATABASE . . . . . . . . . . . . . . . . . 1-54
UNLOAD TO . . . . . . . . . . . . . . . . . . . 1-54
UNLOCK TABLE . . . . . . . . . . . . . . . . . 1-55
UPDATE . . . . . . . . . . . . . . . . . . . . 1-56
UPDATE STATISTICS . . . . . . . . . . . . . . . . 1-57
WHENEVER . . . . . . . . . . . . . . . . . . . 1-58

Chapter 2 SQL Segments

Condition . . . . . . . . . . . . . . . . . . . . 2-3
Constraint Name . . . . . . . . . . . . . . . . . . 2-5
Database Name . . . . . . . . . . . . . . . . . . 2-6
Data Type . . . . . . . . . . . . . . . . . . . . 2-7
DATETIME Field Qualifier . . . . . . . . . . . . . . 2-8
Expression . . . . . . . . . . . . . . . . . . . . 2-9
Identifier . . . . . . . . . . . . . . . . . . . . 2-12
Index Name . . . . . . . . . . . . . . . . . . . 2-13
INTERNAL Field Qualifer . . . . . . . . . . . . . . 2-14
Literal DATETIME . . . . . . . . . . . . . . . . . 2-15
Literal INTERVALS . . . . . . . . . . . . . . . . . 2-16
Literal Number . . . . . . . . . . . . . . . . . . 2-16
Procedure Name . . . . . . . . . . . . . . . . . . 2-17
Quoted String . . . . . . . . . . . . . . . . . . . 2-17
Relational Operator . . . . . . . . . . . . . . . . . 2-18
Synonym Name . . . . . . . . . . . . . . . . . . 2-18
Table Name . . . . . . . . . . . . . . . . . . . 2-19
View Name . . . . . . . . . . . . . . . . . . . 2-19

Chapter 3 Stored Procedure Language Statements

CALL . . . . . . . . . . . . . . . . . . . . . 3-3
CONTINUE . . . . . . . . . . . . . . . . . . . 3-3
DEFINE . . . . . . . . . . . . . . . . . . . . . 3-4
EXIT . . . . . . . . . . . . . . . . . . . . . . 3-5
FOR . . . . . . . . . . . . . . . . . . . . . . 3-6
FOREACH . . . . . . . . . . . . . . . . . . . . 3-7
IF . . . . . . . . . . . . . . . . . . . . . . . 3-8
LET . . . . . . . . . . . . . . . . . . . . . . 3-10
ON EXCEPTION . . . . . . . . . . . . . . . . . . 3-10
RAISE EXCEPTION . . . . . . . . . . . . . . . . 3-11
RETURN . . . . . . . . . . . . . . . . . . . . 3-11

Table of Contents v
 SYSTEM . . . . . . . . . . . . . . . . . . . . . 3-11
TRACE . . . . . . . . . . . . . . . . . . . . . 3-12
WHILE . . . . . . . . . . . . . . . . . . . . . 3-12
SPL Expression . . . . . . . . . . . . . . . . . . . 3-13

Appendix A Notices

vi IBM Informix SQL Quick Syntax Guide

 Introduction

Introduction

In This Introduction . . . . . . . . . . . . . . . . . . 3
About This Manual . . . . . . . . . . . . . . . . . . . 3
Syntax Conventions . . . . . . . . . . . . . . . . . 3
2 IBM Informix SQL Quick Syntax Guide
In This Introduction
This introduction provides an overview of the information in this manual
and describes the conventions it uses.

About This Manual

The following statements and segments are presented in this guide:

■ SQL statements
■ SQL segments
■ Stored Procedure Language (SPL) statements

Syntax Conventions
Syntax diagrams describe the format of SQL statements or commands,
including alternative forms of a statement, required and optional parts of the
statement, and so forth. Syntax diagrams have their own conventions, which
are defined in detail and illustrated in this section. SQL statements are listed
in their entirety in the IBM Informix Guide to SQL: Reference, although some
statements may appear in other manuals.

Each syntax diagram displays the sequences of required and optional

elements that are valid in a statement. Briefly:
■ All keywords are shown in uppercase letters for ease of identifi-
cation, even though you need not enter them that way.
■ Words for which you must supply values are in italics.

Introduction 3
Syntax Conventions

A diagram begins at the upper left with a keyword. It ends at the upper right
with a vertical line. Between these points you can trace any path that does not
stop or back up. Each path describes a valid form of the statement.

Along a path, you may encounter the following elements:

KEYWORD You must spell a word in uppercase letters exactly as shown;
however, you can use either uppercase or lowercase letters when you
enter it.

(.,;+*-/) Punctuation and mathematical notations are literal symbols that you
must enter exactly as shown.

" " Double quotes are literal symbols that you must enter as shown. You
can replace a pair of double quotes with a pair of single quotes, if you
prefer. You cannot mix double and single quotes.

variable A word in italics represents a value that you must supply. The nature
of the value is explained immediately following the diagram unless
the variable appears in a box. In that case, the page number of the
detailed explanation follows the variable name.

A reference in a box represents a subdiagram on the same page or

ADD Clause another page. Imagine that the subdiagram is spliced into the main
p. 7-14
diagram at this point.

A reference to the SQLR represents an SQL statement or segment

Relational described in the IBM Informix Guide to SQL: Reference. Imagine that the
Operator
see SQLR statement or segment is spliced into the main diagram at this point.

I4GL A code in an icon is a signal warning you that this path is valid only
for some products or under certain conditions. The codes indicate the
products or conditions that support the path. The following codes are
used:

SE
SE This path is valid only for IBM Informix SE.

OL This path is valid only for IBM Informix OnLine.

STAR
STAR This path is valid only for IBM Informix STAR.

STAR
INET This path is valid only for IBM Informix NET.

I4GL This path is valid only for IBM Informix 4GL.

4 IBM Informix SQL Quick Syntax Guide

 Syntax Conventions

ISQL
ISQL This path is valid only for IBM Informix SQL.

ESQL
ESQL This path is valid for SQL statements in all the following
embedded language products: IBM Informix ESQL/C
and IBM Informix ESQL/COBOL.

E/C
E/C This path is valid only for IBM Informix ESQL/C.

E/CO
E/C This path is valid only for IBM Informix ESQL/COBOL.

E/F
E/C This path is valid only for INFORMIX-ESQL/FORTRAN.

DB
E/C This path is valid only for DB-Access.

STAR
SPL This path is valid only if you are using Informix Stored
Procedure Language (SPL).

++ This path is an Informix extension to ANSI standard SQL.

If you initiate Informix extension checking and include
this syntax branch, you receive a warning. If you have set
the DBANSIWARN environment variable, you receive the
warnings at run time. To receive the warnings at compile
time, compile with the -ansi flag.

A shaded option is the default. Even if you do not explicitly type the
ALL
option, it will be in effect unless you choose another option.

Syntax enclosed in a pair of arrows indicates that this is a subdiagram.

The vertical line is a terminator and indicates that the statement is

complete.

Introduction 5
Syntax Conventions

IN
A branch below the main line indicates an optional path.

NOT

, A loop indicates a path that can be repeated.

variable

1 column key

A gate ( 1 ) in an option indicates that you can only use

that option once, even though it is within a larger loop.

In the IBM Informix Guide to SQL: Reference, icons that appear in the left margin
indicate that the accompanying shaded text is valid only for some products
or under certain conditions. In addition to the icons described in the
preceding list, you may encounter the following icons in the left margin:

ANSI This icon indicates that the functionality described in the shaded text
is valid only if your database is ANSI-compliant.

X/O This icon indicates that the functionality described in the shaded text
conforms to X/Open standards for dynamic SQL. This functionality
is available when you compile your embedded-language application
with the -xopen flag.

Figure 1 shows the elements of a syntax diagram for the CREATE DATABASE
statement.

6 IBM Informix SQL Quick Syntax Guide

 Syntax Conventions

Figure 1
Elements of a syntax diagram

Reference Boxes
Terminator
Signals
CREATE DATABASE database name

OL IN dbspace SE SE Log Clause

Variables OL Log Clause

Keywords OL

Punctuation
SE Log Clause

WITH LOG IN “pathname”

MODE ANSI
Subdiagrams
OL Log Clause

WITH LOG

BUFFERED

LOG MODE ANSI

To construct a statement using this diagram, start at the top left with the
keywords CREATE DATABASE. Then follow the diagram to the right,
proceeding through the options that you want. The diagram conveys the
following information:
1. You must type the words CREATE DATABASE.
2. You must supply a database name.
3. You can stop, taking the direct route to the terminator, or you can
take one or more of the optional paths.
4. If desired, you can designate a dbspace by typing the word IN and a
dbspace name.

Introduction 7
Syntax Conventions

5. If desired, you can specify logging. Here, you are constrained by the
database server with which you are working.
■ If you are using IBM Informix OnLine, go to the subdiagram
named OL Log Clause. Follow the subdiagram by typing the
keyword WITH, then choosing and typing either LOG, BUFFERED
LOG, or LOG MODE ANSI. Then, follow the arrow back to the
main diagram.
■ If you are using IBM Informix SE, go to the subdiagram named SE
Log Clause. Follow the subdiagram by typing the keywords
WITH LOG IN, typing a double quote, supplying a pathname,
and closing the quotes. You can then choose the MODE ANSI
option below the line or continue to follow the line across.
6. Once you are back at the main diagram, you come to the terminator.
Your CREATE DATABASE statement is complete.

8 IBM Informix SQL Quick Syntax Guide

 Chapter

SQL Statements
1
ALLOCATE DESCRIPTOR . . . . . . . . . . . . . . . . 1-5
ALTER INDEX . . . . . . . . . . . . . . . . . . . . 1-5
ALTER TABLE . . . . . . . . . . . . . . . . . . . . 1-6
BEGIN WORK . . . . . . . . . . . . . . . . . . . . 1-11
CHECK TABLE . . . . . . . . . . . . . . . . . . . . 1-11
CLOSE . . . . . . . . . . . . . . . . . . . . . . . 1-12
CLOSE DATABASE . . . . . . . . . . . . . . . . . . 1-12
COMMIT WORK . . . . . . . . . . . . . . . . . . . 1-12
CREATE AUDIT FOR . . . . . . . . . . . . . . . . . . 1-13
CREATE DATABASE . . . . . . . . . . . . . . . . . . 1-13
CREATE INDEX . . . . . . . . . . . . . . . . . . . . 1-14
CREATE PROCEDURE . . . . . . . . . . . . . . . . . 1-15
CREATE PROCEDURE FROM . . . . . . . . . . . . . . . 1-17
CREATE SCHEMA AUTHORIZATION . . . . . . . . . . . 1-17
CREATE SYNONYM . . . . . . . . . . . . . . . . . . 1-18
CREATE TABLE . . . . . . . . . . . . . . . . . . . . 1-19
CREATE VIEW . . . . . . . . . . . . . . . . . . . . 1-22
DATABASE . . . . . . . . . . . . . . . . . . . . . 1-22
DEALLOCATE DESCRIPTOR . . . . . . . . . . . . . . . 1-23
 DECLARE . . . . . . . . . . . . . . . . . . . . . . 1-24
DELETE FROM . . . . . . . . . . . . . . . . . . . . 1-25
DESCRIBE . . . . . . . . . . . . . . . . . . . . . . 1-25
DROP AUDIT FOR . . . . . . . . . . . . . . . . . . . 1-26
DROP DATABASE . . . . . . . . . . . . . . . . . . . 1-26
DROP INDEX. . . . . . . . . . . . . . . . . . . . . 1-26
DROP PROCEDURE . . . . . . . . . . . . . . . . . . 1-27
DROP SYNONYM . . . . . . . . . . . . . . . . . . . 1-27
DROP TABLE . . . . . . . . . . . . . . . . . . . . . 1-27
DROP VIEW . . . . . . . . . . . . . . . . . . . . . 1-28
EXECUTE . . . . . . . . . . . . . . . . . . . . . . 1-28
EXECUTE IMMEDIATE . . . . . . . . . . . . . . . . . 1-29
EXECUTE PROCEDURE . . . . . . . . . . . . . . . . . 1-29
FETCH . . . . . . . . . . . . . . . . . . . . . . . 1-30
FLUSH . . . . . . . . . . . . . . . . . . . . . . . 1-31
FREE . . . . . . . . . . . . . . . . . . . . . . . . 1-31
GET DESCRIPTOR . . . . . . . . . . . . . . . . . . . 1-32
GRANT . . . . . . . . . . . . . . . . . . . . . . . 1-33
INFO . . . . . . . . . . . . . . . . . . . . . . . . 1-35
INSERT INTO. . . . . . . . . . . . . . . . . . . . . 1-36
LOAD FROM . . . . . . . . . . . . . . . . . . . . . 1-38
LOCK TABLE . . . . . . . . . . . . . . . . . . . . . 1-39
OPEN . . . . . . . . . . . . . . . . . . . . . . . 1-39
OUTPUT TO . . . . . . . . . . . . . . . . . . . . . 1-40
PREPARE . . . . . . . . . . . . . . . . . . . . . . 1-40

1-2 IBM Informix SQL Quick Syntax Guide

PUT. . . . . . . . . . . . . . . . . . . . . . . . . 1-41
RECOVER TABLE . . . . . . . . . . . . . . . . . . . . 1-41
RENAME COLUMN . . . . . . . . . . . . . . . . . . . 1-42
RENAME TABLE . . . . . . . . . . . . . . . . . . . . 1-42
REPAIR TABLE. . . . . . . . . . . . . . . . . . . . . 1-42
REVOKE . . . . . . . . . . . . . . . . . . . . . . . 1-43
REVOKE . . . . . . . . . . . . . . . . . . . . . . . 1-44
ROLLBACK WORK . . . . . . . . . . . . . . . . . . . 1-44
ROLLFORWARD DATABASE . . . . . . . . . . . . . . . 1-44
SELECT . . . . . . . . . . . . . . . . . . . . . . . 1-45
SET CONSTRAINTS . . . . . . . . . . . . . . . . . . . 1-50
SET DEBUG FILE TO. . . . . . . . . . . . . . . . . . . 1-50
SET DESCRIPTOR. . . . . . . . . . . . . . . . . . . . 1-51
SET EXPLAIN . . . . . . . . . . . . . . . . . . . . . 1-52
SET ISOLATION TO . . . . . . . . . . . . . . . . . . . 1-52
SET LOCK MODE TO . . . . . . . . . . . . . . . . . . 1-53
SET . . . . . . . . . . . . . . . . . . . . . . . . . 1-53
SET OPTIMIZATION . . . . . . . . . . . . . . . . . . 1-53
START DATABASE . . . . . . . . . . . . . . . . . . . 1-54
UNLOAD TO . . . . . . . . . . . . . . . . . . . . . 1-54
UNLOCK TABLE . . . . . . . . . . . . . . . . . . . . 1-55
UPDATE . . . . . . . . . . . . . . . . . . . . . . . 1-56
UPDATE STATISTICS . . . . . . . . . . . . . . . . . . 1-57
WHENEVER . . . . . . . . . . . . . . . . . . . . . 1-58

SQL Statements 1-3

1-4 IBM Informix SQL Quick Syntax Guide
ESQL ALLOCATE DESCRIPTOR
Figure 1-1
ALLOCATE DESCRIPTOR

ALLOCATE
DESCRIPTOR " descriptor "

descriptor WITH MAX occurrences

variable
occurrences
variable

+ ALTER INDEX
Figure 1-2
ALTER INDEX

Index Name
ALTER INDEX p. 2-13 TO CLUSTER

NOT

SQL Statements 1-5

ALTER TABLE

+ ALTER TABLE
Figure 1-3
ALTER TABLE

ALTER TABLE Table Name ADD Clause

p. 2-19

Synonym DROP Clause

Name p. 1-9
p. 2-18
MODIFY Clause
p. 1-9

ADD CONSTRAINT
Clause p. 1-10

DROP CONSTRAINT
Clause p. 1-10
OL
MODIFY NEXT SIZE
Clause p. 1-11

LOCK MODE
Clause p. 1-11

ADD Clause
Add Column
ADD Clause p. 1-7

( Add Column )
Clause p. 1-7

1-6 IBM Informix SQL Quick Syntax Guide

 ALTER TABLE

Figure 1-3 (continued)

ALTER TABLE

Add Column
Clause

new
column Data
name Type
NOT
,
p. 2-7
DB NULL
Constraint
ESQL def. (Subset)

DEFAULT
Clause
column
BEFORE name

DEFAULT
Clause

DEFAULT literal

NULL

CURRENT
p. 2-10

DATETIME
Field Qualifier
p. 2-8

USER
p. 2-10
TODAY
OL p. 2-10

SITENAME
p. 2-10

DBSERVERNAME
p. 2-10

SQL Statements 1-7

ALTER TABLE

Figure 1-3 (continued)

ALTER TABLE

Constraint
def. (Subset)
UNIQUE

DB +
ESQL
Constraint
PRIMARY CONSTRAINT Name
KEY p. 2-5

REFERENCES
Clause

CHECK
Clause
p. 1-9

REFERENCES
Clause Table
REFERENCES Name
p. 2-19
,

( column )

1-8 IBM Informix SQL Quick Syntax Guide

 ALTER TABLE

Figure 1-3 (continued)

ALTER TABLE

CHECK Clause

CHECK ( Condition )
p. 2-3
DROP Clause

DROP column name

,

( column name )

MODIFY Clause
Modify Column
MODIFY Clause

( Modify Column )
Clause
Modify Column
Clause
column Data Type
name p. 2-7 ,
NOT
DB NULL
Constraint
ESQL def. (Subset)
p. 1-7
DEFAULT
Clause
p. 1-7

SQL Statements 1-9

ALTER TABLE

Figure 1-3 (continued)

ALTER TABLE

ADD
CONSTRAINT
Clause
Constraint
ADD CONSTRAINT Definition

( Constraint )
Definition

Constraint ,
Definition

UNIQUE ( column )
DB +
ESQL
Constraint
CONSTRAINT Name
PRIMARY
KEY p. 2-5
,
REFERENCES
FOREIGN ( column ) Clause
KEY p. 1-8

CHECK
Clause

DROP
CONSTRAINT
Clause
Constraint
DROP CONSTRAINT Name
p. 2-5
,

( Constraint )
Name
p. 2-5

1-10 IBM Informix SQL Quick Syntax Guide

 BEGIN WORK

Figure 1-3 (continued)

ALTER TABLE

MODIFY
NEXT SIZE
Clause
MODIFY NEXT SIZE kbytes

LOCK MODE
Clause
LOCK MODE ( PAGE )

ROW

+ BEGIN WORK
Figure 1-4
BEGIN WORK

BEGIN WORK

SE CHECK TABLE
DB

ISQL

Figure 1-5
CHECK TABLE

Table
CHECK TABLE Name
p. 2-19

SQL Statements 1-11

CLOSE

I4GL CLOSE
ESQL

Figure 1-6
CLOSE

cursor
CLOSE name

+ CLOSE DATABASE
Figure 1-7
CLOSE DATABASE

CLOSE DATABASE

COMMIT WORK
Figure 1-8
COMMIT WORK

COMMIT WORK

1-12 IBM Informix SQL Quick Syntax Guide

 CREATE AUDIT FOR

SE CREATE AUDIT FOR

+

Figure 1-9
CREATE AUDIT FOR

CREATE AUDIT FOR Table IN "pathname"

Name
p. 2-19

Synonym
Name
p. 2-18

+ CREATE DATABASE
Figure 1-10
CREATE DATABASE

CREATE Database
DATABASE Name
p. 2-6
OL IN dbspace SE SE Log Clause

OL OL Log Clause

SE Log Clause
WITH LOG IN "pathname"

MODE ANSI

OL Log Clause
WITH LOG

BUFFERED

LOG MODE ANSI

SQL Statements 1-13

CREATE INDEX

+ CREATE INDEX
Figure 1-11
CREATE INDEX

Index ON
CREATE INDEX Name Clause
p. 2-13
UNIQUE CLUSTER
DISTINCT

,
ON Clause
ON Table ( column name )
Name
p. 2-19
ASC
Synonym DESC
Name
p. 2-18

1-14 IBM Informix SQL Quick Syntax Guide

 CREATE PROCEDURE

DB CREATE PROCEDURE
ESQL

Figure 1-12 CREATE PROCEDURE

CREATE
PROCEDURE
Procedure ( ) Statement
PROCEDURE Name Block
p. 2-17 , p. 1-16
DBA RETURNING
Parameter Clause

END ;
PROCEDURE ,
WITH
DOCUMENT Quoted String LISTING IN " pathname "
p. 2-17

Parameter
variable SQL Data Type
name (Subset)
p. 2-7 default
value
LIKE table . column

REFERENCES BYTE

TEXT DEFAULT
NULL

,
RETURNING
Clause
RETURNING SQL Data Type ;
(Subset)
p. 2-7

REFERENCES BYTE

TEXT

SQL Statements 1-15

CREATE PROCEDURE

Figure 1-12 (continued) CREATE PROCEDURE

Statement Block

CALL
DEFINE ON Statement
Statement EXCEPTION p. 3-3
p. 3-4 Statement
p. 3-10 CONTINUE
Statement
p. 3-3
EXECUTE PROCEDURE
Statement
p. 1-29
EXIT
Statement
p. 3-5
FOR
Statement
p. 3-6
FOREACH
Statement
p. 3-7
IF
Statement
p. 3-8
LET
Statement
p. 3-10
RAISE EXCEPTION
Statement
p. 3-11
RETURN
Statement
p. 3-11
SYSTEM
Statement
p. 3-11
TRACE
Statement
p. 3-12
WHILE
Statement
p. 3-12
SQL Statement
Statement
BEGIN Block END

1-16 IBM Informix SQL Quick Syntax Guide

 CREATE PROCEDURE FROM

ESQL CREATE PROCEDURE FROM

+

Figure 1-13
CREATE PROCEDURE FROM

CREATE
PROCEDURE " filename "
FROM
ESQL
variable
name

DB CREATE SCHEMA AUTHORIZATION

ISQL

Figure 1-14
CREATE SCHEMA AUTHORIZATION

CREATE SCHEMA AUTHORIZATION user CREATE TABLE

name

+ CREATE INDEX
p. 1-14

CREATE VIEW
p. 1-22

+ CREATE SYNONYM

GRANT
p. 1-33

SQL Statements 1-17

 CREATE SYNONYM

+ CREATE SYNONYM
Figure 1-15
CREATE SYNONYM

Synonym
CREATE SYNONYM FOR Table Name
Name p. 2-19
p. 2-18
PUBLIC
View Name
p. 2-19
PRIVATE

1-18 IBM Informix SQL Quick Syntax Guide

 CREATE TABLE

CREATE TABLE
Figure 1-16
CREATE TABLE

, ,
CREATE Table Column Constraint
TABLE Name ( Definition Definition )
p. 2-19 p. 1-19 p. 1-21
Storage
Option
p. 1-21

, ,
temp Column Constraint
+ TEMP table ( Definition Definition )
TABLE name (Temp Table (Temp Table
Subset) Subset)
p. 1-19 p. 1-21

WITH NO LOG

Column Definition
Data Type
column p. 2-7

DB NOT NULL Constraint

def. (Subset)
ESQL

DEFAULT
Clause

SQL Statements 1-19

CREATE TABLE

Figure 1-16 (continued)

CREATE TABLE

DEFAULT Clause
DEFAULT literal

NULL

CURRENT
p. 2-10
DATETIME
Field Qualifier
p. 2-8

USER
p. 2-10

TODAY
p. 2-10
OL
SITENAME
p. 2-10

DBSERVERNAME
p. 2-10

Constraint
def. (Subset)

UNIQUE
DB +
ESQL Constraint
CONSTRAINT Name
PRIMARY p. 2-5
KEY

REFERENCES
Clause
p. 1-21

CHECK
Clause
p. 1-21

1-20 IBM Informix SQL Quick Syntax Guide

 CREATE TABLE

Figure 1-16 (continued)

CREATE TABLE

Constraint ,
Definitiion
UNIQUE ( column )
+
DB Constraint
CONSTRAINT Name
ESQL p. 2-5
PRIMARY ,
KEY

FOREIGN KEY ( column ) REFERENCES

Clause

CHECK
Clause

REFERENCES
Clause
Table
REFERENCES Name
p. 2-19 ,

( column )

CHECK Clause
CHECK ( Condition )
p. 2-3

Storage Option

OL

IN dbspace Extent Option LOCK MODE

SE IN "pathname"

SQL Statements 1-21

CREATE VIEW

Figure 1-16 (continued)

CREATE TABLE

Extent Option

EXTENT SIZE first NEXT SIZE next

kbytes kbytes

LOCK MODE
Clause
LOCK MODE

PAGE

ROW

CREATE VIEW
Figure 1-17
CREATE VIEW

View SELECT WITH CHECK

CREATE VIEW Name AS (Subset)
, OPTION
p. 2-19 p. 1-45
column
( name )

+ DATABASE
Figure 1-18
DATABASE

Database
DATABASE Name
p. 2-6
EXCLUSIVE

1-22 IBM Informix SQL Quick Syntax Guide

 DEALLOCATE DESCRIPTOR

ESQL+ DEALLOCATE DESCRIPTOR

Figure 1-19
DEALLOCATE DESCRIPTOR

DEALLOCATE descriptor
DESCRIPTOR " "
descriptor
variable

SQL Statements 1-23

DECLARE

I4GL DECLARE
ESQL

Figure 1-20
DECLARE

DECLARE cursor INSERT

id CURSOR FOR + Statement
+ (Subset)
ESQL p. 1-36
+ WITH
HOLD
cursor
variable SELECT
Statement FOR
+
+ (Subset) UPDATE
p. 1-45
SCROLL FOR ,
CURSOR
OF column
WITH
HOLD
SELECT
Statement
p. 1-45

statement id
ESQL
+ statement id
variable

EXECUTE
PROCEDURE
Statement
p. 1-29

1-24 IBM Informix SQL Quick Syntax Guide

 DELETE FROM

DELETE FROM
Figure 1-21
DELETE FROM

Table
DELETE FROM Name
p. 2-19
WHERE Condition
View p. 2-3
Name I4GL
p. 2-19 ESQL
cursor
Synonym CURRENT OF name
Name
p. 2-18

ESQL DESCRIBE
+

Figure 1-22
DESCRIBE

DESCRIBE USING
statement id SQL DESCRIPTOR " descriptor "
descriptor
statement E/C variable
id variable
INTO sqlda pointer

SQL Statements 1-25

DROP AUDIT FOR

SE DROP AUDIT FOR

+

Figure 1-23
DROP AUDIT FOR

Table Name
DROP AUDIT FOR p. 2-19

Synonym
Name
p. 2-18

+ DROP DATABASE
Figure 1-24
DROP DATABASE

Database
DROP DATABASE Name
p. 2-6

+ DROP INDEX
Figure 1-25
DROP INDEX

Index
DROP INDEX Name
p. 2-13

1-26 IBM Informix SQL Quick Syntax Guide

 DROP PROCEDURE

DB DROP PROCEDURE
ESQL

Figure 1-26
DROP PROCEDURE

DROP Procedure
PROCEDURE Name
p. 2-17

+ DROP SYNONYM
Figure 1-27
DROP SYNONYM

Synonym
DROP SYNONYM Name
p. 2-18

+ DROP TABLE
Figure 1-28
DROP TABLE

DROP TABLE Table

Name
p. 2-19

Synonym
Name
p. 2-18

SQL Statements 1-27

DROP VIEW

+ DROP VIEW
Figure 1-29
DROP VIEW

DROP VIEW View

Name
p. 2-19

Synonym
Name
p. 2-18

I4GL EXECUTE
ESQL

Figure 1-30
EXECUTE

EXECUTE statement id
,
ESQL
USING variable name
statement
id variable ESQL

SQL DESCRIPTOR " descriptor "

E/C descriptor variable

DESCRIPTOR sqlda pointer

1-28 IBM Informix SQL Quick Syntax Guide

 EXECUTE IMMEDIATE

I4GL EXECUTE IMMEDIATE

ESQL

Figure 1-31
EXECUTE IMMEDIATE

Quoted
EXECUTE String
IMMEDIATE p. 2-17

statement variable name

DB EXECUTE PROCEDURE
ESQL

Figure 1-32
EXECUTE PROCEDURE

EXECUTE Procedure ( )
PROCEDURE Name
p. 2-17 ,
ESQL
,
SPL
Argument
host
INTO variable

Argument
SPL
Expression
parameter p. 3-13
name =
SELECT
Statement
(singleton)
p. 1-45

SQL Statements 1-29

FETCH

I4GL FETCH
ESQL

Figure 1-33
FETCH

FETCH cursor id INTO data variable

+ ESQL
ESQL
NEXT + : indicator
cursor variable
variable
PREVIOUS indicator
indicator
variable
PRIOR +

FIRST
ESQL
LAST
USING SQL DESCRIPTOR "descriptor"
CURRENT
descriptor
RELATIVE row position E/C variable

+ DESCRIPTOR
sqlda
pointer
-
ABSOLUTE row position

1-30 IBM Informix SQL Quick Syntax Guide

 FLUSH

I4GL FLUSH
ESQL

Figure 1-34
FLUSH

cursor
FLUSH id
ESQL
cursor
variable

I4GL FREE
ESQL

Figure 1-35
FREE

FREE cursor
id
cursor
ESQL variable

statement
id

ESQL statement
id variable
I4GL blob
variable

SQL Statements 1-31

GET DESCRIPTOR

ESQL GET DESCRIPTOR

Figure 1-36
GET DESCRIPTOR

GET DESCRIPTOR "descriptor" host variable = count

descriptor ,
variable
VALUE item Described
number Item Info

item
number
variable
Described
Item Info
field host = TYPE
variable
LENGTH
PRECISION
SCALE
NULLABLE
INDICATOR
NAME
DATA
ITYPE
IDATA
ILEN

1-32 IBM Informix SQL Quick Syntax Guide

 GRANT

GRANT
Figure 1-37
GRANT

Database-
GRANT + Level TO PUBLIC
Privileges
p. 1-34 ,

user
Table-
Level Table PUBLIC
ON Name TO
Privileges
p. 1-34 p. 2-19

View
Name
p. 2-19

Synonym
Name
p. 2-18 ,
Procedure user
EXECUTE ON Name
p. 2-17

+
WITH GRANT OPTION + AS grantor

SQL Statements 1-33

GRANT

Figure 1-37 (continued)

GRANT

Database-Level
Privileges
CONNECT

RESOURCE

DBA
Table-Level
Privileges
ALL

PRIVILEGES

INSERT
DELETE
SELECT
,
+
( column )

UPDATE
,

+ ( column )

REFERENCES
,

( column )
INDEX

ALTER

1-34 IBM Informix SQL Quick Syntax Guide

 INFO

DB INFO
ISQL

Figure 1-38
INFO

INFO TABLES
Table
COLUMNS FOR Name
p. 2-19
INDEXES
ACCESS
PRIVILEGES
REFERENCES
STATUS

SQL Statements 1-35

INSERT INTO

INSERT INTO
Figure 1-39
INSERT INTO

Table VALUES Clause

INSERT INTO Name p. 1-37
p. 2-19 ,
View ( column )
Name name
p. 2-19 SELECT
Statement
Synonym (Subset)
Name p. 1-45
p. 2-18

Procedure ( )
EXECUTE PROCEDURE Name ,
p. 2-17

Argument

Argument
SPL
Expression
p. 3-13
parameter
name =
SELECT
Statement
(singleton)
p. 1-45

1-36 IBM Informix SQL Quick Syntax Guide

 INSERT INTO

Figure 1-39 (continued)

INSERT INTO

,
VALUES Clause
I4GL variable
( name
)
ESQL
ESQL

:indicator variable
$indicator variable

NULL

Literal Number
p. 2-16

Quoted String
p. 2-17

USER p. 2-10
+

Literal DATETIME
p. 2-15

Literal INTERVAL
p. 2-16

TODAY p. 2-10

CURRENT p. 2-10
OL
SITENAME p. 2-10

DBSERVERNAME p. 2-10

SQL Statements 1-37

LOAD FROM

I4GL LOAD FROM

DB

ISQL

Figure 1-40
LOAD FROM

LOAD FROM " filename "

I4GL
DELIMITER " delimiter "
filename
variable I4GL
delimiter
variable

Table
INSERT INTO Name
p. 2-19
,
Synonym
Name ( column )
p. 2-18

View
Name
p. 2-19
I4GL insert
variable

1-38 IBM Informix SQL Quick Syntax Guide

 LOCK TABLE

+ LOCK TABLE
Figure 1-41
LOCK TABLE

Table
LOCK TABLE Name IN SHARE MODE
p. 2-19
EXCLUSIVE
Synonym
Name
p. 2-18

I4GL OPEN
ESQL

Figure 1-42
OPEN

cursor
OPEN id

ESQL ,
cursor USING variable name
variable
ESQL

SQL DESCRIPTOR " descriptor "

E/C descriptor
variable

DESCRIPTOR sqlda
pointer

SQL Statements 1-39

OUTPUT TO

DB OUTPUT TO
ISQL

Figure 1-43
OUTPUT TO

SELECT
OUTPUT TO filename Statement
p. 1-45
WITHOUT
PIPE program HEADINGS

I4GL PREPARE
ESQL

Figure 1-44
PREPARE

statement Quoted
PREPARE id FROM String
p. 2-17
ESQL
statement variable
id variable name

1-40 IBM Informix SQL Quick Syntax Guide

 PUT

I4GL PUT
ESQL

Figure 1-45
PUT

cursor
PUT id
ESQL ,
ESQL

cursor FROM variable name

variable

USING SQL DESCRIPTOR "descriptor"

descriptor
E/C variable

sqlda
DESCRIPTOR pointer

SE RECOVER TABLE
+

Figure 1-46
RECOVER TABLE

Table
RECOVER TABLE Name
p. 2-19

SQL Statements 1-41

RENAME COLUMN

+ RENAME COLUMN
Figure 1-47
RENAME COLUMN

Table
RENAME COLUMN Name .old column TO new column
p. 2-19
owner.

+ RENAME TABLE
Figure 1-48
RENAME TABLE

RENAME TABLE old name TO new name

owner.

SE REPAIR TABLE
DB

ISQL

Figure 1-49
REPAIR TABLE

Table
REPAIR TABLE Name
p. 2-19

1-42 IBM Informix SQL Quick Syntax Guide

 REVOKE

+ REVOKE
Figure 1-50
REVOKE

Table- Table
REVOKE Level ON Name FROM PUBLIC
Privileges p. 2-19 ,
View user
Name
p. 2-19

Synonym
Name
p. 2-18

Procedure
EXECUTE ON Name
p. 2-17

Database-
Level
Privileges

Table-Level
Privileges
ALL

PRIVILEGES

INSERT

DELETE

SELECT

UPDATE

INDEX

ALTER
REFERENCES

SQL Statements 1-43

REVOKE

REVOKE
Figure 1-50 (continued)
REVOKE

Database-Level
Privileges
CONNECT

RESOURCE

DBA

ROLLBACK WORK
Figure 1-51
ROLLBACK WORK

ROLLBACK WORK

SE ROLLFORWARD DATABASE
+

Figure 1-52
ROLLFORWARD DATABASE

ROLLFORWARD Database
DATABASE Name
p. 2-6

1-44 IBM Informix SQL Quick Syntax Guide

 SELECT

SELECT
Figure 1-53
SELECT

UNION

UNION ALL
Select FROM
SELECT List Clause
p. 1-46 I4GL p. 1-47
ESQL
SPL
INTO
Clause
p. 1-46

WHERE GROUP BY HAVING

Clause Clause Clause
p. 1-48 p. 1-48 p. 1-49

+
ORDER BY INTO TEMP
Clause Clause
p. 1-49 p. 1-49

SELECT Clause
Select
SELECT List
p. 1-46

SQL Statements 1-45

SELECT

Figure 1-53 (continued)

SELECT

Select List
,
Expression
p. 2-9
ALL display
label
DISTINCT AS
+
UNIQUE
*
Table
Name .
p. 2-19

View
Name .
p. 2-19

Synonym
Name .
p. 2-18

,
INTO Clause
INTO data variable

ESQL

+ : indicator variable

INDICATOR indicator variable

1-46 IBM Informix SQL Quick Syntax Guide

 SELECT

Figure 1-53 (continued)

SELECT

FROM Clause
Table
FROM Name
p. 2-19 Additional
table , Tables
View alias p. 1-47
Name AS
p. 2-19

Synonym
Name
p. 2-18
Additional Tables
,
Table
Name
p. 2-19
table
View alias
+
Name AS
p. 2-19

Synonym
Name
p. 2-18
Table
OUTER Name
p. 2-19
table
View alias
Name AS
p. 2-19

Synonym
Name
p. 2-18
Table
OUTER ( Name )
p. 2-19
table , Additional
View alias Tables
Name
p. 2-19 AS

Synonym
Name
p. 2-18

SQL Statements 1-47

SELECT

Figure 1-53 (continued)

SELECT

AND
WHERE Clause
WHERE Condition
p. 2-3

Join
p. 1-48

Join
column Relational column
. name Operator .
name
p. 2-18
Table Table
Name Name
p. 2-19 p. 2-19

alias alias

View View
Name Name
p. 2-19 p. 2-19

Synonym Synonym
Name Name
p. 2-18 p. 2-18

,
GROUP BY Clause
GROUP BY column
name
Table .
Name
p. 2-19
View
Name .
p. 2-19
Synonym
Name .
p. 2-18

+ select
number

1-48 IBM Informix SQL Quick Syntax Guide

 SELECT

Figure 1-53 (continued)

SELECT

HAVING Clause
HAVING Condition
p. 2-3

,
ORDER BY Clause
column
ORDER BY name
Table . ASC
Name
p. 2-19 DESC
View
Name .
p. 2-19
Synonym
Name .
p. 2-18
select
number
display
label

INTO TEMP Clause

INTO TEMP temp table name

WITH NO LOG

SQL Statements 1-49

SET CONSTRAINTS

OL SET CONSTRAINTS
+

Figure 1-54
SET CONSTRAINTS

SET ALL
CONSTRAINTS IMMEDIATE

, DEFERRED

Constraint Name
p. 2-5

DB SET DEBUG FILE TO

ESQL

Figure 1-55
SET DEBUG FILE TO

SET DEBUG FILE " filename "

TO
variable name WITH APPEND
character
expression

1-50 IBM Informix SQL Quick Syntax Guide

 SET DESCRIPTOR

ESQL SET DESCRIPTOR

Figure 1-56
SET DESCRIPTOR

SET DESCRIPTOR " descriptor " COUNT = value

count
descriptor variable
variable
,
item Item
VALUE number Descriptor
Information
item
number
variable

Item Descriptor
Information
TYPE = literal integer

LENGTH integer-host
variable
PRECISION
SCALE
NULLABLE
INDICATOR
= Literal Number
DATA p. 2-16
ITYPE Literal DATETIME
IDATA p. 2-15
ILEN Literal INTERVAL
NAME p. 2-16
Quoted String
p. 2-17
data variable

SQL Statements 1-51

SET EXPLAIN

+ SET EXPLAIN
Figure 1-57
SET EXPLAIN

SET EXPLAIN

OFF

ON

OL SET ISOLATION TO
+

Figure 1-58
SET ISOLATION TO

SET ISOLATION TO DIRTY READ

COMMITTED READ

CURSOR STABILITY

REPEATABLE READ

1-52 IBM Informix SQL Quick Syntax Guide

 SET LOCK MODE TO

+ SET LOCK MODE TO

Figure 1-59
SET LOCK MODE TO

SET LOCK MODE TO WAIT

OL
seconds

NOT WAIT

OL SET
+

Figure 1-60
SET

SET LOG

BUFFERED

+ SET OPTIMIZATION
Figure 1-61
SET OPTIMIZATION

SET
OPTIMIZATION
HIGH

LOW

SQL Statements 1-53

START DATABASE

SE START DATABASE
+

Figure 1-62
START DATABASE

Database
START DATABASE Name WITH LOG IN "pathname"
p. 2-6
MODE ANSI

I4GL UNLOAD TO
DB

ISQL

Figure 1-63
UNLOAD TO

SELECT
UNLOAD TO " filename " statement
I4GL p. 1-45
DELIMITER " delimiter "
filename
variable I4GL
delimiter
variable

1-54 IBM Informix SQL Quick Syntax Guide

 UNLOCK TABLE

+ UNLOCK TABLE
Figure 1-64
UNLOCK TABLE

Table
UNLOCK TABLE Name
p. 2-19

Synonym
Name
p. 2-18

SQL Statements 1-55

UPDATE

UPDATE
Figure 1-65
UPDATE

Table
UPDATE Name SET SET Clause
p. 2-19
WHERE Condition
View p. 2-3
Name I4GL
p. 2-19
ESQL
Synonym cursor
Name CURRENT OF name
p. 2-18

SET Clause
,
column
name = Expression
(Subset)
p. 2-9

SELECT
Statement
(Subset)
p. 1-45
,
, ,
column
( name ) = ( Expression )
(Subset)
* p. 2-9
I4GL
Table SELECT
Name .* ( Statement )
p. 2-19 (Subset)
p. 1-45
View I4GL
Name .*
p. 2-19 record .*
variable
Synonym
Name .*
p. 2-18

1-56 IBM Informix SQL Quick Syntax Guide

 UPDATE STATISTICS

+ UPDATE STATISTICS
Figure 1-66
UPDATE STATISTICS

UPDATE
STATISTICS
FOR TABLE

Table
Name
p. 2-19

Synonym
Name
p. 2-18
FOR PROCEDURE

Procedure
Name
p. 2-17

SQL Statements 1-57

WHENEVER

I4GL WHENEVER
ESQL

Figure 1-67
WHENEVER

WHENEVER SQLERROR CONTINUE

NOT FOUND GO TO label

+ GOTO :label
+
SQLWARNING

I4GL WARNING STOP

I4GL function
CALL name
E/CO
E/CO
ERROR paragraph
PERFORM name
I4GL

ANY

1-58 IBM Informix SQL Quick Syntax Guide

 Chapter

SQL Segments
2
Condition . . . . . . . . . . . . . . . . . . . . . . 2-3
Constraint Name . . . . . . . . . . . . . . . . . . . 2-5
Database Name . . . . . . . . . . . . . . . . . . . . 2-6
Data Type . . . . . . . . . . . . . . . . . . . . . . 2-7
DATETIME Field Qualifier . . . . . . . . . . . . . . . . 2-8
Expression . . . . . . . . . . . . . . . . . . . . . . 2-9
Identifier . . . . . . . . . . . . . . . . . . . . . . 2-12
Index Name . . . . . . . . . . . . . . . . . . . . . 2-13
INTERNAL Field Qualifer . . . . . . . . . . . . . . . . 2-14
Literal DATETIME . . . . . . . . . . . . . . . . . . . 2-15
Literal INTERVALS. . . . . . . . . . . . . . . . . . . 2-16
Literal Number . . . . . . . . . . . . . . . . . . . . 2-16
Procedure Name. . . . . . . . . . . . . . . . . . . . 2-17
Quoted String . . . . . . . . . . . . . . . . . . . . 2-17
Relational Operator . . . . . . . . . . . . . . . . . . 2-18
Synonym Name . . . . . . . . . . . . . . . . . . . . 2-18
Table Name . . . . . . . . . . . . . . . . . . . . . 2-19
View Name . . . . . . . . . . . . . . . . . . . . . 2-19
2-2 IBM Informix SQL Quick Syntax Guide
 Condition
Figure 2-1
Condition

AND

OR
Condition Comparison
Condition
p. 2-4
NOT
IN
Subquery
p. 2-5

EXISTS
Subquery
p. 2-5

ALL/ANY/SOME
Subquery
p. 2-5

SQL Segments 2-3

Condition

Figure 2-1 (continued)

Condition

Comparison
Condition

Expression Relational Expression

p. 2-9 Operator p. 2-9
p. 2-18

Expression Expression AND Expression

p. 2-9 BETWEEN p. 2-9 p. 2-9
NOT ,

+ Expression ( Literal )
p. 2-9 IN Number
p. 2-16
NOT
Literal
Datetime
p. 2-15
Table Literal
Name . Interval
p. 2-19 p. 2-16

View Quoted
Name . String
p. 2-19 p. 2-17
TODAY
Synonym
Name .
p. 2-18 USER

CURRENT
Datetime
Field Qualifier
Table OL p. 2-8
Name .
p. 2-19 SITENAME
View
Name . DBSERVERNAME
p. 2-19

Synonym column
Name . name IS NULL
p. 2-18
NOT
column Quoted
name LIKE String
p. 2-17
+
NOT
MATCHES
ESCAPE "char"

2-4 IBM Informix SQL Quick Syntax Guide

 Constraint Name

Figure 2-1 (continued)

Condition

IN Subquery
Expression SELECT
p. 2-9 IN ( (Subset) )
p. 1-45
NOT

EXISTS Subquery
Expression SELECT
p. 2-9 EXISTS ( (Subset) )
p. 1-45
NOT

ALL/ANY/SOME
Subquery
Expression Relational SELECT
Operator ( (Subset) )
p. 2-9
p. 2-18 p. 1-45
ALL
ANY
SOME

Constraint Name
Figure 2-2
Constraint Name

Constraint Name
Identifier
p. 2-12
OL owner.

database

@server

SQL Segments 2-5

Database Name

Database Name
Figure 2-3
Database Name

Database Name
Identifier
p. 2-12
OL
OL dbserver-
@ name

"//dbservername/dbname"
I4GL
ESQL variable-name

SE "//dbservername/directory-path/dbname"

2-6 IBM Informix SQL Quick Syntax Guide

 Data Type

Data Type
Figure 2-4 Data Type

CHAR
Data
Type CHARACTER ( size )
(1)
+ DATE

+ DATETIME DATETIME Field Qualifier p. 2-8

DECIMAL

DEC ( precision )
NUMERIC 16 , scale
FLOAT
float
DOUBLE PRECISION ( precision )
INTEGER

INT
+ INTERVAL INTERVAL Field Qualifier p. 2-14

+ MONEY

( precision )
16 ,2
, scale
+ SERIAL

(1)
(start)
+ SMALLFLOAT

REAL
SMALLINT
OL
TEXT
+
BYTE IN TABLE

blobspace
OL
VARCHAR ( maximum )
+
, reserve
,0

SQL Segments 2-7

DATETIME Field Qualifier

DATETIME Field Qualifier

Figure 2-5
DATETIME Field Qualifier

DATETIME
Field Qualifier
YEAR

MONTH TO YEAR

DAY TO MONTH

HOUR TO DAY

MINUTE TO HOUR

SECOND TO MINUTE

FRACTION TO SECOND

TO FRACTION

(3)
(digit)

2-8 IBM Informix SQL Quick Syntax Guide

 Expression

Expression
Figure 2-6
Expression

+
-

*
/
||

Expression
Column
Expressions
p. 2-10
_
+ Constant
Expressions
p. 2-10

Function
Expressions
p. 2-11

Aggregate
Expressions
p. 2-12

variable
name

( Expression )

SQL Segments 2-9

Expression

Figure 2-6 (continued)

Expression

Column
Expressions
column
name
Table . +
Name
p. 2-19 [first, last]
alias .
ROWID

I4GL @

View
Name .
p. 2-19

Synonym
Name .
p. 2-18

Constant
Expressions
Quoted String
p. 2-17

USER
OL
SITENAME
+
DBSERVERNAME

Literal Number
p. 2-16
+

TODAY

CURRENT
DATETIME Field
Qualifier
p. 2-8
Literal DATETIME p. 2-15

Literal INTERVAL p. 2-16

n UNITS datetime
unit

2-10 IBM Informix SQL Quick Syntax Guide

 Expression

Figure 2-6 (continued)

Expression

date/
+ DAY ( datetime )
expression
Function MONTH
Expressions
WEEKDAY
YEAR

( non-date
DATE expression )

date/
EXTEND ( datetime )
expression
, first TO last
month day year
MDY ( integer , integer , integer )
expression expression expression

LENGTH ( Quoted String )

p. 2-17

I4GL variable
name
ESQL
column
name
Table Name .
p. 2-19

( integer )
HEX expression

( Expression )
ROUND p. 2-9
, digit
,0

( Expression )
TRUNC p. 2-9
, digit
,0

SQL Segments 2-11

Identifier

Figure 2-6 (continued)

Expression

Aggregate
Expressions

COUNT (*)
column
AVG ( DISTINCT name )
MAX UNIQUE
Table
Name
.
MIN p. 2-19
SUM
COUNT
Expression
AVG ( (Subset) )
p. 2-9
MAX ALL
MIN
SUM

Identifier
Figure 2-7
Identifier

Identifier
letter

letter

digit

underscore

2-12 IBM Informix SQL Quick Syntax Guide

 Index Name

Index Name
Figure 2-8
Index Name

Index Name
Identifier

OL owner.

database ;
@ dbservername

SQL Segments 2-13

INTERNAL Field Qualifer

INTERNAL Field Qualifer

Figure 2-9
INTERVAL Field Qualifier

INTERVAL
Field Qualifier
YEAR

(y-precision) TO YEAR
(4)
MONTH

(precision) TO MONTH
(2)

DAY

(precision) TO DAY
(2)
HOUR

(precision) TO HOUR
(2)
MINUTE

(precision) TO MINUTE
(2)
SECOND

(precision) TO SECOND
(2)
FRACTION TO FRACTION

(f-precision)
(3)

2-14 IBM Informix SQL Quick Syntax Guide

 Literal DATETIME

Literal DATETIME
Figure 2-10
Literal DATETIME

Literal DATETIME
Numeric DATETIME
DATETIME ( Date ) Field Qualifier
p. 2-8

Numeric Date

yyyy

-
mo

-
dd

space

hh

:
mi

:
ss

.
f

SQL Segments 2-15

Literal INTERVALS

Literal INTERVALS
Figure 2-11
Literal INTERVALS

Literal INTERVAL
INTERVAL
INTERVAL ( Numeric Date ) Field Qualifier
p. 2-14

Literal Number
Figure 2-12
Literal Number

Literal Number

digit

+ .
-
digit

. digit E digit

2-16 IBM Informix SQL Quick Syntax Guide

 Procedure Name

Procedure Name
Figure 2-13
Procedure Name

Procedure Name
Identifier
p. 2-12
OL owner.

database ;
@ dbservername

Quoted String
Figure 2-14
Quoted String

Quoted String
" "

character

""
' '

character

"

SQL Segments 2-17

Relational Operator

Relational Operator
Figure 2-15
Relational Operator

Relational Operator
<
<=
>
=
>=
<>
!=

Synonym Name
Figure 2-16
Synonym Name

Synonym Name
Identifier
p. 2-12
OL owner.

database ;
@ dbservername

2-18 IBM Informix SQL Quick Syntax Guide

 Table Name

Table Name
Figure 2-17
Table Name

Table Name
Identifier
p. 2-12
OL
owner.
+

database :
@ dbservername

View Name
Figure 2-18
View Name

View Name
Identifier
p. 2-12
OL owner.
+

database :
@ dbservername

SQL Segments 2-19

 Chapter

Stored Procedure Language

Statements 3
CALL . . . . . . . . . . . . . . . . . . . . . . . 3-3
CONTINUE . . . . . . . . . . . . . . . . . . . . . 3-3
DEFINE. . . . . . . . . . . . . . . . . . . . . . . 3-4
EXIT . . . . . . . . . . . . . . . . . . . . . . . . 3-5
FOR . . . . . . . . . . . . . . . . . . . . . . . . 3-6
FOREACH. . . . . . . . . . . . . . . . . . . . . . 3-7
IF . . . . . . . . . . . . . . . . . . . . . . . . . 3-8
LET . . . . . . . . . . . . . . . . . . . . . . . . 3-10
ON EXCEPTION . . . . . . . . . . . . . . . . . . . 3-10
RAISE EXCEPTION . . . . . . . . . . . . . . . . . . 3-11
RETURN . . . . . . . . . . . . . . . . . . . . . . 3-11
SYSTEM . . . . . . . . . . . . . . . . . . . . . . 3-11
TRACE . . . . . . . . . . . . . . . . . . . . . . . 3-12
WHILE . . . . . . . . . . . . . . . . . . . . . . . 3-12
SPL Expression . . . . . . . . . . . . . . . . . . . . 3-13
3-2 IBM Informix SQL Quick Syntax Guide
 CALL
Figure 3-1
CALL

LCALL Procedure
Name
( ) ;
p. 2-17
, ,
procedure
Argument RETURNING variable

Argument
SPL
Expression
p. 3-13
parameter
name =
SELECT
Statement
(Subset)
p. 1-45

CONTINUE
Figure 3-2
CONTINUE

CONTINUE FOR ;
WHILE

FOREACH

Stored Procedure Language Statements 3-3

DEFINE

DEFINE
Figure 3-3
DEFINE

,
DEFINE
GLOBAL variable SQL Data Type DEFAULT Default ;
name (Subset) Value
p. 2-7
DEFAULT
OL REFERENCES BYTE NULL

TEXT
,
variable SQL Data Type
name (Subset)
p. 2-7
OL REFERENCES BYTE

TEXT

Table Name .
LIKE p. 2-19 column

Synonym
Name
p. 2-19

View Name
p. 2-19

PROCEDURE

3-4 IBM Informix SQL Quick Syntax Guide

 EXIT

Figure 3-3 (continued)

DEFINE

Default Value
Literal Number
p. 2-16

Quoted String
p. 2-17

Literal Interval
p. 2-16

Literal Datetime
p. 2-15

CURRENT
p. 2-10
DATETIME
Field
Qualifier
p. 2-8

USER
TODAY

NULL
OL

DBSERVERNAME
SITENAME

EXIT
Figure 3-4
EXIT

EXIT FOR ;
WHILE

FOREACH

Stored Procedure Language Statements 3-5

FOR

FOR
Figure 3-5
FOR

FOR ,
variable Statement
name IN ( left TO right ) Block END
expression expression p. 1-16 FOR

increment
STEP expression

expression

= left TO right
expression expression

increment
STEP expression

3-6 IBM Informix SQL Quick Syntax Guide

 FOREACH

FOREACH
Figure 3-6
FOREACH

FOREACH SELECT...INTO Statement END

Statement Block FOREACH
p. 1-46 p. 1-16
WITH HOLD ;
cursor FOR
name
WITH HOLD

EXECUTE Procedure
PROCEDURE Name ( )
p. 2-17 ,
variable
INTO name

,
SPL
Expression
variable (Subset)
name = p. 3-13

Stored Procedure Language Statements 3-7

IF

IF
Figure 3-7
IF

IF

Condition THEN END IF

p. 2-3
IF Statement ;
List
p. 3-9

Condition IF Statement IF Statement

ELIF p. 2-3 THEN List ELSE List
p. 3-9 p. 3-9

3-8 IBM Informix SQL Quick Syntax Guide

 IF

Figure 3-7 (continued)

IF

IF Statement List
BEGIN Statement
Block END
p. 1-16
CALL
Statement
p. 3-3
CONTINUE
Statement
p. 3-3
EXIT
Statement
p. 3-5
FOR
Statement
p. 3-6
FOREACH
Statement
p. 3-7
IF
Statement
p. 3-8
LET
Statement
p. 3-10
RAISE EXCEPTION
Statement
p. 3-11
RETURN
Statement
p. 3-11
SYSTEM
Statement
p. 3-11
TRACE
Statement
p. 3-12
WHILE
Statement
p. 3-12

SQL Statement

Stored Procedure Language Statements 3-9

LET

LET
Figure 3-8
LET

,
LET
, ,
variable Procedure SPL
name = ( Expression ) ;
Name
p. 2-17 called p. 3-13
variable =

,
SPL
Expression
p. 3-13

ON EXCEPTION
Figure 3-9
ON EXCEPTION

ON EXCEPTION
Statement
Block END EXCEPTION
p. 1-16
, ;
IN ( error )
number

SQL WITH RESUME

SET error
variable ISAM
, error
variable error
, data
variable

3-10 IBM Informix SQL Quick Syntax Guide

 RAISE EXCEPTION

RAISE EXCEPTION
Figure 3-10
RAISE EXCEPTION

RAISE EXCEPTION SQL

error ;
ISAM
, error
error
, text

RETURN
Figure 3-11
RETURN

RETURN ;
,
SPL
Expression
p. 3-13
WITH RESUME

SYSTEM
Figure 3-12
SYSTEM

SYSTEM " character expression " ;

character variable

Stored Procedure Language Statements 3-11

TRACE

TRACE
Figure 3-13
TRACE

TRACE ON ;

OFF

PROCEDURE

SPL
Expression

WHILE
Figure 3-14
WHILE

Condition Statement
WHILE Block END WHILE
p. 2-3
p. 1-16
;

3-12 IBM Informix SQL Quick Syntax Guide

 SPL Expression

SPL Expression
Figure 3-15
SPL Expression

SPL Expression
Expression
p. 2-9
||
procedure +
variable
name -
*
/ ( SPL )
Expression

,
Procedure
Name ( SPL )
p. 2-17 Expression
called =
variable

Stored Procedure Language Statements 3-13

 Appendix

Notices
A
IBM may not offer the products, services, or features discussed
in this document in all countries. Consult your local IBM repre-
sentative for information on the products and services currently
available in your area. Any reference to an IBM product,
program, or service is not intended to state or imply that only
that IBM product, program, or service may be used. Any
functionally equivalent product, program, or service that does
not infringe any IBM intellectual property right may be used
instead. However, it is the user’s responsibility to evaluate and
verify the operation of any non-IBM product, program, or
service.
IBM may have patents or pending patent applications covering
subject matter described in this document. The furnishing of this
document does not give you any license to these patents. You can
send license inquiries, in writing, to:

IBM Director of Licensing

IBM Corporation
North Castle Drive
Armonk, NY 10504-1785
U.S.A.

For license inquiries regarding double-byte (DBCS) information,

contact the IBM Intellectual Property Department in your
country or send inquiries, in writing, to:

IBM World Trade Asia Corporation

Licensing
2-31 Roppongi 3-chome, Minato-ku
Tokyo 106-0032, Japan
 The following paragraph does not apply to the United Kingdom or any
other country where such provisions are inconsistent with local law:
INTERNATIONAL BUSINESS MACHINES CORPORATION PROVIDES THIS
PUBLICATION “AS IS” WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESS
OR IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES
OF NON-INFRINGEMENT, MERCHANTABILITY OR FITNESS FOR A
PARTICULAR PURPOSE. Some states do not allow disclaimer of express or
implied warranties in certain transactions, therefore, this statement may not
apply to you.

This information could include technical inaccuracies or typographical

errors. Changes are periodically made to the information herein; these
changes will be incorporated in new editions of the publication. IBM may
make improvements and/or changes in the product(s) and/or the
program(s) described in this publication at any time without notice.

Any references in this information to non-IBM Web sites are provided for
convenience only and do not in any manner serve as an endorsement of those
Web sites. The materials at those Web sites are not part of the materials for
this IBM product and use of those Web sites is at your own risk.

IBM may use or distribute any of the information you supply in any way it
believes appropriate without incurring any obligation to you.

Licensees of this program who wish to have information about it for the
purpose of enabling: (i) the exchange of information between independently
created programs and other programs (including this one) and (ii) the mutual
use of the information which has been exchanged, should contact:

IBM Corporation
J46A/G4
555 Bailey Avenue
San Jose, CA 95141-1003
U.S.A.

Such information may be available, subject to appropriate terms and condi-

tions, including in some cases, payment of a fee.

The licensed program described in this information and all licensed material
available for it are provided by IBM under terms of the IBM Customer
Agreement, IBM International Program License Agreement, or any equiv-
alent agreement between us.

A-2 IBM Informix SQL Quick Syntax Guide

Information concerning non-IBM products was obtained from the suppliers
of those products, their published announcements or other publicly available
sources. IBM has not tested those products and cannot confirm the accuracy
of performance, compatibility or any other claims related to non-IBM
products. Questions on the capabilities of non-IBM products should be
addressed to the suppliers of those products.

This information contains examples of data and reports used in daily

business operations. To illustrate them as completely as possible, the
examples include the names of individuals, companies, brands, and
products. All of these names are fictitious and any similarity to the names
and addresses used by an actual business enterprise is entirely coincidental.
COPYRIGHT LICENSE:
This information contains sample application programs in source language,
which illustrate programming techniques on various operating platforms.
You may copy, modify, and distribute these sample programs in any form
without payment to IBM, for the purposes of developing, using, marketing
or distributing application programs conforming to the application
programming interface for the operating platform for which the sample
programs are written. These examples have not been thoroughly tested
under all conditions. IBM, therefore, cannot guarantee or imply reliability,
serviceability, or function of these programs. You may copy, modify, and
distribute these sample programs in any form without payment to IBM for
the purposes of developing, using, marketing, or distributing application
programs conforming to IBM’s application programming interfaces.

Each copy or any portion of these sample programs or any derivative work,
must include a copyright notice as follows:

© (your company name) (year). Portions of this code are derived

from IBM Corp. Sample Programs. © Copyright IBM Corp. (enter the
year or years). All rights reserved.

If you are viewing this information softcopy, the photographs and color illus-
trations may not appear.

Notices A-3
Trademarks

Trademarks
AIX; DB2; DB2 Universal Database; Distributed Relational Database
Architecture; NUMA-Q; OS/2, OS/390, and OS/400; IBM Informix;
C-ISAM; Foundation.2000TM; IBM Informix 4GL; IBM Informix
DataBlade Module; Client SDKTM; CloudscapeTM; CloudsyncTM;
IBM Informix Connect; IBM Informix Driver for JDBC; Dynamic
ConnectTM; IBM Informix Dynamic Scalable ArchitectureTM (DSA);
IBM Informix Dynamic ServerTM; IBM Informix Enterprise Gateway
Manager (Enterprise Gateway Manager); IBM Informix Extended Parallel
ServerTM; [Link] ServicesTM; J/FoundationTM; MaxConnectTM; Object
TranslatorTM; Red Brick Decision ServerTM; IBM Informix SE;
IBM Informix SQL; InformiXMLTM; RedBack; SystemBuilderTM; U2TM;
UniData; UniVerse; wintegrate are trademarks or registered trademarks
of International Business Machines Corporation.

Java and all Java-based trademarks and logos are trademarks or registered
trademarks of Sun Microsystems, Inc. in the United States and other
countries.

Windows, Windows NT, and Excel are either registered trademarks or trade-
marks of Microsoft Corporation in the United States and/or other countries.

UNIX is a registered trademark in the United States and other countries

licensed exclusively through X/Open Company Limited.

Other company, product, and service names used in this publication may be
trademarks or service marks of others.

A-4 IBM Informix SQL Quick Syntax Guide