Table of Contents

Table of Contents

About DB2 UDB for AS/400 SQL Reference

Standards Compliance

Who should read the SQL Reference book

Assumptions Relating to Examples of SQL Statements

How to Read Syntax Diagrams

Conventions for Describing Mixed Data Values

What's new for V4R5 in the SQL Reference book

Relational Database

Structured Query Language

Extended Dynamic SQL

Interactive SQL

SQL Call Level Interface

Primary Keys and Unique Keys

Referential Integrity

Check Constraints

Packages and Access Plans

Application Processes, Concurrency, and Recovery

Isolation Level

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

Coded Character Sets and CCSIDs

Authorization and Privileges

Storage Structures

Language Elements

SQL Identifiers

System identifiers

Host Identifiers

Naming Conventions

Qualification of Unqualified Object Names

SQL Names and System Names: Special Considerations

Collections and the SQL Path

Authorization IDs and Authorization-Names

Character Strings

Character Subtypes

Graphic Strings

Graphic Subtypes

Large Objects (LOBs)

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

Distinct Type Assignments

Numeric Comparisons

String Comparisons

Conversion Rules for Comparison

Datetime Comparisons

Distinct Type Comparisons

Rules for Result Data Types

Binary String Operands

Character and Graphic String Operands

Numeric Operands

Datetime Operands

DATALINK Operands

DISTINCT Type Operands

Conversion Rules for Operations That Combine Strings

Integer Constants

Floating-Point Constants

Decimal Constants

Binary-String Constants

Character-String Constants

Graphic-String Constants

Special Registers

CURRENT DATE or CURRENT_DATE

CURRENT PATH, CURRENT_PATH, or CURRENT FUNCTION PATH

CURRENT SERVER or CURRENT_SERVER

CURRENT TIME or CURRENT_TIME

CURRENT TIMESTAMP or CURRENT_TIMESTAMP

CURRENT TIMEZONE or CURRENT_TIMEZONE

Qualified Column Names

Correlation Names

Column Name Qualifiers to Avoid Ambiguity

Column Name Qualifiers in Correlated References

Unqualified Column Names

References to Host Variables

Host Variables in Dynamic SQL

References to LOB Host Variables

References to LOB Locator Variables

References to LOB File Reference Variables

Host Structures in C, C++, COBOL, PL/I, and RPG

Host Structure Arrays in C, C++, COBOL, PL/I, and RPG

Types of Functions

Function Resolution

Method of Finding the Best Fit

Function Invocation

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 Operands and Durations

Datetime Arithmetic in SQL

Precedence of Operations

CASE Expressions

CAST Specification

Basic Predicate

Quantified Predicate

BETWEEN Predicate

EXISTS Predicate

Search Conditions

Built-In Functions

Column Functions

VARIANCE or VAR

Scalar Functions

CHARACTER_LENGTH or CHAR_LENGTH

DOUBLE_PRECISION or DOUBLE

POSITION or POSSTR

SUBSTRING or SUBSTR

group-by-clause

Examples of a subselect

Examples of a fullselect

select-statement

common-table-expression

order-by-clause

read-only-clause

optimize-clause

isolation-clause

Examples of a select-statement

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

ADD unique-constraint

ADD referential-constraint

ADD check-constraint

Cascaded Effects

BEGIN DECLARE SECTION

CONNECT (Type 1)

CONNECT (Type 2)

CREATE COLLECTION

CREATE DISTINCT TYPE

CREATE FUNCTION

CREATE FUNCTION (Scalar)

Notes for SQL Functions

Notes for External Functions

CREATE FUNCTION (Sourced)

CREATE PROCEDURE

Notes for SQL Procedures

Notes for External Procedures

Notes for Java Procedures

column-definition

unique-constraint

referential-constraint

check-constraint

nodegroup-clause

Rules for System Name Generation

DECLARE PROCEDURE

DECLARE STATEMENT

DECLARE VARIABLE

END DECLARE SECTION

Parameter Marker Replacement

EXECUTE IMMEDIATE

multiple-row-fetch

GET DIAGNOSTICS

GRANT (Function or Procedure Privileges)

GRANT (Package Privileges)

GRANT (Table Privileges)

GRANT (User-Defined Type Privileges)

insert-multiple-rows

Parameter Marker Replacement

REVOKE (Function or Procedure Privileges)

REVOKE (Package Privileges)

REVOKE (Table Privileges)

REVOKE (User-Defined Type Privileges)

SET RESULT SETS

SET TRANSACTION

SQL Procedures and Functions

SQL routine body

SQL procedure statement

SQL control statements

assignment-statement

compound-statement

leave-statement

repeat-statement

return-statement

while-statement

Appendix A. SQL Limits

Appendix B. SQL Communication Area

Field Descriptions

INCLUDE SQLCA Declarations

Appendix C. SQL Descriptor Area (SQLDA)

Field Descriptions

Field Descriptions in an Occurrence of SQLVAR

Determining How Many SQLVAR Occurrences are Needed

SQLTYPE and SQLLEN

SQLDATA or SQLNAME

Unrecognized and Unsupported SQLTYPES

INCLUDE SQLDA Declarations

For ILE RPG/400

Appendix D. Reserved Words

Appendix E. CCSID Values

Appendix F. Considerations for Using Distributed Relational Database

CONNECT (Type 1) and CONNECT (Type 2) Differences

Determining the CONNECT rules that apply

Connecting to Application Servers That Only Support Remote Unit of Work

Appendix G. DB2 UDB for AS/400 Catalog Views

Bibliography

[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]