INFORMIX
Informix Guide to SQL: Syntax
chapter_name
Home Contents Index Master Index New Book

Index

Collection variable
See also Literal, Collection; Constant, Collection.

A

ABS function 1-908
Access control. See Privilege, database-level, Connect.
Access method
CREATE TABLE option 1-255
CREATE TABLE with 1-255
index 1-138
name clause 1-256
primary 1-255
secondary 1-138
SQL statements for 1-14
ACOS function 1-937, 1-939
Active set
constructing with OPEN 1-530, 1-531
deallocating 1-94
of sequential cursor 1-414
retrieving data with FETCH 1-411
scroll cursor 1-317, 1-416
sequential cursor 1-316
Aggregate expression. See Aggregate function;Expression segment.
Aggregate function
behavior of, example 1-956
error checking
counting selected rows with COUNT 1-958
empty rows (indicator variable) 1-958
error checking with external (ESQL) function 1-958
restrictions 1-948
restrictions with GROUP BY 1-631
rowid not allowed with 1-890
Aggregate functions 1-946 to 1-960
Algebraic functions 1-905 to 1-911
Algorithm
for adding columns to tables 1-59
Alias
SELECT with 1-599, 1-610
SELECT...AS with 1-613
use with GROUP BY clause 1-630, 1-634
ALLOCATE DESCRIPTOR statement 1-27
syntax 1-22
WITH MAX clause 1-25
Allocating memory
with ALLOCATE COLLECTION 1-22
ALS. See Asian Language Support.
ALTER FRAGMENT statement
ADD Clause 1-46
ATTACH clause 1-32 to 1-36
example 1-36
DETACH clause 1-37
DROP clause 1-48
effects on large objects, triggers, constraints, indexes 1-33
INIT clause 1-38
logging 1-31
MODIFY clause 1-49
privileges 1-30
ALTER INDEX 1-52
ALTER INDEX statement
creating clustered index 1-52
dropping clustered index 1-53
TO CLUSTER clause 1-53
ALTER TABLE
DEFAULT clause 1-61
ALTER TABLE statement
ADD clause 1-58
ADD CONSTRAINT clause 1-81
ADD TYPE clause 1-86
BEFORE option 1-74
CHECK clause 1-73
constraint mode definition 1-65
DROP clause 1-75
DROP CONSTRAINT clause 1-85
LOCK MODE clause 1-88
ON DELETE CASCADE option 1-71
PAGE keyword 1-88
privileges for See Privilege, table-level.
REFERENCES clause 1-68
ROW keyword 1-88
ROWIDS clause 1-88
setting columns NOT NULL 1-64
American National Standards Institute. See ANSI.
AND logical operator 1-853
ANSI compliance
-ansi flag Intro-14, 1-206, 1-214, 1-291
icon Intro-12
level Intro-21
reserved words 1-968
SQL statements 1-19
SQLSTATE variable 1-444
table naming 1-572
updating rows 1-782
ANSI-compliant database
BEGIN WORK with 1-92
CHARACTER VARYING data type 1-863
constraint naming 1-855
criteria for 1-118
cursor in 1-310, 1-311, 1-313
function naming 1-964, 1-1009
index naming 1-985
opaque-type naming 1-168
operator-class naming 1-175
procedure name 1-472, 1-583, 1-816
SQLNOTFOUND 1-396
synonym naming 1-1047
table name with 1-1049
table privileges 1-221
USER function 1-895
view naming 1-1052
API.See Application programming interface (API).
Application
single-threaded 1-687
thread-safe 1-687, 1-690
Arbitrary rule 1-160
Argument 1-829 to 1-831
Arithmetic functions. See Algebraic functions.
Arithmetic operator
binary 1-959
expression with 1-959
unary 1-960
Array
moving rows into with FETCH 1-418
ASCII code set 1-1022
ASIN function 1-939
Assign support function 1-172, 1-503, 1-518, 1-523, 1-793
ATAN function 1-939
ATAN2 function 1-939
ATAN, ATAN2 functions 1-939
AUTOFREE feature 1-677
Automatic type conversion. See Data type conversion.
AVG function 1-953

B

Backslash. See\, backslash.
Backup. See Archiving.
Base data type
See Opaque data type.
BEGIN WORK statement 1-91
Binary Large Object (BLOB)
effect of isolation on retrieval 1-726, 1-747
Bindary data type. See BLOB data type; BYTE data type; Smart large object; Simple large object.
BLOB data type 1-870
copying to a file 1-928
copying to a smart large object 1-930
creating from a file 1-926
handle values 1-890
storage (PUT) location for 1-250
syntax 1-869
unloading 1-771, 1-773
See also Smart large object.
blob.See Simple large object.
BOOLEAN data type
unloading 1-770
Btree cleaner list 1-815
B-tree index
btree_ops operator class 1-179
default operator class 1-179
sort order options 1-145
uses 1-151
B-tree secondary access method 1-151
Built-in data type
owner 1-122
syntax 1-861 to 1-868
See also each data type listed under its own name.
BYTE data type 1-870
loading 1-518
referencing 2-18
referencing in SPL routine 2-11
unloading 1-771, 1-773
with SET DESCRIPTOR 1-712
See also Simple large object.

C

C omments symbol. See--, double dash; {}, curly brackets.
Calculated expression, restrictions with GROUP BY 1-631
CALL statement 2-4
CARDINALITY function 1-912
Caret. See ^, caret.
Cascading deletes 1-330
adding with ALTER TABLE 1-71
ALTER TABLE...ON DELETE CASCADE 1-71
CREATE TABLE example 1-239
CREATE TABLE with 1-239
disallowing with REVOKE REFERENCES 1-580
locking associated with 1-240
logging 1-240
multiple child tables 1-240
privileges 1-240
restriction 1-240
See alsoCREATE TABLE statement;DELETE sstatement.
trigger restriction 1-260
Cascading triggers 1-281
effect on a triggering table 1-278
Cast
CREATE CAST 1-112
DROP CAST 1-350
explicit 1-114
syntax 1-885
function for 1-116
implicit 1-115
privileges 1-113
registering 1-112
system-defined 1-115
DROP CAST restrictions 1-351
CHAR data type 1-863
greater than condition 1-1020
unloading 1-770
CHARACTER data type
ANSI compliancy 1-863
See CHAR data type.
Character data type
fixed and varying length 1-863
fixed length
ANSI compliancy 1-863
length functions 1-921
subscripting substring 1-889
syntax 1-862
varying length in ANSI-compliant database 1-863
See also CHAR; CHARACTER; LVARCHAR; VARCHAR; CHARACTER; VARYING data types.
Character variables
subscripting 1-847
CHARACTER VARYING data type
in ANSI-compliant database 1-863
syntax 1-862
CHARACTER_LENGTH function 1-921
CHARACTER_LENGTH function. See CHAR_LENGTH function.
CHAR_LENGTH function 1-921
Check constraint
adding with ALTER TABLE..CHECK 1-73
renamed column with 1-569
trigger and 1-282
CLASS routine modifier 1-1030
CLIENT routine modifier 1-1030
Client/server
environment 1-284
CLOB data type 1-870
copying to a file 1-928
copying to a smart large object 1-930
creating from a file 1-926
handle values 1-890
syntax 1-869
unloading 1-771, 1-773
See also Smart large object.
CLOSE DATABASE statement 1-97
prerequisite actions 1-97
CLOSE statement 1-93
collection cursor 1-95
function cursor 1-94
insert cursor 1-94
select cursor 1-94
Collating sequence. See Collation order.
Collation order
with relational operators 1-1021
See alsoGlobal Language Support (GLS); Guide to GLS Functionality
Collection
nested 1-993
Collection cursor 1-320 to 1-325
closing 1-95
DECLARE for ESQL/C variable 1-320
deleting elements 1-332
FOR EACH with 2-30
inserting into 1-422, 1-563
opening 1-533
updating elements 1-797
Collection data type 1-876
deleting 1-329, 1-332, 1-333
element
searching for with IN 1-844
IN operator 1-844
loading 1-522
MULTISET 1-877
returning number of elements 1-912
selecting from 1-613
SET 1-877
unloading 1-770
updating 1-791, 1-795, 1-797, 1-798
Collection derived table 1-832 to 1-835
collection cursor 1-422, 1-563
collection variables with 1-833
DECLARE statement with 1-833
DELETE statement with 1-833
DELETE with 1-333, 1-337
FETCH statement with 1-833
FOREACH statement 1-833
INSERT statement with 1-833
INSERT with 1-324, 1-509, 1-514
PUT statement with 1-833
row variables with 1-835
SELECT statement with 1-833, 1-835
SELECT with 1-613
fields from row variable 1-617
TABLE keyword 1-832, 1-833, 1-835
UPDATE row variable with 1-803
UPDATE statement with 1-833, 1-835
UPDATE with 1-798
Collection data type
See also LIST data type; MULTISET data type; SET data type.
Collection variable
accessing 1-833
allocating memory for 1-22
cursor for 1-422
deallocating memory for 1-297
deleting 1-332, 1-333
describing 1-344
inserting 1-509
inserting elements into 1-510
opening a cursor 1-533
selecting from 1-613
selecting, inserting elements 1-320 to 1-325
updating 1-785, 1-797, 1-798
Colon. See:, colon; :: , cast operator.
Column
algorithms for adding 1-59
check constraint for 1-234
comparisons 1-840
constraints 1-226
dropping 1-75
INFO...COLUMNS with 1-491
inserting into 1-497
length 1-341
modifying with ALTER TABLE 1-77
name. SeeColumn name.
primary or foreign key 1-232
privileges 1-468
projection 1-888
referenced and referencing 1-70, 1-236
renaming 1-568
subscript, substring 1-636
value. SeeColumn value.
virtual 1-291
Column definition clause 1-222
Column expression 1-886
determining dynamically 1-341
Column name
determining dynamically 1-341
dot notation 1-888
functions as names 1-973
label for 1-978
rowid as 1-977
when qualified 1-274
workaround for keyword 1-978
'all', 'unique', 'distinct' as 1-975
'interval', 'datetime' as 1-976
'null', `select' 1-981
'as', 'from' as 1-979
Column value
in a SET clause 1-788
in triggered action 1-276
INSERT statement considerations 1-502
qualified vs. unqualified 1-276
Column-level properties. See Constraints
column-level.
Command script file
comment symbols in 1-12
defined 1-12
Comment icons Intro-10
Comment symbol
double dash (--) 1-1045
how to enter 1-11
in application programs 1-12
in prepared statements 1-544
SPL routine 1-1045
COMMIT WORK statement
ANSI-compliancy 1-100
cursor with 1-319
syntax 1-99
Committed Read isolation level (Informix) 1-724
Compare support function 1-173
Complex data type
loading element values 1-522
unloading 1-775
Complex data type. See Collection data type; Row type.
Compliance
icons Intro-12
with industry standards Intro-21
Composite index 1-153
Compound assignment 2-40
Concatenation operator. See ||, concatination operator; concat() operator function.
concat() operator function 1-884
Concurrency
access modes 1-746
ANSI Read Committed isolation 1-745
ANSI Read Uncommitted isolation 1-745
ANSI Repeatable Read isolation 1-745
ANSI Serializable isolation 1-745
defining with SET ISOLATION 1-722
defining with SET TRANSACTION 1-746
Informix Committed Read isolation 1-724
Informix Cursor Stability isolation 1-724
Informix Dirty Read isolation 1-724
Informix Repeatable Read isolation 1-724
Condition 1-836, 1-836 to 1-854
ALL/ANY/SOME subquery 1-621, 1-851
AND operator 1-853
BETWEEN operator 1-842
comparing a range of values 1-847
comparison 1-837
ESCAPE character substitution 1-848
examples using special characters 1-846
value range delimiters 1-847
EXISTS subquery 1-621, 1-851
IN operator 1-843
IN subquery 1-621, 1-850
LIKE comparator 1-846
wildcards 1-845
logical operators in 1-853
MATCHES comparator 1-847
wildcards 1-845
NOT IN operator 1-843
NOT operator with 1-837, 1-846
null values 1-837, 1-854
OR operator 1-853
quotation marks in 1-840
relational operators 1-620
relational operators in 1-841
restrictions 1-836
subquery in 1-849 to 1-853
subquery with 1-621
subscript delimiters 1-847
Condition segment 1-836
join conditions 1-627
Condtion
boolean. SeeCondtion, comparison.
Connect privilege. See Privilege, database-level.
CONNECT statement 1-101 to 1-111
connection context 1-103
connection identifiers 1-103
database environment specification 1-106 to 1-109
DEFAULT option 1-103
implicit connections 1-104
switching connections 1-105
transactions with 1-105
USER clause 1-109
WITH CONCURRENT TRANSACTION option 1-105
Connecting to data
sqlhosts file and sqlexecd daemon 1-109
Connection
context 1-103, 1-347, 1-686
current 1-102, 1-348, 1-690
default 1-104, 1-347, 1-689
dormant 1-102, 1-347, 1-686
identifiers 1-103
implicit 1-104, 1-347, 1-689
Constant
comparisons 1-840
UNITS keyword with 1-899
Constant expression
inserting with PUT 1-558
restrictions with GROUP BY 1-631
Constraint
adding with ALTER TABLE 1-80
adding with object modes 1-663 to 1-669
altering 1-228
column-level 1-230
composite column list 1-83
definition of 1-226
dropping
effect on foreign key 1-85
dropping a column with 1-76
dropping with ALTER TABLE 1-85
duplicate indexes 1-84
enforcing 1-228
limit on size 1-226
mixing 1-83
modifying a column that has constraints 1-77
names 1-228
viewing (sysconstraints) 1-85
naming conventions 1-856
number of columns allowed 1-226
object mode
disabled example 1-660
filtering example 1-660
privileges
See also Privilege,table-level.
shared unique constraint index 1-83
table-level 1-231
transaction mode 1-672
unique 1-83
with DROP INDEX 1-358
See also Check constraint; Not null constraint;Primary key constraint;Referential constraint; Referntial integrity.
Constraint mode
definition, in CREATE TABLE 1-241
restriction 1-229
types of 1-242
using 1-243
Constraint Name segment
syntax 1-855
Constructor function 1-900
CONTINUE statement 2-7
Correlated subquery
cascading deletes with 1-240
Correlated subquery. See Subquery, correlated.
Correlation name
in triggered action 1-274
rules for 1-274
scope of 1-275
table of values 1-276
use with a routine 1-279
when to use 1-275
COS function 1-937, 1-938
COUNT field
with DESCRIBE 1-342
with GET DESCRIPTOR 1-435
with SET DESCRIPTOR 1-705
COUNT function 1-949
with column name argument 1-952
with DISTINCT keyword 1-951
with (*) argument 1-949
COUNT functions 1-949
CREATE CAST statement 1-112 to 1-116
EXPLICIT keyword 1-114
IMPLICIT keyword 1-115
WITH clause 1-116
CREATE DATABASE statement 1-117
ANSI compliance 1-118
logging 1-119
restrictions 1-118
WITH BUFFERED LOG option 1-119
WITH LOG clause 1-119
CREATE DISTINCT TYPE statement 1-121
CREATE FUNCTION statement 1-125 to 1-133
DBA keyword 1-129
DOCUMENT clause 1-131
External Routine Reference 1-131
Procedure List 1-1033
Return clause 1-130, 1-1025
WITH LISTING IN clause 1-132
CREATE INDEX statement 1-137 to 1-166
ASC keyword 1-145
CLUSTER keyword 1-139
composite indexes 1-153
DESC keyword 1-145
DISTINCT keyword 1-139
FILLFACTOR clause 1-156
fragment by expression 1-158
implicit table locks 1-138
IN dbspace clause 1-157
Index Definition clause 1-140
object modes 1-161 to 1-165
storage option 1-157
UNIQUE keyword 1-139
USING clause 1-151
CREATE OPAQUE TYPE statement 1-167 to 1-173
ALIGNMENT modifier 1-170
CANNOTHASH modifier 1-170
INTERNALLENGTH modifier 1-168
MAXLEN modifier 1-170
Modifier clause 1-169
PASSEDBYVALUE modifier 1-170
VARIABLE keyword 1-169
CREATE OPCLASS statement 1-174 to 1-180
STRATEGY clause 1-176
SUPPORT clause 1-178
CREATE PROCEDURE FROM statement 1-134, 1-191
CREATE PROCEDURE statement 1-181 to 1-190
DBA keyword 1-185
DOCUMENT clause 1-188
External Routine Reference 1-187
Procedure List 1-1033
Return clause 1-186
WITH LISTING IN clause 1-188
CREATE ROLE statement 1-193
CREATE ROUTINE FROM statement 1-195
CREATE ROW TYPE statement
extended field definition 1-202
field definition 1-203
field definition syntax 1-876
syntax 1-197
UNDER clause 1-200
CREATE SCHEMA statement 1-204
defining a trigger 1-259
GRANT with 1-206
CREATE SYNONYM statement 1-207 to 1-210
chaining synonyms 1-209
synonym for a table 1-207
synonym for a view 1-207
CREATE TABLE statement 1-211 to 1-257
access method option 1-255
cascading deletes 1-239
CHECK clause 1-233
column definition clause 1-222
constraint definition
column-level 1-230
table-level 1-231
constraint mode definition 1-241
DEFAULT clause 1-223
FRAGMENT BY clause 1-247
fragmentation by expression with 1-249
IN dbspace clause 1-246
LOCK MODE clause 1-254
ON DELETE CASCADE keywords 1-235, 1-239
options clauses 1-243
PUT clause 1-250
REFERENCES clause 1-235
setting columns to NOT NULL 1-224
storage option 1-245
WITH NO LOG keywords 1-244
WITH ROWIDS keywords 1-244
CREATE TRIGGER statement 1-258 to 1-288
action clause referencing 1-270
AFTER action keyword 1-265
CREATE SCHEMA with 1-259
DISABLED/ENABLED keywords 1-286
ESQL/C restriction 1-259
FOR EACH ROW action 1-265
INSERT REFERENCING clause 1-267
privileges for 1-259
trigger object modes 1-286
triggered action list 1-271
UPDATE clause 1-262
UPDATE REFERENCING clause 1-269
WHEN condition 1-271
CREATE VIEW statement 1-289 to 1-294
column data types 1-290
privileges 1-290
virtual column 1-291
WITH CHECK OPTION
1-292
with SELECT * notation 1-290
Current database, specifying with DATABASE 1-295
CURRENT function 1-844, 1-897
CREATE TABLE with 1-223
example 1-506
CURRENT OF keywords
in DELETE 1-331
in UPDATE 1-794
Cursor
characteristics 1-316
CLOSE statement 1-93
closing 1-93 to 1-96, 1-684
ROLLBACK WORK effects 1-594
declaring multiple 1-305
dynamic 1-306
fetching values 1-411
freeing automatically with SET AUTOFREE 1-676
host variable for 1-305
locking considerations 1-312
manipulation statements 1-15
maximum number of 1-305
modes 1-309
name restriction 1-980
OPEN statement for 1-528 to 1-538
opening 1-529, 1-698
optimizing 1-676, 1-698
prepared statement with 1-319
restricted statements 1-314
scope of 1-305
select hold examples 1-318
sequential function 1-414
sequential select 1-414
statement identifier with 1-319
transaction end with 1-95
transaction with 1-325
example 1-326
guidelines 1-325
trigger with 1-260
types of 1-306
Cursor function 2-28
Cursor name
'execute', 'select', 'with' as 1-983
Cursor Stability isolation level (Informix) 1-724
Cursor. SeeCollection cursor; Function cursor; Hold cursor.; Insert cursor; > Read-only cursor; Scroll cursor; Select cursor; Sequential cursor; Update cursor.

D

Data
inserting values 1-499
loading into database 1-515
unloading from database 1-769
Data conversion. See Data type, conversion.
Data definition language statements 1-16
Data distributions
confidence 1-814
on temporary tables 1-811
RESOLUTION 1-814
UPDATE STATISTICS (HIGH) 1-814
UPDATE STATISTICS (MEDIUM) 1-814
DATA field
and INDICATOR field 1-710
and TYPE field 1-709
syntax 1-433
with DATETIME and INTERVAL types 1-710
with DESCRIBE 1-342
with SET DESCRIPTOR 1-709
Data integrity
object modes 1-669
object modes with 1-663 to 1-669
statements 1-17
Data manipulation language
SQL statements for 1-17
Data manipulation statements
object modes with 1-657 to 1-663
Data model
See also Relational model.
Data type 1-860 to 1-880
casting 1-112, 1-885
changing with ALTER TABLE 1-79
collection 1-876
complex 1-874
constructing an instance of 1-900
determining dynamically 1-341
distinct 1-873
formatting loaded values 1-517
INSERT considerations 1-502, 1-1018
opaque 1-167
referential constraints rules 1-71
simple large object 1-870
view inherits 1-290
See also each data type listed under its own name.
Data Type segment 1-860
Database
access control. See Privilege, database-level.
closing with CLOSE DATABASE 1-97
creating with CREATE DATABASE 1-118
default isolation levels 1-725, 1-746
dropping with DROP DATABASE 1-352
exception codes 1-442
loading data into 1-515
lock 1-296
naming conventions 1-858
naming with variable 1-859
opening in exclusive mode 1-296
optimizing queries 1-809
privileges. See Privilege, database-level.
remote 1-859
renaming 1-571
unloading data from 1-769
Database administrator (DBA)
privileges
granting 1-462
list of 1-464
revoking 1-577
Database Name segment
for remote database 1-859
naming conventions 1-857
naming with variable 1-859
syntax 1-857
Database object
creating
revoking privileges for 1-578
database server name
function name qualifier 1-965
DATABASE statement
determining database type 1-296
exclusive mode 1-296
specifying current database 1-295
SQLWARN after 1-296
syntax 1-295
Dataskip
skipping unavailable dbspaces 1-692
DATE data type 1-871
functions in 1-932
greater than condition 1-1020
unloading 1-770
DATE function 1-932, 1-933
DATETIME data type 1-871
field qualifiers 1-879
functions on 1-932
greater than condition 1-1020
unloading 1-772
with SET DESCRIPTOR 1-710
DATETIME Field Qualifier 1-879
DATETIME function
CREATE TABLE with 1-223
DAY function 1-932, 1-934
DBA See Database administrator (DBA); Privilege
database-level.
dbaccessdemo7 script Intro-6
DBANSIWARN environment variable 1-206, 1-291, 1-969
DBBLOBBUF environment variable 1-773
LOAD with 1-521
DBCENTURY environment variable
date value interpretation 1-1045
LOAD with 1-517
DBDATE environment variable 1-770, 1-933
DBDELIMITER environment variable 1-775
LOAD with 1-523
DBINFO function 1-912
sessionid option with 1-915
DBMONEY environment variable 1-771
DBMS. See Database management system.
DBSERVERNAME function 1-895
CREATE TABLE with 1-223
dbspace
DBINFO function with 1-914
default 1-118
INFO statement with 1-492
name 1-914
round robin distribution 1-44
setting with CREATE TABLE 1-246
skipping if unavailable 1-692
temporary tables in 1-246, 1-643
DBSPACETEMP configuration parameter
CREATE TEMP TABLE with 1-219
DBSPACETEMP environment variable 1-643
CREATE TEMP TABLE with 1-219
DBTIME environment variable 1-772
DDL.See Data definition language.
Deadlock detection 1-729
DEALLOCATE COLLECTION statement 1-297
DEALLOCATE DESCRIPTOR statement 1-299
DEALLOCATE ROW statement 1-301
DEC data type. See DECIMAL data type.
DECIMAL data type 1-866
GET DESCRIPTOR with 1-437
precision and scale 1-437
unloading 1-771
with SET DESCRIPTOR 1-710
Decimal point. See \xfa , decimal point.
DECLARE statement 1-303 to 1-326
collection derived table with 1-833
collection variables with 1-833
CURSOR keyword 1-316
cursors with prepared statements 1-319
FOR READ ONLY option 1-310
FOR UPDATE column list 1-312
FOR UPDATE option 1-310
restrictions with SELECT with ORDER BY 1-638
SCROLL keyword 1-317
SELECT with 1-608
transactions with 1-325
WHERE CURRENT OF clause 1-311
WITH HOLD keywords 1-318
DEFAULT keyword
in CONNECT statement 1-103
Default locale Intro-6
Default value
CREATE TABLE limitations 1-225
CREATE TABLE syntax for 1-223
Deferred-PREPARE feature 1-698
DEFINE statement 2-8
placement of 2-10
DELETE statement 1-327 to 1-337
cascading 1-330
collection columns with 1-329
Collection derived table clause 1-333, 1-337
collection derived table with 1-833
collection variables with 1-332, 1-333, 1-833
count of rows 1-450, 1-915
cursor with 1-311
missing WHERE signalled 1-340
object modes with 1-657
privilege for See Privilege, table-level.
row variables with 1-337
trigger 1-260
WHERE clause 1-330
checking for 1-340
WHERE CURRENT OF clause 1-331
with SELECT...FOR UPDATE 1-639
with update cursor 1-331
within a transaction 1-328
DELIMIDENT environment variable 1-972
variable name 'global' with 1-983
Delimiter
specifying with LOAD 1-523
specifying with UNLOAD 1-775
DELIMITER keyword
in UNLOAD 1-775
Demonstration database Intro-6
See also stores7 database.
DESCRIBE statement 1-338 to 1-345
collection variable with 1-344
Deferred-PREPARE feature with 1-699
distinct data type with 1-439
GET DESCRIPTOR with 1-436
INTO sqlda pointer clause 1-343
LENGTH field 1-438
opaque data type with 1-438
statement type 1-339
USING SQL DESCRIPTOR clause 1-341
with SET DESCRIPTOR 1-712
Descriptor. See System-descriptor area.
Destroy support function 1-172, 1-329, 1-377
Detached index 1-157
Diagnostic area
message codes in 1-442
Diagnostic table
examples with object mode 1-659
many to one relationship to violations table 1-663
starting with constraint -mode and index-mode objects 1-666
Diagnostics area
fields of 1-452
GET DIAGNOSTICS statement with 1-441
number of exceptions 1-450
Diagnostics table
creating with START VIOLATIONS TABLE 1-748
examples 1-763, 1-765, 1-768
how to stop 1-767
privileges on 1-761
relationship to target table 1-753
relationship to violations table 1-753
starting with filtering-mode objects 1-653
starting with START VIOLATIONS TABLE 1-748
stopping with filtering-mode objects 1-654
structure 1-760
use with SET 1-653
Dirty Read isolation level (Informix) 1-724
Disabled object mode 1-658
DISCONNECT statement 1-346 to 1-349
ALL keyword 1-349
CURRENT keyword 1-348
DEFAULT option 1-347
transaction with 1-348
Distinct data type 1-873
casting 1-122
casts and DROP TYPE 1-381
creating with CREATE DISTINCT TYPE 1-121
DESCRIBE with 1-439
dropping casts on 1-351
dropping with DROP TYPE 1-381
dynamic SQL with 1-439
GET DESCRIPTOR with 1-439
in dynamic SQL 1-712
privileges 1-122
restrictions on source type 1-122
source data type 1-439, 1-712
with SET DESCRIPTOR 1-712
DISTINCT keyword
in CREATE INDEX 1-139
use in expression 1-948
Distributions
dropping with DROP DISTRIBUTIONS clause 1-813
MEDIUM or HIGH distribution 1-814
privileges required to create 1-813
divide() operator function 1-959
DML.See Data manipulation language.
Documentation conventions
icon Intro-10
sample-code Intro-18
syntax Intro-12
typographical Intro-9
Documentation notes Intro-20
Documentation, types of
documentation notes Intro-20
error message files Intro-20
machine notes Intro-20
on-line manuals Intro-19
printed manuals Intro-19
release notes Intro-20
Double colon. See
, cast operator.
DOUBLE PRECISION data type 1-868
DOUBLE PRECISION data type. SeeFLOAT data type.
Double-dash (--) comment symbol 1-1045
DROP CAST statement 1-350
DROP DATABASE statement 1-352
DROP FUNCTION statement 1-354
DROP INDEX statement 1-358
DROP OPCLASS statement 1-360
DROP PROCEDURE statement 1-362
DROP ROLE statement 1-366
DROP ROUTINE statement 1-368
DROP ROW TYPE statement 1-372
RESTRICT keyword 1-373
DROP SYNONYM statement 1-374
DROP TABLE statement 1-376
CASCADE keyword 1-377
effects of 1-376
RESTRICT keyword 1-377
DROP TRIGGER statement 1-379
DROP TYPE statement 1-381
DROP VIEW statement 1-383
Duplicate values
querying for 1-601
Dynamic management statements 1-508
Dynamic routine-name specification
of SPL functions 1-402
of SPL procedures 1-410

E

Enabled object mode 1-657
benefits of 1-671
End of data
with WHENEVER 1-823
Environment variable
IFX_AUTOFREE 1-678, 1-682, 1-683
IFX_DEFERRED_PREPARE 1-700
NODEFDAC 1-129, 1-185
setting
with SYSTEM statement 2-55
en_us.8859-1 locale Intro-6
Equal sign. See=, equals operator.
equal() operator function 1-1020
Error checking
continuing after procedure error 2-47
error status with ON EXCEPTION 2-44
with SYSTEM 2-55
Error message files Intro-20
Errors
Informix-specific 1-444
with WHENEVER 1-822
Escape character. See\, backslash; Condition, comparison, ESCAPE.
ESQL
See also External routine; External function;External procedure.
ESQL/C
collection cursor with FETCH 1-422
collection cursor with PUT 1-563
cursor example 1-318
deallocating collection variable memory 1-297
deallocating row variable memory 1-301
DISCONNECT statement in 1-348
inserting collection variables with 1-504
inserting row variables 1-506
Exceptions. See End of data; Errors; Error checking; Warning.
Exclamation point . See !, exclamation point.
Exclusive access
specifying in DATABASE statement 1-296
specifying in LOCK TABLE statement 1-526
Exclusive lock
SeeLock
exclusive.
Executable file location 1-1012
EXECUTE FUNCTION
preparing 1-402
EXECUTE FUNCTION statement 1-397
DESCRIBE with 1-341
how it works 1-398
INTO clause 1-399, 1-417
syntax 1-397
EXECUTE IMMEDIATE statement
restricted statements 1-405
syntax and usage 1-404
EXECUTE PROCEDURE statement 1-407 to 1-410
how it works 1-409
in FOREACH 2-27
INTO clause 1-410
syntax 1-407
EXECUTE statement
and sqlca record 1-395
Deferred-PREPARE feature with 1-699
error conditions with 1-395
INTO DESCRIPTOR clause 1-391
INTO keyword 1-387
INTO SQL DESCRIPTOR clause 1-390
parameterizing a statement 1-393
syntax 1-385
USING DESCRIPTOR clause 1-395
USING SQL DESCRIPTOR clause 1-394
EXIT statement 2-20
EXP function 1-918
Explicit temporary table 1-218
Exponential function 1-918
Export support function 1-172, 1-772
Exportbinary support function 1-172, 1-772
Expression 1-881 to 1-960
aggregate 1-946 to 1-960
boolean 1-837
condition with 1-836
casting 1-885
column 1-886
combined expressions 1-959
concatenating 1-883
constant 1-892 to 1-900
fragment 1-35
function 1-903 to 1-960
in UPDATE 1-788
ordering by 1-638
smart large objects in 1-891
Expression-based distribution scheme
in CREATE INDEX 1-158
Expresssion
boolean
See alsoCondition, comparison.
EXTEND function 1-932, 1-935
Extension, to SQL
symbol for Intro-14
Extent
revising size 1-254
size 1-87
External function
aggregate function with 1-958
as operator-class strategy function 1-177
as operator-class support function 1-178
CREATE FUNCTION 1-128
executing 1-397, 1-545
non-variant 1-962
registering 1-128
variant 1-962
See also External routine.
External procedure
creating body of 1-184
executing 1-407, 1-545
See also External routine.
External routine
comments in 1-12
concatenation operator with 1-884
CREATE PROCEDURE FROM statement in 1-192
creating a function in 1-135
DEALLOCATE COLLECTION statement 1-297
ill-behaved 1-1030
pathname syntax 1-1012
preparing 1-545
referencing 1-961
setting SQLSTATE 1-454
See also Routine; External function; External procedure.
External routine reference
example 1-962
External Routine Reference segment 1-961
EXTYPEID field
syntax 1-433
with DESCRIBE 1-342
with SET DESCRIPTOR 1-711
EXTYPELEN field
syntax 1-433
EXTYPELENGTH field
with DESCRIBE 1-342
with SET DESCRIPTOR 1-711
EXTYPENAME field
with DESCRIBE 1-342
with SET DESCRIPTOR 1-711
EXTYPENM field
syntax 1-433
EXTYPEOWNERLENGTH field
with DESCRIBE 1-342
with SET DESCRIPTOR 1-711
EXTYPEOWNERNAME field
with DESCRIBE 1-342
with GET DESCRIPTOR 1-439
with SET DESCRIPTOR 1-711
EXTYPEOWNNM field
syntax 1-433
EXTYPOWNLEN field
syntax 1-433

F

Feature icons Intro-11
Features, product Intro-7
FETCH statement 1-411 to 1-424
checking results with SQLCA 1-424
CLOSE with 1-94
collection derived table with 1-833
collection variables with 1-833
collection with 1-422
fetching a row for update 1-421
INTO clause 1-418
locking considerations 1-421
NEXT keyword 1-414
program arrays with 1-418
row numbers 1-416
sequential cursor with 1-414
syntax error, bad cursor name 1-980
USING DESCRIPTOR clause 1-420
USING SQL DESCRIPTOR clause 1-419, 1-437
with
scroll cursor 1-415
X/Open mode 1-414
Field projection 1-888
File
loading data into database 1-515
OUTPUT statement with 1-539
sqexplain.out 1-714
unloading data from database 1-769
.lok extension, explanation of 1-729
FILETOBLOB function 1-926
FILETOCLOB function 1-923, 1-924, 1-926
FILLFACTOR clause in CREATE INDEX 1-156
Filtering object mode 1-658
benefits of 1-671
Fixed-length data type
opaque data type 1-169
FLOAT data type 1-868
unloading 1-771
FLUSH statement 1-426
FOR statement 2-22
IN keyword 2-26
TO keyword 2-24, 2-25
FOREACH statement 2-27
collection derived table with 1-833
collection variables with 1-833
Foreign key constraint 1-233
Fragment
privileges
duration of 1-484
granting with GRANT FRAGMENT 1-480
revoking with REVOKE FRAGMENT 1-589
Fragmentation
adding fragments 1-46
altering 1-29
arbitrary rule 1-249
attaching tables 1-32
CREATE INDEX..BY EXPRESSION 1-158
CREATE TABLE..FRAGMENT BY 1-247
dbspace for round-robin 1-44
defining and initializing 1-38
detaching fragments 1-37
disk space 1-31
dropping fragments 1-48
expression-based 1-44
fragment expressions 1-45, 1-159
information (INFO statement) 1-492
log space 1-31
modifying 1-49
PDQ with SET EXPLAIN 1-719
PDQPRIORITY 1-735
remainder fragment 1-48
round robin distribution 1-44
row space 1-31
rowid columns 1-44
rowid columns with 1-88, 1-244
skipping an unavailable dbspace 1-692
strategy
arbitrary rule 1-45
by expression 1-158, 1-249
hash rule 1-45, 1-249
range rule 1-45, 1-249
round robin 1-248
Fragmentation initialize 1-38
FREE statement 1-429
cursors with 1-538
implicit 1-677
statement identifier in 1-431
with AUTOFREE feature 1-677
Function 1-903 to 1-960
aggregate 1-946 to 1-960
EXISTS subquery with 1-851
algebraic 1-905 to 1-911
as column name 1-973
casting 1-116
creating indirectly from a stored file 1-135
creating with CREATE FUNCTION 1-125 to 1-133
creating with CREATE FUNCTION FROM 1-134
dropping with DROP FUNCTION 1-354
dropping with DROP ROUTINE 1-368
exponential 1-918
expression with 1-903 to 1-946
length 1-920
logarithmic 1-918
name 1-399, 1-964
fully qualified 1-965
privileges 1-129
procedure contrasted with 1-182
shared library 1-922
smart large object 1-925
system catalog tables for 1-127
time 1-932 to 1-936
trigonometric 1-937 to 1-939
user-defined 1-942 to 1-946
See also each function listed under its own name.
Function cursor 1-308, 1-401
closing 1-94
default mode 1-309
opening 1-531, 1-537
reopening 1-537
Function Modifier 1-1027
Function Name segment 1-964
Function Parameter List 1-1033
Functional index
description 1-138
in composite index 1-153
specifying name of 1-144

G

GET DESCRIPTOR statement 1-433 to 1-440
DECIMAL data type with 1-437
EXTYPEID field with 1-438
EXTYPELENGTH field with 1-439
EXTYPENAME field with 1-438
EXTYPEOWNERLENGTH field with 1-439
TYPE field 1-436, 1-437
VALUE clause 1-436, 1-437
X/Open mode 1-437
GET DIAGNOSTICS statement 1-441 to 1-460
CLASS_ORIGIN keyword 1-453
CONNECTION_NAME keyword 1-457
error-checking with 1-448
EXCEPTION clause 1-451
INFORMIX_SQLCODE keyword 1-453
MESSAGE_LENGTH keyword 1-454
MESSAGE_TEXT keyword 1-454
MORE keyword 1-450
NUMBER keyword 1-450
RETURNED_SQLSTATE keyword 1-453
ROW_COUNT keyword 1-450
SERVER_NAME keyword 1-455
SQLSTATE codes 1-445
Statement clause 1-449
SUBCLASS_ORIGIN keyword 1-454
Global Language Support (GLS) Intro-6
SQL comment symbols with 1-13
GLS. See Global Language Support.
GL_DATE environment variable 1-517, 1-770
GL_DATETIME environment variable 1-518, 1-772
GRANT FRAGMENT statement 1-480
AS grantor clause 1-487
WITH GRANT OPTION clause 1-486
GRANT statement 1-461 to 1-479
ALL (PRIVILEGES) behavior 1-468
ANSI compliance 1-206
AS grantor clause 1-478
column-specific privileges 1-468
CREATE SCHEMA statement with 1-206
database-level privileges 1-463 to 1-464
EXECUTE ON (user-defined routine) 1-472
grantor chain 1-477
role name and privileges 1-475
table-level privileges 1-465 to 1-470
USAGE ON (user-defined data type) 1-471
view name and privileges 1-470
WITH GRANT OPTION clause 1-477
greaterthanorequal() operator function 1-1020
greaterthan() operator function 1-1020

H

HANDLESNULLS routine modifier 1-1029
Hash rule 1-159
HEX function
rowid with 1-890
Hold cursor 1-318
transactions with 1-95
Host variable
for cursor 1-305
inserting fetched values 1-437
selecting values into 1-605
system-descriptor fields 1-706
Hyphen. See-, hyphen.

I

Icons
comment Intro-10
compliance Intro-12
feature Intro-11
product Intro-11
syntax diagram Intro-14
IDATA field
in X/Open programs 1-709
syntax
ILENGTH field 1-433
with SET DESCRIPTOR 1-709
Identifier 1-967 to 1-1010
DELIMIDENT environment variable 1-972
delimited 1-969
example 1-972
keywords as 1-974
multibyte characters in 1-969, 1-972
non-ASCII characters in 1-969, 1-972
IF statement
syntax 2-34
syntax and use 2-34
with null values 2-36
IFX_AUTOFREE environment variable 1-678, 1-682, 1-683
IFX_DEFERRED_PREPARE environment variable 1-700
IFX_RELOAD_MODULE function 1-923
IFX_REPLACE_MODULE function 1-924
ILENGTH field
in X/Open programs 1-709
with SET DESCRIPTOR 1-709
Implicit temporary table 1-218
Import support function 1-172, 1-518
Importbinary support function 1-172, 1-518
Index
bidirectional traversal 1-146
cleaner list. See Btree cleaner list.
clustered
altering 1-52
clustering
with ALTER INDEX...TO CLUSTER 1-53
with CREATE INDEX...CLUSTER 1-139
constraints, effect on 1-139
converting during upgrade 1-806
creating fragments 1-158
creating with CREATE INDEX 1-137
detached 1-157
disabled 1-166
DROP INDEX with 1-358
duplicate 1-84, 1-139
object mode 1-655
expansion with 1-156
FILLFACTOR clause with 1-156
fragmentation with 1-36, 1-42, 1-157, 1-160
INFO statement with 1-491
name 1-985
number on same columns 1-154
ORDER BY with 1-147, 1-639
privilege for creating (Index privilege) See Privilege, table-level.
sharing with constraints 1-228
side-effect 1-178
temporary table 1-644
unique
adding with object modes 1-663 to 1-669
duplicate values, resolving 1-164
Index access method. SeeAccess method, secondary.
Index Name segment 1-985
INDICATOR field
GET DESCRIPTOR with 1-438
SET DESCRIPTOR with 1-710
syntax 1-433
Indicator variable
in dynamic SQL 1-438, 1-710
Industry standards, compliance with Intro-21
INFO statement 1-489 to 1-494
COLUMNS FOR 1-491
FRAGMENT keyword 1-492
INDEXES FOR 1-491
PRIVILEGES FOR 1-493
REFERENCES FOR 1-493
STATUS FOR 1-494
TABLES 1-490
Informix Guide to SQL: Syntax, organization of 1-7
Informix user ID
CONNECT...USER statement with 1-110
host variable for 1-110
INFORMIXDIR/bin directory Intro-6
$INFORMIXDIR/etc/sqlhosts. See sqlhosts file.
Input support function 1-171
Insert buffer
counting inserted rows 1-428, 1-566
cursor with 1-498
filling with constant values 1-558
storing rows with PUT 1-557
triggering flushing 1-565
Insert cursor 1-315, 1-498
benefits 1-316
closing 1-94
opening 1-532
reopening 1-532
restrictions 1-532
sqlca state on closing 1-94
transaction with 1-96
values passed to program 1-559
INSERT statement 1-495 to 1-514
AT clause 1-512
character values 1-502
collection column values 1-504
collection derived table with 1-509, 1-514, 1-833
collection variable elements 1-509
collection variables with 1-833
collection with
Collection variable, cursor for 1-563
count of rows inserted 1-450, 1-915
cursor with 1-498
DESCRIBE with 1-341
dynamic 1-508
ESQL/C 1-504, 1-506
examples with object modes 1-659 to 1-663
insert cursor compared with 1-316
LOAD with 1-517
object modes with 1-657
opaque variables 1-503
privilege for See Privilege, table-level.
PUT with 1-557
putting values into program variables 1-559
row type field values 1-505
row variables 1-514
SELECT statement with 1-507
SERIAL and SERIAL8 columns 1-503
SERIAL and SERIAL8 columns with 1-914
smart large objects with 1-890
TEXT and BYTE columns 1-502
transaction and 1-499
VALUES clause 1-499
expressions with 1-506
NULL keyword with 1-507
view with 1-497
INT8 data type 1-866
unloading 1-771
INTEGER data type 1-866
unloading 1-771
Integrity. See Data integrity.
INTERNAL routine modifier 1-1031
INTERVAL data type 1-871
greater than condition 1-1020
loading 1-518
with SET DESCRIPTOR 1-710
INTERVAL Field Qualifier segment 1-987
INTO DESCRIPTOR keywords 1-387
in EXECUTE 1-391
INTO SQL DESCRIPTOR keywords 1-387
in EXECUTE...INTO 1-390
ISAM error code 2-43, 2-49
ISO 8859-1 code set Intro-6
Isolation level
ANSI Read Committed 1-745
ANSI Read Uncommitted 1-745
ANSI Repeatable Read 1-745
ANSI Serializable 1-745
default 1-725, 1-746
default in ANSI-compliant database 1-745
definitions 1-724, 1-745
FETCH statement with 1-421
in external tables 1-726, 1-742
Informix Committed Read 1-724
Informix Cursor Stability 1-724
Informix Dirty Read 1-724
Informix Repeatable Read 1-724
scroll cursor with 1-317
ITERATOR routine modifier 1-1032
ITYPE field
in X/Open programs 1-709
syntax 1-433
with SET DESCRIPTOR 1-710

J

Join
dynamic hash example 1-716
in Condition segment 1-627
multiple-table join 1-628
outer join 1-629
self-join 1-629
SET EXPLAIN information about 1-716
two-table join 1-628
Join column. See Foreign key.

K

Keywords
SQL 1-968
using in triggered action 1-273

L

Large object 1-869
attaching tables 1-36
constraints 1-229
fragmentation with 1-36
See also Simple large object ; Smart large object.
LENGTH field
and TYPE field 1-709
with DATETIME and INTERVAL types 1-710
with DECIMAL and MONEY types 1-710
with DESCRIBE 1-342, 1-709
with SET DESCRIPTOR 1-709
LENGTH function 1-921
Length functions 1-920
lessthanorequal() operator function 1-1020
lessthan() operator function 1-1020
LET statement
syntax 2-39
Library
shared 1-922
like() operator function 1-847
LIST data type
deleting elements 1-332
deleting elements from 1-329, 1-333
selecting one element from 1-614
unloading 1-770
updating elements 1-791, 1-797, 1-798
updating elements in 1-803
Literal
Collection 1-990 to 1-994
expression with 1-899
nested example 1-993
quotation marks with 1-993
syntax for non-collection elements 1-991
DATETIME 1-996 to 1-998
current date 1-897
quoted string as 1-1017
INSERT with 1-501
INTERVAL 1-999 to 1-1001
expression with 1-898
quoted string as 1-1017
Number 1-1002
expression with 1-896
Row 1-1004 to 1-1008
expression with 1-900
literal collection with 1-993
nested row containing 1-1007
Literal Collection segment 1-990
Literal DATETIME segment 1-996
Literal Row segment 1-1004
LOAD FROM file 1-517
LOAD statement 1-515
DELIMITER clause 1-523
format requirements 1-517
INSERT INTO clause 1-524
LOAD FROM file 1-517
loading CHAR and VARCHAR data types 1-520
loading CLOB or BLOB columns 1-521
loading collections 1-522
loading row types 1-522
loading TEXT or BYTE columns 1-521
opaque-type columns 1-522
privileges 1-516
specifying the table to load into 1-524
Locale Intro-6
Lock
exclusive 1-239
page 1-254
promotable 1-312
row 1-254
setting with SET LOCK MODE 1-728
table
changing mode 1-88
creating with ALTER INDEX 1-53
LOCK TABLE statement 1-525
unlocking 1-777
update 1-783
Lock mode
altering with ALTER TABLE 1-88
LOCK TABLE statement
reversing with UNLOCK TABLE 1-777
with transactions 1-526
without transactions 1-527
Locking
cursors, effect on 1-312
default table locking 1-254
during
delete 1-328
updates 1-783
FETCH statement and 1-421
overriding row-level 1-526
releasing with COMMIT WORK 1-99
releasing with ROLLBACK WORK 1-594
SET ISOLATION with 1-722
transactions with 1-91
waiting period 1-729
LOCOPY function 1-930
LOG10 function 1-919
Logarithmic functions 1-918
Logging
ANSI-compliant database with 1-119
buffered
changing to with SET BUFFERED LOG 1-731
setting with CREATE DATABASE 1-119
buffered versus unbuffered 1-731
cascading deletes 1-240
database creation 1-119
unbuffered
changing to with SET LOG 1-731
with triggers 1-285
Logical operator
AND 1-853
condition with 1-853
NOT 1-837
OR 1-853
unknown values 1-837, 1-854
LOGN function 1-919
Lohandles support function 1-173
.lok extension 1-729
.lok file extension 1-729
Loop
indefinite with WHILE 2-61
LOTOFILE function 1-928
LVARCHAR data type 1-862, 1-863
greater than condition 1-1020
in INSERT 1-502

M

Machine notes Intro-20
Mail, sending from procedure 2-55
Major features Intro-7
matches() operator function 1-848