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)
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
Character Conversion
Character Sets and Code Pages
Code Page Attributes
Authorization and Privileges
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
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
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
COUNT
COUNT_BIG
GROUPING
MAX
MIN
STDDEV
SUM
VARIANCE
Scalar Functions
ABS or ABSVAL
ACOS
ASCII
ASIN
ATAN
ATAN2
BIGINT
BLOB
CEIL or CEILING
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
LEFT
LENGTH
LN
LOCATE
LOG
LOG10
LONG_VARCHAR
LONG_VARGRAPHIC
LTRIM
MICROSECOND
MIDNIGHT_SECONDS
MINUTE
MOD
MONTH
MONTHNAME
NODENUMBER
NULLIF
PARTITION
POSSTR
POWER
QUARTER
RADIANS
RAISE_ERROR
RAND
REAL
REPEAT
REPLACE
RIGHT
ROUND
RTRIM
SECOND
SIGN
SIN
SMALLINT
SOUNDEX
SPACE
SQRT
SUBSTR
TABLE_NAME
TABLE_SCHEMA
TAN
TIME
TIMESTAMP
TIMESTAMP_ISO
TIMESTAMPDIFF
TRANSLATE
TRUNC or TRUNCATE
TYPE_ID
TYPE_NAME
TYPE_SCHEMA
UCASE
VALUE
VARCHAR
VARGRAPHIC
WEEK
YEAR
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 NODEGROUP
ALTER TABLE
ALTER TABLESPACE
ALTER TYPE (Structured)
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 (Sourced)
CREATE INDEX
CREATE NODEGROUP
CREATE PROCEDURE
CREATE SCHEMA
CREATE TABLE
CREATE TABLESPACE
CREATE TRIGGER
CREATE TYPE (Structured)
CREATE VIEW
DECLARE CURSOR
DELETE
DESCRIBE
DISCONNECT
DROP
END DECLARE SECTION
EXECUTE
EXECUTE IMMEDIATE
EXPLAIN
FETCH
FREE LOCATOR
GRANT (Database Authorities)
GRANT (Index Privileges)
GRANT (Package Privileges)
GRANT (Schema Privileges)
GRANT (Table or View 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 (Table or View Privileges)
ROLLBACK
SELECT
SELECT INTO
SET CONNECTION
SET CONSTRAINTS
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 PATH
SET SCHEMA
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 Extended Enterprise Edition Usage of the SQLCA
Appendix C. 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
Packed Decimal Numbers
UNRECOGNIZED AND UNSUPPORTED SQLTYPES
SQLLEN Field for Decimal
Appendix D. Catalog Views
Updatable Catalog Views
"Roadmap" to Catalog Views
"Roadmap" to Updatable Catalog Views
SYSCAT.BUFFERPOOLS
SYSCAT.BUFFERPOOLNODES
SYSCAT.CHECKS
SYSCAT.COLAUTH
SYSCAT.COLCHECKS
SYSCAT.COLDIST
SYSCAT.COLUMNS
SYSCAT.CONSTDEP
SYSCAT.DATATYPES
SYSCAT.DBAUTH
SYSCAT.EVENTMONITORS
SYSCAT.EVENTS
SYSCAT.FUNCPARMS
SYSCAT.FUNCTIONS
SYSCAT.INDEXAUTH
SYSCAT.INDEXES
SYSCAT.KEYCOLUSE
SYSCAT.NODEGROUPDEF
SYSCAT.NODEGROUPS
SYSCAT.PACKAGEAUTH
SYSCAT.PACKAGEDEP
SYSCAT.PACKAGES
SYSCAT.PARTITIONMAPS
SYSCAT.PROCEDURES
SYSCAT.PROCPARMS
SYSCAT.REFERENCES
SYSCAT.SCHEMAAUTH
SYSCAT.SCHEMATA
SYSCAT.STATEMENTS
SYSCAT.TABAUTH
SYSCAT.TABCONST
SYSCAT.TABLES
SYSCAT.TABLESPACES
SYSCAT.TRIGDEP
SYSCAT.TRIGGERS
SYSCAT.VIEWDEP
SYSCAT.VIEWS
SYSSTAT.COLDIST
SYSSTAT.COLUMNS
SYSSTAT.FUNCTIONS
SYSSTAT.INDEXES
SYSSTAT.TABLES
Appendix E. Catalog Views For Use With Structured Types
Updatable Catalog Views For Use With Structured Types
"Roadmap" to Catalog Views for Structured Types
"Roadmap" to Updatable Catalog Views For Structured Types
OBJCAT.ATTRIBUTES
OBJCAT.CHECKS
OBJCAT.COLCHECKS
OBJCAT.COLUMNS
OBJCAT.CONSTDEP
OBJCAT.DATATYPES
OBJCAT.FUNCPARMS
OBJCAT.FUNCTIONS
OBJCAT.HIERARCHIES
OBJCAT.INDEXES
OBJCAT.KEYCOLUSE
OBJCAT.PACKAGEDEP
OBJCAT.REFERENCES
OBJCAT.TABCONST
OBJCAT.TABLES
OBJCAT.TRIGDEP
OBJCAT.TRIGGERS
OBJCAT.VIEWDEP
OBJSTAT.TABLES
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
System Catalog Tables/Views
System Catalog Views
System Catalog Tables
Unique Table Identification
Application Programming
NS, NW and NX Locks
CREATE TABLE NOT LOGGED INITIALLY
DB2 Call Level Interface (DB2 CLI) Defaults
Obsolete DB2 CLI Keywords
DB2 CLI SQLSTATEs
DB2 CLI Mixing Embedded SQL, Without CONNECT RESET
DB2 CLI Use of VARCHAR FOR BIT DATA
DB2 CLI Data Conversion Values for SQLGetInfo
DB2 CLI/ODBC Configuration Keyword Defaults
Obsolete DB2 CLI/ODBC Configuration Keywords
DB2 CLI SQLSTATEs
Stored Procedure Catalog Table
PREP Command - LANGLEVEL
Change to SMALLINT Constants
Down-level Client and Distinct Types Sourced on BIGINT
Error Handling
Maximum Number of Sections in a Package
Bind Warnings
Bind Options
PREP with BINDFILE
Varchar Structures in COBOL
Incompatible APIs
Supported Level of JDBC
Calling Convention for Java Stored Procedures and UDFs
Java Runtime Environment
Obsolete System Monitor Requests for DB2 PE Version 1.2
SQL
Updating Partitioning Key Columns
Column NGNAME
Node Number Temporary Space Usage
Authorities for Create and Drop Nodegroups
Target Map in REDISTRIBUTE NODEGROUP
Node Group for Create Table
Revoking CONTROL on Tables or Views
High Level Qualifiers for Objects in DB2 Version 5
Inoperative VIEWs
Unusable VIEWs
SQLCODE Changes
WITH CHECK OPTION on CREATE VIEW
SQLSTATE Changes
FOR BIT DATA Comparisons
Code Page Conversion
Isolation Levels and Blocking All
ORDER BY Temporary Space Usage
Using Quotes in SQL Statements
Database Security and Tuning
GROUP Authorizations
Authentication Type
SYSADM Groups
Security Enhancements
Obsolete Profile Registry and Environment Variables
Utilities and Tools
Executable Name Changes
Backup and Restore - BUFF_SIZE Parameter
Backup and Restore - Changes Only Option
Backup and Restore - User Exits
Backup and Restore - Authority
Import - IMPORT REPLACE Option
LOAD TERMINATE
REORG - Alternate Path Option
Connectivity and Coexistence
Distributed Transaction Processing - Connect Type
Distributed Transaction Processing - SQLERRD Changes
DDCS - SQLJSETP
DDCS - DDCSSETP
DDCS - SQLJTRC.CMD
DDCS - SQLJBIND.CMD
APPC and APPN Nodes
Configuration Parameters
ADSM_PASSWORD
Agent Pool Size (NUM_POOLAGENTS)
MAXDARI and MAXCAGENTS
LOGFILSIZ
PCKCACHEFILSIZ
APPLHEAPSZ and APP_CTL_HEAP_SZ
BUFFPAGE and Multiple Buffer Pools
NEWLOGPATH
MULTIPAGE_ALLOC
EXTENTSIZE vs SEGPAGES
LOCKLIST
BUFFPAGE and SORTHEAP
Numeric Values for Database Manager Configuration Tokens
Numeric Values for Database Manager Configuration Tokens
New Generic Out-of-Range Return Codes
Segments versus 4KB Pages
Obsolete Database Configuration Parameters
Obsolete Database Manager Configuration Parameters
DB2_MMAP_READ and DB2_MMAP_WRITE
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
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
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
SmartGuides
Online Help
DB2 Books
Viewing Online Books
Searching Online Books
Printing the PostScript Books
Ordering the Printed DB2 Books
Information Center
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 ]