IBM Books

SQL Reference


Table of Contents

Chapter 1. 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
  • 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 ]