SQL Reference
Chapter 1. Introduction
Chapter 2. Concepts
Relational Database
Structured Query Language (SQL)
Embedded SQL
Static SQL
Dynamic SQL
DB2 Call Level Interface (CLI)
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
Chapter 3. 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
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 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
Functions
Function Resolution
Function Invocation
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
Datetime Operations and Durations
Datetime Arithmetic in SQL
Precedence of Operations
CASE Expressions
CAST Specifications
Dereference Operations
Predicates
Basic Predicate
Quantified Predicate
BETWEEN Predicate
EXISTS Predicate
IN Predicate
LIKE Predicate
NULL Predicate
TYPE Predicate
Search Conditions
Chapter 4. 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
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
YEAR
Table Functions
SQLCACHE_SNAPSHOT
User-Defined Functions
Chapter 5. 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
Chapter 6. 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
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 MAPPING
CREATE INDEX
CREATE NICKNAME
CREATE NODEGROUP
CREATE PROCEDURE
CREATE SCHEMA
CREATE SERVER
CREATE TABLE
CREATE TABLESPACE
CREATE TRIGGER
CREATE TYPE (Structured)
CREATE TYPE MAPPING
CREATE USER MAPPING
CREATE VIEW
CREATE WRAPPER
DECLARE CURSOR
DELETE
DESCRIBE
DISCONNECT
DROP
END DECLARE SECTION
EXECUTE
EXECUTE IMMEDIATE
EXPLAIN
FETCH
FREE LOCATOR
FLUSH EVENT MONITOR
GRANT (Database Authorities)
GRANT (Index Privileges)
GRANT (Package Privileges)
GRANT (Schema Privileges)
GRANT (Server Privileges)
GRANT (Table, View, or Nickname Privileges)
INCLUDE
INSERT
LOCK TABLE
OPEN
PREPARE
REFRESH TABLE
RELEASE
RENAME TABLE
REVOKE (Database Authorities)
REVOKE (Index Privileges)
REVOKE (Package Privileges)
REVOKE (Schema Privileges)
REVOKE (Server Privileges)
REVOKE (Table, View, or Nickname Privileges)
ROLLBACK
SELECT
SELECT INTO
SET CONNECTION
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 PASSTHRU RESET
SET PATH
SET SCHEMA
SET SERVER OPTION
SET transition-variable
SIGNAL SQLSTATE
UPDATE
VALUES
VALUES INTO
WHENEVER
Appendix A. SQL Limits
Appendix B. SQL Communication Area (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. Federated Systems: Server Types; Options; Default Type Mappings; Pass-Through Guidelines
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 F. Sample Tables
The Sample Database
To Install 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 G. Reserved Schema Names and Reserved Words
Reserved Schemas
Reserved Words
IBM SQL Reserved Words
ISO/ANS SQL92 Reserved Words
Appendix H. Comparison of Isolation Levels
Appendix I. Interaction of Triggers and Constraints
Appendix J. Incompatibilities Between Releases
DB2 Universal Database Planned Incompatibilities
Read-only Views in a Future Version of DB2 Universal Database
PK_COLNAMES and FK_COLNAMES in a Future Version of DB2 Universal Database
COLNAMES No Longer Available in a Future Version of DB2 Universal Database
DB2 Universal Database Version 6 Incompatibilities
System Catalog Views
Application Programming
SQL
Database Security and Tuning
Utilities and Tools
Connectivity and Coexistence
Configuration Parameters
DB2 Universal Database Version 5 Incompatibilities
System Catalog Views
Application Programming
SQL
Database Security and Tuning
Utilities and Tools
Connectivity and Coexistence
Configuration Parameters
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. How the DB2 Library Is Structured
Completing Tasks with SmartGuides
Accessing Online Help
DB2 Information - Hardcopy and Online
Viewing Online Information
Accessing Information with the Information Center
Setting Up a Document Server
Searching Online Information
Printing the PostScript Books
Ordering the Printed Books
Appendix Q. Notices
Trademarks
Trademarks of Other Companies
Appendix R. Contacting IBM
Index
[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]
[ DB2 List of Books |
Search the DB2 Books ]