Who Should Use This Book
How To Use This Book
How This Book is Structured
How to Read the Syntax Diagrams
Conventions Used in This Manual
Error Conditions
Highlighting Conventions
Related Documentation for This Book
Introduction
Who Should Use This Book
How To Use This Book
How This Book is Structured
How to Read the Syntax Diagrams
Conventions Used in This Manual
Error Conditions
Highlighting Conventions
Related Documentation for This Book
Concepts
Relational Database
Structured Query Language (SQL)
Embedded SQL
Static SQL
Dynamic SQL
DB2 Call Level Interface (CLI) & Open Database Connectivity (ODBC)
Java Database Connectivity (JDBC) and Embedded SQL for Java (SQLJ) Programs
Interactive SQL
Schemas
Controlling Use of Schemas
Tables
Views
Aliases
Indexes
Keys
Unique Keys
Primary Keys
Foreign Keys
Partitioning Keys
Constraints
Unique Constraints
Referential Constraints
Table Check Constraints
Triggers
Event Monitors
Queries
Table Expressions
Common Table Expressions
Packages
Catalog Views
Application Processes, Concurrency, and Recovery
Isolation Level
Repeatable Read (RR)
Read Stability (RS)
Cursor Stability (CS)
Uncommitted Read (UR)
Comparison of Isolation Levels
Distributed Relational Database
Application Servers
CONNECT (Type 1) and CONNECT (Type 2)
Remote Unit of Work
Application-Directed Distributed Unit of Work
Data Representation Considerations
DB2 Federated Systems
The Federated Server, Federated Database, and Data Sources
Tasks to Perform in a DB2 Federated System
Wrappers and Wrapper Modules
Server Definitions and Server Options
User Mappings and User Options
Data Type Mappings
Function Mappings, Function Templates, and Function Mapping Options
Nicknames and Column Options
Index Specifications
Distributed Requests
Compensation
Pass-Through
Character Conversion
Character Sets and Code Pages
Code Page Attributes
Authorization and Privileges
Table Spaces and Other Storage Structures
Data Partitioning Across Multiple Partitions
Partitioning Maps
Table Collocation
Language Elements
Characters
MBCS Considerations
Tokens
MBCS Considerations
Identifiers
SQL Identifiers
Host Identifiers
Naming Conventions and Implicit Object Name Qualifications
Aliases
Authorization IDs and authorization-names
Dynamic SQL Characteristics at run-time
Authorization IDs and Statement Preparation
Data Types
Nulls
Large Objects (LOBs)
Character Strings
Graphic Strings
Binary String
Numbers
Datetime Values
DATALINK Values
User Defined Types
Promotion of Data Types
Casting Between Data Types
Assignments and Comparisons
Numeric Assignments
String Assignments
Datetime Assignments
DATALINK Assignments
User-defined Type Assignments
Reference Type Assignments
Numeric Comparisons
String Comparisons
Datetime Comparisons
User-defined Type Comparisons
Reference Type Comparisons
Rules for Result Data Types
Character Strings
Graphic Strings
Binary Large Object (BLOB)
Numeric
DATE
TIME
TIMESTAMP
DATALINK
User-defined Types
Nullable Attribute of Result
Rules for String Conversions
Partition Compatibility
Constants
Integer Constants
Floating-Point Constants
Decimal Constants
Character String Constants
Hexadecimal Constants
Graphic String Constants
Using Constants with User-defined Types
Special Registers
CURRENT DATE
CURRENT DEFAULT TRANSFORM GROUP
CURRENT DEGREE
CURRENT EXPLAIN MODE
CURRENT EXPLAIN SNAPSHOT
CURRENT NODE
CURRENT PATH
CURRENT QUERY OPTIMIZATION
CURRENT REFRESH AGE
CURRENT SCHEMA
CURRENT SERVER
CURRENT TIME
CURRENT TIMESTAMP
CURRENT TIMEZONE
USER
Column Names
Qualified Column Names
Correlation Names
Column Name Qualifiers to Avoid Ambiguity
Column Name Qualifiers in Correlated References
References to Host Variables
Host Variables in Dynamic SQL
References to BLOB, CLOB, and DBCLOB Host Variables
References to Locator Variables
References to BLOB, CLOB, and DBCLOB File Reference Variables
References to Structured Type Host Variables
Functions
External, SQL and Sourced User-Defined Functions
Scalar, Column, Row and Table User-Defined Functions
Function signatures
SQL Path
Function Resolution
Function Invocation
Methods
External and SQL User-Defined Methods
Method Signatures
Method Invocation
Method Resolution
Method of Choosing the Best Fit
Example of Method Resolution
Method Invocation
Conservative Binding Semantics
Expressions
Without Operators
With the Concatenation Operator
With Arithmetic Operators
Two Integer Operands
Integer and Decimal Operands
Two Decimal Operands
Decimal Arithmetic in SQL
Floating-Point Operands
User-defined Types as Operands
Scalar Fullselect
Datetime Operations and Durations
Datetime Arithmetic in SQL
Precedence of Operations
CASE Expressions
CAST Specifications
Dereference Operations
OLAP Functions
Method Invocation
Subtype Treatment
Predicates
Basic Predicate
Quantified Predicate
BETWEEN Predicate
EXISTS Predicate
IN Predicate
LIKE Predicate
NULL Predicate
TYPE Predicate
Search Conditions
Examples
Functions
Column Functions
AVG
CORRELATION
COUNT
COUNT_BIG
COVARIANCE
GROUPING
MAX
MIN
REGRESSION Functions
STDDEV
SUM
VARIANCE
Scalar Functions
ABS or ABSVAL
ACOS
ASCII
ASIN
ATAN
ATAN2
BIGINT
BLOB
CEILING or CEIL
CHAR
CHR
CLOB
COALESCE
CONCAT
COS
COT
DATE
DAY
DAYNAME
DAYOFWEEK
DAYOFWEEK_ISO
DAYOFYEAR
DAYS
DBCLOB
DECIMAL
DEGREES
DEREF
DIFFERENCE
DIGITS
DLCOMMENT
DLLINKTYPE
DLURLCOMPLETE
DLURLPATH
DLURLPATHONLY
DLURLSCHEME
DLURLSERVER
DLVALUE
DOUBLE
EVENT_MON_STATE
EXP
FLOAT
FLOOR
GENERATE_UNIQUE
GRAPHIC
HEX
HOUR
INSERT
INTEGER
JULIAN_DAY
LCASE or LOWER
LCASE (SYSFUN schema)
LEFT
LENGTH
LN
LOCATE
LOG
LOG10
LONG_VARCHAR
LONG_VARGRAPHIC
LTRIM
LTRIM (SYSFUN schema)
MICROSECOND
MIDNIGHT_SECONDS
MINUTE
MOD
MONTH
MONTHNAME
NODENUMBER
NULLIF
PARTITION
POSSTR
POWER
QUARTER
RADIANS
RAISE_ERROR
RAND
REAL
REPEAT
REPLACE
RIGHT
ROUND
RTRIM
RTRIM (SYSFUN schema)
SECOND
SIGN
SIN
SMALLINT
SOUNDEX
SPACE
SQRT
SUBSTR
TABLE_NAME
TABLE_SCHEMA
TAN
TIME
TIMESTAMP
TIMESTAMP_ISO
TIMESTAMPDIFF
TRANSLATE
TRUNCATE or TRUNC
TYPE_ID
TYPE_NAME
TYPE_SCHEMA
UCASE or UPPER
VALUE
VARCHAR
VARGRAPHIC
WEEK
WEEK_ISO
YEAR
Table Functions
SQLCACHE_SNAPSHOT
User-Defined Functions
Queries
subselect
select-clause
from-clause
table-reference
joined-table
where-clause
group-by-clause
having-clause
Examples of subselects
Examples of Joins
Examples of Grouping Sets, Cube, and Rollup
fullselect
Examples of a fullselect
select-statement
common-table-expression
order-by-clause
update-clause
read-only-clause
fetch-first-clause
optimize-for-clause
Examples of a select-statement
SQL Statements
How SQL Statements Are Invoked
Embedding a Statement in an Application Program
Dynamic Preparation and Execution
Static Invocation of a select-statement
Dynamic Invocation of a select-statement
Interactive Invocation
SQL Return Codes
SQLCODE
SQLSTATE
SQL Comments
ALTER BUFFERPOOL
ALTER NICKNAME
ALTER NODEGROUP
ALTER SERVER
ALTER TABLE
ALTER TABLESPACE
ALTER TYPE (Structured)
ALTER USER MAPPING
ALTER VIEW
BEGIN DECLARE SECTION
CALL
CLOSE
COMMENT ON
COMMIT
Compound SQL (Embedded)
CONNECT (Type 1)
CONNECT (Type 2)
CREATE ALIAS
CREATE BUFFERPOOL
CREATE DISTINCT TYPE
CREATE EVENT MONITOR
CREATE FUNCTION
CREATE FUNCTION (External Scalar)
CREATE FUNCTION (External Table)
CREATE FUNCTION (OLE DB External Table)
CREATE FUNCTION (Source or Template)
CREATE FUNCTION (SQL Scalar, Table or Row)
CREATE FUNCTION MAPPING
CREATE INDEX
CREATE INDEX EXTENSION
CREATE METHOD
CREATE NICKNAME
CREATE NODEGROUP
CREATE PROCEDURE
CREATE SCHEMA
CREATE SERVER
CREATE TABLE
CREATE TABLESPACE
CREATE TRANSFORM
CREATE TRIGGER
CREATE TYPE (Structured)
CREATE TYPE MAPPING
CREATE USER MAPPING
CREATE VIEW
CREATE WRAPPER
DECLARE CURSOR
DECLARE GLOBAL TEMPORARY TABLE
DELETE
DESCRIBE
DISCONNECT
DROP
END DECLARE SECTION
EXECUTE
EXECUTE IMMEDIATE
EXPLAIN
FETCH
FLUSH EVENT MONITOR
FREE LOCATOR
GRANT (Database Authorities)
GRANT (Index Privileges)
GRANT (Package Privileges)
GRANT (Schema Privileges)
GRANT (Server Privileges)
GRANT (Table, View, or Nickname Privileges)
GRANT (Table Space Privileges)
INCLUDE
INSERT
LOCK TABLE
OPEN
PREPARE
REFRESH TABLE
RELEASE (Connection)
RELEASE SAVEPOINT
RENAME TABLE
RENAME TABLESPACE
REVOKE (Database Authorities)
REVOKE (Index Privileges)
REVOKE (Package Privileges)
REVOKE (Schema Privileges)
REVOKE (Server Privileges)
REVOKE (Table, View, or Nickname Privileges)
REVOKE (Table Space Privileges)
ROLLBACK
SAVEPOINT
SELECT
SELECT INTO
SET CONNECTION
SET CURRENT DEFAULT TRANSFORM GROUP
SET CURRENT DEGREE
SET CURRENT EXPLAIN MODE
SET CURRENT EXPLAIN SNAPSHOT
SET CURRENT PACKAGESET
SET CURRENT QUERY OPTIMIZATION
SET CURRENT REFRESH AGE
SET EVENT MONITOR STATE
SET INTEGRITY
SET PASSTHRU
SET PATH
SET SCHEMA
SET SERVER OPTION
SET transition-variable
SIGNAL SQLSTATE
UPDATE
VALUES
VALUES INTO
WHENEVER
SQL Procedures
SQL Procedure Statement
ALLOCATE CURSOR Statement
Assignment Statement
ASSOCIATE LOCATORS Statement
CASE Statement
Compound Statement
FOR Statement
GET DIAGNOSTICS Statement
GOTO Statement
IF Statement
ITERATE Statement
LEAVE Statement
LOOP Statement
REPEAT Statement
RESIGNAL Statement
RETURN Statement
SIGNAL Statement
WHILE Statement
Appendix B. SQL Communications (SQLCA)
Viewing the SQLCA Interactively
SQLCA Field Descriptions
Order of Error Reporting
DB2 Enterprise - Extended Edition Usage of the SQLCA
Appendix C. SQL Descriptor Area (SQLDA)
Field Descriptions
Fields in the SQLDA Header
Fields in an Occurrence of a Base SQLVAR
Fields in an Occurrence of a Secondary SQLVAR
Effect of DESCRIBE on the SQLDA
SQLTYPE and SQLLEN
Unrecognized and Unsupported SQLTYPES
Packed Decimal Numbers
SQLLEN Field for Decimal
Appendix D. Catalog Views
Updatable Catalog Views
'Roadmap' to Catalog Views
'Roadmap' to Updatable Catalog Views
SYSIBM.SYSDUMMY1
SYSCAT.ATTRIBUTES
SYSCAT.BUFFERPOOLNODES
SYSCAT.BUFFERPOOLS
SYSCAT.CASTFUNCTIONS
SYSCAT.CHECKS
SYSCAT.COLAUTH
SYSCAT.COLCHECKS
SYSCAT.COLDIST
SYSCAT.COLOPTIONS
SYSCAT.COLUMNS
SYSCAT.CONSTDEP
SYSCAT.DATATYPES
SYSCAT.DBAUTH
SYSCAT.EVENTMONITORS
SYSCAT.EVENTS
SYSCAT.FULLHIERARCHIES
SYSCAT.FUNCDEP
SYSCAT.FUNCMAPOPTIONS
SYSCAT.FUNCMAPPARMOPTIONS
SYSCAT.FUNCMAPPINGS
SYSCAT.FUNCPARMS
SYSCAT.FUNCTIONS
SYSCAT.HIERARCHIES
SYSCAT.INDEXAUTH
SYSCAT.INDEXCOLUSE
SYSCAT.INDEXDEP
SYSCAT.INDEXES
SYSCAT.INDEXOPTIONS
SYSCAT.KEYCOLUSE
SYSCAT.NAMEMAPPINGS
SYSCAT.NODEGROUPDEF
SYSCAT.NODEGROUPS
SYSCAT.PACKAGEAUTH
SYSCAT.PACKAGEDEP
SYSCAT.PACKAGES
SYSCAT.PARTITIONMAPS
SYSCAT.PASSTHRUAUTH
SYSCAT.PROCEDURES
SYSCAT.PROCOPTIONS
SYSCAT.PROCPARMOPTIONS
SYSCAT.PROCPARMS
SYSCAT.REFERENCES
SYSCAT.REVTYPEMAPPINGS
SYSCAT.SCHEMAAUTH
SYSCAT.SCHEMATA
SYSCAT.SERVEROPTIONS
SYSCAT.SERVERS
SYSCAT.STATEMENTS
SYSCAT.TABAUTH
SYSCAT.TABCONST
SYSCAT.TABLES
SYSCAT.TABLESPACES
SYSCAT.TABOPTIONS
SYSCAT.TBSPACEAUTH
SYSCAT.TRIGDEP
SYSCAT.TRIGGERS
SYSCAT.TYPEMAPPINGS
SYSCAT.USEROPTIONS
SYSCAT.VIEWDEP
SYSCAT.VIEWS
SYSCAT.WRAPOPTIONS
SYSCAT.WRAPPERS
SYSSTAT.COLDIST
SYSSTAT.COLUMNS
SYSSTAT.FUNCTIONS
SYSSTAT.INDEXES
SYSSTAT.TABLES
Appendix E. Catalog Views For Use With Structured Types
'Roadmap' to Catalog Views
OBJCAT.INDEXES
OBJCAT.INDEXEXPLOITRULES
OBJCAT.INDEXEXTENSIONDEP
OBJCAT.INDEXEXTENSIONMETHODS
OBJCAT.INDEXEXTENSIONPARMS
OBJCAT.INDEXEXTENSIONS
OBJCAT.PREDICATESPECS
OBJCAT.TRANSFORMS
Appendix F. Federated Systems
Server Types
SQL Options for Federated Systems
Column Options
Function Mapping Options
Server Options
User Options
Default Data Type Mappings
Default Type Mappings between DB2 and DB2 Universal Database for OS/390 (and DB2 for MVS/ESA) Data Sources
Default Type Mappings between DB2 and 2 Universal Database for AS/400 (and DB2 for OS/400) Data Sources
Default Type Mappings between DB2 and Oracle Data Sources
Default Type Mappings between DB2 and DB2 for VM and VSE (and SQL/DS) Data Sources
Pass-Through Facility Processing
SQL Processing in Pass-Through Sessions
Considerations and Restrictions
Appendix G. Sample Database Tables
The Sample Database
To Create the Sample Database
To Erase the Sample Database
CL_SCHED Table
DEPARTMENT Table
EMPLOYEE Table
EMP_ACT Table
EMP_PHOTO Table
EMP_RESUME Table
IN_TRAY Table
ORG Table
PROJECT Table
SALES Table
STAFF Table
STAFFG Table
Sample Files with BLOB and CLOB Data Type
Quintana Photo
Quintana Resume
Nicholls Photo
Nicholls Resume
Adamson Photo
Adamson Resume
Walker Photo
Walker Resume
Appendix H. Reserved Schema Names and Reserved Words
Reserved Schemas
Reserved Words
IBM SQL Reserved Words
ISO/ANS SQL92 Reserved Words
Appendix I. Comparison of Isolation Levels
Appendix J. Interaction of Triggers and Constraints
Appendix K. Explain Tables and Definitions
EXPLAIN_ARGUMENT Table
EXPLAIN_INSTANCE Table
EXPLAIN_OBJECT Table
EXPLAIN_OPERATOR Table
EXPLAIN_PREDICATE Table
EXPLAIN_STATEMENT Table
EXPLAIN_STREAM Table
ADVISE_INDEX Table
ADVISE_WORKLOAD Table
Table Definitions for Explain Tables
EXPLAIN_ARGUMENT Table Definition
EXPLAIN_INSTANCE Table Definition
EXPLAIN_OBJECT Table Definition
EXPLAIN_OPERATOR Table Definition
EXPLAIN_PREDICATE Table Definition
EXPLAIN_STATEMENT Table Definition
EXPLAIN_STREAM Table Definition
ADVISE_INDEX Table Definition
ADVISE_WORKLOAD Table Definition
Appendix L. Explain Register Values
Appendix M. Recursion Example: Bill of Materials
Example 1: Single Level Explosion
Example 2: Summarized Explosion
Example 3: Controlling Depth
Appendix N. Exception Tables
Rules for Creating an Exception Table
Handling Rows in the Exception Tables
Querying the Exception Tables
Appendix O. Japanese and Traditional-Chinese EUC Considerations
Language Elements
Characters
Tokens
Identifiers
Data Types
Assignments and Comparisons
Rules for Result Data Types
Rules for String Conversions
Constants
Functions
Expressions
Predicates
Functions
LENGTH
SUBSTR
TRANSLATE
VARGRAPHIC
Statements
CONNECT
PREPARE
Appendix P. BNF Specifications for DATALINKs
Appendix Q. Using the DB2 Library
DB2 PDF Files and Printed Books
DB2 Information
Printing the PDF Books
Ordering the Printed Books
DB2 Online Documentation
Accessing Online Help
Viewing Information Online
Using DB2 Wizards
Setting Up a Document Server
Searching Information Online