DB2
SQL Reference
for Cross-Platform Development
Ve r s i o n 3
Product Information
This book contains information about the following relational database products:
v Version 9 of DB2 for z/OS
v Version 5 Release 4 of DB2 for i5/OS
v Version 9 of DB2 for the Linux, UNIX and Windows Platforms
DB2
SQL Reference
for Cross-Platform Development
Ve r s i o n 3
Before using this information and the products it supports, be sure to read the general information under “Notices” on page 895.
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.
© Copyright International Business Machines Corporation 1982, 2008. All rights reserved.
US Government Users Restricted Rights – Use, duplication or disclosure restricted by GSA ADP Schedule Contract
with IBM Corp.
Contents
About this book . . . . . . . . . . .ix
Who should read this book . . . . . . . . .ix
How to use this book . . . . . . . . . . .ix
Assumptions relating to examples of SQL
statements . . . . . . . . . . . . . .x
How to read the syntax diagrams . . . . . .x
Conventions used in this manual . . . . . .xii
SQL accessibility . . . . . . . . . . . xiii
Related documentation . . . . . . . . . xiii
What’s new for this book . . . . . . . . . .xv
Chapter 1. Concepts . . . . . . . . .1
Relational database . . . . . . . . . . . .1
Structured query language . . . . . . . . . .1
Static SQL . . . . . . . . . . . . . .1
Dynamic SQL . . . . . . . . . . . . .1
Interactive SQL . . . . . . . . . . . .2
SQL call level interface and open database
connectivity . . . . . . . . . . . . .2
Java database connectivity and embedded SQL for
Java programs . . . . . . . . . . . . .2
Schemas . . . . . . . . . . . . . . . .3
Tables . . . . . . . . . . . . . . . .3
Keys . . . . . . . . . . . . . . . .3
Constraints . . . . . . . . . . . . . .4
Indexes . . . . . . . . . . . . . . .7
Triggers . . . . . . . . . . . . . . .7
Views . . . . . . . . . . . . . . . . .9
User-defined types . . . . . . . . . . . .9
Aliases . . . . . . . . . . . . . . . .9
Packages and access plans . . . . . . . . .10
Routines . . . . . . . . . . . . . . .10
Functions . . . . . . . . . . . . . .10
Procedures . . . . . . . . . . . . .10
Sequences . . . . . . . . . . . . . . .12
Authorization, privileges and object ownership . .13
Catalog . . . . . . . . . . . . . . . .14
Application processes, concurrency, and recovery . .15
Locking, commit, and rollback . . . . . . .15
Unit of work . . . . . . . . . . . . .15
Rolling back work . . . . . . . . . . .16
Isolation level . . . . . . . . . . . . .18
Repeatable read . . . . . . . . . . . .18
Read stability . . . . . . . . . . . . .19
Cursor stability . . . . . . . . . . . .19
Uncommitted read . . . . . . . . . . .19
Comparison of isolation levels . . . . . . .20
Storage structures . . . . . . . . . . . .21
Character conversion . . . . . . . . . . .21
Character sets and code pages . . . . . . .24
Coded character sets and CCSIDs . . . . . .25
Default CCSID . . . . . . . . . . . .25
Distributed relational database . . . . . . . .26
Application servers . . . . . . . . . . .26
CONNECT (Type 1) and CONNECT (Type 2) . .27
Remote unit of work . . . . . . . . . .27
Application-directed distributed unit of work . .29
Data representation considerations . . . . . .32
Chapter 2. Language elements . . . .33
Characters . . . . . . . . . . . . . . .33
Tokens . . . . . . . . . . . . . . . .34
Identifiers . . . . . . . . . . . . . . .36
SQL identifiers . . . . . . . . . . . .36
Host identifiers . . . . . . . . . . . .36
Naming conventions . . . . . . . . . . .37
SQL path . . . . . . . . . . . . . .42
Qualification of unqualified object names . . .42
Aliases . . . . . . . . . . . . . . . .44
Authorization IDs and authorization names . . .45
Example . . . . . . . . . . . . . .45
Data types . . . . . . . . . . . . . . .46
Nulls . . . . . . . . . . . . . . .47
Numbers . . . . . . . . . . . . . .47
Character strings . . . . . . . . . . .48
Character encoding schemes . . . . . . . .49
Graphic strings . . . . . . . . . . . .50
Graphic encoding schemes . . . . . . . .51
Binary strings . . . . . . . . . . . .51
Large objects . . . . . . . . . . . . .51
Datetime values . . . . . . . . . . . .53
User-defined types . . . . . . . . . . .55
Promotion of data types . . . . . . . . . .58
Casting between data types . . . . . . . . .59
Assignments and comparisons . . . . . . . .63
Numeric assignments . . . . . . . . . .64
String assignments . . . . . . . . . . .65
Datetime assignments . . . . . . . . . .67
Distinct type assignments . . . . . . . . .68
Assignments to LOB locators . . . . . . .69
Numeric comparisons . . . . . . . . . .70
String comparisons . . . . . . . . . . .70
Datetime comparisons . . . . . . . . . .72
Distinct type comparisons . . . . . . . .72
Rules for result data types . . . . . . . . .74
Numeric operands . . . . . . . . . . .74
Character and graphic string operands . . . .75
Binary string operands . . . . . . . . .76
Datetime operands . . . . . . . . . . .76
Distinct type operands . . . . . . . . . .76
Conversion rules for operations that combine strings 77
Constants . . . . . . . . . . . . . . .79
Integer constants . . . . . . . . . . .79
Decimal constants . . . . . . . . . . .79
Floating-point constants . . . . . . . . .79
Character-string constants . . . . . . . .80
Graphic-string constants . . . . . . . . .80
Decimal point . . . . . . . . . . . .81
Special registers . . . . . . . . . . . . .82
CURRENT DATE . . . . . . . . . . .82
© Copyright IBM Corp. 1982, 2008 iii
