DB2 IBM Informix

Version 10.0/8.5

IBM Informix Guide to SQL: Syntax

G251-2284-02

DB2 IBM Informix

Version 10.0/8.5

IBM Informix Guide to SQL: Syntax

G251-2284-02

Note: Before using this information and the product it supports, read the information in Notices on page D-1.

Third Edition (December 2005) This document contains proprietary information of IBM. It is provided under a license agreement and is protected by copyright law. The information contained in this publication does not include any product warranties, and any statements provided in this manual should not be interpreted as such. When you send information to IBM, you grant IBM a nonexclusive right to use or distribute the information in any way it believes appropriate without incurring any obligation to you. Copyright International Business Machines Corporation 1996, 2005. All rights reserved. US Government Users Restricted Rights Use, duplication or disclosure restricted by GSA ADP Schedule Contract with IBM Corp.

Contents
Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . ix
In This Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . ix About This Manual . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . ix Types of Users . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . x Software Dependencies . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . x Assumptions About Your Locale . . . . . . . . . . . . . . . . . . . . . . . . . . . . x Demonstration Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . x New Features in Dynamic Server, Version 10.0 . . . . . . . . . . . . . . . . . . . . . . . . xi 3 New Features in Dynamic Server, Version 10.00.xC3 . . . . . . . . . . . . . . . . . . . . . . xii 4 New Features in Dynamic Server, Version 10.00.xC4 . . . . . . . . . . . . . . . . . . . . . xiii New Features in Extended Parallel Server, Version 8.5 . . . . . . . . . . . . . . . . . . . . . xiii Documentation Conventions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xiv Typographical Conventions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xiv Feature, Product, and Platform Markup . . . . . . . . . . . . . . . . . . . . . . . . . xiv Syntax Diagrams . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xv Example Code Conventions . . . . . . . . . . . . . . . . . . . . . . . . . . . . xviii Additional Documentation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xix IBM Informix Information Center . . . . . . . . . . . . . . . . . . . . . . . . . . . xix Installation Guides . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xix Online Notes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xix Informix Error Messages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxi Manuals . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxi Online Help . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxii Accessibility . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxii IBM Informix Dynamic Server Version 10.0 and CSDK Version 2.90 Documentation Set . . . . . . . . . xxii Compliance with Industry Standards . . . . . . . . . . . . . . . . . . . . . . . . . . xxiv IBM Welcomes Your Comments . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxiv

Chapter 1. Overview of SQL Syntax . . . . . . . . . . . . . . . . . . . . . . . 1-1

In This Chapter . . . . . . . . . . . . . . . . How to Enter SQL Statements . . . . . . . . . . . Using Syntax Diagrams and Syntax Tables . . . . . . Using Examples . . . . . . . . . . . . . . Using Related Information . . . . . . . . . . . How to Enter SQL Comments . . . . . . . . . . . Examples of SQL Comment Symbols. . . . . . . . Non-ASCII Characters in SQL Comments . . . . . . Categories of SQL Statements . . . . . . . . . . . Data Definition Language Statements . . . . . . . Data Manipulation Language Statements . . . . . . Data Integrity Statements . . . . . . . . . . . Cursor Manipulation Statements . . . . . . . . . Dynamic Management Statements . . . . . . . . Data Access Statements . . . . . . . . . . . . Optimization Statements . . . . . . . . . . . . Routine Definition Statements . . . . . . . . . . Auxiliary Statements . . . . . . . . . . . . . Client/Server Connection Statements . . . . . . . Optical Subsystem Statements (IDS) . . . . . . . . ANSI/ISO Compliance and Extensions . . . . . . . . ANSI/ISO-Compliant Statements . . . . . . . . . ANSI/ISO-Compliant Statements with Informix Extensions Statements that are Extensions to the ANSI/ISO Standard . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-1 1-1 1-2 1-3 1-3 1-3 1-5 1-5 1-6 1-6 1-7 1-7 1-7 1-7 1-7 1-8 1-8 1-8 1-8 1-8 1-8 1-8 1-8 1-9

Chapter 2. SQL Statements. . . . . . . . . . . . . . . . . . . . . . . . . . . 2-1

Copyright IBM Corp. 1996, 2005

iii

In This Chapter . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-3 ALLOCATE COLLECTION . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-4 ALLOCATE DESCRIPTOR . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-6 ALLOCATE ROW . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-8 ALTER ACCESS_METHOD . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-9 ALTER FRAGMENT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-11 ALTER FUNCTION . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-31 ALTER INDEX . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-33 ALTER PROCEDURE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-35 ALTER ROUTINE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-37 ALTER SEQUENCE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-40 ALTER TABLE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-43 BEGIN WORK . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-66 CLOSE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-68 CLOSE DATABASE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-71 COMMIT WORK . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-73 CONNECT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-75 CREATE ACCESS_METHOD . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-82 CREATE AGGREGATE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-84 CREATE CAST . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-87 CREATE DATABASE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-90 CREATE DISTINCT TYPE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-93 CREATE DUPLICATE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-96 CREATE EXTERNAL TABLE (XPS) . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-98 CREATE FUNCTION . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-107 CREATE FUNCTION FROM . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-114 CREATE INDEX . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-116 CREATE OPAQUE TYPE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-136 CREATE OPCLASS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-141 CREATE PROCEDURE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-145 CREATE PROCEDURE FROM . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-153 CREATE ROLE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-155 CREATE ROUTINE FROM . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-157 CREATE ROW TYPE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-158 CREATE SCHEMA . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-162 CREATE SCRATCH TABLE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-164 CREATE SEQUENCE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-165 CREATE SYNONYM . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-168 CREATE TABLE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-171 CREATE TEMP TABLE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-208 CREATE Temporary TABLE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-209 CREATE TRIGGER . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-216 CREATE VIEW . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-247 3 CREATE XADATASOURCE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-252 3 CREATE XADATASOURCE TYPE . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-253 DATABASE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-255 DEALLOCATE COLLECTION . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-257 DEALLOCATE DESCRIPTOR . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-258 DEALLOCATE ROW . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-259 DECLARE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-260 DELETE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-275 DESCRIBE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-281 DESCRIBE INPUT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-286 DISCONNECT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-291 DROP ACCESS_METHOD . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-294 DROP AGGREGATE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-295 DROP CAST . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-296 DROP DATABASE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-297 DROP DUPLICATE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-299 DROP FUNCTION . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-300 DROP INDEX . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-302

iv

IBM Informix Guide to SQL: Syntax

DROP OPCLASS . . . . . DROP PROCEDURE . . . . DROP ROLE . . . . . . . DROP ROUTINE . . . . . DROP ROW TYPE . . . . . DROP SEQUENCE . . . . . DROP SYNONYM . . . . . DROP TABLE . . . . . . DROP TRIGGER. . . . . . DROP TYPE . . . . . . . DROP VIEW . . . . . . . DROP XADATASOURCE . . . DROP XADATASOURCE TYPE EXECUTE . . . . . . . . EXECUTE FUNCTION . . . EXECUTE IMMEDIATE . . . EXECUTE PROCEDURE . . . FETCH . . . . . . . . . FLUSH . . . . . . . . . FREE . . . . . . . . . GET DESCRIPTOR . . . . . GET DIAGNOSTICS . . . . GRANT . . . . . . . . GRANT FRAGMENT . . . . INFO . . . . . . . . . INSERT . . . . . . . . . LOAD . . . . . . . . . LOCK TABLE . . . . . . MERGE . . . . . . . . MOVE TABLE . . . . . . OPEN . . . . . . . . . OUTPUT . . . . . . . . PREPARE . . . . . . . . PUT . . . . . . . . . . RENAME COLUMN . . . . RENAME DATABASE . . . . RENAME INDEX . . . . . RENAME SEQUENCE. . . . RENAME TABLE . . . . . REVOKE . . . . . . . . REVOKE FRAGMENT . . . . ROLLBACK WORK . . . . SAVE EXTERNAL DIRECTIVES SELECT . . . . . . . . SET ALL_MUTABLES . . . . SET AUTOFREE . . . . . . SET COLLATION . . . . . SET CONNECTION . . . . SET CONSTRAINTS . . . . SET Database Object Mode . . SET DATASKIP . . . . . . SET DEBUG FILE . . . . . SET Default Table Space . . . SET Default Table Type . . . SET DEFERRED_PREPARE . . SET DESCRIPTOR . . . . . SET ENCRYPTION PASSWORD SET ENVIRONMENT . . . . SET EXPLAIN . . . . . . SET INDEX . . . . . . . SET INDEXES . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

2-304 2-305 2-307 2-308 2-310 2-312 2-313 2-314 2-316 2-317 2-318 2-319 2-320 2-321 2-329 2-334 2-336 2-344 2-353 2-355 2-357 2-362 2-371 2-388 2-393 2-395 2-407 2-413 2-416 2-419 2-424 2-431 2-433 2-442 2-449 2-451 2-452 2-453 2-454 2-456 2-471 2-474 2-476 2-479 2-527 2-529 2-531 2-534 2-538 2-539 2-545 2-547 2-549 2-550 2-552 2-554 2-560 2-563 2-568 2-573 2-574

Contents

SET ISOLATION . . . . . SET LOCK MODE . . . . . SET LOG . . . . . . . . SET OPTIMIZATION . . . . SET PDQPRIORITY . . . . SET PLOAD FILE . . . . . SET Residency . . . . . . SET ROLE . . . . . . . . SET SCHEDULE LEVEL . . . SET SESSION AUTHORIZATION SET STATEMENT CACHE . . SET TABLE . . . . . . . SET TRANSACTION . . . . SET Transaction Mode . . . . SET TRIGGERS . . . . . . START VIOLATIONS TABLE . STOP VIOLATIONS TABLE . . 4 TRUNCATE (IDS) . . . . . TRUNCATE (XPS) . . . . . UNLOAD . . . . . . . . UNLOCK TABLE . . . . . UPDATE . . . . . . . . UPDATE STATISTICS . . . . WHENEVER . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . .

2-575 2-580 2-582 2-584 2-586 2-589 2-590 2-592 2-594 2-595 2-597 2-601 2-602 2-606 2-608 2-609 2-622 2-624 2-628 2-630 2-635 2-636 2-649 2-659

Chapter 3. SPL Statements . . . . . . . . . . . . . . . . . . . . . . . . . . . 3-1

In This Chapter . . CALL . . . . . CASE . . . . . CONTINUE . . . DEFINE . . . . EXIT . . . . . FOR . . . . . FOREACH . . . IF . . . . . . LET . . . . . ON EXCEPTION . RAISE EXCEPTION RETURN . . . SYSTEM . . . . TRACE . . . . WHILE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3-1 . 3-2 . 3-4 . 3-6 . 3-7 . 3-16 . 3-17 . 3-20 . 3-25 . 3-28 . 3-31 . 3-35 . 3-37 . 3-39 . 3-41 . 3-43

Chapter 4. Data Types and Expressions . . . . . . . . . . . . . . . . . . . . . 4-1

In This Chapter . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4-1 Scope of Segment Descriptions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4-1 Use of Segment Descriptions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4-1 Collection Subquery . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4-3 Condition . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4-5 Data Type . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4-18 DATETIME Field Qualifier. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4-32 Expression . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4-34 INTERVAL Field Qualifier . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4-127 Literal Collection . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4-129 Literal DATETIME . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4-132 Literal INTERVAL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4-135 Literal Number . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4-137 Literal Row . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4-139 Quoted String . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4-142 Relational Operator . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4-146

vi

IBM Informix Guide to SQL: Syntax

Chapter 5. Other Syntax Segments . . . . . . . . . . . . . . . . . . . . . . . 5-1

In This Chapter . . . . . Arguments . . . . . . Collection-Derived Table . . Database Name . . . . Database Object Name . . External Routine Reference Identifier . . . . . . Jar Name . . . . . . Optimizer Directives . . . Owner Name . . . . . Purpose Options . . . . Return Clause . . . . . Routine Modifier . . . . Routine Parameter List . . Shared-Object Filename . . Specific Name . . . . . Statement Block . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5-1 . 5-2 . 5-5 . 5-15 . 5-17 . 5-20 . 5-22 . 5-33 . 5-34 . 5-43 . 5-46 . 5-51 . 5-54 . 5-61 . 5-65 . 5-68 . 5-69

Appendix A. Reserved Words for IBM Informix Dynamic Server . . . . . . . . . . . A-1 Appendix B. Reserved Words for IBM Informix Extended Parallel Server . . . . . . . B-1 Appendix C. Accessibility . . . . . . . . . . . . . . . . . . . . . . . . . . . C-1 Notices . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . D-1 Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . X-1

Contents

vii

viii

IBM Informix Guide to SQL: Syntax

Introduction
In This Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . ix About This Manual . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . ix Types of Users . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . x Software Dependencies . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . x Assumptions About Your Locale . . . . . . . . . . . . . . . . . . . . . . . . . . . . x Demonstration Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . x New Features in Dynamic Server, Version 10.0 . . . . . . . . . . . . . . . . . . . . . . . . xi 3 New Features in Dynamic Server, Version 10.00.xC3 . . . . . . . . . . . . . . . . . . . . . . xii 4 New Features in Dynamic Server, Version 10.00.xC4 . . . . . . . . . . . . . . . . . . . . . xiii New Features in Extended Parallel Server, Version 8.5 . . . . . . . . . . . . . . . . . . . . . xiii Documentation Conventions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xiv Typographical Conventions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xiv Feature, Product, and Platform Markup . . . . . . . . . . . . . . . . . . . . . . . . . xiv Syntax Diagrams . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xv How to Read a Command-Line Syntax Diagram . . . . . . . . . . . . . . . . . . . . . xvi Keywords and Punctuation . . . . . . . . . . . . . . . . . . . . . . . . . . . xvii Identifiers and Names . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xviii Example Code Conventions . . . . . . . . . . . . . . . . . . . . . . . . . . . . xviii Additional Documentation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xix IBM Informix Information Center . . . . . . . . . . . . . . . . . . . . . . . . . . . xix Installation Guides . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xix Online Notes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xix Locating Online Notes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xx Online Notes Filenames . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxi Informix Error Messages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxi Manuals . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxi Online Manuals . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxi Printed Manuals . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxii Online Help . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxii Accessibility . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxii IBM Informix Dynamic Server Version 10.0 and CSDK Version 2.90 Documentation Set . . . . . . . . . xxii Compliance with Industry Standards . . . . . . . . . . . . . . . . . . . . . . . . . . xxiv IBM Welcomes Your Comments . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxiv

In This Introduction
This introduction provides an overview of the information in this manual and describes the documentation conventions that it uses.

About This Manual

This manual describes the syntax of the Structured Query Language (SQL) and Stored Procedure Language (SPL) statements for Version 10.0 of IBM Informix Dynamic Server and Version 8.5 of IBM Informix Extended Parallel Server. This manual is a companion volume to the IBM Informix Guide to SQL: Reference, the IBM Informix Guide to SQL: Tutorial, and the IBM Informix Database Design and Implementation Guide. The IBM Informix Guide to SQL: Reference provides reference information about the system catalog, the built-in SQL data types, and environment variables that can affect SQL statements. The IBM Informix Guide to SQL: Tutorial shows how to use basic and advanced SQL and SPL routines to

Copyright IBM Corp. 1996, 2005

ix

access and manipulate the data in your databases. The IBM Informix Database Design and Implementation Guide shows how to use SQL to implement and manage relational databases.

Types of Users
This manual is written for the following users: v Database users v Database administrators v Database-application programmers This manual assumes that you have the following background: v A working knowledge of your computer, your operating system, and the utilities that your operating system provides v Some experience working with relational databases or exposure to database concepts v Some experience with computer programming If you have limited experience with relational databases, SQL, or your operating system, refer to the IBM Informix Getting Started Guide for your database server for a list of supplementary titles.

Software Dependencies
This manual assumes that you are using one of the following database servers: v IBM Informix Extended Parallel Server, Version 8.5 v IBM Informix Dynamic Server, Version 10.0

Assumptions About Your Locale

IBM Informix products can support many languages, cultures, and code sets. All culture-specific information is brought together in a single environment, called a Global Language Support (GLS) locale. This manual assumes that you use the U.S. 8859-1 English locale as the default locale. The default is en_us.8859-1 (ISO 8859-1) on UNIX platforms or en_us.1252 (Microsoft 1252) for Windows environments. These locales support U.S. English format conventions for dates, times, and currency, and also support the ISO 8859-1 or Microsoft 1252 code set, which includes the ASCII code set plus many 8-bit characters such as , , and . If you plan to use non-ASCII characters in your data or in SQL identifiers, or if you want to conform to localized collation rules of character data, you need to specify an appropriate nondefault locale. For instructions on how to specify a nondefault locale, additional syntax, and other considerations related to GLS locales, see the IBM Informix GLS User's Guide.

Demonstration Databases
The DBAccess utility, which is provided with your IBM Informix database server products, includes one or more of the following demonstration databases: v The stores_demo database illustrates a relational schema with information about a fictitious wholesale sporting-goods distributor. Many examples in IBM Informix manuals are based on the stores_demo database.

IBM Informix Guide to SQL: Syntax

v For Extended Parallel Server only, the sales_demo database illustrates a dimensional schema for data-warehousing applications. For conceptual information about dimensional data modeling, see the IBM Informix Database Design and Implementation Guide. v For Dynamic Server only, the superstores_demo database illustrates an object-relational schema that contains examples of extended data types, data-type inheritance and table inheritance, and user-defined routines. For information about how to create and populate the demonstration databases, see the IBM Informix DBAccess User's Guide. For descriptions of the databases and their contents, see the IBM Informix Guide to SQL: Reference. The scripts that you use to install the demonstration databases reside in the $INFORMIXDIR/bin directory on UNIX platforms and in the %INFORMIXDIR%\bin directory in Windows environments.

New Features in Dynamic Server, Version 10.0

For a comprehensive list of new database server features, see the IBM Informix Getting Started Guide. This section lists new features introduced in Version 10.00.xC1 that are relevant to this manual. The following list provides information about the new features of IBM Informix Dynamic Server, Version 10.00.xC1, that this manual describes. v The following new SQL statements are documented in Chapter 2, SQL Statements, on page 2-1: SAVE EXTERNAL DIRECTIVES SET ENCRYPTION PASSWORD SET ENVIRONMENT OPTCOMPIND v The following SQL statements support new syntax in Version 10.0: Statement ALTER FRAGMENT ALTER TABLE CREATE INDEX CREATE INDEX CREATE TABLE DROP INDEX GRANT REVOKE New Keywords PARTITION PARTITION ONLINE PARTITION PARTITION ONLINE DEFAULT ROLE DEFAULT ROLE

SET ROLE DEFAULT v The DBSA (Database Server Administrator) can use the GRANT ROLE EXTEND statement to assign the new built-in role EXTEND to users. When this security feature is enabled, only users who hold the EXTEND role can register or drop external UDRs or create shared libraries in the database. v This release introduces default roles that the DBA can grant or revoke. These take effect when a user who holds a default role connects to the database, so that appropriate access privileges are available for using applications that do not include GRANT statements that reference individual users. New SQL functions can return the current role or the default role of the user.
Introduction

xi

v The CREATE INDEX and DROP INDEX statements can specify the ONLINE keyword to define or drop an index without applying table locks that might interfere with concurrent users. v Tables and indexes now can use fragmentation strategies that define multiple named fragments within the same dbspace. v Data values returned by distributed DML operations or UDRs in databases of the same Dynamic Server instance now can return the built-in opaque data types BLOB, BOOLEAN, CLOB, and LVARCHAR. They can also return UDTs, and DISTINCT types whose base types are built-in types, if the UDTs and DISTINCT types are explicitly cast to built-in data types, and if the casts, DISTINCT types, and UDTs are defined in all the participating databases. v The DS_NONPDQ_QUERY_MEM configuration parameter can be set to improve the performance of certain non-PDQ queries. v This release supports external optimizer directives that reside in the database and that are automatically applied to specified queries. These can be enabled or disabled for a given session by a configuration parameter. v The SET ENVIRONMENT OPTCOMPIND statement can reset the value of OPTCOMPIND dynamically, overriding the corresponding configuration parameter or environment variable setting. This can improve performance during sessions that use queries for both DS (decision-support) and OLTP (online-transaction-processing). v The SET ENCRYPTION PASSWORD statement can specify a session password to support column-level and cell-level encryption of sensitive data. This statement, and new built-in encryption and decryption functions, can support compliance with data security and data confidentiality requirements that are mandated for some industries and jurisdictions. v User-defined functions can include multiple INOUT parameters. 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3

New Features in Dynamic Server, Version 10.00.xC3

For a comprehensive list of new database server features in Version 10.00.xC3, see the IBM Informix Getting Started Guide. This section lists new features introduced in Version 10.00.xC3 that this manual describes. New data definition language (DDL) statements support XA-compliant external data sources. Use these to apply the transactional semantics of Dynamic Server and two-phase commit protocols to transactions with external data sources: v CREATE XADATASOURCE v CREATE XADATASOURCE TYPE v DROP XADATASOURCE v DROP XADATASOURCE TYPE New Projection Clause syntax of the SELECT statement can control the number of qualifying rows in the result set of a query: v SKIP offset v LIMIT max The SKIP offset clause excludes the first offset qualifying rows from the result set, for offset an integer. If you also include the LIMIT max clause (where LIMIT is a keyword synonym for FIRST), no more than max rows are returned. The offset and max values can be specified as literal integers in the SERIAL8 range, or as host variables, or as local SPL variables. You can save the result set as a collection-derived table (CDT).

xii

IBM Informix Guide to SQL: Syntax

3 3 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4

If you also include the ORDER BY clause, qualifying rows are first arranged by the ORDER BY specification before SKIP offset and LIMIT max clauses are applied.

New Features in Dynamic Server, Version 10.00.xC4

For a comprehensive list of new database server features in Version 10.00.xC4, see the IBM Informix Getting Started Guide. In addition to documenting the new feature of Version 10.00.xC4 that is described in this section, this manual corrects errata that have been identified since the previous edition. A new TRUNCATE data definittion language (DDL) statement of SQL removes all active rows and the entire B-tree structure of all indexes from all partitions of a specified table in the local database. Storage formerly occupied by these data rows and indexes becomes available for other tables, or you can optionally reserve the space for reuse in subsequent operations on the same table or index partition. While the TRUNCATE statement is executing, no other session can lock or modify the table until TRUNCATE completes and is either committed or rolled back. In a database with transaction logging, a subsequent ROLLBACK statement can restore the table to its state before the transaction that included TRUNCATE began, but no operation except COMMIT or ROLLBACK can follow TRUNCATE within a transaction. After the TRUNCATE statement successfully executes, the database server automatically resets the statistics and distributions for the table and for its indexes, as if UPDATE STATISTICS had been executed for that table. Any Delete trigger that is defined on the table is ignored when TRUNCATE executes, and the serial counter for SERIAL and SERIAL8 columns is not reset. The table that you truncate can be a raw, temporary, or standard table. It can also be a virtual table, or a table with a virtual-index interface, provided that the virtual table or the virtual-index interface has a valid am_truncate( ) purpose function that was explicitly registered in the sysams system catalog table. This feature offers performance advantages over the DELETE table statement, because TRUNCATE empties a table without logging individual deleted rows or index updates, and without activating Delete triggers. Unlike the DROP TABLE statement, TRUNCATE does not remove the definitions of the table or its indexes, triggers, views, constraints, privileges, and other properties from the system catalog. The TRUNCATE statement can also make storage management more efficient through its ability to free space, or to reuse the space for the same table and its indexes.

New Features in Extended Parallel Server, Version 8.5

The following new SQL statements of IBM Informix Extended Parallel Server, Version 8.5 are documented in Chapter 2, SQL Statements, on page 2-1: v MERGE v MOVE v SET ALL_MUTABLES For a complete list of new features in Extended Parallel Server, Version 8.5, see the Getting Started With IBM Informix Extended Parallel Server Guide.

Introduction

xiii

Documentation Conventions
This section describes the conventions that this manual uses. These conventions make it easier to gather information from this and other volumes in the documentation set. The following conventions are discussed: v Typographical conventions v Other conventions v Syntax diagrams v Command-line conventions v Example code conventions

Typographical Conventions
This manual uses the following conventions to introduce new terms, illustrate screen displays, describe command syntax, and so forth.
Convention KEYWORD italics Meaning Keywords of SQL, SPL, and some other programming languages appear in uppercase letters in a serif font. Within text, new terms and emphasized words appear in italics. Within syntax and code examples, variable values that you are to specify appear in italics. Names of program entities (such as classes, events, and tables), environment variables, file and pathnames, and interface elements (such as icons, menu items, and buttons) appear in boldface. Information that the product displays and information that you enter appear in a monospace typeface. Keys that you are to press appear in uppercase letters in a sans serif font. This symbol indicates a menu item. For example, Choose Tools > Options means choose the Options item from the Tools menu.

boldface

monospace KEYSTROKE >

Tip: When you are instructed to enter characters or to execute a command, immediately press RETURN after the entry. When you are instructed to type the text or to press other keys, no RETURN is required.

Feature, Product, and Platform Markup

Feature, product, and platform markup identifies paragraphs that contain feature-specific, product-specific, or platform-specific information. Some examples

xiv

IBM Informix Guide to SQL: Syntax

of this markup follow: Dynamic Server Identifies information that is specific to IBM Informix Dynamic Server End of Dynamic Server Windows Only Identifies information that is specific to the Windows environment End of Windows Only This markup can apply to one or more paragraphs within a section. When an entire section applies to a particular product or platform, this is noted as part of the heading text, for example: Table Sorting (Linux)

Syntax Diagrams
This guide uses syntax diagrams built with the following components to describe the syntax for statements and all commands other than system-level commands. Syntax diagrams depicting SQL and command-line statements have changed in the following ways: v The symbols at the beginning and end of statements are double arrows. v The symbols at the beginning and end of syntax segment diagrams are vertical lines. v How many times a loop can be repeated is explained in a diagram footnote, whose marker appears above the path that is describes. v Syntax statements that are longer than one line continue on the next line. v Product or condition-specific paths are explained in diagram footnotes, whose markers appear above the path that they describe. v Cross-references to the descriptions of other syntax segments appear as diagram footnotes, whose markers immediately follow the name of the segment that they reference. The following table describes syntax diagram components.
Component represented in PDF Component represented in HTML >>---------------------Meaning Statement begins.

----------------------->

Statement continues on next line. Statement continues from previous line. Statement ends. Required item.

>--------------------------------------------->< --------SELECT----------

Introduction

xv

Component represented in PDF

Component represented in HTML --+-----------------+--------LOCAL------ ---+-----ALL-------+--+--DISTINCT-----+ ---UNIQUE------

Meaning Optional item.

Required item with choice. One and only one item must be present.

---+------------------+--+--FOR UPDATE-----+ --FOR READ ONLY-- .---NEXT---------. ----+----------------+--+---PRIOR--------+ ---PREVIOUS-----

Optional items with choice are shown below the main line, one of which you might specify. The values below the main line are optional, one of which you might specify. If you do not specify an item, the value above the line will be used as the default. Optional items. Several items are allowed; a comma must precede each repetition.

.-------,-----------. V | ---+-----------------+--+---index_name---+ ---table_name---

>>-| Table Reference |->< Table Reference |--+-----view--------+--| +------table------+ ----synonym------

Reference to a syntax segment. Syntax segment.

How to Read a Command-Line Syntax Diagram

The following command-line syntax diagram uses some of the elements listed in the table in the previous section. Creating a No-Conversion Job
onpladm create job job -p project -n -d device -D database

-t table

xvi

IBM Informix Guide to SQL: Syntax

(1) Setting the Run Mode -S server -T target

Notes: 1 See page Z-1

The second line in this diagram has a segment named Setting the Run Mode, which according to the diagram footnote, is on page Z-1. If this was an actual cross-reference, you would find this segment in on the first page of Appendix Z. Instead, this segment is shown in the following segment diagram. Notice that the diagram uses segment start and end components. Setting the Run Mode:
l c -f d p a u n N

To see how to construct a command correctly, start at the top left of the main diagram. Follow the diagram to the right, including the elements that you want. The elements in this diagram are case sensitive because the illustrates utility syntax. Other types of syntax, such as SQL, are not case sensitive. The Creating a No-Conversion Job diagram illustrates the following steps: 1. Type onpladm create job and then the name of the job. 2. Optionally, type -p and then the name of the project. 3. Type the following required elements: v -n v -d and the name of the device v -D and the name of the database v -t and the name of the table 4. Optionally, you can choose one or more of the following elements and repeat them an arbitrary number of times: v -S and the server name v -T and the target server name v The run mode. To set the run mode, follow the Setting the Run Mode segment diagram to type -f, optionally type d, p, or a, and then optionally type l or u. 5. Follow the diagram to the terminator. Your diagram is complete.

Keywords and Punctuation

Keywords are words reserved for statements and all commands except system-level commands. When a keyword appears in a syntax diagram, it is
Introduction

xvii

shown in uppercase letters. When you use a keyword in a command, you can write it in uppercase or lowercase letters, but you must spell the keyword exactly as it appears in the syntax diagram. You must also use any punctuation in your statements and commands exactly as shown in the syntax diagrams.

Identifiers and Names

Variables serve as placeholders for identifiers and names in the syntax diagrams and examples. You can replace a variable with an arbitrary name, identifier, or literal, depending on the context. Variables are also used to represent complex syntax elements that are expanded in additional syntax diagrams. When a variable appears in a syntax diagram, an example, or text, it is shown in lowercase italic. The following syntax diagram uses variables to illustrate the general form of a simple SELECT statement.
SELECT column_name FROM table_name

When you write a SELECT statement of this form, you replace the variables column_name and table_name with the name of a specific column and table.

Example Code Conventions

Examples of SQL code occur throughout this manual. Except as noted, the code is not specific to any single IBM Informix application development tool. If only SQL statements are listed in the example, they are not delimited by semicolons. For instance, you might see the code in the following example:
CONNECT TO stores_demo ... DELETE FROM customer WHERE customer_num = 121 ... COMMIT WORK DISCONNECT CURRENT

To use this SQL code for a specific product, you must apply the syntax rules for that product. For example, if you are using DBAccess, you must delimit multiple statements with semicolons. If you are using an SQL API, you must use EXEC SQL at the start of each statement and a semicolon (or other appropriate delimiter) at the end of the statement. Tip: Ellipsis points in a code example indicate that more code would be added in a full application, but it is not necessary to show it to describe the concept being discussed. For detailed directions on using SQL statements for a particular application development tool or SQL API, see the manual for your product.

xviii

IBM Informix Guide to SQL: Syntax

Additional Documentation
For additional information, refer to the following types of documentation: v Installation guides v Online notes v Informix error messages v Manuals v Online help

IBM Informix Information Center

The Informix Dynamic Server Information Center integrates the entire IBM Informix Dynamic Server 10.0 and IBM Informix Client SDK (CSDK) 2.90 documentation sets in both HTML and PDF formats. The Information Center provides full text search, a master index, logical categories, easy navigation, and links to troubleshooting and support files. The IBM Informix Information Center site is located at http://publib.boulder.ibm.com/infocenter/ids9help/index.jsp.

Installation Guides
Installation guides are located in the /doc directory of the product CD or in the /doc directory of the products compressed file if you downloaded it from the IBM Web site. Alternatively, you can obtain installation guides from the IBM Informix Online Documentation site at http://www.ibm.com/software/data/informix/pubs/library/ or the IBM Informix Information Center at http://publib.boulder.ibm.com/infocenter/ids9help/index.jsp.

Online Notes
The following sections describe the online files that supplement the information in this manual. Please examine these files before you begin using your IBM Informix product. They contain vital information about application and performance issues.

Introduction

xix

Online File TOC Notes

Description The TOC (Table of Contents) notes file provides a comprehensive directory of hyperlinks to the release notes, the fixed and known defects file, and all the documentation notes files for individual manual titles. The documentation notes file for each manual contains important information and corrections that supplement the information in the manual or information that was modified since publication. The release notes file describes feature differences from earlier versions of IBM Informix products and how these differences might affect current products. For some products, this file also contains information about any known problems and their workarounds.

Format HTML

Documentation Notes

HTML, text

Release Notes

HTML, text

Machine Notes

(Non-Windows platforms only) The machine text notes file describes any platform-specific actions that you must take to configure and use IBM Informix products on your computer. This text file lists issues that have been identified with the current version. It also lists customer-reported defects that have been fixed in both the current version and in previous versions. text

Fixed and Known Defects File

Locating Online Notes

Online notes are available from the IBM Informix Online Documentation site at http://www.ibm.com/software/data/informix/pubs/library/ and in the IBM Informix Information Center at http://publib.boulder.ibm.com/infocenter/ids9help/index.jsp. Additionally you can locate these files before or after installation as described below. Before Installation All online notes are located in the /doc directory of the product CD. The easiest way to access the documentation notes, the release notes, and the fixed and known defects file is through the hyperlinks from the TOC notes file. The machine notes file and the fixed and known defects file are only provided in text format. After Installation On UNIX platforms in the default locale, the documentation notes, release notes, and machine notes files appear under the $INFORMIXDIR/release/en_us/0333 directory. Dynamic Server On Windows the documentation and release notes files appear in the Informix folder. To display this folder, choose Start > Programs > IBM product name version > Documentation Notes or Release Notes from the taskbar.

xx

IBM Informix Guide to SQL: Syntax

Machine notes do not apply to Windows platforms. End of Dynamic Server

Online Notes Filenames

Online notes have the following file formats:
Online File TOC Notes Documentation Notes Release Notes Machine Notes Fixed and Known Defects File File Format prod_os_toc_version.html prod_bookname_docnotes_version.html/txt prod_os_relnotes_version.html/txt prod_machine_notes_version.txt prod_defects_version.txt Examples ids_win_toc_10.0.html ids_hpl_docnotes_10.0.html ids_unix_relnotes_10.0.txt ids_machine_notes_10.0.txt ids_defects_10.0.txt client_defects_2.90.txt ids_win_fixed_and_known _defects_10.0.txt

ids_win_fixed_and_known _defects_version.txt

Informix Error Messages

This file is a comprehensive index of error messages and their corrective actions for the Informix products and version numbers. On UNIX platforms, use the finderr command to read the error messages and their corrective actions. Dynamic Server On Windows, use the Informix Error Messages utility to read error messages and their corrective actions. To display this utility, choose Start > Programs > IBM product name version > Informix Error Messages from the taskbar. End of Dynamic Server You can also access these files from the IBM Informix Online Documentation site at http://www.ibm.com/software/data/informix/pubs/library/ or in the IBM Informix Information Center at http://publib.boulder.ibm.com/infocenter/ids9help/index.jsp.

Manuals
Online Manuals
A CD that contains your manuals in electronic format is provided with your IBM Informix products. You can install the documentation or access it directly from the CD. For information about how to install, read, and print online manuals, see the installation insert that accompanies your CD. You can also obtain the same online manuals from the IBM Informix Online Documentation site at http://www.ibm.com/software/data/informix/pubs/library/ or in the IBM Informix Information Center at http://publib.boulder.ibm.com/infocenter/ids9help/index.jsp.

Introduction

xxi

Printed Manuals
To order hardcopy manuals, contact your sales representative or visit the IBM Publications Center Web site at http://www.ibm.com/software/howtobuy/data.html.

Online Help
IBM Informix online help, provided with each graphical user interface (GUI), displays information about those interfaces and the functions that they perform. Use the help facilities that each GUI provides to display the online help.

Accessibility
IBM is committed to making our documentation accessible to persons with disabilities. Our books are available in HTML format so that they can be accessed with assistive technology such as screen reader software. The syntax diagrams in our manuals are available in dotted decimal format, which is an accessible format that is available only if you are using a screen reader. For more information about the dotted decimal format, see the Accessibility appendix.

IBM Informix Dynamic Server Version 10.0 and CSDK Version 2.90 Documentation Set
The following tables list the manuals that are part of the IBM Informix Dynamic Server, Version 10.0 and the CSDK Version 2.90, documentation set. PDF and HTML versions of these manuals are available at http://www.ibm.com/software/data/informix/pubs/library/ or in the IBM Informix Information Center at http://publib.boulder.ibm.com/infocenter/ids9help/index.jsp. You can order hardcopy versions of these manuals from the IBM Publications Center at http://www.ibm.com/software/howtobuy/data.html.
Table 1. Database Server Manuals Manual Administrators Guide Administrators Reference Subject Understanding, configuring, and administering your database server. Reference material for Informix Dynamic Server, such as the syntax of database server utilities onmode and onstat, and descriptions of configuration parameters, the sysmaster tables, and logical-log records. The concepts and methods you need to understand when you use the ON-Bar and ontape utilities to back up and restore data. Using the following DataBlade modules that are included with Dynamic Server: v MQ DataBlade module, to allow IBM Informix database applications to communicate with other MQSeries applications. v Large Object Locator, a foundation DataBlade module that can be used by other modules that create or store large-object data. DB-Access Users Guide DataBlade API Function Reference DataBlade API Programmers Guide Using the DB-Access utility to access, modify, and retrieve data from Informix databases. The DataBlade API functions and the subset of ESQL/C functions that the DataBlade API supports. You can use the DataBlade API to develop client LIBMI applications and C user-defined routines that access data in Informix databases. The DataBlade API, which is the C-language application-programming interface provided with Dynamic Server. You use the DataBlade API to develop client and server applications that access data stored in Informix databases.

Backup and Restore Guide Built-In DataBlade Modules Users Guide

xxii

IBM Informix Guide to SQL: Syntax

Table 1. Database Server Manuals (continued) Manual Database Design and Implementation Guide Enterprise Replication Guide Error Messages file Getting Started Guide Subject Designing, implementing, and managing your Informix databases. How to design, implement, and manage an Enterprise Replication system to replicate data between multiple database servers. Causes and solutions for numbered error messages you might receive when you work with IBM Informix products. Describes the products bundled with IBM Informix Dynamic Server and interoperability with other IBM products. Summarizes important features of Dynamic Server and the new features for each version. Information about Informix databases, data types, system catalog tables, environment variables, and the stores_demo demonstration database. Detailed descriptions of the syntax for all Informix SQL and SPL statements. A tutorial on SQL, as implemented by Informix products, that describes the basic ideas and terms that are used when you work with a relational database. Accessing and using the High-Performance Loader (HPL), to load and unload large quantities of data to and from Informix databases. Instructions for installing IBM Informix Dynamic Server on Windows. Instructions for installing IBM Informix Dynamic Server on UNIX and Linux. Writing user-defined routines (UDRs) in the Java programming language for Informix Dynamic Server with J/Foundation. Conversion to and reversion from the latest versions of Informix database servers. Migration between different Informix database servers. The Optical Subsystem, a utility that supports the storage of BYTE and TEXT data on optical disk. Configuring and operating IBM Informix Dynamic Server to achieve optimum performance. Creating R-tree indexes on appropriate data types, creating new operator classes that use the R-tree access method, and managing databases that use the R-tree secondary access method. The IBM Informix subagent that allows a Simple Network Management Protocol (SNMP) network manager to monitor the status of Informix servers. Informix Storage Manager (ISM), which manages storage devices and media for your Informix database server. The secure-auditing capabilities of Dynamic Server, including the creation and maintenance of audit logs. How to define new data types and enable user-defined routines (UDRs) to extend IBM Informix Dynamic Server. Creating a secondary access method (index) with the Virtual-Index Interface (VII) to extend the built-in indexing schemes of IBM Informix Dynamic Server. Typically used with a DataBlade module. Creating a primary access method with the Virtual-Table Interface (VTI) so that users have a single SQL interface to Informix tables and to data that does not conform to the storage scheme of Informix Dynamic Server.

Guide to SQL: Reference Guide to SQL: Syntax Guide to SQL: Tutorial High-Performance Loader Users Guide Installation Guide for Microsoft Windows Installation Guide for UNIX and Linux J/Foundation Developers Guide Migration Guide Optical Subsystem Guide Performance Guide R-Tree Index Users Guide

SNMP Subagent Guide Storage Manager Administrators Guide Trusted Facility Guide User-Defined Routines and Data Types Developers Guide Virtual-Index Interface Programmers Guide Virtual-Table Interface Programmers Guide

Introduction

xxiii

Table 2. Client/Connectivity Manuals Manual Client Products Installation Guide Embedded SQLJ Users Guide Subject Installing IBM Informix Client Software Developers Kit (Client SDK) and IBM Informix Connect on computers that use UNIX, Linux, and Windows. Using IBM Informix Embedded SQLJ to embed SQL statements in Java programs.

ESQL/C Programmers Manual The IBM Informix implementation of embedded SQL for C. GLS Users Guide The Global Language Support (GLS) feature, which allows IBM Informix APIs and database servers to handle different languages, cultural conventions, and code sets. Installing and using Informix JDBC Driver to connect to an Informix database from within a Java application or applet. Using Informix .NET Provider to enable .NET client applications to access and manipulate data in Informix databases. Using the Informix ODBC Driver API to access an Informix database and interact with the Informix database server. Installing and configuring Informix OLE DB Provider to enable client applications, such as ActiveX Data Object (ADO) applications and Web pages, to access data on an Informix server. The architecture of the C++ object interface and a complete class reference.

JDBC Driver Programmers Guide .NET Provider Reference Guide ODBC Driver Programmers Manual OLE DB Provider Programmers Guide Object Interface for C++ Programmers Guide

Table 3. DataBlade Developers Kit Manuals Manual DataBlade Developers Kit Users Guide DataBlade Module Development Overview DataBlade Module Installation and Registration Guide Subject Developing and packaging DataBlade modules using BladeSmith and BladePack. Basic orientation for developing DataBlade modules. Includes an example illustrating the development of a DataBlade module. Installing DataBlade modules and using BladeManager to manage DataBlade modules in Informix databases.

Compliance with Industry Standards

The American National Standards Institute (ANSI) and the International Organization of Standardization (ISO) have jointly established a set of industry standards for the Structured Query Language (SQL). IBM Informix SQL-based products are fully compliant with SQL-92 Entry Level (published as ANSI X3.135-1992), which is identical to ISO 9075:1992. In addition, many features of IBM Informix database servers comply with the SQL-92 Intermediate and Full Level and X/Open SQL Common Applications Environment (CAE) standards.

IBM Welcomes Your Comments

We want to know about any corrections or clarifications that you would find useful in our manuals, which will help us improve future versions. Include the following information: v The name and version of the manual that you are using v Section and page number v Your suggestions about the manual Send your comments to us at the following email address:

xxiv

IBM Informix Guide to SQL: Syntax

[email protected] This email address is reserved for reporting errors and omissions in our documentation. For immediate help with a technical problem, contact IBM Technical Support. For instructions, see the IBM Informix Technical Support website at http://www306.ibm.com/software/data/informix/support/contact.html. We appreciate your suggestions.

Introduction

xxv

xxvi

IBM Informix Guide to SQL: Syntax

Chapter 1. Overview of SQL Syntax

In This Chapter . . . . . . . . . . . . . . . . How to Enter SQL Statements . . . . . . . . . . . Using Syntax Diagrams and Syntax Tables . . . . . . Using Examples . . . . . . . . . . . . . . Using Related Information . . . . . . . . . . . How to Enter SQL Comments . . . . . . . . . . . Examples of SQL Comment Symbols. . . . . . . . Non-ASCII Characters in SQL Comments . . . . . . Categories of SQL Statements . . . . . . . . . . . Data Definition Language Statements . . . . . . . Data Manipulation Language Statements . . . . . . Data Integrity Statements . . . . . . . . . . . Cursor Manipulation Statements . . . . . . . . . Dynamic Management Statements . . . . . . . . Data Access Statements . . . . . . . . . . . . Optimization Statements . . . . . . . . . . . . Routine Definition Statements . . . . . . . . . . Auxiliary Statements . . . . . . . . . . . . . Client/Server Connection Statements . . . . . . . Optical Subsystem Statements (IDS) . . . . . . . . ANSI/ISO Compliance and Extensions . . . . . . . . ANSI/ISO-Compliant Statements . . . . . . . . . ANSI/ISO-Compliant Statements with Informix Extensions Statements that are Extensions to the ANSI/ISO Standard . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-1 1-1 1-2 1-3 1-3 1-3 1-5 1-5 1-6 1-6 1-7 1-7 1-7 1-7 1-7 1-8 1-8 1-8 1-8 1-8 1-8 1-8 1-8 1-9

In This Chapter
This chapter provides information about how to use the SQL statements, SPL statements, and syntax segments that subsequent chapters of this book discuss. The chapter is organized into the following sections.
Section How to Enter SQL Statements How to Enter SQL Comments on page 1-3 Categories of SQL Statements on page 1-6 ANSI/ISO Compliance and Extensions on page 1-8 Scope How to use syntax diagrams and descriptions to enter SQL statements correctly How to enter comments in SQL statements The SQL statements, listed by functional category The SQL statements, listed by degree of ANSI/ISO compliance

How to Enter SQL Statements

SQL is a free-form language, like C or PASCAL, that generally ignores whitespace characters like TAB, LINEFEED, and extra blank spaces between statements or statement elements. At least one blank character or other delimiter, however, must separate keywords and identifiers from other syntax tokens. SQL is lettercase-insensitive, except within quoted strings; see also Identifier on page 5-22. In an ANSI-compliant database, if you do not delimit the owner of an object by single ( ) quotation marks, and the ANSIOWNER environment variable
Copyright IBM Corp. 1996, 2005

1-1

was not set to 1 when the database server was initialized, the database server stores the owner name in uppercase letters. Statement descriptions are provided in this manual to help you to enter SQL statements successfully. A statement description includes this information: v A brief introduction that explains what the statement does v A syntax diagram that shows how to enter the statement correctly v A syntax table that explains each input parameter in the syntax diagram v Rules of usage, typically with examples that illustrate these rules For some statements, this information is provided for individual clauses. Most statement descriptions conclude with references to related information in this manual and in other manuals. Chapter 2 provides descriptions of each SQL statement, arranged in alphabetical order. Chapter 3 describes each of the SPL statements, using the same format. The major aids for entering SQL statements include: v The combination of the syntax diagram and syntax table v The examples of syntax that appear in the rules of usage v The references to related information

Using Syntax Diagrams and Syntax Tables

Before you try to use the syntax diagrams in this chapter, it is helpful to read the section Syntax Diagrams on page xv of the Introduction. This section is the key to understanding the syntax diagrams and explains the elements that can appear in a syntax diagram and the paths that connect the elements to each other. This section also includes an example that illustrates the elements of typical syntax diagrams. The narrative that follows the example diagram shows how to read the diagram in order to enter the statement successfully. Notes to the diagram can reference other syntax segments or can specify various restrictions. If you are using an application programming interface, such as ESQL/C or 4GL, only the SQL syntax rules that both your client application and the database server support are valid. When a syntax diagram includes input specifications that are not keywords or punctuation symbols, such as identifiers, expressions, filenames, host variables, the syntax diagram is followed by a table that describes how to enter the term without generating errors. Each syntax table includes four columns: v The Element column lists each variable term in the syntax diagram. v The Description column briefly describes the term and identifies the default value, if the term has one. v The Restrictions column summarizes the restrictions on the term, such as acceptable ranges of values. (For some diagrams, restrictions that cannot be tersely summarized appear in the Usage notes, rather than in this column.) v The Syntax column points to the SQL segment that gives the detailed syntax for the term. For a few terms, such as the names of host variables, pathnames, or literal characters, no page reference is provided.

1-2

IBM Informix Guide to SQL: Syntax

The diagrams generally provide an intuitive notation for what is valid in a given SQL statement, but for some statements, dependencies or restrictions among syntax elements are identified only in the text of the Usage section.

Using Examples
To understand the main syntax diagram and subdiagrams for a statement, study the examples of syntax that appear in the rules of usage for each statement. These examples have two purposes: v To show how to accomplish specific tasks with the statement or its clauses v To show how to use syntax of the statement or its clauses in a concrete way Tip: An efficient way to understand a syntax diagram is to find an example of the syntax and compare it with the keywords and parameters in the syntax diagram. By mapping the concrete elements of the example to the abstract elements of the syntax diagram, you can understand the syntax diagram and use it more effectively. For an explanation of the conventions used in the examples in this manual, see Example Code Conventions on page xviii of the Introduction. These code examples are program fragments to illustrate valid syntax, rather than complete SQL programs. In some code examples, ellipsis ( . . . ) symbols indicate that additional code has been omitted. To save space, however, ellipses are not shown at the beginning or end of the program fragments.

Using Related Information

For help in understanding concepts and terms in the SQL statement description, check the Related Information section at the end of each statement. This section points to related information in this manual and other manuals to help you understand the statement in question. The section provides some or all of the following information: v The names of related statements that might contain a fuller discussion of topics in this statement v The titles of other manuals that provide extended discussions of topics in this statement Tip: If you do not have extensive knowledge and experience with SQL, the IBM Informix Guide to SQL: Tutorial gives you the basic SQL knowledge that you need to understand and use the statement descriptions in this manual.

How to Enter SQL Comments

You can add comments to clarify the purpose or effect of particular SQL statements. You can also use comment symbols during program development to disable individual statements without deleting them from your source code. Your comments can help you or others to understand the role of the statement within a program, SPL routine, or command file. The code examples in this manual sometimes include comments that clarify the role of an SQL statement within the code, but your own SQL programs will be easier to read and to maintain if you document them with frequent comments.

Chapter 1. Overview of SQL Syntax

1-3

The following table shows the SQL comment indicators that you can enter in your code. Here a Y in a column signifies that you can use the symbol with the product or with the type of database identified in the column heading. An N in a column signifies that you cannot use the symbol with the indicated product or with a database of the indicated ANSI-compliance status.
ANSICompliant Databases Y Databases Not ANSI Compliant Y

Comment Symbol double hyphen (--)

ESQL/C Y

SPL Routine Y

DBAccess Y

Description The double hyphen precedes a comment within a single line. To comment more than one line, put double hyphen symbols at the beginning of each comment line. Braces enclose the comment. The { precedes the comment, and the } follows it. Braces can delimit single- or multiple-line comments, but comments cannot be nested. C-language style slash and asterisk ( /* */ ) paired delimiters enclose the comment. The /* precedes the comment, and the */ follows it. These can delimit single-line or multiple-line comments, but comments cannot be nested.

braces ({...})

slash and asterisk /* . . . */

Characters within the comment are ignored by the database server. The section Optimizer Directives on page 5-34 describes a context where information within comments can influence query plans of Dynamic Server. If the product that you use supports all of these comment symbols, your choice of a comment symbol depends on requirements for ANSI/ISO compliance: v Double hyphen ( -- ) complies with the ANSI/ISO standard for SQL. v Braces ( { } ) are an Informix extension to the ANSI/ISO standard. v C-style slash-and-asterisk ( /* . . . */ ) comply with the SQL-99 standard. If ANSI/ISO compliance is not an issue, your choice of comment symbols is a matter of personal preference. In DBAccess, you can use any of these comment symbols when you enter SQL statements with the SQL editor and when you create SQL command files with the SQL editor or a system editor. C-style ( /* . . . */ ) comments, however, are not supported by DBAccess within CONNECT, DISCONNECT, INFO, LOAD, OUTPUT, SET CONNECT, nor UNLOAD statements.

1-4

IBM Informix Guide to SQL: Syntax

An SQL command file is an operating-system file that contains one or more SQL statements. Command files are also known as command scripts. For more information about command files, see the discussion of command scripts in the IBM Informix Guide to SQL: Tutorial. For information on how to create and modify command files with the SQL editor or a system editor in DBAccess, see the IBM Informix DBAccess User's Guide. You can use either comment symbol in any line of an SPL routine. See the discussion of how to comment and document an SPL routine in the IBM Informix Guide to SQL: Tutorial. In ESQL/C, the double hyphen ( -- ) can begin a comment that extends to the end of the same line. For information on language-specific comment symbols in ESQL/C programs, see the IBM Informix ESQL/C Programmer's Manual.

Examples of SQL Comment Symbols

These examples illustrate different ways to use the SQL comment indicators.

Examples of the Double-Hyphen Symbol

The next example uses the double hyphen ( -- ) to include a comment after an SQL statement. The comment appears on the same line as the statement.
SELECT * FROM customer -- Selects all columns and rows

The following example uses the same SQL statement and the same comment as in the preceding example, but places the comment on a separate line:
SELECT * FROM customer -- Selects all columns and rows

In the following example, the user enters the same SQL statement as in the preceding example but now enters a multiple-line comment:
SELECT * FROM customer -- Selects all columns and rows -- from the customer table

Examples of the Braces Symbols

This example uses braces ( { } ) to delimit a comment after an SQL statement. In this example, the comment appears on the same line as the statement.
SELECT * FROM customer {Selects all columns and rows}

The next example uses the same SQL statement and the same comment as in the preceding example, but the comment appears on a line by itself:
SELECT * FROM customer {Selects all columns and rows}

In the following example, the same SQL statement as in the preceding example is followed by a multiple-line comment:
SELECT * FROM customer {Selects all columns and rows from the customer table}

Non-ASCII Characters in SQL Comments

You can enter non-ASCII characters (including multibyte characters) in SQL comments if the database locale supports the non-ASCII characters. For further information on the GLS aspects of SQL comments, see the IBM Informix GLS User's Guide.
Chapter 1. Overview of SQL Syntax

1-5

Categories of SQL Statements

SQL statements are traditionally divided into the following logical categories: v Data definition statements. These data definition language (DDL) statements can declare, rename, modify, or destroy objects in the local database. v Data manipulation statements. These data manipulation language (DML) statements can retrieve, insert, delete, or modify data values. v Cursor manipulation statements. These statements can declare, open, and close cursors, which are data structures for operations on multiple rows of data. v Dynamic management statements. These statements support memory management and allow users to specify at runtime the details of DML operations. v Data access statements. These statements specify access privileges and support concurrent access to the database by multiple users. v Data integrity statements. These implement transaction logging and support the referential integrity of the database. v Optimization statements. These can be used to improve the performance of operations on the database. v Routine definition statements. These can declare, define, modify, execute, or destroy user-defined routines that the database stores. v Client/server connection statements. These can open or close a connection between a database and a client application. v Auxiliary statements. These can provide information about the database. (This is also a residual category for statements that are not closely related to the other statement categories.) v Optical subsystem statements. These statements are separately documented in IBM Informix Optical Subsystem Guide. The SQL statements of each category are listed in the pages that follow. As Chapter 2 indicates, some statements (and some statement options, as noted in the syntax diagrams) are specific to Dynamic Server (sometimes abbreviated as IDS) or are specific to Extended Parallel Server (sometimes abbreviated as XPS).

Data Definition Language Statements

3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 ALTER ACCESS_METHOD ALTER FRAGMENT ALTER FUNCTION ALTER INDEX ALTER PROCEDURE ALTER ROUTINE ALTER SEQUENCE ALTER TABLE CLOSE DATABASE CREATE ACCESS_METHOD CREATE AGGREGATE CREATE CAST CREATE DATABASE CREATE DISTINCT TYPE CREATE DUPLICATE CREATE EXTERNAL TABLE CREATE FUNCTION 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 CREATE CREATE CREATE CREATE CREATE CREATE CREATE CREATE CREATE CREATE CREATE CREATE CREATE CREATE CREATE CREATE CREATE FUNCTION FROM INDEX OPAQUE TYPE OPCLASS PROCEDURE PROCEDURE FROM ROLE ROUTINE FROM ROW TYPE SCHEMA SEQUENCE SYNONYM TABLE Temporary TABLE TRIGGER VIEW XADATASOURCE

1-6

IBM Informix Guide to SQL: Syntax

3 3 3 3 3 3 3 3 3 3 3 3 3 3

CREATE XADATASOURCE TYPE DROP ACCESS_METHOD DROP AGGREGATE DROP CAST DROP DATABASE DROP DUPLICATE DROP FUNCTION DROP INDEX DROP OPCLASS DROP PROCEDURE DROP ROLE DROP ROUTINE DROP ROW TYPE DROP SEQUENCE

3 3 3 3 3 3 3 3 3 3 3 3 3 3

DROP SYNONYM DROP TABLE DROP TRIGGER DROP TYPE DROP VIEW DROP XADATASOURCE DROP XADATASOURCE TYPE MOVE RENAME COLUMN RENAME DATABASE RENAME INDEX RENAME SEQUENCE RENAME TABLE TRUNCATE

Data Manipulation Language Statements

DELETE INSERT LOAD MERGE SELECT UNLOAD UPDATE Note: The DELETE, INSERT, SELECT, and UPDATE statements are DML statements in the ANSI/ISO standard for SQL. Although LOAD, UNLOAD, and MERGE resemble DML in their functionality, these are out-of-scope for most references in this manual to DML statements.

Data Integrity Statements

BEGIN WORK COMMIT WORK ROLLBACK WORK SET Database Object Mode SET LOG SET PLOAD FILE SET Transaction Mode START VIOLATIONS TABLE STOP VIOLATIONS TABLE

Cursor Manipulation Statements

CLOSE DECLARE FETCH FLUSH FREE OPEN PUT SET AUTOFREE

Dynamic Management Statements

ALLOCATE COLLECTION ALLOCATE DESCRIPTOR ALLOCATE ROW DEALLOCATE COLLECTION DEALLOCATE DESCRIPTOR DEALLOCATE ROW DESCRIBE DESCRIBE INPUT EXECUTE EXECUTE IMMEDIATE FREE GET DESCRIPTOR INFO PREPARE SET DEFERRED_PREPARE SET DESCRIPTOR

Data Access Statements

GRANT GRANT FRAGMENT LOCK TABLE REVOKE REVOKE FRAGMENT SET ISOLATION SET LOCK MODE SET ROLE SET SESSION AUTHORIZATION SET TRANSACTION SET Transaction Mode UNLOCK TABLE
Chapter 1. Overview of SQL Syntax

1-7

Optimization Statements
SAVE EXTERNAL DIRECTIVES SET ALL_MUTABLES SET Default Table Space SET Default Table Type SET ENVIRONMENT SET EXPLAIN SET OPTIMIZATION SET PDQPRIORITY SET Residency SET SCHEDULE LEVEL SET STATEMENT CACHE UPDATE STATISTICS

Routine Definition Statements

ALTER FUNCTION ALTER PROCEDURE ALTER ROUTINE CREATE FUNCTION CREATE FUNCTION FROM CREATE PROCEDURE CREATE PROCEDURE FROM CREATE ROUTINE FROM DROP FUNCTION DROP PROCEDURE DROP ROUTINE EXECUTE FUNCTION EXECUTE PROCEDURE SET DEBUG FILE TO

Auxiliary Statements
GET DIAGNOSTICS INFO OUTPUT SET COLLATION SET DATASKIP SET ENCRYPTION PASSWORD WHENEVER

Client/Server Connection Statements

CONNECT DATABASE DISCONNECT SET CONNECTION

Optical Subsystem Statements (IDS)

ALTER OPTICAL CLUSTER CREATE OPTICAL CLUSTER DROP OPTICAL CLUSTER RELEASE RESERVE SET MOUNTING TIMEOUT Important: See the IBM Informix Optical Subsystem Guide for more information.

ANSI/ISO Compliance and Extensions

Lists that follow show statements that match the ANSI SQL-92 standard at the entry level, statements that are ANSI compliant but include Informix extensions, and statements that are Informix extensions to the ANSI/ISO standard.

ANSI/ISO-Compliant Statements
CLOSE COMMIT WORK EXECUTE IMMEDIATE ROLLBACK WORK SET SESSION AUTHORIZATION SET TRANSACTION

ANSI/ISO-Compliant Statements with Informix Extensions

ALLOCATE DESCRIPTOR ALTER TABLE CONNECT CREATE SCHEMA

1-8

IBM Informix Guide to SQL: Syntax

CREATE TABLE CREATE Temporary TABLE CREATE VIEW DEALLOCATE DESCRIPTOR DECLARE DELETE DESCRIBE DESCRIBE INPUT DISCONNECT EXECUTE FETCH GET DESCRIPTOR GET DIAGNOSTICS

GRANT INSERT OPEN PREPARE REVOKE SELECT SET CONNECTION SET CONSTRAINTS SET DESCRIPTOR SET Transaction Mode UPDATE WHENEVER

Statements that are Extensions to the ANSI/ISO Standard

ALLOCATE COLLECTION ALLOCATE ROW ALTER ACCESS_METHOD ALTER FRAGMENT ALTER FUNCTION ALTER INDEX ALTER OPTICAL CLUSTER ALTER PROCEDURE ALTER ROUTINE ALTER SEQUENCE BEGIN WORK CLOSE DATABASE CREATE ACCESS_METHOD CREATE AGGREGATE CREATE CAST CREATE DATABASE CREATE DISTINCT TYPE CREATE DUPLICATE CREATE EXTERNAL TABLE CREATE FUNCTION CREATE FUNCTION FROM CREATE INDEX CREATE OPAQUE TYPE CREATE OPCLASS CREATE OPTICAL CLUSTER CREATE PROCEDURE CREATE PROCEDURE FROM CREATE ROLE CREATE ROUTINE FROM CREATE ROW TYPE CREATE SEQUENCE CREATE SYNONYM CREATE TRIGGER CREATE XADATASOURCE CREATE XADATASOURCE TYPE DATABASE DEALLOCATE COLLECTION DEALLOCATE ROW DROP ACCESS_METHOD DROP AGGREGATE DROP CAST DROP DATABASE DROP DUPLICATE DROP FUNCTION DROP INDEX DROP OPCLASS DROP OPTICAL CLUSTER DROP PROCEDURE DROP ROLE DROP ROUTINE DROP ROW TYPE DROP SEQUENCE DROP SYNONYM DROP TABLE DROP TRIGGER DROP TYPE DROP VIEW DROP XADATASOURCE DROP XADATASOURCE TYPE EXECUTE FUNCTION EXECUTE PROCEDURE FLUSH FREE GET DIAGNOSTICS GRANT FRAGMENT LOAD LOCK TABLE MERGE MOVE OUTPUT PUT

Chapter 1. Overview of SQL Syntax

1-9

RELEASE RENAME COLUMN RENAME DATABASE RENAME INDEX RENAME SEQUENCE RENAME TABLE RESERVE REVOKE FRAGMENT SAVE EXTERNAL DIRECTIVES SET ALL_MUTABLES SET AUTOFREE SET COLLATION SET Database Object Mode SET DATASKIP SET DEBUG FILE TO SET Default Table Space SET Default Table Type SET DEFERRED_PREPARE SET ENCRYPTION PASSWORD SET ENVIRONMENT SET EXPLAIN SET ISOLATION SET LOCK MODE SET LOG SET MOUNTING TIMEOUT SET OPTIMIZATION SET PDQPRIORITY SET PLOAD FILE SET Residency SET ROLE SET SCHEDULE LEVEL SET STATEMENT CACHE START VIOLATIONS TABLE STOP VIOLATIONS TABLE TRUNCATE UNLOAD UNLOCK TABLE UPDATE STATISTICS

1-10

IBM Informix Guide to SQL: Syntax

Chapter 2. SQL Statements

In This Chapter . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-3 ALLOCATE COLLECTION . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-4 ALLOCATE DESCRIPTOR . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-6 ALLOCATE ROW . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-8 ALTER ACCESS_METHOD . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-9 ALTER FRAGMENT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-11 ALTER FUNCTION . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-31 ALTER INDEX . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-33 ALTER PROCEDURE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-35 ALTER ROUTINE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-37 ALTER SEQUENCE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-40 ALTER TABLE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-43 BEGIN WORK . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-66 CLOSE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-68 CLOSE DATABASE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-71 COMMIT WORK . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-73 CONNECT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-75 CREATE ACCESS_METHOD . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-82 CREATE AGGREGATE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-84 CREATE CAST . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-87 CREATE DATABASE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-90 CREATE DISTINCT TYPE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-93 CREATE DUPLICATE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-96 CREATE EXTERNAL TABLE (XPS) . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-98 CREATE FUNCTION . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-107 CREATE FUNCTION FROM . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-114 CREATE INDEX . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-116 CREATE OPAQUE TYPE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-136 CREATE OPCLASS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-141 CREATE PROCEDURE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-145 CREATE PROCEDURE FROM . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-153 CREATE ROLE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-155 CREATE ROUTINE FROM . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-157 CREATE ROW TYPE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-158 CREATE SCHEMA . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-162 CREATE SCRATCH TABLE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-164 CREATE SEQUENCE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-165 CREATE SYNONYM . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-168 CREATE TABLE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-171 CREATE TEMP TABLE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-208 CREATE Temporary TABLE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-209 CREATE TRIGGER . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-216 CREATE VIEW . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-247 3 CREATE XADATASOURCE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-252 3 CREATE XADATASOURCE TYPE . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-253 DATABASE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-255 DEALLOCATE COLLECTION . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-257 DEALLOCATE DESCRIPTOR . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-258 DEALLOCATE ROW . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-259 DECLARE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-260 DELETE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-275 DESCRIBE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-281 DESCRIBE INPUT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-286 DISCONNECT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-291 DROP ACCESS_METHOD . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-294
Copyright IBM Corp. 1996, 2005

2-1

DROP AGGREGATE . . . . DROP CAST . . . . . . . DROP DATABASE . . . . . DROP DUPLICATE . . . . . DROP FUNCTION . . . . . DROP INDEX . . . . . . DROP OPCLASS . . . . . DROP PROCEDURE . . . . DROP ROLE . . . . . . . DROP ROUTINE . . . . . DROP ROW TYPE . . . . . DROP SEQUENCE . . . . . DROP SYNONYM . . . . . DROP TABLE . . . . . . DROP TRIGGER. . . . . . DROP TYPE . . . . . . . DROP VIEW . . . . . . . DROP XADATASOURCE . . . DROP XADATASOURCE TYPE EXECUTE . . . . . . . . EXECUTE FUNCTION . . . EXECUTE IMMEDIATE . . . EXECUTE PROCEDURE . . . FETCH . . . . . . . . . FLUSH . . . . . . . . . FREE . . . . . . . . . GET DESCRIPTOR . . . . . GET DIAGNOSTICS . . . . GRANT . . . . . . . . GRANT FRAGMENT . . . . INFO . . . . . . . . . INSERT . . . . . . . . . LOAD . . . . . . . . . LOCK TABLE . . . . . . MERGE . . . . . . . . MOVE TABLE . . . . . . OPEN . . . . . . . . . OUTPUT . . . . . . . . PREPARE . . . . . . . . PUT . . . . . . . . . . RENAME COLUMN . . . . RENAME DATABASE . . . . RENAME INDEX . . . . . RENAME SEQUENCE. . . . RENAME TABLE . . . . . REVOKE . . . . . . . . REVOKE FRAGMENT . . . . ROLLBACK WORK . . . . SAVE EXTERNAL DIRECTIVES SELECT . . . . . . . . SET ALL_MUTABLES . . . . SET AUTOFREE . . . . . . SET COLLATION . . . . . SET CONNECTION . . . . SET CONSTRAINTS . . . . SET Database Object Mode . . SET DATASKIP . . . . . . SET DEBUG FILE . . . . . SET Default Table Space . . . SET Default Table Type . . . SET DEFERRED_PREPARE . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

2-295 2-296 2-297 2-299 2-300 2-302 2-304 2-305 2-307 2-308 2-310 2-312 2-313 2-314 2-316 2-317 2-318 2-319 2-320 2-321 2-329 2-334 2-336 2-344 2-353 2-355 2-357 2-362 2-371 2-388 2-393 2-395 2-407 2-413 2-416 2-419 2-424 2-431 2-433 2-442 2-449 2-451 2-452 2-453 2-454 2-456 2-471 2-474 2-476 2-479 2-527 2-529 2-531 2-534 2-538 2-539 2-545 2-547 2-549 2-550 2-552

2-2

IBM Informix Guide to SQL: Syntax

SET DESCRIPTOR . . . . . SET ENCRYPTION PASSWORD SET ENVIRONMENT . . . . SET EXPLAIN . . . . . . SET INDEX . . . . . . . SET INDEXES . . . . . . SET ISOLATION . . . . . SET LOCK MODE . . . . . SET LOG . . . . . . . . SET OPTIMIZATION . . . . SET PDQPRIORITY . . . . SET PLOAD FILE . . . . . SET Residency . . . . . . SET ROLE . . . . . . . . SET SCHEDULE LEVEL . . . SET SESSION AUTHORIZATION SET STATEMENT CACHE . . SET TABLE . . . . . . . SET TRANSACTION . . . . SET Transaction Mode . . . . SET TRIGGERS . . . . . . START VIOLATIONS TABLE . STOP VIOLATIONS TABLE . . 4 TRUNCATE (IDS) . . . . . TRUNCATE (XPS) . . . . . UNLOAD . . . . . . . . UNLOCK TABLE . . . . . UPDATE . . . . . . . . UPDATE STATISTICS . . . . WHENEVER . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

2-554 2-560 2-563 2-568 2-573 2-574 2-575 2-580 2-582 2-584 2-586 2-589 2-590 2-592 2-594 2-595 2-597 2-601 2-602 2-606 2-608 2-609 2-622 2-624 2-628 2-630 2-635 2-636 2-649 2-659

In This Chapter
This chapter describes the syntax and semantics of SQL statements that are recognized by Dynamic Server or by Extended Parallel Server. Statements (and statement segments, and notes describing usage) that are not explicitly restricted to one of these database servers are valid for both. The statement descriptions appear in alphabetical order. For some statements, important details of the semantics appear in other volumes of this documentation set, as indicated by cross-references. For many statements, the syntax diagram, or the table of terms immediately following the diagram, or both, includes references to syntax segments in Chapter 4, Data Types and Expressions, on page 4-1 or in Chapter 5, Other Syntax Segments, on page 5-1. When the name of an SQL statement includes lowercase characters, such as CREATE Temporary TABLE, it means that the first mixed-lettercase string in the statement name is not an SQL keyword, but that two or more different SQL keywords can follow the preceding uppercase keyword. For an explanation of the structure of statement descriptions, see Chapter 1, Overview of SQL Syntax, on page 1-1.

Chapter 2. SQL Statements

2-3

ALLOCATE COLLECTION

ALLOCATE COLLECTION
Use the ALLOCATE COLLECTION statement to allocate memory for a variable of a collection data type (such as LIST, MULTISET, or SET) or an untyped collection variable. Only Dynamic Server supports this statement, which is an extension to the ANSI/ISO standard for SQL. Use this statement with ESQL/C.

Syntax
ALLOCATE COLLECTION variable

Element variable

Description Name of the typed or untyped collection variable to allocate

Restrictions Must be an unallocated ESQL/C collection-type host variable

Syntax Language-specific rules for names

Usage
The ALLOCATE COLLECTION statement allocates memory for an ESQL/C variable that can store the value of a collection data type. To create a collection variable for an ESQL/C program: 1. Declare the collection variable as a client collection variable in an ESQL/C program. The collection variable can be a typed or untyped collection variable. 2. Allocate memory for the collection variable with the ALLOCATE COLLECTION statement. The ALLOCATE COLLECTION statement sets SQLCODE (that is, sqlca.sqlcode) to zero (0) if the memory allocation was successful and to a negative error code if the allocation failed. You must explicitly release memory with the DEALLOCATE COLLECTION statement. After you free the collection variable with the DEALLOCATE COLLECTION statement, you can reuse the collection variable. Tip: The ALLOCATE COLLECTION statement allocates memory for an ESQL/C collection variable only. To allocate memory for an ESQL/C row variable, use the ALLOCATE ROW statement.

Examples
The following example shows how to allocate resources with the ALLOCATE COLLECTION statement for the untyped collection variable, a_set:
EXEC SQL BEGIN DECLARE SECTION; client collection a_set; EXEC SQL END DECLARE SECTION; . . . EXEC SQL allocate collection :a_set;

The following example uses ALLOCATE COLLECTION to allocate resources for a typed collection variable, a_typed_set:

2-4

IBM Informix Guide to SQL: Syntax

ALLOCATE COLLECTION
EXEC SQL BEGIN DECLARE SECTION; client collection set(integer not null) a_typed_set; EXEC SQL END DECLARE SECTION; . . . EXEC SQL allocate collection :a_typed_set;

Related Information
Related examples: Refer to the collection-variable example in PUT. Related statements: ALLOCATE ROW and DEALLOCATE COLLECTION For a discussion of collection data types in ESQL/C programs, see the IBM Informix ESQL/C Programmer's Manual.

Chapter 2. SQL Statements

2-5

ALLOCATE DESCRIPTOR
Use the ALLOCATE DESCRIPTOR statement to allocate memory for a system-descriptor area (SDA). Use this statement with ESQL/C.

Syntax
ALLOCATE DESCRIPTOR descriptor descriptor_var WITH MAX items items_var

Element descriptor descriptor_var items items_var

Description Name of an unallocated system-descriptor area

Restrictions

Syntax

Enclose in single ( ) quotes. Must be Quoted String on unique among SDA names page 4-142. Language specific Literal Number on page 4-137 Language specific

Host variable that stores the name of Must contain name of unallocated a system-descriptor area system-descriptor area Number of item descriptors in descriptor. Default value is 100. Host variable that contains the number of items Must be an unsigned INTEGER greater than zero Data type must be INTEGER or SMALLINT

Usage
The ALLOCATE DESCRIPTOR statement creates a system-descriptor area, which is a location in memory that holds information that the DESCRIBE statement can display, or that holds information about the WHERE clause of a query. A system-descriptor area (SDA) contains one or more fields called item descriptors. Each item descriptor holds a data value that the database server can receive or send. The item descriptors also contain information about the data, such as data type, length, scale, precision, and nullability. A system-descriptor area holds information that a DESCRIBE...USING SQL DESCRIPTOR statement obtains or that holds information about the WHERE clause of a dynamically executed statement. If the name that you assign to a system-descriptor area matches the name of an existing system-descriptor area, the database server returns an error. If you free the descriptor with the DEALLOCATE DESCRIPTOR statement, however, you can reuse the same descriptor name.

WITH MAX Clause

You can use the WITH MAX clause to indicate the maximum number of item descriptors you need. When you use this clause, the COUNT field is set to the number of items that you specify. If you do not specify the WITH MAX clause, the default value of the COUNT field is 100. You can change the value of the COUNT field with the SET DESCRIPTOR statement. The following examples show valid ALLOCATE DESCRIPTOR statements. Each example includes the WITH MAX clause. The first line uses embedded variable names to identify the system-descriptor area and to specify the desired number of item descriptors. The second line uses a quoted string to identify the system-descriptor area and an unsigned integer to specify the desired number of item descriptors.

2-6

IBM Informix Guide to SQL: Syntax

ALLOCATE DESCRIPTOR
EXEC SQL allocate descriptor :descname with max :occ; EXEC SQL allocate descriptor desc1 with max 3;

Related Information
Related statements: DEALLOCATE DESCRIPTOR, DECLARE, DESCRIBE, EXECUTE, FETCH, GET DESCRIPTOR, OPEN, PREPARE, PUT, and SET DESCRIPTOR For more information on system-descriptor areas, refer to the IBM Informix ESQL/C Programmer's Manual.

Chapter 2. SQL Statements

2-7

ALLOCATE ROW
Use the ALLOCATE ROW statement to allocate memory for a row variable. Only Dynamic Server supports this statement, which is an extension to the ANSI/ISO standard for SQL. Use this statement with ESQL/C.

Syntax
ALLOCATE ROW variable

Element variable

Description Name of a typed or untyped row variable to allocate

Restrictions Must be an unallocated ESQL/C row-type host variable

Syntax Language specific

Usage
The ALLOCATE ROW statement allocates memory for a host variable that stores row-type data. To create a row variable, an ESQL/C program must do the following: 1. Declare the row variable. The row variable can be a typed or untyped row variable. 2. Allocate memory for the row variable with the ALLOCATE ROW statement. The following example shows how to allocate resources with the ALLOCATE ROW statement for the typed row variable, a_row:
EXEC SQL BEGIN DECLARE SECTION; row (a int, b int) a_row; EXEC SQL END DECLARE SECTION; . . . EXEC SQL allocate row :a_row;

The ALLOCATE ROW statement sets SQLCODE (the contents of sqlca.sqlcode) to zero (0) if the memory allocation operation was successful, or to a negative error code if the allocation failed. You must explicitly release memory with the DEALLOCATE ROW statement. Once you free the row variable with the DEALLOCATE ROW statement, you can reuse the row variable. Tip: The ALLOCATE ROW statement allocates memory for an ESQL/C row variable only. To allocate memory for an ESQL/C collection variable, use the ALLOCATE COLLECTION statement. When you use the same row variable in multiple function calls without deallocating it, a memory leak on the client computer results. Because there is no way to determine if a pointer is valid when it is passed, ESQL/C assumes that the pointer is not valid and assigns it to a new memory location.

Related Information
Related statements: ALLOCATE COLLECTION and DEALLOCATE ROW For a discussion of complex data types in ESQL/C programs, see the IBM Informix ESQL/C Programmer's Manual.

2-8

IBM Informix Guide to SQL: Syntax

ALTER ACCESS_METHOD
The ALTER ACCESS_METHOD statement changes the attributes of a user-defined access method in the sysams system catalog table. Only Dynamic Server supports this statement, which is an extension to the ANSI/ISO standard for SQL.

Syntax
ALTER ACCESS_METHOD access_method

, (1) MODIFY Purpose Option ADD DROP purpose_keyword

Notes: 1
Element access _method purpose _keyword Description Name of the access method to alter A keyword that indicates which feature to change

See Purpose Options on page 5-46

Restrictions Access method must be registered in the sysams system catalog table by a previous CREATE ACCESS_METHOD statement Keyword must be associated with the access method by a previous CREATE or ALTER ACCESS_METHOD statement Syntax Database Object Name on page 5-17 Purpose Functions, Methods, Flags, and Values on page 5-47

Usage
Use ALTER ACCESS_METHOD to modify the definition of a user-defined access method. You must be the owner of the access method or have DBA privileges to alter an access method. When you alter an access method, you change the purpose-option specifications (purpose functions, purpose methods, purpose flags, or purpose values) that define the access method. For example, you might alter an access method to assign a new user-defined function or method name or to provide a multiplier for the scan cost on a table. If a transaction is in progress, the database server waits to alter the access method until after the transaction is committed or rolled back. No other users can execute the access method until the transaction has completed.

Example
The following statement alters the remote user-defined access method:
ALTER ACCESS_METHOD remote ADD am_scancost = FS_scancost, ADD am_rowids, DROP am_getbyid, MODIFY am_costfactor = 0.9;

Chapter 2. SQL Statements

2-9

ALTER ACCESS_METHOD
The preceding example makes the following changes to the access method: v Adds a user-defined function or method named FS_scancost( ), which is associated in the sysams table with the am_scancost keyword v Sets (adds) the am_rowids flag v Drops the user-defined function or method associated with the am_getbyid keyword v Modifies the am_costfactor value

Related Information
Related statements: CREATE ACCESS_METHOD and DROP ACCESS_METHOD For information about how to set purpose-option specifications, see Purpose Options on page 5-46. For the schema of the sysams system catalog table, see the IBM Informix Guide to SQL: Reference. For more information on primary-access methods, see the IBM Informix Virtual-Table Interface Programmer's Guide. For more information on secondary-access methods, see the IBM Informix Virtual-Index Interface Programmer's Guide and the IBM Informix User-Defined Routines and Data Types Developer's Guide. For a discussion of privileges, see the GRANT statement in this chapter, or the IBM Informix Database Design and Implementation Guide.

2-10

IBM Informix Guide to SQL: Syntax

ALTER FRAGMENT
Use the ALTER FRAGMENT statement to change the distribution strategy or the storage location of an existing table or index. This statement is an extension to the ANSI/ISO standard for SQL.

Syntax
ALTER FRAGMENT ON

(1) TABLE surviving_table ATTACH Clause (2) DETACH Clause (3) INIT Clause (4) ADD Clause (5) DROP Clause MODIFY Clause (5) INDEX surviving_index INIT Clause (4) ADD Clause (6) DROP Clause (7) MODIFY Clause (3)

(6) (7)

Notes: 1 2 3 4 5 6 7
Element surviving _index surviving _table Description Index on which to modify the distribution or storage Table on which to modify the distribution or storage

See ATTACH Clause on page 2-13 See DETACH Clause on page 2-19 See INIT Clause on page 2-20 See ADD Clause on page 2-26 Dynamic Server only See DROP Clause (IDS) on page 2-27 See MODIFY Clause (IDS) on page 2-28
Restrictions Must exist when the statement executes Syntax Database Object Name on page 5-17 Database Object Name on page 5-17

Must exist. See Restrictions on the ALTER FRAGMENT Statement on page 2-12.

Chapter 2. SQL Statements

2-11

ALTER FRAGMENT

Usage
The ALTER FRAGMENT statement applies only to table fragments or index fragments that are located at the current site (or the current cluster, for Extended Parallel Server). No remote information is accessed or updated. You must have the Alter or the DBA privilege to change the fragmentation strategy of a table. You must have the Index or the DBA privilege to alter the fragmentation strategy of an index. Attention: This statement can cause indexes to be dropped and rebuilt. Before undertaking alter operations, check corresponding sections in your IBM Informix Performance Guide to review effects and strategies. Clauses of the ALTER FRAGMENT statement support the following tasks. Clause ATTACH DETACH INIT Effect Combines two or more tables that have the same schema into a single fragmented table Detaches a table fragment (or in Extended Parallel Server, a slice) from a fragmentation strategy, and places it in a new table Provides the following options: v Defines and initializes a fragmentation strategy on a table v Creates a fragmentation strategy for tables v Changes the order of evaluation of fragment expressions v Alters the fragmentation strategy of a table or index v Changes the storage location of an existing table Adds an additional fragment to an existing fragmentation list Drops an existing fragment from a fragmentation list Changes an existing fragmentation expression

ADD DROP MODIFY

Use the CREATE TABLE statement or the INIT clause of the ALTER FRAGMENT statement to create fragmented tables. After a dbspace has been renamed successfully by the onspaces utility, only the new name can reference the renamed dbspace. Existing fragmentation strategies for tables or indexes are automatically updated, however, by the database server to replace the old dbspace name with the new name. You do not need to take any additional action to update a distribution strategy or storage location that was defined using the old dbspace name, but you must use the new name if you reference the dbspace in an ALTER FRAGMENT or ALTER TABLE statement.

Restrictions on the ALTER FRAGMENT Statement

You cannot use the ALTER FRAGMENT statement on a temporary table, an external table, or on a view. If your table or index is not already fragmented, the only clauses available to you are INIT and ATTACH. In Extended Parallel Server, you cannot use ALTER FRAGMENT on a generalized-key (GK) index. If the surviving_table has hash fragmentation, the only clauses available are ATTACH and INIT. You cannot use ALTER FRAGMENT on any table that has a dependent GK index defined on it. In addition, you cannot use this statement on a table that has range fragmentation.

2-12

IBM Informix Guide to SQL: Syntax

ALTER FRAGMENT
In Dynamic Server, you cannot use ALTER FRAGMENT on a typed table that is part of a table hierarchy.

ALTER FRAGMENT and Transaction Logging

If your database supports transaction logging, ALTER FRAGMENT is executed within a single transaction. If the fragmentation strategy uses large numbers of records, you might run out of log space or disk space. (To alter a fragmentation strategy, the database server requires extra disk space that it later frees.) If you run out of log space or disk space, try one of the following procedures to reduce your log-space or disk-space requirements: v Turn off logging and turn it back on again at the end of the operation. This procedure indirectly requires a backup of the root dbspace. v Split the operations into multiple ALTER FRAGMENT statements, moving a smaller portion of records each time. For information about log-space requirements and disk-space requirements, see your IBM Informix Administrator's Guide. That guide also contains detailed instructions about how to turn off logging. For information about backups, refer to your IBM Informix Backup and Restore Guide.

Determining the Number of Rows in the Fragment

You can place as many rows into a fragment as the available space in the partition, dbspace, or dbslice allows. To find out how many rows are in a fragment: 1. Run the UPDATE STATISTICS statement on the table. This step fills the sysfragments system catalog table with the current table information. 2. Query the sysfragments system catalog table to examine the npused and nrows values. The npused column shows the number of data pages used in the fragment, and the nrows column shows the number of rows in the fragment.

ATTACH Clause
Use the ATTACH clause of the ALTER FRAGMENT ON TABLE statement to combine tables that have identical structures into a fragmentation strategy. ATTACH Clause:
, (1) ATTACH surviving_table consumed_table AS (2) PARTITION new_frag (1) expr AFTER old_frag BEFORE (2) REMAINDER (3)

Notes: 1 2 3 Use path no more than once Dynamic Server only; required if another surviving_table fragment has same name as dbspace Required for fragmentation by expression; optional for round-robin fragmentation

Chapter 2. SQL Statements

2-13

ALTER FRAGMENT
Element consumed _table Description Table that loses its identity to be merged with surviving_table Restrictions Syntax

Must exist. Cannot include serial columns Database Object nor unique, referential, or primary key Name on page constraints. See also General Restrictions for 5-17 the ATTACH Clause on page 2-14. Can include only columns from the current table and only data values from a single row. See also General Restrictions for the ATTACH Clause on page 2-14. Must be unique among the names of partitions and of dbspaces that store fragments of surviving_table Must exist. See also Altering Hybrid-Fragmented Tables (XPS) on page 2-16. Condition on page 4-5; Expression on page 4-34 Identifier on page 5-22 Identifier on page 5-22

expr

Expression defining which rows are stored in a fragment of a fragmented-by-expression table Name declared here for consumed_table fragment. Default is the dbspace name. Partition or dbspace where a surviving_table fragment exists Table on which to modify the distribution or storage location

new_frag

old_frag

surviving _table

Must exist. Cannot have any constraints. See Database Object also Restrictions on the ALTER FRAGMENT Name on page Statement on page 2-12. 5-17

To use this clause, you must have the DBA privilege or else be the owner of the specified tables. The ATTACH clause supports the following tasks: v Creates a single fragmented table by combining two or more identically-structured, nonfragmented tables (See Combining Nonfragmented Tables to Create a Fragmented Table on page 2-15.) v Attaches one or more tables to a fragmented table (See Attaching a Table to a Fragmented Table on page 2-15.)

General Restrictions for the ATTACH Clause

This clause is not valid in ALTER FRAGMENT ON INDEX statements. Any tables that you attach must have been created previously in separate partitions. You cannot attach the same table more than once. All consumed tables listed in the ATTACH clause must have the same structure as the surviving table. The number, names, data types, and relative position of the columns must be identical. The expression cannot include aggregates, subqueries, or variant functions. Additional Restrictions on the ATTACH Clause in Dynamic Server: User-defined routines and references to fields of a ROW-type column are not valid. You cannot attach a fragmented table to another fragmented table. All of the dbspaces that store the fragments must have the same page size. Additional Restrictions on the ATTACH Clause in XPS: In addition to the general restrictions, every consumed table must be of the same usage type as the surviving table. For information about how to specify the usage type of a table, refer to Logging Options on page 2-172. The ATTACH clause is not valid under either of the following conditions:

2-14

IBM Informix Guide to SQL: Syntax

ALTER FRAGMENT
v If the consumed tables contain data that belongs in some existing fragment of the surviving table v If existing data in the surviving table would belong in a new fragment Thus, you cannot use the ATTACH clause for data movement among fragments. To perform this task, see the INIT Clause on page 2-20.

Using the BEFORE, AFTER, and REMAINDER Options

The BEFORE and AFTER options allow you to place a new fragment either before or after an existing dbspace. You cannot use the BEFORE and AFTER options when the distribution scheme is round-robin. When you attach a new fragment without an explicit BEFORE or AFTER option, the database server places the added fragment at the end of the fragmentation list, unless a remainder fragment exists. If a remainder fragment exists, the new fragment is placed just before the remainder fragment. You cannot attach a new fragment after the remainder fragment. In Extended Parallel Server, when you create or append to a hybrid-fragmented table, the positioning specification (BEFORE or AFTER) applies to an entire dbslice. You can use any dbspace in a dbslice to identify the dbslice for the BEFORE or AFTER position. If you omit the AS PARTITION fragment specification, the name of the fragment is the name of the dbspace where it is stored.

Combining Nonfragmented Tables to Create a Fragmented Table

When you transform tables with identical table structures into fragments in a single table, you allow the database server to manage the fragmentation instead of allowing the application to manage the fragmentation. The distribution scheme can be round-robin or expression based. To make a single, fragmented table from two or more identically-structured, nonfragmented tables, the ATTACH clause must contain the surviving table in the attach list. The attach list is the list of tables in the ATTACH clause. In Dynamic Server, to include a rowid column in the newly-created single, fragmented table, attach all tables first and then add the rowid with the ALTER TABLE statement.

Attaching a Table to a Fragmented Table

To attach a nonfragmented table to an already fragmented table, the nonfragmented table must have been created in a separate dbspace and must have the same table structure as the fragmented table. In the following example, a round-robin distribution scheme fragments the table cur_acct, and the table old_acct is a nonfragmented table that resides in a separate dbspace. The following example shows how to attach (as the consumed table) old_acct to cur_acct (as the surviving table):
ALTER FRAGMENT ON TABLE cur_acct ATTACH old_acct

In Dynamic Server, when you attach one or more tables to a fragmented table, a consumed_table must be nonfragmented. In Extended Parallel Server, when you attach one or more tables to a fragmented table, a consumed_table can be nonfragmented or have hash fragmentation. If you

Chapter 2. SQL Statements

2-15

ALTER FRAGMENT
specify a consumed_table that has hash fragmentation, the hash column specification must match that of the surviving_table and of any other consumed_table involved in the attach operation.

Altering Hybrid-Fragmented Tables (XPS)

When you alter a hybrid-fragmented table with an ATTACH or DETACH clause, you need specify only one dbspace to identify the entire set of dbspaces associated with a given expression in the base fragmentation strategy of the table. The set of dbspaces associated with an expression in the base fragmentation strategy of the table might have been defined as one or more dbslices or dbspaces. For more information, see Fragmenting by HYBRID (XPS) on page 2-195. If you know the dbslice name, but not the names of any of the dbspaces that make it up, you can name the first dbspace in the dbslice by appending .1 to the dbslice name. For example, if the dbslice were named dbsl1, you could specify dbsl1.1.

Effect of the ATTACH Clause

After an ATTACH operation, all consumed tables no longer exist. Any CHECK constraints or NOT NULL constraints on the consumed tables also no longer exist. You must reference the records that were in the consumed tables through the surviving table. What Happens to Indexes?: A detached index on the surviving table retains its same fragmentation strategy. That is, a detached index does not automatically adjust to accommodate the new fragmentation of the surviving table. For more information on what happens to indexes, see the discussion about altering table fragments in your IBM Informix Performance Guide. In a logging database, an ATTACH operation extends any attached index on the surviving table according to the new fragmentation strategy of the surviving table. All rows in the consumed table are subject to these automatically adjusted indexes. For information on whether the database server completely rebuilds the index on the surviving table or reuses an index that was on the consumed table, see your IBM Informix Performance Guide. In a nonlogging database of Dynamic Server, an ATTACH operation does not extend indexes on the surviving table according to the new fragmentation strategy of the surviving table. To extend the fragmentation strategy of an attached index according to the new fragmentation strategy of the surviving table, you must drop the index and re-create it on the surviving table. What Happens to BYTE and TEXT Columns?: When an ATTACH occurs, BYTE and TEXT fragments of the consumed table become part of the surviving table and continue to be associated with the same rows and data fragments as they were before the ATTACH operation. In Dynamic Server, each BYTE and TEXT column in every table that is specified in the ATTACH clause must have the same storage type, either blobspace or tblspace. If the BYTE or TEXT column is stored in a blobspace, the same column in all tables must be in the same blobspace. If the BYTE or TEXT column is stored in a tblspace, the same column must be stored in a tblspace in all tables. In Extended Parallel Server, BYTE and TEXT columns are stored in separate, additional fragments that the database server creates for that purpose in the same dbspace as each regular table fragment where a given row resides.

2-16

IBM Informix Guide to SQL: Syntax

ALTER FRAGMENT
What Happens to Triggers and Views?: When you attach tables, triggers on the surviving table survive the ATTACH, but triggers on the consumed table are automatically dropped. No triggers are activated by the ATTACH clause, but subsequent data-manipulation operations on the new rows can activate triggers. Views on the surviving table survive the ATTACH operation, but views on the consumed table are automatically dropped. What Happens with the Distribution Scheme?: You can attach a nonfragmented table to a table with any type of supported distribution scheme. In general, the resulting table has the same fragmentation strategy as the prior fragmentation strategy of the surviving_table. When you attach two or more nonfragmented tables, however, the distribution scheme can either be based on expression or round-robin. For Dynamic Server, only the following distribution schemes can result from combining the distribution schemes of the tables in the ATTACH clause.
Prior Distribution Scheme of Surviving Table None Round-robin Expression Prior Distribution Scheme of Consumed Table None None None Resulting Distribution Scheme Round-robin or expression Round-robin Expression

For Extended Parallel Server, this table shows the distribution schemes that can result from different distribution schemes of the tables in the ATTACH clause.
Prior Distribution Scheme of Surviving Table None None Round-robin Expression Hash Hash Hybrid Hybrid Prior Distribution Scheme of Consumed Table None Hash None None None Hash None Hash Resulting Distribution Scheme Round-robin or expression Hybrid Round-robin Expression Hybrid Hybrid Hybrid Hybrid

In Extended Parallel Server, when you attach a nonfragmented table to a table that has hash fragmentation, the resulting table has hybrid fragmentation. You can attach a table with a hash distribution scheme to a table that currently has no fragmentation, or hash fragmentation, or hybrid fragmentation. In any of these situations, the resulting table has a hybrid distribution scheme. The following examples illustrate the use of the ATTACH clause to create fragmented tables with different distribution schemes. Round-Robin Distribution Scheme: The following example combines nonfragmented tables pen_types and pen_makers into a single, fragmented table, pen_types. Table pen_types resides in dbspace dbsp1, and table pen_makers resides in dbspace dbsp2. Table structures are identical in each table.
ALTER FRAGMENT ON TABLE pen_types ATTACH pen_types, pen_makers
Chapter 2. SQL Statements

2-17

ALTER FRAGMENT
After you execute the ATTACH clause, the database server fragments the table pen_types using a round-robin scheme into two dbspaces: the dbspace that contained pen_types and the dbspace that contained pen_makers. Table pen_makers is consumed and no longer exists; all rows that were in table pen_makers are now in table pen_types. Expression Distribution Scheme: Consider the following example that combines tables cur_acct and new_acct and uses an expression-based distribution scheme. Table cur_acct was originally created as a fragmented table and has fragments in dbspaces dbsp1 and dbsp2. The first statement of the example shows that table cur_acct was created with an expression-based distribution scheme. The second statement of the example creates table new_acct in dbsp3 without a fragmentation strategy. The third statement combines the tables cur_acct and new_acct. Table structures (columns) are identical in each table.
CREATE TABLE cur_acct (a int) FRAGMENT BY EXPRESSION a < 5 in dbsp1, a >= 5 and a < 10 in dbsp2; CREATE TABLE new_acct (a int) IN dbsp3; ALTER FRAGMENT ON TABLE cur_acct ATTACH new_acct AS a>=10;

When you examine the sysfragments system catalog table after you alter the fragment, you see that table cur_acct is fragmented by expression into three dbspaces. For additional information about the sysfragments system catalog table, see the IBM Informix Guide to SQL: Reference. In addition to simple range rules, you can use the ATTACH clause to fragment by expression with hash or arbitrary rules. For a discussion of all types of expressions that you can use in an expression-based distribution scheme, see page Fragmenting by EXPRESSION on page 2-192. Warning: When you specify a date value as the default value for a parameter, make sure to specify 4 digits instead of 2 digits for the year. If you specify a 2-digit year, the setting of the DBCENTURY environment variable can affect how the database server interprets the date value. For more information, see the IBM Informix Guide to SQL: Reference. Hybrid Fragmentation Distribution Scheme (XPS): Consider a case where monthly sales data is added to the sales_info table defined below. Due to the large amount of data, the table is distributed evenly across multiple coservers with a system-defined hash function. To manage monthly additions of data to the table, it is also fragmented by a date expression. The combined hybrid fragmentation is declared in the following CREATE TABLE statement:
CREATE TABLE sales_info (order_num INT, sale_date DATE, ...) FRAGMENT BY HYBRID (order_num) EXPRESSION sale_date >= 01/01/1996 AND sale_date < 02/01/1996 IN sales_slice_9601, sale_date >= 02/01/1996 AND sale_date < 03/01/1996 IN sales_slice_9602, . . . sale_date >= 12/01/1996 AND sale_date < 01/01/1997 IN sales_slice_9612;

Values for a new month are originally loaded from an external source. The data values are distributed evenly across the same coservers on which the sales_info table is defined, using a system-defined hash function on the same column:
CREATE TABLE jan_97 (order_num INT, sale_date DATE, ...) FRAGMENT BY HASH (order_num) IN sales_slice_9701 INSERT INTO jan_97 SELECT (...) FROM ... ;

2-18

IBM Informix Guide to SQL: Syntax

ALTER FRAGMENT
After data values are loaded, you can attach the new table to sales_info. You can issue the following ALTER FRAGMENT statement to attach the new table:
ALTER FRAGMENT ON TABLE sales_info ATTACH jan_97 AS sale_date >= 01/01/1997 AND sale_date < 02/01/1997

DETACH Clause
Use the DETACH clause of the ALTER FRAGMENT ON TABLE statement to detach a table fragment from a distribution scheme and place the contents into a new nonfragmented table. This clause is not valid in ALTER FRAGMENT ON INDEX statements. In Extended Parallel Server, the new table can also be a table with hash fragmentation. For an explanation of distribution schemes, see FRAGMENT BY Clause on page 2-190. DETACH Clause:
DETACH (1) PARTITION fragment new_table

Notes: 1
Element fragment Description Partition (IDS only), dbspace, or dbslice (XPS only) that contains the table fragment to be detached. With hybrid-fragmentation, dbslice identifies a set of dbspaces. See Altering Hybrid-Fragmented Tables (XPS) on page 2-16.

Dynamic Server only

Restrictions Must exist at the time of execution Syntax Identifier on page 5-22

new_table

Nonfragmented table that results after you execute the ALTER Must not exist FRAGMENT statement. (In XPS, this can also be a hash-fragmented before table.) execution

Database Object Name on page 5-17

The new table that results from executing the DETACH clause does not inherit any indexes nor constraints from the original table. Only data values remain. Similarly, the new table does not inherit any privileges from the original table. Instead, the new table has the default privileges of any new table. For further information on default table-level privileges, see the GRANT statement on Table-Level Privileges on page 2-374. The DETACH clause cannot be applied to a table if that table is the parent of a referential constraint or if a rowid column is defined on the table. In Dynamic Server, if you omit the PARTITION keyword, the name of the fragment must be the name of the dbspace where the fragment is stored. In Extended Parallel Server, you cannot use the DETACH clause if the table has a dependent generalized key (GK) index defined on it.

Chapter 2. SQL Statements

2-19

ALTER FRAGMENT

Detach with BYTE and TEXT Columns

If the DETACH clause specifies the first fragment of a table that includes simple large objects of data type BYTE or TEXT, the database server applies locks on the blobspaces of every fragment of the table. To detach any other fragment of the table locks only the blobspaces of the specified fragment, rather than the blobspaces of all fragments, so fewer locks are required if the fragment is not the first.

Detach That Results in a Nonfragmented Table

The following example uses the table cur_acct fragmented into two dbspaces, dbsp1 and dbsp2:
ALTER FRAGMENT ON TABLE cur_acct DETACH dbsp2 accounts

This example detaches dbsp2 from the distribution scheme for cur_acct and places the rows in a new table, accounts. Table accounts now has the same structure (column names, number of columns, data types, and so on) as table cur_acct, but the table accounts does not contain any indexes or constraints from the table cur_acct. Both tables are now nonfragmented. The following example shows a table that contains three fragments:
ALTER FRAGMENT ON TABLE bus_acct DETACH dbsp3 cli_acct

This statement detaches dbsp3 from the distribution scheme for bus_acct and places the rows in a new table, cli_acct. Table cli_acct now has the same structure (column names, number of columns, data types, and so on) as bus_acct, but the table cli_acct does not contain any indexes or constraints from the table bus_acct. Table cli_acct is a nonfragmented table, and table bus_acct remains a fragmented table.

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

The new table is a hash-fragmented table if the surviving_table had hybrid fragmentation and the detached dbslice has more than one fragment. In a hybrid-fragmented table, the dbslice is detached if you specify any dbspace in that slice. For example, see the sales_info table discussed in the Hybrid Fragmentation Distribution Scheme (XPS) on page 2-18. Once the January 1997 data is available in sales_info, you might archive year-old sales_info data.
ALTER FRAGMENT ON TABLE sales_info DETACH sales_slice_9601.1 jan_96

In this example, data from January 1996 is detached from the sales_info table and placed in a new table called jan_96.

INIT Clause
The INIT clause of the ALTER FRAGMENT statement can define or modify the fragmentation strategy or the storage location of an existing table or (for Dynamic Server) an existing index. The INIT clause has the following syntax. INIT Clause:
(2) INIT (1) WITH ROWIDS (1) (1) FRAGMENT BY Clause for Tables dbspace (3) PARTITION fragment dbslice (4) FRAGMENT BY Clause for Indexes IN

2-20

IBM Informix Guide to SQL: Syntax

ALTER FRAGMENT
Notes: 1 2 3 4 Dynamic Server only See 2-22 Extended Parallel Server only See FRAGMENT BY Clause for Indexes (IDS) on page 2-24
Description Dbslice storing fragmented data Dbspace storing fragmented data Partition within dbspace Restrictions Must exist at time of execution Must exist at time of execution No more than 2048 for same table Syntax Identifier on page 5-22 Identifier on page 5-22 Identifier on page 5-22

Element dbslice dbspace fragment

The INIT clause can accomplish tasks that include the following: v Move a nonfragmented table from one partition (IDS only) or dbslice (XPS only) or dbspace to another partition or to another dbslice or dbspace. v Convert a fragmented table to a nonfragmented table. v Fragment an existing non-fragmented table without redefining it. v Convert a fragmentation strategy to another fragmentation strategy. v Fragment an existing index that is not fragmented without redefining the index (IDS only). v Convert a fragmented index to a nonfragmented index (IDS only). v Add a new rowid column to the table definition (IDS only). With Extended Parallel Server, you cannot use the INIT clause to change the fragmentation strategy of a table that has a GK index. When you use the INIT clause to modify a table, the tabid value in the system catalog tables changes for the affected table. The constrid value of all unique and referential constraints on the table also changes. For more information about the storage spaces in which you can store a table, see Using the IN Clause on page 2-189. Attention: When you execute the ALTER FRAGMENT statement with this clause, it results in data movement if the table contains any data. If data values move, the potential exists for significant logging, for the transaction being aborted as a long transaction, and for a relatively long exclusive lock being held on the affected tables. Use this statement when it does not interfere with day-to-day operations.

WITH ROWIDS Option (IDS)

Nonfragmented tables contain a hidden column called rowid. Its integer value defines the physical location of a row. 3 3 3 3 3 To include a rowid column in a fragmented table, you must explicitly request it by using the WITH ROWIDS clause in CREATE TABLE (or ADD ROWIDS in ALTER TABLE, or WITH ROWIDS in ALTER FRAGMENT INIT). The rowid in a row of a fragmented table does not identify a physical location for the row in the same way that a rowid in a non-fragmented table does.
Chapter 2. SQL Statements

2-21

ALTER FRAGMENT
When you use the WITH ROWIDS option to add a new rowid column to a fragmented table, the database server assigns a unique rowid number to each row and creates an index that it can use to find the physical location of the row. Performance using this access method is comparable to using a SERIAL or SERIAL8 column. The rowid value of a row cannot be updated, but remains stable during the existence of the row. Each row requires an additional four bytes to store the rowid column after you specify the WITH ROWIDS option. Attention: It is recommended that users creating new applications move toward using primary keys as a method of row identification instead of using rowid values.

Converting a Fragmented Table to a Nonfragmented Table

You might decide that you no longer want a table to be fragmented. You can use the INIT clause to convert a fragmented table to a nonfragmented table. The following example shows the original fragmentation definition, as well as how to use the INIT clause of the ALTER FRAGMENT statement to convert the table:
CREATE TABLE checks (col1 INT, col2 INT) FRAGMENT BY ROUND ROBIN IN dbsp1, dbsp2, dbsp3; ALTER FRAGMENT ON TABLE checks INIT IN dbsp1;

You must use the IN dbspace clause to place the table in a dbspace explicitly. When you use the INIT clause to change a fragmented table to a nonfragmented table, all attached indexes become nonfragmented indexes. In addition, constraints that do not use existing user-defined indexes (detached indexes) become nonfragmented indexes. All newly nonfragmented indexes exist in the same dbspace as the new nonfragmented table. Using the INIT clause to change a fragmented table to a nonfragmented table has no effect on the fragmentation strategy of detached indexes, nor of constraints that use detached indexes. Use the FRAGMENT BY portion of the INIT clause to fragment an existing non-fragmented table, or to convert one fragmentation strategy to another. FRAGMENT BY Clause for Tables:
, FRAGMENT BY (1) PARTITION BY , (1) IN dbspace EXPRESSION ( (2) HASH ( , (2) HYBRID ( column ) Expression List column ) IN (dbspace, dbslice dbspace ) PARTITION part Fragment List ) , ROUND ROBIN IN dbspace (2) dbslice

2-22

IBM Informix Guide to SQL: Syntax

ALTER FRAGMENT
Fragment List:
, ( expr ) (3) REMAINDER IN dbspace

(1) PARTITION part

Expression List:
, EXPRESSION expr (3) REMAINDER ( dbspace dbspace ) IN dbslice ,

Notes: 1 2 3 Dynamic Server only Extended Parallel Server only If included, must be the last item in fragment list
Description Column to which the strategy applies Dbslice that contains the table fragment Dbspace that contains the table fragment Expression that defines a table fragment Name declared here for a partition of dbspace that contains the table fragment Restrictions Must exist in the table Must be defined Must specify at least 2 but no more than 2,048 dbspaces Must evaluate to a Boolean value (t or f) Must be unique among names of fragments of table in dbspace Syntax Identifier on page 5-22 Identifier on page 5-22 Identifier on page 5-22 Expression on page 4-34 Identifier on page 5-22

Element column dbslice dbspace expr part

In the HYBRID option of Extended Parallel Server, column identifies a column on which to apply the hash portion of the hybrid table fragmentation strategy. The expression can reference columns only from the current table and data values only from a single row. Subqueries, aggregates, and the built-in CURRENT, DATE, and TODAY functions are not valid in the expression. For more information on the available fragmentation strategies for tables, see the FRAGMENT BY Clause on page 2-190.

Changing an Existing Fragmentation Strategy on a Table

You can redefine a fragmentation strategy on a table if you decide that your initial strategy does not fulfill your needs. When you alter a fragmentation strategy, the database server discards the existing fragmentation strategy and moves records to fragments as defined in the new fragmentation strategy.

Chapter 2. SQL Statements

2-23

ALTER FRAGMENT
The following example shows the statement that originally defined the fragmentation strategy on the table account and then shows an ALTER FRAGMENT statement that redefines the fragmentation strategy:
CREATE TABLE account (col1 INT, col2 INT) FRAGMENT BY ROUND ROBIN IN dbsp1, dbsp2; ALTER FRAGMENT ON TABLE account INIT FRAGMENT BY EXPRESSION col1 < 0 IN dbsp1, col2 >= 0 IN dbsp2;

If an existing dbspace is full when you redefine a fragmentation strategy, you must not use it in the new fragmentation strategy.

Defining a Fragmentation Strategy on a Nonfragmented Table

The INIT clause can define a fragmentation strategy on a nonfragmented table, regardless of whether the table was created with a storage option.
CREATE TABLE balances (col1 INT, col2 INT) IN dbsp1; ALTER FRAGMENT ON TABLE balances INIT FRAGMENT BY EXPRESSION col1 <= 500 IN dbsp1, col1 > 500 AND col1 <=1000 IN dbsp2, REMAINDER IN dbsp3;

In Dynamic Server, when you use the INIT clause to fragment an existing nonfragmented table, all indexes on the table become fragmented in the same way as the table. In Extended Parallel Server, when the INIT clause fragments an existing nonfragmented table, any indexes retain their existing fragmentation strategy.

FRAGMENT BY Clause for Indexes (IDS)

The INIT FRAGMENT BY clause for indexes of the ALTER FRAGMENT statement can fragment an existing nonfragmented index by an expression-based distribution scheme without redefining the index. FRAGMENT BY Clause for Indexes:
FRAGMENT PARTITION BY EXPRESSION

, ( expr ) IN dbspace PARTITION part , PARTITION part REMAINDER ( expr ) IN dbspace

2-24

IBM Informix Guide to SQL: Syntax

ALTER FRAGMENT
Element dbspace expr Description Dbspace that contains the fragmented information Expression defining an index fragment Restrictions Must specify at least two, but no more than 2,048 dbspaces Must return a Boolean value Syntax Identifier on page 5-22 Condition on page 4-5; Expression on page 4-34 Identifier on page 5-22

part

Name that you declare here for a partition of the specified dbspace

Required for any partition in the same dbspace as another partition of the same index

The keywords FRAGMENT BY and PARTITION BY are synonyms in this context. You can convert an existing fragmentation strategy to another expression-based fragmentation strategy. Dynamic Server discards the existing fragmentation strategy and moves the data records to fragments that you define in the new fragmentation strategy. (To convert an existing fragmented index to a nonfragmented index, you can use the INIT clause to specify IN dbspace or else PARTITION partition IN dbspace as the only storage specification for a previously fragmented index.) The expression can contain only columns from the current table and data values from only a single row. No subqueries nor aggregates are allowed. The built-in CURRENT, DATE, and TODAY functions are not valid here.

Fragmenting Unique and System Indexes

You can fragment unique indexes on a table that uses a round-robin or expression-based distribution scheme, but any columns referenced in the fragment expression must be indexed columns. If your index fragmentation strategy violates this restriction, the ALTER FRAGMENT INIT statement fails, and work is rolled back. You might have an attached unique index on a table fragmented by Column A. If you attempt to use ALTER FRAGMENT INIT to change the table fragmentation to Column B, the statement fails because the unique index is defined on Column A. To resolve this issue, use the INIT clause on the index to detach it from the table fragmentation strategy and fragment it separately. System indexes (such as those used in referential constraints and unique constraints) use user-defined indexes if the indexes exist. If no user-defined indexes can be used, system indexes remain nonfragmented and are moved to the dbspace where the database was created. To fragment a system index, create the fragmented index on the constraint columns and then use the ALTER TABLE statement to add the constraint.

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

You can detach an index from a table-fragmentation strategy with the INIT clause of the ALTER TABLE FOR INDEX statement, so that an attached index becomes a detached index. This breaks any dependency of the index on the table fragmentation strategy. If the INIT clause specifies only IN dbspace or PARTITION fragment IN dbspace for a previously fragmented index, or specifies an index fragmentation strategy that differs from the storage option of the table, then the index becomes a detached index.

Chapter 2. SQL Statements

2-25

ALTER FRAGMENT

ADD Clause
Use the ADD clause to add another fragment to an existing fragment list for a table or (for Dynamic Server only) for an index. ADD Clause:
ADD REMAINDER IN (1) PARTITION part expression IN dbspace (1) PARTITION part BEFORE AFTER fragment (1) PARTITION part IN dbspace

Notes: 1 Dynamic Server only

Description Restrictions Syntax

Element fragment expression

Name of an existing fragment in the Must exist at the time when you Identifier on page fragmentation list execute the statement 5-22 Expression that defines the new fragment that is to be added Must return a Boolean value (t or f) Condition on page 4-5; Expression on page 4-34

dbspace part

Name of dbspace to be added to the Must exist at the time when you Identifier on page fragmentation scheme execute the statement 5-22 Name that you declare here for the fragment. The default name is the name of the dbspace Required for any partition in the same dbspace as another partition of the same index Identifier on page 5-22

The expression can contain column names only from the current table and data values only from a single row. No subqueries or aggregates are allowed. In addition, the built-in current, date, and time functions are not valid here.

Adding a New Dbspace to a Round-Robin Distribution Scheme

You can add more dbspaces to a round-robin distribution scheme. The following example shows the original round-robin definition:
CREATE TABLE book (col1 INT, col2 INT) FRAGMENT BY ROUND ROBIN IN dbsp1, dbsp4;

To add another dbspace, use the ADD clause, as in this example:

ALTER FRAGMENT ON TABLE book ADD dbsp3;

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

In Dynamic Server, you can add a partition of a dbspace to an existing round-robin distribution scheme. The name must be unique within the distribution among partitions of the same dbspace. The following example specifies the same original round-robin fragmentation definition as in the previous section:
CREATE TABLE book (col1 INT, col2 INT) FRAGMENT BY ROUND ROBIN IN dbsp1, dbsp4;

To add a new fragment that is stored in a partition within a dbspace, you can use the ADD clause, as in the following example:

2-26

IBM Informix Guide to SQL: Syntax

ALTER FRAGMENT
ALTER FRAGMENT ON TABLE book ADD PARTITION chapter3 IN dbsp1;

The new distribution uses dbsp1, dbsp4, and chapter3 as storage locations for a 3-part round-robin fragmentation scheme. The records in the fragment chapter3 are stored in a separate partition of dbsp1 from the records in the first fragment, which are stored in a partition whose default name is dbsp1. (The database server issues an error if you attempt to declare the same name for different fragments of the same fragmented table or index.)

Adding Fragment Expressions

Adding a fragment expression to the fragmentation list in an expression-based distribution scheme can relocate records from existing fragments into the new fragment. When you add a new fragment into the middle of the fragmentation list, the database server reevaluates all the data existing in fragments after the new fragment. The next example shows the original expression definition:
FRAGMENT BY EXPRESSION c1 < 100 IN dbsp1, c1 >= 100 AND c1 < 200 IN dbsp2, REMAINDER IN dbsp3;

To add another fragment in a new partition of dbspace dbsp2 to hold rows for c1 values between 200 and 299, use the following ALTER FRAGMENT statement:
ALTER FRAGMENT ON TABLE news ADD PARTITION century3 (c1 >= 200 AND c1 < 300) IN dbsp2;

Any rows that were formerly in the remainder fragment but that fit the criteria (c1 >= 200 AND c1 < 300) move to the new century3 partition in dbspace dbsp2.

Using the BEFORE and AFTER Options

The BEFORE and AFTER options can position the new fragment either before or after an existing fragment within the fragmentation list. The name of a fragment is the name of the partition where it is stored (or the name of the dbspace, if the dbspace has only one partition). You cannot use the BEFORE and AFTER options if the distribution scheme is round-robin. When you attach a new fragment without an explicit BEFORE or AFTER option, the database server places the added fragment at the end of the fragmentation list, unless a remainder fragment exists. If a remainder fragment exists, the new fragment is placed immediately before the remainder fragment. You cannot attach a new fragment after the remainder fragment.

Using the REMAINDER Option

You cannot add a remainder fragment if one already exists. If you add a new fragment when a remainder exists, the database server retrieves and reevaluates all records in the remainder fragment; some records might move to the new fragment. The remainder fragment is always the last item in the fragment list.

DROP Clause (IDS)

Use the DROP clause to remove an existing fragment from the fragmentation list of a fragmented table or index. DROP Clause:
DROP PARTITION fragment

Chapter 2. SQL Statements

2-27

ALTER FRAGMENT
Element fragment Description Name of a partition of the dbspace that stores the dropped fragment Restrictions Must exist when you execute the statement Syntax Identifier on page 5-22

If the table is fragmented by expression, you cannot drop a fragment containing data that cannot be moved to another fragment. (If the distribution scheme has a REMAINDER option, or if the expressions overlap, you can drop a fragment that contains data.) You cannot drop a fragment if the table has only two fragments. When you want to make a fragmented table nonfragmented, use either the INIT clause or the DETACH clause of the ALTER FRAGMENT statement. If the fragment was not given a name when it was created or added then the name of the dbspace is also the name of the fragment. When you drop a fragment, the underlying partition or dbspace is not affected. Only the fragment data values within that partition or dbspace are affected. When you drop a fragment, the database server attempts to move all records in the dropped fragment to another fragment. In this case, the destination fragment might not have enough space for the additional records. If this happens, follow one of the procedures that ALTER FRAGMENT and Transaction Logging on page 2-13 describes to increase your available space, and retry the procedure. The following examples show how to drop a fragment from a fragmentation list. The first line shows how to drop an index fragment, and the second line shows how to drop a table fragment.
ALTER FRAGMENT ON INDEX cust_indx DROP dbsp2; ALTER FRAGMENT ON TABLE customer DROP dbsp1;

MODIFY Clause (IDS)

Use the MODIFY clause to change an existing fragment expression on an existing partition in the fragmentation list of a table or of an index. You can also use the MODIFY clause to relocate a fragment corresponding to an expression from one dbspace to a different dbspace. MODIFY Clause:
, MODIFY PARTITION old TO PARTITION new expression (1) REMAINDER IN dbspace

Notes: 1 Use this path no more than once

2-28

IBM Informix Guide to SQL: Syntax

ALTER FRAGMENT
Element dbspace expression Description Dbspace that stores the modified information Modified expression Restrictions Must exist at time of execution Can specify columns in current table only and data from only a single row Must be unique in fragmentation list among names of partitions of dbspace Must exist at time of execution Syntax Identifier on page 5-22 Condition on page 4-5; Expression on page 4-34 Identifier on page 5-22 Identifier on page 5-22

new old

Name that you declare here for a partition of dbspace Name of an existing fragment

Here dbspace and old (or old and new) can be identical, if you are not changing the storage location. You must declare a new partition (using the PARTITION keyword) if more than one fragment of the same table or index is named the same as the dbspace. The expression must evaluate to a Boolean value (true or false). No subqueries or aggregates are allowed in the expression. In addition, the built-in CURRENT, DATE, and TODAY functions are not valid. When you use the MODIFY clause, the underlying dbspaces are not affected. Only the fragment data values within the partitions or dbspaces are affected. You cannot change a REMAINDER fragment into a nonremainder fragment if records within the REMAINDER fragment do not satisfy the new expression. When you use the MODIFY clause to change an expression without changing the storage location for the expression, you must use the same name for the old and the new fragment (or else the same name for old and for dbspace, if the dbspace consists of only a single partition, as in the following example):
ALTER FRAGMENT ON TABLE cust_acct MODIFY dbsp1 TO acct_num < 65 IN dbsp1

When you use the MODIFY clause to move an expression from one dbspace to another, old is the name of the dbspace where the expression was previously located, and dbspace is the new location for the expression:
ALTER FRAGMENT ON TABLE cust_acct MODIFY dbsp1 TO acct_num < 35 IN dbsp2

Here the distribution scheme for the cust_acct table is modified so that all row items in column acct_num that are less than 35 are now contained in the dbspace dbsp2. These items were formerly contained in the dbspace dbsp1. When you use the MODIFY clause to move an expression from one partition of a dbspace to another partition, old is the name of the partition where information fragmented by the expression was previously located, and new is the name of the partition that is the new location for the expression, as in the following example:
ALTER FRAGMENT ON TABLE cust_acct MODIFY PARTITION part1 TO PARTITION part2 (acct_num < 35) IN dbsp2

Chapter 2. SQL Statements

2-29

ALTER FRAGMENT
Here the distribution scheme for the cust_acct table is modified so that all row items in column acct_num that have a value less than 35 are now contained in the partition part2 of dbspace dbsp2. These items were formerly contained in the partition part1. To use the MODIFY clause both to change the expression and to move its corresponding fragment to a new storage location, you must change the expression and you must also specify the name of a different dbspace or partition. If the indexes on a table are attached indexes, and you modify the table, the index fragmentation strategy is also modified.

Related Information
Related statements: CREATE TABLE, CREATE INDEX, and ALTER TABLE For a discussion of fragmentation strategy, refer to the IBM Informix Database Design and Implementation Guide. For information on how to maximize performance when you make fragment modifications, see your IBM Informix Performance Guide.

2-30

IBM Informix Guide to SQL: Syntax

ALTER FUNCTION
Use the ALTER FUNCTION statement to change the routine modifiers or pathname of a user-defined function. Only Dynamic Server supports this statement, which is an extension to the ANSI/ISO standard for SQL.

Syntax
, ALTER FUNCTION function ( parameter_type (1) Specific Name )

SPECIFIC FUNCTION ,

(2) WITH( ADD Routine Modifier MODIFY DROP (3) MODIFY EXTERNAL NAME = )

(4) Shared-Object Filename

Notes: 1 2 3 4
Element function

See Specific Name on page 5-68 See Routine Modifier on page 5-54 External routines only See Shared-Object Filename on page 5-65
Restrictions Must be registered in the database. If the name does not uniquely identify a function, you must enter one or more appropriate values for parameter_type. Must be the same data types (and specified in the same order) as in the definition of function Syntax Database Object Name on page 5-17 Identifier on page 5-22

Description User-defined function to be modified Data type of a parameter

parameter_type

Usage
The ALTER FUNCTION statement can modify a user-defined function to tune its performance by modifying characteristics that control how the function executes. You can also add or replace related user-defined routines (UDRs) that provide alternatives for the query optimizer, which can improve performance. All modifications take effect on the next invocation of the function. Only the UDR owner or the DBA can use the ALTER FUNCTION statement.

Chapter 2. SQL Statements

2-31

ALTER FUNCTION

Keywords That Introduce Modifications

Use the following keywords to introduce what you modify in the UDR.
Keyword ADD MODIFY DROP MODIFY EXTERNAL NAME (for external functions only) Effect on Specified Routine Modifier Add a new routine modifier to the UDR Change an attribute of the routine modifier Delete the routine modifier from the UDR Replace the file specification of the executable file. When the IFX_EXTEND_ROLE configuration parameter = ON, this option is valid only for users to whom the DBSA has granted the EXTEND role. With IFX_EXTEND_ROLE = OFF (or not set), the UDR owner or the DBA can use this option. Introduces all modifications

WITH

If the routine modifier is a BOOLEAN value, MODIFY sets the value to be t (equivalent of using the keyword ADD to add the routine modifier). For example, both of the following statements alter the func1 function so that it can be executed in parallel in the context of a parallelizable data query:
ALTER FUNCTION func1 WITH (MODIFY PARALLELIZABLE) ALTER FUNCTION func1 WITH (ADD PARALLELIZABLE)

See also Altering Routine Modifiers Example on page 2-38.

Related Information
Related Statements: ALTER PROCEDURE, ALTER ROUTINE, CREATE FUNCTION, and CREATE PROCEDURE For a discussion of how to create and use SPL routines, see the IBM Informix Guide to SQL: Tutorial. For a discussion of how to create and use external routines, see IBM Informix User-Defined Routines and Data Types Developer's Guide and the IBM Informix DataBlade API Programmer's Guide.

2-32

IBM Informix Guide to SQL: Syntax

ALTER INDEX
Use the ALTER INDEX statement to change the clustering attribute or the locking mode of an existing index. This statement is an extension to the ANSI/ISO standard for SQL.

Syntax
(1) ALTER INDEX index (2) LOCK MODE NORMAL COARSE TO NOT CLUSTER

Notes: 1 2
Element index Description Name of the index to be altered

Dynamic Server only Extended Parallel Server only

Restrictions Must exist Syntax Database Object Name on page 5-17

Usage
ALTER INDEX is valid only for indexes that the CREATE INDEX statement created explicitly. ALTER INDEX cannot modify an index on a temporary table, nor an index that the database server created implicitly to support a constraint. You cannot change the collating order of an existing index. If you use ALTER INDEX to modify an index after the SET COLLATION statement of Dynamic Server has specified a non-default collating order, the SET COLLATION statement has no effect on the index.

TO CLUSTER Option
The TO CLUSTER option causes the database server to reorder the rows of the physical table according to the indexed order. The next example shows how you can use the ALTER INDEX TO CLUSTER statement to order the rows in the orders table physically. The CREATE INDEX statement creates an index on the customer_num column of the table. Then the ALTER INDEX statement causes the physical ordering of the rows.
CREATE INDEX ix_cust ON orders (customer_num); ALTER INDEX ix_cust TO CLUSTER;

For an ascending index, TO CLUSTER puts rows in lowest-to-highest order. For a descending index, the rows are reordered in highest-to-lowest order. When you reorder, the entire file is rewritten. This process can take a long time, and it requires sufficient disk space to maintain two copies of the table. While a table is clustering, it is locked IN EXCLUSIVE MODE. When another process is using the table to which the index name belongs, the database server
Chapter 2. SQL Statements

2-33

ALTER INDEX
cannot execute the ALTER INDEX statement with the TO CLUSTER option; it returns an error unless lock mode is set to WAIT. (When lock mode is set to WAIT, the database server retries the ALTER INDEX statement.) Over time, if you modify the table, you can expect the benefit of an earlier cluster to disappear because rows are added in space-available order, not sequentially. You can recluster the table to regain performance by issuing another ALTER INDEX TO CLUSTER statement on the clustered index. You do not need to drop a clustered index before you issue another ALTER INDEX TO CLUSTER statement on a currently clustered index. Extended Parallel Server cannot use the CLUSTER option on STANDARD tables.

TO NOT CLUSTER Option

The TO NOT CLUSTER option drops the cluster attribute on the index name without affecting the physical table. Because no more than one clustered index can exist on a given table, you must use the TO NOT CLUSTER option to release the cluster attribute from one index before you assign it to another index on the same table. The following statements illustrate how to remove clustering from one index and how a second index physically reclusters the table:
CREATE UNIQUE INDEX ix_ord ON orders (order_num); CREATE CLUSTER INDEX ix_cust ON orders (customer_num); . . . ALTER INDEX ix_cust TO NOT CLUSTER; ALTER INDEX ix_ord TO CLUSTER;

The first two statements create indexes for the orders table and cluster the physical table in ascending order on the customer_num column. The last two statements recluster the physical table in ascending order on the order_num column.

LOCK MODE Options (XPS)

Use the LOCK MODE clause to specify the locking granularity of the index. When you use the COARSE mode, index-level locks are acquired on the index instead of item-level or page-level locks. This mode reduces the number of lock calls on an index. The COARSE mode offers performance advantages when you know that the index is unlikely to change; for example, when read-only operations are performed on the index. Use the NORMAL mode to place item-level or page-level locks on the index as necessary. Use this mode when the index gets updated frequently. When the database server executes the LOCK MODE COARSE option, it acquires an exclusive lock on the table for the duration of the ALTER INDEX statement. Any transactions currently using a lock of finer granularity must complete before the database server switches to the COARSE lock mode.

Related Information
Related statements: CREATE INDEX and CREATE TABLE For a discussion of the performance implications of clustered indexes, see your IBM Informix Performance Guide.

2-34

IBM Informix Guide to SQL: Syntax

ALTER PROCEDURE
Use the ALTER PROCEDURE statement to change the routine modifiers or pathname of a previously defined external procedure. Only Dynamic Server supports this statement, which is an extension to the ANSI/ISO standard for SQL.

Syntax
, ALTER PROCEDURE procedure ( parameter_type (1) Specific Name )

SPECIFIC PROCEDURE ,

(2) WITH( ADD Routine Modifier MODIFY DROP (3) MODIFY EXTERNAL NAME = )

(4) Shared-Object Filename

Notes: 1 2 3 4
Element procedure

See Specific Name on page 5-68 See Routine Modifier on page 5-54 External routines only See Shared-Object Filename on page 5-65
Restrictions Must be registered in the database. If the name does not uniquely identify a function, you must enter one or more appropriate values for parameter_type. Must be the same data types (and specified in the same order) as in the definition of procedure Syntax Database Object Name on page 5-17 Identifier on page 5-22

Description User-defined procedure to modify Data type of a parameter

parameter_type

Usage
The ALTER PROCEDURE statement enables you to modify an external procedure to tune its performance by modifying characteristics that control how it executes. You can also add or replace related UDRs that provide alternatives for the optimizer, which can improve performance. All modifications take effect on the next invocation of the procedure. Only the UDR owner or the DBA can use the ALTER PROCEDURE statement. If the procedure name is not unique among routines registered in the database, you must enter one or more appropriate values for parameter_type.

Chapter 2. SQL Statements

2-35

ALTER PROCEDURE
The following keywords introduce what you want to modify in procedure.
Keyword ADD MODIFY DROP MODIFY EXTERNAL NAME (for external procedures only) Effect Add a new routine modifier to the UDR Change an attribute of a routine modifier Delete a routine modifier from the UDR Replace the file specification of the executable file. When the IFX_EXTEND_ROLE configuration parameter = ON, this option is valid only for users to whom the DBSA has granted the EXTEND role. With IFX_EXTEND_ROLE = OFF (or not set), the UDR owner or the DBA can use this option. Replace the file specification of the executable file. (Valid only for users who have the EXTEND role) Introduces all modifications

MODIFY EXTERNAL NAME (for external procedures only) WITH

If the routine modifier is a BOOLEAN value, MODIFY sets the value to be T (equivalent to using the keyword ADD to add the routine modifier). For example, both of the following statements alter the proc1 procedure so that it can be executed in parallel in the context of a parallelizable data query:
ALTER PROCEDURE proc1 WITH (MODIFY PARALLELIZABLE) ALTER PROCEDURE proc1 WITH (ADD PARALLELIZABLE)

See also Altering Routine Modifiers Example on page 2-38.

Related Information
Related statements: ALTER ROUTINE, CREATE PROCEDURE, DROP PROCEDURE, and DROP ROUTINE. For a discussion of SPL routines, see the IBM Informix Guide to SQL: Tutorial. For a discussion of how to create and use external routines, see IBM Informix User-Defined Routines and Data Types Developer's Guide. For information about how to create C UDRs, see the IBM Informix DataBlade API Programmer's Guide.

2-36

IBM Informix Guide to SQL: Syntax

ALTER ROUTINE
Use the ALTER ROUTINE statement to change the routine modifiers or pathname of a previously defined user-defined routine (UDR). Only Dynamic Server supports this statement, which is an extension to the ANSI/ISO standard for SQL.

Syntax
, ALTER ROUTINE routine ( parameter_type (1) Specific Name )

SPECIFIC ROUTINE ,

(2) WITH( ADD Routine Modifier MODIFY DROP (3) MODIFY EXTERNAL NAME = )

(4) Shared-Object Filename

Notes: 1 2 3 4
Element routine

See Specific Name on page 5-68 See Routine Modifier on page 5-54 External routines only See Shared-Object Filename on page 5-65
Restrictions Must be registered in the database. If the name does not uniquely identify a routine, you must enter one or more appropriate values for parameter_type. Must be the same data types (and specified in the same order) as in the definition of routine Syntax Database Database Object Name on page 5-17 Identifier on page 5-22

Description User-defined routine to modify Data type of a parameter

parameter_type

Usage
The ALTER ROUTINE statement allows you to modify a previously defined UDR to tune its performance by modifying characteristics that control how the UDR executes. You can also add or replace related UDRs that provide alternatives for the optimizer, which can improve performance. This statement is useful when you do not know whether a UDR is a user-defined function or a user-defined procedure. When you use this statement, the database server alters the appropriate user-defined procedure or user-defined function. All modifications take effect on the next invocation of the UDR. Only the UDR owner or the DBA can use the ALTER ROUTINE statement.

Chapter 2. SQL Statements

2-37

ALTER ROUTINE

Restrictions
If the name does not uniquely identify a UDR, you must enter one or more appropriate values for parameter_type. When you use this statement, the type of UDR cannot be ambiguous. The UDR that you specify must refer to either a user-defined function or a user-defined procedure. If either of the following conditions exist, the database server returns an error: v The name (and parameters) that you specify applies to both a user-defined procedure and a user-defined function. v The specific name that you specify applies to both a user-defined function and a user-defined procedure.

Keywords That Introduce Modifications

Use these keywords to introduce the items in the UDR that you want to modify:
Keyword ADD DROP MODIFY MODIFY EXTERNAL NAME (for external routines only) Effect Add a routine modifier to the UDR Delete a routine modifier from the UDR Change an attribute of the routine modifier Replace the file specification of the executable file. When the IFX_EXTEND_ROLE configuration parameter = ON, this option is valid only for users to whom the DBSA has granted the EXTEND role. With IFX_EXTEND_ROLE = OFF (or not set), the UDR owner or the DBA can use this option. Introduces all modifications

WITH

If the routine modifier is a BOOLEAN value, MODIFY sets the value to be T (equivalent to using the keyword ADD to add the routine modifier). For example, both of the following statements alter the func1 UDR so that it can be executed in parallel in the context of a parallelizable data query statement:
ALTER ROUTINE func1 WITH (MODIFY PARALLELIZABLE) ALTER ROUTINE func1 WITH (ADD PARALLELIZABLE)

Altering Routine Modifiers Example

Suppose you have an external function func1 that is set to handle NULL values and has a cost per invocation set to 40. The following ALTER ROUTINE statement adjusts the settings of the function by dropping the ability to handle NULL values, tunes the func1 by changing the cost per invocation to 20, and indicates that the function can execute in parallel:
ALTER ROUTINE func1(CHAR, INT, BOOLEAN) WITH ( DROP HANDLESNULLS, MODIFY PERCALL_COST = 20, ADD PARALLELIZABLE )

Because the name func1 is not unique to the database, the data type parameters are specified so that the routine signature is unique. If this function had a Specific Name, for example, raise_sal, specified when it was created, you could identify the function with the following first line:
ALTER SPECIFIC ROUTINE raise_sal

2-38

IBM Informix Guide to SQL: Syntax

ALTER ROUTINE

Related Information
Related Statements: ALTER FUNCTION, ALTER PROCEDURE, CREATE FUNCTION, CREATE PROCEDURE, DROP FUNCTION, DROP PROCEDURE, and DROP ROUTINE. For a discussion of how to create and use SPL routines, see the IBM Informix Guide to SQL: Tutorial. For a discussion of how to create and use external routines, see IBM Informix User-Defined Routines and Data Types Developer's Guide. For information about how to create C UDRs, see the IBM Informix DataBlade API Programmer's Guide.

Chapter 2. SQL Statements

2-39

ALTER SEQUENCE
Use the ALTER SEQUENCE statement to modify the definition of a sequence object. Only Dynamic Server supports this statement, which is an extension to the ANSI/ISO standard for SQL.

Syntax
ALTER SEQUENCE owner. sequence

(1) (1)

NOCYCLE CYCLE CACHE size NOCACHE ORDER NOORDER BY INCREMENT step WITH RESTART NOMAXVALUE MAXVALUE max NOMINVALUE MINVALUE min restart

(1) (1) (1) (1) (1)

Notes: 1
Element max min owner restart sequence size step Description New upper limit on values New lower limit on values Owner of sequence New first value in sequence Name of existing sequence New number of values to preallocate in memory New interval between successive values

Use path no more than once

Restrictions Must be integer > CURRVAL and restart Must be integer < CURRVAL and restart Cannot be changed by this statement Must be integer in the INT8 range Must exist. Cannot be a synonym. Integer > 2 but < cardinality of values in one cycle (= |(max - min)/step|) Must be a nonzero integer Syntax Literal Number on page 4-137 Literal Number on page 4-137 Owner Name on page 5-43 Literal Number on page 4-137 Identifier on page 5-22 Literal Number on page 4-137 Literal Number on page 4-137

2-40

IBM Informix Guide to SQL: Syntax

ALTER SEQUENCE

Usage
ALTER SEQUENCE redefines an existing sequence object. It only affects subsequently generated values (and any unused values in the sequence cache). You cannot use the ALTER SEQUENCE statement to rename a sequence nor to change the owner of a sequence. You must be the owner, or the DBA, or else have the Alter privilege on the sequence to modify its definition. Only elements of the sequence definition that you specify explicitly in the ALTER SEQUENCE statement are modified. An error occurs if you make contradictory changes, such as specifying both MAXVALUE and NOMAXVALUE, or both the CYCLE and NOCYCLE options.

INCREMENT BY Option
Use the INCREMENT BY option to specify a new interval between successive numbers in a sequence. The interval, or step value, can be a positive whole number (for ascending sequences) or a negative whole number (for descending sequences) in the INT8 range. The BY keyword is optional.

RESTART WITH Option

Use the RESTART WITH option to specify a new first number of the sequence. The restart value must be an integer within the INT8 range that is greater than or equal to the min value (for an ascending sequence) or that is less than or equal to the max value (for a descending sequence), if min or max is specified in the ALTER SEQUENCE statement. The WITH keyword is optional. When you modify a sequence using the RESTART option, the restart value is stored in the syssequences system catalog table only until the first use of the sequence object in a NEXTVAL expression. After that, the value is reset in the system catalog. Use of the dbschema utility can increment sequence objects in the database, creating gaps in the generated numbers that might not be expected in applications that require serialized integers.

MAXVALUE or NOMAXVALUE Option

Use the MAXVALUE option to specify a new upper limit of values in the sequence. The maximum value, or max, must be an integer in the INT8 range that is greater than sequence.CURRVAL and restart (or greater than the origin in the original CREATE SEQUENCE statement, if restart is not specified). Use the NOMAXVALUE option to replace the current limit with a new default maximum of 2e64 for ascending sequences or -1 for descending sequences.

MINVALUE or NOMINVALUE Option

Use the MINVALUE option to specify a new lower limit of values in the sequence. The minimum value, or min, must be an integer the INT8 range that is less than sequence.CURRVAL and restart (or less than the origin in the original CREATE SEQUENCE statement, if restart is not specified). Use the NOMINVALUE option to replace the current lower limit with a default of 1 for ascending sequences or -(2e64) for descending sequences.

CYCLE or NOCYCLE Option

Use the CYCLE option to continue generating sequence values after the sequence reaches the maximum (ascending) or minimum (descending) limit, to replace the

Chapter 2. SQL Statements

2-41

ALTER SEQUENCE
NOCYCLE attribute. After an ascending sequence reaches max, it generates the min value for the next value. After a descending sequence reaches min, it generates the max value for the next sequence value. Use the NOCYCLE option to prevent the sequence from generating more values after reaching the declared limit. Once the sequence reaches the limit, the next reference to sequence.NEXTVAL returns an error message.

CACHE or NOCACHE Option

Use the CACHE option to specify a new number of sequence values that are preallocated in memory for rapid access. The cache size must be a whole number in the INT range that is less than the number of values in a cycle (or less than (|max - min)/step|). The minimum size is 2 preallocated values. Use NOCACHE to have no values preallocated in memory. (See also the description of SEQ_CACHE_SIZE in CREATE SEQUENCE on page 2-165.)

ORDER or NOORDER Option

These keywords have no effect on the behavior of the sequence. The sequence always issues values to users in the order of their requests, as if the ORDER keyword were always specified. The ORDER and NOORDER keywords are accepted by the ALTER SEQUENCE statement, however, for compatibility with implementations of sequence objects in other dialects of SQL.

Related Information
Related statements: CREATE SEQUENCE, DROP SEQUENCE, RENAME SEQUENCE, CREATE SYNONYM, DROP SYNONYM, GRANT, REVOKE, INSERT, UPDATE, and SELECT For information about the syssequences system catalog table in which sequence objects are registered, see the IBM Informix Guide to SQL: Reference. For information about initializing, generating, or reading values from a sequence, see NEXTVAL and CURRVAL Operators (IDS) on page 4-61.

2-42

IBM Informix Guide to SQL: Syntax

ALTER TABLE
Use the ALTER TABLE statement to modify the definition of an existing table.

Syntax
(1) ALTER TABLE table synonym Basic Table Options (2) Logging TYPE Options

Notes: 1 2
Element synonym table Description Synonym for the table to be altered Name of table to be altered

See page 2-44 See page 2-63

Restrictions Synonym and its table must exist; USETABLENAME not set Must exist in current database Syntax Database Object Name, p. 5-17 Database Object Name, p. 5-17

Usage
In Dynamic Server, the database server performs the actions in the ALTER TABLE statement in the order that you specify. If any action fails, the entire operation is cancelled. Altering a table on which a view depends might invalidate the view. Warning: The clauses available with this statement have varying performance implications. Before you undertake alter operations, check corresponding sections in your IBM Informix Performance Guide to review effects and strategies. You cannot alter a temporary table. You also cannot alter a violations or diagnostics table. In addition, you cannot add, drop, or modify a column if the table that contains the column has a violation table or a diagnostics table associated with it. If the USETABLENAME environment variable is set, you cannot specify a synonym for the table in the ALTER TABLE statement. In Extended Parallel Server, if a table has range fragmentation, only the Logging TYPE options and LOCK MODE clause are valid. All other ALTER TABLE options return an error. For a RAW or (in Extended Parallel Server) a STATIC table, the Logging TYPE options are the only part of the ALTER TABLE statement that you can use. To use ALTER TABLE, you must meet one of the following conditions: v You must have DBA privilege on the database containing the table. v You must own the table (and for Extended Parallel Server, you must also have the Resource privilege). v You must have the Alter privilege on the specified table and the Resource privilege on the database where the table resides.
Chapter 2. SQL Statements

2-43

ALTER TABLE
v To add a referential constraint, you must have the DBA or References privilege on either the referenced columns or the referenced table. v To drop a constraint, you must have the DBA privilege or be the owner of the constraint. If you are the owner of the constraint but not the owner of the table, you must have Alter privilege on the specified table. You do not need the References privilege to drop a constraint.

Basic Table Options

The Basic Table Options segment of ALTER TABLE has the following syntax. Basic Table Options:
, (1) ADD Clause (2) ADD CONSTRAINT Clause (3) MODIFY Clause (4) DROP CONSTRAINT Clause (5) DROP Clause (6) MODIFY NEXT SIZE Clause (6) (8) LOCK MODE Clause (9) (10) PUT Clause (9) ADD DROP (9) CRCOLS ROWIDS (11) ADD TYPE Clause

(7)

Notes: 1 2 3 4 5 6 7 8 9 10 11 See page 2-45 See page 2-59 See page 2-53 See page 2-61 See page 2-52 Use path once See page 2-61 See page 2-62 Dynamic Server only See page 2-58 See page 2-64

You can use the Basic Table Options segment to modify the schema of a table by adding, modifying, or dropping columns and constraints, or changing the extent

2-44

IBM Informix Guide to SQL: Syntax

ALTER TABLE
size or locking granularity of a table. The database server performs alterations in the order that you specify. If any of the actions fails, the entire operation is cancelled. With Dynamic Server, you can also associate a table with a named ROW type or specify a new storage space to store large-object data. You can also add or drop rowid columns and shadow columns for Enterprise Replication. You cannot, however, specify these options in conjunction with any other alterations.

Using the ADD ROWIDS Keywords (IDS)

Use the ADD ROWIDS keywords to add a new column called rowid to a fragmented table. (Fragmented tables do not contain the hidden rowid column by default.) When you add a rowid column, the database server assigns a unique number to each row that remains stable for the life of the row. The database server creates an index that it uses to find the physical location of the row. After you add the rowid column, each row of the table contains an additional 4 bytes to store the rowid value. Tip: Use the ADD ROWIDS clause only on fragmented tables. In nonfragmented tables, the rowid column remains unchanged. It is recommended that you use primary keys as an access method rather than exploiting the rowid column. For additional information about the rowid column, refer to your IBM Informix Administrator's Reference.

Using the DROP ROWIDS Keywords (IDS)

The DROP ROWIDS keywords can drop a rowid column that you added (with either the CREATE TABLE or ALTER FRAGMENT statement) to a fragmented table. You cannot drop the rowid column of a nonfragmented table.

Using the ADD CRCOLS Keywords (IDS)

The add CRCOLS keywords create shadow columns, cdrserver and cdrtime, that Enterprise Replication uses for conflict resolution. These columns enable the database server to use the time-stamp or SPL conflict-resolution rule. For more information, refer to Using the WITH CRCOLS Option (IDS) on page 2-188 and to the IBM Informix Dynamic Server Enterprise Replication Guide.

Using the DROP CRCOLS Keywords (IDS)

Use the DROP CRCOLS keywords to drop the cdrserver and cdrtime shadow columns. You cannot drop these columns if Enterprise Replication is in use.

ADD Clause
Use the ADD Column clause to add a column to a table. ADD Column Clause:
, ADD ( New Column New Column )

New Column:

Chapter 2. SQL Statements

2-45

ALTER TABLE
(1) new_column Data Type (2) DEFAULT Clause

(3) Single-Column Constraint Format

BEFORE column

Notes: 1 2 3
Element column new_column Description Name of column before which new_column is to be placed Name of column that you are adding

See page 4-18 See page 2-46 See page 2-47

Restrictions Must already exist in the table You cannot add a serial column if the table contains data Syntax Identifier, p. 5-22 Identifier, p. 5-22

The following restrictions apply to the ADD clause: v You cannot add a serial column to a table that contains data. v In Extended Parallel Server, you cannot add a column to a table that has a bit-mapped index. v You cannot add columns beyond the maximum row size of 32,767 bytes.

Using the BEFORE Option

The BEFORE option specifies the column before which to add the new columns. In the following example, the BEFORE option directs the database server to add the item_weight column before the total_price column:
ALTER TABLE items ADD (item_weight DECIMAL(6,2) NOT NULL BEFORE total_price)

If you do not include the BEFORE option, the database server adds the new column or list of columns to the end of the table definition by default.

DEFAULT Clause
Use the DEFAULT clause to specify value that the database server should insert in a column when an explicit value for the column is not specified.

2-46

IBM Informix Guide to SQL: Syntax

ALTER TABLE
DEFAULT Clause:
DEFAULT literal NULL USER (1) CURRENT (2) DATETIME Field Qualifier TODAY SITENAME DBSERVERNAME

Notes: 1 2
Element literal Description Literal default value for the column

Informix extension See page 4-32

Restrictions Must be appropriate for the data type of the column. See Using a Literal as a Default Value on page 2-175. Syntax Expression, p. 4-34

You cannot specify a default value for serial columns. If the table that you are altering already has rows in it when you add a column that contains a default value, the database server inserts the default value for all pre-existing rows. The following example adds a column to the items table. In items, the new column item_weight has a literal default value:
ALTER TABLE items ADD item_weight DECIMAL (6, 2) DEFAULT 2.00 BEFORE total_price

In this example, each existing row in the items table has a default value of 2.00 for the item_weight column. For more information about the options of the DEFAULT clause, refer to DEFAULT Clause on page 2-174.

Single-Column Constraint Format

Use the Single-Column Constraint Format to associate constraints with a specified column. Single-Column Constraint Format:

NOT NULL (1) Constraint Definition (2)

Chapter 2. SQL Statements

2-47

ALTER TABLE

UNIQUE (1) DISTINCT PRIMARY KEY (3) REFERENCES Clause (4) CHECK Clause

(1) Constraint Definition

(2)

Notes: 1 2 3 4 Informix extension See page 2-49 See page 2-49 See page 2-51

You cannot specify a primary-key or unique constraint on a new column if the table contains data. In the case of a unique constraint, however, the table can contain a single row of data. When you want to add a column with a primary-key constraint, the table must be empty when you issue the ALTER TABLE statement. The following rules apply when you place primary-key or unique constraints on existing columns: v When you place a primary-key or unique constraint on a column or set of columns, the database server creates an internal B-tree index on the constrained column or set of columns unless a user-created index was defined on the column or set of columns. v When you place a primary-key or unique constraint on a column or set of columns, and a unique index already exists on that column or set of columns, the constraint shares the index. If the existing index allows duplicates, however, the database server returns an error. You must then drop the existing index before you add the constraint. v When you place a primary-key or unique constraint on a column or set of columns, and a referential constraint already exists on that column or set of columns, the duplicate index is upgraded to UNIQUE (if possible), and the index is shared. You cannot place a unique constraint on a BYTE or TEXT column, nor can you place referential constraints on columns of these types. A check constraint on a BYTE or TEXT column can check only for IS NULL, IS NOT NULL, or LENGTH. When you place a referential constraint on a column or set of columns, and an index already exists on that column or set of columns, the index is upgraded to UNIQUE (if possible) and the index is shared.

Using NOT NULL Constraints with ADD

If a table contains data, when you add a column with a NOT NULL constraint you must also include a DEFAULT clause. If the table is empty, however, you can add a column and apply only the NOT NULL constraint. The following statement is valid whether or not the table contains data:
ALTER TABLE items ADD (item_weight DECIMAL(6,2) DEFAULT 2.0 NOT NULL BEFORE total_price)

2-48

IBM Informix Guide to SQL: Syntax

ALTER TABLE

Constraint Definition
In Dynamic Server, use the Constraint Definition portion of the ALTER TABLE statement to declare the name of a constraint and to set the mode of the constraint to disabled, enabled, or filtering. In Extended Parallel Server, use the Constraint Definition portion of the ALTER TABLE statement to declare the name of a constraint. Constraint Definition:

CONSTRAINT constraint

(1) ENABLED DISABLED FILTERING WITHOUT ERROR WITH ERROR

Notes: 1
Element constraint Description Name declared here for the constraint

Dynamic Server only

Restrictions Must be unique among the names of indexes and constraints in database Syntax Identifier, p. 5-22

For more information about constraint-mode options, see Choosing a Constraint-Mode Option (IDS) on page 2-183.

REFERENCES Clause
The REFERENCES clause has the following syntax. REFERENCES Clause:
REFERENCES table , ( column ) (1) ON DELETE CASCADE

Notes: 1
Element column table Description

Informix extension
Restrictions Syntax

Referenced column in the See Restrictions on Referential Constraints Identifier, p. 5-22 referenced table on page 2-50. The referenced table The referenced and the referencing tables must reside in the same database Database Object Name, p. 5-17

The REFERENCES clause allows you to place a foreign-key reference on a column. The referenced column can be in the same table as the referencing column, or in a different table in the same database.

Chapter 2. SQL Statements

2-49

ALTER TABLE
If the referenced table is different from the referencing table, the default column is the primary-key column. If the referenced table is the same as the referencing table, there is no default.

Restrictions on Referential Constraints

You must have the REFERENCES privilege to create a referential constraint. The following restrictions apply to the column that is specified (the referenced column) in the REFERENCES clause: v The referenced and referencing tables must be in the same database. v The referenced column (or set of columns) must have a unique or primary-key constraint. v The referencing and referenced columns must be the same data type. (The only exception is that a referencing column must be an integer data type if the referenced column is a serial data type.) v You cannot place a referential constraint on a BYTE or TEXT column. v Constraints uses the collation in effect at their time of creation. v A column-level REFERENCES clause can include only a single column name. v Maximum number of columns in a table-level REFERENCES clause is 16. v In Dynamic Server, the total length of the columns in a table-level REFERENCES clause cannot exceed 390 bytes. v In Extended Parallel Server, the total length of the columns in a table-level REFERENCES clause cannot exceed 255 bytes.

Default Column for the References Clause

If the referenced table is different from the referencing table, you do not need to specify the referenced column; the default column is the primary-key column (or columns) of the referenced table. If the referenced table is the same as the referencing table, you must specify the referenced column. The following example creates a new column in the cust_calls table, ref_order. The ref_order column is a foreign key that references the order_num column in the orders table.
ALTER TABLE cust_calls ADD ref_order INTEGER REFERENCES orders (order_num) BEFORE user_id

When you place a referential constraint on a column or set of columns, and a duplicate or unique index already exists on that column or set of columns, the index is shared.

Using the ON DELETE CASCADE Option

Use the ON DELETE CASCADE option if you want rows deleted in the child table when corresponding rows are deleted in the parent table. If you do not specify cascading deletes, the default behavior of the database server prevents you from deleting data in a table if other tables reference it. If you specify this option, when you delete a row in the parent table, the database server also deletes any rows associated with that row (foreign keys) in a child table. The advantage of the ON DELETE CASCADE option is that it allows you to reduce the quantity of SQL statements needed to perform delete actions.

2-50

IBM Informix Guide to SQL: Syntax

ALTER TABLE
For example, in the stores_demo database, the stock table contains the stock_num column as a primary key. The catalog table refers to the stock_num column as a foreign key. The following ALTER TABLE statements drop an existing foreign-key constraint (without cascading delete) and add a new constraint that specifies cascading deletes:
ALTER TABLE catalog DROP CONSTRAINT aa ALTER TABLE catalog ADD CONSTRAINT (FOREIGN KEY (stock_num, manu_code) REFERENCES stock ON DELETE CASCADE CONSTRAINT ab)

With cascading deletes specified on the child table, in addition to deleting a stock item from the stock table, the delete cascades to the catalog table that is associated with the stock_num foreign key. This cascading delete works only if the stock_num that you are deleting was not ordered; otherwise, the constraint from the items table would disallow the cascading delete. For more information, see Restrictions on DELETE When Tables Have Cascading Deletes on page 2-277. If a table has a trigger with a DELETE trigger event, you cannot define a cascading-delete referential constraint on that table. You receive an error when you attempt to add a referential constraint that specifies ON DELETE CASCADE to a table that has a delete trigger. For information about syntax restrictions and locking implications when you delete rows from tables that have cascading deletes, see Considerations When Tables Have Cascading Deletes on page 2-276.

Locks Held During Creation of a Referential Constraint

When you create a referential constraint, the database server places an exclusive lock on the referenced table. The lock is released after you finish with the ALTER TABLE statement or at the end of a transaction (if you are altering the table in a database that uses transaction logging).

CHECK Clause
A check constraint designates a condition that must be met before data can be inserted into a column. CHECK Clause:
(1) CHECK ( Condition )

Notes: 1 See page 4-5

During an insert or update, if a row returns false for any check constraint defined on a table, the database server returns an error. No error is returned, however, if a row returns NULL for a check constraint. In some cases, you might want to use both a check constraint and a NOT NULL constraint. Check constraints are defined using search conditions. The search condition cannot contain user-defined routines, subqueries, aggregates, host variables, or rowids. In addition, the condition cannot contain the variant built-in functions CURRENT, USER, SITENAME, DBSERVERNAME, or TODAY.

Chapter 2. SQL Statements

2-51

ALTER TABLE
The check constraint cannot include columns in different tables. When you are using the ADD or MODIFY clause, the check constraint cannot depend upon values in other columns of the same table. The next example adds a new unit_price column to the items table and includes a check constraint to ensure that the entered value is greater than 0:
ALTER TABLE items ADD (unit_price MONEY (6,2) CHECK (unit_price > 0) )

To create a constraint that checks values in more than one column, use the ADD CONSTRAINT clause. The following example builds a constraint on the column that was added in the previous example. The check constraint now spans two columns in the table.
ALTER TABLE items ADD CONSTRAINT CHECK (unit_price < total_price)

DROP Clause
Use the DROP clause to drop one or more columns from a table. DROP Clause:
, DROP ( column column )

Element column

Description Name of column to be dropped

Restrictions Must exist in the table. No fragment expression can reference column, and it cannot be the last column in the table.

Syntax Identifier, p. 5-22

You cannot issue an ALTER TABLE DROP statement that would drop every column from the table. At least one column must remain in the table. You cannot drop a column that is part of a fragmentation strategy. In Extended Parallel Server, you cannot use the DROP clause if the table has a dependent GK index.

How Dropping a Column Affects Constraints

When you drop a column, all constraints on that column are also dropped: v All single-column constraints are dropped. v All referential constraints that reference the column are dropped. v All check constraints that reference the column are dropped. v If the column is part of a multiple-column primary-key or unique constraint, the constraints placed on the multiple columns are also dropped. This action, in turn, triggers the dropping of all referential constraints that reference the multiple columns. Because any constraints that are associated with a column are dropped when the column is dropped, the structure of other tables might also be altered when you use this clause. For example, if the dropped column is a unique or primary key that is referenced in other tables, those referential constraints also are dropped. Therefore the structure of those other tables is also altered.

2-52

IBM Informix Guide to SQL: Syntax

ALTER TABLE

How Dropping a Column Affects Triggers

In general, when you drop a column from a table, the triggers based on that table remain unchanged. If the column that you drop appears in the action clause of a trigger, however, dropping the column can invalidate the trigger. The following statements illustrate the possible effects on triggers:
CREATE TABLE tab1 (i1 int, i2 int, i3 int); CREATE TABLE tab2 (i4 int, i5 int); CREATE TRIGGER col1trig UPDATE OF i2 ON tab1 BEFORE(INSERT INTO tab2 VALUES(1,1)); ALTER TABLE tab2 DROP i4;

After the ALTER TABLE statement, tab2 has only one column. The col1trig trigger is invalidated because the action clause as it is currently defined with values for two columns cannot occur. If you drop a column that occurs in the triggering column list of an UPDATE trigger, the database server drops the column from the triggering column list. If the column is the only member of the triggering column list, the database server drops the trigger from the table. For more information on triggering columns in an UPDATE trigger, see CREATE TRIGGER on page 2-216. If a trigger is invalidated when you alter the underlying table, drop and then re-create the trigger.

How Dropping a Column Affects Views

When you drop a column from a table, the views based on that table remain unchanged. That is, the database server does not automatically drop the corresponding columns from associated views. The view is not automatically dropped because ALTER TABLE can change the order of columns in a table by dropping a column and then adding a new column with the same name. In this case, views based on the altered table continue to work, but retain their original sequence of columns. If a view is invalidated when you alter the underlying table, you must rebuild the view by using the DROP VIEW and CREATE VIEW statements.

How Dropping a Column Affects a Generalized-Key Index

In Extended Parallel Server, if you drop a column from a table that has a dependent GK index, all GK indexes on the table that refer to the dropped column are dropped. Any GK indexes on other tables that refer to the dropped column are also dropped.

MODIFY Clause (IDS)

Use the MODIFY clause to change the data type, length, or default value of a column, to allow or disallow NULL values in a column, or to reset the serial counter of a SERIAL or SERIAL8 column. MODIFY Clause:
, MODIFY ( Modify Column Clause Modify Column Clause )

Chapter 2. SQL Statements

2-53

ALTER TABLE
Modify Column Clause:
(1) column Data Type (2) DEFAULT Clause

(3) Single-Column Constraint Format

Notes: 1 2 3
Element column Description Column to modify

See page 4-18 See page 2-46 See page 2-47

Restrictions Must exist in table. Cannot be a collection data type. Syntax Identifier, p. 5-22

In Extended Parallel Server, you cannot use the MODIFY clause if the table has a dependent GK index. You cannot change the data type of a column to a COLLECTION or a ROW type. When you modify a column, all attributes previously associated with the column (that is, default value, single-column check constraint, or referential constraint) are dropped. When you want certain attributes of the column to remain, such as PRIMARY KEY, you must re-specify those attributes. For example, if you are changing the data type of an existing column, quantity, to SMALLINT, but you want to keep the default value (in this case, 1) and the NOT NULL column attribute, you can issue this statement:
ALTER TABLE items MODIFY (quantity SMALLINT DEFAULT 1 NOT NULL)

Tip: Both attributes are specified again in the modify clause. When you change the data type of a column, the database server does not perform the modification in place. The next example (for Dynamic Server only) changes a VARCHAR(15) column to an LVARCHAR(3072) column:
ALTER TABLE stock MODIFY (description LVARCHAR(3072))

When you modify a column that has column constraints associated with it, the following constraints are dropped: v All single-column constraints are dropped. v All referential constraints that reference the column are dropped. v If the modified column is part of a multiple-column primary-key or unique constraint, all referential constraints that reference the multiple columns also are dropped. For example, if you modify a column that has a unique constraint, the unique constraint is dropped. If this column was referenced by columns in other tables,

2-54

IBM Informix Guide to SQL: Syntax

ALTER TABLE
those referential constraints are also dropped. In addition, if the column is part of a multiple-column primary-key or unique constraint, the multiple-column constraints are not dropped, but any referential constraints placed on the column by other tables are dropped. For another example, suppose that a column is part of a multiple-column primary-key constraint. This primary key is referenced by foreign keys in two other tables. When this column is modified, the multiple-column primary-key constraint is not dropped, but the referential constraints placed on it by the two other tables are dropped.

Using the MODIFY Clause

The characteristics of the object you are attempting to modify can affect how you handle your modifications.

Altering BYTE and TEXT Columns

You can use the MODIFY clause to change a BYTE column to a TEXT column, and vice versa. You cannot use the MODIFY clause, however, to change a BYTE or TEXT column to any other type of column, and vice versa. In Dynamic Server, you can also use the MODIFY clause to change a BYTE column to a BLOB column and a TEXT column to a CLOB column.

Altering the Next Serial Value

You can use the MODIFY clause to reset the next value of a SERIAL or SERIAL8 column. You cannot set the next value below the current maximum value in the column because that action can cause the database server to generate duplicate numbers. You can set the next value, however, to any value higher than the current maximum, which creates a gap in the series of values. If the new serial value that you specify is less than the current maximum value in the serial column, the maximum value is not altered. If the maximum value is less than what you specify, the next serial number will be what you specify. The next serial value is not equivalent to one greater than the maximum serial value in the column in two situations: v There are no rows in the table, and an initial serial value was specified when the table was created (or by a previous ALTER TABLE statement). v There are rows in the table, but the next serial value was modified by a previous ALTER TABLE statement. The following example sets the next serial value to 1000:
ALTER TABLE my_table MODIFY (serial_num serial (1000))

As an alternative, you can use the INSERT statement to create a gap in the series of serial values in the column. For more information, see Inserting Values into Serial Columns on page 2-400. Altering the Next Serial Value in a Typed Table (IDS): You can set the initial serial number or modify the next serial number for a ROW-type field with the MODIFY clause of the ALTER TABLE statement. (You cannot set the initial number for a serial field when you create a ROW data type.) Suppose you have ROW types parent, child1, child2, and child3.

Chapter 2. SQL Statements

2-55

ALTER TABLE
CREATE CREATE CREATE CREATE ROW ROW ROW ROW TYPE TYPE TYPE TYPE parent child1 child2 child3 (a (s (b (d int); serial) UNDER parent; float, s8 serial8) UNDER child1; int) UNDER child2;

You then create corresponding typed tables:

CREATE CREATE CREATE CREATE TABLE TABLE TABLE TABLE OF OF OF OF TYPE TYPE TYPE TYPE parent; child1 UNDER parent; child2 UNDER child1; child3 UNDER child2;

To change the next SERIAL and SERIAL8 numbers to 75, you can issue the following statement:
ALTER TABLE child3 MODIFY (s serial(75), s8 serial8(75))

When the ALTER TABLE statement executes, the database server updates corresponding serial columns in the child1, child2, and child3 tables.

Altering the Structure of Tables

When you use the MODIFY clause, you can also alter the structure of other tables. If the modified column is referenced by other tables, those referential constraints are dropped. You must add those constraints to the referencing tables again, using the ALTER TABLE statement. When you change the data type of an existing column, all data is converted to the new data type, including numbers to characters and characters to numbers (if the characters represent numbers). The following statement changes the data type of the quantity column:
ALTER TABLE items MODIFY (quantity CHAR(6))

When a primary-key or unique constraint exists, however, conversion takes place only if it does not violate the constraint. If a data type conversion would result in duplicate values (by changing FLOAT to SMALLFLOAT, for example, or by truncating CHAR values), the ALTER TABLE statement fails.

Modifying Tables for NULL Values

You can modify an existing column that formerly permitted NULLs to disallow NULLs, provided that the column contains no NULL values. To do this, specify MODIFY with the same column name and data type and the NOT NULL keywords. Those keywords create a NOT NULL constraint on the column. You can modify an existing column that did not permit NULLs to permit NULLs. To do this, specify MODIFY with the column name and the existing data type, and omit the NOT NULL keywords. The omission of the NOT NULL keywords drops the NOT NULL constraint on the column. If a unique index exists on the column, you can remove it using the DROP INDEX statement. An alternative method of permitting NULL values in an existing column that did not permit NULL values is to use the DROP CONSTRAINT clause to drop the NOT NULL constraint on the column.

Adding a Constraint That Existing Rows Violate (IDS)

If you use the MODIFY clause to add a constraint in the enabled mode and receive an error message because existing rows would violate the constraint, take the following steps to add the constraint successfully: 1. Add the constraint in the disabled mode.

2-56

IBM Informix Guide to SQL: Syntax

ALTER TABLE
Issue the ALTER TABLE statement again, but this time specify the DISABLED keyword in the MODIFY clause. Start a violations and diagnostics table for the target table with the START VIOLATIONS TABLE statement. Issue the SET CONSTRAINTS statement to switch the database object mode of the constraint to the enabled mode. When you issue this statement, existing rows in the target table that violate the constraint are duplicated in the violations table; however, you receive an integrity-violation error message, and the constraint remains disabled. Issue a SELECT statement on the violations table to retrieve the nonconforming rows that are duplicated from the target table. You might need to join the violations and diagnostics tables to get all the necessary information. Take corrective action on the rows in the target table that violate the constraint. After you fix all the nonconforming rows in the target table, issue the SET statement again to enable the constraint that was disabled. Now the constraint is enabled, and no integrity-violation error message is returned because all rows in the target table now satisfy the new constraint.

2. 3.

4.

5. 6.

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

In Extended Parallel Server, when you modify a column, all GK indexes that reference the column are dropped if the column is used in the GK index in a way that is incompatible with the new data type of the column. For example, if a numeric column is changed to a character column, any GK indexes involving that column are dropped if they involve arithmetic expressions.

How Modifying a Column Affects Triggers

If you modify a column that appears in the triggering column list of an UPDATE trigger, the trigger is unchanged. When you modify a column in a table, the triggers based on that table remain unchanged, but the column modification might invalidate the trigger. The following statements illustrate the possible affects on triggers:
CREATE TABLE tab1 (i1 int, i2 int, i3 int); CREATE TABLE tab2 (i4 int, i5 int); CREATE TRIGGER col1trig UPDATE OF i2 ON tab1 BEFORE(INSERT INTO tab2 VALUES(1,1)); ALTER TABLE tab2 MODIFY i4 char;

After the ALTER TABLE statement, column i4 accepts only character values. Because character columns accept only values enclosed in quotation marks, the action clause of the col1trig trigger is invalidated. If a trigger is invalidated when you modify the underlying table, drop and then re-create the trigger.

How Modifying a Column Affects Views

When you modify a column in a table, the views based on that table remain unchanged. If a view is invalidated when you alter the underlying table, you must rebuild the view.

Chapter 2. SQL Statements

2-57

ALTER TABLE

PUT Clause (IDS)

Use the PUT clause to specify the storage space (an sbspace) for a column that contains smart large objects. This clause can specify storage characteristics for a new column or replace the storage characteristics of an existing column. The syntax is similar to the PUT clause of the CREATE TABLE statement (page 2-199), but specifies only a single column, rather than a list of columns. PUT Clause:
, PUT column IN ( sbspace )

, ( EXTENT SIZE kilobytes NO LOG LOG HIGH INTEG NO KEEP ACCESS TIME KEEP ACCESS TIME )

Element column kilobytes sbspace

Description Column to store in the specified sbspace Number of kilobytes to allocate for the extent size

Restrictions Must be a UDT, or a complex, BLOB, or CLOB data type Must be an integer value

Syntax Identifier, p. 5-22 Literal Number, p. 4-137 Identifier, p. 5-22

Name of an area of storage for The sbspace must exist smart large objects

When you modify the storage characteristics of a column, all attributes previously associated with the storage space for that column are dropped. When you want certain attributes to remain, you must specify those attributes again. For example, to retain logging, you must specify the log keyword again. The format column.field is not valid here. That is, the smart large object that you are storing cannot be one field of a row type. When you modify the storage characteristics of a column that holds smart large objects, the database server does not alter smart large objects that already exist, but applies the new storage characteristics only to those smart large objects that are inserted after the ALTER TABLE statement takes effect. For more information on the available storage characteristics, refer to the counterpart of this section in the CREATE TABLE statement, PUT Clause (IDS) on page 2-199. For a discussion of large-object characteristics, refer to Large-Object Data Types on page 4-25.

2-58

IBM Informix Guide to SQL: Syntax

ALTER TABLE

ADD CONSTRAINT Clause

Use the ADD CONSTRAINT clause to specify a constraint on a new or existing column or on a set of columns. ADD CONSTRAINT Clause:
(1) ADD CONSTRAINT Multiple-Column Constraint Format , (1) ( Multiple-Column Constraint Format )

Notes: 1 See page 2-59

For example, to add a unique constraint to the fname and lname columns of the customer table, use the following statement:
ALTER TABLE customer ADD CONSTRAINT UNIQUE (lname, fname)

To declare a name for the constraint, change the preceding statement:

ALTER TABLE customer ADD CONSTRAINT UNIQUE (lname, fname) CONSTRAINT u_cust

When you do not specify a name for a new constraint, the database server provides one. You can find the name of the constraint in the sysconstraints system catalog table. For more information about the sysconstraints system catalog table, see the IBM Informix Guide to SQL: Reference. When you add a constraint, the collating order must be the same as when the table was created.

Multiple-Column Constraint Format

Use the Multiple-Column Constraint Format option to assign a constraint to one column or a set of columns. Multiple-Column Constraint Format:
, (2) UNIQUE (1) ( column )

DISTINCT PRIMARY KEY (3) CHECK Clause , (2) FOREIGN KEY (

(4) column ) REFERENCES Clause

(1) Constraint Definition

(5)

Chapter 2. SQL Statements

2-59

ALTER TABLE
Notes: 1 2 3 4 5
Element column Description A column on which the constraint is placed

Informix extension Use path no more than 16 times See page 2-51 See page 2-49 See page 2-49
Restrictions No more than 16 columns Syntax Identifier, p. 5-22

A multiple-column constraint has these restrictions: v It can include no more than 16 column names. v In Dynamic Server, the total length of the list of columns cannot exceed 390 bytes. v In Extended Parallel Server, the total length of the list of columns cannot exceed 255 bytes. You can declare a name for the constraint and set its mode by means of Constraint Definition on page 2-49.

Adding a Primary-Key or Unique Constraint

When you place a primary-key or unique constraint on a column or set of columns, those columns must contain unique values. The database server checks for existing constraints and indexes: v If a user-created unique index already exists on that column or set of columns, the constraint shares the index. v If a user-created index that allows duplicates already exists on that column or set of columns, the database server returns an error. In this case, you must drop the existing index before adding the primary-key or unique constraint. v If a referential constraint already exists on that column or set of columns, the duplicate index is upgraded to unique (if possible) and the index is shared. v If no referential constraint or user-created index exists on that column or set of columns, the database server creates an internal B-tree index on the specified columns. When you place a referential constraint on a column or set of columns, and an index already exists on that column or set of columns, the index is shared. If you own the table or have the Alter privilege on the table, you can create a check, primary-key, or unique constraint on the table and specify yourself as the owner of the constraint. To add a referential constraint, you must have the References privilege on either the referenced columns or the referenced table. When you have the DBA privilege, you can create constraints for other users.

Recovery from Constraint Violations (IDS)

If you use the ADD CONSTRAINT clause to add a constraint in the enabled mode, you receive an error message because existing rows would violate the constraint. For a procedure to add the constraint successfully, see Adding a Constraint That Existing Rows Violate (IDS) on page 2-56.

2-60

IBM Informix Guide to SQL: Syntax

ALTER TABLE

DROP CONSTRAINT Clause

Use the DROP CONSTRAINT clause to drop a named constraint. DROP CONSTRAINT Clause:
, DROP CONSTRAINT ( constraint constraint )

Element constraint

Description Constraint to be dropped

Restrictions Must exist.

Syntax Database Object Name, p. 5-17

To drop an existing constraint, specify the DROP CONSTRAINT keywords and the name of the constraint. Here is an example of dropping a constraint:
ALTER TABLE manufact DROP CONSTRAINT con_name

If no name is specified when the constraint is created, the database server generates the name. You can query the sysconstraints system catalog table for the name and owner of a constraint. For example, to find the name of the constraint placed on the items table, you can issue the following statement:
SELECT constrname FROM sysconstraints WHERE tabid = (SELECT tabid FROM systables WHERE tabname = items)

When you drop a primary-key or unique constraint that has a corresponding foreign key, the referential constraints are dropped. For example, if you drop the primary-key constraint on the order_num column in the orders table and order_num exists in the items table as a foreign key, that referential relationship is also dropped. You cannot use the MODIFY NEXT SIZE clause of the ALTER TABLE statement to change the size of the next extent of any system catalog table, even if you are user informix.

MODIFY NEXT SIZE Clause

Use the MODIFY NEXT SIZE clause to change the size of new extents. MODIFY NEXT SIZE Clause:
MODIFY NEXT SIZE kilobytes

Element kilobytes

Description Length (in kilobytes) assigned here to the next extent for this table

Restrictions Specification cannot be a variable, and (4(page size)) kilobytes (chunk size)

Syntax Expression, p. 4-34

The minimum extent size is 4 times the disk-page size. For example, on a system with 2-kilobyte pages, the minimum length is 8 kilobytes. The maximum length is equal to the chunk size. This example specifies an extent size of 32 kilobytes:
ALTER TABLE customer MODIFY NEXT SIZE 32

Chapter 2. SQL Statements

2-61

ALTER TABLE
This clause cannot change the size of existing extents. You cannot change the size of existing extents without unloading all of the data. To change the size of existing extents, you must unload all the data, drop the table, modify the first-extent and next-extent sizes in the CREATE TABLE definition in the database schema, re-create the database, and reload the data. For information about how to optimize extent sizes, see your IBM Informix Administrator's Guide.

LOCK MODE Clause

Use the LOCK MODE keywords to change the locking granularity of a table. LOCK MODE Clause:
LOCK MODE ( PAGE ROW (1) TABLE )

Notes: 1 Extended Parallel Server only

The following table describes the locking-granularity options available. Granularity PAGE Effect Obtains and releases one lock on a whole page of rows This is the default locking granularity. Page-level locking is especially useful when you know that the rows are grouped into pages in the same order that you are using to process all the rows. For example, if you are processing the contents of a table in the same order as its cluster index, page locking is especially appropriate. ROW Obtains and releases one lock per row Row-level locking provides the highest level of concurrency. If you are using many rows at one time, the lock-management overhead can become significant. You can also exceed the maximum number of locks available, depending on the configuration of your database server. TABLE (XPS only) Places a lock on the entire table This type of lock reduces update concurrency in comparison to row and page locks. A table lock reduces the lock-management overhead for a table. Multiple read-only transactions can still access the table.

Precedence and Default Behavior

The LOCK MODE setting in an ALTER TABLE statement takes precedence over the settings of the IFX_DEF_TABLE_LOCKMODE environment variable and the DEF_TABLE_LOCKMODE configuration parameter. For information about the IFX_DEF_TABLE_LOCKMODE environment variable, refer to the IBM Informix

2-62

IBM Informix Guide to SQL: Syntax

ALTER TABLE
Guide to SQL: Reference. For information about the DEF_TABLE_LOCKMODE configuration parameter, refer to the IBM Informix Dynamic Server Administrator's Reference.

Logging TYPE Options

Use the Logging TYPE options to specify that the table have particular characteristics that can improve various bulk operations on it. Logging TYPE Options:
TYPE ( STANDARD RAW (1) OPERATIONAL STATIC )

Notes: 1 Extended Parallel Server only

Here STANDARD, the default option of the CREATE TABLE statement, is used for online transaction processing (OLTP) databases. The OPERATIONAL and STATIC options are used primarily to improve performance in data warehousing databases. A table can have any of the following logging characteristics. Option STANDARD Effect Logging table that allows rollback, recovery, and restoration from archives. This is the default. Use this type for recovery and constraints functionality on OLTP databases. Nonlogging table that cannot have indexes or referential constraints but can be updated. Use this type for quickly loading data. In XPS, raw tables take advantage of light appends and avoid the overhead of logging, checking constraints, and building indexes. Logging table that uses light appends and cannot be restored from archive. Use this type on tables that are refreshed frequently. Light appends allow the quick addition of many rows. Nonlogging table that can contain index and referential constraints but cannot be updated. Use this type for read-only operations because there is no logging or locking overhead.

RAW

OPERATIONAL (XPS only)

STATIC (XPS only)

Warning: Use raw tables for fast loading of data. It is recommended that you alter the logging type to STANDARD and perform a level-0 backup before you use the table in a transaction or modify the data within the table. If you must use a raw table within a transaction, either set the isolation level to Repeatable Read or lock the table in exclusive mode to prevent concurrency problems.

Chapter 2. SQL Statements

2-63

ALTER TABLE
The Logging TYPE option can convert a non-logging table, such as a RAW table, to a STANDARD table that supports transaction logging. If you use this feature, you should be aware that the database server does not check to see whether a level 0 archive has been performed on the table. Operations on a RAW table are not logged and are not recoverable, so RAW tables are always at risk. When the database server converts a table that is not logged to a STANDARD table type, it is your responsibility to perform a level-0 backup before using the table in a transaction or modifying data in the table. Failure to do this might lead to recovery problems in the event of a server crash. For more information on these logging types of tables, refer to your IBM Informix Administrator's Guide. The Logging TYPE options have the following restrictions: v You must perform a level-0 archive before the logging type of a table can be altered to STANDARD from any other logging type. v If you want to change the logging type of a table to RAW, you must drop all indexes on the table before you do so. v If you have triggers defined on the table, you cannot change the logging type to RAW or STATIC. These nonlogging table types do not support triggers. v The table cannot be a SCRATCH or TEMP table, and you cannot change any of these types of tables to a SCRATCH or TEMP table. v The table cannot have a dependent generalized-key (GK) index.

ADD TYPE Clause (IDS)

Use the ADD TYPE clause to convert a table that is not based on a named ROW data type into a typed table. This clause is an extension to the ANSI/ISO standard for SQL. ADD TYPE Clause:
ADD TYPE row_type

Element row_type

Description Identifier of an existing named ROW data type for the table

Restrictions

Syntax

The row_type fields must match the column Identifier, p. 5-22 data type in their order and number

When you use the ADD TYPE clause, you assign the specified named ROW data type to a table whose columns match the fields of that data type. In addition to the requirements common to all ALTER TABLE operations (namely DBA privilege on the database, Alter privilege on the table, and ownership of the table), all of the following must be also true when you use the ADD TYPE clause to convert an untyped table to the specified named ROW data type: v The named ROW data type is already registered in the database. v You hold the Usage privilege on the named ROW data type. v There must be a 1-to-1 correspondence between the ordered set of column data types of the untyped table and the ordered set of field data types of the named ROW data type. v The table cannot be a fragmented table that has rowid values.

2-64

IBM Informix Guide to SQL: Syntax

ALTER TABLE
You cannot combine the ADD TYPE clause with any clause that changes the schema of the table. No other ADD, DROP, or MODIFY clause is valid in the same ALTER TABLE statement that has the ADD TYPE clause. The ADD TYPE clause does not allow you to change column data types. (To change the data type of a column, use the MODIFY clause.)

Using the MODIFY Clause

ALTER TABLE supports only the following options for tables of ROW data types. Typed-Table Options:
,

(1) ADD CONSTRAINT Clause (2) DROP CONSTRAINT Clause (3) MODIFY NEXT SIZE Clause (3) (5) LOCK MODE Clause

(4)

Notes: 1 2 3 4 5 See page 2-59 See page 2-61 Use path no more than once See page 2-61 See page 2-62

Altering Subtables and Supertables

Two considerations apply to typed tables that are part of inheritance hierarchies: v For subtables, ADD CONSTRAINT and DROP CONSTRAINT are not valid on inherited constraints. v For supertables, ADD CONSTRAINT and DROP CONSTRAINT propagate to all subtables.

Related Infrmation
Related statements: CREATE TABLE, DROP TABLE, LOCK TABLE, and SET Database Object Mode. For discussions of data-integrity constraints and the ON DELETE CASCADE option, see the IBM Informix Guide to SQL: Tutorial. For a discussion of database and table creation, see the IBM Informix Database Design and Implementation Guide. For information on how to maximize performance when you make table modifications, see your IBM Informix Performance Guide.

Chapter 2. SQL Statements

2-65

BEGIN WORK
Use the BEGIN WORK statement to start a transaction (a series of database operations that the COMMIT WORK or ROLLBACK WORK statement terminates). This statement is an extension to the ANSI/ISO standard for SQL.

Syntax
WORK BEGIN (1) WITHOUT REPLICATION

Notes: 1 Dynamic Server and ESQL/C only

Usage
The BEGIN WORK statement is valid only in a database that supports transaction logging. Each row that an UPDATE, DELETE, or INSERT statement affects during a transaction is locked and remains locked throughout the transaction. A transaction that contains many such statements or that contains statements that affect many rows can exceed the limits that your operating system or the database server configuration imposes on the number of simultaneous locks. If no other user is accessing the table, you can avoid locking limits and reduce locking overhead by locking the table with the LOCK TABLE statement after you begin the transaction. Like other locks, this table lock is released when the transaction terminates. The example of a transaction on Example of BEGIN WORK on page 2-67 includes a LOCK TABLE statement. Important: Issue the BEGIN WORK statement only if a transaction is not in progress. If you issue a BEGIN WORK statement while you are in a transaction, the database server returns an error. The WORK keyword is optional. The following two statements are equivalent:
BEGIN; BEGIN WORK;

In ESQL/C, if you use the BEGIN WORK statement within a UDR called by a WHENEVER statement, specify WHENEVER SQLERROR CONTINUE and WHENEVER SQLWARNING CONTINUE before the ROLLBACK WORK statement. These statements prevent the program from looping endlessly if the ROLLBACK WORK statement encounters an error or a warning.

BEGIN WORK and ANSI-Compliant Databases

In an ANSI-compliant database, you do not need the BEGIN WORK statement because transactions are implicit; every SQL statement occurs within a transaction. The database server generates a warning when you use a BEGIN WORK statement immediately after any of the following statements: v DATABASE v COMMIT WORK

2-66

IBM Informix Guide to SQL: Syntax

BEGIN WORK
v CREATE DATABASE v ROLLBACK WORK The database server returns an error when you use a BEGIN WORK statement after any other statement in an ANSI-compliant database.

BEGIN WORK WITHOUT REPLICATION (IDS, ESQL/C)

When you use Enterprise Replication for data replication, you can use the BEGIN WORK WITHOUT REPLICATION statement to start a transaction that does not replicate to other database servers. You cannot execute BEGIN WORK WITHOUT REPLICATION as a stand-alone embedded statement in an ESQL/C application. Instead you must execute this statement indirectly. You can use either of the following methods: v You can use a combination of the PREPARE and EXECUTE statements to prepare and execute the BEGIN WORK WITHOUT REPLICATION statement. v You can use the EXECUTE IMMEDIATE statement to prepare and execute BEGIN WORK WITHOUT REPLICATION in a single step. You cannot use the DECLARE cursor CURSOR WITH HOLD statement with the BEGIN WORK WITHOUT REPLICATION statement. For more information about data replication, see the IBM Informix Dynamic Server Enterprise Replication Guide.

Example of BEGIN WORK

The following code fragment shows how you might place statements within a transaction. The transaction is made up of the statements that occur between the BEGIN WORK and COMMIT WORK statements. The transaction locks the stock table (LOCK TABLE), updates rows in the stock table (UPDATE), deletes rows from the stock table (DELETE), and inserts a row into the manufact table (INSERT).
BEGIN WORK; LOCK TABLE stock; UPDATE stock SET unit_price = unit_price * 1.10 WHERE manu_code = KAR; DELETE FROM stock WHERE description = baseball bat; INSERT INTO manufact (manu_code, manu_name, lead_time) VALUES (LYM, LYMAN, 14); COMMIT WORK;

The database server must perform this sequence of operations either completely or not at all. When you include all of these operations within a single transaction, the database server guarantees that all the statements are completely and perfectly committed to disk, or else the database is restored to the same state that it was in before the transaction began.

Related Information
Related statements: COMMIT WORK and SAVE EXTERNAL DIRECTIVES For discussions of transactions and locking, see the IBM Informix Guide to SQL: Tutorial.

Chapter 2. SQL Statements

2-67

CLOSE
Use the CLOSE statement when you no longer need to refer to the rows that a select or function cursor retrieved, or to flush and close an insert cursor. Use this statement with ESQL/C.

Syntax
CLOSE cursor_id (1) cursor_id_var

Notes: 1
Element cursor_id cursor_id_var Description Name of cursor to be closed Host variable that contains the value of cursor_id

Informix extension
Restrictions Must have been declared Must be of a character data type Syntax Identifier on page 5-22 Must conform to language-specific rules for names.

Usage
Closing a cursor makes the cursor unusable for any statements except OPEN or FREE and releases resources that the database server had allocated to the cursor. A CLOSE statement treats a cursor that is associated with an INSERT statement differently from one that is associated with a SELECT or EXECUTE FUNCTION (or EXECUTE PROCEDURE) statement. In a database that is not ANSI-compliant, you can close a cursor that has not been opened or that has already been closed. No action is taken in these cases. In an ANSI-compliant database, the database server returns an error if you close a cursor that was not open.

Closing a Select or Function Cursor

When a cursor identifier is associated with a SELECT or EXECUTE FUNCTION (or EXECUTE PROCEDURE) statement, closing the cursor terminates the SELECT or EXECUTE FUNCTION (or EXECUTE PROCEDURE) statement. The database server releases all resources that it might have allocated to the active set of rows, for example, a temporary table that it used to hold an ordered set. The database server also releases any locks that it might have held on rows that were selected through the cursor. If a transaction contains the CLOSE statement, the database server does not release the locks until you execute COMMIT WORK or ROLLBACK WORK. After you close a select or function cursor, you cannot execute a FETCH statement that names that cursor until you have reopened it.

Closing an Insert Cursor

When a cursor identifier is associated with an INSERT statement, the CLOSE statement writes any remaining buffered rows into the database. The number of

2-68

IBM Informix Guide to SQL: Syntax

CLOSE
rows that were successfully inserted into the database is returned in the third element of the sqlerrd array, sqlca.sqlerrd[2], in the sqlca structure. For information on how to use SQLERRD to count the total number of rows that were inserted, see Error Checking on page 2-447. The SQLCODE field of the sqlca structure, sqlca.sqlcode, indicates the result of the CLOSE statement for an insert cursor. If all buffered rows are successfully inserted, SQLCODE is set to zero. If an error is encountered, the sqlca.sqlcode field in the SQLCODE is set to a negative error message number. When SQLCODE is zero, the row buffer space is released, and the cursor is closed; that is, you cannot execute a PUT or FLUSH statement that names the cursor until you reopen it. Tip: When you encounter an SQLCODE error, a corresponding SQLSTATE error value also exists. For information about how to get the message text, check the GET DIAGNOSTICS statement. If the insert is not successful, the number of successfully inserted rows is stored in sqlerrd. Any buffered rows that follow the last successfully inserted row are discarded. Because the insert fails, the CLOSE statement fails also, and the cursor is not closed. For example, a CLOSE statement can fail if insufficient disk space prevents some of the rows from being inserted. In this case, a second CLOSE statement can be successful because no buffered rows exist. An OPEN statement can also be successful because the OPEN statement performs an implicit close.

Closing a Collection Cursor (IDS)

You can declare both select and insert cursors on collection variables. Such cursors are called collection cursors. Use the CLOSE statement to deallocate resources that have been allocated for the collection cursor. For more information on how to use a collection cursor, see Fetching from a Collection Cursor (IDS) on page 2-350 and Inserting into a Collection Cursor (IDS) on page 2-445.

Using End of Transaction to Close a Cursor

The COMMIT WORK and ROLLBACK WORK statements close all cursors except those that are declared with a hold. It is better to close all cursors explicitly, however. For select or function cursors, this action simply makes the intent of the program clear. It also helps to avoid a logic error if the WITH HOLD clause is later added to the declaration of a cursor. For an insert cursor, it is important to use the CLOSE statement explicitly so that you can test the error code. Following the COMMIT WORK statement, SQLCODE reflects the result of the COMMIT statement, not the result of closing cursors. If you use a COMMIT WORK statement without first using a CLOSE statement, and if an error occurs while the last buffered rows are being written to the database, the transaction is still committed. For how to use insert cursors and the WITH HOLD clause, see DECLARE on page 2-260. In an ANSI-compliant database, a cursor cannot be closed implicitly. You must issue a CLOSE statement.

Chapter 2. SQL Statements

2-69

CLOSE

Related Information
Related statements: DECLARE, FETCH, FLUSH, FREE, OPEN, PUT, and SET AUTOFREE For an introductory discussion of cursors, see the IBM Informix Guide to SQL: Tutorial. For a more advanced discussion of cursors, see the IBM Informix ESQL/C Programmer's Manual.

2-70

IBM Informix Guide to SQL: Syntax

CLOSE DATABASE
Use the CLOSE DATABASE statement to close the current database. This statement is an extension to the ANSI/ISO standard for SQL.

Syntax
CLOSE DATABASE

Usage
When you issue a CLOSE DATABASE statement, you can issue only the following SQL statements immediately after it: v v v v v CONNECT CREATE DATABASE DATABASE DROP DATABASE DISCONNECT (The DISCONNECT statement is valid here only if an explicit connection existed before CLOSE DATABASE was executed.)

Issue the CLOSE DATABASE statement before you drop the current database. If your current database supports transaction logging, and if you have started a transaction, you must issue a COMMIT WORK or ROLLBACK WORK statement before you can use the CLOSE DATABASE statement. The following example shows how to use the CLOSE DATABASE statement to drop the current database:
DATABASE stores_demo . . . CLOSE DATABASE DROP DATABASE stores_demo

In ESQL/C, the CLOSE DATABASE statement cannot appear in a multistatement PREPARE operation. If a previous CONNECT statement has established an explicit connection to a database, and that connection is still your current connection, you cannot use the CLOSE DATABASE statement to close that explicit connection. (You can use the DISCONNECT statement to close the explicit connection.) If you use the CLOSE DATABASE statement within a UDR called by a WHENEVER statement, specify WHENEVER SQLERROR CONTINUE and WHENEVER SQLWARNING CONTINUE before the ROLLBACK WORK statement. This action prevents the program from looping if the ROLLBACK WORK statement encounters an error or a warning. When you issue the CLOSE DATABASE statement, any declared cursors are no longer valid. You must re-declare any cursors that you want to use.

Chapter 2. SQL Statements

2-71

CLOSE DATABASE
In an ANSI-compliant database, if no error is encountered while you exit from DBAccess in non-interactive mode without issuing the CLOSE DATABASE, COMMIT WORK, or DISCONNECT statement, the database server automatically commits any open transaction.

Related Information
Related statements: CONNECT, CREATE DATABASE, DATABASE, DISCONNECT, and DROP DATABASE

2-72

IBM Informix Guide to SQL: Syntax

COMMIT WORK
Use the COMMIT WORK statement to commit all modifications made to the database from the beginning of a transaction.

Syntax
COMMIT WORK

Usage
The COMMIT WORK statement informs the database server that you reached the end of a series of statements that must succeed as a single unit. The database server takes the required steps to make sure that all modifications that the transaction makes are completed correctly and saved to disk. Use COMMIT WORK only at the end of a multistatement operation in a database with transaction logging, when you are sure that you want to keep all changes made to the database from the beginning of a transaction. The COMMIT WORK statement releases all row and table locks. The WORK keyword is optional in a COMMIT WORK statement. The following two statements are equivalent:
COMMIT; COMMIT WORK;

The following example shows a transaction bounded by BEGIN WORK and COMMIT WORK statements.
BEGIN WORK; DELETE FROM call_type WHERE call_code = O; INSERT INTO call_type VALUES (S, order status); COMMIT WORK;

In this example, the user first deletes the row from the call_type table where the value of the call_code column is O. The user then inserts a new row in the call_type table where the value of the call_code column is S. The database server guarantees that both operations succeed or else neither succeeds. In ESQL/C, the COMMIT WORK statement closes all open cursors except those that were declared using the WITH HOLD option.

Issuing COMMIT WORK in a Database That Is Not ANSI Compliant

In a database that is not ANSI compliant, but that supports transaction logging, if you initiate a transaction with a BEGIN WORK statement, you must issue a COMMIT WORK statement at the end of the transaction. If you fail to issue a COMMIT WORK statement in this case, the database server rolls back any modifications that the transaction made to the database. If you do not issue a BEGIN WORK statement, however, each statement executes within its own transaction. These single-statement transactions do not require either a BEGIN WORK statement or a COMMIT WORK statement.

Chapter 2. SQL Statements

2-73

COMMIT WORK
Explicit DB-Access Transactions: When you use DB-Access in interactive mode with a database that is not ANSI-compliant but that supports transaction logging, if you select the Commit menu but do not issue the COMMIT WORK statement after a transaction has been started by the BEGIN WORK statement, DB-Access automatically commits the data, but issues the following warning:
Warning: Data commit is a result of unhandled exception in TXN PROC/FUNC

The purpose of this warning is to remind you to issue COMMIT WORK explicitly to end a transaction that BEGIN WORK initiated. In non-interactive mode, however, DB-Access rolls back the current transaction if you end a session without issuing the COMMIT WORK statement.

Issuing COMMIT WORK in an ANSI-Compliant Database

In an ANSI-compliant database, you do not need BEGIN WORK to mark the beginning of a transaction. You only need to mark the end of each transaction, because a transaction is always in effect. A new transaction starts automatically after each COMMIT WORK or ROLLBACK WORK statement. You must, however, issue an explicit COMMIT WORK statement to mark the end of each transaction. If you fail to do so, the database server rolls back any modifications that the transaction made to the database. In an ANSI-compliant database, however, if no error is encountered while you exit from DBAccess in non-interactive mode without issuing the CLOSE DATABASE, COMMIT WORK, or DISCONNECT statement, the database server automatically commits any open transaction.

Related Information
Related statements: BEGIN WORK, SAVE EXTERNAL DIRECTIVES, and DECLARE For a discussion of concepts related to transactions, see the IBM Informix Guide to SQL: Tutorial.

2-74

IBM Informix Guide to SQL: Syntax

CONNECT
Use the CONNECT statement to connect to a database environment. This statement is an extension to the ANSI/ISO standard for SQL.

Syntax
CONNECT TO

(1) Database Environment (2) AS DEFAULT connection connection_var (3) USER Clause (4)

(2) WITH CONCURRENT TRANSACTION

Notes: 1 2 3 4 See Database Environment on page 2-78 ESQL/C only ESQL/C and DB-Access only See USER Clause on page 2-80
Description Restrictions Syntax Quoted String on page 4-142 Language specific

Element connection connection_var

Case-sensitive name that you declare Must be unique among here for a connection connection names Host variable that stores the name of Must be a fixed-length connection character data type

Usage
The CONNECT statement connects an application to a database environment, which can be a database, a database server, or a database and a database server. If the application successfully connects to the specified database environment, the connection becomes the current connection for the application. SQL statements fail if the application has no current connection to a database server. If you specify a database name, the database server opens that database. You cannot include CONNECT within a PREPARE statement. An application can connect to several database environments at the same time, and it can establish multiple connections to the same database environment, provided each connection has a unique connection name. On UNIX, the only restriction on establishing multiple connections to the same database environment is that an application can establish only one connection to each local server that uses the shared-memory connection mechanism. To find out whether a local server uses the shared-memory connection mechanism or the local-loopback connection mechanism, examine the $INFORMIXDIR/etc/sqlhosts file. For more information on the sqlhosts file, refer to your IBM Informix Administrator's Guide.
Chapter 2. SQL Statements

2-75

CONNECT
On Windows, the local connection mechanism is named pipes. Multiple connections to the local server from one client can exist. Only one connection is current at any time; other connections are dormant. The application cannot interact with a database through a dormant connection. When an application establishes a new connection, that connection becomes current, and the previous current connection becomes dormant. You can make a dormant connection current with the SET CONNECTION statement. See also SET CONNECTION on page 2-534.

Privileges for Executing the CONNECT Statement

The current user, or PUBLIC, must have the Connect privilege on the database specified in the CONNECT statement. The user who executes the CONNECT statement cannot have the same user name as an existing role in the database. For information on how to use the USER clause to specify an alternate user name when the CONNECT statement connects to a database server on a remote host, see USER Clause on page 2-80.

Connection Identifiers
The optional connection name is a unique identifier that an application can use to refer to a connection in subsequent SET CONNECTION and DISCONNECT statements. If the application does not provide a connection name (or a connection-host variable), it can refer to the connection using the database environment. If the application makes more than one connection to the same database environment, however, each connection must have a unique name. After you associate a connection name with a connection, you can refer to the connection using only that connection name.

Connection Context
Each connection encompasses a set of information that is called the connection context. The connection context includes the name of the current user, the information that the database environment associates with this name, and information on the state of the connection (such as whether an active transaction is associated with the connection). The connection context is saved when an application becomes dormant, and this context is restored when the application becomes current again. (For more information, see Making a Dormant Connection the Current Connection on page 2-534.)

DEFAULT Option
Use the DEFAULT option to request a connection to a default database server, called a default connection. The default database server can be local or remote. To designate the default database server, set its name in the INFORMIXSERVER environment variable. This option of CONNECT does not open a database. If you select the DEFAULT option for the CONNECT statement, you must use the DATABASE statement or the CREATE DATABASE statement to open or create a database in the default database environment.

The Implicit Connection with DATABASE Statements

If you do not execute a CONNECT statement in your application, the first SQL statement must be one of the following database statements (or a single statement PREPARE for one of the following statements): v DATABASE v CREATE DATABASE

2-76

IBM Informix Guide to SQL: Syntax

CONNECT
v DROP DATABASE If one of these database statements is the first SQL statement in an application, the statement establishes a connection to a database server, which is known as an implicit connection. If the database statement specifies only a database name, the database server name is obtained from the DBPATH environment variable. This situation is described in Specifying the Database Environment on page 2-79. An application that makes an implicit connection can establish other connections explicitly (using the CONNECT statement) but cannot establish another implicit connection unless the original implicit connection is closed. An application can terminate an implicit connection using the DISCONNECT statement. After you create an explicit connection, you cannot use any database statement to create implicit connections until after you close the explicit connection. After any implicit connection is made, that connection is considered to be the default connection, regardless of whether the database server is the default that the INFORMIXSERVER environment variable specifies. This feature allows the application to refer to the implicit connection if additional explicit connections are made, because the implicit connection has no identifier. For example, if you establish an implicit connection followed by an explicit connection, you can make the implicit connection current by issuing the SET CONNECTION DEFAULT statement. This means, however, that once you establish an implicit connection, you cannot use the CONNECT DEFAULT statement, because the implicit connection is now the default connection. The database statements can always be used to open a database or create a new database on the current database server.

WITH CONCURRENT TRANSACTION Option

The WITH CONCURRENT TRANSACTION clause enables you to switch to a different connection while a transaction is active in the current connection. If the current connection was not established using the WITH CONCURRENT TRANSACTION clause, you cannot switch to a different connection if a transaction is active; the CONNECT or SET CONNECTION statement fails, returning an error, and the transaction in the current connection continues to be active. In this case, the application must commit or roll back the active transaction in the current connection before it switches to a different connection. The WITH CONCURRENT TRANSACTION clause supports the concept of multiple concurrent transactions, where each connection can have its own transaction and the COMMIT WORK and ROLLBACK WORK statements affect only the current connection. The WITH CONCURRENT TRANSACTION clause does not support global transactions in which a single transaction spans databases over multiple connections. The COMMIT WORK and ROLLBACK WORK statements do not act on databases across multiple connections. The following example illustrates how to use the WITH CONCURRRENT TRANSACTION clause:
main() { EXEC SQL connect to a@srv1 as A; EXEC SQL connect to b@srv2 as B with concurrent transaction; EXEC SQL connect to c@srv3 as C with concurrent transaction;

Chapter 2. SQL Statements

2-77

CONNECT
/* Execute SQL statements in connection C , starting a transaction */ EXEC SQL set connection B; -- switch to connection B /* Execute SQL statements starting a transaction in B. Now there are two active transactions, one each in B and C. */ EXEC SQL set connection A; -- switch to connection A /* Execute SQL statements starting a transaction in A. Now there are three active transactions, one each in A, B and C. */ EXEC SQL set connection C; -- ERROR, transaction active in A /* SET CONNECTION C fails (current connection is still A) The transaction in A must be committed or rolled back because connection A was started without the CONCURRENT TRANSACTION clause. */

EXEC SQL commit work; /*

-- commit tx in current connection (A)

Now, there are two active transactions, in B and in C, which must be committed or rolled back separately */ EXEC SQL set connection B; -- switch to connection B EXEC SQL commit work; -- commit tx in current connection (B) EXEC SQL set connection C; -- go back to connection C EXEC SQL commit work; -- commit tx in current connection (C) EXEC SQL disconnect all; }

Warning: When an application uses the WITH CONCURRENT TRANSACTION clause to establish multiple connections to the same database environment, a deadlock condition can occur.

Database Environment
Database Environment:
dbname @dbservername dbname@dbservername (1) db_var

Notes: 1 ESQL/C only

2-78

IBM Informix Guide to SQL: Syntax

CONNECT
Element db_var Description Restrictions Syntax Language specific

Host variable that contains a Must be a fixed-length character data type, valid database environment (in whose contents are in a format from the one of the formats in the syntax diagram syntax diagram) Database to which to connect Must already exist

dbname dbservername

Identifier on page 5-22 Identifier on page 5-22

Name of the database server to Must already exist; blank space is not valid which a connection is made between @ symbol and dbservername. See also Restrictions on dbservername.

If the DELIMIDENT environment variable is set, any quotation ( ) marks in the database environment must be single. If DELIMIDENT is not set, then either single ( ) or double ( ) quotation marks are valid here.

Restrictions on dbservername
If you specify dbservername, it must satisfy the following restrictions. v If the database server that you specify is not online, you receive an error. v On UNIX, the database server that you specify in dbservername must match the name of a database server in the sqlhosts file. v On Windows, dbservername must match the name of a database server in the sqlhosts subkey in the registry. It is recommended that you use the setnet32 utility to update the registry.

Specifying the Database Environment

You can specify a database server and a database, or a database server only, or a database only. How a database is located and opened depends on whether you specify a database server name in the database environment expression. Only Database Server Specified: The @dbservername option establishes a connection to the database server only; it does not open a database. When you use this option, you must subsequently use the DATABASE or CREATE DATABASE statement (or a PREPARE statement for one of these statements and an EXECUTE statement) to open a database. Database Server and Database Specified: If you specify both a database server and a database, your application connects to the database server, which locates and opens the database. Only Database Specified: The dbname option establishes a connection to the default database server or to another database server in the DBPATH environment variable. It also locates and opens the specified database. (The same is true of the db_var option if this specifies only a database name.) If you specify only dbname, its database server is read from the DBPATH environment variable. The database server in the INFORMIXSERVER environment variable is always added before the DBPATH value. On UNIX, set the INFORMIXSERVER and DBPATH environment variables as the following example (for the C shell) shows:
setenv INFORMIXSERVER srvA setenv DBPATH //srvB://srvC

Chapter 2. SQL Statements

2-79

CONNECT
On Windows, choose Start > Programs > Informix > setnet32 from the Task Bar and set the INFORMIXSERVER and DBPATH environment variables:
set INFORMIXSERVER = srvA set DBPATH = //srvA://srvB://srvC

The next example shows the resulting DBPATH that your application uses:
//srvA://srvB://srvC

The application first establishes a connection to the database server that INFORMIXSERVER specifies. The database server uses parameters in the configuration file to locate the database. If the database does not reside on the default database server, or if the default database server is not online, the application connects to the next database server in DBPATH. In the previous example, that database server would be srvB.

USER Clause
The USER clause specifies information that is used to determine whether the application can access the target computer on a remote host. USER Clause:
USER user_id user_id_var USING validation_var

Element user_id user_id_var validation_var

Description Valid login name Host variable that contains user_id

Restrictions See Restrictions on the User Identifier Parameter on page 2-81. Must be a fixed-length character data type; same restrictions as user_id

Syntax Quoted String on page 4-142 Language specific

Host variable that contains a valid Must be a fixed-length character type. See Language specific password for login name in user_id Restrictions on the Validation Variable or user_id_var Parameter on page 2-80.

The USER clause is required when the CONNECT statement connects to the database server on a remote host. Subsequent to the CONNECT statement, all database operations on the remote host use the specified user name. In DB-Access, the USING clause is valid within files executed from DB-Access. In interactive mode, DB-Access prompts you for a password, so the USING keyword and validation_var are not used.

Restrictions on the Validation Variable Parameter

On UNIX, the password stored in validation_var must be a valid password and must exist in the /etc/passwd file. If the application connects to a remote database server, the password must exist in this file on both the local and remote database servers. On Windows, the password stored in validation_var must be valid and must be the password entered in User Manager. If the application connects to a remote database server, the password must exist in the domain of both the client and the server.

2-80

IBM Informix Guide to SQL: Syntax

CONNECT

Restrictions on the User Identifier Parameter

The connection is rejected if any of the following conditions occur: v The specified user lacks the privileges to access the database specified in the database environment. v The specified user lacks the permissions to connect to the remote host. v You supply a USER clause but omit the USING validation_var specification. In compliance with the X/Open standard for the CONNECT statement, the ESQL/C preprocessor supports a CONNECT statement that has a USER clause without the USING validation_var specification. If the validation_var is not present, however, the database server rejects the connection at runtime. On UNIX, the user_id that you specify must be a valid login name and must exist in the /etc/passwd file. If the application connects to a remote server, the login name must exist in this file on both the local and remote database servers. On Windows, the user_id that you specify must be a valid login name and must exist in User Manager. If the application connects to a remote server, the login name must exist in the domain of both the client and the server.

Use of the Default User ID

If you do not supply the USER clause, the default user ID is used to attempt the connection. The default user ID is the login name of the user running the application. In this case, you obtain network permissions with the standard authorization procedures. For example, on UNIX, the default user ID must match a user ID in the /etc/hosts.equiv file. On Windows, you must be a member of the domain, or if the database server is installed locally, you must be a valid user on the computer where it is installed.

Related Information
Related Statements: DISCONNECT, SET CONNECTION, DATABASE, and CREATE DATABASE For more information about sqlhosts, refer to your IBM Informix Administrator's Guide.

Chapter 2. SQL Statements

2-81

CREATE ACCESS_METHOD
Use the CREATE ACCESS_METHOD statement to register a new primary or secondary access method in the sysams system catalog table. Only Dynamic Server supports this statement, which is an extension to the ANSI/ISO standard for SQL.

Syntax
CREATE SECONDARY PRIMARY ACCESS_METHOD access_method

, (1) ( Purpose Options )

Notes: 1
Element access method Description Name declared here for the new access method

See Purpose Options on page 5-46

Restrictions Must be unique among access-method names in the sysams system catalog table Syntax Database Object Name on page 5-17

Usage
The CREATE ACCESS_METHOD statement adds a user-defined access method to a database. To create an access method, you specify purpose functions (or purpose methods), purpose flags, or purpose values as attributes of the access method, and you associate keywords (based on column names in the sysams system catalog table) with UDRs. You must have the DBA or Resource privilege to create an access method. For information on setting purpose options, including a list of all the purpose function keywords, refer to Purpose Options on page 5-46. The PRIMARY keyword specifies a user-defined primary-access method for a virtual table. The SECONDARY keyword specifies creating a user-defined secondary-access method for a virtual index. The SECONDARY keyword (and creating virtual indexes) is not supported in the Java Virtual-Table Interface. The following statement creates a secondary-access method named T_tree:
CREATE SECONDARY ACCESS_METHOD T_tree ( am_getnext = ttree_getnext, . . . am_unique, am_cluster, am_sptype = S );

2-82

IBM Informix Guide to SQL: Syntax

CREATE ACCESS_METHOD
In the preceding example, the am_getnext keyword in the Purpose Options list is associated with the ttree_getnext( ) UDR as the name of a method to scan for the next item that satisfies a query. This example indicates that the T_tree secondary access method supports unique keys and clustering, and resides in an sbspace. Any UDR that the CREATE ACCESS_METHOD statement associates with the keyword for a purpose function task, such as the association of ttree_getnext( ) with am_getnext in the preceding example, must already have been registered in the database by the CREATE FUNCTION statement (or by a functionally equivalent statement, such as CREATE PROCEDURE FROM). The following statement creates a primary-access method named am_tabprops that resides in an extspace.
CREATE PRIMARY ACCESS_METHOD am_tabprops ( am_open = FS_open, am_close = FS_close, am_beginscan = FS_beginScan, am_create = FS_create, am_scancost = FS_scanCost, am_endscan = FS_endScan, am_getnext = FS_getNext, am_getbyid = FS_getById, am_drop = FS_drop, am_truncate = FS_truncate, am_rowids, am_sptype = x );

Related Information
Related statements: ALTER ACCESS_METHOD and DROP ACCESS_METHOD For the schema of the sysams table, see the IBM Informix Guide to SQL: Reference. For information about how to set purpose-option specifications, see Purpose Options on page 5-46. For more information on primary-access methods, see the IBM Informix Virtual-Table Interface Programmer's Guide. For more information on secondary-access methods, see the IBM Informix Virtual-Index Interface Programmer's Guide and the IBM Informix User-Defined Routines and Data Types Developer's Guide. For a discussion of privileges, see the GRANT or REVOKE statements or the IBM Informix Database Design and Implementation Guide.

Chapter 2. SQL Statements

2-83

CREATE AGGREGATE
Use the CREATE AGGREGATE statement to create a new aggregate function and register it in the sysaggregates system catalog table. User-defined aggregates extend the functionality of the database server by performing aggregate computations that the user implements. Only Dynamic Server supports this statement, which is an extension to the ANSI/ISO standard for SQL.

Syntax
CREATE AGGREGATE (1) Owner Name . aggregate

, WITH ( Modifiers )

Modifiers:
INIT=init_func ITER=iter_func COMBINE=comb_func FINAL=final_func HANDLESNULLS

Notes: 1
Element aggregate comb_func Description Name of the new aggregate

See Owner Name on page 5-43

Restrictions Must be unique among names of built-in aggregates and UDRs Syntax Identifier on page 5-22 Database Object Name on page 5-17 Database Object Name on page 5-17 Database Object Name on page 5-17 Database Object Name on page 5-17

Function that merges one partial Must specify the combined function result into the other and returns the both for parallel queries and for updated partial result sequential queries Function that converts a partial result into the result type Function that initializes the data structures required for the aggregate computation If this is omitted, then the returned value is the final result of iter_func Must be able to handle NULL arguments

final_func init_func

iter_func

Function that merges a single value Must specify an iterator function. If with a partial result and returns init_func is omitted, iter_func must be updated partial result able to handle NULL arguments

Usage
You can specify the INIT, ITER, COMBINE, FINAL, and HANDLESNULLS modifiers in any order.

2-84

IBM Informix Guide to SQL: Syntax

CREATE AGGREGATE
Important: You must specify the ITER and COMBINE modifiers in a CREATE AGGREGATE statement. You do not have to specify the INIT, FINAL, and HANDLESNULLS modifiers in a CREATE AGGREGATE statement. The ITER, COMBINE, FINAL, and INIT modifiers specify the support functions for a user-defined aggregate. These support functions do not have to exist at the time you create the user-defined aggregate. If you omit the HANDLESNULLS modifier, rows with NULL aggregate argument values do not contribute to the aggregate computation. If you include the HANDLESNULLS modifier, you must define all the support functions to handle NULL values as well.

Extending the Functionality of Aggregates

Dynamic Server provides two ways to extend the functionality of aggregates. Use the CREATE AGGREGATE statement only for the second of the two cases. v Extensions of built-in aggregates A built-in aggregate is an aggregate that the database server provides, such as COUNT, SUM, or AVG. These only support built-in data types. To extend a built-in aggregate so that it supports a user-defined data type (UDT), you must create user-defined routines that overload the binary operators for that aggregate. For further information on extending built-in aggregates, see the IBM Informix User-Defined Routines and Data Types Developer's Guide. v Creation of user-defined aggregates A user-defined aggregate is an aggregate that you define to perform an aggregate computation that the database server does not provide. You can use user-defined aggregates with built-in data types, extended data types, or both. To create a user-defined aggregate, use the CREATE AGGREGATE statement. In this statement, you name the new aggregate and specify the support functions that compute the aggregate result. These support functions perform initialization, sequential aggregation, combination of results, and type conversion. Example of Creating a User-Defined Aggregate: The following example defines a user-defined aggregate named average:
CREATE AGGREGATE average WITH ( INIT = average_init, ITER = average_iter, COMBINE = average_combine, FINAL = average_final )

Before you use the average aggregate in a query, you must also use CREATE FUNCTION statements to create the support functions specified in the CREATE AGGREGATE statement. The following table gives an example of the task that each support function might perform for average.

Chapter 2. SQL Statements

2-85

CREATE AGGREGATE
Support Function average_init average_iter

Keyword INIT ITER

Effect Allocates and initializes an extended data type storing the current sum and the current row count For each row, adds the value of the expression to the current sum and increments the current row count by one Adds the current sum and the current row count of one partial result to the other and returns the updated result Returns the ratio of the current sum to the current row count and converts this ratio to the result type

COMBINE

average_combine

FINAL

average_final

Parallel Execution
The database server can break up an aggregate computation into several pieces and compute them in parallel. The database server uses the INIT and ITER support functions to compute each piece sequentially. Then the database server uses the COMBINE function to combine the partial results from all the pieces into a single result value. Whether an aggregate is parallel is an optimization decision that is transparent to the user.

Related Information
Related statements: CREATE FUNCTION and DROP AGGREGATE For information about how to invoke a user-defined aggregate, see User-Defined Aggregates on page 4-117 in the Expression segment. For a description of the sysaggregates system catalog table that stores data about user-defined aggregates, see the IBM Informix Guide to SQL: Reference. For a discussion of user-defined aggregates, see IBM Informix User-Defined Routines and Data Types Developer's Guide.

2-86

IBM Informix Guide to SQL: Syntax

CREATE CAST
Use the CREATE CAST statement to register a cast that converts data from one data type to another. Only Dynamic Server supports this statement, which is an extension to the ANSI/ISO standard for SQL.

Syntax
CREATE EXPLICIT IMPLICIT CAST ) WITH function

( source_type AS target_type

Element function

Description UDR that you register to implement the cast Data type to be converted

Restrictions See WITH Clause on page 2-89.

Syntax Database Object Name on page 5-17 Data Type on page 4-18

source_type

Must exist in the database at the time the cast is registered. See also Source and Target Data Types on page 2-87.

target_type

Data type that results from the conversion

The same restrictions that apply for the source_type (as Data Type on listed above) also apply for the target_type page 4-18

Usage
A cast is a mechanism that the database server uses to convert one data type to another. The database server uses casts to perform the following tasks: v To compare two values in the WHERE clause of a SELECT, UPDATE, or DELETE statement v To pass values as arguments to user-defined routines v To return values from user-defined routines To create a cast, you must have the necessary privileges on both the source data type and the target data type. All users have access privileges to use the built-in data types. To create a cast to or from an OPAQUE, DISTINCT, or named ROW data type, however, requires the Usage privilege on that data type. The CREATE CAST statement registers a cast in the syscasts system catalog table. For more information on syscasts, see the chapter on system catalog tables in the IBM Informix Guide to SQL: Reference.

Source and Target Data Types

The CREATE CAST statement defines a cast that converts a source type to a target type. Both the source and target data types must exist in the database when you execute the CREATE CAST statement to register the cast. The source and the target data types have the following restrictions: v Either the source or the target type, but not both, can be a built-in data type. v Neither the source nor the target type can be a DISTINCT type of the other. v Neither the source nor the target types can be a COLLECTION data type.
Chapter 2. SQL Statements

2-87

CREATE CAST

Explicit and Implicit Casts

To process queries with multiple data types often requires casts that convert data from one data type to another. You can use the CREATE CAST statement to create the following kinds of casts: v Use the CREATE EXPLICIT CAST statement to define an explicit cast. v Use the CREATE IMPLICIT CAST statement to define an implicit cast. Explicit Casts: An explicit cast is a cast that you must specifically invoke, with either the CAST AS keywords or with the cast operator ( :: ). The database server does not automatically invoke an explicit cast to resolve data type conversions. The EXPLICIT keyword is optional; by default, the CREATE CAST statement creates an explicit cast. The following CREATE CAST statement defines an explicit cast from the rate_of_return opaque data type to the percent distinct data type:
CREATE EXPLICIT CAST (rate_of_return AS percent WITH rate_to_prcnt)

The following SELECT statement explicitly invokes this explicit cast in its WHERE clause to compare the bond_rate column (of type rate_of_return) to the initial_APR column (of type percent):
SELECT bond_rate FROM bond WHERE bond_rate::percent > initial_APR

Implicit Casts: The database server invokes built-in casts to convert from one built-in data type to another built-in type that is not directly substitutable. For example, the database server performs conversion of a character type such as CHAR to a numeric type such as INTEGER through a built-in cast. An implicit cast is a cast that the database server can invoke automatically when it encounters data types that cannot be compared with built-in casts. This type of cast enables the database server to automatically handle conversions between other data types. To define an implicit cast, specify the IMPLICIT keyword in the CREATE CAST statement. For example, the following CREATE CAST statement specifies that the database server should automatically use the prcnt_to_char( ) function to convert from the CHAR data type to a distinct data type, percent:
CREATE IMPLICIT CAST (CHAR AS percent WITH char_to_prcnt)

This cast only supports automatic conversion from the CHAR data type to percent. For the database server to convert from percent to CHAR, you also need to define another implicit cast, as follows:
CREATE IMPLICIT CAST (percent AS CHAR WITH prcnt_to_char)

The database server automatically invokes the char_to_prcnt( ) function to evaluate the WHERE clause of the following SELECT statement:
SELECT commission FROM sales_rep WHERE commission > "25%"

Users can also invoke implicit casts explicitly. For more information on how to explicitly invoke a cast function, see Explicit Casts on page 2-88. When a built-in cast does not exist for conversion between data types, you can create user-defined casts to make the necessary conversion.

2-88

IBM Informix Guide to SQL: Syntax

CREATE CAST

WITH Clause
The WITH clause of the CREATE CAST statement specifies the name of the user-defined function to invoke to perform the cast. This function is called the cast function. You must specify a function name unless the source data type and the target data type have identical representations. Two data types have identical representations when the following conditions are met: v Both data types have the same length and alignment. v Both data types are passed by reference or both are passed by value. The cast function must be registered in the same database as the cast at the time the cast is invoked, but need not exist when the cast is created. The CREATE CAST statement does not check privileges on the specified function name, or even verify that the cast function exists. Each time a user invokes the cast explicitly or implicitly, the database server verifies that the user has the Execute privilege on the cast function.

Related Information
Related statements: CREATE FUNCTION, CREATE DISTINCT TYPE, CREATE OPAQUE TYPE, CREATE ROW TYPE, and DROP CAST For more information about data types, casting, and conversion, see the Data Types segment in this manual and the IBM Informix Guide to SQL: Reference. For examples that show how to create and use casts, see the IBM Informix Database Design and Implementation Guide.

Chapter 2. SQL Statements

2-89

CREATE DATABASE
Use the CREATE DATABASE statement to create a new database. This statement is an extension to the ANSI/ISO standard for SQL.

Syntax
CREATE DATABASE database IN dbspace

WITH BUFFERED LOG MODE ANSI

LOG

Element database dbspace

Description Name that you declare here for the new database that you are creating The dbspace to store the data for this database; default is the root dbspace

Restrictions Must be unique among names of databases of the database server Must already exist

Syntax Database Name on page 5-15 Identifier on page 5-22

Usage
This statement is an extension to ANSI-standard syntax. (The ANSI/ISO standard for the SQL language does not specify any syntax for construction of a database, the process by which a database comes into existence.) The database that CREATE DATABASE specifies becomes the current database. The database name that you declare must be unique within the database server environment in which you are working. The database server creates the system catalog tables that describe the structure of the new database. When you create a database, you alone can access it. It remains inaccessible to other users until you, as DBA, grant database privileges. For information on how to grant database privileges, see GRANT on page 2-371. If a previous CONNECT statement has established an explicit connection to a database, and that connection is still your current connection, you cannot use the CREATE DATABASE statement (nor any SQL statement that creates an implicit connection) until after you use DISCONNECT to close the explicit connection. In ESQL/C, the CREATE DATABASE statement cannot appear in a multistatement PREPARE operation. If you do not specify the dbspace, the database server creates the system catalog tables in the root dbspace. The following statement creates the vehicles database in the root dbspace:
CREATE DATABASE vehicles

The following statement creates the vehicles database in the research dbspace:
CREATE DATABASE vehicles IN research

2-90

IBM Informix Guide to SQL: Syntax

CREATE DATABASE
In Extended Parallel Server, you can create a database in the dbspace of the primary coserver (coserver 1) only.

Logging Options
The logging options of the CREATE DATABASE statement determine the type of logging that is done for the database. In the event of a failure, the database server uses the log to re-create all committed transactions in your database. If you do not specify the WITH LOG option, you cannot use transactions nor the statements that support transaction logging (BEGIN WORK, COMMIT WORK, ROLLBACK WORK, SET IMPLICIT TRANSACTION, SET LOG, and SET ISOLATION). If you are using Extended Parallel Server, the CREATE DATABASE statement always creates a database with logging. If you do not specify the WITH LOG option, the unbuffered log type is used by default.

Designating Buffered Logging

The following example creates a database that uses a buffered log:
CREATE DATABASE vehicles WITH BUFFERED LOG

If you use a buffered log, you marginally enhance the performance of logging at the risk of not being able to re-create the last few transactions after a failure. (See the discussion of buffered logging in the IBM Informix Database Design and Implementation Guide.)

ANSI-Compliant Databases
When you use the LOG MODE ANSI option in the CREATE DATABASE statement, the database that you create is an ANSI-compliant database. The following example creates an ANSI-compliant database:
CREATE DATABASE employees WITH LOG MODE ANSI

ANSI-compliant databases are different from databases that are not ANSI compliant in several ways, including the following features: v All statements are automatically contained in transactions. v All databases use unbuffered logging. v Owner naming is enforced. You must qualify with the owner name any table, view, synonym, index, or constraint, unless you are the owner. Unless you enclose the name between quotation marks, alphabetic characters in owner names default to uppercase. v For databases, the default isolation level is REPEATABLE READ. v Default privileges on objects differ from those in databases that are not ANSI compliant. Users do not receive PUBLIC privilege to tables and synonyms by default. Other slight differences exist between databases that are ANSI compliant and those that are not. These differences are noted with the related SQL statement in this manual. For a detailed discussion of the differences between ANSI compliant databases and databases that are not ANSI-compliant, see the IBM Informix Database Design and Implementation Guide. Creating an ANSI-compliant database does not mean that you automatically receive warnings for Informix extensions to the ANSI/ISO standard for SQL syntax

Chapter 2. SQL Statements

2-91

CREATE DATABASE
when you run the database. You must also use the -ansi flag or the DBANSIWARN environment variable to receive such warnings. For additional information about -ansi and DBANSIWARN, see the IBM Informix Guide to SQL: Reference.

Related Information
Related statements: CLOSE DATABASE, CONNECT, DATABASE, and DROP DATABASE

2-92

IBM Informix Guide to SQL: Syntax

CREATE DISTINCT TYPE

Use the CREATE DISTINCT TYPE statement to create a new distinct data type. Only Dynamic Server supports this statement, which is an extension to the ANSI/ISO standard for SQL.

Syntax
CREATE DISTINCT TYPE distinct_type AS source_type

Element distinct_type

Description

Restrictions

Syntax Data Type on page 4-18

Name that you In an ANSI-compliant database, the combination of the declare here for the owner and data type must be unique within the database. In new distinct data type a database that is not ANSI compliant, the name must be unique among names of data types in the database. Name of existing type Must be either a built-in data type or one created with the on which the new CREATE DISTINCT TYPE, CREATE OPAQUE TYPE, or type is based CREATE ROW TYPE statement

source_type

Data Type on page 4-18

Usage
A distinct type is a data type based on a built-in data type or on an existing opaque data type, a named ROW data type, or another distinct data type. Distinct data types are strongly typed. Although the distinct type has the same physical representation of data as its source type, values of the two types cannot be compared without an explicit cast from one type to the other. To create a distinct data type, you must have the Resource privilege on the database. Any user with the Resource privilege can create a distinct type from one of the built-in data types, which user informix owns. Important: You cannot create a distinct type on the SERIAL or SERIAL8 data types. To create a distinct type from an opaque type, from a named ROW type, or from another distinct type, you must be the owner of the data type or have the Usage privilege on the data type. By default, after a distinct type is defined, only the owner of the distinct type and the DBA can use it. The owner of the distinct type, however, can grant to other users the Usage privilege on the distinct type. A distinct type has the same storage structure as its source type. The following statement creates the distinct type birthday, based on the built-in DATE data type:
CREATE DISTINCT TYPE birthday AS DATE

Although Dynamic Server uses the same storage format for the distinct type as it does for its source type, a distinct type and its source type cannot be compared in an operation unless one type is explicitly cast to the other type.

Chapter 2. SQL Statements

2-93

CREATE DISTINCT TYPE

Privileges on Distinct Types

To create a distinct type, you must have the Resource privilege on the database. When you create the distinct type, only you, the owner, have Usage privilege on this type. Use the GRANT or REVOKE statements to grant or revoke Usage privilege to other database users. To find out what privileges exist on a particular type, check the sysxtdtypes system catalog table for the owner name and the sysxtdtypeauth system catalog table for additional data type privileges that might have been granted. For more information on system catalog tables, see the IBM Informix Guide to SQL: Reference. The DBAccess utility can also display privileges on distinct types.

Support Functions and Casts

When you create a distinct type, Dynamic Server automatically defines two explicit casts: v A cast from the distinct type to its source type v A cast from the source type to the distinct type Because the two data types have the same representation (the same length and alignment), no support functions are required to implement the casts. You can create an implicit cast between a distinct type and its source type. To create an implicit cast, use the Table Options clause to specify the format of the external data. You must first drop the default explicit cast, however, between the distinct type and its source type. All support functions and casts that are defined on the source type can be used on the distinct type. Casts and support functions that are defined on the distinct type, however, are not available to the source type. Use the Table Options clause to specify the format of the external data.

Manipulating Distinct Types

When you compare or manipulate data of a distinct type and its source type, you must explicitly cast one type to the other in the following situations: v To insert or update a column of one type with values of the other type v To use a relational operator to add, subtract, multiply, divide, compare, or otherwise manipulate two values, one of the source type and one of the distinct type For example, suppose you create a distinct type, dist_type, that is based on the NUMERIC data type. You then create a table with two columns, one of type dist_type and one of type NUMERIC.
CREATE DISTINCT TYPE dist_type AS NUMERIC; CREATE TABLE t(col1 dist_type, col2 NUMERIC);

To directly compare the distinct type and its source type or assign a value of the source type to a column of the distinct type, you must cast one type to the other, as the following examples show:
INSERT INTO tab (col1) VALUES (3.5::dist_type); SELECT col1, col2 FROM t WHERE (col1::NUMERIC) > col2; SELECT col1, col2, (col1 + col2::dist_type) sum_col FROM tab;

2-94

IBM Informix Guide to SQL: Syntax

CREATE DISTINCT TYPE

Related Information
Related statements: CREATE CAST, CREATE FUNCTION, CREATE OPAQUE TYPE, CREATE ROW TYPE, DROP TYPE, and DROP ROW TYPE For information and examples that show how to use and cast distinct types, see the IBM Informix Guide to SQL: Tutorial. For more information on when you might create a distinct type, see IBM Informix User-Defined Routines and Data Types Developer's Guide.

Chapter 2. SQL Statements

2-95

CREATE DUPLICATE
Use the CREATE DUPLICATE statement to create a duplicate copy of an existing table for read-only use in a specified dbslice or in specified dbspaces across coservers. Only Extended Parallel Server supports this statement, which is an extension to the ANSI/ISO standard for SQL.

Syntax
, CREATE DUPLICATE OF TABLE table IN ( dbspace dbslice )

Element dbslice dbspace table

Description Name of a dbslice in which to duplicate one fragment of table Name of a dbspace in which to duplicate one fragment of table Name of the original table from which to create a duplicate

Restrictions Must exist and must contain at most one dbspace on each target coserver

Syntax Identifier on page 5-22

Must exist; must not contain an original or Identifier on page duplicate fragment of table 5-22 Must exist in the database. See also Supported Operations on page 2-97. Database Object Name on page 5-17

Usage
If the original table resides entirely on a single coserver, you can create duplicate copies of small tables across coservers for read-only use. For each attached index of the original table, a similarly defined index is built on each table duplicate, using the same dbspaces as the table. Because query operators read the local copy of the table, duplicating small tables across coservers might improve the performance of some queries. If a local copy of a duplicated table exists but is not available because the dbspace that stores it is offline (or for some similar reason), a query that requires access to the table fails. The database server does not attempt to access the original table. The location of a duplicated table can be either a dbslice or a comma-separated list of dbspaces. You can combine dbslices and lists of dbspaces in a single CREATE DUPLICATE statement. v If the original table is not fragmented, the dbspace list need provide only a single dbspace on each coserver. For example, if the table tab1 is not fragmented, enter the following statement to create a duplicate on the remaining three of the four coservers. If the original table is stored in the dbspace db1 on coserver 1 and db2 is on coserver 2, db3 is on coserver 3, and db4 is on coserver 4.
CREATE DUPLICATE OF TABLE tab1 IN (db2, db3, db4)

v If the original table is fragmented with one fragment in the first dbspace of several dbslices that contain dbspaces on all coservers, you can create duplicate copies of the table in the remaining dbspaces of the dbslice. For example, you might create the tab3 table in the first dbspace of three dbslices, each of which contains a dbspace on each coserver, as follows:

2-96

IBM Informix Guide to SQL: Syntax

CREATE DUPLICATE
CREATE TABLE tab3 (...) FRAGMENT BY HASH (....) IN dbsl1.l, dbsl2.1, dbsl3.1;

To duplicate the tab3 table across the remaining coservers, use the following statement:
CREATE DUPLICATE OF TABLE tab3 IN dbsl1, dbsl2, dbsl3

v You can mix dbslice names and dbspace lists in the same CREATE DUPLICATE statement. For example, instead of using dbspaces in a dbslice, for the previous example, you might enter the following statement in which dbsp2a is on coserver 2, dbsp3a is on coserver 3, and dbsp4a is on coserver 4:
CREATE DUPLICATE OF TABLE tab3 IN dbsl1, dbsl2, (dbsp2a, dbsp3a, dbsp4a)

The first fragment of the original table is duplicated into dbsl1, which contains a dbspace on each coserver, the second fragment into dbsl2, which also contains a dbspace on each coserver, and the third fragment into the list of dbspaces. Only one fragment of a duplicated table can reside in any single dbspace. You cannot list an existing dbspace of the duplicated table in the list of dbspaces into which it is duplicated, but it is not an error for an existing dbspace to be a member of a dbslice that specifies duplication dbspaces. Matching dbspaces in the dbslice are ignored. The CREATE DUPLICATE statement requires the ALTER privilege.

Supported Operations
The following operations are permitted on duplicated tables: v SELECT v UPDATE STATISTICS v LOCK and UNLOCK v SET Residency v DROP TABLE You cannot duplicate a table in certain circumstances. The table must not: v Have GK or detached indexes v Use range fragmentation v v v v Be a temporary table Be a violations or diagnostic table Contain BYTE, TEXT, SERIAL, or SERIAL8 columns Have referential constraints

The CREATE DUPLICATE statement does not support incremental duplication. It also does not support multiple duplicates of the same table on a single coserver, nor duplication of tables that are fragmented across coservers. If you need to take a dbspace offline and it contains a copy of a duplicated table, or if you need to update data in a duplicated table, first drop all duplicates of the table, as described in DROP DUPLICATE on page 2-299.

Related Information
DROP DUPLICATE

Chapter 2. SQL Statements

2-97

CREATE EXTERNAL TABLE (XPS)

Use the CREATE EXTERNAL TABLE statement to define an external source that is not part of your database to load and unload data for your database. Only Extended Parallel Server supports this statement, which is an extension to the ANSI/ISO standard for SQL.

Syntax
(1) CREATE EXTERNAL TABLE table Column Definition (3) USING( (2) Table Options Table Options DATAFILES Clause (2) )

Notes: 1 2 3 See Column Definition See Table Options on page 2-103 See DATAFILES Clause on page 2-102
Description Name declared here for a table to store external data Restrictions Must be unique among names of tables, views, and synonyms in the current database Syntax Database Object Name on page 5-17

Element table

Usage
The first portion of the syntax diagram declares the name of the table and defines its columns and any column-level constraints. The portion that follows the USING keyword identifies external files that the database server opens when you use the external table, and specifies additional options for characteristics of the external table. After executing the CREATE EXTERNAL TABLE statement, you can move data to and from the external source with an INSERT INTO ... SELECT statement. See the section INTO EXTERNAL Clause (XPS) on page 2-523 for more information about loading the results of a query into an external table.

Column Definition
Column Definition:
SAMEAS template , (1) column Data Type Other Optional Clauses

2-98

IBM Informix Guide to SQL: Syntax

CREATE EXTERNAL TABLE (XPS)

Other Optional Clauses:

EXTERNAL

HEX TEXT (1) Data Type 'PACKED(p,s)' 'ZONED(p,s)' 'BINARY(n)' NULL 'null_string'

(2) Default Clause

(3) Column-Level Constraints

Notes: 1 2 3 See Data Type on page 4-18 See DEFAULT Clause on page 2-174 See Column-Level Constraints on page 2-101
Description One column name for each column of the external table Number of 8-bit bytes to represent the integer Precision (number of digits) Scale (digits in fractional part) Value to represent NULL Existing table with the same schema as the external table Restrictions Syntax

Element column n p s null_string template

For each column, you must specify a Identifier on page 5-22 built-in data type For FIXED format binary integers; big-endian byte order For FIXED-format files only For FIXED-format files only See Defining NULL Values on page 2-100. Cannot be subset of columns nor differ in any column data type n=2 for 16-bit integers n=4 for 32-bit integers Literal Number on page 4-137 Literal Number on page 4-137 Quoted String on page 4-142 Database Object Name on page 5-17

Using the SAMEAS Clause

The SAMEAS template clause uses all the column names and data types from the template table in the definition of new table. You cannot, however, use indexes in the external table definition, and you cannot use the SAMEAS clause for FIXED-format files.

Using the EXTERNAL Keyword

Use the EXTERNAL keyword to specify a data type for each column of your external table that has a data type different from the internal table. For example, you might have a VARCHAR column in the internal table that you want to map to a CHAR column in the external table. You must specify an external type for every column that is in fixed format. You cannot specify an external type for delimited format columns except for BYTE and TEXT columns where your specification is optional. For more information, see TEXT and HEX External Types on page 2-100. Integer Data Types: Besides valid Informix integer data types, you can specify packed decimal, zoned decimal, and IBM-format binary representation of integers.
Chapter 2. SQL Statements

2-99

CREATE EXTERNAL TABLE (XPS)

For packed or zoned decimal, specify precision (total number of digits in the number) and scale (number of digits that are to the right of the decimal point). Packed decimal representation can store two digits, or a digit and a sign, in each byte. Zoned decimal requires (p + 1) bytes to store p digits and the sign. Big-Endian Format: The database server also supports two IBM-format binary representations of integers: BINARY(2) for 16-bit integer storage and BINARY(4) for 32-bit integer storage. The most significant byte of each number has the lowest address; that is, binary-format integers are stored big-end first (big-endian format) in the manner of IBM and Motorola processors. Intel processors and some others store binary-format integers little-end first, a storage method that the database server does not support for external data. Defining NULL Values: The packed decimal, zoned decimal, and binary data types do not have a natural NULL value, so you must define a value to be interpreted as a NULL when loading or unloading data from an external source. You can define the null_string as a number outside the set of numbers stored in the data file (for example, -9999.99). You can also define a bit pattern in the field as a hexadecimal pattern, such as 0xffff, that is to be interpreted as a NULL. The database server uses the NULL representation for a FIXED-format external table to both interpret values as the data is loaded into the database and to format NULL values into the appropriate data type when data is unloaded to an external table. The following examples are of column definitions with NULL values for a FIXED-format external table:
i smallint external binary (2) null -32767 li integer external binary (4) null -99999 d decimal (5,2) external packed (5,2) null 0xffffff z decimal (4,2) external zoned (4,2) null 0x0f0f0f0f zl decimal (3,2) external zoned (3,2) null -1.00

If the packed decimal or zoned decimal is stored with all bits cleared to represent a NULL value, the null_string can be defined as 0x0. The following rules apply to the value assigned to a null_string: v The NULL representation must fit into the length of the external field. v If a bit pattern is defined, the null_string is not case sensitive. v If a bit pattern is defined, the null_string must begin with 0x. v For numeric fields, the left-most fields are assigned zeros by the database server if the bit pattern does not fill the entire field. v If the NULL representation is not a bit pattern, the NULL value must be a valid number for that field. Warning: If a row that contains a NULL value is unloaded into an external table and the column that receives the NULL value has no NULL value defined, the database server inserts a zero into the column. TEXT and HEX External Types: An Informix BYTE or TEXT column can be encoded in either the TEXT or HEX external type. You can use only delimited BYTE and TEXT formats with these external types. Fixed formats are not allowed. In addition, you cannot use these external types with any other type of delimited-format columns (such as character columns).

2-100

IBM Informix Guide to SQL: Syntax

CREATE EXTERNAL TABLE (XPS)

You do not need to specify these external types. If you do not define an external column specifically, Informix TEXT columns default to TEXT and Informix BYTE columns default to HEX. The database server interprets two adjacent field delimiters as a NULL value. User-defined delimiters are limited to one byte each. During unloading, delimiters and backslash ( \ ) symbols are escaped. During loading, any character that follows a backslash is interpreted as a literal. In TEXT format, nonprintable characters are directly embedded in the data file. For delimiter rules in a multibyte locale, see the IBM Informix GLS User's Guide. For more information on BYTE and TEXT data, see your IBM Informix Administrator's Guide.

Manipulating Data in Fixed Format Files

For files in FIXED format, you must declare the column name and the EXTERNAL item for each column to set the name and number of characters. For FIXED-format files, the only data type allowed is CHAR. You can use the keyword NULL to specify what string to interpret as a NULL value.

Column-Level Constraints
Use column-level constraints to limit the type of data that is allowed in a column. Constraints at the column level are limited to a single column. Column-Level Constraints:

NOT NULL CHECK ( Condition

(1) )

Notes: 1 See Condition on page 4-5

Using the NOT NULL Constraint

If you do not indicate a default value for a column, the default is NULL unless you place a not-NULL constraint on the column. In that case, no default value exists for the column. If you place a NOT NULL constraint on a column (and no default value is specified), the data in the external table must have a value set for the column when loading through the external table. When no reject file exists and no value is encountered, the database server returns an error and the loading stops. When a reject file exists and no value is encountered, the error is reported in the reject file and the load continues.

Using the CHECK Constraint

Check constraints allow you to designate conditions that must be met before data can be assigned to a column during an INSERT or UPDATE statement. When a reject file does not exist and a row evaluates to false for any check constraint defined on a table during an insert or update, the database server returns an error. When there is a reject file and a row evaluates to false for a check constraint defined on the table, the error is reported in the reject file and the statement continues to execute.

Chapter 2. SQL Statements

2-101

CREATE EXTERNAL TABLE (XPS)

Check constraints are defined with search conditions. The search condition cannot contain subqueries, aggregates, host variables, or SPL routines. In addition, it cannot include the built-in functions CURRENT, USER, SITENAME, DBSERVERNAME, or TODAY. When you define a check constraint at the column level, the only column that the check constraint can check against is the column itself. In other words, the check constraint cannot depend upon values in other columns of the table.

DATAFILES Clause
The DATAFILES clause specifies external files that are opened when you use external tables. DATAFILES Clause:
DATAFILES

, ( ' DISK PIPE : coserver_num coserver_group : fixed_path formatted_path ' )

Element coserver_group coserver_num fixed_path formatted_path

Description Coserver group that contains the external data

Restrictions Must exist

Syntax Identifier on page 5-22 Literal Number on page 4-137 Must conform to operating-system rules. Must conform to operating-system rules.

Numeric ID of coserver containing the external data Must exist Pathname for input or output files in the definition of the external table Formatted pathname that uses pattern-matching characters Must exist Must exist

You can use cogroup names and coserver numbers when you describe the input or output files for the external table definition. You can identify the DATAFILES either by coserver number or by cogroup name. A coserver number contains only digits. A cogroup name is a valid identifier that begins with a letter but otherwise contains any combination of letters, digits, and underscore symbols. If you use only some of the available coservers for reading or writing files, you can designate these coservers as a cogroup using onutil and then use the cogroup name, rather than explicitly specifying each coserver and file separately. Whenever you use all coservers to manage external files, you can use the predefined coserver_group. For examples of the DATAFILES clause, see Examples on page 2-106.

Using Formatting Characters

You can use a formatted pathname to designate a filename. If you use a formatted pathname, you can take advantage of the substitution characters %c, %n, and %r (first ... last). Formatting String %c Effect Replaced with the number of the coserver that manages the file

2-102

IBM Informix Guide to SQL: Syntax

CREATE EXTERNAL TABLE (XPS)

%n %r(first ... last) Replaced with the name of the node on which the coserver that manages the file resides Specifies multiple files on a single coserver

Important: The formatted pathname option does not support the %o formatting string.

Table Options
These options specify additional characteristics that define the table. Table Options:
, DELIMITED FORMAT ' INFORMIX FIXED DEFAULT ESCAPE EXPRESS DELUXE ASCII CODESET ' ' EBCDIC DELIMITER field_delimiter RECORDEND record_delimiter MAXERRORS num_errors REJECTFILE filename SIZE num_rows '

Element field_delimiter filename num_errors num_rows quoted_string record_delimiter

Description

Restrictions

Syntax Quoted String on page 4-142 Must conform to operating-system rules. Literal Number on page 4-137 Literal Number on page 4-137 Quoted String on page 4-142 Quoted String on page 4-142

Character to separate fields. Default For nonprinting characters, is pipe ( | ) character use octal Full pathname for conversion error messages from coservers Errors per coserver before load operations are terminated Approximate number of rows contained in the external table ASCII character specified here as the escape character Character to separate records Default is Newline ( \n ) See Reject Files on page 2-104. Value is ignored unless MAXERRORS is set Cannot be a negative number Only a single character is valid For nonprinting characters, use octal

The num_errors specification is ignored during unload tasks. If the MAXERRORS environment variable is not set, the database server processes all data in load operations, regardless of the number of errors or num_errors value. If the RECORDEND environment variable is not set, record_delimiter defaults to the Newline character ( \n ). To specify a nonprinting character as the record delimiter or field delimiter, you must encode it as the octal representation of the ASCII character. For example, \006 can represent CTRL-F.

Chapter 2. SQL Statements

2-103

CREATE EXTERNAL TABLE (XPS)

Use the table options keywords as the following table describes. You can use each keyword whenever you plan to load or unload data unless only one of the two modes is specified. Keyword CODESET DEFAULT (load only) Description Specifies the type of code set of the data Specifies replacing missing values in delimited input files with column defaults (if they are defined) instead of NULLs, so input files can be sparsely populated. Files do not need an entry for every column in the file where a default is the value to be loaded. Specifies the character that separates fields in a delimited text file Sets a flag causing the database server to load data in deluxe mode Deluxe mode is required for loading into STANDARD tables. ESCAPE EXPRESS Defines a character to mark ASCII special characters in ASCII-text-based data files Sets a flag that causes the database server to attempt to load data in express mode If you request express mode but indexes or unique constraints exist on the table or the table contains BYTE or TEXT data, or the target table is not RAW or OPERATIONAL, the load stops with an error message that reports the problem. Specifies the format of the data in the data files Sets the number of errors that are allowed per coserver before the database server stops the load Specifies the character that separates records in a delimited text file Sets the full pathname where all coservers write data-conversion errors If not specified or if files cannot be opened, any error ends the load job abnormally. See also Reject Files on page 2-104. The approximate number of rows in the external table. This can improve performance when external table is used in a join query.

DELIMITER DELUXE (load only)

FORMAT MAXERRORS RECORDEND REJECTFILE

SIZE

Important: Check constraints on external tables are designed to be evaluated only when loading data. The database server cannot enforce check constraints on external tables because the data can be freely altered outside the control of the database server. If you want to restrict rows that are written to an external table during unload, use a WHERE clause to filter the rows.

Reject Files
Rows that have conversion errors during a load or rows that violate check constraints on the external table are written to a reject file on the coserver that

2-104

IBM Informix Guide to SQL: Syntax

CREATE EXTERNAL TABLE (XPS)

performs the conversion. Each coserver manages its own reject file. The REJECTFILE clause declares the name of the reject file on each coserver. You can use the formatting characters %c and %n (but not %r) in the filename format. Use the %c formatting characters to make the filenames unique. For more information on how to format characters, see the section Using Formatting Characters on page 2-102. If you perform another load to the same table during the same session, any earlier reject file of the same name is overwritten. Reject file entries have the following format:
coserver-number, filename, record, reason-code, field-name: bad-line

The following table describes these elements of the reject file: Element coserver-number filename record reason-code field-name Description Number of the coserver from which the file is read Name of the input file Record number in the input file where the error was detected Description of the error External field name where the first error in the line occurred, or <none> if the rejection is not specific to a particular column Line that caused the error (delimited or fixed-position character files only), up to 80 characters

bad-line

The reject file writes the coserver-number, filename, record, field-name, and reason-code in ASCII. The bad-line information varies with the type of input file. v For delimited files or fixed-position character files, up to 80 characters of the bad line are copied directly into the reject file. v For Informix internal data files, the bad line information is not placed in the reject file because you cannot edit the binary representation in a file; but the coserver-number, filename, record, reason-code, and field-name are still reported in the reject file so you can isolate the problem. Use the Table Options clause to specify the format of the external data. Errors that can cause a row to be rejected include the following. Error Text Explanation

CONSTRAINT constraint name This constraint was violated. CONVERT_ERR MISSING_DELIMITER MISSING_RECORDEND NOT NULL ROW_TOO_LONG Any field encounters a conversion error. No delimiter was found. No record end was found. A NULL was found in field-name. The input record is longer than 32 kilobytes.

Chapter 2. SQL Statements

2-105

CREATE EXTERNAL TABLE (XPS)

Examples
The examples in this section show how to specify the DATAFILES field. Assume that the database server is running on four nodes, and one file is to be read from each node. All files have the same name. The DATAFILES specification can then be as follows:
DATAFILES ("DISK:cogroup_all:/work2/unload.dir/mytbl")

Now, consider a system with 16 coservers where only three coservers have tape drives attached (for example, coservers 2, 5, and 9). If you define a cogroup for these coservers before you run load and unload commands, you can use the cogroup name rather than a list of individual coservers when you execute the commands. To set up the cogroup, run onutil.
% onutil 1> create cogroup tape_group 2> from coserver.2, coserver.5, coserver.9; Cogroup successfully created.

Then define the file locations for named pipes:

DATAFILES ("PIPE:tape_group:/usr/local/TAPE.%c")

The filenames expand as follows:

DATAFILES ("pipe:2:/usr/local/TAPE.2", "pipe:5:/usr/local/TAPE.5", "pipe:9:/usr/local/TAPE.9")

If, instead, you want to process three files on each of two coservers, define the files as follows:
DATAFILES ("DISK:1:/work2/extern.dir/mytbl.%r(1..3)", "DISK:2:/work2/extern.dir/mytbl.%r(4..6)")

The expanded list follows:

DATAFILES ("disk:1:/work2/extern.dir/mytbl.1", "disk:1:/work2/extern.dir/mytbl.2", "disk:1:/work2/extern.dir/mytbl.3", "disk:2:/work2/extern.dir/mytbl.4", "disk:2:/work2/extern.dir/mytbl.5", "disk:2:/work2/extern.dir/mytbl.6")

Related Information
Related statements: INSERT and SET PLOAD FILE See also the INTO Table Clauses of SELECT. For more information on external tables, refer to your IBM Informix Administrator's Reference.

2-106

IBM Informix Guide to SQL: Syntax

CREATE FUNCTION
Use the CREATE FUNCTION statement to create a user-defined function, register an external function, or to write and register an SPL function. Only Dynamic Server supports this statement, which is an extension to the ANSI/ISO standard for SQL.

Syntax
CREATE DBA Routine Parameter List FUNCTION function( (1) )

(2) Return Clause (3) SPECIFIC Specific Name

, (4) WITH( Routine Modifier )

(5) Statement Block (7)

(6) END FUNCTION (8) External Routine Reference

, (9) DOCUMENT Quoted String

WITH LISTING IN pathname

Notes: 1 2 3 4 5 6 7 8 9 See Routine Parameter List on page 5-61 See Return Clause on page 5-51 See Specific Name on page 5-68 See Routine Modifier on page 5-54 Stored Procedure Language only See Statement Block on page 5-69 External routines only See External Routine Reference on page 5-20 See Quoted String on page 4-142

Chapter 2. SQL Statements

2-107

CREATE FUNCTION
Element function Description Name of new function that is defined here Restrictions You must have the appropriate language privileges. See GRANT on page 2-371 and Overloading the Name of a Function on page 2-109. The specified pathname must exist on the computer where the database resides Syntax Database Object Name on page 5-17

pathname

Pathname to a file in which compile-time warnings are stored

The path and filename must conform to your operating-system rules.

Tip: If you are trying to create a function from text of source code that is in a separate file, use the CREATE FUNCTION FROM statement.

Usage
Dynamic Server supports user-defined functions written in these languages: v Stored Procedure Language (SPL) v One of the external languages (C or Java) that Dynamic Server supports (external functions) When the IFX_EXTEND_ROLE configuration parameter of is set to ON, only users to whom the DBSA grants the built-in EXTEND role can create external functions. How many values a function can return is language-dependent. Functions written in SPL can return one or more values. External functions written in the C or Java languages must return exactly one value. But a C function can return a collection type, and external functions in queries can return additional values indirectly from OUT parameters (and for the Java language, from INOUT parameters) that Dynamic Server can process as statement-local variables (SVLs). For information on how this manual uses the terms UDR, function, and procedure as well as recommended usage, see Relationship Between Routines, Functions, and Procedures on page 2-146 and Using CREATE PROCEDURE Versus CREATE FUNCTION on page 2-146, respectively. The entire length of a CREATE FUNCTION statement must be less than 64 kilobytes. This length is the literal length of the statement, including whitespace characters such as blank spaces and tabs. In ESQL/C, you can use a CREATE FUNCTION statement only within a PREPARE statement. If you want to create a user-defined function for which the text is known at compile time, you must put the text in a file and specify this file with the CREATE FUNCTION FROM statement. Functions use the collating order that was in effect when they were created. See SET COLLATION for information about using non-default collation.

Privileges Necessary for Using CREATE FUNCTION

You must have the Resource privilege on a database to create a function within that database. Before you can create an SPL function, you must also have the Usage privilege on the SPL language . For more information, see Usage Privilege in Stored Procedure Language on page 2-382.

2-108

IBM Informix Guide to SQL: Syntax

CREATE FUNCTION
By default, Usage privilege on SPL is granted to PUBLIC. You must also have at least Resource privilege on a database to create an SPL function in that database.

DBA Keyword and Privileges on the Created Function

The level of privilege necessary to execute a UDR depends on whether the UDR is created with the DBA keyword. If you create a UDR with the DBA keyword, it is known as a DBA-privileged UDR. You need the DBA privilege to create or execute a DBA-privileged UDR. If you omit the DBA keyword, the UDR is known as an owner-privileged UDR. If you create an owner-privileged UDR in an ANSI-compliant database, anyone can execute the UDR. If you create an owner-privileged UDR in a database that is not ANSI compliant, the NODEFDAC environment variable prevents privileges on that UDR from being granted to PUBLIC. If this environment variable is set, the owner of a UDR must grant the Execute privilege for that UDR to other users. If an external C or Java language function has a negator function, you must grant the Execute privilege on both the external function and on its negator function before users can execute the external function.

Overloading the Name of a Function

Because Dynamic Server supports routine overloading, you can define more than one function with the same name, but different parameter lists. You might want to overload functions in the following situations: v You create a user-defined function with the same name as a built-in function (such as equal( )) to process a new user-defined data type. v You create type hierarchies, in which subtypes inherit data representation and functions from supertypes. v You create distinct types, which are data types that have the same internal storage representation as an existing data type, but have different names and cannot be compared to the source type without casting. Distinct types inherit support functions from their source types. For a brief description of the routine signature that uniquely identifies each user-defined function, see Routine Overloading and Naming UDRs with a Routine Signature (IDS) on page 5-19. Using the SPECIFIC Clause to Specify a Specific Name: You can declare a specific name, unique to the database, for a user-defined function. A specific name is useful when you are overloading a function.

DOCUMENT Clause
The quoted string in the DOCUMENT clause provides a synopsis and description of the UDR. The string is stored in the sysprocbody system catalog table and is intended for the user of the UDR. Anyone with access to the database can query the sysprocbody system catalog table to obtain a description of one or all of the UDRs stored in the database. For example, the following query obtains a description of the SPL function update_by_pct, that SPL Functions on page 2-110 shows:

Chapter 2. SQL Statements

2-109

CREATE FUNCTION
SELECT data FROM sysprocbody b, sysprocedures p WHERE b.procid = p.procid --join between the two catalog tables AND p.procname = update_by_pct -- look for procedure named update_by_pct AND b.datakey = D-- want user document;

The preceding query returns the following text:

USAGE: Update a price by a percentage Enter an integer percentage from 1 - 100 and a part id number

A UDR or application program can query the system catalog tables to fetch the DOCUMENT clause and display it for a user. For C and Java language functions, you can include a DOCUMENT clause at the end of the CREATE FUNCTION statement, whether or not you use the END FUNCTION keywords.

WITH LISTING IN Clause

The WITH LISTING IN clause specifies a filename where compile time warnings are sent. After you compile a UDR, this file holds one or more warning messages. If you do not use the WITH LISTING IN clause, the compiler does not generate a list of warnings. On UNIX platforms, if you specify a filename but not a directory, this listing file is created in your home directory on the computer where the database resides. If you do not have a home directory on this computer, the file is created in the root directory (the directory named /). On Windows systems, if you specify a filename but not a directory, this listing file is created in your current working directory if the database is on the local computer. Otherwise, the default directory is %INFORMIXDIR%\bin.

SPL Functions
SPL functions are UDRs written in SPL that return one or more values. To write and register an SPL function, use a CREATE FUNCTION statement. Embed appropriate SQL and SPL statements between the CREATE FUNCTION and END FUNCTION keywords. You can also follow the function with the DOCUMENT and WITH FILE IN options. SPL functions are parsed, optimized (as far as possible), and stored in the system catalog tables in executable format. The body of an SPL function is stored in the sysprocbody system catalog table. Other information about the function is stored in other system catalog tables, including sysprocedures, sysprocplan, and sysprocauth. For more information about these system catalog tables, see the IBM Informix Guide to SQL: Reference. The END FUNCTION keywords are required in every SPL function, and a semicolon ( ; ) must follow the clause that immediately precedes the statement block. The following code example creates an SPL function:
CREATE FUNCTION update_by_pct ( pct INT, pid CHAR(10)) RETURNING INT; DEFINE n INT; UPDATE inventory SET price = price + price * (pct/100) WHERE part_id = pid;

2-110

IBM Informix Guide to SQL: Syntax

CREATE FUNCTION
LET n = price; RETURN price; END FUNCTION DOCUMENT "USAGE: Update a price by a percentage", "Enter an integer percentage from 1 - 100", "and a part id number" WITH LISTING IN /tmp/warn_file

For more information on how to write SPL functions, see the chapter about SPL routines in IBM Informix Guide to SQL: Tutorial. See also the section Transactions in SPL Routines on page 5-72. You can include valid SQL or SPL language statements in SPL functions. See, however, the following sections in Chapter 5 that describe restrictions on SQL and SPL statements within SPL routines: Subset of SPL Statements Valid in the Statement Block on page 5-69; SQL Statements Not Valid in an SPL Statement Block on page 5-70; and Restrictions on SPL Routines in Data-Manipulation Statements on page 5-71.

Internal representation
External functions are functions you write in an external language (that is, a programming language other than SPL) that Dynamic Server supports. To create a C user-defined function: 1. Write the C function. 2. Compile the function and store the compiled code in a shared library (the shared-object file for C). 3. Register the function in the database server with the CREATE FUNCTION statement. To create a user-defined function written in the Java language: 1. Write a Java static method, which can use the JDBC functions to interact with the database server. 2. Compile the Java source file and create a .jar file (the shared-object file for Java). 3. Execute the install_jar( ) procedure with the EXECUTE PROCEDURE statement to install the jar file in the current database. 4. If the UDR uses user-defined types, create a map between SQL data types and Java classes. Use the setUDTExtName( ) procedure that is explained in EXECUTE PROCEDURE on page 2-336. 5. Register the UDR with the CREATE FUNCTION statement. Rather than storing the body of an external routine directly in the database, the database server stores only the pathname of the shared-object file that contains the compiled version of the routine. When it executes the external routine, the database server invokes the external object code. The database server stores information about an external function in system catalog tables, including sysprocbody and sysprocauth. For more information on the system catalog, see the IBM Informix Guide to SQL: Reference.

Example of Registering a C User-Defined Function

The following example registers an external C user-defined function named equal( ) in the database. This function takes two arguments of the type basetype1 and
Chapter 2. SQL Statements

2-111

CREATE FUNCTION
returns a single Boolean value. The external routine reference name specifies the path to the C shared library where the function object code is actually stored. This library contains a C function basetype1_equal( ), which is invoked during execution of the equal( ) function.
CREATE FUNCTION equal ( arg1 basetype1, arg2 basetype1) RETURNING BOOLEAN; EXTERNAL NAME "/usr/lib/basetype1/lib/libbtype1.so(basetype1_equal)" LANGUAGE C END FUNCTION

Example of Registering a UDR Written in the Java Language

The following CREATE FUNCTION statement registers the user-defined function, sql_explosive_reaction( ). This function is discussed in sqlj.install_jar on page 2-339.
CREATE FUNCTION sql_explosive_reaction(int) RETURNS int WITH (class="jvp") EXTERNAL NAME "course_jar:Chemistry.explosiveReaction" LANGUAGE JAVA

This function returns a single INTEGER value. The EXTERNAL NAME clause specifies that the Java implementation of the sql_explosive_reaction( ) function is a method called explosiveReaction( ), which resides in the Chemistry Java class that resides in the course_jar jar file.

Ownership of Created Database Objects

The user who creates an owner-privileged UDR, rather than the user who executes the UDR, owns any database objects that are created by the UDR when the UDR is executed, unless another owner is specified for the created object. For example, assume that user mike creates this user-defined function:
CREATE FUNCTION func1 () RETURNING INT; CREATE TABLE tab1 (colx INT); RETURN 1; END FUNCTION

If user joan now executes function func1, user mike, not user joan, is the owner of the newly created table tab1. In the case of a DBA-privileged UDR, however, the user who executes a UDR (rather than the UDR owner) owns any database objects created by the UDR, unless another owner is specified for the database object within the UDR. For example, assume that user mike creates this user-defined function:
CREATE DBA FUNCTION func2 () RETURNING INT; CREATE TABLE tab2 (coly INT); RETURN 1; END FUNCTION;

If user joan now executes function func2, user joan, not user mike, is the owner of the newly created table tab2. See also the section Support for Roles and User Identity on page 5-72.

Related Information
Related statements: ALTER FUNCTION, ALTER ROUTINE, CREATE PROCEDURE, CREATE FUNCTION FROM, DROP FUNCTION, DROP ROUTINE, GRANT, EXECUTE FUNCTION, PREPARE, REVOKE, and UPDATE STATISTICS

2-112

IBM Informix Guide to SQL: Syntax

CREATE FUNCTION
Chapter 3 of this manual describes the SPL language. For a discussion of how to create and use SPL routines, see the IBM Informix Guide to SQL: Tutorial. For a discussion of how to create and use external routines, see IBM Informix User-Defined Routines and Data Types Developer's Guide. For information about how to create C UDRs, see the IBM Informix DataBlade API Programmer's Guide.

Chapter 2. SQL Statements

2-113

CREATE FUNCTION FROM

Use the CREATE FUNCTION FROM statement to access a user-defined function whose CREATE FUNCTION statement resides in a separate file. Only Dynamic Server supports this statement, which is an extension to the ANSI/ISO standard for SQL. Use this statement with ESQL/C.

Syntax
CREATE FUNCTION FROM file file_var

Element file

Description Path and filename of a file that contains the full CREATE FUNCTION statement text. Default pathname is current directory. Variable storing value of file

Restrictions Must exist and contain exactly one CREATE FUNCTION statement Same as for file

Syntax Must conform to operating-system rules. Language specific

file_var

Usage
Functions written in the C or Java language are called external functions. When the IFX_EXTEND_ROLE configuration parameter is set to ON, only users who have been granted the built-in EXTEND role can create external functions. An ESQL/C program cannot directly create a user-defined function. That is, it cannot contain the CREATE FUNCTION statement. To create these functions within an ESQL/C program: 1. Create a source file with the CREATE FUNCTION statement. 2. Use the CREATE FUNCTION FROM statement to send the contents of this source file to the database server for execution. The file that you specify in the file parameter can contain only one CREATE FUNCTION statement. For example, suppose that the following CREATE FUNCTION statement is in a separate file, called del_ord.sql:
CREATE FUNCTION delete_order( p_order_num int) RETURNING int, int; DEFINE item_count int; SELECT count(*) INTO item_count FROM items WHERE order_num = p_order_num; DELETE FROM orders WHERE order_num = p_order_num; RETURN p_order_num, item_count; END FUNCTION;

In the ESQL/C program, you can access the delete_order( ) SPL function with the following CREATE FUNCTION FROM statement:
EXEC SQL create function from del_ord.sql;

If you are not sure whether the UDR in the file is a user-defined function or a user-defined procedure, use the CREATE ROUTINE FROM statement.

2-114

IBM Informix Guide to SQL: Syntax

CREATE FUNCTION FROM

The filename that you provide is relative. If you provide a simple filename with no pathname (as in the preceding example), the client application looks for the file in the current directory. Important: The ESQL/C preprocessor does not process the contents of the file that you specify. It only sends the contents to the database server for execution. Therefore, there is no syntactic check that the file that you specify in CREATE FUNCTION FROM actually contains a CREATE FUNCTION statement. To improve readability of the code, however, it is recommended that you match these two statements.

Related Information
Related statements: CREATE FUNCTION, CREATE PROCEDURE, CREATE PROCEDURE FROM, and CREATE ROUTINE FROM

Chapter 2. SQL Statements

2-115

CREATE INDEX
Use the CREATE INDEX statement to create an index for one or more columns in a table, or on values returned by a UDR that uses column values as arguments. This statement is an extension to the ANSI/ISO standard for SQL.

Syntax
CREATE

(1) Index-Type Options (3) GK INDEX index ON static ( GK SELECT Clause Index Scope Index-Key Specs

(2) Index Options (4) ) USING BITMAP

(3) LOCK MODE Options

(5)

(6) ONLINE

Index Scope:
INDEX index ON table synonym

Index Options:

(6) USING Access-Method Clause

(7) FILLFACTOR Option

(8)

(9) Storage Options (3) USING BITMAP

(6) Index Modes

(10)

Notes: 1 2 3 4 5 6 7 8 9 10 See Index-Type Options on page 2-117 See Index-Key Specification on page 2-118 Extended Parallel Server only See SELECT Clause for Generalized-Key Index on page 2-132 See LOCK MODE Options (XPS) on page 2-132 Dynamic Server only See USING Access-Method Clause (IDS) on page 2-123 See FILLFACTOR Option on page 2-125 See Storage Options on page 2-125 See Index Modes (IDS) on page 2-130
IBM Informix Guide to SQL: Syntax

2-116

CREATE INDEX
Element index static synonym, table Description Name declared here for a new index Table on which a Generalized Key index is created (XPS) Name or synonym of a standard or temporary table to be indexed Restrictions Must be unique among names of indexes in the database Table must exist and be static; it cannot be a virtual table Synonym and its table must exist in the current database Syntax Database Object Name on page 5-17 Database Object Name on page 5-17 Database Object Name on page 5-17

Usage
When you issue the CREATE INDEX statement, the table is locked in exclusive mode. If another process is using the table, CREATE INDEX returns an error. (For an exception, however, see The ONLINE Keyword (IDS) on page 2-302.) Indexes use the collation that was in effect when CREATE INDEX executed. A secondary-access method (sometimes referred to as an index-access method) is a set of database server functions that build, access, and manipulate an index structure such as a B-tree, R-tree, or an index structure that a DataBlade module provides, in order to speed up the retrieval of data. In Dynamic Server, neither synonym nor table can refer to a virtual table. If you are using Extended Parallel Server, use the USING BITMAP keywords to store the list of records in each key of the index as a compressed bitmap. The storage option is not compatible with a bitmap index because bitmap indexes must be fragmented in the same way as the table.

Index-Type Options
The index-type options let you specify attributes of the index. Index-Type Options:

DISTINCT UNIQUE

CLUSTER

UNIQUE or DISTINCT Option

Use the UNIQUE or DISTINCT keyword to require that the columns on which the index is based accept only unique data. If you do not specify the UNIQUE or DISTINCT keyword, the index allows duplicate values in the indexed column (or in the set of indexed columns). The following example creates a unique index:
CREATE UNIQUE INDEX c_num_ix ON customer (customer_num)

A unique index prevents duplicate values in the customer_num column. A column with a unique index can have, at most, one NULL value. The DISTINCT and UNIQUE keywords are synonyms in this context, so the following statement has the same effect as the previous example:
CREATE DISTINCT INDEX c_num_ix ON customer (customer_num)

The index in both examples is maintained in ascending order, which is the default order.
Chapter 2. SQL Statements

2-117

CREATE INDEX
You can also prevent duplicates in a column or set of columns by creating a unique constraint with the CREATE TABLE or ALTER TABLE statement. You cannot specify an R-tree secondary-access method for a UNIQUE index key. For more information on how to create unique constraints, see the CREATE TABLE or ALTER TABLE statements. See also the section Differences Between a Unique Constraint and a Unique Index on page 2-178. How Indexes Affect Primary-Key, Unique, and Referential Constraints: The database server creates internal B-tree indexes for primary-key, unique, and referential constraints. If a primary-key, unique, or referential constraint is added after the table is created, any user-created indexes on the constrained columns are used, if appropriate. An appropriate index is one that indexes the same columns that are used in the primary-key, referential, or unique constraint. If an appropriate user-created index is not available, the database server creates a nonfragmented internal index on the constrained column or columns.

CLUSTER Option
Use the CLUSTER keyword to reorder the rows of the table in the order that the index designates. The CREATE CLUSTER INDEX statement fails if a CLUSTER index already exists on the same table.
CREATE CLUSTER INDEX c_clust_ix ON customer (zipcode)

This statement creates an index on the customer table and physically orders the rows according to their postal code values, in (by default) ascending order. If the CLUSTER option is specified in addition to fragments on, the data values are clustered only within each fragment, and not globally across the entire table. In Dynamic Server, you cannot specify the CLUSTER option and the ONLINE keyword in the same statement. In addition, some secondary-access methods (such as R-tree) do not support clustering. Before you specify CLUSTER for your index, be sure that the index uses an access method that supports clustering. If you are using Extended Parallel Server, you cannot use the CLUSTER option on STANDARD tables. In addition, you cannot specify the CLUSTER option and storage options in t