Table of Contents

Table of Contents

About DB2 UDB for AS/400 SQL Programming Concepts

Who should read this book

Assumptions relating to examples of SQL statements

How to interpret syntax diagrams in this guide

What's new in the V4R5 version of the SQL programming concepts information

Introduction to DB2 UDB for AS/400 Structured Query Language

SQL relational database and system terminology

Types of SQL statements

Tables, Rows, and Columns

Stored Procedures

User-defined functions

User-defined type

Application program objects

User source file member

Output source file member

Service program

Getting Started with SQL

Starting interactive SQL

Creating an SQL collection

Example: Creating the SQL Collection (SAMPLECOLL)

Creating and using a table

Example: Creating a table (INVENTORY_LIST)

Creating the Supplier Table (SUPPLIERS)

Using the LABEL ON statement

Inserting information into a table

Example: Inserting information into a table (INVENTORY_LIST)

Getting information from a single table

Getting information from more than one table

Changing information in a table

Example: Changing information in a table

Deleting information from a table

Example: Deleting information from a table (INVENTORY_LIST)

Creating and using a view

Example: Creating a view on a single table

Example: Creating a view combining data from more than one table

Basic Concepts and Techniques

Using basic SQL statements and clauses

Inserting rows using the INSERT statement

Changing data in a table using the UPDATE statement

Removing rows from a table using the DELETE statement

Querying data using the SELECT INTO statement

Data retrieval errors

The SELECT clause

Specifying a search condition using the WHERE clause

The GROUP BY clause

ORDER BY clause

Null Values to indicate absence of column values in a row

Special registers in SQL statements

Date, Time, and Timestamp data types

Specifying current date and time values

Date/Time arithmetic

Creating and using ALIAS names

Creating descriptive labels using the LABEL ON statement

Describing an SQL object using COMMENT ON

Getting comments after running a COMMENT ON statement

Sort sequences in SQL

Sort sequence used with ORDER BY and record selection

Record selection

Sort sequence and views

Sort Sequence and the CREATE INDEX Statement

Sort sequence and constraints

Using a Cursor

Types of cursors

Scrollable cursor

Example of using a cursor

Step 1: Define the cursor

Step 2: Open the cursor

Step 3: Specify what to do when end-of-data is reached

Step 4: Retrieve a row using a cursor

Step 5a: Update the current row

Step 5b: Delete the current row

Step 6: Close the cursor

Using the multiple-row FETCH statement

Multiple-row FETCH using a host structure array

Multiple-row FETCH using a row storage area

Unit of work and open cursors

Advanced Coding Techniques

Advanced insert techniques

Inserting rows into a table using a Select-Statement

Inserting multiple rows in a table with the blocked INSERT statement

Advanced update techniques

Preventing duplicate rows

Performing complex search conditions

Keywords for use in search conditions

Joining data from more than one table

Left Outer Join

Multiple join types in one statement

Specifying itermediate join tables using table expressions

Using the UNION keyword to combine subselects

Specifying UNION ALL

Subqueries in SELECT statements

Subqueries and search conditions

How subqueries are used

Using subqueries with UPDATE and DELETE

Notes on using subqueries

Correlated subqueries

Using correlated subqueries in an UPDATE statement

Using correlated subqueries in a DELETE statement

Notes on using correlated subqueries

Changing a table definition

Adding a column

Changing a column

Allowable conversions

Deleting a column

Order of operations for ALTER TABLE statement

Creating and using views

Catalogs in database design

Getting catalog information about a table

Getting catalog information about a column

Data Integrity

Adding and using check constraints

Referential integrity

Adding or dropping referential constraints

Removing referential constraints

Inserting into tables with referential constraints

Updating tables with referential constraints

Deleting from tables with referential constraints

WITH CHECK OPTION on a View

WITH CASCADED CHECK OPTION

WITH LOCAL CHECK OPTION

DB2 UDB for AS/400 trigger support

Stored Procedures

Creating a procedure

Defining an external procedure

Defining an SQL procedure

Invoking a stored procedure

Using CALL Statement where procedure definition exists

Using Embedded CALL Statement where no procedure definition exists

Using Embedded CALL statement with an SQLDA

Using Dynamic CALL Statement where no CREATE PROCEDURE exists

Parameter passing conventions for stored procedures

Indicator variables and stored procedures

Returning a completion status to the calling program

Examples of CALL statements

Example 1: ILE C and PL/I procedures called from ILE C applications

Considerations for stored procedures that are written in Java

Coding a Java stored procedure that uses the JAVA parameter

Coding a Java stored procedure using the DB2GENERAL parameter style

Restrictions on Java stored procedures

Using the Object-Relational Capabilities

Why use the DB2 object extensions?

DB2 approach to supporting objects

Using Large Objects (LOBs)

Understanding large object data types (BLOB, CLOB, DBCLOB)

Understanding large object locators

Example: Using a locator to work with a CLOB value

Indicator variables and LOB locators

LOB file reference variables

Example: Extracting a document to a file

Example: Inserting data into a CLOB column

Display layout of LOB columns

Journal entry layout of LOB columns

User-defined functions (UDF)

Implementing UDFs

Registering UDFs

Examples: Registering UDFs

User-defined distinct types (UDT)

Resolving unqualified UDTs

Examples: Using CREATE DISTINCT TYPE

Defining tables with UDTs

Manipulating UDTs

Examples of manipulating UDTs

Synergy between UDTs, UDFs, and LOBs

Combining UDTs, UDFs, and LOBs

Examples of complex applications

Using DataLinks

NO LINK CONTROL

FILE LINK CONTROL (with File System Permissions)

FILE LINK CONTROL (with Database Permissions)

Commands used for working with DataLinks

Writing User-Defined Functions (UDFs)

UDF runtime environment

Length of time that the UDF runs

Threads considerations

Parallel processing

Writing function code

Writing UDFs as SQL functions

Writing UDFs as external functions

Examples of UDF code

Example: Square of a number UDF

Example: Counter

Dynamic SQL Applications

Designing and running a dynamic SQL application

Processing non-SELECT statements

CCSID of dynamic SQL statements

Using the PREPARE and EXECUTE statements

Processing SELECT statements and using an SQLDA

Fixed-list SELECT statements

Varying-list Select-statements

SQL Descriptor Area (SQLDA)

Example: Select-statement for allocating storage for SQLDA

Parameter markers

Use of dynamic SQL through client interfaces

Accessing data with Java

Accessing data with Domino

Accessing data with Open Database Connectivity (ODBC)

Using Interactive SQL

Basic functions of interactive SQL

Starting interactive SQL

Using statement entry function

Using the list selection function

Session services description

Exiting interactive SQL

Using an existing SQL session

Recovering an SQL session

Accessing remote databases with interactive SQL

Using the SQL Statement Processor

Execution of statements after errors occur

Commitment control in the SQL statement processor

Schemas in the SQL Statement Processor

Source member listing for the SQL statement processor

DB2 UDB for AS/400 Data Protection

Security for SQL objects

Authorization ID

Commitment control

Atomic operations

Damage tolerance

Catalog integrity

User auxiliary storage pool (ASP)

Testing SQL Statements in Application Programs

Establishing a test environment

Designing a test data structure

Testing your SQL application programs

Program debug phase

Performance verification phase

Solving Common Database Problems

Paging through retrieved data

Retrieving in reverse order

Establishing position at the end of a table

Adding data to the end of a table

Updating data as it is retrieved from a table

Updating data previously retrieved

Changing the table definition

Distributed Relational Database Function

DB2 UDB for AS/400 distributed relational database support

DB2 UDB for AS/400 distributed relational database example program

SQL package support

Valid SQL statements in an SQL package

Considerations for creating an SQL package

CCSID considerations for SQL

Connection management and activation groups

Connections and conversations

Source code for PGM1:

Source code for PGM2:

Source code for PGM3:

Multiple connections to the same relational database

Implicit connection management for the default activation group

Implicit connection management for nondefault activation groups

Distributed support

Determining connection type

Connect and commitment control restrictions

Determining connection status

Distributed unit of work connection considerations

Ending connections

Distributed unit of work

Managing distributed unit of work connections

Cursors and prepared statements

Application requester driver programs

Problem handling

DB2 UDB for AS/400 Sample Tables

Department Table (CORPDATA.DEPARTMENT)

Employee Table (CORPDATA.EMPLOYEE)

Employee to Project Activity Table (CORPDATA.EMP_ACT)

Project Table (CORPDATA.PROJECT)

Class Schedule Table (CL_SCHED)

In Tray Table (IN_TRAY)

SQLCODEs and SQLSTATEs

SQLCODE and SQLSTATE Descriptions

Positive SQLCODEs

Negative SQLCODEs

DB2 UDB for AS/400 CL Command Descriptions

CRTSQLPKG (Create Structured Query Language Package) Command

DLTSQLPKG (Delete Structured Query Language Package) Command

RUNSQLSTM (Run Structured Query Language Statement) Command

STRSQL (Start Structured Query Language) Command

Bibliography

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