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

MAX function 1-953

MDY function 1-936

Memory

allocating for collection variable 1-22

allocating for row variable 1-27

allocating for system-descriptor area 1-24

DEALLOCATE COLLECTION statement for 1-297

DEALLOCATE DESCRIPTOR statement for 1-299

DEALLOCATE ROW statement for 1-301

Message

error 1-442

Messages

End of data 1-442

Not Found 1-442

warning 1-442

MIN function 1-954

minus() operator function 1-959

MOD function 1-908

Model. See Data model.

MONEY data type 1-866

GET DESCRIPTOR with 1-437

loading 1-518

precision and scale 1-437

syntax 1-865

unloading 1-771

with SET DESCRIPTOR 1-710

MONTH function 1-934

MS-DOS operating system. See DOS operating system.

Multiple triggers

preventing overriding 1-284

MULTISET 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

N

NAME field

syntax 1-433

with DESCRIBE 1-342

Named row data type

updating fields 1-803

Named row type 1-875

assigning with ALTER TABLE 1-86

constraints in table containing 1-202

creating with CREATE ROW TYPE 1-197

dropping with DROP ROW TYPE 1-372

inheritance 1-200

unloading 1-771, 1-775

Naming convention

constraint 1-856

database 1-858

index 1-986

synonym 1-1048

table 1-1050

view 1-1053

NCHAR data type 1-863

loading 1-520

syntax 1-862

with relational operators 1-1020

negate() operator function 1-960

Nested ordering, in SELECT 1-637

NODEFDAC environment variable 1-129, 1-185, 1-221

effects on new routine 1-129, 1-185

GRANT statement with 1-469, 1-474

Condition

IS 1-845

Not found condition. See End of data.

NOT logical operator 1-837

NOT VARIANT routine modifier 1-1031

notequal() operator function 1-1020

NULL comparator 1-845

Null value

AND and OR operators with 1-854

BETWEEN operator with 1-842

checking for in SELECT 1-387, 1-392

GET DESCRIPTOR with 1-438

IN operator results with 1-844

in SPL IF statement 2-36

inserting with the VALUES clause 1-507

LIKE and MATCHES results with 1-845

loading 1-518

NOT operator with 1-837

returned implicitly 2-51

testing for 1-623, 1-845

unloading 1-771

updating a column 1-788

used in the ORDER BY clause 1-637

WHILE statement with 2-61

with SET DESCRIPTOR 1-710

NULLABLE field

with DESCRIBE 1-342

Number data type

literal 1-1002

syntax 1-1002

Numeric data type 1-864 to 1-868

See also DECIMAL;DOUBLE PRECISION; FLOAT; INTEGER; INT8; MONEY; NUMERIC; REAL; SERIAL; SERIAL8; SMALLINT; SMALL FLOAT data types.

NUMERIC data type. See DECIMAL data type.

NVARCHAR data type 1-863

loading 1-520

syntax 1-862

with relational operators 1-1020

O

Object mode

adding objects examples 1-665

benefits of 1-670 to 1-671

data manipulation statements (INSERT, UPDATE, DELETE) with 1-657

disabled 1-658

duplicate index 1-655

enabled 1-657

benefits of 1-671

examples 1-659

filtering 1-658

benefits of 1-671

error options in SET 1-652

privileges required for changing 1-649

setting with SET 1-648

terminology 1-648

trigger 1-286, 1-655

See also Trigger; Constraint; Index;SET statement

SET statement

See also Object mode

Object See database object.

OCTET_LENGTH function 1-921

ON EXCEPTION statement

placement of 2-45

SET LOCK MODE with 1-984

SET statement with 1-984

syntax 2-43

ON keyword

in CREATE INDEX 1-139

ONCONFIG parameter

DBSPACETEMP 1-643

On-line manuals Intro-19

Opaque data type 1-873

alignment of 1-170

as argument 1-170

creating with CREATE OPAQUE TYPE 1-167

DESCRIBE with 1-438

dropping with DROP TYPE 1-381

extended identifier 1-438, 1-711

fixed length 1-169

fixed-length 1-169

GET DESCRIPTOR with 1-438

in DELETE 1-329

in DROP TABLE 1-377

in dynamic SQL 1-711

in INSERT 1-503

in LOAD 1-522

in UPDATE 1-793

loading 1-518, 1-522

modifiers 1-169

name of 1-438, 1-711

naming 1-168

owner name 1-439, 1-711

privileges 1-168

row field literal 1-1008

sizing 1-168

support functions 1-171

unloading 1-772

varying length 1-169

varying-length 1-169

with SET DESCRIPTOR 1-711

Opaque variable

inserting 1-503

Opclass. SeeOperator class.

OPEN statement 1-528 to 1-538

collection cursor 1-533

constructing the active set 1-530

cursors for data manipulation 1-530

Deferred-PREPARE feature with 1-698

FREE statement with 1-538

function cursor 1-531

insert cursor 1-532

reopening a cursor 1-532, 1-537, 1-684

substituting values for ? parameters 1-533

USING clause 1-534

USING DESCRIPTOR clause 1-535

WITH REOPTIMIZATION clause 1-536

Operator

See Arithmetic operator; Cast operator; Logical operator; Relational operator.

Operator class

btree_ops 1-179

creating with CREATE OPCLASS 1-174

default 1-179

default for B-Tree 1-179

definition 1-145, 1-175

dropping with DROP OPCLASS 1-360

rtree_ops 1-179

specifying with CREATE INDEX 1-142, 1-145

Operator function

concat() 1-884

divide() 1-959

equal() 1-1020

greaterthanorequal() 1-1020

greaterthan() 1-1020

lessthanorequal() 1-1020

lessthan() 1-1020

like() 1-847

matches() 1-848

minus() 1-959

negate() 1-960

notequal() 1-1020

plus() 1-959

positive() 1-960

times() 1-959

Optimizer

specifying a high or low level with SET OPTIMIZATION 1-733

strategy functions 1-176

with UPDATE STATISTICS 1-809, 1-817

Optimizing

a query 1-714

across a network 1-734

cursor with Deferred-PREPARE feature 1-698

cursor with SET AUTOFREE 1-676

database server 1-733

OR logical operator 1-853

ORDER BY keywords

indexes on ORDER BY columns 1-147

Outer join, forming 1-612

OUTPUT statement 1-539

PIPE with 1-540

Output support function 1-171

Owner

ANSI-compliancy 1-475, 1-1050

function name qualifier 1-964

in Constraint Name segment 1-855

in CREATE SYNONYM 1-207

in RENAME COLUMN 1-569

in RENAME TABLE 1-572

index name qualifier 1-985

procedure name qualifier 1-1009

table-name qualifier 1-1050

view name with 1-1053

P

Parallel distributed query. SeePDQ.

Parent-child relationship 1-70, 1-236

PDQ

SET PDQPRIORITY statement 1-735

with SET EXPLAIN 1-719

PDQPRIORITY environment variable 1-735

PERCALL_COST routine modifier 1-1030

Permission

running operating-system command from a routine 2-55

See Privilege.

Phantom row 1-724, 1-745

plus() operator function 1-959

positive() operator function 1-960

POW function 1-909

Precedence

dot notation rules 1-889

PRECISION field

with DESCRIBE 1-342

with SET DESCRIPTOR 1-710

PREPARE statement

deferring 1-698

executing 1-385

for collection variables 1-545

freeing resources with FREE 1-431

FROM INSERT...VALUES 1-560

increasing performance efficiency 1-553

multi-statement text 1-552, 1-553

parameterizing a statement 1-547

parameterizing for SQL identifiers 1-549

question (?) mark as placeholder 1-542

restrictions with SELECT 1-544

statement identifier 1-319

statement identifier use 1-543

syntax 1-541

valid statement text 1-544

with CREATE FUNCTION 1-133

with CREATE PROCEDURE 1-189

with external routines 1-545

with SPL routines 1-545

Prepared statement

comment symbols in 1-544

DESCRIBE statement with 1-339

executing 1-385

parameterizing 1-393, 1-549

prepared object limit 1-542

valid statement text 1-544

Primary key constraint 1-70

data type conversion 1-79

enforcing 1-228

modifying a column with 1-78

naming 1-84

referencing 1-70

rules of use 1-236

shared unique index 1-83

using 1-232

Printed manuals Intro-19

Privilege

chaining grantors 1-477

effect of REVOKE 1-587

CREATE SCHEMA with GRANT 1-206

database-level

granting 1-463 to 1-464

revoking 1-577 to 1-578

DBA 1-464

DROP ROLE effects 1-366

Execute 1-472

defaulting to DBA 1-129, 1-185

trigger requirements 1-280

Execute (user-defined routine)

ANSI-compliancy 1-474

revoking 1-583

fragment

granting with GRANT FRAGMENT 1-480

revoking with REVOKE FRAGMENT 1-589

granting with GRANT 1-461

INFO statement for 1-493

needed

to create a cast 1-113

on an opaque type 1-168

public

NODEFDAC effect on 1-469, 1-474

revoking 1-580

role name 1-475, 1-586

purpose 1-193

table-level

ANSI-compliancy 1-469

column-specific 1-466

creating a view 1-290

effect on view 1-470

revoking 1-579 to 1-582

Usage 1-471

revoking 1-582

Usage (user-defined data type) 1-471

Procedure

creating

from separate file 1-191

with CREATE PROCEDURE FROM 1-191

creating from file 1-192

dropping with DROP PROCEDURE 1-362

dropping with DROP ROUTINE 1-368

function contrasted with 1-182

naming conventions 1-409

privileges 1-185, 1-189

system catalog tables for 1-183

PROCEDURE data type 2-17

Procedure Modifier 1-1028

Procedure Parameter List 1-1033

Product icons Intro-11

Program variables

inserting 1-559

preparing 1-560

Projection

column with dot notation 1-888

field with dot notation 1-888

Promotable lock

SeeLock

promotable

PUT statement

checking results with SQLCA 1-565

collection derived table with 1-833

collection variables with 1-833

FLUSH with 1-557

FROM keyword 1-560

insert cursor 1-559

multiple with transactions 1-319

source of row values 1-558

syntax 1-555

trigger with 1-260

use in transactions 1-557

USING DESCRIPTOR clause 1-562

USING SQL DESCRIPTOR clause 1-561

Q

Quoted string

See also", double quotes

QRT function 1-910

Query

complex vs. simple 1-718

design plan 1-536

memory for returned values 1-416

multirow

statements for 1-413

optimization information statements 1-18

optimizing 1-536

ordering with UNION operator 1-645

piping results 1-540

UNION operator with 1-645

See also Subquery.

Question mark. See? (question mark).

Quotation marks

double

around quoted string 1-1015

within quoted string 1-1017

single

around quoted string 1-1015

within quoted string 1-1017

Quoted Pathname segment 1-1012

Quoted string

comparing 1-841

DATETIME, INTERVAL with 1-1017

inserting values with 1-1018

LIKE, MATCHES condition with 1-1017

examples 1-623

TRACE statement with 2-59

Quoted String segment 1-1015

R

RAISE EXCEPTION statement 2-49

RANGE function 1-954

Range rule 1-159

Read Committed isolation level (ANSI) 1-745

Read Uncommitted isolation level (ANSI) 1-745

Read-only cursor

opening 1-530

REAL data type 1-868

REAL data type. SeeSMALLFLOAT data type.

REATE TRIGGER statement

DELETE REFERENCING clause 1-268

Receive support function 1-172

References 1-10

Referential constraint 1-70

disallowing with REVOKE REFERENCES 1-580

enforcing 1-228

establishing with CREATE TABLE..REFERENCES 1-237

index name 1-229

locking 1-239

modifying a column with 1-78

with other constraints 1-83

See alsoConstraint; Cascading deletes.

Referential constraints

ALTER TABLE...REFERENCES 1-68

Referential integrity

See alsoReferential constraint; Constraint.

Relational operator

BETWEEN 1-622, 1-842

condition with 1-841

EXISTS 1-625, 1-851

IN 1-622, 1-843

LIKE 1-623, 1-846

MATCHES 1-623, 1-847

segment 1-1019

SELECT statement with 1-621

Release notes Intro-20

RENAME DATABASE statement 1-571

RENAME TABLE statement

ANSI-compliant naming 1-572

syntax 1-572

Repeatable Read isolation level

emulating during update 1-422

with SET ISOLATION statement 1-724

with SET TRANSACTION 1-745

Reserved words 1-968

delimited identifiers with 1-971

identifiers with 1-968

Resolution

in UPDATE STATISTICS 1-814

with data distributions 1-814

RETURN statement 2-51 to 2-53

Return value

declaring in CREATE FUNCTION 1-1025

RETURN statement for 2-51

REVOKE FRAGMENT statement 1-589 to 1-593

REVOKE statement

ALL keyword behavior 1-582

CASCADE keyword 1-587

column-specific privileges 1-581

effect on grantor chain 1-587

EXECUTE ON (user-defined routine) 1-583

No record found message 1-581

Privilege not revoked message 1-582

privileges needed 1-576

RESTRICT option 1-588

role name and privileges 1-586

table-level privileges 1-579

Role

creating with CREATE ROLE 1-193

dropping with DROP ROLE 1-366

enabling with SET ROLE 1-738

establishing with CREATE, GRANT, SET 1-476

granting name with GRANT 1-476

granting privileges with GRANT 1-477

revoking privileges with REVOKE 1-586

ROLLBACK WORK statement

syntax 1-594

use with WHENEVER 1-595

WHENEVER with 1-92, 1-98

with DROP DATABASE 1-353

ROOT function 1-909

ROUND function 1-909

Routine

creating with CREATE ROUTINE FROM 1-195

dropping with DROP ROUTINE 1-368

header 2-10

parameter list 1-1033

privileges 1-189

restrictions in SQL statements 1-1044

setting SQLSTATE 1-453

specific name 1-1040

SQL statements not valid in 1-1043

SYSTEM statements permission 2-55

See also User-defined routine.

Routine Modifier 1-1027

Routine modifier

CLASS 1-1030

CLIENT 1-1030

HANDLESNULLS 1-1029

INTERNAL 1-1031

ITERATOR 1-1032

NOT VARIANT 1-1031

PERCALL_COST 1-1030

SELFUNC 1-1030

STACK 1-1031

VARIANT 1-1031

Routine resolution

internal routines 1-1031

return values 1-1025

Row

empty, finding with indicator variable 1-958

engine response to locked row 1-729

insert cursor 1-498

inserting

with a stored procedure 1-509

with a user-defined function 1-509

inserting through a view 1-497

inserting values 1-499

loading into database 1-516

multirow, fetching with cursor 1-413

none selected, finding with COUNT (*) 1-958

number vs. rowid with FETCH 1-416

order, effect on trigger 1-266

phantom 1-724, 1-745

unloading from database 1-770

updating through a view 1-781

writing buffered rows with FLUSH 1-426

ROW constructor 1-900

Row lock. See Lock, row.

Row type

collection type literal in 1-1008

constructor syntax 1-900

deleting 1-337

dot notation with 1-888

field-level literal values 1-1007

loading field values 1-518, 1-522

nested 1-1007

selecting fields 1-604, 1-618

selecting from 1-617

unloading 1-771, 1-775

updating 1-789, 1-803

Row type See also Named row type; Unnamed row type.

ROW type. See Unnamed row type.

Row variable

accessing 1-835

allocating memory for 1-27

deallocating memory for 1-301

deleting 1-337

inserting 1-505

inserting into 1-514

literalSee Literal, Row

selecting from 1-617

updating 1-803

See alsoCollection derived table.

Rowid

adding with ALTER FRAGMENT 1-44

adding with ALTER TABLE 1-88

adding with CREATE TABLE 1-244

column expression with 1-890

column name as 1-977

restrictions 1-890

R-tree index

creating 1-152

default operator class 1-179

rtree_ops operator class 1-179

uses 1-151

rtree secondary access method 1-151

Rule

arbitrary 1-160

hash 1-159

range 1-159

S

Sample-code conventions Intro-18

SB_SPACE_NAME environment

CREATE TABLE with 1-251

SCALE field

with DESCRIBE 1-342

with SET DESCRIPTOR 1-710

Schema. See Data model.

Scroll cursor 1-317

active set 1-317, 1-416

FETCH with 1-415

position options 1-415

Secondary access method

B-tree 1-151

CREATE INDEX... USING 1-151

default operator class 1-179

definition 1-138, 1-175

See Access method, secondary; Index.

specifying with CREATE OPCLASS 1-175

secondary access method

R-tree 1-151

Security

See alsoPrivilege.

Segment 1-826

Literal INTERVAL 1-999

Select cursor 1-308

closing 1-94

default mode 1-309

opening 1-530, 1-537

reopening 1-537

Select cursor. SeeCursor,select.

SELECT statement 1-596 to 1-646

aggregate function with 1-603

ALL keyword 1-601

BETWEEN condition 1-622

collection derived table with 1-833

collection variables with 1-833

collection with 1-613

column expression in 1-602

column numbers 1-638

constant expression in 1-602

cursor for 1-639, 1-640

DESCRIBE with 1-341

DISTINCT keyword 1-601

expressions in select list 1-601

FOR READ ONLY clause 1-640

FOR UPDATE clause 1-639

FROM Clause 1-610

function expression in 1-603

GROUP BY clause 1-630

HAVING clause 1-632

IN condition 1-622

indicator variables with 1-401, 1-607

INSERT with 1-507

INTO clause 1-417, 1-605

array variable with 1-608

INTO clause restrictions 1-544

INTO TEMP clause 1-642

IS NULL condition 1-623

joining tables in WHERE clause 1-627

LIKE or MATCHES condition 1-623

ORDER BY clause 1-634

ascending/descending order 1-637

column number with 1-638

null values in 1-637

UNION operator 1-645

privilege for See Privilege, table-level.

relational-operator condition 1-621

restrictions in routine 1-1044

routine argument as 1-831

row type 1-604

row type with 1-617

rowid

example 1-890

SELECT clause 1-598

select numbers 1-638

singleton 1-606

smart large objects with 1-890

SPL function in 1-603

subquery in WHERE clause 1-621

UNION operator 1-644

UNIQUE keyword 1-601

WHILE or FOR loop with 1-983

with

FOREACH 2-27

LET 2-40

writing rows retrieved to an ASCII file 1-769

SELFUNC routine modifier 1-1030

Send support function 1-172

Sequential cursor

active set 1-414

DECLARE with 1-316

FETCH with 1-414

with FETCH 1-414

Sequential cursor. SeeCursor, sequential.

SERIAL data type

See also SERIAL8 data type

inserting values 1-503

length 1-867

resetting values 1-79

value range 1-867

with routines 2-11

SERIAL8 data type

See also SERIAL data type

inserting values 1-503

value range 1-867

Serializable isolation level

with SET TRANSACTION 1-745

Serializable isolation level (ANSI)

with SET TRANSACTION 1-743

Server

See also Database server.

Session control block

accessed by DBINFO function 1-915

contents of 1-915

Session ID

obtaining with DBINFO() 1-915

SessionID

returned by DBINFO function 1-915

SET AUTOFREE statement

1-676 to 1-684

cursor-AUTOFREE mode 1-680, 1-683

DISABLED option 1-682

ENABLED option 1-677

global-AUTOFREE mode 1-678, 1-682

IFX_AUTOFREE environment variable with 1-683

with IFX_AUTOFREE environment variable 1-700

SET BUFFERED LOG statement 1-731

SET CONNECTION statement 1-685 to 1-691

CURRENT keyword 1-690

DEFAULT option 1-689

SET CONSTRAINT statement

trigger activated 1-282

SET 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

SET DATASKIP statement

restrictions 1-693

syntax 1-692

SET DEBUG FILE TO statement

syntax and use 1-695

with TRACE 2-57

SET DEFERRED_PREPARE statement 1-698 to 1-701

DISABLED option 1-700

ENABLED option 1-699

SET DESCRIPTOR statement 1-702 to 1-712

COUNT keyword 1-705

IDATA field 1-438

PUT with 1-561

VALUE clause 1-706

X/Open mode 1-708

SET EXPLAIN statement 1-714 to 1-721

DYNAMIC HASH JOIN information 1-716

examples 1-717

interpreting output 1-715

MERGE JOIN information 1-716

optimizer access paths 1-715

SORT SCAN information 1-716

with fragmentation 1-719

with PDQ 1-719

with SET OPTIMIZATION 1-721

with table inheritance 1-721

SET ISOLATION statement 1-722 to 1-726

default database levels 1-725

definition of isolation levels 1-724

effects of isolation 1-726

similarities to SET TRANSACTION statement 1-743

SET keyword

use in UPDATE 1-786

SET LOCK MODE statement 1-728 to 1-730

kernel locking 1-729

ON EXCEPTION statement with 1-984

setting wait period 1-729

SET LOG statement 1-731

SET OPTIMIZATION statement 1-733

HIGH keyword 1-733

LOW keyword 1-733

with SET EXPLAIN 1-721

SET PDQPRIORITY statement 1-735

SET ROLE statement 1-738

SET SESSION AUTHORIZATION statement 1-740

SET statement 1-648 to 1-674

diagnostics tables with 1-653

error options 1-652

ON EXCEPTION statement with 1-984

privileges required for executing 1-649

relationship to START VIOLATIONS TABLE 1-750

transaction mode format 1-672

trigger object modes 1-287

violations tables with 1-653

SET TRANSACTION statement

default database levels 1-746

definition of isolation levels 1-745

effects of isolation 1-747

similarities to SET ISOLATION statement 1-743

syntax 1-742

SHARE keyword, syntax in LOCK TABLE 1-525

Shared library 1-922

Shared lock

SeeLock

shared.

Side-effect index 1-178

Simple assignment 2-40

Simple large object 1-870

See also BYTE data type; Large object ; TEXT data type.

inserting 1-502

loading 1-518, 1-521

syntax 1-869

Simple large-object data type

unloading 1-771, 1-773

SIN function 1-937, 1-938

Single-threaded application 1-687

Singleton SELECT statement 1-606

SITENAME function 1-895

CREATE TABLE with 1-223

SMALLFLOAT data type 1-868

unloading 1-771

SMALLINT data type

unloading 1-771

Smart large object 1-870

column data

accessing 1-891

copying to a file 1-928

copying to a smart large object 1-930

creating from a file 1-923, 1-924, 1-926

expressions with 1-890

FILETOBLOB function 1-926

FILETOCLOB function 1-926

functions for copying 1-925

generating filename for 1-929

handle values 1-890

LOCOPY function 1-930

LOTOFILE function 1-928

modifying characteristics 1-78

syntax 1-869

See also BLOB data type; CLOB data type; Large object.

Smart large-object

loading values 1-518, 1-521

Smart large-object data type

access time 1-252

extent size 1-251

logging 1-251

unloading 1-771, 1-773

SMI.SeeSystem Monitoring Interface.

Software dependencies Intro-5

Sorting

in SELECT 1-634

SOURCEID field

with GET DESCRIPTOR 1-439

with SET DESCRIPTOR 1-712

SOURCETYPE field

with GET DESCRIPTOR 1-439

with SET DESCRIPTOR 1-712

Specific name

syntax 1-1039

SPL

statements described 2-3

SPL function

CREATE FUNCTION 1-127

CREATE FUNCTION example 1-127

cursors with 2-27

dynamic routine-name specification 1-402

executing 1-397, 1-545

optimization 1-127

punctuation for 1-127

registering 1-127

registering from inside an external routine 1-135

SPL procedure

creating with CREATE PROCEDURE 1-183

dynamic routine-name specification 1-410

executing 1-407, 1-545

optimization 1-183

registering with CREATE PROCEDURE 1-183

TRACE output file 1-695

SPL routine

comments 1-1045

executing operating system commands from 2-54

loop

expression with FOR...IN 2-26

multiple ranges for 2-25

range and expression with FOR statement 2-26

range of FOR..TO 2-24

loop, controlled 2-22

modifying, restrictions on 1-371

passing BYTE or TEXT argument to 2-18

preparing 1-545

sqexplain.out file 1-714

SQL

keywords 1-968

SQL API

comments with 1-12

concatenation operator with 1-884

SQL command script file 1-12

SQL Communications Area (SQLCA)

checking for WHERE clause 1-340

CLOSE results 1-94

cursor with 1-530

inserting rows 1-914, 1-917

processing rows 1-915

result after DATABASE 1-296

result after DESCRIBE 1-339

result after EXECUTE 1-395

result after FETCH 1-424

result after FLUSH 1-427

result after PUT 1-565

result after SELECT 1-609

SQLCODE field. See SQLCODE field.

SQLERRD array. See SQLERRD array.

SQLWARN array. See SQLWARN array.

warning when dbspace skipped 1-692

warnings in 1-822

SQL procedure

debugging 2-57

SQL statement

diagnostics area 1-441

SQL statements 1-14 to 1-21

access method 1-14

ANSI-compliant 1-19

client/server connection 1-15

comment symbol 1-12

comments examples 1-12

comments in 1-11

data access 1-15

data definition 1-16

data integrity 1-17

data manipulation 1-17

dynamic management 1-17

GET DIAGNOSTICS for 1-442

Informix extensions 1-19

messages in SQLSTATE variable 1-442

not available in routines 1-1043

parts of 1-8

restrictions on invoking routines 1-1044

segments describe shared syntax 1-7

SQLCA. See SQL Communications Area.

SQLCODE field

End of Data condition 1-823

errors in 1-822

Not Found condition 1-823

SQLCODE variable

See also SQL Communications Area (SQLCA).

sqlda structure

DESCRIBE statement with 1-338

FETCH statement with 1-420

FETCH with 1-413

in DESCRIBE 1-343

in EXECUTE 1-388, 1-393

in EXECUTE...INTO 1-391

in EXECUTE...USING 1-395

in PUT 1-557, 1-562

OPEN...USING DESCRIPTOR 1-535

SQLERRD array

count of rows 1-915

value of inserted SERIAL value 1-914

value of inserted SERIAL8 value 1-917

SQLNOTFOUND constant

with EXECUTE 1-396

with PREPARE 1-552

SQLSTATE variable

ANSI compliance 1-444

applications with 1-448

class and subclass codes 1-445

cursor with 1-95, 1-531

end of data class code 1-443

End of Data condition 1-823

errors class code 1-443

errors in 1-822

Informix-specific message, subclass code 1-444

message in 1-442

not found class code 1-443

Not Found condition 1-823

quick reference 1-443

subclass codes 1-443

success class code 1-443

warning class code 1-443

warnings in 1-822

X/Open mode 1-442

SQLWARN array

when dbspace skipped 1-692

with DBANSIWARN environment variable 1-969

with SELECT 1-609

with WHENEVER 1-822

STACK routine modifier 1-1031

START VIOLATIONS TABLE statement 1-748 to 1-766

privileges required for executing 1-751

relationship to SET 1-750

relationship to STOP VIOLATIONS TABLE 1-750

Statement

SQL

Statements not valid in stored routine 1-1043

Statement block 1-1042 to 1-1046

statements not allowed in 1-1043

Statement identifier

cursor for 1-319

DECLARE statement 1-304

definition of 1-543

dynamic 1-544

releasing 1-543

scope of 1-386

syntax

in PREPARE 1-541

use

in PREPARE 1-543

Statement local variable

declaration 1-943

expression 1-944

using 1-944

Statement. See SQL, statement.

STDEV function 1-955

STOP VIOLATIONS TABLE statement

description of 1-767

privileges required for executing 1-768

relationship to START VIOLATIONS TABLE 1-750

syntax 1-767

Stored procedure

dropping with DROP PROCEDURE 1-365

executing 1-408

handling multiple rows 2-52

inserting data with legacy SPL 1-509

replaced by SPL function 1-126, 1-734

replaced by SPL procedure 1-183

Return clause 1-1025

saving return values 1-410

sending mail from 2-55

simulating errors 2-49

See also Procedure;SPL Procedure;SPL routine.

Stored Procedure Language. See SPL.

stores7 database Intro-6

See also Demonstration database.

Strategy function

definition 1-176

optimizer use of 1-176

side-effect data 1-178

specifying in CREATE OPCLASS 1-176

Structured Query Language. See SQL.

Subquery 1-621

ALL/ANY/SOME 1-851

examples 1-626

beginning with IN keyword 1-625

condition with 1-849 to 1-853

UNIQUE or DISTINCT keyword 1-850

correlated

condition dependencies for 1-849

DISTINCT keyword with 1-601

restrictions with UNION operator 1-645

Subscripting

on character columns 1-636

Substring

in ORDER BY clause of SELECT 1-636

Subtable

inherited properties 1-216

SUM function 1-954

Support function

assign 1-172, 1-503, 1-523, 1-793

compare 1-173

defining 1-171

definition 1-178

destroy 1-172, 1-329, 1-377

export 1-172

exportbinary 1-172

import 1-172

importbinary 1-172

input 1-171

lohandles 1-173

output 1-171

receive 1-172

send 1-172

specifying in CREATE OPCLASS 1-178

Synonym

ANSI-compliant naming 1-207

chaining 1-209, 1-374

creating with CREATE SYNONYM 1-207

difference from alias 1-207

dropping with DROP SYNONYM 1-374

identical names 1-209

loading data into 1-515

naming conventions 1-1048

private 1-208

privileges 1-208

privileges. See Privilege,table-level.

public 1-208

Synonym Name segment 1-1047

Syntax conventions

elements of Intro-13

example diagram Intro-16

how to read Intro-16

icons used in Intro-14

Syntax Guide

diagrams in 1-9

Elements, Purpose, Restrictions, and segment location (Syntax) 1-9

organization 1-7

References sections 1-10

Segments sections 1-7

Sysmaster database

See alsoSystem catalog. 70

System catalog

location 1-118

syschecks 1-569

sysdepend

use with DROP VIEW 1-383

sysviews 1-569

sysxtdtypes 1-168

user-defined function information 1-127

user-defined procedure information 1-183

System descriptor area

DATA

fetching null value 1-438

host variables in 1-706

IDATA field 1-438

PRECISION field 1-437

X/Open standard with 1-437

System name, in database name 1-859

SYSTEM statement

setting environment variables with 2-55

syntax 2-54

System-defined cast

See Cast, system-defined.

System-descriptor area

allocating memory for 1-24

COUNT field. See COUNT field.

DATA field. See DATA field.

deallocating memory for 1-299

determining number of values 1-435

FETCH statement with 1-419

FETCH.. USING SQL DESCRIPTOR with 1-419

getting values from 1-436

IDATA field. See IDATA field.

ILENGTH field. See ILENGTH field.

INDICATOR field. See INDICATOR field.

item descriptors 1-25

ITYPE field. See ITYPE field.

LENGTH field. See LENGTH field.

modifying contents of 1-436

NAME field. See NAME field.

NULLABLE field. See NULLABLE field.

number of items allocated 1-705

obtaining values from 1-433

OPEN using 1-535

PRECISION field 1-437

PRECISION field. See PRECISION field.

SCALE field 1-437

SCALE field. See SCALE field.

setting number of values 1-705

setting with SET DESCRIPTOR 1-702

TYPE field 1-436, 1-437

TYPE field. See TYPE field.

undefined 1-705

with DESCRIBE 1-341, 1-342

with EXECUTE...INTO 1-390

with EXECUTE...USING 1-394

with PUT 1-561

sysxtdtypes

DESCRIBE and GET DESCRIPTOR with 1-438, 1-439

sysxtdtypes system catalog table 1-168, 1-381, 1-711, 1-712