AS/400e IBM

DB2 UDB for AS/400 SQL Programming

Version 4

RBAF-Y000-00
AS/400e IBM

DB2 UDB for AS/400 SQL Programming

Version 4

RBAF-Y000-00
© Copyright International Business Machines Corporation 1997, 1999. All rights reserved.
Note to U.S. Government Users — Documentation related to restricted rights — Use, duplication or disclosure is
subject to restrictions set forth in GSA ADP Schedule Contract with IBM Corp.
Contents
About DB2 UDB for AS/400 SQL Programming. . . . . . . . . . . . xvii
Who should read this book . . . . . . . . . . . . . . . . . . . . xvii
Assumptions Relating to Examples of SQL Statements . . . . . . . . . xvii
How to Interpret Syntax Diagrams in this Guide . . . . . . . . . . . xviii
AS/400 Operations Navigator . . . . . . . . . . . . . . . . . . . xix
Installing Operations Navigator. . . . . . . . . . . . . . . . . . xx
How this book has changed . . . . . . . . . . . . . . . . . . . . xxi
Prerequisite and related information . . . . . . . . . . . . . . . . . xxi
How to send your comments . . . . . . . . . . . . . . . . . . . xxi

Chapter 1. Introduction to DB2 UDB for AS/400 Structured Query

Language . . . . . . . . . . . . . . . . . . . . . . . . . 1
SQL Concepts . . . . . . . . . . . . . . . . . . . . . . . . . 1
Relational Database and Terminology . . . . . . . . . . . . . . . 3
Types of SQL Statements . . . . . . . . . . . . . . . . . . . 4
SQL Objects . . . . . . . . . . . . . . . . . . . . . . . . . 5
Collections . . . . . . . . . . . . . . . . . . . . . . . . . 6
Tables, Rows, and Columns. . . . . . . . . . . . . . . . . . . 6
Aliases . . . . . . . . . . . . . . . . . . . . . . . . . . 7
Views . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
Indexes . . . . . . . . . . . . . . . . . . . . . . . . . . 8
Constraints . . . . . . . . . . . . . . . . . . . . . . . . . 8
Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . 8
Stored Procedures . . . . . . . . . . . . . . . . . . . . . . 9
User-defined functions . . . . . . . . . . . . . . . . . . . . . 9
Packages . . . . . . . . . . . . . . . . . . . . . . . . . 9
Application Program Objects . . . . . . . . . . . . . . . . . . . 9
User Source File Member . . . . . . . . . . . . . . . . . . . 11
Output Source File Member . . . . . . . . . . . . . . . . . . . 11
Program . . . . . . . . . . . . . . . . . . . . . . . . . . 11
Package . . . . . . . . . . . . . . . . . . . . . . . . . . 12
Module . . . . . . . . . . . . . . . . . . . . . . . . . . 12
Service Program . . . . . . . . . . . . . . . . . . . . . . . 12

Chapter 2. Getting Started with SQL . . . . . . . . . . . . . . . . 13

Starting Interactive SQL . . . . . . . . . . . . . . . . . . . . . 13
Creating an SQL Collection . . . . . . . . . . . . . . . . . . . . 13
Example: Creating the SQL Collection (SAMPLECOLL) . . . . . . . . 13
Creating and Using a Table . . . . . . . . . . . . . . . . . . . . 14
Example: Creating a Table (INVENTORY_LIST) . . . . . . . . . . . 14
Creating the Supplier Table (SUPPLIERS) . . . . . . . . . . . . . 16
Using the LABEL ON Statement . . . . . . . . . . . . . . . . . . 16
Inserting Information into a Table . . . . . . . . . . . . . . . . . . 18
Example: Inserting Information into a Table (INVENTORY_LIST) . . . . . 18
Getting Information from a Single Table . . . . . . . . . . . . . . . 20
Getting Information from More Than One Table. . . . . . . . . . . . . 23
Changing Information in a Table . . . . . . . . . . . . . . . . . . 25
Example: Changing Information in a Table . . . . . . . . . . . . . 25
Deleting Information from a Table. . . . . . . . . . . . . . . . . . 28
Example: Deleting Information from a Table (INVENTORY_LIST) . . . . . 28
Creating and Using a View . . . . . . . . . . . . . . . . . . . . 28
Example: Creating a view on a single table . . . . . . . . . . . . . 29
Example: Creating a view combining data from more than one table . . . . 30

© Copyright IBM Corp. 1997, 1999 iii

 Chapter 3. Basic Concepts and Techniques . . . . . . . . . . . . . 31
Using Basic SQL Statements and Clauses . . . . . . . . . . . . . . 31
The INSERT Statement . . . . . . . . . . . . . . . . . . . . 31
The UPDATE Statement . . . . . . . . . . . . . . . . . . . . 33
The DELETE Statement . . . . . . . . . . . . . . . . . . . . 34
The SELECT INTO Statement . . . . . . . . . . . . . . . . . . 35
Data retrieval errors. . . . . . . . . . . . . . . . . . . . . . 36
The SELECT Clause . . . . . . . . . . . . . . . . . . . . . 38
The WHERE Clause . . . . . . . . . . . . . . . . . . . . . 38
The GROUP BY Clause . . . . . . . . . . . . . . . . . . . . 40
The HAVING Clause . . . . . . . . . . . . . . . . . . . . . 42
The ORDER BY Clause . . . . . . . . . . . . . . . . . . . . 43
Using Null Values . . . . . . . . . . . . . . . . . . . . . . . 45
Using Special Registers . . . . . . . . . . . . . . . . . . . . . 45
Using Date, Time, and Timestamp . . . . . . . . . . . . . . . . . 47
Specifying Current Date and Time Values. . . . . . . . . . . . . . 47
Date/Time Arithmetic . . . . . . . . . . . . . . . . . . . . . 47
Creating and using ALIAS names. . . . . . . . . . . . . . . . . . 48
Using LABEL ON. . . . . . . . . . . . . . . . . . . . . . . . 48
Using COMMENT ON . . . . . . . . . . . . . . . . . . . . . . 49
Getting Comments . . . . . . . . . . . . . . . . . . . . . . 50
Using Sort Sequence in SQL . . . . . . . . . . . . . . . . . . . 50
Sort Sequence Used with ORDER BY and Record Selection . . . . . . 50
ORDER BY . . . . . . . . . . . . . . . . . . . . . . . . . 51
Record selection . . . . . . . . . . . . . . . . . . . . . . . 52
Sort Sequence and Views . . . . . . . . . . . . . . . . . . . 53
Sort Sequence and the CREATE INDEX Statement . . . . . . . . . . 53
Sort Sequence and Constraints . . . . . . . . . . . . . . . . . 54

Chapter 4. Using a Cursor . . . . . . . . . . . . . . . . . . . . 55

Types of cursors . . . . . . . . . . . . . . . . . . . . . . . . 55
Serial cursor . . . . . . . . . . . . . . . . . . . . . . . . 55
Scrollable cursor . . . . . . . . . . . . . . . . . . . . . . . 55
Example of Using a Cursor . . . . . . . . . . . . . . . . . . . . 56
Step 1: Define the Cursor . . . . . . . . . . . . . . . . . . . 58
Step 2: Open the Cursor . . . . . . . . . . . . . . . . . . . . 59
Step 3: Specify What to Do When End-of-Data Is Reached . . . . . . . 59
Step 4: Retrieve a Row Using a Cursor . . . . . . . . . . . . . . 60
Step 5a: Update the Current Row . . . . . . . . . . . . . . . . 61
Step 5b: Delete the Current Row . . . . . . . . . . . . . . . . . 61
Step 6: Close the Cursor . . . . . . . . . . . . . . . . . . . . 62
Using the Multiple-Row FETCH Statement . . . . . . . . . . . . . . 62
Multiple-Row FETCH Using a Host Structure Array . . . . . . . . . . 63
Multiple-Row FETCH Using a Row Storage Area . . . . . . . . . . . 64
Unit of Work and Open Cursors . . . . . . . . . . . . . . . . . . 68

Chapter 5. Advanced Coding Techniques . . . . . . . . . . . . . . 69

Advanced Insert Techniques . . . . . . . . . . . . . . . . . . . 69
Inserting Rows into a Table Using a Select-Statement . . . . . . . . . 69
Using the Blocked Insert Statement . . . . . . . . . . . . . . . . 70
Advanced Update Techniques . . . . . . . . . . . . . . . . . . . 70
Preventing Duplicate Rows . . . . . . . . . . . . . . . . . . . . 71
Performing Complex Search Conditions . . . . . . . . . . . . . . . 72
Keywords for Use in Search Conditions . . . . . . . . . . . . . . 72
Joining Data from More Than One Table . . . . . . . . . . . . . . . 75
Inner Join . . . . . . . . . . . . . . . . . . . . . . . . . 75

iv DB2 UDB for AS/400 SQL Programming V4R4

 Left Outer Join . . . . . . . . . . . . . . . . . . . . . . . 76
Exception Join. . . . . . . . . . . . . . . . . . . . . . . . 77
Cross Join . . . . . . . . . . . . . . . . . . . . . . . . . 78
Using multiple join types in one statement . . . . . . . . . . . . . 78
Notes on Joins . . . . . . . . . . . . . . . . . . . . . . . 79
Using Table Expressions . . . . . . . . . . . . . . . . . . . . . 79
Using the UNION Keyword to Combine Subselects . . . . . . . . . . . 80
Specifying UNION ALL. . . . . . . . . . . . . . . . . . . . . 83
Using Subqueries . . . . . . . . . . . . . . . . . . . . . . . 84
Correlation . . . . . . . . . . . . . . . . . . . . . . . . . 85
Subqueries and Search Conditions . . . . . . . . . . . . . . . . 85
How Subqueries Are Used . . . . . . . . . . . . . . . . . . . 86
Using Subqueries with UPDATE and DELETE . . . . . . . . . . . . 88
Notes on Using Subqueries . . . . . . . . . . . . . . . . . . . 88
Correlated Subqueries . . . . . . . . . . . . . . . . . . . . . 88
Using Correlated Subqueries in an UPDATE Statement . . . . . . . . 91
Using Correlated Subqueries in a DELETE Statement . . . . . . . . . 92
Notes on Using Correlated Subqueries. . . . . . . . . . . . . . . 92
Changing a Table Definition . . . . . . . . . . . . . . . . . . . . 93
Adding a column . . . . . . . . . . . . . . . . . . . . . . . 93
Changing a column . . . . . . . . . . . . . . . . . . . . . . 93
Allowable Conversions. . . . . . . . . . . . . . . . . . . . . 93
Deleting a column . . . . . . . . . . . . . . . . . . . . . . 95
Order of operations for ALTER TABLE statement . . . . . . . . . . . 95
Creating and Using Views . . . . . . . . . . . . . . . . . . . . 95
Adding Indexes . . . . . . . . . . . . . . . . . . . . . . . . 96
Using the Catalog in Database Design . . . . . . . . . . . . . . . . 97
Getting Catalog Information about a Table . . . . . . . . . . . . . 97
Getting Catalog Information about a Column . . . . . . . . . . . . 98

Chapter 6. Data Integrity . . . . . . . . . . . . . . . . . . . . 99

Adding and Using Check Constraints . . . . . . . . . . . . . . . . 99
Referential Integrity . . . . . . . . . . . . . . . . . . . . . . . 99
Adding or dropping referential constraints . . . . . . . . . . . . . . 100
Removing Referential Constraints . . . . . . . . . . . . . . . . 102
Inserting into Tables with Referential Constraints . . . . . . . . . . . 102
Updating Tables with Referential Constraints . . . . . . . . . . . . 103
Deleting from Tables with Referential Constraints . . . . . . . . . . . 105
Check Pending . . . . . . . . . . . . . . . . . . . . . . . 107
WITH CHECK OPTION on a View . . . . . . . . . . . . . . . . . 108
WITH CASCADED CHECK OPTION . . . . . . . . . . . . . . . 108
WITH LOCAL CHECK OPTION . . . . . . . . . . . . . . . . . 109
DB2 UDB for AS/400 Trigger Support . . . . . . . . . . . . . . . . 111
Trigger Sample . . . . . . . . . . . . . . . . . . . . . . . 111

Chapter 7. Stored Procedures . . . . . . . . . . . . . . . . . . 117

Creating a Procedure . . . . . . . . . . . . . . . . . . . . . . 117
Defining an External Procedure . . . . . . . . . . . . . . . . . . 117
Defining an SQL Procedure . . . . . . . . . . . . . . . . . . . . 118
Invoking a Stored Procedure . . . . . . . . . . . . . . . . . . . 124
Using CALL Statement Where Procedure Definition Exists . . . . . . . 124
Using Embedded CALL Statement Where No Procedure Definition Exists . . 125
Using Embedded CALL Statement With an SQLDA . . . . . . . . . . 125
Using Dynamic CALL Statement Where No CREATE PROCEDURE Exists . 127
Parameter Passing Conventions for Stored Procedures . . . . . . . . . 128
Indicator Variables and Stored Procedures . . . . . . . . . . . . . . 132

Contents v
 Returning a Completion Status to the Calling Program . . . . . . . . . . 134
Examples . . . . . . . . . . . . . . . . . . . . . . . . . . 135
Example 1. ILE C and PL/I Procedures Called From ILE C Applications . . 136

Chapter 8. Using the Object-Relational Capabilities . . . . . . . . . . 145

Why Use the DB2 Object Extensions? . . . . . . . . . . . . . . . . 145
DB2 Approach to Supporting Objects . . . . . . . . . . . . . . . . 146
Using Large Objects (LOBs) . . . . . . . . . . . . . . . . . . . 146
Understanding Large Object Data Types (BLOB, CLOB, DBCLOB) . . . . 147
Understanding Large Object Locators . . . . . . . . . . . . . . . 147
Example: Using a Locator to Work With a CLOB Value . . . . . . . . . 148
Indicator Variables and LOB Locators . . . . . . . . . . . . . . . 151
LOB File Reference Variables . . . . . . . . . . . . . . . . . . 151
Example: Extracting a Document To a File . . . . . . . . . . . . . 152
Example: Inserting Data Into a CLOB Column . . . . . . . . . . . . 154
Display Layout of LOB Columns . . . . . . . . . . . . . . . . . 155
Journal Entry Layout of LOB Columns . . . . . . . . . . . . . . . 155
User-Defined Functions (UDF) . . . . . . . . . . . . . . . . . . . 156
Why Use UDFs? . . . . . . . . . . . . . . . . . . . . . . . 156
UDF Concepts. . . . . . . . . . . . . . . . . . . . . . . . 158
Implementing UDFs . . . . . . . . . . . . . . . . . . . . . . 160
Registering UDFs . . . . . . . . . . . . . . . . . . . . . . 161
Examples of Registering UDFs . . . . . . . . . . . . . . . . . 161
Using UDFs . . . . . . . . . . . . . . . . . . . . . . . . 164
User-defined Distinct Types (UDT) . . . . . . . . . . . . . . . . . 169
Why Use UDTs? . . . . . . . . . . . . . . . . . . . . . . . 169
Defining a UDT . . . . . . . . . . . . . . . . . . . . . . . 170
Resolving Unqualified UDTs . . . . . . . . . . . . . . . . . . . 171
Examples of Using CREATE DISTINCT TYPE . . . . . . . . . . . . 171
Defining Tables with UDTs . . . . . . . . . . . . . . . . . . . 171
Manipulating UDTs . . . . . . . . . . . . . . . . . . . . . . 172
Examples of Manipulating UDTs . . . . . . . . . . . . . . . . . 173
Synergy Between UDTs, UDFs, and LOBs . . . . . . . . . . . . . . 177
Combining UDTs, UDFs, and LOBs . . . . . . . . . . . . . . . . 177
Examples of Complex Applications . . . . . . . . . . . . . . . . 177
Using DataLinks . . . . . . . . . . . . . . . . . . . . . . . . 180
NO LINK CONTROL . . . . . . . . . . . . . . . . . . . . . 181
FILE LINK CONTROL (with File System Permissions) . . . . . . . . . 181
FILE LINK CONTROL (with Database Permissions) . . . . . . . . . . 181
Commands Used for Working with DataLinks . . . . . . . . . . . . 182

Chapter 9. Writing User-Defined Functions (UDFs) . . . . . . . . . . 185

UDF runtime environment . . . . . . . . . . . . . . . . . . . . 185
Length of time that the UDF runs . . . . . . . . . . . . . . . . . 185
Threads considerations . . . . . . . . . . . . . . . . . . . . 186
Parallel processing . . . . . . . . . . . . . . . . . . . . . . 186
Writing function code . . . . . . . . . . . . . . . . . . . . . . 186
Writing UDFs as SQL functions . . . . . . . . . . . . . . . . . 186
Writing UDFs as external functions . . . . . . . . . . . . . . . . 187
Examples of UDF code . . . . . . . . . . . . . . . . . . . . . 193
Example: Square of a number UDF . . . . . . . . . . . . . . . . 193
Example: Counter . . . . . . . . . . . . . . . . . . . . . . 194

Chapter 10. Dynamic SQL Applications. . . . . . . . . . . . . . . 197

Designing and Running a Dynamic SQL Application . . . . . . . . . . . 199
Processing Non-SELECT statements . . . . . . . . . . . . . . . . 199

vi DB2 UDB for AS/400 SQL Programming V4R4

 CCSID of Dynamic SQL Statements. . . . . . . . . . . . . . . . 200
Using the PREPARE and EXECUTE Statements . . . . . . . . . . . 200
Processing SELECT Statements and Using an SQLDA. . . . . . . . . . 201
Fixed-List SELECT Statements . . . . . . . . . . . . . . . . . 201
Varying-List Select-Statements . . . . . . . . . . . . . . . . . . 202
The SQL Descriptor Area (SQLDA) . . . . . . . . . . . . . . . . 203
SQLDA Format . . . . . . . . . . . . . . . . . . . . . . . 204
Example of a Select-Statement for Allocating Storage for SQLDA . . . . . 208
Using a Cursor . . . . . . . . . . . . . . . . . . . . . . . 212
Using Parameter Markers . . . . . . . . . . . . . . . . . . . 213

Chapter 11. Common Concepts and Rules for Using SQL with Host
Languages . . . . . . . . . . . . . . . . . . . . . . . . . 215
Using Host Variables in SQL Statements . . . . . . . . . . . . . . . 215
Assignment Rules . . . . . . . . . . . . . . . . . . . . . . 216
Indicator Variables . . . . . . . . . . . . . . . . . . . . . . 219
Handling SQL Error Return Codes . . . . . . . . . . . . . . . . . 221
Handling Exception Conditions with the WHENEVER Statement . . . . . . 222

Chapter 12. Coding SQL Statements in C and C++ Applications . . . . . 225

Defining the SQL Communications Area . . . . . . . . . . . . . . . 225
Defining SQL Descriptor Areas. . . . . . . . . . . . . . . . . . . 226
Embedding SQL Statements . . . . . . . . . . . . . . . . . . . 227
Comments . . . . . . . . . . . . . . . . . . . . . . . . . 228
Continuation for SQL Statements . . . . . . . . . . . . . . . . . 228
Including Code . . . . . . . . . . . . . . . . . . . . . . . 228
Margins . . . . . . . . . . . . . . . . . . . . . . . . . . 229
Names . . . . . . . . . . . . . . . . . . . . . . . . . . 229
NULLs and NULs . . . . . . . . . . . . . . . . . . . . . . 229
Statement Labels . . . . . . . . . . . . . . . . . . . . . . 229
Preprocessor Sequence . . . . . . . . . . . . . . . . . . . . 229
Trigraphs. . . . . . . . . . . . . . . . . . . . . . . . . . 229
WHENEVER Statement . . . . . . . . . . . . . . . . . . . . 230
Using Host Variables . . . . . . . . . . . . . . . . . . . . . . 230
Declaring Host Variables . . . . . . . . . . . . . . . . . . . . 230
Using Host Structures . . . . . . . . . . . . . . . . . . . . . . 239
Host Structure Declarations . . . . . . . . . . . . . . . . . . . 240
Host Structure Indicator Array . . . . . . . . . . . . . . . . . . 241
Using Arrays of Host Structures . . . . . . . . . . . . . . . . . . 241
Host Structure Array . . . . . . . . . . . . . . . . . . . . . 242
Host Structure Array Indicator Structure . . . . . . . . . . . . . . 244
Using Pointer Data Types . . . . . . . . . . . . . . . . . . . . 244
Using ILE C for AS/400 External File Descriptions . . . . . . . . . . . 245
Determining Equivalent SQL and C or C++ Data Types. . . . . . . . . . 246
Notes on C and C++ Variable Declaration and Usage . . . . . . . . . 249
Using Indicator Variables . . . . . . . . . . . . . . . . . . . . . 249

Chapter 13. Coding SQL Statements in COBOL Applications . . . . . . 251

Defining the SQL Communications Area . . . . . . . . . . . . . . . 251
Defining SQL Descriptor Areas. . . . . . . . . . . . . . . . . . . 252
Embedding SQL Statements . . . . . . . . . . . . . . . . . . . 253
Comments . . . . . . . . . . . . . . . . . . . . . . . . . 253
Continuation for SQL Statements . . . . . . . . . . . . . . . . . 253
Including Code . . . . . . . . . . . . . . . . . . . . . . . 254
Margins . . . . . . . . . . . . . . . . . . . . . . . . . . 254
Sequence Numbers . . . . . . . . . . . . . . . . . . . . . . 254

Contents vii
 Names . . . . . . . . . . . . . . . . . . . . . . . . . . 254
COBOL Compile-Time Options. . . . . . . . . . . . . . . . . . 254
Statement Labels . . . . . . . . . . . . . . . . . . . . . . 255
WHENEVER Statement . . . . . . . . . . . . . . . . . . . . 255
Multiple source programs. . . . . . . . . . . . . . . . . . . . 255
Using Host Variables . . . . . . . . . . . . . . . . . . . . . . 255
Declaring Host Variables . . . . . . . . . . . . . . . . . . . . 255
Using Host Structures . . . . . . . . . . . . . . . . . . . . . . 264
Host Structure . . . . . . . . . . . . . . . . . . . . . . . . 265
Host Structure Indicator Array . . . . . . . . . . . . . . . . . . 268
Using Host Structure Arrays . . . . . . . . . . . . . . . . . . . 268
Host Structure Array . . . . . . . . . . . . . . . . . . . . . 269
Host Array Indicator Structure . . . . . . . . . . . . . . . . . . 272
Using External File Descriptions . . . . . . . . . . . . . . . . . . 272
Using External File Descriptions for Host Structure Arrays. . . . . . . . 273
Determining Equivalent SQL and COBOL Data Types . . . . . . . . . . 274
Notes on COBOL Variable Declaration and Usage . . . . . . . . . . 276
Using Indicator Variables . . . . . . . . . . . . . . . . . . . . . 276

Chapter 14. Coding SQL Statements in PL/I Applications. . . . . . . . 279

Defining the SQL Communications Area . . . . . . . . . . . . . . . 279
Defining SQL Descriptor Areas. . . . . . . . . . . . . . . . . . . 280
Embedding SQL Statements . . . . . . . . . . . . . . . . . . . 280
Example . . . . . . . . . . . . . . . . . . . . . . . . . . 281
Comments . . . . . . . . . . . . . . . . . . . . . . . . . 281
Continuation for SQL Statements . . . . . . . . . . . . . . . . . 281
Including Code . . . . . . . . . . . . . . . . . . . . . . . 281
Margins . . . . . . . . . . . . . . . . . . . . . . . . . . 281
Names . . . . . . . . . . . . . . . . . . . . . . . . . . 282
Statement Labels . . . . . . . . . . . . . . . . . . . . . . 282
WHENEVER Statement . . . . . . . . . . . . . . . . . . . . 282
Using Host Variables . . . . . . . . . . . . . . . . . . . . . . 282
Declaring Host Variables . . . . . . . . . . . . . . . . . . . . 282
Using Host Structures . . . . . . . . . . . . . . . . . . . . . . 286
Host Structures . . . . . . . . . . . . . . . . . . . . . . . 287
Host Structure Indicator Arrays. . . . . . . . . . . . . . . . . . 288
Using Host Structure Arrays . . . . . . . . . . . . . . . . . . . . 289
Host Structure Array . . . . . . . . . . . . . . . . . . . . . 290
Using External File Descriptions . . . . . . . . . . . . . . . . . . 291
Determining Equivalent SQL and PL/I Data Types. . . . . . . . . . . . 292
Using Indicator Variables . . . . . . . . . . . . . . . . . . . . . 294
Differences in PL/I Because of Structure Parameter Passing Techniques . . . 294

Chapter 15. Coding SQL Statements in RPG for AS/400 Applications . . . 297
Defining the SQL Communications Area . . . . . . . . . . . . . . . 297
Defining SQL Descriptor Areas. . . . . . . . . . . . . . . . . . . 298
Embedding SQL Statements . . . . . . . . . . . . . . . . . . . 298
Example . . . . . . . . . . . . . . . . . . . . . . . . . . 298
Comments . . . . . . . . . . . . . . . . . . . . . . . . . 299
Continuation for SQL Statements . . . . . . . . . . . . . . . . . 299
Including Code . . . . . . . . . . . . . . . . . . . . . . . 299
Sequence Numbers . . . . . . . . . . . . . . . . . . . . . . 299
Names . . . . . . . . . . . . . . . . . . . . . . . . . . 299
Statement Labels . . . . . . . . . . . . . . . . . . . . . . 300
WHENEVER Statement . . . . . . . . . . . . . . . . . . . . 300
Using Host Variables . . . . . . . . . . . . . . . . . . . . . . 300

viii DB2 UDB for AS/400 SQL Programming V4R4

 Declaring Host Variables . . . . . . . . . . . . . . . . . . . . 300
Using Host Structures . . . . . . . . . . . . . . . . . . . . . . 300
Using Host Structure Arrays . . . . . . . . . . . . . . . . . . . . 301
Using External File Descriptions . . . . . . . . . . . . . . . . . . 302
External File Description Considerations for Host Structure Arrays. . . . . 303
Determining Equivalent SQL and RPG for AS/400 Data Types . . . . . . . 303
Notes on RPG for AS/400 Variable Declaration and Usage . . . . . . . 306
Using Indicator Variables . . . . . . . . . . . . . . . . . . . . . 306
Example . . . . . . . . . . . . . . . . . . . . . . . . . . 306
Differences in RPG for AS/400 Because of Structure Parameter Passing
Techniques . . . . . . . . . . . . . . . . . . . . . . . . . 307
Ending a Called RPG for AS/400 Program Correctly . . . . . . . . . . . 307

Chapter 16. Coding SQL Statements in ILE RPG for AS/400 Applications . 309
Defining the SQL Communications Area . . . . . . . . . . . . . . . 309
Defining SQL Descriptor Areas. . . . . . . . . . . . . . . . . . . 310
Embedding SQL Statements . . . . . . . . . . . . . . . . . . . 311
Example . . . . . . . . . . . . . . . . . . . . . . . . . . 311
Comments . . . . . . . . . . . . . . . . . . . . . . . . . 311
Continuation for SQL Statements . . . . . . . . . . . . . . . . . 311
Including Code . . . . . . . . . . . . . . . . . . . . . . . 312
Sequence Numbers . . . . . . . . . . . . . . . . . . . . . . 312
Names . . . . . . . . . . . . . . . . . . . . . . . . . . 312
Statement Labels . . . . . . . . . . . . . . . . . . . . . . 312
WHENEVER Statement . . . . . . . . . . . . . . . . . . . . 312
Using Host Variables . . . . . . . . . . . . . . . . . . . . . . 312
Declaring Host Variables . . . . . . . . . . . . . . . . . . . . 313
Using Host Structures . . . . . . . . . . . . . . . . . . . . . . 314
Using Host Structure Arrays . . . . . . . . . . . . . . . . . . . . 314
Declaring LOB Host Variables . . . . . . . . . . . . . . . . . . . 315
LOB Host Variables . . . . . . . . . . . . . . . . . . . . . . 315
LOB Locators . . . . . . . . . . . . . . . . . . . . . . . . 316
LOB File Reference Variables . . . . . . . . . . . . . . . . . . 316
Using External File Descriptions . . . . . . . . . . . . . . . . . . 316
External File Description Considerations for Host Structure Arrays. . . . . 317
Determining Equivalent SQL and RPG Data Types . . . . . . . . . . . 318
Notes on ILE/RPG 400 Variable Declaration and Usage . . . . . . . . 322
Using Indicator Variables . . . . . . . . . . . . . . . . . . . . . 322
Example . . . . . . . . . . . . . . . . . . . . . . . . . . 322
SQLDA Example of the SQLDA for a Multiple Row-Area Fetch . . . . . . . 323

Chapter 17. Coding SQL Statements in REXX Applications . . . . . . . 325

Using the SQL Communications Area . . . . . . . . . . . . . . . . 325
Using SQL Descriptor Areas . . . . . . . . . . . . . . . . . . . 325
Embedding SQL Statements . . . . . . . . . . . . . . . . . . . 327
Comments . . . . . . . . . . . . . . . . . . . . . . . . . 328
Continuation of SQL Statements . . . . . . . . . . . . . . . . . 328
Including Code . . . . . . . . . . . . . . . . . . . . . . . 328
Margins . . . . . . . . . . . . . . . . . . . . . . . . . . 329
Names . . . . . . . . . . . . . . . . . . . . . . . . . . 329
Nulls . . . . . . . . . . . . . . . . . . . . . . . . . . . 329
Statement Labels . . . . . . . . . . . . . . . . . . . . . . 329
Handling Errors and Warnings . . . . . . . . . . . . . . . . . . 329
Using Host Variables . . . . . . . . . . . . . . . . . . . . . . 329
Determining Data Types of Input Host Variables . . . . . . . . . . . 330
The Format of Output Host Variables . . . . . . . . . . . . . . . 331

Contents ix
 Avoiding REXX Conversion . . . . . . . . . . . . . . . . . . . 332
Using Indicator Variables . . . . . . . . . . . . . . . . . . . . . 332

Chapter 18. Preparing and Running a Program with SQL Statements . . . 333
Basic Processes of the SQL Precompiler . . . . . . . . . . . . . . . 333
Input to the Precompiler . . . . . . . . . . . . . . . . . . . . 334
Source File CCSIDs . . . . . . . . . . . . . . . . . . . . . 334
Output from the Precompiler . . . . . . . . . . . . . . . . . . 335
Non-ILE Precompiler Commands . . . . . . . . . . . . . . . . . . 340
Compiling a Non-ILE Application Program . . . . . . . . . . . . . 340
ILE Precompiler Commands. . . . . . . . . . . . . . . . . . . . 341
Compiling an ILE Application Program . . . . . . . . . . . . . . . 341
Precompiling for the VisualAge C++ for OS/400 Compiler . . . . . . . . 342
Interpreting Application Program Compile Errors . . . . . . . . . . . . 343
Error and Warning Messages during a Compile . . . . . . . . . . . 343
Binding an Application . . . . . . . . . . . . . . . . . . . . . . 344
Program References . . . . . . . . . . . . . . . . . . . . . 345
Displaying Precompiler Options . . . . . . . . . . . . . . . . . . 345
Running a Program with Embedded SQL . . . . . . . . . . . . . . . 346
OS/400 DDM Considerations . . . . . . . . . . . . . . . . . . 346
Override Considerations . . . . . . . . . . . . . . . . . . . . 346
SQL Return Codes . . . . . . . . . . . . . . . . . . . . . . 347

Chapter 19. Using Interactive SQL . . . . . . . . . . . . . . . . 349

Basic Functions of Interactive SQL . . . . . . . . . . . . . . . . . 349
Starting Interactive SQL . . . . . . . . . . . . . . . . . . . . 350
Using Statement Entry Function . . . . . . . . . . . . . . . . . 351
Prompting . . . . . . . . . . . . . . . . . . . . . . . . . 351
Using the List Selection Function . . . . . . . . . . . . . . . . . 354
Session Services Description . . . . . . . . . . . . . . . . . . 356
Exiting Interactive SQL . . . . . . . . . . . . . . . . . . . . 357
Using an existing SQL Session . . . . . . . . . . . . . . . . . 358
Recovering an SQL Session . . . . . . . . . . . . . . . . . . 358
Accessing Remote Databases with Interactive SQL . . . . . . . . . . 359

Chapter 20. Using the SQL Statement Processor . . . . . . . . . . . 361

Execution of Statements After Errors Occur . . . . . . . . . . . . . . 362
Commitment Control in the SQL Statement Processor . . . . . . . . . . 362
Schemas in the SQL Statement Processor . . . . . . . . . . . . . . 362
Source Member Listing for the SQL Statement Processor . . . . . . . . . 363

Chapter 21. DB2 UDB for AS/400 Data Protection. . . . . . . . . . . 365

Security . . . . . . . . . . . . . . . . . . . . . . . . . . . 365
Authorization ID . . . . . . . . . . . . . . . . . . . . . . . 366
Views . . . . . . . . . . . . . . . . . . . . . . . . . . . 366
Auditing . . . . . . . . . . . . . . . . . . . . . . . . . . 366
Data Integrity . . . . . . . . . . . . . . . . . . . . . . . . . 366
Concurrency . . . . . . . . . . . . . . . . . . . . . . . . 367
Journaling . . . . . . . . . . . . . . . . . . . . . . . . . 368
Commitment Control . . . . . . . . . . . . . . . . . . . . . 369
Atomic Operations . . . . . . . . . . . . . . . . . . . . . . 373
Constraints . . . . . . . . . . . . . . . . . . . . . . . . . 375
Save/Restore . . . . . . . . . . . . . . . . . . . . . . . . 375
Damage Tolerance . . . . . . . . . . . . . . . . . . . . . . 376
Index Recovery . . . . . . . . . . . . . . . . . . . . . . . 376
Catalog Integrity . . . . . . . . . . . . . . . . . . . . . . . 377

x DB2 UDB for AS/400 SQL Programming V4R4

 User Auxiliary Storage Pool (ASP) . . . . . . . . . . . . . . . . 378

Chapter 22. Testing SQL Statements in Application Programs . . . . . . 379

Establishing a Test Environment . . . . . . . . . . . . . . . . . . 379
Designing a Test Data Structure . . . . . . . . . . . . . . . . . 379
Testing Your SQL Application Programs . . . . . . . . . . . . . . . 380
The Program Debug Phase . . . . . . . . . . . . . . . . . . . 380
The Performance Verification Phase. . . . . . . . . . . . . . . . 381
CL Command Usage for SQL Application Performance Verification . . . . 381
Performance Information Messages . . . . . . . . . . . . . . . . 382
Performance Information Messages and Open Data Paths . . . . . . . 388

Chapter 23. Using the DB2 UDB for AS/400 Predictive Query Governor . . 391
Cancelling a Query . . . . . . . . . . . . . . . . . . . . . . . 392
General Implementation Considerations . . . . . . . . . . . . . . . 392
User Application Implementation Considerations . . . . . . . . . . . . 392
Controlling the Default Reply to the Inquiry Message . . . . . . . . . . 393
Using the Governor for Performance Testing. . . . . . . . . . . . . . 393
Examples . . . . . . . . . . . . . . . . . . . . . . . . . . 394

Chapter 24. DB2 UDB for AS/400 Data Management and Query Optimizer
Tips . . . . . . . . . . . . . . . . . . . . . . . . . . . 395
General Optimization Tips . . . . . . . . . . . . . . . . . . . . 395
Data Management Methods . . . . . . . . . . . . . . . . . . . . 396
Access Path . . . . . . . . . . . . . . . . . . . . . . . . 396
Access Method . . . . . . . . . . . . . . . . . . . . . . . 397
Data Access Methods . . . . . . . . . . . . . . . . . . . . . . 420
The Optimizer . . . . . . . . . . . . . . . . . . . . . . . . . 422
Cost Estimation . . . . . . . . . . . . . . . . . . . . . . . 423
Access Plan Validation. . . . . . . . . . . . . . . . . . . . . 425
Optimizer Decision-Making Rules . . . . . . . . . . . . . . . . . 425
Join Optimization . . . . . . . . . . . . . . . . . . . . . . . 426
Grouping Optimization . . . . . . . . . . . . . . . . . . . . . 442
Effectively Using SQL Indexes . . . . . . . . . . . . . . . . . . . 446
Using Indexes With Sort Sequence . . . . . . . . . . . . . . . . . 449
Using Indexes and Sort Sequence With Selection, Joins, or Grouping . . . 449
Ordering . . . . . . . . . . . . . . . . . . . . . . . . . . 450
Example Indexes . . . . . . . . . . . . . . . . . . . . . . . 450
Tips for using VARCHAR and VARGRAPHIC data types . . . . . . . . . 456

Chapter 25. Additional SQL performance considerations . . . . . . . . 459

Improving Performance by Using Database Manager Blocking Considerations . 461
Improving Performance Using FETCH FOR n ROWS . . . . . . . . . . 462
Improving Performance with SQL Blocking . . . . . . . . . . . . . . 462
Improving Performance Using INSERT n ROWS . . . . . . . . . . . . 463
Improving Performance When Paging Interactively Displayed Data . . . . . 463
Improving Performance by Using SELECT Statements Effectively . . . . . . 464
Improving Performance by Using Live Data . . . . . . . . . . . . . . 464
Improving Performance by Using the ALWCPYDTA Parameter . . . . . . . 465
Improving Performance by Using the Optimize Clause . . . . . . . . . . 466
Improving Performance by Retaining Cursor Positions . . . . . . . . . . 467
Improving Performance by Retaining Cursor Positions for Non-ILE Program
Calls . . . . . . . . . . . . . . . . . . . . . . . . . . 467
Improving Performance by Retaining Cursor Positions across ILE Program
Calls . . . . . . . . . . . . . . . . . . . . . . . . . . . 468
General Rules for Retaining Cursor Positions For All Program Calls . . . . . 469

Contents xi
 Improving Performance of SQL PREPARE Statements . . . . . . . . . . 470
Effects on Performance When Using Long Object Names . . . . . . . . . 470
Improving Performance Using the Precompile Options . . . . . . . . . . 471
Improving Performance by Using Structure Parameter Passing Techniques . . 472
Background Information on Parameter Passing. . . . . . . . . . . . 472
Some Differences Because of Structure Parameter Passing Techniques . . 473
Controlling Parallel Processing . . . . . . . . . . . . . . . . . . . 473
Controlling Parallel Processing System Wide . . . . . . . . . . . . 474
Controlling Parallel Processing for a Job . . . . . . . . . . . . . . 474

Chapter 26. Monitoring and Optimizing Query Performance Tools . . . . 477

Optimizing Query Performance Using Query Optimization Tools . . . . . . 477
Query optimizer debug messages . . . . . . . . . . . . . . . . . 477
Print SQL information . . . . . . . . . . . . . . . . . . . . . . 478
Database monitor statistics . . . . . . . . . . . . . . . . . . . . 478
Start Database Monitor (STRDBMON) Command . . . . . . . . . . . 479
End Database Monitor (ENDDBMON) Command . . . . . . . . . . . 480
Database Monitor Performance Records . . . . . . . . . . . . . . 481
Query Optimizer Index Advisor . . . . . . . . . . . . . . . . . . 482
Database Monitor Examples . . . . . . . . . . . . . . . . . . 482
Database Monitor Physical File DDS . . . . . . . . . . . . . . . 489
Database Monitor Logical File DDS . . . . . . . . . . . . . . . . 493
Memory Resident Database Monitor APIs. . . . . . . . . . . . . . . 524
External API Description . . . . . . . . . . . . . . . . . . . . 525
External File Description . . . . . . . . . . . . . . . . . . . . 525
Record Identification . . . . . . . . . . . . . . . . . . . . . 542
Comparison table of query optimization tools . . . . . . . . . . . . 542
Change query attributes . . . . . . . . . . . . . . . . . . . . . 543
Query Options File QAQQINI . . . . . . . . . . . . . . . . . . . 543
Specifying the QAQQINI file. . . . . . . . . . . . . . . . . . . 544
Creating the QAQQINI Query Options File . . . . . . . . . . . . . 544

Chapter 27. Solving Common Database Problems . . . . . . . . . . 551

Paging through Retrieved Data . . . . . . . . . . . . . . . . . . 551
Retrieving in Reverse Order. . . . . . . . . . . . . . . . . . . . 551
Establishing Position at the End of a Table . . . . . . . . . . . . . . 551
Adding Data to the End of a Table . . . . . . . . . . . . . . . . . 552
Updating Data as It Is Retrieved from a Table . . . . . . . . . . . . . 552
Restrictions . . . . . . . . . . . . . . . . . . . . . . . . . 553
Updating Data Previously Retrieved . . . . . . . . . . . . . . . . . 553
Changing the Table Definition . . . . . . . . . . . . . . . . . . . 554

Chapter 28. Distributed Relational Database Function . . . . . . . . . 555

DB2 UDB for AS/400 Distributed Relational Database Support . . . . . . . 555
DB2 UDB for AS/400 Distributed Relational Database Example Program . . . 556
SQL Package Support . . . . . . . . . . . . . . . . . . . . . . 557
Valid SQL Statements in an SQL Package . . . . . . . . . . . . . 558
Considerations for Creating an SQL Package . . . . . . . . . . . . 558
CCSID Considerations for SQL . . . . . . . . . . . . . . . . . . 561
Connection Management and Activation Groups . . . . . . . . . . . . 561
Connections and conversations . . . . . . . . . . . . . . . . . 561
Source Code for PGM1: . . . . . . . . . . . . . . . . . . . . 562
Source Code for PGM2: . . . . . . . . . . . . . . . . . . . . 563
Source Code for PGM3: . . . . . . . . . . . . . . . . . . . . 563
Multiple Connections to the Same Relational Database. . . . . . . . . 565
Implicit Connection Management for the Default Activation Group . . . . . 565

xii DB2 UDB for AS/400 SQL Programming V4R4

 Implicit Connection Management for Nondefault Activation Groups . . . . 566
Distributed Support . . . . . . . . . . . . . . . . . . . . . . . 566
Determining Connection Type . . . . . . . . . . . . . . . . . . 567
Connect and Commitment Control Restrictions . . . . . . . . . . . . 570
Determining Connection Status . . . . . . . . . . . . . . . . . 570
Distributed Unit of Work Connection Considerations . . . . . . . . . . 572
Ending Connections. . . . . . . . . . . . . . . . . . . . . . 573
Distributed Unit of Work . . . . . . . . . . . . . . . . . . . . . 573
Managing Distributed Unit of Work Connections . . . . . . . . . . . 574
Cursors and Prepared Statements . . . . . . . . . . . . . . . . 576
Application Requester Driver Programs . . . . . . . . . . . . . . . 577
Problem Handling . . . . . . . . . . . . . . . . . . . . . . . 578

Appendix A. DB2 UDB for AS/400 Sample Tables . . . . . . . . . . . 579

Department Table (CORPDATA.DEPARTMENT) . . . . . . . . . . . . 579
DEPARTMENT . . . . . . . . . . . . . . . . . . . . . . . 580
Employee Table (CORPDATA.EMPLOYEE) . . . . . . . . . . . . . . 580
Employee to Project Activity Table (CORPDATA.EMP_ACT) . . . . . . . . 581
EMP_ACT . . . . . . . . . . . . . . . . . . . . . . . . . 582
Project Table (CORPDATA.PROJECT) . . . . . . . . . . . . . . . . 584
PROJECT . . . . . . . . . . . . . . . . . . . . . . . . . 584
Class Schedule Table (CL_SCHED) . . . . . . . . . . . . . . . . . 585
In Tray Table (IN_TRAY) . . . . . . . . . . . . . . . . . . . . . 585

Appendix B. SQLCODEs and SQLSTATEs . . . . . . . . . . . . . 587

SQLCODE and SQLSTATE Descriptions . . . . . . . . . . . . . . . 589
Positive SQLCODEs . . . . . . . . . . . . . . . . . . . . . 589
Negative SQLCODEs . . . . . . . . . . . . . . . . . . . . . 591

Appendix C. Sample Programs Using DB2 UDB for AS/400 Statements . . 605
Examples of programs that use SQL statements . . . . . . . . . . . . 605
SQL Statements in ILE C and C++ Programs . . . . . . . . . . . . . 606
SQL Statements in COBOL and ILE COBOL Programs. . . . . . . . . . 613
SQL Statements in PL/I . . . . . . . . . . . . . . . . . . . . . 621
SQL Statements in RPG for AS/400 Programs . . . . . . . . . . . . . 628
SQL Statements in ILE RPG for AS/400 Programs . . . . . . . . . . . 634
SQL Statements in REXX Programs. . . . . . . . . . . . . . . . . 640
Report Produced by Sample Programs. . . . . . . . . . . . . . . . 643

Appendix D. DB2 UDB for AS/400 CL Command Descriptions . . . . . . 645

CRTSQLCBL (Create Structured Query Language COBOL) Command . . . . 645
Purpose . . . . . . . . . . . . . . . . . . . . . . . . . . 648
Parameters . . . . . . . . . . . . . . . . . . . . . . . . . 648
Example . . . . . . . . . . . . . . . . . . . . . . . . . . 661
CRTSQLCBLI (Create SQL ILE COBOL Object) Command . . . . . . . . 661
Purpose . . . . . . . . . . . . . . . . . . . . . . . . . . 664
Parameters . . . . . . . . . . . . . . . . . . . . . . . . . 664
Example . . . . . . . . . . . . . . . . . . . . . . . . . . 678
CRTSQLCI (Create Structured Query Language ILE C Object) Command . . . 678
Purpose . . . . . . . . . . . . . . . . . . . . . . . . . . 681
Parameters . . . . . . . . . . . . . . . . . . . . . . . . . 681
Example . . . . . . . . . . . . . . . . . . . . . . . . . . 695
CRTSQLCPPI (Create Structured Query Language C++ Object) Command . . 695
Purpose . . . . . . . . . . . . . . . . . . . . . . . . . . 699
Parameters . . . . . . . . . . . . . . . . . . . . . . . . . 699
Example . . . . . . . . . . . . . . . . . . . . . . . . . . 712

Contents xiii
 CRTSQLPLI (Create Structured Query Language PL/I) Command. . . . . . 712
Purpose . . . . . . . . . . . . . . . . . . . . . . . . . . 715
Parameters . . . . . . . . . . . . . . . . . . . . . . . . . 715
Example . . . . . . . . . . . . . . . . . . . . . . . . . . 728
CRTSQLRPG (Create Structured Query Language RPG) Command . . . . . 728
Purpose . . . . . . . . . . . . . . . . . . . . . . . . . . 731
Parameters . . . . . . . . . . . . . . . . . . . . . . . . . 732
Example . . . . . . . . . . . . . . . . . . . . . . . . . . 744
CRTSQLRPGI (Create SQL ILE RPG Object) Command . . . . . . . . . 744
Purpose . . . . . . . . . . . . . . . . . . . . . . . . . . 748
Parameters . . . . . . . . . . . . . . . . . . . . . . . . . 748
Example . . . . . . . . . . . . . . . . . . . . . . . . . . 761
CRTSQLPKG (Create Structured Query Language Package) Command . . . 762
Purpose . . . . . . . . . . . . . . . . . . . . . . . . . . 763
Parameters . . . . . . . . . . . . . . . . . . . . . . . . . 763
Example . . . . . . . . . . . . . . . . . . . . . . . . . . 765
CVTSQLCPP (Convert Structured Query Language C++ Source) Command . . 766
Purpose . . . . . . . . . . . . . . . . . . . . . . . . . . 769
Parameters . . . . . . . . . . . . . . . . . . . . . . . . . 769
Example . . . . . . . . . . . . . . . . . . . . . . . . . . 781
DLTSQLPKG (Delete Structured Query Language Package) Command. . . . 781
Purpose . . . . . . . . . . . . . . . . . . . . . . . . . . 782
Parameters . . . . . . . . . . . . . . . . . . . . . . . . . 782
Example . . . . . . . . . . . . . . . . . . . . . . . . . . 783
PRTSQLINF (Print Structured Query Language Information) Command . . . . 783
Purpose . . . . . . . . . . . . . . . . . . . . . . . . . . 783
Parameters . . . . . . . . . . . . . . . . . . . . . . . . . 783
Example . . . . . . . . . . . . . . . . . . . . . . . . . . 784
RUNSQLSTM (Run Structured Query Language Statement) Command . . . . 784
Purpose . . . . . . . . . . . . . . . . . . . . . . . . . . 786
Parameters . . . . . . . . . . . . . . . . . . . . . . . . . 786
Parameters for SQL procedures . . . . . . . . . . . . . . . . . 792
Example . . . . . . . . . . . . . . . . . . . . . . . . . . 794
STRSQL (Start Structured Query Language) Command . . . . . . . . . 794
Purpose . . . . . . . . . . . . . . . . . . . . . . . . . . 796
Parameters . . . . . . . . . . . . . . . . . . . . . . . . . 796
Example . . . . . . . . . . . . . . . . . . . . . . . . . . 801

Appendix E. Using the C for AS/400 and FORTRAN for AS/400

Precompilers . . . . . . . . . . . . . . . . . . . . . . . . 803
Using the C for AS/400 Precompiler . . . . . . . . . . . . . . . . . 803
Access plans . . . . . . . . . . . . . . . . . . . . . . . . 803
Host variable data types . . . . . . . . . . . . . . . . . . . . 803
Using external file descriptions . . . . . . . . . . . . . . . . . . 803
CRTSQLC (Create Structured Query Language C) Command . . . . . . 804
Using the FORTRAN/400 Precompiler . . . . . . . . . . . . . . . . 819
CRTSQLFTN (Create Structured Query Language FORTRAN) Command . . 819

Appendix F. Coding SQL Statements in FORTRAN Applications . . . . . 837

Defining the SQL Communications Area . . . . . . . . . . . . . . . 837
Defining SQL Descriptor Areas. . . . . . . . . . . . . . . . . . . 838
Embedding SQL Statements . . . . . . . . . . . . . . . . . . . 839
Comments . . . . . . . . . . . . . . . . . . . . . . . . . 839
Debug Lines . . . . . . . . . . . . . . . . . . . . . . . . 839
Continuation for SQL statements . . . . . . . . . . . . . . . . . 839
Including Code . . . . . . . . . . . . . . . . . . . . . . . 840

xiv DB2 UDB for AS/400 SQL Programming V4R4

 Margins . . . . . . . . . . . . . . . . . . . . . . . . . . 840
Names . . . . . . . . . . . . . . . . . . . . . . . . . . 840
Statement Labels . . . . . . . . . . . . . . . . . . . . . . 840
WHENEVER Statement . . . . . . . . . . . . . . . . . . . . 841
FORTRAN Compile-Time Options . . . . . . . . . . . . . . . . 841
Using Host Variables . . . . . . . . . . . . . . . . . . . . . . 841
Declaring Host Variables . . . . . . . . . . . . . . . . . . . . 841
Determining Equivalent SQL and FORTRAN Data Types . . . . . . . . . 842
Notes on FORTRAN Variable Declaration and Usage . . . . . . . . . 843
Using Indicator Variables . . . . . . . . . . . . . . . . . . . . . 844

Bibliography . . . . . . . . . . . . . . . . . . . . . . . . . 845

Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . 847

Readers’ Comments — We’d Like to Hear from You. . . . . . . . . . 879

Contents xv
xvi DB2 UDB for AS/400 SQL Programming V4R4
 About DB2 UDB for AS/400 SQL Programming
This book explains to programmers and database administrators:
v How to use the DB2 SQL for AS/400 licensed program
v How to access data in a database
v How to prepare, run, test, and optimize an application program containing SQL
statements.

For more information on DB2 UDB for AS/400 SQL guidelines and examples for
implementation in an application programming environment, see the following
books:
v DB2 UDB for AS/400 SQL Reference
v DB2 UDB for AS/400 SQL Call Level Interface

Note: The DB2 UDB for AS/400 library will only be available in the AS/400e
Information Center beginning in Version 4, Release 4.
v DATABASE 2/400 Advanced Database Functions, GG24-4249.

Who should read this book

| This guide should be used by application programmers and database administrators
| who are familiar with and can program with COBOL for AS/400, ILE COBOL for
| AS/400, AS/400 PL/I, ILE C for AS/400, ILE C++, VisualAge C++ for AS/400, REXX,
| RPG III (part of RPG for AS/400), or ILE RPG for AS/400 language and who can
| understand basic database applications.

Assumptions Relating to Examples of SQL Statements

The examples of SQL statements shown in this guide are based on the sample
tables in Appendix A, ″DB2 UDB for AS/400 Sample Tables,″ and assume the
following:
v They are shown in the interactive SQL environment or they are written in ILE C
or in COBOL. EXEC SQL and END-EXEC are used to delimit an SQL statement
in a COBOL program. A description of how to use SQL statements in a COBOL
program is provided in ″Coding SQL Statements in COBOL Applications.″ A
description of how to use SQL statements in an ILE C program is provided in
″Coding SQL Statements in C Applications.″
v Each SQL example is shown on several lines, with each clause of the statement
on a separate line.
v SQL keywords are highlighted.
v Table names provided in Appendix A, ″DB2 UDB for AS/400 Sample Tables,″ use
the collection CORPDATA. Table names that are not found in Appendix A, ″DB2
UDB for AS/400 Sample Tables,″ should use collections you create.
v Calculated columns are enclosed in parentheses, (), and brackets, [].
v The SQL naming convention is used.
v The APOST and APOSTSQL precompiler options are assumed although they are
not the default options in COBOL. Character string literals within SQL and host
language statements are delimited by apostrophes (’).
v A sort sequence of *HEX is used, unless otherwise noted.

© Copyright IBM Corp. 1997, 1999 xvii

 v The complete syntax of the SQL statement is usually not shown in any one
example. For the complete description and syntax of any of the statements
described in this guide, see the DB2 UDB for AS/400 SQL Reference

Whenever the examples vary from these assumptions, it is stated.

Because this guide is for the application programmer, most of the examples are
shown as if they were written in an application program. However, many examples
can be slightly changed and run interactively by using interactive SQL. The syntax
of an SQL statement, when using interactive SQL, differs slightly from the format of
the same statement when it is embedded in a program.

How to Interpret Syntax Diagrams in this Guide

Throughout this book, syntax is described using the structure defined as follows:
v Read the syntax diagrams from left to right, from top to bottom, following the path
of the line.
The ÊÊ─── symbol indicates the beginning of a statement.
The ───Ê symbol indicates that the statement syntax is continued on the next
line.
The Ê─── symbol indicates that a statement is continued from the previous line.
The ───ÊÍ symbol indicates the end of a statement.
Diagrams of syntactical units other than complete statements start with the Ê───
symbol and end with the ───Ê symbol.
v Required items appear on the horizontal line (the main path).

ÊÊ required_item ÊÍ

v Optional items appear below the main path.

ÊÊ required_item ÊÍ
optional_item

If an optional item appears above the main path, that item has no effect on the
execution of the statement and is used only for readability.

optional_item
ÊÊ required_item ÊÍ

v If you can choose from two or more items, they appear vertically, in a stack.
If you must choose one of the items, one item of the stack appears on the main
path.

ÊÊ required_item required_choice1 ÊÍ
required_choice2

If choosing one of the items is optional, the entire stack appears below the main
path.

xviii DB2 UDB for AS/400 SQL Programming V4R4

 ÊÊ required_item ÊÍ
optional_choice1
optional_choice2

If one of the items is the default, it will appear above the main path and the
remaining choices will be shown below.

default_choice
ÊÊ required_item ÊÍ
optional_choice
optional_choice

v An arrow returning to the left, above the main line, indicates an item that can be
repeated.

ÊÊ required_item · repeatable_item ÊÍ

If the repeat arrow contains a comma, you must separate repeated items with a
comma.

ÊÊ required_item · repeatable_item ÊÍ

A repeat arrow above a stack indicates that you can repeat the items in the
stack.
v Keywords appear in uppercase (for example, FROM). They must be spelled exactly
as shown. Variables appear in all lowercase letters (for example, column-name).
They represent user-supplied names or values.
v If punctuation marks, parentheses, arithmetic operators, or other such symbols
are shown, you must enter them as part of the syntax.

AS/400 Operations Navigator

AS/400 Operations Navigator is a powerful graphical interface for Windows clients.
With AS/400 Operations Navigator, you can manage and administer your AS/400
systems from your Windows desktop.

You can use Operations Navigator to manage communications, printing, database,

security, and other system operations. Operations Navigator includes Management
Central for managing multiple AS/400 systems centrally.

Figure 1 on page xx shows an example of the Operations Navigator display:

About DB2 UDB for AS/400 SQL Programming xix

 Figure 1. AS/400 Operations Navigator Display

This new interface has been designed to make you more productive and is the only
user interface to new, advanced features of OS/400. Therefore, IBM recommends
that you use AS/400 Operations Navigator, which has online help to guide you.
While this interface is being developed, you may still need to use a traditional
emulator such as PC5250 to do some of your tasks.

Installing Operations Navigator

To use AS/400 Operations Navigator, you must have Client Access installed on your
Windows PC. For help in connecting your Windows PC to your AS/400 system,
consult Client Access Express for Windows - Setup, SC41-5507-00.

AS/400 Operations Navigator is a separately installable component of Client Access

that contains many subcomponents. If you are installing for the first time and you
use the Typical installation option, the following options are installed by default:
v Operations Navigator base support
v Basic operations (messages, printer output, and printers)

To select the subcomponents that you want to install, select the Custom installation
option. (After Operations Navigator has been installed, you can add subcomponents
by using Client Access Selective Setup.)
1. Display the list of currently installed subcomponents in the Component
Selection window of Custom installation or Selective Setup.
2. Select AS/400 Operations Navigator.
3. Select any additional subcomponents that you want to install and continue with
Custom installation or Selective Setup.

After you install Client Access, double-click the AS400 Operations Navigator icon
on your desktop to access Operations Navigator and create an AS/400 connection.

xx DB2 UDB for AS/400 SQL Programming V4R4

 How this book has changed
| The major new features covered in this manual include:
v Large objects (LOBs)
| v DataLinks
| v User-defined types
| v User-defined functions
| v Derived tables

Prerequisite and related information

Use the AS/400 Information Center as your starting point for looking up AS/400
technical information. You can access the Information Center from the AS/400e
Information Center CD-ROM (English version: SK3T-2027) or from one of these
Web sites:
http://www.as400.ibm.com/infocenter
http://publib.boulder.ibm.com/pubs/html/as400/infocenter.htm

The AS/400 Information Center contains important topics such as logical

partitioning, clustering, Java, TCP/IP, Web serving, and secured networks. It also
contains Internet links to Web sites such as the AS/400 Online Library and the
AS/400 Technical Studio. Included in the Information Center is a link that describes
at a high level the differences in information between the Information Center and
the Online Library.

For a list of related publications, see the “Bibliography” on page 845.

How to send your comments

Your feedback is important in helping to provide the most accurate and high-quality
information. If you have any comments about this book or any other AS/400
documentation, fill out the readers’ comment form at the back of this book.
v If you prefer to send comments by mail, use the readers’ comment form with the
address that is printed on the back. If you are mailing a readers’ comment form
from a country other than the United States, you can give the form to the local
IBM branch office or IBM representative for postage-paid mailing.
v If you prefer to send comments by FAX, use either of the following numbers:
– United States and Canada: 1-800-937-3430
– Other countries: 1-507-253-5192
v If you prefer to send comments electronically, use one of these e-mail addresses:
– Comments on books:
[email protected]
IBMMAIL, to IBMMAIL(USIB56RZ)
– Comments on the AS/400 Information Center:
[email protected]
Be sure to include the following:
v The name of the book.
v The publication number of the book.

About DB2 UDB for AS/400 SQL Programming xxi

 v The page number or topic to which your comment applies.

xxii DB2 UDB for AS/400 SQL Programming V4R4

Chapter 1. Introduction to DB2 UDB for AS/400 Structured
Query Language
This guide describes the AS/400* system implementation of the Structured Query
Language (SQL) using DB2 UDB for AS/400 and the DB2 UDB Query Manager and
SQL Development Kit Version 4 licensed program. SQL manages information based
on the relational model of data. SQL statements can be embedded in high-level
languages, dynamically prepared and run, or run interactively.

SQL consists of statements and clauses that describe what you want to do with the
data in a database and under what conditions you want to do it.

SQL can access data in a remote relational database, using the IBM Distributed
Relational Database Architecture* (DRDA*). This function is described in
Chapter 28. Distributed Relational Database Function, of this guide. Further
information about DRDA is contained in the Distributed Database Programming
book.

SQL Concepts
DB2 UDB for AS/400 SQL consists of the following main parts:
v SQL run-time support
SQL run-time parses SQL statements and runs any SQL statements. This
support is that part of the Operating System/400* (OS/400) licensed program
which allows applications that contain SQL statements to be run on systems
where the DB2 UDB Query Manager and SQL Development Kit licensed program
is not installed.
v SQL precompilers
SQL precompilers support precompiling embedded SQL statements in host
languages. The following languages are supported:
– ILE C for AS/400*
– ILE C++ for AS/400
– VisualAge C++ for AS/400
– ILE COBOL for AS/400*
– COBOL for AS/400*
– AS/400 PL/I*
– RPG III (part of RPG for AS/400*)
– ILE RPG for AS/400*
The SQL host language precompilers prepare an application program containing
SQL statements. The host language compilers then compile the precompiled host
source programs. For more information on precompiling, see Chapter 18.
Preparing and Running a Program with SQL Statements. The precompiler
support is part of the DB2 UDB Query Manager and SQL Development Kit
licensed program.
v SQL interactive interface
SQL interactive interface allows you to create and run SQL statements. For more
information on interactive SQL, see Chapter 19. Using Interactive SQL.
Interactive SQL is part of the DB2 UDB Query Manager and SQL Development
Kit licensed program.
v Run SQL Statements CL command

© Copyright IBM Corp. 1997, 1999 1

 RUNSQLSTM allows you to run a series of SQL statements, which are stored in
a source file. The RUNSQLSTM command is part of the DB2 UDB Query
Manager and SQL Development Kit licensed program. See Chapter 20. Using the
SQL Statement Processor for more information on the Run SQL Statements
command.
v DB2 Query Manager for AS/400
DB2 Query Manager for AS/400 provides a prompt-driven interactive interface
that allows you to create data, add data, maintain data, and run reports on the
databases. Query Manager is part of the DB2 UDB Query Manager and SQL
Development Kit licensed program. For more information, refer to the DB2 UDB
for AS/400 Query Manager Use book.
v SQL REXX Interface
The SQL REXX interface allows you to run SQL statements in a REXX
procedure. This interface is part of the DB2 UDB Query Manager and SQL
Development Kit licensed program. For more information on using SQL
statements in REXX procedures, see Chapter 17. Coding SQL Statements in
REXX Applications.
v SQL Call Level Interface
DB2 UDB for AS/400 supports the SQL Call Level Interface. This allows users of
any of the ILE languages to access SQL functions directly through procedure
calls to a service program provided by the system. Using the SQL Call Level
Interface, one can perform all the SQL functions without the need for a
precompile. This is a standard set of procedure calls to prepare SQL statements,
execute SQL statements, fetch rows of data, and even do advanced functions
such as accessing the catalogs and binding program variables to output columns.
For a complete description of all the available functions, and their syntax, see the
DB2 UDB for AS/400 SQL Call Level Interface book.
v QSQPRCED API
This Application Program Interface (API) provides an extended dynamic SQL
capability. SQL statements can be prepared into an SQL package and then
executed using this API. Statements prepared into a package by this API persist
until the package or statement is explicitly dropped. QSQPRCED is part of the
OS/400 licensed program. For more information on the QSQPRCED API, see the
System API Reference book.
v QSQCHKS API
This API syntax checks SQL statements. QSQCHKS is part of the OS/400
licensed program. For more information on the QSQCHKS API, see the System
API Reference book.
v DB2 Multisystem
This feature of the operating system allows your data to be distributed across
multiple AS/400 systems. For more information on DB2 Multisystem, see the DB2
Multisystem for AS/400 book.
v DB2 UDB Symmetric Multiprocessing
This feature of the operating system provides the query optimizer with additional
methods for retrieving data that include parallel processing. Symmetric
multiprocessing (SMP) is a form of parallelism achieved on a single system
where multiple processors (CPU and I/O processors) that share memory and disk
resource work simultaneously towards achieving a single end result. This parallel
processing means that the database manager can have more than one (or all) of
the system processors working on a single query simultaneously. See
“Controlling Parallel Processing” on page 473 for information on how to control
parallel processing.

2 DB2 UDB for AS/400 SQL Programming V4R4

Relational Database and Terminology
In the relational model of data, all data is perceived as existing in tables. DB2 UDB
for AS/400 objects are created and maintained as AS/400 system objects. The
following table shows the relationship between AS/400 system terms and SQL
relational database terms. For more information on database, see the DB2 UDB for
AS/400 Database Programming book.
Table 1. Relationship of System Terms to SQL Terms
System Terms SQL Terms
Library. Groups related objects and allows Collection. Consists of a library, a journal, a
you to find the objects by name. journal receiver, an SQL catalog, and
optionally a data dictionary. A collection
groups related objects and allows you to find
the objects by name.
Physical file. A set of records. Table. A set of columns and rows.
Record. A set of fields. Row. The horizontal part of a table
containing a serial set of columns.
Field. One or more characters of related Column. The vertical part of a table of one
information of one data type. data type.
Logical file. A subset of fields and records View. A subset of columns and rows of one
of one or more physical files. or more tables.
SQL Package. An object type that is used to Package. An object type that is used to run
run SQL statements. SQL statements.
User Profile Authorization name or Authorization ID.

SQL Terminology
There are two naming conventions that can be used in DB2 UDB for AS/400
programming: system (*SYS) and SQL (*SQL). The naming convention used affects
the method for qualifying file and table names and the terms used on the interactive
SQL displays. The naming convention used is selected by a parameter on the SQL
commands or, for REXX, selected through the SET OPTION statement.

System naming (*SYS): In the system naming convention, files are qualified by
library name in the form:
library/file

If the table name is not explicitly qualified and a default collection name is specified
for the default relational database collection (DFTRDBCOL) parameter of the
CRTSQLxxx 1 or the CRTSQLPKG commands, the default collection name is used.
If the table name is not explicitly qualified and the default collection name is not
specified, the qualification rules are:
v The following CREATE statements resolve to unqualified objects as follows:
– CREATE TABLE – The table is created in the current library (*CURLIB).
– CREATE VIEW – The view is created in the first library referenced in the
subselect.
– CREATE INDEX – The index is created into the collection or library that
contains the table on which the index is being built.

1. The xxx in this command refers to the host language indicators: CI for the ILE C for AS/400 language, CPPI for the ILE C++ for
AS/400 language, CBL for the COBOL for AS/400 language, CBLI for the ILE COBOL for AS/400 language, PLI for the AS/400
PL/I language, RPG for the RPG for AS/400 language, and RPGI for the ILE RPG for AS/400 language. The CVTSQLCPP
command is considered part of this group of commands even though it does not start with CRT.

Chapter 1. Introduction to DB2 UDB for AS/400 Structured Query Language 3

| – CREATE ALIAS – The alias is created into the collection or library that
| contains the table for which you defined the alias. If the table is not qualified
| or is not found, the alias is created in the current library (*CURLIB).
v All other SQL statements cause SQL to search the library list (*LIBL) for the
unqualified table.

The default relational database collection (DFTRDBCOL) parameter applies only to

static SQL statements.

SQL naming (*SQL): In the SQL naming convention, tables are qualified by the
collection name in the form:
collection.table

If the table name is not explicitly qualified and the default collection name is
specified in the default relational database collection (DFTRDBCOL) parameter of
the CRTSQLxxx command, the default collection name is used. If the table name is
not explicitly qualified and the default collection name is not specified, the rules are:
v For static SQL, the default qualifier is the user profile of the program owner.
v For dynamic SQL or interactive SQL, the default qualifier is the user profile of the
job running the statement.

Types of SQL Statements

There are four basic types of SQL statements: data definition language (DDL)
statements, data manipulation language (DML) statements, dynamic SQL
statements, and miscellaneous statements. SQL statements can operate on objects
that are created by SQL as well as AS/400 externally described physical files and
AS/400 single-format logical files, whether or not they reside in an SQL collection.
They do not refer to the IDDU dictionary definition for program-described files.
Program-described files appear as a table with only a single column.

4 DB2 UDB for AS/400 SQL Programming V4R4

 SQL DDL Statements SQL DML Statements
ALTER TABLE CLOSE
COMMENT ON COMMIT
CREATE ALIAS DECLARE CURSOR
CREATE COLLECTION DELETE
CREATE DISTINCT TYPE FETCH
CREATE FUNCTION INSERT
CREATE INDEX LOCK TABLE
CREATE PROCEDURE OPEN
CREATE SCHEMA ROLLBACK
CREATE TABLE SELECT INTO
CREATE VIEW SET variable
DROP ALIAS UPDATE
DROP COLLECTION VALUES INTO
DROP DISTINCT TYPE
DROP FUNCTION
DROP INDEX
DROP PACKAGE
DROP PROCEDURE
DROP SCHEMA
DROP TABLE
DROP VIEW
GRANT DISTINCT TYPE
GRANT FUNCTION
GRANT PACKAGE
GRANT PROCEDURE
GRANT TABLE
LABEL ON
RENAME
REVOKE DISTINCT TYPE
REVOKE FUNCTION
REVOKE PACKAGE
REVOKE PROCEDURE
REVOKE TABLE

Dynamic SQL Statements Miscellaneous Statements

DESCRIBE BEGIN DECLARE SECTION
EXECUTE CALL
EXECUTE IMMEDIATE CONNECT
PREPARE DECLARE PROCEDURE
DECLARE STATEMENT
DECLARE VARIABLE
DESCRIBE TABLE
DISCONNECT
END DECLARE SECTION
FREE LOCATOR
INCLUDE
RELEASE
SET CONNECTION
SET OPTION
SET PATH
SET RESULT SETS
SET TRANSACTION
WHENEVER

SQL Objects
SQL objects used on the AS/400 system are collections, tables, aliases, views, SQL
packages, indexes, and catalogs. SQL creates and maintains these objects as
AS/400 database objects. A brief description of these objects follows.

Chapter 1. Introduction to DB2 UDB for AS/400 Structured Query Language 5

Collections
A collection provides a logical grouping of SQL objects. A collection consists of a
library, a journal, a journal receiver, a catalog, and optionally, a data dictionary.
Tables, views, and system objects (such as programs) can be created, moved, or
restored into any AS/400 library. All AS/400 files can be created or moved into an
SQL collection if the SQL collection does not contain a data dictionary. If the SQL
collection contains a data dictionary then:
v AS/400 source physical files or nonsource physical files with one member can be
created, moved, or restored into an SQL collection.
v AS/400 logical files cannot be placed in an SQL collection because they cannot
be described in the data dictionary.
You can create and own many collections.

Data Dictionary
A collection contains a data dictionary if it was created prior to Version 3 Release 1
or if the WITH DATA DICTIONARY clause was specified on the CREATE
COLLECTION or the CREATE SCHEMA statements. A data dictionary is a set of
tables containing object definitions. If SQL created the dictionary, then it is
automatically maintained by the system. You can work with data dictionaries by
using the interactive data definition utility (IDDU), which is part of the OS/400
program. For more information on IDDU, see the IDDU Use book.

Journals and Journal Receivers

A journal and journal receiver are used to record changes to tables and views in
the database. The journal and journal receiver are then used in processing SQL
COMMIT and ROLLBACK statements. The journal and journal receiver can also be
used as an audit trail or for forward or backward recovery. For more information on
journaling, see the Backup and Recovery book.

Catalogs
An SQL catalog consists of a set of tables and views which describe tables, views,
indexes, packages, procedures, files, and constraints. This information is contained
in a set of cross-reference tables in libraries QSYS and QSYS2. Library QSYS2
also contains a set of catalog views built over the QSYS catalog tables which
describe information about all the tables, views, indexes, packages, procedures,
files, and constraints on the system. In each SQL collection there is a set of views
built over the catalog tables which contains information about the tables, views,
indexes, packages, files, and constraints in the collection.

A catalog is automatically created when you create a collection. You cannot drop or
explicitly change the catalog.

For more information about SQL catalogs, see the DB2 UDB for AS/400 SQL
Reference book.

Tables, Rows, and Columns

A table is a two-dimensional arrangement of data consisting of rows and columns.
The row is the horizontal part containing one or more columns. The column is the
vertical part containing one or more rows of data of one data type. All data for a

6 DB2 UDB for AS/400 SQL Programming V4R4

 column must be of the same type. A table in SQL is a keyed or nonkeyed physical
file. See the section on data types in the DB2 UDB for AS/400 SQL Reference book
for a description of data types.

Data in a table can be distributed across AS/400 systems. For more information
about distributed tables, see the DB2 Multisystem for AS/400 book.

The following is a sample SQL table:

Columns

PROJNO PROJNAME DEPTNO DEPTMGR PRSTAFF

Rows MA2100 MFG AUTOMATION D11 000060 12

MA2110 MFG PROGRAMMING E21 000100 3

MA2112 ROBOT DESIGN E01 000050 3

MA2113 PROD CONTROL PROG D11 000060 3

... ... ... ... ...

RV2W573-0

Aliases
An alias is an alternate name for a table or view. You can use an alias to refer to a
table or view in those cases where an existing table or view can be referred to. For
more information on aliases, see the DB2 UDB for AS/400 SQL Reference book.

Views
A view appears like a table to an application program; however, a view contains no
data. It is created over one or more tables. A view can contain all the columns of
given tables or some subset of them, and can contain all the rows of given tables or
some subset of them. The columns can be arranged differently in a view than they
are in the tables from which they are taken. A view in SQL is a special form of a
nonkeyed logical file.

The following figure shows a view created from the preceding example of an SQL
table. Notice that the view is created only over the PROJNO and PROJNAME
columns of the table and for rows MA2110 and MA2100.

Columns

PROJNO PROJNAME

Rows MA2100 MFG AUTOMATION

MA2110 MFG PROGRAMMING

RV2W574-0

Chapter 1. Introduction to DB2 UDB for AS/400 Structured Query Language 7

Indexes
An SQL index is a subset of the data in the columns of a table that are logically
arranged in either ascending or descending order. Each index contains a separate
arrangement. These arrangements are used for ordering (ORDER BY clause),
grouping (GROUP BY clause), and joining. An SQL index is a keyed logical file.

The index is used by the system for faster data retrieval. Creating an index is
optional. You can create any number of indexes. You can create or drop an index at
any time. The index is automatically maintained by the system. However, because
the indexes are maintained by the system, a large number of indexes can adversely
affect the performance of applications that change the table.

Constraints
Constraints are rules enforced by the database manager. DB2 UDB for AS/400
supports the following constraints:
v Unique constraints
A unique constraint is the rule that the values of the key are valid only if they
are unique. Unique constraints can be created using the CREATE TABLE and
ALTER TABLE statements. 2
Unique constraints are enforced during the execution of INSERT and UPDATE
statements. A PRIMARY KEY constraint is a form of UNIQUE constraint. The
difference is that a PRIMARY KEY cannot contain any nullable columns.
v Referential constraints
A referential constraint is the rule that the values of the foreign key are valid
only if:
– They appear as values of a parent key, or
– Some component of the foreign key is null.
Referential constraints are enforced during the execution of INSERT, UPDATE,
and DELETE statements.
v Check constraints
A check constraint is a rule that limits the values allowed in a column or group
of columns. Check constraints can be added using the CREATE TABLE and
ALTER TABLE statements. Check constraints are enforced during the execution
of INSERT and UPDATE statements. To satisfy the constraint, each row of data
inserted or updated in the table must make the specified condition either TRUE
or unknown (due to a null value).

For more information on constraints, see Chapter 6. Data Integrity.

Triggers
A trigger is a set of actions that are executed automatically whenever a specified
event occurs to a specified base table. An event can be an insert, update, or delete
operation. The trigger can be run either before or after the event. For more
information on triggers, see Chapter 6. Data Integrity in this book or see the DB2
UDB for AS/400 Database Programming book.

2. Although CREATE INDEX can create a unique index that also guarantees uniqueness, such an index is not a constraint.

8 DB2 UDB for AS/400 SQL Programming V4R4

 Stored Procedures
| A stored procedure is a program that can be called using the SQL CALL
| statement. DB2 UDB for AS/400 supports external stored procedures and SQL
| procedures. External stored procedures can be any AS/400 program or REXX
| procedure. They cannot be System/36 programs or procedures, or service
| programs. An SQL procedure is defined entirely in SQL and can contain SQL
| statements including SQL control statements. For more information on stored
| procedures, see Chapter 7. Stored Procedures.

| User-defined functions
| A user-defined function is a program that can be invoked like any built-in function.
| DB2 UDB for AS/400 supports external functions, SQL functions, and sourced
| functions. External functions can be any AS/400 ILE program or service program.
| An SQL function is defined entirely in SQL and can contain SQL statements,
| including SQL control statements. A sourced function is built over any built-in or any
| existing user-defined function. For more information on user-defined functions, see
| “Chapter 9. Writing User-Defined Functions (UDFs)” on page 185.

Packages
An SQL package is an object that contains the control structure produced when the
SQL statements in an application program are bound to a remote relational
database management system (DBMS). The DBMS uses the control structure to
process SQL statements encountered while running the application program.

SQL packages are created when a relational database name (RDB parameter) is
specified on a Create SQL (CRTSQLxxx) command and a program object is
created. Packages can also be created using the CRTSQLPKG command. For
more information about packages and distributed relational database function, see
Chapter 28. Distributed Relational Database Function

SQL packages can also be created using the QSQPRCED API. For more
information on QSQPRCED, see the System API Reference book.

Application Program Objects

The process of creating a DB2 UDB for AS/400 application program may result in
the creation of several objects. This section briefly describes the process of creating
a DB2 UDB for AS/400 application. DB2 UDB for AS/400 supports both non-ILE and
ILE precompilers. Application programs may be either distributed or nondistributed.
Additional information on creating DB2 UDB for AS/400 application programs is in
Chapter 18. Preparing and Running a Program with SQL Statements.

With DB2 UDB for AS/400 you may need to manage the following objects:
v The original source
v Optionally, the module object for ILE programs
v The program or service program
v The SQL package for distributed programs

With a nondistributed non-ILE DB2 UDB for AS/400 program, you must manage
only the original source and the resulting program. The following shows the objects

Chapter 1. Introduction to DB2 UDB for AS/400 Structured Query Language 9

 involved and steps that happen during the precompile and compile processes for a
nondistributed non-ILE DB2 UDB for AS/400 program:

User Temporary
Source Precompile Source Compile
Program
File File
Member Member

Processed Access
SQL Plan
Statements

RV2W565-1

With a nondistributed ILE DB2 UDB for AS/400 program, you may need to manage
the original source, the modules, and the resulting program or service program. The
following shows the objects involved and steps that happen during the precompile
and compile processes for a nondistributed ILE DB2 UDB for AS/400 program when
OBJTYPE(*PGM) is specified on the precompile command:

User Temporary
Source Precompile Source Compile Module Bind Program
File File
Member Member

Processed Processed Access

SQL SQL Plan
Statements Statements

RV2W569-0

With a distributed non-ILE DB2 UDB for AS/400 program, you must manage the
original source, the resulting program, and the resulting package. The following
shows the objects and steps that occur during the precompile and compile
processes for a distributed non-ILE DB2 UDB for AS/400 program:

Create
User Temporary
Source Precompile Source Compile SQL
Program SQL
File File Package
Member Package
Member

Processed Access
SQL Plan Access
Statements Plan

RV2W566-2

With a distributed ILE DB2 UDB for AS/400 program, you must manage the original
source, module objects, the resulting program or service program, and the resulting
packages. An SQL package can be created for each distributed module in a
distributed ILE program or service program. The following shows the objects and
steps that occur during the precompile and compile processes for a distributed ILE
DB2 UDB for AS/400 program:

10 DB2 UDB for AS/400 SQL Programming V4R4

 Create
User Temporary SQL
Source Precompile Source Compile Module Bind Program Package SQL
File File Package
Member Member

Processed Processed Access Access

SQL SQL Plan Plan
Statements Statements

RV2W570-1

Note: The access plans associated with the DB2 UDB for AS/400 distributed
program object are not created until the program is run locally.

User Source File Member

A source file member contains the programmer’s application language and SQL
statements. You can create and maintain the source file member by using the
source entry utility (SEU), a part of the AS/400 Application Development Tools
licensed program.

Output Source File Member

The SQL precompile creates an output source file member. By default, the
precompile process (CRTSQLxxx commands) creates a temporary source file called
QSQLTEMP (QSQLTEMP1 for CRTSQLRPGI) in library QTEMP. If the precompile
process uses the QTEMP library, the system automatically deletes the file when the
job completes. You can specify the output source file as a permanent file name on
the precompile commands. A member with the same name as the program name is
added to the output source file. This member contains the following items:
v Calls to the SQL run-time support, which have replaced embedded SQL
statements
v Parsed and syntax-checked SQL statements
By default, the precompiler calls the host language compiler. For more information
on precompilers, see Chapter 18. Preparing and Running a Program with SQL
Statements.

Program
A program is the object which you can run that is created as a result of the compile
process for non-ILE compiles or as a result of the bind process for ILE compiles.

An access plan is a set of internal structures and information that tells SQL how to
run an embedded SQL statement most effectively. It is created only when the
program has successfully created. Access plans are not created during program
creation for SQL statements if the statements:
v Refer to a table or view that cannot be found
v Refer to a table or view to which you are not authorized

The access plans for such statements are created when the program is run. If, at
that time, the table or view still cannot be found or you are still not authorized, a
negative SQLCODE is returned. Access plans are stored and maintained in the
program object for nondistributed SQL programs and in the SQL package for
distributed SQL programs.

Chapter 1. Introduction to DB2 UDB for AS/400 Structured Query Language 11

Package
An SQL package contains the access plans for a distributed SQL program.

An SQL package is an object that is created when:

v A distributed SQL program is successfully created using the RDB parameter on
CRTSQLxxx commands.
v When the Create SQL Package (CRTSQLPKG) command is run.

When a distributed SQL program is created, the name of the SQL package and an
internal consistency token are saved in the program. These are used at run time to
find the SQL package and to verify that the SQL package is correct for this
program. Because the name of the SQL package is critical for running distributed
SQL programs, an SQL package cannot be:
v Moved
v Renamed
v Duplicated
v Restored to a different library

Module
A module is an Integrated Language Environment (ILE) object that is created by
compiling source code using the CRTxxxMOD command (or any of the CRTBNDxxx
commands where xxx is C, CBL, CPP, or RPG). You can run a module only if you
use the Create Program (CRTPGM) command to bind it into a program. You usually
bind several modules together, but you can bind a module by itself. Modules
contain information about the SQL statements; however, the SQL access plans are
not created until the modules are bound into either a program or service program.

Service Program
A service program is an Integrated Language Environment (ILE) object that
provides a means of packaging externally supported callable routines (functions or
procedures) into a separate object. Bound programs and other service programs
can access these routines by resolving their imports to the exports provided by a
service program. The connections to these services are made when the calling
programs are created. This improves call performance to these routines without
including the code in the calling program.

12 DB2 UDB for AS/400 SQL Programming V4R4

 Chapter 2. Getting Started with SQL
| This chapter describes how to create and work with SQL collections, tables, and
| views using SQL statements.

The syntax for each of the SQL statements used in this chapter is described in
detail in the DB2 UDB for AS/400 SQL Reference book. A description of how to use
SQL statements and clauses in more complex situations is provided in Chapter 3.
Basic Concepts and Techniques and Chapter 5. Advanced Coding Techniques.

| In this chapter, the examples use the interactive SQL interface to show the
| execution of SQL statements. Each SQL interface provides methods for using SQL
| statements to define tables, views, and other objects, methods for updating the
| objects, and methods for reading data from the objects. Some tasks described here
| can also be done using Operations Navigator. In those cases, the information about
| the task notes that it can be done using Operations Navigator.

Starting Interactive SQL

To start using interactive SQL for the following examples, type:
STRSQL NAMING(*SQL)

and press Enter. When the Enter SQL Statements display appears, you are ready
to start typing SQL Statements. For more information on interactive SQL and the
STRSQL command, see Chapter 19. Using Interactive SQL.

If you are reusing an existing interactive SQL session, make sure that you set the
naming mode to SQL naming. You can specify this on the F13 (Services) panel,
option 1 (Change session attributes).

Creating an SQL Collection

| An SQL collection is the basic object in which tables, views, indexes, and packages
| are placed. One way to create a collection (called a library) is by using Operations
| Navigator. Or you can use the SQL CREATE COLLECTION statement.

For an example of creating a collection using interactive SQL, see “Example:

Creating the SQL Collection (SAMPLECOLL)”.

Example: Creating the SQL Collection (SAMPLECOLL)

| You can create a sample collection, named SAMPLECOLL, by typing the following
| SQL statement on the Enter SQL Statements display and pressing Enter:

© Copyright IBM Corp. 1997, 1999 13

| Enter SQL Statements
|
| Type SQL statement, press Enter.
| Current connection is to relational database SYSTEM1
| ===> CREATE COLLECTION SAMPLECOLL_________________________________________
| _____________________________________________________________________
| _____________________________________________________________________
| _____________________________________________________________________
| Bottom
| F3=Exit F4=Prompt F6=Insert line F9=Retrieve F10=Copy line
| F12=Cancel F13=Services F24=More keys
|

| Note: Running this statement causes several objects to be created and takes
| several seconds.
| After you have successfully created a collection, you can create tables, views, and
| indexes in it. Tables, views, and indexes can also be created in libraries instead of
| collections.

Creating and Using a Table

| You can create a table by using the SQL CREATE TABLE statement or by using
| Operations Navigator. The CREATE TABLE statement allows you to create a table,
| define the physical attributes of the columns in the table, and define constraints to
| restrict the values that are allowed in the table.

For an example of creating a table using interactive SQL, see “Example: Creating a
Table (INVENTORY_LIST)”.

| When creating a table, you need to understand the concepts of null value and
| default value. A null value indicates the absence of a column value for a row. It is
| not the same as a value of zero or all blanks. It means ″unknown″. It is not equal to
| any value, not even to other null values. If a column does not allow the null value, a
| value must be assigned to the column, either a default value or a user supplied
| value.

| A default value is assigned to a column when a row is added to a table and no

| value is specified for that column. If a specific default value is not defined for a
| column, the system default value will be used. For more information on the default
| values used by INSERT, see “The INSERT Statement” on page 31

| Example: Creating a Table (INVENTORY_LIST)

| We are going to create a table to maintain information about the current inventory
| of a business. It will have information about the items kept in the inventory, their
| cost, quantity currently on hand, the last order date, and the number last ordered.
| The item number will be a required value. It cannot be null. The item name, quantity
| on hand, and order quantity will have user supplied default values. The last order
| date and quantity ordered will allow the null value.

| On the Enter SQL Statements display, type CREATE TABLE and press F4 (Prompt).
| The following display is shown (with the input areas not yet filled in):

14 DB2 UDB for AS/400 SQL Programming V4R4

| Specify CREATE TABLE Statement
|
| Type information, press Enter.
|
| Table . . . . . . . . . INVENTORY_LIST______ Name
| Collection . . . . . . SAMPLECOLL__ Name, F4 for list
|
| Nulls: 1=NULL, 2=NOT NULL, 3=NOT NULL WITH DEFAULT
|
| Column FOR Column Type Length Scale Nulls
| ITEM_NUMBER_______ ____________ CHAR___________ 6____ __ 2
| ITEM_NAME_________ ____________ VARCHAR________ 20___ __ 3
| UNIT_COST_________ ____________ DECIMAL________ 8____ 2_ 3
| QUANTITY_ON_HAND__ ____________ SMALLINT_______ _____ __ 1
| LAST_ORDER_DATE___ ____________ DATE___________ _____ __ 1
| ORDER_QUANTITY____ ____________ SMALLINT_______ _____ __ 1
| __________________ ____________ _______________ _____ __ 3
| Bottom
| Table CONSTRAINT . . . . . . . . . . . . . N Y=Yes, N=No
| Distributed Table . . . . . . . . . . . . N Y=Yes, N=No
|
| F3=Exit F4=Prompt F5=Refresh F6=Insert line F10=Copy line
| F11=Display more attributes F12=Cancel F14=Delete line F24=More keys
|

| Type the table name and collection name of the table you are creating,
| INVENTORY_LIST in SAMPLECOLL, for the Table and Collection prompts. Each
| column you want to define for the table is represented by an entry in the list on the
| lower part of the display. For each column, type the name of the column, the data
| type of the column, its length and scale, and the null attribute.

| Press F11 to see more attributes that can be specified for the columns. This is
| where a default value may be specified.
| Specify CREATE TABLE Statement
|
| Type information, press Enter.
|
| Table . . . . . . . . . INVENTORY_LIST______ Name
| Collection . . . . . . SAMPLECOLL__ Name, F4 for list
|
| Data: 1=BIT, 2=SBCS, 3=MIXED, 4=CCSID
|
| Column Data Allocate
CCSID CONSTRAINT Default
| ITEM NUMBER_______ _ __________ N __________________
| ITEM NAME_________ _ __________ N '***UNKNOWN***'___
| UNIT_COST_________ _ __________ N __________________
| QUANTITY_ON_HAND__ _ __________ N NULL______________
| LAST_ORDER_DATE___ _ __________ N __________________
| ORDER_QUANTITY____ _ __________ N 20________________
| __________________ _ __________ _ __________________
| Bottom
| Table CONSTRAINT . . . . . . . . . . . . . N Y=Yes, N=No
| Distributed Table . . . . . . . . . . . . N Y=Yes, N=No
|
| F3=Exit F4=Prompt F5=Refresh F6=Insert line F10=Copy line
| F11=Display more attributes F12=Cancel F14=Delete line F24=More keys
|

| Note: Another way of entering column definitions is to press F4 (Prompt) with your
| cursor on one of the column entries in the list. This will bring up a display
| that shows all of the attributes for defining a single column.

| When all the values have been entered, press Enter to create the table. The Enter
| SQL Statements display will be shown again with a message indicating that the
| table has been created.

Chapter 2. Getting Started with SQL 15

| You can directly key in this CREATE TABLE statement on the Enter SQL
| Statements display as follows:
| CREATE TABLE SAMPLECOLL.INVENTORY_LIST
| (ITEM_NUMBER CHAR(6) NOT NULL,
| ITEM_NAME VARCHAR(20) NOT NULL WITH DEFAULT '***UNKNOWN***',
| UNIT_COST DECIMAL(8,2) NOT NULL WITH DEFAULT,
| QUANTITY_ON_HAND SMALLINT DEFAULT NULL,
| LAST_ORDER_DATE DATE,
| ORDER_QUANTITY SMALLINT DEFAULT 20)

| Creating the Supplier Table (SUPPLIERS)

Later in our examples, we will need a second table as well. This table will contain
information about suppliers of our inventory items, which items they supply, and the
cost of the item from that supplier. To create it, either type it in directly on the Enter
SQL Statements display or press F4 (Prompt) to use the interactive SQL displays to
create the definition.
CREATE TABLE SAMPLECOLL.SUPPLIERS
(SUPPLIER_NUMBER CHAR(4) NOT NULL,
ITEM_NUMBER CHAR(6) NOT NULL,
SUPPLIER_COST DECIMAL(8,2))

Using the LABEL ON Statement

Normally, the column name is used as the column heading when showing the
output of a SELECT statement in interactive SQL. By using the LABEL ON
statement, you can create a more descriptive label for the column name. Since we
are going to be running our examples in interactive SQL, we will use the LABEL ON
statement to change the column headings. Even though the column names are
descriptive, it will be easier to read if the column headings show each part of the
name on a single line. It will also allow us to see more columns of our data on a
single display.

To change the labels for our columns, type LABEL ON COLUMN on the Enter SQL
Statements display and press F4 (Prompt). The following display will appear:

16 DB2 UDB for AS/400 SQL Programming V4R4

 Specify LABEL ON Statement

Type choices, press Enter.

Label on . . . . 2 1=Table or view

2=Column
3=Package
4=Alias

Table or view INVENTORY_LIST______ Name, F4 for list

Collection . . SAMPLECOLL__ Name, F4 for list

Option . . . . . 1 1=Column heading

2=Text

F3=Exit F4=Prompt F5=Refresh F12=Cancel F20=Display full names

F21=Display statement

Type in the name of the table and collection containing the columns for which you
want to add labels and press Enter. The following display will be shown, prompting
you for each of the columns in the table.
Specify LABEL ON Statement

Type information, press Enter.

Column Heading
Column ....+....1....+....2....+....3....+....4....+....5....
ITEM_NUMBER 'ITEM NUMBER'___________________________
ITEM_NAME 'ITEM NAME'_____________________________
UNIT_COST 'UNIT COST'_____________________________
QUANTITY_ON_HAND 'QUANTITY ON HAND'_________
LAST_ORDER_DATE 'LAST ORDER DATE'_________
ORDER_QUANTITY 'NUMBER ORDERED'__________________________

Bottom
F3=Exit F5=Refresh F6=Insert line F10=Copy line F12=Cancel
F14=Delete line F19=Display system column names F24=More keys

Type the column headings for each of the columns. Column headings are defined in
20 character sections. Each section will be displayed on a different line when
showing the output of a SELECT statement. The ruler across the top of the column
heading entry area can be used to easily space the headings correctly. When the
headings are typed, press Enter.

The following message indicates that the LABEL ON statement was successful.
LABEL ON for INVEN00001 in SAMPLECOLL completed.

The table name in the message is the system table name for this table, not the
name that was actually specified in the statement. DB2 UDB for AS/400 maintains

Chapter 2. Getting Started with SQL 17

 two names for tables with names longer than ten characters. For more information
on system table names, see the CREATE TABLE statement in the DB2 UDB for
AS/400 SQL Reference book.

The LABEL ON statement can also be keyed in directly on the Enter SQL
statements display as follows:
LABEL ON SAMPLECOLL/INVENTORY_LIST
(ITEM_NUMBER IS 'ITEM NUMBER',
ITEM_NAME IS 'ITEM NAME',
UNIT_COST IS 'UNIT COST',
QUANTITY_ON_HAND IS 'QUANTITY ON HAND',
LAST_ORDER_DATE IS 'LAST ORDER DATE',
ORDER_QUANTITY IS 'NUMBER ORDERED')

Inserting Information into a Table

After you create a table, you can insert, or add, information (data) into it by using
Operations Navigator. Or you can use the SQL INSERT statement.

| For an example of inserting data into a table using interactive SQL, see “Example:
| Inserting Information into a Table (INVENTORY_LIST)”.

| Example: Inserting Information into a Table (INVENTORY_LIST)

| To work with interactive SQL, on the Enter SQL Statements display, type INSERT
| and press F4 (Prompt). The Specify INSERT Statement display will be shown.
| Specify INSERT Statement
|
| Type choices, press Enter.
|
| INTO table . . . . . . . INVENTORY_LIST______ Name, F4 for list
| Collection . . . . . . SAMPLECOLL__ Name, F4 for list
|
| Select columns to insert
| INTO . . . . . . . . . Y Y=Yes, N=No
| Insertion method . . . . 1 1=Input VALUES
| 2=Subselect
|
| Type choices, press Enter.
|
| WITH isolation level . . 1 1=Current level, 2=NC (NONE)
| 3=UR (CHG), 4=CS, 5=RS (ALL)
| 6=RR
|
|
|
| F3=Exit F4=Prompt F5=Refresh F12=Cancel F20=Display full names
| F21=Display statement
|

Type the table name and collection name in the input fields as shown. Change the
Select columns to insert INTO prompt to Yes. Press Enter to see the display where
the columns you want to insert values into can be selected.

18 DB2 UDB for AS/400 SQL Programming V4R4

 Specify INSERT Statement

Type sequence numbers (1-999) to make selections, press Enter.

Seq Column Type Length Scale

1__ ITEM_NUMBER CHARACTER 6
2__ ITEM_NAME VARCHAR 20
3__ UNIT_COST DECIMAL 8 2
4__ QUANTITY_ON_HAND SMALLINT 4
___ LAST_ORDER_DATE DATE
___ ORDER_QUANTITY SMALLINT 4

Bottom
F3=Exit F5=Refresh F12=Cancel F19=Display system column names
F20=Display entire name F21=Display statement

In this example, we only want to insert into four of the columns. We will let the other
| columns have their default value inserted. The sequence numbers on this display
| indicate the order that the columns and values will be listed in the INSERT
| statement. Press Enter to show the display where values for the selected columns
| can be typed.
| Specify INSERT Statement
|
| Type values to insert, press Enter.
|
| Column Value
| ITEM_NUMBER '153047'_____________________________________________
| ITEM_NAME 'Pencils, red'_______________________________________
| UNIT_COST 10.00________________________________________________
| QUANTITY_ON_HAND 25___________________________________________________
|
|
|
|
|
|
|
|
|
|
|
| Bottom
| F3=Exit F5=Refresh F6=Insert line F10=Copy line F11=Display type
| F12=Cancel F14=Delete line F15=Split line F24=More keys
|

| Note: To see the data type and length for each of the columns in the insert list,
| press F11 (Display type). This will show a different view of the insert values
| display, providing information about the column definition.

| Type the values to be inserted for all of the columns and press Enter. A row
| containing these values will be added to the table. The values for the columns that
| were not specified will have a default value inserted. For LAST_ORDER_DATE it
| will be the null value since no default was provided and the column allows the null
| value. For ORDER_QUANTITY it will be 20, the value specified as the default value
| on the CREATE TABLE statement.

| You can type the INSERT statement on the Enter SQL Statements display as:
| INSERT INTO SAMPLECOLL.INVENTORY_LIST
| (ITEM_NUMBER,
| ITEM_NAME,
| UNIT_COST,
| QUANTITY_ON_HAND)

Chapter 2. Getting Started with SQL 19

| VALUES('153047',
| 'Pencils, red',
| 10.00,
| 25)

| To add the next row to the table, press F9 (Retrieve) on the Enter SQL Statements
| display. This will copy the previous INSERT statement to the typing area. You can
| either type over the values from the previous INSERT statement or press F4
| (Prompt) to use the Interactive SQL displays to enter data.

| Continue using the INSERT statement to add the following rows to the table. Values
| not shown in the chart below should not be inserted so that the default will be used.
| In the INSERT statement column list, specify only the column names for which you
| want to insert a value. For example, to insert the third row, you would specify only
| ITEM_NUMBER and UNIT_COST for the column names and only the two values
| for these columns in the VALUES list.

ITEM_NUMBER ITEM_NAME UNIT_COST QUANTITY_ON_HAND

153047 Pencils, red 10.00 25
229740 Lined tablets 1.50 120
544931 5.00
303476 Paper clips 2.00 100
559343 Envelopes, legal 3.00 500
291124 Envelopes, standard
775298 Chairs, secretary 225.00 6
073956 Pens, black 20.00 25

Add the following rows to the SAMPLECOLL.SUPPLIERS table.

SUPPLIER_NUMBER ITEM_NUMBER SUPPLIER_COST

1234 153047 10.00
1234 229740 1.00
1234 303476 3.00
9988 153047 8.00
9988 559343 3.00
2424 153047 9.00
2424 303476 2.50
5546 775298 225.00
3366 303476 1.50
3366 073956 17.00

| The sample collection now contains two tables with several rows of data in each.

Getting Information from a Single Table

Now that we have inserted all the information into our tables, we need to be able to
look at it again. In SQL, this is done with the SELECT statement. The SELECT
statement is the most complex of all SQL statements. This statement is composed
of three main clauses:

20 DB2 UDB for AS/400 SQL Programming V4R4

1. The SELECT clause, which specifies those columns containing the desired
data.
2. The FROM clause, which specifies the table or tables containing the columns
with the desired data.
3. The WHERE clause, which supplies conditions that determine which rows of
data are retrieved.

In addition to the three main clauses, there are several other clauses described in
“Using Basic SQL Statements and Clauses” on page 31, and in the DB2 UDB for
AS/400 SQL Reference book, which affect the final form of returned data.

To see the values we inserted into the INVENTORY_LIST table, type SELECT and
press F4 (prompt). The following display will be shown:
Specify SELECT Statement

Type SELECT statement information. Press F4 for a list.

FROM tables . . . . . . . . SAMPLECOLL.INVENTORY_LIST____________________

SELECT columns . . . . . . . *____________________________________________
WHERE conditions . . . . . . _____________________________________________
GROUP BY columns . . . . . . _____________________________________________
HAVING conditions . . . . . _____________________________________________
ORDER BY columns . . . . . . _____________________________________________
FOR UPDATE OF columns . . . _____________________________________________

Bottom
Type choices, press Enter.

DISTINCT rows in result table . . . . . . . . . N Y=Yes, N=No

UNION with another SELECT . . . . . . . . . . . N Y=Yes, N=No
Specify additional options . . . . . . . . . . . N Y=Yes, N=No

F3=Exit F4=Prompt F5=Refresh F6=Insert line F9=Specify subquery

F10=Copy line F12=Cancel F14=Delete line F15=Split line F24=More keys

Type the table name in the FROM tables field on the display. To select all columns
from the table, type * for the SELECT columns field on the display. Press Enter and
the statement will run to select all of the data for all of the columns in the table. The
following output will be shown:
Display Data
Data width . . . . . . : 71
Position to line . . . . . Shift to column . . . . . .
....+....1....+....2....+....3....+....4....+....5....+....6....+....7.
ITEM ITEM UNIT QUANTITY LAST NUMBER
NUMBER NAME COST ON ORDER ORDERED
HAND DATE
153047 Pencils, red 10.00 25 - 20
229740 Lined tablets 1.50 120 - 20
544931 ***UNKNOWN*** 5.00 - - 20
303476 Paper clips 2.00 100 - 20
559343 Envelopes, legal 3.00 500 - 20
291124 Envelopes, standard .00 - - 20
775298 Chairs, secretary 225.00 6 - 20
073956 Pens, black 20.00 25 - 20
******** End of data ********

F3=Exit F12=Cancel F19=Left F20=Right F21=Split

The column headings that were defined using the LABEL ON statement are shown.
The ITEM_NAME for the third entry has the default value that was specified in the
CREATE TABLE statement. The QUANTITY_ON_HAND column has a null value for

Chapter 2. Getting Started with SQL 21

 the rows where no value was inserted. The LAST_ORDER_DATE column contains
all null values since that column is not in any of the INSERT statements and the
column was not defined to have a default value. Similarly, the ORDER_QUANTITY
column contains the default value for all rows.

This statement could be entered on the Enter SQL Statements display as:
SELECT *
FROM SAMPLECOLL.INVENTORY_LIST

To limit the number of columns returned by the SELECT statement, the columns
you want to see must be specified. To restrict the number of output rows returned,
the WHERE clause is used. To see only the items that cost more than 10 dollars,
and only have the values for the columns ITEM_NUMBER, UNIT_COST, and
ITEM_NAME returned, type SELECT and press F4 (Prompt). The Specify SELECT
Statement display will be shown.
Specify SELECT Statement

Type SELECT statement information. Press F4 for a list.

FROM tables . . . . . . . . SAMPLECOLL.INVENTORY_LIST____________________

SELECT columns . . . . . . . ITEM_NUMBER, UNIT_COST, ITEM_NAME____________
WHERE conditions . . . . . . UNIT_COST > 10.00____________________________
GROUP BY columns . . . . . . _____________________________________________
HAVING conditions . . . . . _____________________________________________
ORDER BY columns . . . . . . _____________________________________________
FOR UPDATE OF columns . . . _____________________________________________

Bottom
Type choices, press Enter.

DISTINCT rows in result table . . . . . . . . . N Y=Yes, N=No

UNION with another SELECT . . . . . . . . . . . N Y=Yes, N=No
Specify additional options . . . . . . . . . . . N Y=Yes, N=No

F3=Exit F4=Prompt F5=Refresh F6=Insert line F9=Specify subquery

F10=Copy line F12=Cancel F14=Delete line F15=Split line F24=More keys

Although only one line is initially shown for each prompt on the Specify SELECT
Statement display, F6 (Insert line) can be used to add more lines to any of the input
areas in the top part of the display. This could be used if more columns were to be
entered in the SELECT columns list, or a longer, more complex WHERE condition
were needed.

Fill in the display as shown above. When Enter is pressed, the SELECT statement
is run. The following output will be seen:
Display Data
Data width . . . . . . : 41
Position to line . . . . . Shift to column . . . . . .
....+....1....+....2....+....3....+....4.
ITEM UNIT ITEM
NUMBER COST NAME
775298 225.00 Chairs, secretary
073956 20.00 Pens, black
******** End of data ********

F3=Exit F12=Cancel F19=Left F20=Right F21=Split

The only rows returned are those whose data values compare with the condition
specified in the WHERE clause. Furthermore, the only data values returned are

22 DB2 UDB for AS/400 SQL Programming V4R4

 from the columns you explicitly specified in the SELECT clause. Data values of
columns other than those explicitly identified are not returned.

This statement could have been entered on the Enter SQL Statements display as:
SELECT ITEM_NUMBER, UNIT_COST, ITEM_NAME
FROM SAMPLECOLL.INVENTORY_LIST
WHERE UNIT_COST > 10.00

Getting Information from More Than One Table

SQL allows you to get information from columns contained in more than one table.
This operation is called a join operation. (For a more detailed description of the join
operation, see “Joining Data from More Than One Table” on page 75). In SQL, a
join operation is specified by placing the names of those tables you want to join
together into the same FROM clause of a SELECT statement.

Suppose you want to see a list of all the suppliers and the item numbers and item
names for their supplied items. The item name is not in the SUPPLIERS table. It is
in the INVENTORY_LIST table. Using the common column, ITEM_NUMBER, we
can see all three of the columns as if they were from a single table.

Whenever the same column name exists in two or more tables being joined, the
column name must be qualified by the table name to specify which column is really
being referenced. In this SELECT statement, the column name ITEM_NUMBER is
defined in both tables so the column name needs to be qualified by the table name.
If the columns had different names, there would be no confusion so qualification
would not be needed.

To perform this join, the following SELECT statement can be used. Enter it by
typing it directly on the Enter SQL Statements display or by prompting. If using
prompting, both table names need to be typed on the FROM tables input line.
SELECT SUPPLIER_NUMBER, SAMPLECOLL.INVENTORY_LIST.ITEM_NUMBER, ITEM_NAME
FROM SAMPLECOLL.SUPPLIERS, SAMPLECOLL.INVENTORY_LIST
WHERE SAMPLECOLL.SUPPLIERS.ITEM_NUMBER
= SAMPLECOLL.INVENTORY_LIST.ITEM_NUMBER

Another way to enter the same statement is to use a correlation name. A correlation
name provides another name for a table name to use in a statement. A correlation
name must be used when the table names are the same. It can be specified
following each table name in the FROM list. The previous statement could be
rewritten as:
SELECT SUPPLIER_NUMBER, Y.ITEM_NUMBER, ITEM_NAME
FROM SAMPLECOLL.SUPPLIERS X, SAMPLECOLL.INVENTORY_LIST Y
WHERE X.ITEM_NUMBER = Y.ITEM_NUMBER

In this example, SAMPLECOLL.SUPPLIERS is given a correlation name of X and

SAMPLECOLL.INVENTORY_LIST is given a correlation name of Y. The names X
and Y are then used to qualify the ITEM_NUMBER column name.

For more information on correlation names, see the DB2 UDB for AS/400 SQL
Reference book.

Running this example returns the following output:

Chapter 2. Getting Started with SQL 23

 Display Data
Data width . . . . . . : 45
Position to line . . . . . Shift to column . . . . . .
....+....1....+....2....+....3....+....4....+
SUPPLIER_NUMBER ITEM ITEM
NUMBER NAME
1234 153047 Pencils, red
9988 153047 Pencils, red
2424 153047 Pencils, red
1234 229740 Lined tablets
1234 303476 Paper clips
2424 303476 Paper clips
3366 303476 Paper clips
9988 559343 Envelopes, legal
5546 775298 Chairs, secretary
3366 073956 Pens, black
******** End of data ********

F3=Exit F12=Cancel F19=Left F20=Right F21=Split

The data values in the result table represent a composite of the data values
contained in the two tables INVENTORY_LIST and SUPPLIERS. This result table
contains the supplier number from the SUPPLIER table and the item number and
item name from the INVENTORY_LIST table. Any item numbers that do not appear
in the SUPPLIER table are not shown in this result table. The results are not
guaranteed to be in any order unless the ORDER BY clause is specified for the
SELECT statement. Since we did not change any column headings for the
SUPPLIER table, the SUPPLIER_NUMBER column name is used as the column
heading.

The following is an example of using ORDER BY to guarantee the order of the

rows. The statement will first order the result table by the SUPPLIER_NUMBER
column. Rows with the same value for SUPPLIER_NUMBER will be ordered by
their ITEM_NUMBER.
SELECT SUPPLIER_NUMBER, Y.ITEM_NUMBER, ITEM_NAME
FROM SAMPLECOLL.SUPPLIERS X, SAMPLECOLL.INVENTORY_LIST Y
WHERE X.ITEM_NUMBER = Y.ITEM_NUMBER
ORDER BY SUPPLIER_NUMBER, Y.ITEM_NUMBER

Running the previous statement would produce the following output.

24 DB2 UDB for AS/400 SQL Programming V4R4

 Display Data
Data width . . . . . . : 45
Position to line . . . . . Shift to column . . . . . .
....+....1....+....2....+....3....+....4....+
SUPPLIER_NUMBER ITEM ITEM
NUMBER NAME
1234 153047 Pencils, red
1234 229740 Lined tablets
1234 303476 Paper clips
2424 153047 Pencils, red
2424 303476 Paper clips
3366 073956 Pens, black
3366 303476 Paper clips
5546 775298 Chairs, secretary
9988 153047 Pencils, red
9988 559343 Envelopes, legal
******** End of data ********

F3=Exit F12=Cancel F19=Left F20=Right F21=Split

Changing Information in a Table

| You can use the SQL UPDATE statement to change the data values in some or all
| of the columns of a table. You may also use Operations Navigator to accomplish
| this task.

For an example of changing information in a table using interactive SQL, see

“Example: Changing Information in a Table”.

| If you want to limit the number of rows being changed during a single statement
| execution, use the WHERE clause with the UPDATE statement. For more
| information see, “The UPDATE Statement” on page 33. If you do not specify the
| WHERE clause, all of the rows in the specified table are changed. However, if you
| use the WHERE clause, the system changes only the rows satisfying the conditions
| that you specify. For more information, see “The WHERE Clause” on page 38.

| Example: Changing Information in a Table

| Suppose we want to use interactive SQL and are placing an order for more paper
| clips today. To update the LAST_ORDER_DATE and ORDER_QUANTITY for item
| number 303476, type UPDATE and press F4 (Prompt). The Specify UPDATE
| Statement display will be shown.

Chapter 2. Getting Started with SQL 25

| Specify UPDATE Statement
|
| Type choices, press Enter.
|
| Table . . . . . . . . INVENTORY_LIST______ Name, F4 for list
| Collection . . . . . SAMPLECOLL__ Name, F4 for list
|
| Correlation . . . . . ____________________ Name
|
|
|
|
|
|
|
|
|
|
|
|
|
| F3=Exit F4=Prompt F5=Refresh F12=Cancel F20=Display full names
| F21=Display statement
|

| After typing the table name and collection name, press Enter. The display will be
| shown again with the list of columns in the table.
| Specify UPDATE Statement
|
| Type choices, press Enter.
|
| Table . . . . . . . . INVENTORY_LIST______ Name, F4 for list
| Collection . . . . . SAMPLECOLL__ Name, F4 for list
|
| Correlation . . . . . ____________________ Name
|
|
| Type information, press Enter.
|
| Column Value
| ITEM_NUMBER _____________________________________________________
| ITEM_NAME _____________________________________________________
| UNIT_COST _____________________________________________________
| QUANTITY_ON_HAND _____________________________________________________
| LAST_ORDER_DATE CURRENT DATE_________________________________________
| ORDER_QUANTITY 50___________________________________________________
|
| Bottom
| F3=Exit F4=Prompt F5=Refresh F6=Insert line F10=Copy line
| F11=Display type F12=Cancel F14=Delete line F24=More keys
|

| Specifying CURRENT DATE for a value will change the date in all the selected
| rows to be today’s date.

| After typing the values to be updated for the table, press Enter to see the display
| on which the WHERE condition can be specified. If a WHERE condition is not
| specified, all the rows in the table will be updated using the values from the
| previous display.

26 DB2 UDB for AS/400 SQL Programming V4R4

| Specify UPDATE Statement
|
| Type WHERE conditions, press Enter. Press F4 for a list.
| ITEM_NUMBER = '303476'________________________________________________
| ______________________________________________________________________
|
|
|
| Bottom
| Type choices, press Enter.
|
| WITH isolation level . . . 1 1=Current level, 2=NC (NONE)
| 3=UR (CHG), 4=CS, 5=RS (ALL)
| 6=RR
|
|
|
|
|
|
|
| F3=Exit F4=Prompt F5=Refresh F6=Insert line F9=Specify subquery
| F10=Copy line F12=Cancel F14=Delete line F15=Split line F24=More keys
|

| After typing the condition, press Enter to perform the update on the table. A
| message will indicate that the function is complete.

| This statement could have been typed on the Enter SQL Statements display as:
| UPDATE SAMPLECOLL.INVENTORY_LIST
| SET LAST_ORDER_DATE = CURRENT DATE,
| ORDER_QUANTITY = 50
| WHERE ITEM_NUMBER = '303476'

| Running a SELECT statement to get all the rows from the table (SELECT * FROM
SAMPLECOLL.INVENTORY_LIST), returns the following result:
Display Data
Data width . . . . . . : 71
Position to line . . . . . Shift to column . . . . . .
....+....1....+....2....+....3....+....4....+....5....+....6....+....7.
ITEM ITEM UNIT QUANTITY LAST NUMBER
NUMBER NAME COST ON ORDER ORDERED
HAND DATE
153047 Pencils, red 10.00 25 - 20
229740 Lined tablets 1.50 120 - 20
544931 ***UNKNOWN*** 5.00 - - 20
303476 Paper clips 2.00 100 05/30/94 50
559343 Envelopes, legal 3.00 500 - 20
291124 Envelopes, standard .00 - - 20
775298 Chairs, secretary 225.00 6 - 20
073956 Pens, black 20.00 25 - 20
******** End of data ********
Bottom
F3=Exit F12=Cancel F19=Left F20=Right F21=Split

Only the entry for Paper clips was changed. The LAST_ORDER_DATE was
changed to be the current date. This date is always the date the update is run. The
NUMBER_ORDERED shows its updated value.

Chapter 2. Getting Started with SQL 27

 Deleting Information from a Table
| You can delete data from a table by using Operations Navigator. Or, using the SQL
| DELETE statement, you can delete entire rows from a table when they no longer
| contain needed information. You can use the WHERE clause with the DELETE
| statement to identify rows to be deleted during a single statement execution. For
| more information, see “The DELETE Statement” on page 34.

| For an example of deleting information in a table using interactive SQL, see

| “Example: Deleting Information from a Table (INVENTORY_LIST)”.

Example: Deleting Information from a Table (INVENTORY_LIST)

If we want to remove all the rows in our table that have the null value for the
QUANTITY_ON_HAND column, you could enter the following statement on the
Enter SQL Statements display:
DELETE
FROM SAMPLECOLL.INVENTORY_LIST
WHERE QUANTITY_ON_HAND IS NULL

To check a column for the null value, the IS NULL comparison is used. Running
another SELECT statement after the delete has completed will return the following
result table:
Display Data
Data width . . . . . . : 71
Position to line . . . . . Shift to column . . . . . .
....+....1....+....2....+....3....+....4....+....5....+....6....+....7.
ITEM ITEM UNIT QUANTITY LAST NUMBER
NUMBER NAME COST ON ORDER ORDERED
HAND DATE
153047 Pencils, red 10.00 25 - 20
229740 Lined tablets 1.50 120 - 20
303476 Paper clips 2.00 100 05/30/94 50
559343 Envelopes, legal 3.00 500 - 20
775298 Chairs, secretary 225.00 6 - 20
073956 Pens, black 20.00 25 - 20
******** End of data ********
Bottom
F3=Exit F12=Cancel F19=Left F20=Right F21=Split

The rows with a null value for QUANTITY_ON_HAND were deleted.

Creating and Using a View

| You may find that no single table contains all the information you need. You may
| also want to give users access to only part of the data in a table. Views provide a
| way to subset the table so that you deal with only the data you need. A view
| reduces complexity and, at the same time, restricts access.

You can create a view using Operations Navigator. Or you can use the SQL
CREATE VIEW statement. Using the CREATE VIEW statement, defining a view on
a table is like creating a new table containing just the columns and rows you want.
When your application uses a view, it cannot access rows or columns of the table
that are not included in the view. However, rows that do not match the selection
criteria may still be inserted through a view if the SQL WITH CHECK OPTION is not
used. See Chapter 6. Data Integrity for more information on using WITH CHECK
OPTION.

28 DB2 UDB for AS/400 SQL Programming V4R4

| For examples of creating a view using interactive SQL, see the following:
| v Example: “Example: Creating a view on a single table” or
| v Example: “Example: Creating a view combining data from more than one table”
| on page 30

| In order to create a view you must have the proper authority to the tables or
physical files on which the view is based. See the CREATE VIEW statement in the
SQL Reference for a list of authorities needed.

If you do not specify column names in the view definition, the column names will be
the same as those for the table on which the view is based.

You can make changes to a table through a view even if the view has a different
number of columns or rows than the table. For INSERT, columns in the table that
are not in the view must have a default value.

You can use the view as though it were a table, even though the view is totally
dependent on one or more tables for data. The view has no data of its own and
therefore requires no storage for the data. Because a view is derived from a table
that exists in storage, when you update the view data, you are really updating data
in the table. Therefore, views are automatically kept up-to-date as the tables they
depend on are updated.

See “Creating and Using Views” on page 95 for additional information.

Example: Creating a view on a single table

The following example shows how to create a view on a single table. The view is
built on the INVENTORY_LIST table. The table has six columns, but the view uses
only three of the columns: ITEM_NUMBER, LAST_ORDER_DATE, and
QUANTITY_ON_HAND. The order of the columns in the SELECT clause is the
order in which they will appear in the view. The view will contain only the rows for
items that were ordered in the last two weeks. The CREATE VIEW statement looks
like this:
CREATE VIEW SAMPLECOLL.RECENT_ORDERS AS
SELECT ITEM_NUMBER, LAST_ORDER_DATE, QUANTITY_ON_HAND
FROM SAMPLECOLL.INVENTORY_LIST
WHERE LAST_ORDER_DATE > CURRENT DATE - 14 DAYS

In the example above, the columns in the view have the same name as the
columns in the table because no column list follows the view name. The collection
that the view is created into does not need to be the same collection as the table it
is built over. Any collection or library could be used. The following display is the
result of running the SQL statement:
SELECT * FROM SAMPLECOLL.RECENT_ORDERS

Chapter 2. Getting Started with SQL 29

 Display Data
Data width . . . . . . : 26
Position to line . . . . . Shift to column . . . . . .
....+....1....+....2....+.
ITEM LAST QUANTITY
NUMBER ORDER ON
DATE HAND
303476 05/30/94 100
******** End of data ********
Bottom
F3=Exit F12=Cancel F19=Left F20=Right F21=Split

The only row selected by the view is the row that we updated to have the current
date. All other dates in our table still have the null value so they are not returned.

Example: Creating a view combining data from more than one table
You can create a view that combines data from two or more tables by naming more
than one table in the FROM clause. In the following example, the
INVENTORY_LIST table contains a column of item numbers called
ITEM_NUMBER, and a column with the cost of the item, UNIT_COST. These are
joined with the ITEM_NUMBER column and the SUPPLIER_COST column of the
SUPPLIERS table. A WHERE clause is used to limit the number of rows returned.
The view will only contain those item numbers for suppliers that can supply an item
at lower cost than the current unit cost.

The CREATE VIEW statement looks like this:

CREATE VIEW SAMPLECOLL.LOWER_COST AS
SELECT SUPPLIER_NUMBER, A.ITEM_NUMBER, UNIT_COST, SUPPLIER_COST
FROM SAMPLECOLL.INVENTORY_LIST A, SAMPLECOLL.SUPPLIERS B
WHERE A.ITEM_NUMBER = B.ITEM_NUMBER
AND UNIT_COST > SUPPLIER_COST

The following table is the result of running the SQL statement:

SELECT * FROM SAMPLECOLL.LOWER_COST

Display Data
Data width . . . . . . : 51
Position to line . . . . . Shift to column . . . . . .
....+....1....+....2....+....3....+....4....+....5.
SUPPLIER_NUMBER ITEM UNIT SUPPLIER_COST
NUMBER COST
9988 153047 10.00 8.00
2424 153047 10.00 9.00
1234 229740 1.50 1.00
3366 303476 2.00 1.50
3366 073956 20.00 17.00
******** End of data ********
Bottom
F3=Exit F12=Cancel F19=Left F20=Right F21=Split

The rows that can be seen through this view are only those rows that have a
supplier cost that is less than the unit cost.

30 DB2 UDB for AS/400 SQL Programming V4R4

Chapter 3. Basic Concepts and Techniques
This chapter explains some of the concepts used in SQL statements. It discusses
many of the common statements and clauses in SQL. The examples in this chapter
refer to the tables shown in Appendix A. DB2 UDB for AS/400 Sample Tables.

Using Basic SQL Statements and Clauses

This section shows the basic SQL statements and clauses that retrieve, update,
delete, and insert data into tables and views. The SQL statements used are
SELECT, UPDATE, DELETE, and INSERT. FETCH statements can be used in an
application program to access data as well. This statement is covered in Chapter 4.
Examples using these SQL statements are supplied to help you develop SQL
applications. Detailed syntax and parameter descriptions for SQL statements are
given in the DB2 UDB for AS/400 SQL Reference book.

You can write SQL statements on one line or on many lines. The rules for the
continuation of lines are the same as those of the host language (the language the
program is written in).
Notes:
1. The SQL statements described in this section can be run on SQL tables and
views, and database physical and logical files. The tables, views, and files can
be either in an SQL collection or in a library.
2. Character strings specified in an SQL statement (such as those used with
WHERE or VALUES clauses) are case sensitive; that is, uppercase characters
must be entered in uppercase and lowercase characters must be entered in
lowercase.
WHERE ADMRDEPT='a00' (does not return a result)

WHERE ADMRDEPT='A00' (returns a valid department number)

Comparisons may not be case sensitive if a shared-weight sort sequence is

being used where uppercase and lowercase characters are treated as the same
character.

The INSERT Statement

You can use the INSERT statement to add new rows to a table or view in one of
the following ways:
v Specifying values in the INSERT statement for columns of the single row to be
added.
v Specifying the blocked form of the INSERT statement to add multiple rows.
“Using the Blocked Insert Statement” on page 70 explains how to use the
blocked form of the INSERT statement to add multiple rows to a table.
v Including a select-statement in the INSERT statement to tell SQL what data for
the new row is contained in another table or view. “Inserting Rows into a Table
Using a Select-Statement” on page 69 explains how to use the select-statement
within an INSERT statement to add zero, one, or many rows to a table.

Note: Because views are built on tables and actually contain no data, working with
views can be confusing. See “Creating and Using Views” on page 95 for
more information on inserting data by using a view.

© Copyright IBM Corp. 1997, 1999 31

 For every row you insert, you must supply a value for each column defined with the
NOT NULL attribute if that column does not have a default value. The INSERT
statement for adding a row to a table or view may look like this:
INSERT INTO table-name
(column1, column2, ... )
VALUES (value-for-column1, value-for-column2, ... )

The INTO clause names the columns for which you specify values. The VALUES
clause specifies a value for each column named in the INTO clause.

You must provide a value in the VALUES clause for each column named in an
INSERT statement’s column list. The column name list can be omitted if all columns
in the table have a value provided in the VALUES clause. If a column has a default
value, the keyword DEFAULT may be used as a value on the VALUES clause.

It is a good idea to name all columns into which you are inserting values because:
v Your INSERT statement is more descriptive.
v You can verify that you are giving the values in the proper order based on the
column names.
v You have better data independence. The order in which the columns are defined
in the table does not affect your INSERT statement.

If the column is defined to allow null values or to have a default, you do not need to
name it in the column name list or specify a value for it. The default value is used.
If the column is defined to have a default value, the default value is placed in the
column. If DEFAULT was specified for the column definition without an explicit
default value, SQL places the default value for that data type in the column. If the
column does not have a default value defined for it, but is defined to allow the null
value (NOT NULL was not specified in the column definition), SQL places the null
value in the column.
v For numeric columns, the default value is 0.
v For fixed length character or graphic columns, the default is blanks.
| v For varying length character or graphic columns or LOB columns, the default is a
| zero length string.
v For date, time, and timestamp columns, the default value is the current date,
time, or timestamp. When inserting a block of records, the default date/time value
is extracted from the system when the block is written. This means that the
column will be assigned the same default value for each row in the block.
| v For DataLink columns, the default value corresponds to DLVALUE(’’,’URL’,’’).
| v For distinct-type columns, the default value is the default value of the
| corresponding source type.

When your program attempts to insert a row that duplicates another row already in
the table, an error might occur. Multiple null values may or may not be considered
duplicate values, depending on the option used when the index was created.
v If the table has a primary key, unique key, or unique index, the row is not
inserted. Instead, SQL returns an SQLCODE of −803.
v If the table does not have a primary key, unique key, or unique index, the row
can be inserted without error.

If SQL finds an error while running the INSERT statement, it stops inserting data. If
you specify COMMIT(*ALL), COMMIT(*CS), COMMIT(*CHG), or COMMIT(*RR), no
rows are inserted. Rows already inserted by this statement, in the case of INSERT

32 DB2 UDB for AS/400 SQL Programming V4R4

 with a select-statement or blocked insert, are deleted. If you specify
COMMIT(*NONE), any rows already inserted are not deleted.

A table created by SQL is created with the Reuse Deleted Records parameter of
*YES. This allows the database manager to reuse any rows in the table that were
marked as deleted. The CHGPF command can be used to change the attribute to
*NO. This causes INSERT to always add rows to the end of the table.

The order in which rows are inserted does not guarantee the order in which they
will be retrieved.

If the row is inserted without error, the SQLERRD(3) field of the SQLCA has a value
of 1.

Note: For blocked INSERT or for INSERT with select-statement, more than one
row can be inserted. The number of rows inserted is reflected in
SQLERRD(3).

The UPDATE Statement

To change the data in a table, use the UPDATE statement. With the UPDATE
statement, you can change the value of one or more columns in each row that
satisfies the search condition of the WHERE clause. The result of the UPDATE
statement is one or more changed column values in zero or more rows of a table
(depending on how many rows satisfy the search condition specified in the WHERE
clause). The UPDATE statement looks like this:
UPDATE table-name
SET column-1 = value-1,
column-2 = value-2, ...
WHERE search-condition ...

For example, suppose an employee was relocated. To update several items of the
employee’s data in the CORPDATA.EMPLOYEE table to reflect the move, you can
specify:
UPDATE CORPDATA.EMPLOYEE
SET JOB = :PGM-CODE,
PHONENO = :PGM-PHONE
WHERE EMPNO = :PGM-SERIAL

Use the SET clause to specify a new value for each column you want to update.
The SET clause names the columns you want updated and provides the values you
want them changed to. The value you specify can be:
A column name. Replace the column’s current value with the contents of
another column in the same row.
A constant. Replace the column’s current value with the value provided in the
SET clause.
A null value. Replace the column’s current value with the null value, using the
keyword NULL. The column must be defined as capable of containing a null
value when the table was created, or an error occurs.
A host variable. Replace the column’s current value with the contents of a host
variable.
A special register. Replace the column’s current value with a special register
value; for example, USER.
An expression. Replace the column’s current value with the value that results
from an expression. The expression can contain any of the values in this list.

Chapter 3. Basic Concepts and Techniques 33

 A scalar subselect. Replace the column’s current value with the value that the
subquery returns.
The DEFAULT keyword. Replace the column’s current value with the default
value of the column. The column must have a default value defined for it or
allow the NULL value, or an error occurs.

The following is an example of a statement that uses many different values:

UPDATE WORKTABLE
SET COL1 = 'ASC',
COL2 = NULL,
COL3 = :FIELD3,
COL4 = CURRENT TIME,
COL5 = AMT - 6.00,
COL6 = COL7
WHERE EMPNO = :PGM-SERIAL

To identify the rows to be updated, use the WHERE clause:

v To update a single row, use a WHERE clause that selects only one row.
v To update several rows, use a WHERE clause that selects only the rows you
want to update.

You can omit the WHERE clause. If you do, SQL updates each row in the table or
view with the values you supply.

If the database manager finds an error while running your UPDATE statement, it
stops updating and returns a negative SQLCODE. If you specify COMMIT(*ALL),
COMMIT(*CS), COMMIT(*CHG), or COMMIT(*RR), no rows in the table are
changed (rows already changed by this statement, if any, are restored to their
previous values). If COMMIT(*NONE) is specified, any rows already changed are
not restored to previous values.

If the database manager cannot find any rows that satisfy the search condition, an
SQLCODE of +100 is returned.

Note: UPDATE with a WHERE clause may have updated more than one row. The
number of rows updated is reflected in SQLERRD(3).

The DELETE Statement

To remove rows from a table, use the DELETE statement. When you DELETE a
row, you remove the entire row. DELETE does not remove specific columns from
the row. The result of the DELETE statement is the removal of zero or more rows of
a table (depending on how many rows satisfy the search condition specified in the
WHERE clause). If you omit the WHERE clause from a DELETE statement, SQL
removes all the rows of the table. The DELETE statement looks like this:
DELETE FROM table-name
WHERE search-condition ...

For example, suppose department D11 was moved to another place. You want to
delete each row in the CORPDATA.EMPLOYEE table with a WORKDEPT value of
D11 as follows:
DELETE FROM CORPDATA.EMPLOYEE
WHERE WORKDEPT = 'D11'

The WHERE clause tells SQL which rows you want to delete from the table. SQL
deletes all the rows that satisfy the search condition from the base table. You can

34 DB2 UDB for AS/400 SQL Programming V4R4

 omit the WHERE clause, but it is best to include one, because a DELETE
statement without a WHERE clause deletes all the rows from the table or view. To
delete a table definition as well as the table contents, issue the DROP statement
(described in the DB2 UDB for AS/400 SQL Reference book).

If SQL finds an error while running your DELETE statement, it stops deleting data
and returns a negative SQLCODE. If you specify COMMIT(*ALL), COMMIT(*CS),
COMMIT(*CHG), or COMMIT(*RR), no rows in the table are deleted (rows already
deleted by this statement, if any, are restored to their previous values). If
COMMIT(*NONE) is specified, any rows already deleted are not restored to their
previous values.

If SQL cannot find any rows that satisfy the search condition, an SQLCODE of +100
is returned.

Note: DELETE with WHERE clause may have deleted more than one row. The
number of rows deleted is reflected in SQLERRD(3).

The SELECT INTO Statement

| You can use a variety of statements and clauses to query your data. One way to do
| this is to use the SELECT INTO statement in a program to retrieve a specific row
| (for example, the row for an employee). Furthermore, in this example, a variety of
| clauses are used to gather data in a specific way. You can use the “The WHERE
| Clause” on page 38, “The GROUP BY Clause” on page 40, “The HAVING Clause”
| on page 42, and “The ORDER BY Clause” on page 43 to tailor your query to
| gather data in a specific manner.

The format and syntax shown here are very basic. SELECT INTO statements can
be more varied than the examples presented in this chapter. A SELECT INTO
statement can include the following:
1. The name of each column you want
2. The name of each host variable used to contain retrieved data
3. The name of the table or view that contains the data
4. A search condition to uniquely identify the row that contains the information you
want
5. The name of each column used to group your data
6. A search condition that uniquely identifies a group that contains the information
you want
7. The order of the results so a specific row among duplicates can be returned.

A SELECT INTO statement looks like this:

SELECT column names
INTO host variables
FROM table or view name
WHERE search condition
GROUP BY column names
HAVING search condition
ORDER BY column-name

The SELECT, INTO, and FROM clauses must be specified. The other clauses are
optional.

Chapter 3. Basic Concepts and Techniques 35

 The INTO clause names the host variables (variables in your program used to
contain retrieved column values). The value of the first column specified in the
SELECT clause is put into the first host variable named in the INTO clause; the
second value is put into the second host variable, and so on.

The result table for a SELECT INTO should contain just one row. For example,
each row in the CORPDATA.EMPLOYEE table has a unique EMPNO (employee
number) column. The result of a SELECT INTO statement for this table if the
WHERE clause contains an equal comparison on the EMPNO column, would be
exactly one row (or no rows). Finding more than one row is an error, but one row is
still returned. You can control which row will be returned in this error condition by
specifying the ORDER BY clause. If you use the ORDER BY clause, the first row in
the result table is returned.

If you want more than one row to be the result of a select-statement, use a
DECLARE CURSOR statement to select the rows, followed by a FETCH statement
to move the column values into host variables one or many rows at a time. Using
cursors is described in “Chapter 4. Using a Cursor” on page 55.

The FROM clause names the table (or view) that contains the data you are
interested in.

For example, assume that each department listed in the

CORPDATA.DEPARTMENT table has a unique department number. You want to
retrieve the department name and manager number from the
CORPDATA.DEPARTMENT table for department C01. To do this, your program can
set PGM-DEPT to the value C01 and issue:
SELECT DEPTNAME, MGRNO
INTO :PGM-DEPTNAME, :PGM-MGRNO
FROM CORPDATA.DEPARTMENT
WHERE DEPTNO = :PGM-DEPT

When the statement is run, the result is one row:

PGM-DEPTNAME PGM-MGRNO
INFORMATION CENTER 000030

These values are assigned to the host variables PGM-DEPTNAME and

PGM-MGRNO.

If SQL is unable to find a row that satisfies the search condition, an SQLCODE of
+100 is returned.

If SQL finds errors while running your select-statement, a negative SQLCODE is

returned. If SQL finds more host variables than results, +326 is returned.

You can retrieve data from a view in exactly the same way you retrieve data from a
table. However, there are several restrictions when you attempt to update, insert, or
delete data in a view. These restrictions are described in “Creating and Using
Views” on page 95.

Data retrieval errors

If SQL finds that a retrieved character or graphic column is too long to be placed in
a host variable, SQL does the following:

36 DB2 UDB for AS/400 SQL Programming V4R4

v Truncates the data while assigning the value to the host variable.
v Sets SQLWARN0 and SQLWARN1 in the SQLCA to the value 'W'.
v Sets the indicator variable, if provided, to the length of the value before
truncation.

If SQL finds a data mapping error while running a statement, one of two things
occurs:
v If the error occurs on an expression in the SELECT list and an indicator variable
is provided for the expression in error:
– SQL returns a −2 for the indicator variable corresponding to the expression in
error.
– SQL returns all valid data for that row.
– SQL returns a positive SQLCODE.
v If an indicator variable is not provided, SQL returns the corresponding negative
SQLCODE in the SQLCA.

Data mapping errors include:

v +138 - Argument of the substringing function is not valid.
v +180 - Syntax for a string representation of a date, time, or timestamp is not
valid.
v +181 - String representation of a date, time, or timestamp is not a valid value.
v +183 - Invalid result from a date/time expression. The resulting date or timestamp
is not within the valid range of dates or timestamps.
v +191 - MIXED data is not properly formed.
v +304 - Numeric conversion error (for example, overflow, underflow, or division by
zero).
v +331 - Characters cannot be converted.
v +420 - Character in the CAST argument is not valid.
v +802 - Data conversion or data mapping error.

For data mapping errors, the SQLCA reports only the last error detected. The
indicator variable corresponding to each result column having an error is set to −2.

If the full-select contains DISTINCT in the select list and a column in the select list
contains numeric data that is not valid, the data is considered equal to a null value
if the query is completed as a sort. If an existing index is used, the data is not
considered equal to a null.

The impact of data mapping errors on the ORDER BY clause depends on the
situation:
v If the data mapping error occurs while data is being assigned to a host variable
in a SELECT INTO or FETCH statement, and that same expression is used in
the ORDER BY clause, the result record is ordered based on the value of the
expression. It is not ordered as if it were a null (higher than all other values). This
is because the expression was evaluated before the assignment to the host
variable is attempted.
v If the data mapping error occurs while an expression in the select-list is being
evaluated and the same expression is used in the ORDER BY clause, the result
column is normally ordered as if it were a null value (higher than all other
values). If the ORDER BY clause is implemented by using a sort, the result
column is ordered as if it were a null value. If the ORDER BY clause is

Chapter 3. Basic Concepts and Techniques 37

 implemented by using an existing index, in the following cases, the result column
is ordered based on the actual value of the expression in the index:
– The expression is a date column with a date format of *MDY, *DMY, *YMD, or
*JUL, and a date conversion error occurs because the date is not within the
valid range for dates.
– The expression is a character column and a character could not be converted.
– The expression is a decimal column and a numeric value that is not valid is
detected.

The SELECT Clause

With the SELECT clause (the first part of a select-statement), you specify the name
of each column you want to retrieve. For example:
SELECT EMPNO, LASTNAME, WORKDEPT
.
.
.

You can specify that only one column be retrieved, or as many as 8000 columns.
The value of each column you name is retrieved in the order specified in the
SELECT clause.

If you want to retrieve all columns (in the same order as they appear in the row),
use an asterisk (*) instead of naming the columns:
SELECT *
.
.
.

When using the select-statement in an application program, list the column names
to give your program more data independence. There are two reasons for this:
1. When you look at the source code statement, you can easily see the one-to-one
correspondence between the column names in the SELECT clause and the host
variables named in the INTO clause.
2. If a column is added to a table or view you access and you use “SELECT * ...,”
and you create the program again from source, the INTO clause does not have
a matching host variable named for the new column. The extra column causes
you to get a warning (not an error) in the SQLCA (SQLWARN4 will contain a
“W”).

The WHERE Clause

The WHERE clause specifies a search condition that identifies the row or rows you
want to retrieve, update, or delete. The number of rows you process with an SQL
statement then depends on the number of rows that satisfy the WHERE clause
search condition. A search condition consists of one or more predicates. A
predicate specifies a test that you want SQL to apply to a specified row or rows of a
table.

In the following example, WORKDEPT = 'C01' is a predicate, WORKDEPT and

'C01' are expressions, and the equal sign (=) is a comparison operator. Note that
character values are enclosed in apostrophes ('); numeric values are not. This
applies to all constant values wherever they are coded within an SQL statement.
For example, to specify that you are interested in the rows where the department
number is C01, you would say:
... WHERE WORKDEPT = 'C01'

38 DB2 UDB for AS/400 SQL Programming V4R4

In this case, the search condition consists of one predicate: WORKDEPT = 'C01'.

If the search condition contains character or UCS-2 graphic column predicates, the
sort sequence that is in effect when the query is run is applied to those predicates.
See “Using Sort Sequence in SQL” on page 50 for more information on sort
sequence and selection.

Using Expressions in the WHERE Clause

An expression in a WHERE clause names or specifies something you want to
compare to something else. Each expression, when evaluated by SQL, is a
character string, date/time/timestamp, or a numeric value. The expressions you
specify can be:
v A column name names a column. For example:
... WHERE EMPNO = '000200'

EMPNO names a column that is defined as a 6-byte character value. Equality

comparisons (that is, X = Y or X <> Y) can be performed on character data.
Other types of comparisons can also be evaluated for character data.

However, you cannot compare character strings to numbers. You also cannot
perform arithmetic operations on character data (even though EMPNO is a
character string that appears to be a number). You can add and subtract
date/time values.
v An expression identifies two values that are added (+), subtracted (−), multiplied
(*), divided (/), have exponentiation (**), or concatenated (CONCAT or ||) to result
in a value. The operands of an expression can be:
A constant (that is, a literal value)
A column
A host variable
A value returned from a function
A special register
Another expression

For example:
... WHERE INTEGER(PRENDATE - PRSTDATE) > 100

When the order of evaluation is not specified by parentheses, the expression is

evaluated in the following order:
1. Prefix operators
2. Exponentiation
3. Multiplication, division, and concatenation
4. Addition and subtraction

Operators on the same precedence level are applied from left to right.
v A constant specifies a literal value for the expression. For example:
... WHERE 40000 < SALARY

SALARY names a column that is defined as an 9-digit packed decimal value

(DECIMAL(9,2)). It is compared to the numeric constant 40000.
v A host variable identifies a variable in an application program. For example:
... WHERE EMPNO = :EMP

Chapter 3. Basic Concepts and Techniques 39

 v A special register identifies a special value generated by the database manager.
For example:
... WHERE LASTNAME = USER
v The NULL value specifies the condition of having an unknown value.
... WHERE DUE_DATE IS NULL

A search condition need not be limited to two column names or constants separated
by arithmetic or comparison operators. You can develop a complex search condition
that specifies several predicates separated by AND and OR. No matter how
complex the search condition, it supplies a TRUE or FALSE value when evaluated
against a row. There is also an unknown truth value, which is effectively false. That
is, if the value of a row is null, this null value is not returned as a result of a search
because it is not less than, equal to, or greater than the value specified in the
search condition. More complex search conditions and predicates are described in
“Performing Complex Search Conditions” on page 72.

To fully understand the WHERE clause, you need to know how SQL evaluates
search conditions and predicates, and compares the values of expressions. This
topic is discussed in the DB2 UDB for AS/400 SQL Reference book.

Comparison Operators
SQL supports the following comparison operators:

= Equal to
<> or ¬= Not equal to
< Less than
> Greater than
<= or ¬> Less than or equal to (or not greater than)
> = or ¬< Greater than or equal to (or not less than)

The NOT Keyword

You can precede a predicate with the NOT keyword to specify that you want the
opposite of the predicate’s value (that is, TRUE if the predicate is FALSE, or vice
versa). NOT applies only to the predicate it precedes, not to all predicates in the
WHERE clause. For example, to indicate that you are interested in all employees
except those working in department C01, you could say:
... WHERE NOT WORKDEPT = 'C01'

which is equivalent to:

... WHERE WORKDEPT <> 'C01'

The GROUP BY Clause

Without a GROUP BY clause, the application of SQL column functions returns one
row. When GROUP BY is used, the function is applied to each group, thereby
returning as many rows as there are groups.

| The GROUP BY clause allows you to find the characteristics of groups of rows
| rather than individual rows. When you specify a GROUP BY clause, SQL divides
| the selected rows into groups such that the rows of each group have matching
| values in one or more columns or expressions. Next, SQL processes each group to
| produce a single-row result for the group. You can specify one or more columns or

40 DB2 UDB for AS/400 SQL Programming V4R4

| expressions in the GROUP BY clause to group the rows. The items you specify in
| the SELECT statement are properties of each group of rows, not properties of
| individual rows in a table or view.

For example, the CORPDATA.EMPLOYEE table has several sets of rows, and each
set consists of rows describing members of a specific department. To find the
average salary of people in each department, you could issue:

The SQL statement: Results in:

fetch WORK-DEPT AVG-SALARY
1 A00 42833
2 B01 41250
... ... ...

RV2W551-1

The result is several rows, one for each department.

Notes:
1. Grouping the rows does not mean ordering them. Grouping puts each selected
row in a group, which SQL then processes to derive characteristics of the group.
Ordering the rows puts all the rows in the results table in ascending or
descending collating sequence. ( “The ORDER BY Clause” on page 43
describes how to do this.)
2. If there are null values in the column you specify in the GROUP BY clause, a
single-row result is produced for the data in the rows with null values.
3. If the grouping occurs over character or UCS-2 graphic columns, the sort
sequence in effect when the query is run is applied to the grouping. See “Using
Sort Sequence in SQL” on page 50 for more information on sort sequence and
selection.

| When you use GROUP BY, you list the columns or expressions you want SQL to
| use to group the rows. For example, suppose you want a list of the number of
| people working on each major project described in the CORPDATA.PROJECT
| table. You could issue:

The SQL statement: Results in:

fetch SUM-PR MAJ-PROJ
1 6 AD3100
2 5 AD3110
3 10 MA2100
... ... ...

RV2W552-3

Chapter 3. Basic Concepts and Techniques 41

 The result is a list of the company’s current major projects and the number of
people working on each project.

| You can also specify that you want the rows grouped by more than one column or
| expression. For example, you could issue a select-statement to find the average
| salary for men and women in each department, using the CORPDATA.EMPLOYEE
| table. To do this, you could issue:

The SQL statement: Results in:

fetch DEPT SEX AVG-WAGES
1 A00 F 52750
2 A00 M 37875
3 B01 M 41250
4 C01 F 30156
... ... ... ...

RV2W553-1

Because you did not include a WHERE clause in this example, SQL examines and
process all rows in the CORPDATA.EMPLOYEE table. The rows are grouped first
by department number and next (within each department) by sex before SQL
derives the average SALARY value for each group.

The HAVING Clause

You can use the HAVING clause to specify a search condition for the groups
selected based on a GROUP BY clause. The HAVING clause says that you want
only those groups that satisfy the condition in that clause. Therefore, the search
condition you specify in the HAVING clause must test properties of each group
rather than properties of individual rows in the group.

The HAVING clause follows the GROUP BY clause and can contain the same kind
of search condition you can specify in a WHERE clause. In addition, you can
specify column functions in a HAVING clause. For example, suppose you wanted to
retrieve the average salary of women in each department. To do this, you would
use the AVG column function and group the resulting rows by WORKDEPT and
specify a WHERE clause of SEX = 'F'.

To specify that you want this data only when all the female employees in the
selected department have an education level equal to or greater than 16 (a college
graduate), use the HAVING clause. The HAVING clause tests a property of the
group. In this case, the test is on MIN(EDLEVEL), which is a group property:

42 DB2 UDB for AS/400 SQL Programming V4R4

 The SQL statement: Results in:
fetch DEPT AVG-WAGES MIN-EDUC
1 A00 52750 18
2 C01 30156 16
3 D11 24476 17

RV2W554-3

You can use multiple predicates in a HAVING clause by connecting them with AND
and OR, and you can use NOT for any predicate of a search condition.

Note: If you intend to update a column or delete a row, you cannot include a
GROUP BY or HAVING clause in the SELECT statement within a DECLARE
CURSOR statement. (The DECLARE CURSOR statement is described in
“Chapter 4. Using a Cursor” on page 55.)

Predicates with arguments that are not column functions can be coded in either
WHERE or HAVING clauses. It is usually more efficient to code the selection criteria
in the WHERE clause. It is processed during the initial phase of the query
processing. The HAVING selection is performed in post processing of the result
table.

If the search condition contains predicates involving character or UCS-2 graphic

columns, the sort sequence in effect when the query is run is applied to those
predicates. See “Using Sort Sequence in SQL” on page 50 for more information on
sort sequence and selection.

The ORDER BY Clause

| You can specify that you want selected rows retrieved in a particular order, sorted
| by ascending or descending collating sequence of a column’s value, with the
| ORDER BY clause. You can use an ORDER BY clause as you would a GROUP BY
| clause: specify the columns or expressions you want SQL to use when retrieving
| the rows in a collated sequence.

For example, to retrieve the names and department numbers of female employees
listed in the alphanumeric order of their department numbers, you could use this
select-statement:

Chapter 3. Basic Concepts and Techniques 43

 The SQL statement: Results in:
fetch PGM-NAME3 DEPT
1 HAAS A00
2 KWAN C01
3 QUINTANA C01
4 NICHOLLS C01
5 PIANKA D11
6 SCOUTTEN D11
7 LUTZ D11
8 PULASKI D21
9 JOHNSON D21
10 PEREZ D21
11 HENDERSON E11
12 SCHNEIDER E11
13 SETRIGHT E11

RV2W555-3

Notes:
1. All columns named in the ORDER BY clause must also be named in the
SELECT list.
2. Null values are ordered as the highest value.

| To order by a column function, or something other than a column name, you can
| specify an AS clause in the select-list. To order by an expression, you can either
| specify the exact same expression in the ORDER BY clause, or you can specify an
| AS clause in the select-list.

The AS clause names the result column. This name can be specified in the ORDER
BY clause. To order by a name specified in the AS clause:
v The name must be unique in the select-list.
v The name must not be qualified.
For example, to retrieve the full name of employees listed in alphabetic order, you
could use this select-statement:
SELECT LASTNAME CONCAT FIRSTNAME AS FULLNAME ...
ORDER BY FULLNAME

| This select-statement could optionally be written as:

| SELECT LASTNAME CONCAT FIRSTNAME
| ORDER BY LASTNAME CONCAT FIRSTNAME

| Instead of naming the columns to order the results, you can use a number. For
example, ORDER BY 3 specifies that you want the results ordered by the third
column of the results table, as specified by the select-statement. Use a number to
order the rows of the results table when the sequencing value is not a named
column.

You can also specify whether you want SQL to collate the rows in ascending (ASC)
or descending (DESC) sequence. An ascending collating sequence is the default. In
the above select-statement, SQL first returns the row with the lowest department

44 DB2 UDB for AS/400 SQL Programming V4R4

 number (alphabetically and numerically), followed by rows with higher department
numbers. To order the rows in descending collating sequence based on the
department number, specify:
... ORDER BY WORKDEPT DESC

As with GROUP BY, you can specify a secondary ordering sequence (or several
levels of ordering sequences) as well as a primary one. In the example above, you
might want the rows ordered first by department number, and within each
department, ordered by employee name. To do this, specify:
... ORDER BY WORKDEPT, LASTNAME

If character columns or UCS-2 graphic columns are used in the ORDER BY clause,
ordering for these columns is based on the sort sequence in effect when the query
is run. See “Using Sort Sequence in SQL” on page 50 for more information on sort
sequence and its affect on ordering.

Using Null Values

A null value indicates the absence of a column value in a row. A null value is not
the same as zero or all blanks. A null value is the same as “unknown”. Null values
can be used as a condition in the WHERE and HAVING clauses, and as a
mathematical argument. For example, a WHERE clause can specify a column that,
for some rows, contains a null value. Normally, a comparison predicate using a
column that contains null values does not select a row that has a null value for the
column. This is because a null value is neither less than, equal to, nor greater than
the value specified in the condition. To select the values for all rows that contain a
null value for the manager number, you could specify:
SELECT DEPTNO, DEPTNAME, ADMRDEPT
FROM CORPDATA.DEPARTMENT
WHERE MGRNO IS NULL

The result would be:

DEPTNO DEPTNAME ADMRDEPT

D01 DEVELOPMENT A00
CENTER

To get the rows that do not have a null value for the manager number, you could
change the WHERE clause like this:
WHERE MGRNO IS NOT NULL

For more information on the use of null values, see the DB2 UDB for AS/400 SQL
Reference book.

Using Special Registers

You can specify certain “special registers” in SQL statements. For locally run SQL
statements, the special registers and their contents are shown in the following table:

Special Registers Contents

CURRENT DATE The current date.
CURRENT_DATE

Chapter 3. Basic Concepts and Techniques 45

 Special Registers Contents
CURRENT TIME The current time.
CURRENT_TIME
CURRENT TIMESTAMP The current date and time in timestamp
CURRENT_TIMESTAMP format.
CURRENT TIMEZONE A duration of time that links local time to
CURRENT_TIMEZONE Universal Coordinated Time (UTC) using the
formula:
local time - CURRENT TIMEZONE = UTC

It is taken from the system value

QUTCOFFSET.
CURRENT SERVER The name of the relational database as
CURRENT_SERVER contained in the relational database directory
table in the relational database directory.
USER The run-time authorization identifier (user
profile) of the job.
CURRENT PATH The SQL path used to resolve unqualified
CURRENT_PATH data type names, procedure names, and
CURRENT FUNCTION PATH function names.

If a single statement contains more than one reference to any of CURRENT DATE,
CURRENT TIME, or CURRENT TIMESTAMP special registers, or the CURDATE,
CURTIME, or NOW scalar functions, all values are based on a single clock reading.

For remotely run SQL statements, the special registers and their contents are
shown in the following table:

Special Registers Contents

CURRENT DATE The current date and time at the remote
CURRENT_DATE system, not the local system.
CURRENT TIME
CURRENT_TIME
CURRENT TIMESTAMP
CURRENT_TIMESTAMP
CURRENT TIMEZONE A duration of time that links the remote
CURRENT_TIMEZONE system time to UTC.
CURRENT SERVER The name of the relational database as
CURRENT_SERVER contained in the relational database directory
table in the relational database directory.
USER The run-time authorization identifier of the
server job on the remote system.
CURRENT PATH The current path value at the remote system.
CURRENT_PATH
CURRENT FUNCTION PATH

When a query over a distributed table references a special register, the contents of
the special register on the system that requests the query are used. For more
information on distributed tables, see DB2 Multisystem for AS/400 book.

46 DB2 UDB for AS/400 SQL Programming V4R4

Using Date, Time, and Timestamp
Date, time, and timestamp are data types represented in an internal form not seen
by the SQL user. Date, time, and timestamp can be represented by character string
values and assigned to character string variables. The database manager
recognizes the following as date, time, and timestamp values:
v A value returned by the DATE, TIME, or TIMESTAMP scalar functions.
v A value returned by the CURRENT DATE, CURRENT TIME, or CURRENT
TIMESTAMP special registers.
v A character string when it is an operand of an arithmetic expression or a
comparison and the other operand is a date, time, or timestamp. For example, in
the predicate:
... WHERE HIREDATE < '1950-01-01'

if HIREDATE is a date column, the character string ’1950-01-01’ is interpreted as

a date.
v A character string variable or constant used to set a date, time, or timestamp
column in either the SET clause of an UPDATE statement, or the VALUES
clause of an INSERT statement.
For more information on character string formats of date, time, and timestamp
values, see Chapter 2 of the DB2 UDB for AS/400 SQL Reference book .

Specifying Current Date and Time Values

You can specify a current date, time, or timestamp in an expression by specifying
one of three special registers: CURRENT DATE, CURRENT TIME, or CURRENT
TIMESTAMP. The value of each is based on a time-of-day clock reading obtained
during the running of the statement. Multiple references to CURRENT DATE,
CURRENT TIME, or CURRENT TIMESTAMP within the same SQL statement use
the same value. The following statement returns the age (in years) of each
employee in the EMPLOYEE table when the statement is run:
SELECT YEAR(CURRENT DATE - BIRTHDATE)
FROM CORPDATA.EMPLOYEE

The CURRENT TIMEZONE special register allows a local time to be converted to

Universal Coordinated Time (UTC). For example, if you have a table named
DATETIME, containing a time column type with a name of STARTT, and you want
to convert STARTT to UTC, you can use the following statement:
SELECT STARTT - CURRENT TIMEZONE
FROM DATETIME

Date/Time Arithmetic
Addition and subtraction are the only arithmetic operators applicable to date, time,
and timestamp values. You can increment and decrement a date, time, or
timestamp by a duration; or subtract a date from a date, a time from a time, or a
timestamp from a timestamp. For a detailed description of date and time arithmetic,
see Chapter 2 of the DB2 UDB for AS/400 SQL Reference book.

Chapter 3. Basic Concepts and Techniques 47

 Creating and using ALIAS names
| When you refer to an existing table or view to an AS/400 physical file that consists
| of multiple members, you can avoid using file overrides by creating an alias. You
| can use the SQL CREATE ALIAS statement to do this. Or you can use Operations
| Navigator.

You can create an alias for

v A table or view
| v A member of a table

| A table alias defines a name for the file, including the specific member name. You
| can use this alias name in an SQL statement in the same way that you would use a
| table name. Unlike overrides, alias names are objects that exist until they are
| dropped.

For example, if there is a multiple member file MYLIB.MYFILE with members MBR1
and MBR2, an alias can be created for the second member so that SQL can easily
refer to it.
CREATE ALIAS MYLIB.MYMBR2_ALIAS FOR MYLIB.MYFILE (MBR2)

When alias MYLIB.MYMBR2_ALIAS is specified on the following insert statement,

the values are inserted into member MBR2 in MYLIB.MYFILE.
INSERT INTO MYLIB.MYMBR2_ALIAS VALUES('ABC', 6)

Alias names can also be specified on DDL statements. Assume that alias
MYLIB.MYALIAS exists and is an alias for table MYLIB.MYTABLE. The following
DROP statement will drop table MYLIB.MYTABLE.
DROP TABLE MYLIB.MYALIAS

If you really want to drop the alias name instead, specify the ALIAS keyword on the
drop statement:
DROP ALIAS MYLIB.MYALIAS

Using LABEL ON
Sometimes the table name, column name, view name, alias name, or SQL package
name does not clearly define data that is shown on an interactive display of the
table. By using the LABEL ON statement, you can create a more descriptive label
for the table name, column name, view name, alias name, or SQL package name.
These labels can be seen in the SQL catalog in the LABEL column.

The LABEL ON statement looks like this:

LABEL ON
TABLE CORPDATA.DEPARTMENT IS 'Department Structure Table'

LABEL ON
COLUMN CORPDATA.DEPARTMENT.ADMRDEPT IS 'Reports to Dept.'

After these statements are run, the table named DEPARTMENT displays the text
description as Department Structure Table and the column named ADMRDEPT
displays the heading Reports to Dept. The label for tables, views, SQL packages,

48 DB2 UDB for AS/400 SQL Programming V4R4

 and column text cannot be more than 50 characters and the label for column
headings cannot be more than 60 characters (blanks included). The following are
examples of LABEL ON statements:

This LABEL ON statement provides column heading 1 and column heading 2.

*...+....1....+....2....+....3....+....4....+....5....+....6..*
LABEL ON COLUMN CORPDATA.EMPLOYEE.EMPNO IS
'Employee Number'

This LABEL ON statement provides 3 levels of column headings for the SALARY
column.
*...+....1....+....2....+....3....+....4....+....5....+....6..*
LABEL ON COLUMN CORPDATA.EMPLOYEE.SALARY IS
'Yearly Salary (in dollars)'

This LABEL ON statement removes the column heading for SALARY.

*...+....1....+....2....+....3....+....4....+....5....+....6..*
LABEL ON COLUMN CORPDATA.EMPLOYEE.SALARY IS ''

An example of a DBCS column heading with two levels specified.

*...+....1....+....2....+....3....+....4....+....5....+....6..*
LABEL ON COLUMN CORPDATA.EMPLOYEE.SALARY IS
'<AABBCCDD> <EEFFGG>'

This LABEL ON statement provides column text for the EDLEVEL column.
*...+....1....+....2....+....3....+....4....+....5....+....6..*
LABEL ON COLUMN CORPDATA.EMPLOYEE.EDLEVEL TEXT IS
'Number of years of formal education'

For more information about the LABEL ON statement, see the DB2 UDB for AS/400
SQL Reference book.

Using COMMENT ON
| After you create an SQL object such as a table, view, index, package, procedure,
| parameter, user-defined type, or function, you can supply information about it for
| future referral, such as the purpose of the object, who uses it, and anything unusual
| or special about it. You can also include similar information about each column of a
| table or view. Your comment must not be more than 2000 bytes.

A comment is especially useful if your names do not clearly indicate the contents of
the columns or objects. In that case, use a comment to describe the specific
contents of the column or objects.

An example of using COMMENT ON follows:

COMMENT ON TABLE CORPDATA.EMPLOYEE IS
'Employee table. Each row in this table represents
one employee of the company.'

Chapter 3. Basic Concepts and Techniques 49

 Getting Comments
| After running a COMMENT ON statement for a table, your comments are stored in
| the REMARKS column of SYSTABLES. Comments for the other objects are stored
| in the REMARKS column of the appropriate catalog table. (If the indicated row had
| already contained a comment, the old comment is replaced by the new one.) The
| following example gets the comments added by the COMMENT ON statement in
| the previous example:
| SELECT REMARKS
| FROM CORPDATA.SYSTABLES
| WHERE NAME = 'EMPLOYEE'
|
Using Sort Sequence in SQL
A sort sequence defines how characters in a character set relate to each other
when they are compared or ordered. For more information on sort sequences, see
Chapter 1 in DB2 UDB for AS/400 SQL Reference book.

The sort sequence is used for all character and UCS-2 graphic comparisons
performed in SQL statements. There are sort sequence tables for both single byte
and double byte character data. Each single byte sort sequence table has an
associated double byte sort sequence table, and vice versa. Conversion between
the two tables is performed when necessary to implement a query. In addition, the
CREATE INDEX statement has the sort sequence (in effect at the time the
statement was run) applied to the character columns referred to in the index.

Sort Sequence Used with ORDER BY and Record Selection

To see how to use a sort sequence, run the examples in this section against the
STAFF table shown in Table 2. Notice that the values in the JOB column are in
mixed case. You can see the values 'Mgr', 'MGR', and 'mgr'.
Table 2. The STAFF Table
ID NAME DEPT JOB YEARS SALARY COMM
10 Sanders 20 Mgr 7 18357.50 0
20 Pernal 20 Sales 8 18171.25 612.45
30 Merenghi 38 MGR 5 17506.75 0
40 OBrien 38 Sales 6 18006.00 846.55
50 Hanes 15 Mgr 10 20659.80 0
60 Quigley 38 SALES 0 16808.30 650.25
70 Rothman 15 Sales 7 16502.83 1152.00
80 James 20 Clerk 0 13504.60 128.20
90 Koonitz 42 sales 6 18001.75 1386.70
100 Plotz 42 mgr 6 18352.80 0

In the following examples, the results are shown for each statement using:
v *HEX sort sequence
v Shared-weight sort sequence using the language identifier ENU
v Unique-weight sort sequence using the language identifier ENU

50 DB2 UDB for AS/400 SQL Programming V4R4

 Note: ENU is chosen as a language identifier by specifying either
SRTSEQ(*LANGIDUNQ), or SRTSEQ(*LANGIDSHR) and LANGID(ENU), on
the CRTSQLxxx, STRSQL, or RUNSQLSTM commands, or by using the
SET OPTION statement.

ORDER BY
The following SQL statement causes the result table to be sorted using the values
in the JOB column:
SELECT * FROM STAFF ORDER BY JOB

Table 3 shows the result table using a *HEX sort sequence. The rows are sorted
based on the EBCDIC value in the JOB column. In this case, all lowercase letters
sort before the uppercase letters.
Table 3. ″SELECT * FROM STAFF ORDER BY JOB″ Using the *HEX Sort Sequence.
ID NAME DEPT JOB YEARS SALARY COMM
100 Plotz 42 mgr 6 18352.80 0
90 Koonitz 42 sales 6 18001.75 1386.70
80 James 20 Clerk 0 13504.60 128.20
10 Sanders 20 Mgr 7 18357.50 0
50 Hanes 15 Mgr 10 20659.80 0
30 Merenghi 38 MGR 5 17506.75 0
20 Pernal 20 Sales 8 18171.25 612.45
40 OBrien 38 Sales 6 18006.00 846.55
70 Rothman 15 Sales 7 16502.83 1152.00
60 Quigley 38 SALES 0 16808.30 650.25

Table 4 shows how sorting is done for a unique-weight sort sequence. After the sort
sequence is applied to the values in the JOB column, the rows are sorted. Notice
that after the sort, lowercase letters are before the same uppercase letters, and the
values 'mgr', 'Mgr', and 'MGR' are adjacent to each other.
Table 4. ″SELECT * FROM STAFF ORDER BY JOB″ Using the Unique-Weight Sort
Sequence for the ENU Language Identifier.
ID NAME DEPT JOB YEARS SALARY COMM
80 James 20 Clerk 0 13504.60 128.20
100 Plotz 42 mgr 6 18352.80 0
10 Sanders 20 Mgr 7 18357.50 0
50 Hanes 15 Mgr 10 20659.80 0
30 Merenghi 38 MGR 5 17506.75 0
90 Koonitz 42 sales 6 18001.75 1386.70
20 Pernal 20 Sales 8 18171.25 612.45
40 OBrien 38 Sales 6 18006.00 846.55
70 Rothman 15 Sales 7 16502.83 1152.00
60 Quigley 38 SALES 0 16808.30 650.25

Table 5 on page 52 shows how sorting is done for a shared-weight sort sequence.
After the sort sequence is applied to the values in the JOB column, the rows are
sorted. For the
Chapter 3. Basic Concepts and Techniques 51
 sort comparison, each lowercase letter is treated the same as the corresponding
uppercase letter. In Table 5, notice that all the values 'MGR', 'mgr' and 'Mgr' are
mixed together.
Table 5. ″SELECT * FROM STAFF ORDER BY JOB″ Using the Shared-Weight Sort
Sequence for the ENU Language Identifier.
ID NAME DEPT JOB YEARS SALARY COMM
80 James 20 Clerk 0 13504.60 128.20
10 Sanders 20 Mgr 7 18357.50 0
30 Merenghi 38 MGR 5 17506.75 0
50 Hanes 15 Mgr 10 20659.80 0
100 Plotz 42 mgr 6 18352.80 0
20 Pernal 20 Sales 8 18171.25 612.45
40 OBrien 38 Sales 6 18006.00 846.55
60 Quigley 38 SALES 0 16808.30 650.25
70 Rothman 15 Sales 7 16502.83 1152.00
90 Koonitz 42 sales 6 18001.75 1386.70

Record selection
The following SQL statement selects records with the value 'MGR' in the JOB
column:
SELECT * FROM STAFF WHERE JOB='MGR'

Table 6 shows how record selection is done with a *HEX sort sequence. In Table 6,
the rows that match the record selection criteria for the column 'JOB' are selected
exactly as specified in the select statement. Only the uppercase 'MGR' is selected.
Table 6. ″SELECT * FROM STAFF WHERE JOB=’MGR’ Using the *HEX Sort Sequence.″
ID NAME DEPT JOB YEARS SALARY COMM
30 Merenghi 38 MGR 5 17506.75 0

Table 7 shows how record selection is done with a unique-weight sort sequence. In
Table 7, the lowercase and uppercase letters are treated as unique. The lowercase
'mgr' is not treated the same as uppercase 'MGR'. Therefore, the lower case 'mgr'
is not selected.
Table 7. ″SELECT * FROM STAFF WHERE JOB = ’MGR’ ″ Using Unique-Weight Sort
Sequence for the ENU Language Identifier.
ID NAME DEPT JOB YEARS SALARY COMM
30 Merenghi 38 MGR 5 17506.75 0

Table 8 shows how record selection is done with a shared-weight sort sequence. In
Table 8, the rows that match the record selection criteria for the column 'JOB' are
selected by treating uppercase letters the same as lowercase letters. Notice that in
Table 8 all the values 'mgr', 'Mgr' and 'MGR' are selected.
Table 8. ″SELECT * FROM STAFF WHERE JOB = ’MGR’ ″ Using the Shared-Weight Sort
Sequence for the ENU Language Identifier.
ID NAME DEPT JOB YEARS SALARY COMM
10 Sanders 20 Mgr 7 18357.50 0

52 DB2 UDB for AS/400 SQL Programming V4R4

 Table 8. ″SELECT * FROM STAFF WHERE JOB = ’MGR’ ″ Using the Shared-Weight Sort
Sequence for the ENU Language Identifier. (continued)
ID NAME DEPT JOB YEARS SALARY COMM
30 Merenghi 38 MGR 5 17506.75 0
50 Hanes 15 Mgr 10 20659.80 0
100 Plotz 42 mgr 6 18352.80 0

Sort Sequence and Views

Views are created with the sort sequence that was in effect when the CREATE
VIEW statement was run. When the view is referred to in a FROM clause, that sort
sequence is used for any character comparisons in the subselect of the CREATE
VIEW. At that time, an intermediate result table is produced from the view
subselect. The sort sequence in effect when the query is being run is then applied
to all the character and UCS-2 graphic comparisons (including those comparisons
involving implicit conversions to character or UCS-2 graphic) specified in the query.

The following SQL statements and tables show how views and sort sequences
work. View V1, used in the following examples, was created with a shared-weight
sort sequence of SRTSEQ(*LANGIDSHR) and LANGID(ENU). The CREATE VIEW
statement would be as follows:
CREATE VIEW V1 AS SELECT *
FROM STAFF
WHERE JOB = 'MGR' AND ID < 100

Table 9 shows the result table from the view.

Table 9. ″SELECT * FROM V1″
ID NAME DEPT JOB YEARS SALARY COMM
10 Sanders 20 Mgr 7 18357.50 0
30 Merenghi 38 MGR 5 17506.75 0
50 Hanes 15 Mgr 10 20659.80 0

Any queries run against view V1 are run against the result table shown in Table 9.
The query shown below is run with a sort sequence of SRTSEQ(*LANGIDUNQ)
and LANGID(ENU).
Table 10. ″SELECT * FROM V1 WHERE JOB = ’MGR’″ Using the Unique-Weight Sort
Sequence for Language Identifier ENU
ID NAME DEPT JOB YEARS SALARY COMM
30 Merenghi 38 MGR 5 17506.75 0

Sort Sequence and the CREATE INDEX Statement

Indexes are created using the sort sequence that was in effect when the CREATE
INDEX statement was run. An entry is added to the index every time an insert is
made into the table over which the index is defined. Index entries contain the
weighted value for character key and UCS-2 graphic key columns. The system gets
the weighted value by converting the key value based on the sort sequence of the
index.

Chapter 3. Basic Concepts and Techniques 53

 When selection is made using that sort sequence and that index, the character or
UCS-2 graphic keys do not need to be converted prior to comparison. This
improves the performance of the query.

Sort Sequence and Constraints

Unique constraints are implemented with indexes. If the table on which a unique
constraint is added was defined with a sort sequence, the index will be created with
that same sort sequence.

If defining a referential constraint, the sort sequence between the parent and
dependent table must match. For more information on sort sequence and
constraints, see the DB2 UDB for AS/400 Database Programming book.

The sort sequence used at the time a check constraint is defined is the same sort
sequence the system uses to validate adherence to the constraint at the time of an
INSERT or UPDATE.

54 DB2 UDB for AS/400 SQL Programming V4R4

Chapter 4. Using a Cursor
When SQL runs a select statement, the resulting rows comprise the result table. A
cursor provides a way to access a result table. It is used within an SQL program to
maintain a position in the result table. SQL uses a cursor to work with the rows in
the result table and to make them available to your program. Your program can
have several cursors, although each must have a unique name.

Statements related to using a cursor include the following:

v A DECLARE CURSOR statement to define and name the cursor and specify the
rows to be retrieved with the embedded select statement.
v OPEN and CLOSE statements to open and close the cursor for use within the
program. The cursor must be opened before any rows can be retrieved.
v A FETCH statement to retrieve rows from the cursor’s result table or to position
the cursor on another row.
v An UPDATE ... WHERE CURRENT OF statement to update the current row of a
cursor.
v A DELETE ... WHERE CURRENT OF statement to delete the current row of a
cursor.

Types of cursors
SQL supports serial and scrollable cursors. The type of cursor determines the
positioning methods which can be used with the cursor.

Serial cursor
A serial cursor is one defined without the SCROLL keyword.

For a serial cursor, each row of the result table can be fetched only once per OPEN
of the cursor. When the cursor is opened, it is positioned before the first row in the
result table. When a FETCH is issued, the cursor is moved to the next row in the
result table. That row is then the current row. If host variables are specified (with
the INTO clause on the FETCH statement), SQL moves the current row’s contents
into your program’s host variables.

This sequence is repeated each time a FETCH statement is issued until the
end-of-data (SQLCODE = 100) is reached. When you reach the end-of-data, close
the cursor. You cannot access any rows in the result table after you reach the
end-of-data. To use the cursor again, you must first close the cursor and then
re-issue the OPEN statement. You can never back up.

Scrollable cursor
For a scrollable cursor, the rows of the result table can be fetched many times. The
cursor is moved through the result table based on the position option specified on
the FETCH statement. When the cursor is opened, it is positioned before the first
row in the result table. When a FETCH is issued, the cursor is positioned to the row
in the result table that is specified by the position option. That row is then the
current row. If host variables are specified (with the INTO clause on the FETCH

© Copyright IBM Corp. 1997, 1999 55

 statement), SQL moves the current row’s contents into your program’s host
variables. Host variables cannot be specified for the BEFORE and AFTER position
options.

This sequence is repeated each time a FETCH statement is issued. The cursor
does not need to be closed when an end-of-data or beginning-of-data condition
occurs. The position options enable the program to continue fetching rows from the
table.

The following scroll options are used to position the cursor when issuing a FETCH
statement. These positions are relative to the current cursor location in the result
table.

NEXT Positions the cursor on the next row. This is the default if no
position is specified.
PRIOR Positions the cursor on the previous row.
FIRST Positions the cursor on the first row.
LAST Positions the cursor on the last row.
BEFORE Positions the cursor before the first row.
AFTER Positions the cursor after the last row.
CURRENT Does not change the cursor position.
RELATIVE n Evaluates a host variable or integer n in relationship to the
cursor’s current position. For example, if n is -1, the cursor is
positioned on the previous row of the result table. If n is +3,
the cursor is positioned three rows after the current row.

Example of Using a Cursor

Suppose your program examines data about people in department D11. The
following examples show the SQL statements you would include in a program to
define and use a serial and a scrollable cursor. These cursors can be used to
obtain information about the department from the CORPDATA.EMPLOYEE table.

For the serial cursor example, the program processes all of the rows from the table,
updating the job for all members of department D11 and deleting the records of
employees from the other departments.
Table 11. A Serial Cursor Example
Serial Cursor SQL Statement Described in Section
EXEC SQL “Step 1: Define the Cursor” on page 58.
DECLARE THISEMP CURSOR FOR
SELECT EMPNO, LASTNAME,
WORKDEPT, JOB
FROM CORPDATA.EMPLOYEE
FOR UPDATE OF JOB
END-EXEC.
EXEC SQL “Step 2: Open the Cursor” on page 59.
OPEN THISEMP
END-EXEC.
EXEC SQL “Step 3: Specify What to Do When
WHENEVER NOT FOUND End-of-Data Is Reached” on page 59.
GO TO CLOSE-THISEMP
END-EXEC.

56 DB2 UDB for AS/400 SQL Programming V4R4

Table 11. A Serial Cursor Example (continued)
Serial Cursor SQL Statement Described in Section
EXEC SQL “Step 4: Retrieve a Row Using a Cursor” on
FETCH THISEMP page 60.
INTO :EMP-NUM, :NAME2,
:DEPT, :JOB-CODE
END-EXEC.
... for all employees “Step 5a: Update the Current Row” on
in department D11, update page 61.
the JOB value:

EXEC SQL
UPDATE CORPDATA.EMPLOYEE
SET JOB = :NEW-CODE
WHERE CURRENT OF THISEMP
END-EXEC.

... then print the row.

... for other employees, “Step 5b: Delete the Current Row” on
delete the row: page 61.

EXEC SQL
DELETE FROM CORPDATA.EMPLOYEE
WHERE CURRENT OF THISEMP
END-EXEC.
Branch back to fetch and process the next
row.
CLOSE-THISEMP. “Step 6: Close the Cursor” on page 62.
EXEC SQL
CLOSE THISEMP
END-EXEC.

For the scrollable cursor example, the program uses the RELATIVE position option
to obtain a representative sample of salaries from department D11.
Table 12. Scrollable Cursor Example
Scrollable Cursor SQL Statement Described in Section
EXEC SQL “Step 1: Define the Cursor” on page 58.
DECLARE THISEMP DYNAMIC SCROLL CURSOR FOR
SELECT EMPNO, LASTNAME,
SALARY
FROM CORPDATA.EMPLOYEE
WHERE WORKDEPT = ’D11’
END-EXEC.
EXEC SQL “Step 2: Open the Cursor” on page 59.
OPEN THISEMP
END-EXEC.
EXEC SQL “Step 3: Specify What to Do When
WHENEVER NOT FOUND End-of-Data Is Reached” on page 59.
GO TO CLOSE-THISEMP
END-EXEC.

Chapter 4. Using a Cursor 57

 Table 12. Scrollable Cursor Example (continued)
Scrollable Cursor SQL Statement Described in Section
...initialize program summation “Step 4: Retrieve a Row Using a Cursor” on
salary variable page 60.
EXEC SQL
FETCH RELATIVE 3 FROM THISEMP
INTO :EMP-NUM, :NAME2,
:JOB-CODE
END-EXEC.
...add the current salary to
program summation salary
...branch back to fetch and
process the next row.
...calculate the average
salary
CLOSE-THISEMP. “Step 6: Close the Cursor” on page 62.
EXEC SQL
CLOSE THISEMP
END-EXEC.

Step 1: Define the Cursor

To define a result table to be accessed with a cursor, use the DECLARE CURSOR
statement.

The DECLARE CURSOR statement names a cursor and specifies a

select-statement. The select-statement defines a set of rows that, conceptually,
make up the result table. For a serial cursor, the statement looks like this (the FOR
UPDATE OF clause is optional):
EXEC SQL
DECLARE cursor-name CURSOR FOR
SELECT column-1, column-2 ,...
FROM table-name , ...
FOR UPDATE OF column-2 ,...
END-EXEC.

For a scrollable cursor, the statement looks like this (the WHERE clause is
optional):
EXEC SQL
DECLARE cursor-name DYNAMIC SCROLL CURSOR FOR
SELECT column-1, column-2 ,...
FROM table-name ,...
WHERE column-1 = expression ...
END-EXEC.

The select-statements shown here are rather simple. However, you can code
several other types of clauses in a select-statement within a DECLARE CURSOR
statement for a serial and a scrollable cursor.

If you intend to update any columns in any or all of the rows of the identified table
(the table named in the FROM clause), include the FOR UPDATE OF clause. It
names each column you intend to update. If you do not specify the names of
columns, and you specify either the ORDER BY clause or FOR READ ONLY
clause, a negative SQLCODE is returned if an update is attempted. If you do not

58 DB2 UDB for AS/400 SQL Programming V4R4

 specify the FOR UPDATE OF clause, the FOR READ ONLY clause, or the ORDER
BY clause, and the result table is not read-only, you can update any of the columns
of the specified table.

You can update a column of the identified table even though it is not part of the
result table. In this case, you do not need to name the column in the SELECT
statement. When the cursor retrieves a row (using FETCH) that contains a column
value you want to update, you can use UPDATE ... WHERE CURRENT OF to
update the row.

For example, assume that each row of the result table includes the EMPNO,
LASTNAME, and WORKDEPT columns from the CORPDATA.EMPLOYEE table. If
you want to update the JOB column (one of the columns in each row of the
CORPDATA.EMPLOYEE table), the DECLARE CURSOR statement should include
FOR UPDATE OF JOB ... even though JOB is omitted from the SELECT statement.

The result table and cursor are read-only if any of the following are true:
v The first FROM clause identifies more than one table or view.
v The first FROM clause identifies a read-only view.
v The first SELECT clause specifies the keyword DISTINCT.
v The outer subselect contains a GROUP BY clause.
v The outer subselect contains a HAVING clause.
v The first SELECT clause contains a column function.
v The select-statement contains a subquery such that the base object of the outer
subselect and of the subquery is the same table.
v The select-statement contains a UNION or UNION ALL operator.
v The select-statement contains an ORDER BY clause, and the FOR UPDATE OF
clause and DYNAMIC SCROLL are not specified.
v The select-statement includes a FOR READ ONLY clause.
v The SCROLL keyword is specified without DYNAMIC.
| v The select-list includes a DataLink column and a FOR UPDATE OF clause is not
| specified.

Step 2: Open the Cursor

To begin processing the rows of the result table, issue the OPEN statement. When
your program issues the OPEN statement, SQL processes the select-statement
within the DECLARE CURSOR statement to identify a set of rows, called a result
table 3, using the current value of any host variables specified in the
select-statement. The OPEN statement looks like this:
EXEC SQL
OPEN cursor-name
END-EXEC.

Step 3: Specify What to Do When End-of-Data Is Reached

To find out when the end of the result table is reached, test the SQLCODE field for
a value of 100 or test the SQLSTATE field for a value of '02000' (that is,

3. A result table can contain zero, one, or many rows, depending on the extent to which the search condition is satisfied.

Chapter 4. Using a Cursor 59

 end-of-data). This condition occurs when the FETCH statement has retrieved the
last row in the result table and your program issues a subsequent FETCH. For
example:
...
IF SQLCODE =100 GO TO DATA-NOT-FOUND.

or

IF SQLSTATE ='02000' GO TO DATA-NOT-FOUND.

An alternative to this technique is to code the WHENEVER statement. Using

WHENEVER NOT FOUND can result in a branch to another part of your program,
where a CLOSE statement is issued. The WHENEVER statement looks like this:
EXEC SQL
WHENEVER NOT FOUND GO TO symbolic-address
END-EXEC.

Your program should anticipate an end-of-data condition whenever a cursor is used

to fetch a row, and should be prepared to handle this situation when it occurs.

When you are using a serial cursor and the end-of-data is reached, every
subsequent FETCH statement returns the end-of-data condition. You cannot
position the cursor on rows that are already processed. The CLOSE statement is
the only operation that can be performed on the cursor.

When you are using a scrollable cursor and the end-of-data is reached, the result
table can still process more data. You can position the cursor anywhere in the result
table using a combination of the position options. You do not need to CLOSE the
cursor when the end-of-data is reached.

Step 4: Retrieve a Row Using a Cursor

To move the contents of a selected row into your program’s host variables, use the
FETCH statement. The SELECT statement within the DECLARE CURSOR
statement identifies rows that contain the column values your program wants.
However, SQL does not retrieve any data for your application program until the
FETCH statement is issued.

When your program issues the FETCH statement, SQL uses the current cursor
position as a starting point to locate the requested row in the result table. This
changes that row to the current row. If an INTO clause was specified, SQL moves
the current row’s contents into your program’s host variables. This sequence is
repeated each time the FETCH statement is issued.

SQL maintains the position of the current row (that is, the cursor points to the
current row) until the next FETCH statement for the cursor is issued. The UPDATE
statement does not change the position of the current row within the result table,
although the DELETE statement does.

The serial cursor FETCH statement looks like this:

EXEC SQL
FETCH cursor-name
INTO :host variable-1[, :host variable-2] ...
END-EXEC.

The scrollable cursor FETCH statement looks like this:

60 DB2 UDB for AS/400 SQL Programming V4R4

 EXEC SQL
FETCH RELATIVE integer
FROM cursor-name
INTO :host variable-1[, :host variable-2] ...
END-EXEC.

Step 5a: Update the Current Row

When your program has positioned the cursor on a row, you can update its data by
using the UPDATE statement with the WHERE CURRENT OF clause. The WHERE
CURRENT OF clause specifies a cursor that points to the row you want to update.
The UPDATE ... WHERE CURRENT OF statement looks like this:
EXEC SQL
UPDATE table-name
SET column-1 = value [, column-2 = value] ...
WHERE CURRENT OF cursor-name
END-EXEC.

When used with a cursor, the UPDATE statement:

v Updates only one row—the current row
v Identifies a cursor that points to the row to be updated
v Requires that the columns updated be named previously in the FOR UPDATE
OF clause of the DECLARE CURSOR statement, if an ORDER BY clause was
also specified

After you update a row, the cursor’s position remains on that row (that is, the
current row of the cursor does not change) until you issue a FETCH statement for
the next row.

Step 5b: Delete the Current Row

When your program has retrieved the current row, you can delete the row by using
the DELETE statement. To do this, you issue a DELETE statement designed for
use with a cursor; the WHERE CURRENT OF clause specifies a cursor that points
to the row you want to delete. The DELETE ... WHERE CURRENT OF statement
looks like this:
EXEC SQL
DELETE FROM table-name
WHERE CURRENT OF cursor-name
END-EXEC.

When used with a cursor, the DELETE statement:

v Deletes only one row—the current row
v Uses the WHERE CURRENT OF clause to identify a cursor that points to the
row to be deleted

After you delete a row, you cannot update or delete another row using that cursor
until you issue a FETCH statement to position the cursor.

“The DELETE Statement” on page 34 shows you how to use the DELETE
statement to delete all rows that meet a specific search condition. You can also use
the FETCH and DELETE ... WHERE CURRENT OF statements when you want to
obtain a copy of the row, examine it, then delete it.

Chapter 4. Using a Cursor 61

Step 6: Close the Cursor
If you processed the rows of a result table for a serial cursor, and you want to use
the cursor again, issue a CLOSE statement to close the cursor prior to re-opening
it.
EXEC SQL
CLOSE cursor-name
END-EXEC.

If you processed the rows of a result table and you do not want to use the cursor
again, you can let the system close the cursor. The system automatically closes the
cursor when:
v A COMMIT without HOLD statement is issued and the cursor is not declared
using the WITH HOLD clause.
v A ROLLBACK without HOLD statement is issued.
v The job ends.
v The activation group ends and CLOSQLCSR(*ENDACTGRP) was specified on
the precompile.
v The first SQL program in the call stack ends and neither
CLOSQLCSR(*ENDJOB) or CLOSQLCSR(*ENDACTGRP) was specified when
the program was precompiled.
v The connection to the application server is ended using the DISCONNECT
statement.
v The connection to the application server was released and a successful COMMIT
occurred.
v An *RUW CONNECT occurred.
Because an open cursor still holds locks on referred-to-tables or views, you
should explicitly close any open cursors as soon as they are no longer needed.

Using the Multiple-Row FETCH Statement

The multiple-row FETCH statement can be used to retrieve multiple rows from a
table or view with a single FETCH. The program controls the blocking of rows by
the number of rows requested on the FETCH statement (OVRDBF has no effect).
The maximum number of rows that can be requested on a single fetch call is
32767. Once the data is retrieved, the cursor is positioned on the last row retrieved.

There are two ways to define the storage where fetched rows are placed: a host
structure array or a row storage area with an associated descriptor. Both methods
can be coded in all of the languages supported by the SQL precompilers, with the
exception of the host structure array in REXX. Refer to Chapter 12. Coding SQL
Statements in C and C++ Applications, through Chapter 17. Coding SQL
Statements in REXX Applications, for more information on the programming
languages. Both forms of the multiple-row FETCH statement allow the application to
code a separate indicator array. The indicator array should contain one indicator for
each host variable that is null capable.

The multiple-row FETCH statement can be used with both serial and scrollable
cursors. The operations used to define, open, and close a cursor for a multiple-row
FETCH remain the same. Only the FETCH statement changes to specify the
number of rows to retrieve and the storage where the rows are placed.

62 DB2 UDB for AS/400 SQL Programming V4R4

 After each multiple-row FETCH, information is returned to the program through the
SQLCA. In addition to the SQLCODE and SQLSTATE fields, the SQLERRD
provides the following information:
v SQLERRD3 contains the number of rows retrieved on the multiple-row FETCH
statement. If SQLERRD3 is less than the number of rows requested, then an
error or end-of-data condition occurred.
v SQLERRD4 contains the length of each row retrieved.
v SQLERRD5 contains an indication that the last row in the table was fetched. It
can be used to detect the end-of-data condition in the table being fetched when
the cursor does not have immediate sensitivity to updates. Cursors which do
have immediate sensitivity to updates should continue fetching until an
SQLCODE +100 is received to detect an end-of-data condition.

Multiple-Row FETCH Using a Host Structure Array

To use the multiple-row FETCH with the host structure array, the application must
define a host structure array that can be used by SQL. Each language has its own
conventions and rules for defining a host structure array. Host structure arrays can
be defined by using variable declarations or by using compiler directives to retrieve
External File Descriptions (such as the COBOL COPY directive).

The host structure array consists of an array of structures. Each structure

corresponds to one row of the result table. The first structure in the array
corresponds to the first row, the second structure in the array corresponds to the
second row, and so on. SQL determines the attributes of elementary items in the
host structure array based on the declaration of the host structure array. To
maximize performance, the attributes of the items that make up the host structure
array should match the attributes of the columns being retrieved.

Consider the following COBOL example:

EXEC SQL INCLUDE SQLCA

END-EXEC.

...

01 TABLE-1.
02 DEPT OCCURS 10 TIMES.
05 EMPNO PIC X(6).
05 LASTNAME.
49 LASTNAME-LEN PIC S9(4) BINARY.
49 LASTNAME-TEXT PIC X(15).
05 WORKDEPT PIC X(3).
05 JOB PIC X(8).
01 TABLE-2.
02 IND-ARRAY OCCURS 10 TIMES.
05 INDS PIC S9(4) BINARY OCCURS 4 TIMES.

...
EXEC SQL
DECLARE D11 CURSOR FOR
SELECT EMPNO, LASTNAME, WORKDEPT, JOB
FROM CORPDATA.EMPLOYEE
WHERE WORKDEPT = "D11"
END-EXEC.

...

EXEC SQL
OPEN D11

Chapter 4. Using a Cursor 63

 END-EXEC.
PERFORM FETCH-PARA UNTIL SQLCODE NOT EQUAL TO ZERO.
ALL-DONE.
EXEC SQL CLOSE D11 END-EXEC.

...

FETCH-PARA.
EXEC SQL WHENEVER NOT FOUND GO TO ALL-DONE END-EXEC.
EXEC SQL FETCH D11 FOR 10 ROWS INTO :DEPT :IND-ARRAY
END-EXEC.

...

In this example, a cursor was defined for the CORPDATA.EMPLOYEE table to

select all rows where the WORKDEPT column equals 'D11'. The result table
contains eight rows. The DECLARE CURSOR and OPEN statements do not have
any special syntax when they are used with a multiple-row FETCH statement.
Another FETCH statement that returns a single row against the same cursor can be
coded elsewhere in the program. The multiple-row FETCH statement is used to
retrieve all of the rows in the result table. Following the FETCH, the cursor position
remains on the last row retrieved.

The host structure array DEPT and the associated indicator array IND-ARRAY are
defined in the application. Both arrays have a dimension of ten. The indicator array
has an entry for each column in the result table.

The attributes of type and length of the DEPT host structure array elementary items
match the columns that are being retrieved.

When the multiple-row FETCH statement has successfully completed, the host
structure array contains the data for all eight rows. The indicator array, IND_ARRAY,
contains zeros for every column in every row because no NULL values were
returned.

The SQLCA that is returned to the application contains the following information:
v SQLCODE contains 0
v SQLSTATE contains '00000'
v SQLERRD3 contains 8, the number of rows fetched
v SQLERRD4 contains 34, the length of each row
v SQLERRD5 contains +100, indicating the last row in the result table is in the
block

See Appendix B of the DB2 UDB for AS/400 SQL Reference book for a description
of the SQLCA.

Multiple-Row FETCH Using a Row Storage Area

The application must define a row storage area and an associated description area
before the application can use a multiple-row FETCH with a row storage area. The
row storage area is a host variable defined in the application program. The row
storage area contains the results of the multiple-row FETCH. A row storage area
can be a character variable with enough bytes to hold all of the rows requested on
the multiple-row FETCH.

64 DB2 UDB for AS/400 SQL Programming V4R4

An SQLDA that contains the SQLTYPE and SQLLEN for each returned column is
defined by the associated descriptor used on the row storage area form of the
multiple-row FETCH. The information provided in the descriptor determines the data
mapping from the database to the row storage area. To maximize performance, the
attribute information in the descriptor should match the attributes of the columns
retrieved.

See Appendix C of the DB2 UDB for AS/400 SQL Reference book for a description
of the SQLDA.

Consider the following PL/I example:

Chapter 4. Using a Cursor 65

 *....+....1....+....2....+....3....+....4....+....5....+....6....+....7...*
EXEC SQL INCLUDE SQLCA;
EXEC SQL INCLUDE SQLDA;

...

DCL DEPTPTR PTR;

DCL 1 DEPT(10) BASED(DEPTPTR),
3 EMPNO CHAR(6),
3 LASTNAME CHAR(15) VARYING,
3 WORKDEPT CHAR(3),
3 JOB CHAR(8);
DCL I BIN(31) FIXED;
DEC J BIN(31) FIXED;
DCL ROWAREA CHAR(2000);

...

ALLOCATE SQLDA SET(SQLDAPTR);

EXEC SQL
DECLARE D11 CURSOR FOR
SELECT EMPNO, LASTNAME, WORKDEPT, JOB
FROM CORPDATA.EMPLOYEE
WHERE WORKDEPT = 'D11';

...

EXEC SQL
OPEN D11;
/* SET UP THE DESCRIPTOR FOR THE MULTIPLE-ROW FETCH */
/* 4 COLUMNS ARE BEING FETCHED */
SQLD = 4;
SQLN = 4;
SQLDABC = 366;
SQLTYPE(1) = 452; /* FIXED LENGTH CHARACTER - */
/* NOT NULLABLE */
SQLLEN(1) = 6;
SQLTYPE(2) = 456; /*VARYING LENGTH CHARACTER */
/* NOT NULLABLE */
SQLLEN(2) = 15;
SQLTYPE(3) = 452; /* FIXED LENGTH CHARACTER - */
SQLLEN(3) = 3;
SQLTYPE(4) = 452; /* FIXED LENGTH CHARACTER - */
/* NOT NULLABLE */
SQLLEN(4) = 8;
/*ISSUE THE MULTIPLE-ROW FETCH STATEMENT TO RETRIEVE*/
/*THE DATA INTO THE DEPT ROW STORAGE AREA */
/*USE A HOST VARIABLE TO CONTAIN THE COUNT OF */
/*ROWS TO BE RETURNED ON THE MULTIPLE-ROW FETCH */

J = 10; /*REQUESTS 10 ROWS ON THE FETCH */

...

Figure 2. Example of Multiple-Row FETCH Using a Row Storage Area (Part 1 of 2)

66 DB2 UDB for AS/400 SQL Programming V4R4

EXEC SQL
WHENEVER NOT FOUND
GOTO FINISHED;
EXEC SQL
WHENEVER SQLERROR
GOTO FINISHED;
EXEC SQL
FETCH D11 FOR :J ROWS
USING DESCRIPTOR :SQLDA INTO :ROWAREA;
/* ADDRESS THE ROWS RETURNED */
DEPTPTR = ADDR(ROWAREA);
/*PROCESS EACH ROW RETURNED IN THE ROW STORAGE */
/*AREA BASED ON THE COUNT OF RECORDS RETURNED */
/*IN SQLERRD3. */
DO I = 1 TO SQLERRD(3);
IF EMPNO(I) = '000170' THEN
DO;
:
END;
END;
IF SQLERRD(5) = 100 THEN
DO;
/* PROCESS END OF FILE */
END;
FINISHED:

Figure 2. Example of Multiple-Row FETCH Using a Row Storage Area (Part 2 of 2)

In this example, a cursor has been defined for the CORPDATA.EMPLOYEE table to
select all rows where the WORKDEPT column equal 'D11'. The sample EMPLOYEE
table in Appendix A. DB2 UDB for AS/400 Sample Tables shows the result table
contains eight rows. The DECLARE CURSOR and OPEN statements do not have
special syntax when they are used with a multiple-row FETCH statement. Another
FETCH statement that returns a single row against the same cursor can be coded
elsewhere in the program. The multiple-row FETCH statement is used to retrieve all
rows in the result table. Following the FETCH, the cursor position remains on the
eighth record in the block.

The row area, ROWAREA, is defined as a character array. The data from the result
table is placed in the host variable. In this example, a pointer variable is assigned to
the address of ROWAREA. Each item in the rows that are returned is examined
and used with the based structure DEPT.

The attributes (type and length) of the items in the descriptor match the columns
that are retrieved. In this case, no indicator area is provided.

After the FETCH statement is completed, the ROWAREA contains eight rows. The
SQLCA that is returned to the application contains the following:
v SQLCODE contains 0
v SQLSTATE contains '00000'
v SQLERRD3 contains 8, the number of rows returned
v SQLERRD4 contains 34, for the length of the row fetched
v SQLERRD5 contains +100, indicating the last row in the result table was fetched

In this example, the application has taken advantage of the fact that SQLERRD5
contains an indication of the end of the file being reached. As a result, the
application does not need to call SQL again to attempt to retrieve more rows. If the

Chapter 4. Using a Cursor 67

 cursor has immediate sensitivity to inserts, you should call SQL in case any records
were added. Cursors have immediate sensitivity when the commitment control level
is something other than *RR.

Unit of Work and Open Cursors

When your program completes a unit of work, it should commit or rollback the
changes you made. Unless you specified HOLD on the COMMIT or ROLLBACK
statement, all open cursors are automatically closed by SQL. Cursors that are
declared with the WITH HOLD clause are not automatically closed on COMMIT.
They are automatically closed on a ROLLBACK (the WITH HOLD clause specified
on the DECLARE CURSOR statement is ignored).

If you want to continue processing from the current cursor position after a COMMIT
or ROLLBACK, you must specify COMMIT HOLD or ROLLBACK HOLD. When
HOLD is specified, any open cursors are left open and keep their cursor position so
processing can resume. On a COMMIT statement, the cursor position is
maintained. On a ROLLBACK statement, the cursor position is restored to just after
the last row retrieved from the previous unit of work. All record locks are still
released.

After issuing a COMMIT or ROLLBACK statement without HOLD, all locks are
released and all cursors are closed. You can open the cursor again, but you will
begin processing at the first row of the result table.

Note: Specification of the ALWBLK(*ALLREAD) parameter of the CRTSQLxxx

commands can change the restoration of the cursor position for read-only
cursors. See Chapter 10. Dynamic SQL Applications for information on the
use of the ALWBLK parameter and other performance related options on the
CRTSQLxxx commands.

68 DB2 UDB for AS/400 SQL Programming V4R4

Chapter 5. Advanced Coding Techniques
This chapter covers the more advanced SQL coding techniques. The topics
included in this chapter are:
v inserting rows
v updating rows
v preventing duplicate rows
v searching
v joining
v using UNION
v using subqueries
v using views
v using indexes
v using the system catalog

Advanced Insert Techniques

The next two sections cover two advanced techniques on how to insert rows into a
table. The first section Inserting Rows into a Table Using a Select-Statement
discusses how to insert more than one row at a time using a select statement. The
second section Using the Blocked Insert Statement discusses how to insert multiple
rows that are in a host structure array. The second method can be used with all
languages except REXX.

Inserting Rows into a Table Using a Select-Statement

You can use a select-statement within an INSERT statement to insert zero, one, or
more rows selected from the table or view you specify into another table. The table
you select the rows from can be the same table you are inserting into. If they are
the same table, SQL will create a temporary result table containing the selected
rows and then insert from the temporary table into the target table.

One use for this kind of INSERT statement is to move data into a table you created
for summary data. For example, suppose you want a table that shows each
employee’s time commitments to projects. You could create a table called
EMPTIME with the columns EMPNUMBER, PROJNUMBER, STARTDATE,
ENDDATE, and TTIME, and then use the following INSERT statement to fill the
table:
INSERT INTO CORPDATA.EMPTIME
(EMPNUMBER, PROJNUMBER, STARTDATE, ENDDATE)
SELECT EMPNO, PROJNO, EMSTDATE, EMENDATE
FROM CORPDATA.EMP_ACT

The select-statement embedded in the INSERT statement is no different from the

select-statement you use to retrieve data. With the exception of FOR READ ONLY,
FOR UPDATE OF, or the OPTIMIZE clause, you can use all the keywords, column
functions, and techniques used to retrieve data. SQL inserts all the rows that meet
the search conditions into the table you specify. Inserting rows from one table into
another table does not affect any existing rows in either the source table or the
target table.

© Copyright IBM Corp. 1997, 1999 69

 Notes on Multiple-Row Insertion
You should consider the following when inserting multiple rows into a table:
v The number of columns implicitly or explicitly listed in the INSERT statement
must equal the number of columns listed in the select-statement.
v The data in the columns you are selecting must be compatible with the columns
you are inserting into when using the INSERT with select-statement.
v In the event the select-statement embedded in the INSERT returns no rows, an
SQLCODE of 100 is returned to alert you that no rows were inserted. If you
successfully insert rows, the SQLERRD(3) field of the SQLCA has an integer
representing the number of rows SQL actually inserted.
v If SQL finds an error while running the INSERT statement, SQL stops the
operation. If you specify COMMIT (*CHG), COMMIT(*CS), COMMIT (*ALL), or
COMMIT(*RR), nothing is inserted into the table and a negative SQLCODE is
returned. If you specify COMMIT(*NONE), any rows inserted prior to the error
remain in the table.
v You can join two or more tables with a select-statement in an INSERT statement.
Loaded in this manner, the table can be operated on with UPDATE, DELETE,
and INSERT statements, because the rows exist as physically stored rows in a
table.

Using the Blocked Insert Statement

A blocked INSERT can be used to insert multiple rows into a table with a single
statement. The blocked INSERT statement is supported in all of the languages
except REXX. The data inserted into the table must be in a host structure array. If
indicator variables are used with a blocked INSERT, they must also be in a host
structure array. For information on host structure arrays for a particular language,
refer to the chapter on that language.

For example, to add ten employees to the CORPDATA.EMPLOYEE table:

INSERT INTO CORPDATA.EMPLOYEE
(EMPNO,FIRSTNME,MIDINIT,LASTNAME,WORKDEPT)
10 ROWS VALUES(:DSTRUCT:ISTRUCT)

DSTRUCT is a host structure array with five elements that is declared in the
program. The five elements correspond to EMPNO, FIRSTNME, MIDINIT,
LASTNAME, and WORKDEPT. DSTRUCT has a dimension of at least ten to
accommodate inserting ten rows. ISTRUCT is a host structure array that is declared
in the program. ISTRUCT has a dimension of at least ten small integer fields for the
indicators.

Blocked INSERT statements are supported for non-distributed SQL applications and
for distributed applications where both the application server and the application
requester are AS/400 systems.

Advanced Update Techniques

The SET clause of an UPDATE statement can be used in many ways to determine
the actual values to be set in each row being updated. The following example lists
each column with its corresponding value:

70 DB2 UDB for AS/400 SQL Programming V4R4

 UPDATE EMPLOYEE
SET WORKDEPT = 'D11',
PHONENO = '7213',
JOB = 'DESIGNER'
WHERE EMPNO = '000270'

The previous update can also be written by specifying all of the columns and then
all of the values:
UPDATE EMPLOYEE
SET (WORKDEPT, PHONENO, JOB)
= ('D11', '7213', 'DESIGNER')
WHERE EMPNO = '000270'

Another way to select a value (or multiple values) for an update is to use a
scalar-subselect. The scalar-subselect allows you to update one or more columns
by setting them to one or more values selected from another table. In the following
example, an employee moves to a different department but continues working on
the same projects. The employee table has already been updated to contain the
new department number. Now the project table needs to be updated to reflect the
new department number of this employee (employee number is ’000030’).
UPDATE PROJECT
SET DEPTNO =
(SELECT WORKDEPT FROM EMPLOYEE
WHERE PROJECT.RESPEMP = EMPLOYEE.EMPNO)
WHERE RESPEMP='000030'

This same technique could be used to update a list of columns with multiple values
returned from a single select.

It is also possible to update an entire row in one table with values from a row in
another table.

Suppose there is a master class schedule table that needs to be udpated with
changes that have been made in a copy of the table. The changes are made to the
work copy and merged into the master table every night. The two tables have
exactly the same columns and one column, CLASS_CODE, is a unique key
column.
UPDATE CL_SCHED
SET ROW =
(SELECT * FROM MYCOPY
WHERE CL_SCHED.CLASS_CODE = MYCOPY.CLASS_CODE)

This update will update all of the rows in CL_SCHED with the values from
MYCOPY.

Preventing Duplicate Rows

When SQL evaluates a select-statement, several rows might qualify to be in the
result table, depending on the number of rows that satisfy the select-statement’s
search condition. Some of the rows in the result table might be duplicates. You can
specify that you do not want any duplicates by using the DISTINCT keyword,
followed by the list of column names:
SELECT DISTINCT JOB, SEX
...

DISTINCT means you want to select only the unique rows. If a selected row
duplicates another row in the result table, the duplicate row is ignored (it is not put

Chapter 5. Advanced Coding Techniques 71

 into the result table). For example, suppose you want a list of employee job codes.
You do not need to know which employee has what job code. Because it is
probable that several people in a department have the same job code, you can use
DISTINCT to ensure that the result table has only unique values.

The following example shows how to do this:

DECLARE XMP2 CURSOR FOR
SELECT DISTINCT JOB
FROM CORPDATA.EMPLOYEE
WHERE WORKDEPT = :JOB-DEPT
...
FETCH XMP2
INTO :JOB

The result is two rows (in this example, JOB-DEPT is set to D11).

fetch JOB
1 Designer
RV2W557-2

If you do not include DISTINCT in a SELECT clause, you might find duplicate rows
in your result, because SQL retrieves the JOB column’s value for each row that
satisfies the search condition. Null values are treated as duplicate rows for
DISTINCT.

If you include DISTINCT in a SELECT clause and you also include a shared-weight
sort sequence, fewer values are returned. The sort sequence causes values that
contain the same characters to be weighted the same. If 'MGR', 'Mgr', and 'mgr'
were all in the same table, only one of these values would be returned.

Performing Complex Search Conditions

The following section explains more advanced things you can do with search
conditions.

Keywords for Use in Search Conditions

A search condition can contain any of the keywords BETWEEN, IN, IS NULL, and
LIKE.

Note: Constants are shown in the following examples to keep the examples simple.
However, you could just as easily code host variables instead. Remember to
precede each host variable with a colon.

For character and UCS-2 graphic column predicates, the sort sequence is applied
to the operands before evaluation of the predicates for BETWEEN, IN, EXISTS, and
LIKE clauses. See “Using Sort Sequence in SQL” on page 50 for more information
on the using sort sequence with selection.
v BETWEEN ... AND ... is used to specify a search condition that is satisfied by
any value that falls on or between two other values. For example, to find all
employees who were hired in 1987, you could use this:
... WHERE HIREDATE BETWEEN '1987-01-01' AND '1987-12-31'

72 DB2 UDB for AS/400 SQL Programming V4R4

 The BETWEEN keyword is inclusive. A more complex, but explicit, search
condition that produces the same result is:
... WHERE HIREDATE >= '1987-01-01' AND HIREDATE <= '1987-12-31'
v IN says you are interested in rows in which the value of the specified expression
is among the values you listed. For example, to find the names of all employees
in departments A00, C01, and E21, you could specify:
... WHERE WORKDEPT IN ('A00', 'C01', 'E21')
v LIKE says you are interested in rows in which a column value is similar to the
value you supply. When you use LIKE, SQL searches for a character string
similar to the one you specify. The degree of similarity is determined by two
special characters used in the string that you include in the search condition:
_ An underline character stands for any single character.
% A percent sign stands for an unknown string of 0 or more characters. If
the percent sign starts the search string, then SQL allows 0 or more
character(s) to precede the matching value in the column. Otherwise, the
search string must begin in the first position of the column.

Note: If you are operating on MIXED data, the following distinction applies: an
SBCS underline character refers to one SBCS character. No such
restriction applies to the percent sign; that is, a percent sign refers to any
number of SBCS or DBCS characters. See the DB2 UDB for AS/400 SQL
Reference book for more information on the LIKE predicate and MIXED
data.

Use the underline character or percent sign either when you do not know or do
not care about all the characters of the column’s value. For example, to find out
which employees live in Minneapolis, you could specify:
... WHERE ADDRESS LIKE '%MINNEAPOLIS%'

In this case, you should be sure that MINNEAPOLIS was not part of a street
address or part of another city name. SQL returns any row with the string
MINNEAPOLIS in the ADDRESS column, no matter where the string occurs.

In another example, to list the towns whose names begin with 'SAN', you could
specify:
... WHERE TOWN LIKE 'SAN%'

If you want to search for a character string that contains either the underscore or
percent character, use the ESCAPE clause to specify an escape character. For
example, to see all businesses that have a percent in their name, you could
specify:
... WHERE BUSINESS_NAME LIKE '%@%%' ESCAPE '@'

The first and last percent characters are interpreted as usual. The combination
’@%’ is taken as the actual percent character.

Special Considerations for LIKE

v When host variables are used in place of string constants in a search pattern,
you should consider using varying length host variables. This allows you to:
– Assign previously used string constants to host variables without any change.
– Obtain the same selection criteria and results as if a string constant was used.
v When fixed-length host variables are used in place of string constants in a
search pattern, you should ensure the value specified in the host variable

Chapter 5. Advanced Coding Techniques 73

 matches the pattern previously used by the string constants. All characters in a
host variable that are not assigned a value are initialized with a blank.
For example, if you did a search using the string pattern ’ABC%’, these are some
of the values that could be returned:
'ABCD ' 'ABCDE' 'ABCxxx' 'ABC '

For example, if you did a search using the search pattern ’ABC%’ contained in a
host variable with a fixed length of 10, these are some the values that could be
returned assuming the column has a length of 12:
'ABCDE ' 'ABCD ' 'ABCxxx ' 'ABC '

Note that all returned values start with ’ABC’ and end with at least six blanks.
This is because the last six characters in the host variable were not assigned a
specific value so blanks were used.

If you wanted to do a search on a fixed-length host variable where the last 7

characters could be anything, you would search for ’ABC%%%%%%%’. These
are some values that could be returned:
'ABCDEFGHIJ' 'ABCXXXXXXX' 'ABCDE' 'ABCDD'

Multiple Search Conditions within a WHERE Clause

You saw how to qualify a request using one search condition. You can qualify your
request further by coding a search condition that includes several predicates. The
search condition you specify can contain any of the comparison operators or the
keywords BETWEEN, IN, LIKE, IS NULL, and IS NOT NULL.

You can join any two predicates with the connectors AND and OR. In addition, you
can use the NOT keyword to specify that the desired search condition is the
negated value of the specified search condition. A WHERE clause can have as
many predicates as you want.
v AND says that, for a row to qualify, the row must satisfy both predicates of the
search condition. For example, to find out which employees in department D21
were hired after December 31, 1987, you would specify:
...
WHERE WORKDEPT = 'D21' AND HIREDATE > '1987-12-31'
v OR says that, for a row to qualify, the row can satisfy the condition set by either
or both predicates of the search condition. For example, to find out which
employees are in either department C01 or D11, you could specify 4:
...
WHERE WORKDEPT = 'C01' OR WORKDEPT = 'D11'
v NOT says that, to qualify, a row must not meet the criteria set by the search
condition or predicate that follows the NOT. For example, to find all employees in
department E11 except those with a job code equal to analyst, you could specify:
...
WHERE WORKDEPT = 'E11' AND NOT JOB = 'ANALYST'

When SQL evaluates search conditions that contain these connectors, it does so in
a specific order. SQL first evaluates the NOT clauses, next evaluates the AND
clauses, and then the OR clauses.

4. You could also use IN to specify this request: WHERE WORKDEPT IN ('C01', 'D11').

74 DB2 UDB for AS/400 SQL Programming V4R4

 You can change the order of evaluation by using parentheses. The search
conditions enclosed in parentheses are evaluated first. For example, to select all
employees in departments E11 and E21 who have education levels greater than 12,
you could specify:
...
WHERE EDLEVEL > 12 AND
(WORKDEPT = 'E11' OR WORKDEPT = 'E21')

The parentheses determine the meaning of the search condition. In this example,
you want all rows that have a:
WORKDEPT value of E11 or E21, and
EDLEVEL value greater than 12

If you did not use parentheses:

...
WHERE EDLEVEL > 12 AND WORKDEPT = 'E11'
OR WORKDEPT = 'E21'

Your result is different. The selected rows are rows that have:
WORKDEPT = E11 and EDLEVEL > 12, or
WORKDEPT = E21, regardless of the EDLEVEL value

Joining Data from More Than One Table

Sometimes the information you want to see is not in a single table. To form a row of
the result table, you might want to retrieve some column values from one table and
some column values from another table. You can retrieve and join column values
from two or more tables into a single row.

Four different types of joins are supported by DB2 UDB for AS/400: inner join, left
outer join, exception join, and cross join.
v An “Inner Join” returns only the rows from each table that have matching values
in the join columns. Any rows that do not have a match between the tables will
not appear in the result table.
v A “Left Outer Join” on page 76 returns values for all of the rows from the first
table (the table on the left) and the values from the second table for the rows that
match. Any rows that do not have a match in the second table will return the null
value for all columns from the second table.
v An “Exception Join” on page 77 returns only the rows from the left table that do
not have a match in the right table. Columns in the result table that come from
the right table have the null value.
v A “Cross Join” on page 78 returns a row in the result table for each combination
of rows from the tables being joined (a Cartesian Product).

Inner Join
With an inner join, column values from one row of a table are combined with
column values from another row of another (or the same) table to form a single row
of data. SQL examines both tables specified for the join to retrieve data from all the
rows that meet the search condition for the join. There are two ways of specifying
an inner join: using the JOIN syntax, and using the WHERE clause.

Chapter 5. Advanced Coding Techniques 75

 Suppose you want to retrieve the employee numbers, names, and project numbers
for all employees that are responsible for a project. In other words, you want the
EMPNO and LASTNAME columns from the CORPDATA.EMPLOYEE table and the
PROJNO column from the CORPDATA.PROJECT table. Only employees with last
names starting with ’S’ or later should be considered. To find this information, you
need to join the two tables.

Inner Join Using JOIN Syntax

To use the inner join syntax, both of the tables you are joining are listed in the
FROM clause, along with the join condition that applies to the tables. The join
condition is specified after the ON keyword and determines how the two tables are
to be compared to each other to produce the join result. The condition can be any
comparison operator; it does not need to be the equal operator. Multiple join
conditions can be specified in the ON clause separated by the AND keyword. Any
additional conditions that do not relate to the actual join are specified in either the
WHERE clause or as part of the actual join in the ON clause.
SELECT EMPNO, LASTNAME, PROJNO
FROM CORPDATA.EMPLOYEE INNER JOIN CORPDATA.PROJECT
ON EMPNO = RESPEMP
WHERE LASTNAME > 'S'

In this example, the join is done on the two tables using the EMPNO and
RESPEMP columns from the tables. Since only employees that have last names
starting with at least ’S’ are to be returned, this additional condition is provided in
the WHERE clause.

This query returns the following output:

EMPNO LASTNAME PROJNO

000020 THOMPSON PL2100
000060 STERN MA2110
000100 SPENSER OP2010
000250 SMITH AD3112

Inner Join Using the WHERE Clause

Using the WHERE clause to perform this same join is written with both the join
condition and the additional selection condition in the WHERE clause. The tables to
be joined are listed in the FROM clause, separated by commas.
SELECT EMPNO, LASTNAME, PROJNO
FROM CORPDATA.EMPLOYEE, CORPDATA.PROJECT
WHERE EMPNO = RESPEMP
AND LASTNAME > 'S'

This query returns the same output as the previous example.

Left Outer Join

A left outer join will return all the rows that an inner join returns plus one row for
each of the other rows in the first table that did not have a match in the second
table.

Suppose you want to find all employees and the projects they are currently
responsible for. You want to see those employees that are not currently in charge of

76 DB2 UDB for AS/400 SQL Programming V4R4

 a project as well. The following query will return a list of all employees whose
names are greater than ’S’, along with their assigned project numbers.
SELECT EMPNO, LASTNAME, PROJNO
FROM CORPDATA.EMPLOYEE LEFT OUTER JOIN CORPDATA.PROJECT
ON EMPNO = RESPEMP
WHERE LASTNAME > 'S'

The result of this query contains some employees that do not have a project
number. They are listed in the query, but have the null value returned for their
project number.

EMPNO LASTNAME PROJNO

000020 THOMPSON PL2100
000060 STERN MA2110
000100 SPENSER OP2010
000170 YOSHIMURA -
000180 SCOUTTEN -
000190 WALKER -
000250 SMITH AD3112
000280 SCHNEIDER -
000300 SMITH -
000310 SETRIGHT -

Notes
Using the RRN scalar function to return the relative record number for a column in
the table on the right in a left outer join or exception join will return a value of 0 for
the unmatched rows.

Exception Join
An exception join returns only the records from the first table that do NOT have a
match in the second table. Using the same tables as before, return those
employees that are not responsible for any projects.
SELECT EMPNO, LASTNAME, PROJNO
FROM CORPDATA.EMPLOYEE EXCEPTION JOIN CORPDATA.PROJECT
ON EMPNO = RESPEMP
WHERE LASTNAME > 'S'

This join returns the output:

EMPNO LASTNAME PROJNO

000170 YOSHIMURA -
000180 SCOUTTEN -
000190 WALKER -
000280 SCHNEIDER -
000300 SMITH -
000310 SETRIGHT -

An exception join can also be written as a subquery using the NOT EXISTS
predicate. The previous query could be rewritten in the following way:

Chapter 5. Advanced Coding Techniques 77

 SELECT EMPNO, LASTNAME
FROM CORPDATA.EMPLOYEE
WHERE LASTNAME > 'S'
AND NOT EXISTS
(SELECT * FROM CORPDATA.PROJECT
WHERE EMPNO = RESPEMP)

The only difference in this query is that it cannot return values from the PROJECT
table.

Cross Join
A cross join (or Cartesian Product join) will return a result table where each row
from the first table is combined with each row from the second table. The number of
rows in the result table is the product of the number of rows in each table. If the
tables involved are large, this join can take a very long time.

A cross join can be specified in two ways: using the JOIN syntax or by listing the
tables in the FROM clause separated by commas without using a WHERE clause
to supply join criteria.

Suppose the following tables exist.

Table 13. Table A
T1COL1 T1COL2
A1 AA1
A2 AA2
A3 AA3

Table 14. Table B

T2COL1 T2COL2
B1 BB1
B2 BB2

The following two select statements produce identical results.

SELECT * FROM A CROSS JOIN B
SELECT * FROM A, B

The result table for either of these select statements looks like this:

T1COL1 T1COL2 T2COL1 T2COL2

A1 AA1 B1 BB1
A1 AA1 B2 BB2
A2 AA2 B1 BB1
A2 AA2 B2 BB2
A3 AA3 B1 BB1
A3 AA3 B2 BB2

Using multiple join types in one statement

There are times when more than two tables need to be joined to produce the
desired result. If you wanted to return all the employees, their department name,
and the project they are responsible for, if any, the EMPLOYEE table,

78 DB2 UDB for AS/400 SQL Programming V4R4

 DEPARTMENT table, and PROJECT table would all need to be joined to get the
information. The following example shows the query and the results.
SELECT EMPNO, LASTNAME, DEPTNAME, PROJNO
FROM CORPDATA.EMPLOYEE INNER JOIN CORPDATA.DEPARTMENT
ON WORKDEPT = DEPTNO
LEFT OUTER JOIN CORPDATA.PROJECT
ON EMPNO = RESPEMP
WHERE LASTNAME > 'S'

EMPNO LASTNAME DEPTNAME PROJNO

000020 THOMPSON PLANNING PL2100
000060 STERN MANUFACTURING SYSTEMS MA2110
000100 SPENSER SOFTWARE SUPPORT OP2010
000170 YOSHIMURA MANUFACTURING SYSTEMS -
000180 SCOUTTEN MANUFACTURING SYSTEMS -
000190 WALKER MANUFACTURING SYSTEMS -
000250 SMITH ADMINISTRATION SYSTEMS AD3112
000280 SCHNEIDER OPERATIONS -
000300 SMITH OPERATIONS -
000310 SETRIGHT OPERATIONS -

Notes on Joins
When you join two or more tables:
v If there are common column names, you must qualify each common name with
the name of the table (or a correlation name). Column names that are unique do
not need to be qualified.
v If you do not list the column names you want, but instead use SELECT *, SQL
returns rows that consist of all the columns of the first table, followed by all the
columns of the second table, and so on.
v You must be authorized to select rows from each table or view specified in the
FROM clause.
v The sort sequence is applied to all character and UCS-2 graphic columns being
joined.
|
| Using Table Expressions
| You can use table expressions to specify an intermediate result table. They can be
| used in place of a view to avoid creating the view when general use of the view is
| not required. Table expressions consist of nested table expressions and common
| table expressions.

| Nested table expressions are specified within parentheses in the FROM clause. For
| example, suppose you want a result table that shows the manager number,
| department number, and maximum salary for each department. The manager
| number is in the DEPARTMENT table, the department number is in both the
| DEPARTMENT and EMPLOYEE tables, and the salaries are in the EMPLOYEE
| table. You can use a table expression in the from clause to select the maximum
| salary for each department. You add a correlation name, T2, following the nested
| table expression to name the derived table. The outer select then uses T2 to qualify
| columns that are selected from the derived table, in this case MAXSAL and

Chapter 5. Advanced Coding Techniques 79

| WORKDEPT. Note that the MAX(SALARY) column selected in the nested table
| expression must be named in order to be referenced in the outer select. The AS
| clause is used to do that.
| SELECT MGRNO, T1.DEPTNO, MAXSAL
| FROM CORPDATA.DEPARTMENT T1,
| (SELECT MAX(SALARY) AS MAXSAL, WORKDEPT
| FROM CORPDATA.EMPLOYEE E1
| GROUP BY WORKDEPT) T2
| WHERE T1.DEPTNO = T2.WORKDEPT
| ORDER BY DEPTNO

| The result of the query is:

| MGRNO DEPTNO MAXSAL
| 000010 A00 52,750.00
| 000020 B01 41,250.00
| 000030 C01 38,250.00
| 000060 D11 32,250.00
| 000070 D21 36,170.00
| 000050 E01 40,175.00
| 000090 E11 29,750.00
| 000100 E21 26,150.00

| Common table expressions can be specified prior to the full-select in a SELECT

| statement, an INSERT statement, or a CREATE VIEW statement. They can be
| used when the same result table needs to be shared in a full-select. Common table
| expressions are preceeded with the keyword WITH.

| For example, suppose you want a table that shows the minimum and maximum of
| the average salary of a certain set of departments. The first character of the
| department number has some meaning and you want to get the minimum and
| maximum for those departments that start with the letter ’D’ and those that start
| with the letter ’E’. You can use a common table expression to select the average
| salary for each department. Again, you must name the derived table; in this case,
| the name is DT. You can then specify a SELECT statement using a WHERE clause
| to restrict the selection to only the departments that begin with a certain letter.
| Specify the minimum and maximum of column AVGSAL from the derived table DT.
| Specify a UNION to get the results for the letter ’E’ and the results for the letter ’D’.
| WITH DT AS (SELECT E.WORKDEPT AS DEPTNO, AVG(SALARY) AS AVGSAL
| FROM CORPDATA.DEPARTMENT D , CORPDATA.EMPLOYEE E
| WHERE D.DEPTNO = E.WORKDEPT
| GROUP BY E.WORKDEPT)
| SELECT 'E', MAX(AVGSAL), MIN(AVGSAL) FROM DT
| WHERE DEPTNO LIKE 'E%'
| UNION
| SELECT 'D', MAX(AVGSAL), MIN(AVGSAL) FROM DT
| WHERE DEPTNO LIKE 'D%'

| The result of the query is:

| MAX ( AVGSAL ) MIN ( AVGSAL )
| E 40,175.00 20,998.00
| D 25,153.33 24,677.77
|
Using the UNION Keyword to Combine Subselects
Using the UNION keyword, you can combine two or more subselects to form a
single select-statement. When SQL encounters the UNION keyword, it processes
each subselect to form an interim result table, then it combines the interim result
table of each subselect and deletes duplicate rows to form a combined result table.

80 DB2 UDB for AS/400 SQL Programming V4R4

You use UNION to merge lists of values from two or more tables. You can use any
of the clauses and techniques you have learned so far when coding
select-statements, including ORDER BY.

You can use UNION to eliminate duplicates when merging lists of values obtained
from several tables. For example, you can obtain a combined list of employee
numbers that includes:
v People in department D11
v People whose assignments include projects MA2112, MA2113, and AD3111

The combined list is derived from two tables and contains no duplicates. To do this,
specify:
MOVE 'D11' TO WORK-DEPT.
...
EXEC SQL
DECLARE XMP6 CURSOR FOR
SELECT EMPNO
FROM CORPDATA.EMPLOYEE
WHERE WORKDEPT = :WORK-DEPT
UNION
SELECT EMPNO
FROM CORPDATA.EMP_ACT
WHERE PROJNO = 'MA2112' OR
PROJNO = 'MA2113' OR
PROJNO = 'AD3111'
ORDER BY EMPNO
END-EXEC.
...
EXEC SQL
FETCH XMP6
INTO :EMP-NUMBER
END-EXEC.

To better understand what results from these SQL statements, imagine that SQL
goes through the following process:

Chapter 5. Advanced Coding Techniques 81

 Step 1: SQL processes the Which results in an interim
first SELECT statement: result table:
(from CORPDATA.EMPLOYEE)
000060
000150
000160
000170
...

Step 2: SQL processes the Which results in another

second SELECT statement: interim result table:

(from CORPDATA.EMP ACT)

000230
000230
000230
...

Which results in a combined

Step 3: SQL combines the result table with values in
two interim result tables: ascending sequence:

fetch EMP-NUMBER
1 000060
2 000150
3 000160
4 000170
5 000180
... ...

RV3W186-0

When you use UNION:

v Any ORDER BY clause must appear after the last subselect that is part of the
union. In this example, the results are sequenced on the basis of the first
selected column, EMPNO. The ORDER BY clause specifies that the combined
result table is to be in collated sequence.
v A name may be specified on the ORDER BY clause if the result columns are
named. A result column is named if the corresponding columns in each of the
unioned select-statements have the same name. An AS clause can be used to
assign a name to columns in the select list.
SELECT A + B AS X ...
UNION SELECT X ... ORDER BY X

If the result columns are unnamed, use numbers to order the result. The number
refers to the position of the expression in the list of expressions you include in
your subselects.
SELECT A + B ...
UNION SELECT X ... ORDER BY 1

You cannot use UNION when creating a view.

82 DB2 UDB for AS/400 SQL Programming V4R4

 To identify which subselect each row is from, you can include a constant at the end
of the select list of each subselect in the union. When SQL returns your results, the
last column contains the constant for the subselect that is the source of that row.
For example, you can specify:
SELECT A, B, 'A1' ... UNION SELECT X, Y, 'B2'

When a row is presented to your program, it includes a value (either A1 or B2) to

indicate the table that is the source of the row’s values. If the column names in the
union are different, SQL uses the set of column names specified in the first
subselect when interactive SQL displays or prints the results, or in the SQLDA
resulting from processing an SQL DESCRIBE statement.

For information on compatibility of the length and data type for columns in a
UNION, see chapter 4 of the DB2 UDB for AS/400 SQL Reference book.

Note: Sort sequence is applied after the fields across the UNION pieces are made
compatible. The sort sequence is used for the distinct processing that
implicitly occurs during UNION processing.

Specifying UNION ALL

If you want to keep duplicates in the result of a UNION, specify UNION ALL instead
of just UNION.

Step 3. SQL combines two Resulting in a result table that

interim result tables: includes duplicates:

fetch EMP-NUMBER
1 000060
2 000150
3 000150
4 000150
5 000160
6 000160
7 000170
8 000170
... ...

RV3W187-0

v The UNION ALL operation is associative, for example:

(SELECT PROJNO FROM CORPDATA.PROJECT
UNION ALL
SELECT PROJNO FROM CORPDATA.PROJECT)
UNION ALL
SELECT PROJNO FROM CORPDATA.EMP_ACT

gives the same result as:

SELECT PROJNO FROM CORPDATA.PROJECT
UNION ALL
(SELECT PROJNO FROM CORPDATA.PROJECT
UNION ALL
SELECT PROJNO FROM CORPDATA.EMP_ACT)
v When you include the UNION ALL in the same SQL statement as a UNION
operator, however, the result of the operation depends on the order of evaluation.

Chapter 5. Advanced Coding Techniques 83

 Where there are no parentheses, evaluation is from left to right. Where
parentheses are included, the parenthesized subselect is evaluated first,
followed, from left to right, by the other parts of the statement.

Using Subqueries
In the WHERE and HAVING clauses you have seen so far, you specified a search
condition by using a literal value, a column name, an expression, or the registers. In
those search conditions, you know that you are searching for a specific value, but
sometimes you cannot supply that value until you have retrieved other data from a
table. For example, suppose you want a list of the employee numbers, names, and
job codes of all employees working on a particular project, say project number
MA2100. The first part of the statement is easy to write:
DECLARE XMP CURSOR FOR
SELECT EMPNO, LASTNAME, JOB
FROM CORPDATA.EMPLOYEE
WHERE EMPNO ...

But you cannot go further because the CORPDATA.EMPLOYEE table does not
include project number data. You do not know which employees are working on
project MA2100 without issuing another SELECT statement against the
CORPDATA.EMP_ACT table.

With SQL, you can nest one SELECT statement within another to solve this
problem. The inner SELECT statement is called a subquery. The SELECT
statement surrounding the subquery is called the outer-level SELECT. Using a
subquery, you could issue just one SQL statement to retrieve the employee
numbers, names, and job codes for employees who work on project MA2100:
DECLARE XMP CURSOR FOR
SELECT EMPNO, LASTNAME, JOB
FROM CORPDATA.EMPLOYEE
WHERE EMPNO IN
(SELECT EMPNO
FROM CORPDATA.EMP_ACT
WHERE PROJNO = 'MA2100')

To better understand what will result from this SQL statement, imagine that SQL
goes through the following process:

84 DB2 UDB for AS/400 SQL Programming V4R4

 Step 1: SQL evaluates the Which results in an interim
subquery to obtain a list results table:
of EMPNO values:

(from CORPDATA.EMP ACT)

000010
000110

Step 2: The interim results The final result table looks like this:
table then serves as a list
in the search condition of
the outer-level SELECT.
Essentially, this is what is
executed.

fetch EMPNO LASTNAME JOB

1 000010 HAAS PRES
2 000110 LUCCHESSI SALESREP

RV2W559-2

Correlation
The purpose of a subquery is to supply information needed to qualify a row
(WHERE clause) or a group of rows (HAVING clause). This is done through the
result table that the subquery produces. Conceptually, the subquery is evaluated
whenever a new row or group of rows must be qualified. In fact, if the subquery is
the same for every row or group, it is evaluated only once. For example, the
previous subquery has the same content for every row of the table
CORPDATA.EMPLOYEE. Subqueries like this are said to be uncorrelated.

Some subqueries vary in content from row to row or group to group. The
mechanism that allows this is called correlation, and the subqueries are said to be
correlated. More information on correlated subqueries can be found in “Correlated
Subqueries” on page 88. Even so, what is said before that point applies equally to
correlated and uncorrelated subqueries.

Subqueries and Search Conditions

A subquery is always part of a search condition. The search condition is in the form
operand operator (subquery). In the example, the operand is EMPNO and
operator is IN. The search condition can be part of a WHERE or HAVING clause.
The clause can include more than one search condition that contains a subquery. A
search condition containing a subquery, like any other search condition, can be
enclosed in parentheses, can be preceded by the keyword NOT, and can be linked

Chapter 5. Advanced Coding Techniques 85

 to other search conditions through the keywords AND and OR. For example, the
WHERE clause of some query could look something like this:
WHERE X IN (subquery1) AND (Y > SOME (subquery2) OR Z = 100)

Subqueries can also appear in the search conditions of other subqueries. Such
subqueries are said to be nested at some level of nesting. For example, a
subquery within a subquery within an outer-level SELECT is nested at a nesting
level of two. SQL allows nesting down to a nesting level of 32, but few queries
require a nesting level greater than 1.

How Subqueries Are Used

There are four ways to include a subquery in either a WHERE or HAVING clause:

Basic Comparisons
You can use a subquery immediately after any of the comparison operators. If you
do, the subquery can return at most one value. The value can be the result of a
column function or an arithmetic expression. SQL then compares the value that
results from the subquery with the value to the left of the comparison operator. For
example, suppose you want to find the employee numbers, names, and salaries for
employees whose education level is higher than the average education level
throughout the company.
DECLARE XMP CURSOR FOR
SELECT EMPNO, LASTNAME, SALARY
FROM CORPDATA.EMPLOYEE
WHERE EDLEVEL >
(SELECT AVG(EDLEVEL)
FROM CORPDATA.EMPLOYEE)

SQL first evaluates the subquery and then substitutes the result in the WHERE
clause of the SELECT statement. In this example, the result is (as it should be) the
company-wide average educational level. Besides returning a single value, a
subquery could return no value at all. If it does, the result of the compare is
unknown. Consider, for example, the first query shown in this section, and assume
that there are not any employees currently working on project MA2100. Then the
subquery would return no value, and the search condition would be unknown for
every row. In this case, then, the result produced by the query would be an empty
table.

Quantified Comparisons (ALL, ANY, and SOME)

You can use a subquery after a comparison operator followed by the keyword ALL,
ANY, or SOME. When used in this way, the subquery can return zero, one, or many
values, including null values. You can use ALL, ANY, and SOME in the following
ways:
v Use ALL to indicate that the value you supplied must compare in the indicated
way to ALL the values the subquery returns. For example, suppose you use the
greater-than comparison operator with ALL:
... WHERE expression > ALL (subquery)

To satisfy this WHERE clause, the value in the expression must be greater than
all the values (that is, greater than the highest value) returned by the subquery. If
the subquery returns an empty set (that is, no values were selected), the
condition is satisfied.

86 DB2 UDB for AS/400 SQL Programming V4R4

v Use ANY or SOME to indicate that the value you supplied must compare in the
indicated way to at least one of the values the subquery returns. For example,
suppose you use the greater-than comparison operator with ANY:
... WHERE expression > ANY (subquery)

To satisfy this WHERE clause, the value in the expression must be greater than
at least one of the values (that is, greater than the lowest value) returned by the
subquery. If what the subquery returns is empty, the condition is not satisfied.

Note: The results when a subquery returns one or more null values may surprise
you, unless you are familiar with formal logic. For applicable rules, read the
discussion of quantified predicates in the DB2 UDB for AS/400 SQL
Reference .

Using the IN Keyword

You can use IN to say that the value in the expression must be among the values
returned by the subquery. The first example in this chapter illustrates this type of
usage. Using IN is equivalent to using =ANY or =SOME. Using ANY and SOME
were previously described. You could also use the IN keyword with the NOT
keyword in order to select rows when the value is not among the values returned by
the subquery. For example, you could use:
... WHERE WORKDEPT NOT IN (SELECT ...)

Using the EXISTS Keyword

In the subqueries presented so far, SQL evaluates the subquery and uses the result
as part of the WHERE clause of the outer-level SELECT. In contrast, when you use
the keyword EXISTS, SQL simply checks whether the subquery returns one or
more rows. If it does, the condition is satisfied. If it does not (if it returns no rows),
the condition is not satisfied. For example:
DECLARE XMP CURSOR FOR
SELECT EMPNO,LASTNAME
FROM CORPDATA.EMPLOYEE
WHERE EXISTS
(SELECT *
FROM CORPDATA.PROJECT
WHERE PRSTDATE > '1982-01-01');

In the example, the search condition holds if any project represented in the
CORPDATA.PROJECT table has an estimated start date that is later than January
1, 1982. Please note that this example does not show the full power of EXISTS,
because the result is always the same for every row examined for the outer-level
SELECT. As a consequence, either every row appears in the results, or none
appear. In a more powerful example, the subquery itself would be correlated, and
would change from row to row. See “Correlated Subqueries” on page 88 for more
information on correlated subqueries.

As shown in the example, you do not need to specify column names in the
subquery of an EXISTS clause. Instead, you can code SELECT *.

You could also use the EXISTS keyword with the NOT keyword in order to select
rows when the data or condition you specify does not exist. That is, you could use:
... WHERE NOT EXISTS (SELECT ...)

For all general types of usage for subqueries but one (using a subquery with the
EXISTS keyword), the subquery must produce a one-column result table. This

Chapter 5. Advanced Coding Techniques 87

 means that the SELECT clause in a subquery must name a single column, or
contain a single expression. For example, both of the following SELECT clauses
would be allowed for all four usage types:
SELECT AVG(SALARY)
SELECT EMPNO

The result table produced by a subquery can have zero or more rows. For some
usages, no more than one row is allowed.

Using Subqueries with UPDATE and DELETE

In the examples shown so far, you have seen subqueries within SELECT
statements. You can also use subqueries in the WHERE clause of the UPDATE or
DELETE statements or in the SET clause of an UPDATE. For the most part, this is
not very different from using subqueries with outer-level SELECTs.

Notes on Using Subqueries

| 1. When nesting SELECT statements, you can use as many as you need to satisfy
| your requirements (1 to 31 subqueries), although performance is slower for
| each additional subquery. A maximum of 128 tables can be specified in an SQL
| statement.
2. When the outer statement is a SELECT statement (at any level of nesting):
v The subquery can be based on the same table or view as the outer
statement, or on a different table or view.
v You can use a subquery in the WHERE clause of the outer-level SELECT,
even when the outer-level SELECT is part of a DECLARE CURSOR,
CREATE VIEW, or INSERT statement.
v You can use a subquery in the HAVING clause of a SELECT statement.
When you do, SQL evaluates the subquery and uses it to qualify each group.
3. When the statement is an UPDATE or DELETE statement, you can use
subqueries in the WHERE clause of the UPDATE or DELETE statement.
4. When a subquery is used in the SET clause of an UPDATE statement, the
result table of a subselect must contain the same number of values as the
corresponding list of columns to be updated. In all other cases, the result table
for a subquery must consist of a single column, unless the subquery is being
used with the EXISTS keyword. The number of rows in this table can vary from
zero to many, but for comparisons not involving the keywords ALL, ANY, or
SOME, the number of rows must be zero or one.
5. A subquery cannot include the ORDER BY, UNION, UNION ALL, FOR READ
ONLY, UPDATE, or OPTIMIZE clauses.
6. In any subquery, as in any search condition, the values compared must be
compatible.
7. Using a column function or an arithmetic expression with a column name in a
subquery does not make it incompatible. The data type of the column does not
change after SQL applies a column function or arithmetic operator.

Correlated Subqueries
In the subqueries previously discussed, SQL evaluates the subquery once,
substitutes the result of the subquery in the right side of the search condition, and
evaluates the outer-level SELECT based on the value of the search condition. You
can also write a subquery that SQL may have to re-evaluate as it examines each

88 DB2 UDB for AS/400 SQL Programming V4R4

new row (WHERE clause) or group of rows (HAVING clause) in the outer-level
SELECT. This is called a correlated subquery.

Example of a Correlated Subquery in a WHERE Clause

Suppose that you want a list of all the employees whose education levels are
higher than the average education levels in their respective departments. To get this
information, SQL must search the CORPDATA.EMPLOYEE table. For each
employee in the table, SQL needs to compare the employee’s education level to the
average education level for the employee’s department. In the subquery, you tell
SQL to calculate the average education level for the department number in the
current row. For example:
DECLARE XMP CURSOR FOR
SELECT EMPNO, LASTNAME, WORKDEPT, EDLEVEL
FROM CORPDATA.EMPLOYEE X
WHERE EDLEVEL >
(SELECT AVG(EDLEVEL)
FROM CORPDATA.EMPLOYEE
WHERE WORKDEPT = X.WORKDEPT)

A correlated subquery looks like an uncorrelated one, except for the presence of
one or more correlated references. In the example, the single correlated reference
is the occurrence of X.WORKDEPT in the subselect’s FROM clause. Here, the
qualifier X is the correlation name defined in the FROM clause of the outer SELECT
statement. In that clause, X is introduced as the correlation name of the table
CORPDATA.EMPLOYEE.

Now, consider what happens when the subquery is executed for a given row of
CORPDATA.EMPLOYEE. Before it is executed, the occurrence of X.WORKDEPT is
replaced with the value of the WORKDEPT column for that row. Suppose, for
example, that the row is for CHRISTINE I HAAS. Her work department is A00,
which is the value of WORKDEPT for this row. The subquery executed for this row
is:
(SELECT AVG(EDLEVEL)
FROM CORPDATA.EMPLOYEE
WHERE WORKDEPT = 'A00')

Thus, for the row considered, the subquery produces the average education level of
Christine’s department. This is then compared in the outer statement to Christine’s
own education level. For some other row for which WORKDEPT has a different
value, that value appears in the subquery in place of A00. For example, for the row
for MICHAEL L THOMPSON, this value would be B01, and the subquery for his row
would deliver the average education level for department B01.

The result table produced by the query would have the following values:

Chapter 5. Advanced Coding Techniques 89

 (from CORPDATA.EMPLOYEE)

fetch EMPNO LASTNAME WORKDEPT EDLEVEL

1 000010 HAAS A00 18

2 000030 KWAN C01 20
3 000070 PULASKI D21 16
4 000090 HENDERSON E11 16

RV2W560-3

Example of a Correlated Subquery in a HAVING Clause

Suppose that you want a list of all the departments whose average salary is higher
than the average salary of their area (all departments whose WORKDEPT begins
with the same letter belong to the same area). To get this information, SQL must
search the CORPDATA.EMPLOYEE table. For each department in the table, SQL
compares the department’s average salary to the average salary of the area. In the
subquery, SQL calculates the average salary for the area of the department in the
current group. For example:
DECLARE XMP CURSOR FOR
SELECT WORKDEPT, DECIMAL(AVG(SALARY),8,2)
FROM CORPDATA.EMPLOYEE X
GROUP BY WORKDEPT
HAVING AVG(SALARY) >
(SELECT AVG(SALARY)
FROM CORPDATA.EMPLOYEE
WHERE SUBSTR(X.WORKDEPT,1,1) = SUBSTR(WORKDEPT,1,1))

Consider what happens when the subquery is executed for a given department of
CORPDATA.EMPLOYEE. Before it is executed, the occurrence of X.WORKDEPT is
replaced with the value of the WORKDEPT column for that group. Suppose, for
example, that the first group selected has A00 for the value of WORKDEPT. The
subquery executed for this group is:
(SELECT AVG(SALARY)
FROM CORPDATA.EMPLOYEE
WHERE SUBSTR('A00',1,1) = SUBSTR(WORKDEPT,1,1))

Thus, for the group considered, the subquery produces the average salary for the
area. This is then compared in the outer statement to the average salary for
department 'A00'. For some other group for which WORKDEPT is ’B01’, the
subquery would result in the average salary for the area where department B01
belongs.

The result table produced by the query would have the following values:

90 DB2 UDB for AS/400 SQL Programming V4R4

 (from CORPDATA.EMPLOYEE)
AVG
fetch WORKDEPT SALARY
1 D21 25153.33
2 E01 40175.00
RV2W561-3

Correlated Names and References

A correlated reference can appear only in a search condition in a subquery. The
reference is always of the form X.C, where X is a correlation name and C is the
name of a column in the table that X represents. In the preceding example, for
instance, X represents the table CORPDATA.EMPLOYEE, and C identifies the
column WORKDEPT in this table.

The correlation name is defined in the FROM clause of some query. This query
could be the outer-level SELECT, or any of the subqueries that contain the one with
the reference. Suppose, for example, that a query contains subqueries A, B, and C,
and that A contains B and B contains C. Then a correlation name used in C could
be defined in B, A, or the outer-level SELECT.

You can define a correlation name for each table name appearing in a FROM
clause. Simply include the correlation names after the table names. Leave one or
more blanks between a table name and its correlation name, and place a comma
after the correlation name if it is followed by another table name. The following
FROM clause, for example, defines the correlation names TA and TB for the tables
TABLEA and TABLEB, and no correlation name for the table TABLEC.
FROM TABLEA TA, TABLEC, TABLEB TB

Any number of correlated references can appear in a subquery. There are no

restrictions on variety. For example, one correlated name in a reference could be
defined in the outer-level SELECT, while another could be defined in a containing
subquery.

Before the subquery is executed, a value from the referenced column is always
substituted for the correlated reference. The value is determined as follows:

Note: Use D to designate the query in which the correlation name is defined. Then
the subquery is either in the WHERE clause of D, or in its HAVING clause.
v If the subquery is in the WHERE clause, its results are used by D to qualify a
row. The substituted value is then taken from this row. This is the case for the
example, where the defining query is the outer one and the subquery appears in
the outer query’s WHERE clause.
v If the subquery is in the HAVING clause, its results are used by D to qualify a
group of rows. The substituted value is then taken from this group. Note that in
this case, the column specified must be identified in the GROUP BY clause in D.
If it is not, the specified column could have more than one value for the group.

Using Correlated Subqueries in an UPDATE Statement

When you use a correlated subquery in an UPDATE statement, the correlation
name refers to the rows you are interested in updating. For example, when all

Chapter 5. Advanced Coding Techniques 91

 activities of a project must be completed before September 1983, your department
considers that project to be a priority project. You could use the SQL statement
below to evaluate the projects in the CORPDATA.PROJECT table, and write a 1 (a
flag to indicate PRIORITY) in the PRIORITY column (a column you added to
CORPDATA.PROJECT for this purpose) for each priority project.
UPDATE CORPDATA.PROJECT X
SET PRIORITY = 1
WHERE '1983-09-01' >
(SELECT MAX(EMENDATE)
FROM CORPDATA.EMP_ACT
WHERE PROJNO = X.PROJNO)

As SQL examines each row in the CORPDATA.EMP_ACT table, it determines the

maximum activity end date (EMENDATE) for all activities of the project (from the
CORPDATA.PROJECT table). If the end date of each activity associated with the
project is prior to September 1983, the current row in the CORPDATA.PROJECT
table qualifies and is updated.

Using Correlated Subqueries in a DELETE Statement

When you use a correlated subquery in a DELETE statement, the correlation name
represents the row you delete. SQL evaluates the correlated subquery once for
each row in the table named in the DELETE statement to decide whether or not to
delete the row.

Suppose a row in the CORPDATA.PROJECT table was deleted. Rows related to

the deleted project in the CORPDATA.EMP_ACT table must also be deleted. To do
this, you can use:
DELETE FROM CORPDATA.EMP_ACT X
WHERE NOT EXISTS
(SELECT *
FROM CORPDATA.PROJECT
WHERE PROJNO = X.PROJNO)

SQL determines, for each row in the CORPDATA.EMP_ACT table, whether a row
with the same project number exists in the CORPDATA.PROJECT table. If not, the
CORPDATA.EMP_ACT row is deleted.

Notes on Using Correlated Subqueries

v The correlation name is separated from its associated table name with a space.
To specify another table name, precede the table name with a comma, for
example:
FROM CORPDATA.EMPLOYEE X, CORPDATA.PROJECT ....
v The correlated subquery and the outer-level statement can refer to the same
table or to different tables.
v In an INSERT statement, neither the correlated subquery nor an outer-level
SELECT within the INSERT statement can be based on the same table into
which you are inserting.
v The outer-level SELECT that defines the correlation name can join two or more
tables.
v You can use correlated subqueries in HAVING clauses. When you do, SQL
evaluates the subquery, once per group, of the outer-level SELECT. The column
you refer to in the HAVING clause must specify a property of each group (for
example, WORKDEPT) either the columns you grouped the rows by or another
column with one of the column functions.

92 DB2 UDB for AS/400 SQL Programming V4R4

 v You can nest correlated subqueries.

Changing a Table Definition

| Changing the definition of a table allows you to add new columns, change an
| existing column definition (change its length, default value, and so on), drop existing
| columns, and add and remove constraints. You can change the definition of a table
| using Operations Navigator. Or, use the SQL ALTER TABLE statement.

You can add, change, or drop columns and add or remove constraints all with one
ALTER TABLE statement. However, a single column can be referenced only once in
the ADD COLUMN, ALTER COLUMN, and DROP COLUMN clauses. That is, you
cannot add a column and then alter that column in the same ALTER TABLE
statement.

Adding a column
| You can add a column to a table using Operations Navigator. Or use the ADD
| COLUMN clause of the SQL ALTER TABLE statement.

When you add a new column to a table, the column is initialized with its default
value for all existing rows. If NOT NULL is specified, a default value must also be
specified.

| The altered table may consist of up to 8000 columns. The sum of the byte counts of
| the columns must not be greater than 32766 or, if a VARCHAR or VARGRAPHIC
| column is specified, 32740. If a LOB column is specified, the sum of record data
| byte counts of the columns must not be greater than 15 728 640.

Changing a column
| You can change a column in a table using Operations Navigator. Or, you can use
| the ALTER COLUMN clause of the ALTER TABLE statement. When you change the
| data type of an existing column, the old and new attributes must be compatible.
| “Allowable Conversions” shows the conversions with compatible attributes.

| When you convert to a data type with a longer length, data will be padded with the
| appropriate pad character. When you convert to a data type with a shorter length,
| data may be lost due to truncation. An inquiry message prompts you to confirm the
| request.

| If you have a column that does not allow the null value and you want to change it to
| now allow the null value, use the DROP NOT NULL clause. If you have a column
| that allows the null value and you want to prevent the use of null values, use the
| SET NOT NULL clause. If any of the existing values in that column are the null
| value, the ALTER TABLE will not be performed and an SQLCODE of -190 will
| result.

Allowable Conversions
Table 15. Allowable Conversions
FROM data type TO data type
Decimal Numeric

Chapter 5. Advanced Coding Techniques 93

 Table 15. Allowable Conversions (continued)
FROM data type TO data type
Decimal Integer, Smallint
Decimal Float
Numeric Decimal
Numeric Integer, Smallint
Numeric Float
Integer, Smallint Decimal
Integer, Smallint Numeric
Integer, Smallint Float
Float Numeric
Float Integer, Smallint
Character DBCS-open
Character UCS-2 graphic
DBCS-open Character
DBCS-open UCS-2 graphic
DBCS-either Character
DBCS-either DBCS-open
DBCS-either UCS-2 graphic
DBCS-only DBCS-open
DBCS-only DBCS graphic
DBCS-only UCS-2 graphic
DBCS graphic UCS-2 graphic
UCS-2 graphic Character
UCS-2 graphic DBCS-open
UCS-2 graphic DBCS graphic
| distinct type source type
| source type distinct type

| When modifying an existing column, only the attributes that you specify will be
| changed. All other attributes will remain unchanged. For example, given the
| following table definition:
| CREATE TABLE EX1 (COL1 CHAR(10) DEFAULT 'COL1',
| COL2 VARCHAR(20) ALLOCATE(10) CCSID 937,
| COL3 VARGRAPHIC(20) ALLOCATE(10)
| NOT NULL WITH DEFAULT)

| After running the following ALTER TABLE statement:

ALTER TABLE EX1 ALTER COLUMN COL2 SET DATA TYPE VARCHAR(30)
ALTER COLUMN COL3 DROP NOT NULL

COL2 would still have an allocated length of 10 and CCSID 937, and COL3 would
still have an allocated length of 10.

94 DB2 UDB for AS/400 SQL Programming V4R4

 Deleting a column
| You can delete a column using Operations Navigator. Or you can delete a column
| using the DROP COLUMN clause of the ALTER TABLE statement.

Dropping a column deletes that column from the table definition. If CASCADE is
specified, any views, indexes, and constraints dependent on that column will also
be dropped. If RESTRICT is specified, and any views, indexes, or constraints are
dependent on the column, the column will not be dropped and SQLCODE of -196
will be issued.

Order of operations for ALTER TABLE statement

| An ALTER TABLE statement is performed as a set of steps as follows:
| 1. Drop constraints
| 2. Drop columns for which the RESTRICT option was specified
| 3. Alter column definitions (this includes adding columns and dropping columns for
| which the CASCADE option was specified)
| 4. Add constraints

| Within each of these steps, the order in which you specify the clauses is the order
in which they are performed, with one exception. If any columns are being dropped,
that operation is logically done before any column definitions are added or altered,
in case record length is increased as a result of the ALTER TABLE statement.

Creating and Using Views

A view can be used to access part of the data in one or more tables. You can
define the columns of the view in the SELECT clause and the tables the view is
based on in the FROM clause. To define the rows in the view, you can specify a
WHERE clause, a GROUP by clause, or a HAVING clause.

For example, to create a view that selects only the last name and the department of
all the managers, specify:
CREATE VIEW CORPDATA.EMP_MANAGERS AS
SELECT LASTNAME, WORKDEPT FROM CORPDATA.EMPLOYEE
WHERE JOB = 'MANAGER'

If the select list contains elements other than columns such as expressions,
functions, constants, or special registers, and the AS clause was not used to name
the columns, a column list must be specified for the view. In the following example,
the columns of the view are LASTNAME and YEARSOFSERVICE.
CREATE VIEW CORPDATA.EMP_YEARSOFSERVICE
(LASTNAME, YEARSOFSERVICE) AS
SELECT LASTNAME, YEARS (CURRENT DATE - HIREDATE)
FROM CORPDATA.EMPLOYEE

The previous view can also be defined by using the AS clause in the select list to
name the columns in the view. For example:
CREATE VIEW CORPDATA.EMP_YEARSOFSERVICE AS
SELECT LASTNAME,
YEARS (CURRENT_DATE - HIREDATE) AS YEARSOFSERVICE
FROM CORPDATA.EMPLOYEE

Chapter 5. Advanced Coding Techniques 95

 Once you have created the view, you can use it to select the data or possibly
change the data in the base table.

The following restrictions must be considered when creating the view:

v You cannot change, insert, or delete data in a read-only view. A view is read-only
if it includes any of the following:
– The first FROM clause identifies more than one table (join).
– The first FROM clause identifies a read-only view.
– The first SELECT clause contains any of the SQL column functions (SUM,
MAX, MIN, AVG, COUNT, STDDEV, or VAR).
– The first SELECT clause specifies the keyword DISTINCT.
– The outer subselect contains a GROUP BY or HAVING clause.
– A subquery, such that the base object of the outer-most subselect and a table
of a subquery are the same table
In the above cases, you can get data from the views by means of the SQL
SELECT statement, but you cannot use statements such as INSERT,
UPDATE, or DELETE.
v You cannot insert a row in a view if:
– The table on which the view is based has a column that has no default value,
does not allow nulls, and is not in the view.
– The view has a column resulting from an expression, a constant, a function,
or a special register and the column was specified in the INSERT column list.
– The WITH CHECK OPTION was specified when the view was created and
the row does not match the selection criteria.
v You cannot update a column of a view that results from an expression, a