DB2 Universal Database for z/OS

Version 8

Application Programming and SQL Guide

SC18-7415-05

DB2 Universal Database for z/OS

Version 8

Application Programming and SQL Guide

SC18-7415-05

Note Before using this information and the product it supports, be sure to read the general information under Notices on page 1183.

Sixth Edition, Softcopy Only (February 2008) This edition applies to Version 8 of IBM DB2 Universal Database for z/OS (DB2 UDB for z/OS), product number 5625-DB2, and to any subsequent releases until otherwise indicated in new editions. Make sure you are using the correct edition for the level of the product. This softcopy version is based on the printed edition of the book and includes the changes indicated in the printed version by vertical bars. Additional changes made to this softcopy version of the book since the hardcopy book was published are indicated by the hash (#) symbol in the left-hand margin. Editorial changes that have no technical significance are not noted. This and other books in the DB2 UDB for z/OS library are periodically updated with technical changes. These updates are made available to licensees of the product on CD-ROM and on the Web (currently at www.ibm.com/software/data/db2/zos/library.html). Check these resources to ensure that you are using the most current information. Copyright International Business Machines Corporation 1983, 2008. All rights reserved. US Government Users Restricted Rights Use, duplication or disclosure restricted by GSA ADP Schedule Contract with IBM Corp.

Contents
About this book . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxi
Who should read this book . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxi Terminology and citations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxi How to read the syntax diagrams . . . . . . . . . . . . . . . . . . . . . . . . . . . xxii Accessibility . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxiii How to send your comments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxiv

Summary of changes to this book . . . . . . . . . . . . . . . . . . . . . . . xxv

Part 1. Using SQL queries . . . . . . . . . . . . . . . . . . . . . . . . . . . 1

Chapter 1. Retrieving data . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
Result tables . . . . . . . . . . . . . . . . Data types . . . . . . . . . . . . . . . . . Selecting columns: SELECT . . . . . . . . . . . Selecting all columns: SELECT * . . . . . . . . . Selecting some columns: SELECT column-name . . . Selecting derived columns: SELECT expression . . . . Eliminating duplicate rows: DISTINCT . . . . . . Naming result columns: AS . . . . . . . . . . Selecting rows using search conditions: WHERE . . . . Putting the rows in order: ORDER BY . . . . . . . . Specifying the sort key . . . . . . . . . . . Referencing derived columns . . . . . . . . . Summarizing group values: GROUP BY . . . . . . . Subjecting groups to conditions: HAVING . . . . . . Merging lists of values: UNION . . . . . . . . . Using UNION to eliminate duplicates . . . . . . Using UNION ALL to keep duplicates . . . . . . Creating common table expressions: WITH . . . . . . Using WITH instead of CREATE VIEW . . . . . . Using common table expressions with CREATE VIEW . Using common table expressions when you use INSERT Using recursive SQL . . . . . . . . . . . . Accessing DB2 data that is not in a table . . . . . . Using 15-digit and 31-digit precision for decimal numbers . Finding information in the DB2 catalog . . . . . . . Displaying a list of tables you can use . . . . . . Displaying a list of columns in a table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3 . 4 . 5 . 5 . 6 . 7 . 7 . 7 . 8 . 9 . 10 . 10 . 11 . 11 . 12 . 13 . 13 . 13 . 14 . 14 . 15 . 15 . 16 . 16 . 17 . 18 . 18

| | | | |

Chapter 2. Working with tables and modifying data . . . . . . . . . . . . . . . . 19

Working with tables . . . . . . . . . . . . . Creating your own tables: CREATE TABLE . . . . . Working with temporary tables . . . . . . . . . Dropping tables: DROP TABLE . . . . . . . . . Working with views . . . . . . . . . . . . . Defining a view: CREATE VIEW . . . . . . . . Changing data through a view . . . . . . . . . Dropping views: DROP VIEW . . . . . . . . . Modifying DB2 data . . . . . . . . . . . . . Inserting rows: INSERT . . . . . . . . . . . Selecting values as you insert: SELECT FROM INSERT . Updating current values: UPDATE . . . . . . . Deleting rows: DELETE . . . . . . . . . . .
Copyright IBM Corp. 1983, 2008

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

19 19 21 25 25 25 26 27 27 27 31 36 37

iii

Chapter 3. Joining data from more than one table . . . . . . . . . . . . . . . . . 39

Inner join . . . . . . . . . . . . . . . . . . . Full outer join . . . . . . . . . . . . . . . . . Left outer join . . . . . . . . . . . . . . . . . Right outer join . . . . . . . . . . . . . . . . . SQL rules for statements containing join operations . . . . . Using more than one join in an SQL statement . . . . . . Using nested table expressions and user-defined table functions in Using correlated references in table specifications in joins . . . . . . . . . . . . . . . joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 40 41 42 43 44 45 46 47

Chapter 4. Using subqueries . . . . . . . . . . . . . . . . . . . . . . . . . . 49

Conceptual overview . . . . . . . . . . . . . Correlated and uncorrelated subqueries . . . . . . Subqueries and predicates . . . . . . . . . . The subquery result table . . . . . . . . . . . Tables in subqueries of UPDATE, DELETE, and INSERT How to code a subquery . . . . . . . . . . . . Basic predicate . . . . . . . . . . . . . . Quantified predicate : ALL, ANY, or SOME . . . . . IN keyword . . . . . . . . . . . . . . . EXISTS keyword . . . . . . . . . . . . . Using correlated subqueries . . . . . . . . . . . An example of a correlated subquery . . . . . . . Using correlation names in references . . . . . . Using correlated subqueries in an UPDATE statement . Using correlated subqueries in a DELETE statement . . . . . . . . . . . . . . . . . . statements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49 50 50 50 51 51 51 51 52 53 53 53 54 55 56

Chapter 5. Using SPUFI to execute SQL from your workstation . . . . . . . . . . . 59

Allocating an input data set and using SPUFI . . . . Changing SPUFI defaults . . . . . . . . . . . # Changing SPUFI defaults - panel 2 . . . . . . . Entering SQL statements . . . . . . . . . . . Using the ISPF editor . . . . . . . . . . . Retrieving Unicode UTF-16 graphic data . . . . # Entering comments . . . . . . . . . . . Setting the SQL terminator character . . . . . . Controlling toleration of warnings . . . . . . # Processing SQL statements . . . . . . . . . . When SQL statements exceed resource limit thresholds . Browsing the output . . . . . . . . . . . . Format of SELECT statement results . . . . . . Content of the messages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 59 62 65 66 66 67 67 67 68 68 68 69 70 71

Part 2. Coding SQL in your host application program . . . . . . . . . . . . . 73

Chapter 6. Basics of coding SQL in an application program . . . . . . . . . . . . . 77
Conventions used in examples of coding SQL statements . . . . Delimiting an SQL statement . . . . . . . . . . . . . Declaring table and view definitions . . . . . . . . . . . Accessing data using host variables, variable arrays, and structures Using host variables . . . . . . . . . . . . . . . Using host variable arrays . . . . . . . . . . . . . Using host structures . . . . . . . . . . . . . . . Checking the execution of SQL statements . . . . . . . . . Using the SQL communication area (SQLCA) . . . . . . . SQLCODE and SQLSTATE . . . . . . . . . . . . . The WHENEVER statement . . . . . . . . . . . . . Handling arithmetic or conversion errors . . . . . . . . The GET DIAGNOSTICS statement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 78 78 79 79 80 86 90 91 91 92 93 93 94

iv

Application Programming and SQL Guide

Calling DSNTIAR to display SQLCA fields .

. 98

Chapter 7. Using a cursor to retrieve a set of rows . . . . . . . . . . . . . . . . 103

Accessing data by using a row-positioned cursor . . . . . Step 1: Declare the cursor . . . . . . . . . . . . Step 2: Open the cursor . . . . . . . . . . . . . Step 3: Specify what to do at end-of-data . . . . . . . Step 4: Execute SQL statements . . . . . . . . . . Step 5: Close the cursor . . . . . . . . . . . . . Accessing data by using a rowset-positioned cursor . . . . Step 1: Declare the rowset cursor . . . . . . . . . . Step 2: Open the rowset cursor . . . . . . . . . . Step 3: Specify what to do at end-of-data for a rowset cursor Step 4: Execute SQL statements with a rowset cursor . . . Step 5: Close the rowset cursor . . . . . . . . . . Types of cursors . . . . . . . . . . . . . . . . Scrollable and non-scrollable cursors. . . . . . . . . Held and non-held cursors . . . . . . . . . . . . Examples of using cursors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 103 103 105 105 106 108 108 108 108 109 109 113 113 113 122 124

| | | | | |

Chapter 8. Generating declarations for your tables using DCLGEN . . . . . . . . . 131

Invoking DCLGEN through DB2I . . . . . . . . . . Including the data declarations in your program . . . . . DCLGEN support of C, COBOL, and PL/I languages . . . . Example: Adding a table declaration and host-variable structure Step 1. Specify COBOL as the host language . . . . . . Step 2. Create the table declaration and host structure . . . Step 3. Examine the results . . . . . . . . . . . . . . . to . . . . . . . . . . . . a library . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 132 136 136 138 138 139 140

Chapter 9. Embedding SQL statements in host languages . . . . . . . . . . . . . 143

Coding SQL statements in an assembler application . . . . Defining the SQL communications area . . . . . . . . Defining SQL descriptor areas . . . . . . . . . . . Embedding SQL statements . . . . . . . . . . . Using host variables . . . . . . . . . . . . . . Declaring host variables . . . . . . . . . . . . . Determining equivalent SQL and assembler data types . . Determining compatibility of SQL and assembler data types. Using indicator variables . . . . . . . . . . . . Handling SQL error return codes . . . . . . . . . . Macros for assembler applications . . . . . . . . . Coding SQL statements in a C or C++ application . . . . . Defining the SQL communication area . . . . . . . . Defining SQL descriptor areas . . . . . . . . . . . Embedding SQL statements . . . . . . . . . . . Using host variables and host variable arrays. . . . . . Declaring host variables . . . . . . . . . . . . . Declaring host variable arrays . . . . . . . . . . . Using host structures . . . . . . . . . . . . . . Determining equivalent SQL and C data types . . . . . Determining compatibility of SQL and C data types . . . Using indicator variables and indicator variable arrays . . Handling SQL error return codes . . . . . . . . . . Coding considerations for C and C++ . . . . . . . . Coding SQL statements in a COBOL application. . . . . . Defining the SQL communication area . . . . . . . . Defining SQL descriptor areas . . . . . . . . . . . Embedding SQL statements . . . . . . . . . . . Using host variables and host variable arrays. . . . . . Declaring host variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 143 143 144 145 148 148 151 155 156 157 158 158 158 159 160 161 162 168 173 175 181 182 184 186 186 186 187 187 191 192

| |

Contents

| |

Declaring host variable arrays . . . . . . . . . . . . . . Using host structures . . . . . . . . . . . . . . . . . Determining equivalent SQL and COBOL data types . . . . . . Determining compatibility of SQL and COBOL data types . . . . Using indicator variables and indicator variable arrays . . . . . Handling SQL error return codes . . . . . . . . . . . . . Coding considerations for object-oriented extensions in COBOL . . Coding SQL statements in a Fortran application . . . . . . . . . Defining the SQL communication area . . . . . . . . . . . Defining SQL descriptor areas . . . . . . . . . . . . . . Embedding SQL statements . . . . . . . . . . . . . . Using host variables . . . . . . . . . . . . . . . . . Declaring host variables . . . . . . . . . . . . . . . . Determining equivalent SQL and Fortran data types . . . . . . Determining compatibility of SQL and Fortran data types . . . . Using indicator variables . . . . . . . . . . . . . . . Handling SQL error return codes . . . . . . . . . . . . . Coding SQL statements in a PL/I application . . . . . . . . . Defining the SQL communication area . . . . . . . . . . . Defining SQL descriptor areas . . . . . . . . . . . . . . Embedding SQL statements . . . . . . . . . . . . . . Using host variables and host variable arrays. . . . . . . . . Declaring host variables . . . . . . . . . . . . . . . . Declaring host variable arrays . . . . . . . . . . . . . . Using host structures . . . . . . . . . . . . . . . . . Determining equivalent SQL and PL/I data types . . . . . . . Determining compatibility of SQL and PL/I data types . . . . . Using indicator variables and indicator variable arrays . . . . . Handling SQL error return codes . . . . . . . . . . . . . Coding SQL statements in a REXX application . . . . . . . . . Defining the SQL communication area . . . . . . . . . . . Defining SQL descriptor areas . . . . . . . . . . . . . . Accessing the DB2 REXX Language Support application programming Embedding SQL statements in a REXX procedure . . . . . . . Using cursors and statement names . . . . . . . . . . . . Using REXX host variables and data types . . . . . . . . . Using indicator variables . . . . . . . . . . . . . . . Setting the isolation level of SQL statements in a REXX procedure . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . interfaces . . . . . . . . . . . . . . . . . . . . .

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

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

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

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

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

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

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

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

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

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

199 205 210 214 216 217 219 220 220 220 221 223 223 225 227 228 229 230 230 230 231 233 234 237 240 241 245 246 247 249 249 250 250 252 253 254 257 258

Chapter 10. Using constraints to maintain data integrity . . . . . . . . . . . . . . 259

Using check constraints . . . . . . . . . . . . Check constraint considerations . . . . . . . . When check constraints are enforced . . . . . . How check constraints set CHECK-pending status . . Using referential constraints . . . . . . . . . . Parent key columns . . . . . . . . . . . . Defining a parent key and a unique index . . . . . Defining a foreign key . . . . . . . . . . . Referential constraints on tables with multilevel security Using informational referential constraints . . . . . . . . . . . . . . . . . . . . . . with . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . row-level granularity . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 259 259 260 260 261 261 262 264 266 267

| | | | | | | | | | |

Chapter 11. Using DB2-generated values as keys . . . . . . . . . . . . . . . . . 269

Using ROWID columns as keys . . Defining a ROWID column . . . Direct row access . . . . . . Using identity columns as keys . . Defining an identity column . . Parent keys and foreign keys . . Using values obtained from sequence Creating a sequence object . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . objects as keys . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 269 269 270 270 271 272 273 273

vi

Application Programming and SQL Guide

| |

Referencing a sequence object . Keys across multiple tables . .

. .

. .

. .

. .

. .

. .

. .

. .

. .

. .

. .

. .

. .

. .

. .

. .

. .

. .

. .

. .

. .

. .

. .

. .

. .

. .

. 274 . 274

Chapter 12. Using triggers for active data . . . . . . . . . . . . . . . . . . . . 277

Example of creating and using a trigger . . . . . . . . . . . . . . . . . . . Parts of a trigger . . . . . . . . . . . . . . . . . . . . . . . . . . . Trigger name . . . . . . . . . . . . . . . . . . . . . . . . . . . Subject table . . . . . . . . . . . . . . . . . . . . . . . . . . . . Trigger activation time . . . . . . . . . . . . . . . . . . . . . . . . Triggering event . . . . . . . . . . . . . . . . . . . . . . . . . . Granularity . . . . . . . . . . . . . . . . . . . . . . . . . . . . Transition variables . . . . . . . . . . . . . . . . . . . . . . . . . Transition tables . . . . . . . . . . . . . . . . . . . . . . . . . . Triggered action . . . . . . . . . . . . . . . . . . . . . . . . . . Invoking stored procedures and user-defined functions from triggers . . . . . . . . . . Passing transition tables to user-defined functions and stored procedures . . . . . . . . Trigger cascading . . . . . . . . . . . . . . . . . . . . . . . . . . . Ordering of multiple triggers . . . . . . . . . . . . . . . . . . . . . . . Interactions between triggers and referential constraints . . . . . . . . . . . . . . Interactions between triggers and tables that have multilevel security with row-level granularity Creating triggers to obtain consistent results . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 277 279 279 279 279 279 280 281 282 283 285 286 286 287 288 289 290

Part 3. Using DB2 object-relational extensions . . . . . . . . . . . . . . . . 293

Chapter 13. Introduction to DB2 object-relational extensions . . . . . . . . . . . . 295 Chapter 14. Programming for large objects
Introduction to LOBs . . . . . . . . . . . . Declaring LOB host variables and LOB locators . . . LOB materialization . . . . . . . . . . . . Using LOB locators to save storage . . . . . . . Deferring evaluation of a LOB expression to improve Indicator variables and LOB locators . . . . . Valid assignments for LOB locators . . . . . . Avoiding character conversion for LOB locators . . #

. . . . . . . . . . . . . . . . . . . 297
. . . . . . . . . . . . . . . . . . . . performance . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 297 300 305 305 306 308 309 309

Chapter 15. Creating and using user-defined functions . . . . . . . . . . . . . . 311

Overview of user-defined function definition, implementation, and invocation Example of creating and using a user-defined scalar function . . . . . User-defined function samples shipped with DB2 . . . . . . . . . Defining a user-defined function . . . . . . . . . . . . . . . . Components of a user-defined function definition . . . . . . . . . Examples of user-defined function definitions . . . . . . . . . . Implementing an external user-defined function . . . . . . . . . . . Writing a user-defined function . . . . . . . . . . . . . . . Preparing a user-defined function for execution . . . . . . . . . . Testing a user-defined function . . . . . . . . . . . . . . . Implementing an SQL scalar function . . . . . . . . . . . . . . Invoking a user-defined function . . . . . . . . . . . . . . . . Syntax for user-defined function invocation . . . . . . . . . . . Ensuring that DB2 executes the intended user-defined function. . . . . Casting of user-defined function arguments . . . . . . . . . . . What happens when a user-defined function abnormally terminates . . . Nesting SQL statements . . . . . . . . . . . . . . . . . . Recommendations for user-defined function invocation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 311 312 313 314 314 316 318 318 350 352 355 355 355 356 362 363 363 365

Chapter 16. Creating and using distinct types . . . . . . . . . . . . . . . . . . 367

Introduction to distinct types . . . . . . Using distinct types in application programs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 367 . 368

Contents

vii

Comparing distinct types . . . . . Assigning distinct types . . . . . . Using distinct types in UNIONs . . . Invoking functions with distinct types . Combining distinct types with user-defined

. . . . . . . . . . . . functions

. . . . . . . . and

. . . . . . . . LOBs

. . . . .

. . . . .

. . . . .

. . . . .

. . . . .

. . . . .

. . . . .

. . . . .

. . . . .

. . . . .

. . . . .

. . . . .

. . . . .

. . . . .

. . . . .

. . . . .

. . . . .

368 369 371 371 372

Part 4. Designing a DB2 database application . . . . . . . . . . . . . . . . 377

Chapter 17. Planning for DB2 program preparation . . . . . . . . . . . . . . . . 381
Planning to process SQL statements . . . . Planning to bind . . . . . . . . . . Binding DBRMs with packages and plans . Planning for changes to your application . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 383 384 384 386

Chapter 18. Planning for concurrency . . . . . . . . . . . . . . . . . . . . . . 393

Definitions of concurrency and locks . . . . . . . Effects of DB2 locks . . . . . . . . . . . . . Suspension . . . . . . . . . . . . . . . Timeout . . . . . . . . . . . . . . . . Deadlock . . . . . . . . . . . . . . . . Basic recommendations to promote concurrency . . . . Recommendations for database design . . . . . . Recommendations for application design . . . . . Aspects of transaction locks . . . . . . . . . . The size of a lock . . . . . . . . . . . . . The duration of a lock . . . . . . . . . . . The mode of a lock . . . . . . . . . . . . The object of a lock . . . . . . . . . . . . Options for tuning locks . . . . . . . . . . . . Bind options. . . . . . . . . . . . . . . Isolation overriding with SQL statements . . . . . The LOCK TABLE statement . . . . . . . . . Access paths . . . . . . . . . . . . . . LOB locks . . . . . . . . . . . . . . . . Relationship between transaction locks and LOB locks Hierarchy of LOB locks . . . . . . . . . . . LOB and LOB table space lock modes . . . . . . LOB lock and LOB table space lock duration . . . . Instances when LOB table space locks are not taken . The LOCK TABLE statement for LOBs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 393 394 394 394 395 397 398 399 402 402 404 404 407 408 408 421 422 424 425 425 426 426 427 428 428

Chapter 19. Planning for recovery . . . . . . . . . . . . . . . . . . . . . . . 431

Unit of work in TSO batch and online . . . . . . . . . Unit of work in CICS . . . . . . . . . . . . . . . Unit of work in IMS online programs . . . . . . . . . Planning ahead for program recovery: Checkpoint and restart When are checkpoints important? . . . . . . . . . Checkpoints in MPPs and transaction-oriented BMPs . . . Checkpoints in batch-oriented BMPs . . . . . . . . Specifying checkpoint frequency . . . . . . . . . . Unit of work in DL/I and IMS batch programs . . . . . . Commit and rollback coordination . . . . . . . . . Restart and recovery in IMS batch . . . . . . . . . Using savepoints to undo selected changes within a unit of work . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 432 432 433 435 436 436 437 438 438 438 439 439

Chapter 20. Planning to access distributed data . . . . . . . . . . . . . . . . . 441

Planning for DRDA and DB2 private protocol access . . . . Advantages of DRDA access . . . . . . . . . . . Moving from DB2 private protocol access to DRDA access . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 441 . 442 . 442

viii

Application Programming and SQL Guide

| |

Bind processes for DRDA and DB2 private protocol access . . . . . . . . . Precompiler and bind options for DRDA access . . . . . . . . . . . . . Coding methods for distributed data . . . . . . . . . . . . . . . . . Using three-part table names to access distributed data . . . . . . . . . . Using explicit CONNECT statements to access distributed data . . . . . . . Coordinating updates to two or more data sources . . . . . . . . . . . . . Working without two-phase commit . . . . . . . . . . . . . . . . . Update restrictions on servers that do not support two-phase commit . . . . . Forcing update restrictions by using CONNECT (Type 1) . . . . . . . . . . Maximizing performance for distributed data . . . . . . . . . . . . . . Coding efficient queries . . . . . . . . . . . . . . . . . . . . . Maximizing LOB performance in a distributed environment . . . . . . . . . Using bind options to improve performance for distributed applications . . . . Using block fetch in distributed applications . . . . . . . . . . . . . . Limiting the number of DRDA network transmissions . . . . . . . . . . . Limiting the number of rows returned to DRDA clients . . . . . . . . . . Working with distributed data . . . . . . . . . . . . . . . . . . . . SQL limitations at dissimilar servers . . . . . . . . . . . . . . . . . Executing long SQL statements in a distributed environment . . . . . . . . Retrieving data from ASCII or Unicode tables . . . . . . . . . . . . . Accessing data with a scrollable cursor when the requester is down-level . . . . Accessing data with a rowset-positioned cursor when the requester is down-level . Maintaining data currency by using cursors . . . . . . . . . . . . . . Copying a table from a remote location. . . . . . . . . . . . . . . . Transmitting mixed data. . . . . . . . . . . . . . . . . . . . .

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

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

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

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

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

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

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

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

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

444 445 448 448 450 452 452 453 453 454 454 454 456 458 461 464 465 465 466 466 467 467 467 467 467

Part 5. Developing your application . . . . . . . . . . . . . . . . . . . . . 469

Chapter 21. Preparing an application program to run . . . . . . . . . . . . . . . 471
Steps in program preparation . . . . . . . . . . . . . Step 1: Process SQL statements . . . . . . . . . . . Step 2: Compile (or assemble) and link-edit the application . . Step 3: Bind the application . . . . . . . . . . . . Step 4: Run the application . . . . . . . . . . . . . Using JCL procedures to prepare applications . . . . . . . Available JCL procedures . . . . . . . . . . . . . Including code from SYSLIB data sets . . . . . . . . . Starting the precompiler dynamically . . . . . . . . . An alternative method for preparing a CICS program . . . . Using JCL to prepare a program with object-oriented extensions Using ISPF and DB2 Interactive . . . . . . . . . . . . DB2I help . . . . . . . . . . . . . . . . . . DB2I Primary Option Menu . . . . . . . . . . . . DB2 Program Preparation panel . . . . . . . . . . . DB2I Defaults Panel 1 . . . . . . . . . . . . . . DB2I Defaults Panel 2 . . . . . . . . . . . . . . Precompile panel . . . . . . . . . . . . . . . . Bind/Rebind/Free selection panel . . . . . . . . . . Bind Package panel . . . . . . . . . . . . . . . Bind Plan panel . . . . . . . . . . . . . . . . Rebind Package panel . . . . . . . . . . . . . . Rebind Trigger Package panel . . . . . . . . . . . . Rebind Plan panel . . . . . . . . . . . . . . . . Free Package panel . . . . . . . . . . . . . . . Free Plan panel . . . . . . . . . . . . . . . . . The Defaults for Bind or Rebind Package or Plan panels . . . System Connection Types panel . . . . . . . . . . . Panels for entering lists of values . . . . . . . . . . . Program Preparation: Compile, Link, and Run panel . . . . The Run panel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 472 473 494 495 508 512 512 513 514 516 517 518 518 518 521 526 527 528 531 533 536 539 540 542 544 545 546 550 552 553 555

Contents

ix

Chapter 22. Testing an application program . . . . . . . . . . . . . . . . . . . 557

Establishing a test environment . . . . . . . . . . . Designing a test data structure . . . . . . . . . . Filling the tables with test data . . . . . . . . . . Testing SQL statements using SPUFI. . . . . . . . . . Debugging your program . . . . . . . . . . . . . Debugging programs in TSO . . . . . . . . . . . Debugging programs in IMS . . . . . . . . . . . Debugging programs in CICS . . . . . . . . . . . Locating the problem . . . . . . . . . . . . . . . Analyzing error and warning messages from the precompiler SYSTERM output from the precompiler . . . . . . . SYSPRINT output from the precompiler . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 557 557 559 560 560 560 561 562 566 567 567 568

Chapter 23. Processing DL/I batch applications . . . . . . . . . . . . . . . . . . 573

Planning to use DL/I batch applications . . . . . Features and functions of DB2 DL/I batch support . Requirements for using DB2 in a DL/I batch job . Authorization . . . . . . . . . . . . . Program design considerations . . . . . . . . Address spaces . . . . . . . . . . . . Commits . . . . . . . . . . . . . . . SQL statements and IMS calls . . . . . . . . Checkpoint calls . . . . . . . . . . . . Application program synchronization . . . . . Checkpoint and XRST considerations . . . . . Synchronization call abends . . . . . . . . Input and output data sets for DL/I batch jobs . . . DB2 DL/I batch input . . . . . . . . . . DB2 DL/I batch output . . . . . . . . . . Preparation guidelines for DL/I batch programs . . Precompiling . . . . . . . . . . . . . Binding . . . . . . . . . . . . . . . Link-editing . . . . . . . . . . . . . Loading and running . . . . . . . . . . Restart and recovery . . . . . . . . . . . . JCL example of a batch backout . . . . . . . JCL example of restarting a DL/I batch job . . . Finding the DL/I batch checkpoint ID . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 573 573 574 574 574 574 575 575 575 575 575 576 576 576 578 578 578 578 579 579 580 581 581 582

Part 6. Additional programming techniques . . . . . . . . . . . . . . . . . 583

Chapter 24. Coding dynamic SQL in application programs . . . . . . . . . . . . . 593
Choosing between static and dynamic SQL . . . . . . . Flexibility of static SQL with host variables . . . . . . Flexibility of dynamic SQL . . . . . . . . . . . . Limitations of dynamic SQL . . . . . . . . . . . Dynamic SQL processing . . . . . . . . . . . . Performance of static and dynamic SQL . . . . . . . Caching dynamic SQL statements . . . . . . . . . . Using the dynamic statement cache . . . . . . . . . # Using the statement cache table . . . . . . . . . . Keeping prepared statements after commit points . . . . Limiting dynamic SQL with the resource limit facility . . . . Writing an application to handle reactive governing . . . Writing an application to handle predictive governing . . . Using predictive governing and down-level DRDA requesters Using predictive governing and enabled requesters . . . . Choosing a host language for dynamic SQL applications . . . Dynamic SQL for non-SELECT statements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 593 594 594 594 594 595 596 597 599 599 601 602 602 603 603 603 603

Application Programming and SQL Guide

Dynamic execution using EXECUTE IMMEDIATE . . . . . . . . Dynamic execution using PREPARE and EXECUTE . . . . . . . Dynamic execution of a multiple-row INSERT statement . . . . . . Using DESCRIBE INPUT to put parameter information in an SQLDA . Dynamic SQL for fixed-list SELECT statements . . . . . . . . . . Declaring a cursor for the statement name . . . . . . . . . . . Preparing the statement . . . . . . . . . . . . . . . . . Opening the cursor . . . . . . . . . . . . . . . . . . Fetching rows from the result table . . . . . . . . . . . . . Closing the cursor . . . . . . . . . . . . . . . . . . . Dynamic SQL for varying-list SELECT statements . . . . . . . . . What your application program must do . . . . . . . . . . . Preparing a varying-list SELECT statement . . . . . . . . . . Executing a varying-list SELECT statement dynamically . . . . . . Executing arbitrary statements with parameter markers . . . . . . How bind options REOPT(ALWAYS) and REOPT(ONCE) affect dynamic Using dynamic SQL in COBOL . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . SQL . . .

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

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

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

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

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

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

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

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

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

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

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

604 605 608 610 610 611 611 612 612 612 613 613 613 623 624 625 627

Chapter 25. Using stored procedures for client/server processing . . . . . . . . . . 629

Introduction to stored procedures . . . . . . . . . . . . . . . . . . . . An example of a simple stored procedure . . . . . . . . . . . . . . . . . . Setting up the stored procedures environment . . . . . . . . . . . . . . . . Defining your stored procedure to DB2 . . . . . . . . . . . . . . . . . . Refreshing the stored procedures environment (for system administrators) . . . . . . Moving stored procedures to a WLM-established environment (for system administrators) Writing and preparing an external stored procedure . . . . . . . . . . . . . . Language requirements for the stored procedure and its caller . . . . . . . . . . Calling other programs . . . . . . . . . . . . . . . . . . . . . . . Using reentrant code . . . . . . . . . . . . . . . . . . . . . . . Writing a stored procedure as a main program or subprogram . . . . . . . . . . Restrictions on a stored procedure . . . . . . . . . . . . . . . . . . . Using COMMIT and ROLLBACK statements in a stored procedure . . . . . . . . Using special registers in a stored procedure . . . . . . . . . . . . . . . . Accessing other sites in a stored procedure . . . . . . . . . . . . . . . . Writing a stored procedure to access IMS databases . . . . . . . . . . . . . Writing a stored procedure to return result sets to a DRDA client . . . . . . . . . Preparing a stored procedure . . . . . . . . . . . . . . . . . . . . . Binding the stored procedure . . . . . . . . . . . . . . . . . . . . . Writing a REXX stored procedure . . . . . . . . . . . . . . . . . . . Writing and preparing an SQL procedure . . . . . . . . . . . . . . . . . . Comparison of an SQL procedure and an external procedure . . . . . . . . . . Statements that you can include in a procedure body . . . . . . . . . . . . . Declaring and using variables, parameters, and conditions in an SQL procedure . . . . # Parameter style for an SQL procedure . . . . . . . . . . . . . . . . . . Terminating statements in an SQL procedure . . . . . . . . . . . . . . . . Handling SQL conditions in an SQL procedure . . . . . . . . . . . . . . . Examples of SQL procedures . . . . . . . . . . . . . . . . . . . . . Preparing an SQL procedure . . . . . . . . . . . . . . . . . . . . . Writing and preparing an application to use stored procedures . . . . . . . . . . . Forms of the CALL statement . . . . . . . . . . . . . . . . . . . . . Authorization for executing stored procedures . . . . . . . . . . . . . . . Linkage conventions . . . . . . . . . . . . . . . . . . . . . . . . Using indicator variables to speed processing . . . . . . . . . . . . . . . Declaring data types for passed parameters . . . . . . . . . . . . . . . . Writing a DB2 UDB for z/OS client program or SQL procedure to receive result sets . . Accessing transition tables in a stored procedure . . . . . . . . . . . . . . Calling a stored procedure from a REXX procedure . . . . . . . . . . . . . Preparing a client program . . . . . . . . . . . . . . . . . . . . . Running a stored procedure . . . . . . . . . . . . . . . . . . . . . . How DB2 determines which version of a stored procedure to run . . . . . . . . . Using a single application program to call different versions of a stored procedure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 629 630 634 635 639 640 641 641 642 642 643 646 646 647 649 650 650 652 653 654 657 658 659 661 662 662 663 667 669 681 682 683 684 703 703 708 714 714 718 719 720 720

Contents

xi

Running multiple stored procedures concurrently . . . . . . . . . . Multiple instances of a stored procedure . . . . . . . . . . . . . Accessing non-DB2 resources . . . . . . . . . . . . . . . . . Testing a stored procedure . . . . . . . . . . . . . . . . . . . Debugging the stored procedure as a stand-alone program on a workstation . Debugging with the Debug Tool and IBM VisualAge COBOL . . . . . . Debugging an SQL procedure or C language stored procedure with the Debug Tools for z/OS . . . . . . . . . . . . . . . . . . . . . . Debugging with Debug Tool for z/OS interactively and in batch mode . . . Using the MSGFILE run-time option . . . . . . . . . . . . . . Using driver applications . . . . . . . . . . . . . . . . . . Using SQL INSERT statements . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . Tool and . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . C/C++ Productivity . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . .

722 722 723 725 725 725 726 727 728 729 729

Chapter 26. Tuning your queries . . . . . . . . . . . . . . . . . . . . . . . . 731

General tips and questions . . . . . . . . . . . . . . . . . . . . . . . Is the query coded as simply as possible? . . . . . . . . . . . . . . . . . Are all predicates coded correctly? . . . . . . . . . . . . . . . . . . . Are there subqueries in your query? . . . . . . . . . . . . . . . . . . . Does your query involve aggregate functions? . . . . . . . . . . . . . . . Do you have an input variable in the predicate of an SQL query? . . . . . . . . . Do you have a problem with column correlation? . . . . . . . . . . . . . . Can your query be written to use a noncolumn expression? . . . . . . . . . . . Can materialized query tables help your query performance? . . . . . . . . . . | Does the query contain encrypted data? . . . . . . . . . . . . . . . . . | Writing efficient predicates . . . . . . . . . . . . . . . . . . . . . . . Properties of predicates . . . . . . . . . . . . . . . . . . . . . . . Predicates in the ON clause . . . . . . . . . . . . . . . . . . . . . General rules about predicate evaluation . . . . . . . . . . . . . . . . . . Order of evaluating predicates. . . . . . . . . . . . . . . . . . . . . Summary of predicate processing . . . . . . . . . . . . . . . . . . . . Examples of predicate properties . . . . . . . . . . . . . . . . . . . . Predicate filter factors . . . . . . . . . . . . . . . . . . . . . . . Column correlation . . . . . . . . . . . . . . . . . . . . . . . . DB2 predicate manipulation . . . . . . . . . . . . . . . . . . . . . Predicates with encrypted data . . . . . . . . . . . . . . . . . . . . | Using host variables efficiently . . . . . . . . . . . . . . . . . . . . . Changing the access path at run time . . . . . . . . . . . . . . . . . . | Rewriting queries to influence access path selection . . . . . . . . . . . . . Writing efficient subqueries . . . . . . . . . . . . . . . . . . . . . . . Correlated subqueries . . . . . . . . . . . . . . . . . . . . . . . Noncorrelated subqueries . . . . . . . . . . . . . . . . . . . . . . Conditions for DB2 to transform a subquery into a join . . . . . . . . . . . . Subquery tuning . . . . . . . . . . . . . . . . . . . . . . . . . Using scrollable cursors efficiently . . . . . . . . . . . . . . . . . . . . | Writing efficient queries on tables with data-partitioned secondary indexes . . . . . . . Special techniques to influence access path selection . . . . . . . . . . . . . . Obtaining information about access paths . . . . . . . . . . . . . . . . . Fetching a limited number of rows: FETCH FIRST n ROWS ONLY . . . . . . . . Minimizing overhead for retrieving few rows: OPTIMIZE FOR n ROWS . . . . . . Favoring index access . . . . . . . . . . . . . . . . . . . . . . . | Using a subsystem parameter to control outer join processing . . . . . . . . . . # Using the CARDINALITY clause to improve the performance of queries with user-defined | references . . . . . . . . . . . . . . . . . . . . . . . . . . . | Reducing the number of matching columns . . . . . . . . . . . . . . . . Creating indexes for efficient star-join processing . . . . . . . . . . . . . . Rearranging the order of tables in a FROM clause . . . . . . . . . . . . . . Updating catalog statistics . . . . . . . . . . . . . . . . . . . . . . Using a subsystem parameter . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . table function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 731 731 731 732 733 734 734 734 734 735 735 735 738 739 739 740 745 746 752 755 759 759 760 763 766 766 767 768 770 771 772 774 775 775 776 778 779 779 780 782 784 785 786

Chapter 27. Using EXPLAIN to improve SQL performance . . . . . . . . . . . . . 789 xii

Application Programming and SQL Guide

| |

Obtaining PLAN_TABLE information from EXPLAIN . . . . . . . . . Creating PLAN_TABLE . . . . . . . . . . . . . . . . . . Populating and maintaining a plan table . . . . . . . . . . . . Reordering rows from a plan table . . . . . . . . . . . . . . Asking questions about data access . . . . . . . . . . . . . . . Is access through an index? (ACCESSTYPE is I, I1, N or MX) . . . . . Is access through more than one index? (ACCESSTYPE=M) . . . . . . How many columns of the index are used in matching? (MATCHCOLS=n) Is the query satisfied using only the index? (INDEXONLY=Y) . . . . . Is direct row access possible? (PRIMARY_ACCESSTYPE = D) . . . . . Is a view or nested table expression materialized? . . . . . . . . . Was a scan limited to certain partitions? (PAGE_RANGE=Y) . . . . . What kind of prefetching is expected? (PREFETCH = L, S, D, or blank) . . Is data accessed or processed in parallel? (PARALLELISM_MODE is I, C, or Are sorts performed? . . . . . . . . . . . . . . . . . . . Is a subquery transformed into a join? . . . . . . . . . . . . . When are aggregate functions evaluated? (COLUMN_FN_EVAL) . . . . How many index screening columns are used? . . . . . . . . . . Is a complex trigger WHEN clause used? (QBLOCKTYPE=TRIGGR) . . . Interpreting access to a single table . . . . . . . . . . . . . . . Table space scans (ACCESSTYPE=R PREFETCH=S) . . . . . . . . Index access paths . . . . . . . . . . . . . . . . . . . . UPDATE using an index . . . . . . . . . . . . . . . . . Interpreting access to two or more tables (join) . . . . . . . . . . . Definitions and examples of join operations . . . . . . . . . . . Nested loop join (METHOD=1) . . . . . . . . . . . . . . . Merge scan join (METHOD=2) . . . . . . . . . . . . . . . Hybrid join (METHOD=4) . . . . . . . . . . . . . . . . . Star join (JOIN_TYPE=S) . . . . . . . . . . . . . . . . . Interpreting data prefetch . . . . . . . . . . . . . . . . . . Sequential prefetch (PREFETCH=S) . . . . . . . . . . . . . . Dynamic prefetch (PREFETCH=D) . . . . . . . . . . . . . . List prefetch (PREFETCH=L) . . . . . . . . . . . . . . . . Sequential detection at execution time . . . . . . . . . . . . . Determining sort activity . . . . . . . . . . . . . . . . . . Sorts of data . . . . . . . . . . . . . . . . . . . . . . Sorts of RIDs . . . . . . . . . . . . . . . . . . . . . The effect of sorts on OPEN CURSOR . . . . . . . . . . . . . Processing for views and nested table expressions . . . . . . . . . . Merge . . . . . . . . . . . . . . . . . . . . . . . . Materialization . . . . . . . . . . . . . . . . . . . . . Using EXPLAIN to determine when materialization occurs . . . . . . Using EXPLAIN to determine UNION activity and query rewrite . . . . Performance of merge versus materialization . . . . . . . . . . . Estimating a statements cost . . . . . . . . . . . . . . . . . Creating a statement table . . . . . . . . . . . . . . . . . Populating and maintaining a statement table . . . . . . . . . . Retrieving rows from a statement table . . . . . . . . . . . . . The implications of cost categories . . . . . . . . . . . . . .

. . . . . . . . . . . . . X) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

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

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

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

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

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

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

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

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

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

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

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

790 791 798 799 800 801 801 802 802 803 806 806 807 807 807 808 808 808 809 809 809 810 815 815 815 818 820 821 823 830 830 831 831 832 834 834 835 835 836 836 837 839 840 842 842 843 845 845 846

Chapter 28. Parallel operations and query performance . . . . . . . . . . . . . . 847

Comparing the methods of parallelism . . . . . . . . . . Enabling parallel processing . . . . . . . . . . . . . When parallelism is not used . . . . . . . . . . . . . Interpreting EXPLAIN output . . . . . . . . . . . . . A method for examining PLAN_TABLE columns for parallelism PLAN_TABLE examples showing parallelism. . . . . . . Tuning parallel processing . . . . . . . . . . . . . . Disabling query parallelism . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 848 850 851 852 852 852 854 855

Chapter 29. Programming for the Interactive System Productivity Facility . . . . . . 857
Contents

xiii

Using ISPF and the DSN command processor . . . . Invoking a single SQL program through ISPF and DSN . Invoking multiple SQL programs through ISPF and DSN . Invoking multiple SQL programs through ISPF and CAF .

. . . .

. . . .

. . . .

. . . .

. . . .

. . . .

. . . .

. . . .

. . . .

. . . .

. . . .

. . . .

. . . .

. . . .

. . . .

. . . .

. . . .

. . . .

. . . .

857 858 859 859

Chapter 30. Programming for the call attachment facility. . . . . . . . . . . . . . 861

CAF capabilities and requirements . . . . . . CAF capabilities . . . . . . . . . . . CAF requirements . . . . . . . . . . . How to use CAF . . . . . . . . . . . . Summary of connection functions . . . . . Accessing the CAF language interface . . . . General properties of CAF connections . . . . CAF function descriptions . . . . . . . . CONNECT: Syntax and usage . . . . . . . OPEN: Syntax and usage . . . . . . . . CLOSE: Syntax and usage . . . . . . . . DISCONNECT: Syntax and usage . . . . . TRANSLATE: Syntax and usage . . . . . . Summary of CAF behavior . . . . . . . . Sample scenarios . . . . . . . . . . . . A single task with implicit connections . . . . A single task with explicit connections . . . . Several tasks . . . . . . . . . . . . Exit routines from your application . . . . . . Attention exit routines . . . . . . . . . Recovery routines . . . . . . . . . . . Error messages and dsntrace . . . . . . . . CAF return codes and reason codes . . . . . . Program examples for CAF . . . . . . . . . Sample JCL for using CAF . . . . . . . . Sample assembler code for using CAF . . . . Loading and deleting the CAF language interface Connecting to DB2 for CAF . . . . . . . Checking return codes and reason codes for CAF Using dummy entry point DSNHLI for CAF . . Variable declarations for CAF . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 861 861 863 864 866 867 868 869 871 875 877 878 880 881 882 882 883 883 883 883 884 884 884 885 886 886 886 886 888 890 891

Chapter 31. Programming for the Resource Recovery Services attachment facility . . 893
RRSAF capabilities and requirements . . . RRSAF capabilities . . . . . . . . RRSAF requirements . . . . . . . . How to use RRSAF . . . . . . . . . Summary of connection functions . . . Implicit connections . . . . . . . . Accessing the RRSAF language interface . General properties of RRSAF connections . Summary of RRSAF behavior . . . . . RRSAF function descriptions . . . . . . Register conventions . . . . . . . . Parameter conventions for function calls . IDENTIFY: Syntax and usage . . . . . SWITCH TO: Syntax and usage . . . . SIGNON: Syntax and usage . . . . . AUTH SIGNON: Syntax and usage . . . CONTEXT SIGNON: Syntax and usage. . SET_ID: Syntax and usage . . . . . . SET_CLIENT_ID: Syntax and usage . . . CREATE THREAD: Syntax and usage . . TERMINATE THREAD: Syntax and usage . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 893 893 894 896 896 897 898 900 902 903 903 904 904 907 909 914 918 922 923 926 928

xiv

Application Programming and SQL Guide

TERMINATE IDENTIFY: Syntax and usage . . . TRANSLATE: Syntax and usage . . . . . . . RRSAF connection examples . . . . . . . . . Example of a single task . . . . . . . . . . Example of multiple tasks . . . . . . . . . Example of calling SIGNON to reuse a DB2 thread . Example of switching DB2 threads between tasks . RRSAF return codes and reason codes . . . . . . Program examples for RRSAF . . . . . . . . . Sample JCL for using RRSAF . . . . . . . . Loading and deleting the RRSAF language interface Using dummy entry point DSNHLI for RRSAF . . Connecting to DB2 for RRSAF . . . . . . . .

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

930 931 932 932 933 933 933 934 935 935 935 935 936

Chapter 32. CICS-specific programming techniques . . . . . . . . . . . . . . . . 939

Controlling the CICS attachment facility from an application . Improving thread reuse . . . . . . . . . . . . . . Detecting whether the CICS attachment facility is operational . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 939 . 939 . 939

| | | # | # # | # # # # # # #

Chapter 33. WebSphere MQ with DB2 . . . . . . . . . . . . . . . . . . . . . . 941

WebSphere MQ messages . . . . . . . . . . . . . . . . . . . WebSphere MQ message handling . . . . . . . . . . . . . . . WebSphere MQ functions and stored procedures . . . . . . . . . . . Commit environment for AMI-based DB2 MQ functions and stored procedures DB2 MQ tables . . . . . . . . . . . . . . . . . . . . . . Converting applications to use the MQI functions . . . . . . . . . . How to use WebSphere MQ functions . . . . . . . . . . . . . . Asynchronous messaging in DB2 UDB for z/OS and OS/390 . . . . . . . MQListener in DB2 for OS/390 and z/OS . . . . . . . . . . . . . Configuring and running MQListener in DB2 UDB for OS/390 and z/OS . . Configuring MQListener tasks . . . . . . . . . . . . . . . . . MQListener error processing . . . . . . . . . . . . . . . . . Creating a sample stored procedure to use with MQListener . . . . . . MQListener examples . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 941 941 944 948 949 958 959 966 967 968 971 972 973 974

Chapter 34. Using DB2 as a web services consumer and provider

DB2 as a web services consumer . . . . . . . . . . . . The SOAPHTTPV and SOAPHTTPC user-defined functions . . The SOAPHTTPNV and SOAPHTTPNC user-defined functions # SQLSTATEs for DB2 as a web services consumer . . . . . DB2 as a web services provider . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . 977
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 977 977 978 979 980

Chapter 35. Programming techniques: Questions and answers . . . . . . . . . . . 983

Providing a unique key for a table . . . . . . . . Scrolling through previously retrieved data . . . . . Using a scrollable cursor . . . . . . . . . . Using a ROWID or identity column . . . . . . . Scrolling through a table in any direction . . . . . . Updating data as it is retrieved from the database . . . Updating previously retrieved data . . . . . . . . Updating thousands of rows . . . . . . . . . . Retrieving thousands of rows . . . . . . . . . . Using SELECT * . . . . . . . . . . . . . . Optimizing retrieval for a small set of rows . . . . . Adding data to the end of a table . . . . . . . . Translating requests from end users into SQL statements . Changing the table definition . . . . . . . . . . Storing data that does not have a tabular format . . . Finding a violated referential or check constraint . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 983 983 983 984 985 986 986 986 987 987 987 988 988 988 989 989

Contents

xv

Part 7. Appendixes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 991

Appendix A. DB2 sample tables . . . . . . . . . . . . . . . . . . . . . . . . 993
Activity table (DSN8810.ACT) . . . . . . . . . . . . . . . Department table (DSN8810.DEPT) . . . . . . . . . . . . . Employee table (DSN8810.EMP) . . . . . . . . . . . . . . Employee photo and resume table (DSN8810.EMP_PHOTO_RESUME) . Project table (DSN8810.PROJ) . . . . . . . . . . . . . . Project activity table (DSN8810.PROJACT) . . . . . . . . . . Employee to project activity table (DSN8810.EMPPROJACT) . . . . Unicode sample table (DSN8810.DEMO_UNICODE) . . . . . . . Relationships among the sample tables . . . . . . . . . . . Views on the sample tables . . . . . . . . . . . . . . . Storage of sample application tables . . . . . . . . . . . . Storage group . . . . . . . . . . . . . . . . . . Databases . . . . . . . . . . . . . . . . . . . . Table spaces . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 993 . 994 . 996 . 999 . 1000 . 1001 . 1002 . 1003 . 1004 . 1004 . 1009 . 1010 . 1010 . 1010

Appendix B. Sample applications . . . . . . . . . . . . . . . . . . . . . . . 1013

Types of sample applications . Using the sample applications TSO . . . . . . . . IMS . . . . . . . . CICS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1013 1015 1016 1018 1018

Appendix C. Running the productivity-aid sample programs

Running DSNTIAUL . . . . . Running DSNTIAD . . . . . . Running DSNTEP2 and DSNTEP4 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . 1019
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1020 . 1024 . 1026

Appendix D. Programming examples . . . . . . . . . . . . . . . . . . . . . . 1031

Sample COBOL dynamic SQL program . . . . . . . Pointers and based variables . . . . . . . . . . Storage allocation . . . . . . . . . . . . . Example . . . . . . . . . . . . . . . . Sample dynamic and static SQL in a C program . . . . Sample DB2 REXX application . . . . . . . . . . Sample COBOL program using DRDA access . . . . . Sample COBOL program using DB2 private protocol access Examples of using stored procedures . . . . . . . . Calling a stored procedure from a C program . . . . Calling a stored procedure from a COBOL program . . Calling a stored procedure from a PL/I program . . . C stored procedure: GENERAL . . . . . . . . . C stored procedure: GENERAL WITH NULLS . . . . COBOL stored procedure: GENERAL . . . . . . . COBOL stored procedure: GENERAL WITH NULLS . . PL/I stored procedure: GENERAL . . . . . . . . PL/I stored procedure: GENERAL WITH NULLS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1031 1031 1031 1032 1043 1047 1061 1069 1075 1075 1079 1082 1083 1085 1088 1091 1093 1094

| Appendix E. Recursive common table expression examples. . . . . . . . . . . . 1097

Appendix F. REBIND subcommands for lists of plans or packages . . . . . . . . . 1103

Overview of the procedure for generating lists of REBIND commands . Sample SELECT statements for generating REBIND commands . . . Sample JCL for running lists of REBIND commands . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1103 . 1103 . 1106

Appendix G. Reserved schema names and reserved words . . . . . . . . . . . . 1109

Reserved schema names . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1109

xvi

Application Programming and SQL Guide

Reserved words .

. 1109

Appendix H. Characteristics of SQL statements in DB2 UDB for z/OS . . . . . . . . 1113

Actions allowed on SQL statements . . . . . . . . . . . SQL statements allowed in external functions and stored procedures SQL statements allowed in SQL procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1113 . 1116 . 1118

Appendix I. Program preparation options for remote packages . . . . . . . . . . 1123 Appendix J. DB2-supplied stored procedures . . . . . . . . . . . . . . . . . . 1127
WLM environment refresh stored procedure (WLM_REFRESH) . . . . . . . . . . . . . . . . Environment for WLM_REFRESH . . . . . . . . . . . . . . . . . . . . . . . . . Authorization required for WLM_REFRESH . . . . . . . . . . . . . . . . . . . . . . WLM_REFRESH syntax diagram . . . . . . . . . . . . . . . . . . . . . . . . . WLM_REFRESH option descriptions . . . . . . . . . . . . . . . . . . . . . . . . Example of WLM_REFRESH invocation . . . . . . . . . . . . . . . . . . . . . . . The CICS transaction invocation stored procedure (DSNACICS) . . . . . . . . . . . . . . . . Environment for DSNACICS . . . . . . . . . . . . . . . . . . . . . . . . . . . Authorization required for DSNACICS . . . . . . . . . . . . . . . . . . . . . . . DSNACICS syntax diagram . . . . . . . . . . . . . . . . . . . . . . . . . . . DSNACICS option descriptions . . . . . . . . . . . . . . . . . . . . . . . . . . DSNACICX user exit routine . . . . . . . . . . . . . . . . . . . . . . . . . . . Example of DSNACICS invocation . . . . . . . . . . . . . . . . . . . . . . . . . DSNACICS output . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . DSNACICS restrictions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . DSNACICS debugging . . . . . . . . . . . . . . . . . . . . . . . . . . . . . IMS transactions stored procedure (DSNAIMS) . . . . . . . . . . . . . . . . . . . . . . Environment for DSNAIMS . . . . . . . . . . . . . . . . . . . . . . . . . . . Authorization required for DSNAIMS . . . . . . . . . . . . . . . . . . . . . . . . DSNAIMS syntax diagram . . . . . . . . . . . . . . . . . . . . . . . . . . . DSNAIMS option descriptions . . . . . . . . . . . . . . . . . . . . . . . . . . Examples of DSNAIMS invocation . . . . . . . . . . . . . . . . . . . . . . . . . Connecting to multiple IMS subsystems with DSNAIMS . . . . . . . . . . . . . . . . . The DB2 EXPLAIN stored procedure . . . . . . . . . . . . . . . . . . . . . . . . . Environment . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Authorization required . . . . . . . . . . . . . . . . . . . . . . . . . . . . . DSNAEXP syntax diagram . . . . . . . . . . . . . . . . . . . . . . . . . . . DSNAEXP option descriptions . . . . . . . . . . . . . . . . . . . . . . . . . . Example of DSNAEXP invocation . . . . . . . . . . . . . . . . . . . . . . . . . DSNAEXP output . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Deprecated: Store an XML document from an MQ message queue in DB2 tables (DXXMQINSERT) . . . . Environment for DXXMQINSERT . . . . . . . . . . . . . . . . . . . . . . . . . Authorization required for DXXMQINSERT . . . . . . . . . . . . . . . . . . . . . . DXXMQINSERT syntax diagram . . . . . . . . . . . . . . . . . . . . . . . . . DXXMQINSERT option descriptions . . . . . . . . . . . . . . . . . . . . . . . . Example of DXXMQINSERT invocation . . . . . . . . . . . . . . . . . . . . . . . DXXMQINSERT output . . . . . . . . . . . . . . . . . . . . . . . . . . . . Deprecated: Store an XML document from an MQ message queue in DB2 tables (DXXMQSHRED) . . . . Environment for DXXMQSHRED . . . . . . . . . . . . . . . . . . . . . . . . . Authorization required for DXXMQSHRED . . . . . . . . . . . . . . . . . . . . . . DXXMQSHRED syntax diagram . . . . . . . . . . . . . . . . . . . . . . . . . . DXXMQSHRED option descriptions . . . . . . . . . . . . . . . . . . . . . . . . Example of DXXMQSHRED invocation . . . . . . . . . . . . . . . . . . . . . . . DXXMQSHRED output . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Deprecated: Store a large XML document from an MQ message queue in DB2 tables (DXXMQINSERTCLOB) Environment for DXXMQINSERTCLOB . . . . . . . . . . . . . . . . . . . . . . . Authorization required for DXXMQINSERTCLOB . . . . . . . . . . . . . . . . . . . . DXXMQINSERTCLOB syntax diagram . . . . . . . . . . . . . . . . . . . . . . . DXXMQINSERTCLOB option descriptions . . . . . . . . . . . . . . . . . . . . . . Example of DXXMQINSERTCLOB invocation . . . . . . . . . . . . . . . . . . . . .
Contents

# # # # # # # # # # # # # # #

# # # # # # # # # # # # #

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

1129 1129 1129 1130 1130 1131 1131 1132 1132 1132 1133 1135 1137 1138 1139 1139 1139 1139 1139 1139 1140 1142 1143 1143 1143 1144 1144 1144 1145 1146 1146 1146 1146 1147 1147 1147 1148 1148 1149 1149 1149 1149 1150 1151 1151 1151 1151 1151 1152 1152

xvii

# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #

DXXMQINSERTCLOB output . . . . . . . . . . . . . . . . . . . . . . . . . . . Deprecated: Store a large XML document from an MQ message queue in DB2 tables (DXXMQSHREDCLOB) Environment for DXXMQSHREDCLOB . . . . . . . . . . . . . . . . . . . . . . . . Authorization required for DXXMQSHREDCLOB . . . . . . . . . . . . . . . . . . . . . DXXMQSHREDCLOB syntax diagram . . . . . . . . . . . . . . . . . . . . . . . . . DXXMQSHREDCLOB option descriptions . . . . . . . . . . . . . . . . . . . . . . . Example of DXXMQSHREDCLOB invocation . . . . . . . . . . . . . . . . . . . . . . DXXMQSHREDCLOB output. . . . . . . . . . . . . . . . . . . . . . . . . . . . Deprecated: Store XML documents from an MQ message queue in DB2 tables (DXXMQINSERTALL) . . . . Environment for DXXMQINSERTALL . . . . . . . . . . . . . . . . . . . . . . . . . Authorization required for DXXMQINSERTALL . . . . . . . . . . . . . . . . . . . . . DXXMQINSERTALL syntax diagram . . . . . . . . . . . . . . . . . . . . . . . . . DXXMQINSERTALL option descriptions . . . . . . . . . . . . . . . . . . . . . . . . Example of DXXMQINSERTALL invocation . . . . . . . . . . . . . . . . . . . . . . . DXXMQINSERTALL output . . . . . . . . . . . . . . . . . . . . . . . . . . . . Deprecated: Store XML documents from an MQ message queue in DB2 tables (DXXMQSHREDALL) . . . . Environment for DXXMQSHREDALL . . . . . . . . . . . . . . . . . . . . . . . . . Authorization required for DXXMQSHREDALL . . . . . . . . . . . . . . . . . . . . . DXXMQSHREDALL syntax diagram . . . . . . . . . . . . . . . . . . . . . . . . . DXXMQSHREDALL option descriptions . . . . . . . . . . . . . . . . . . . . . . . . Example of DXXMQSHREDALL invocation . . . . . . . . . . . . . . . . . . . . . . . DXXMQSHREDALL output . . . . . . . . . . . . . . . . . . . . . . . . . . . . Deprecated: Store large XML documents from an MQ message queue in DB2 tables (DXXMQSHREDALLCLOB) Environment for DXXMQSHREDALLCLOB . . . . . . . . . . . . . . . . . . . . . . . Authorization required for DXXMQSHREDALLCLOB . . . . . . . . . . . . . . . . . . . DXXMQSHREDALLCLOB syntax diagram . . . . . . . . . . . . . . . . . . . . . . . DXXMQSHREDALLCLOB option descriptions . . . . . . . . . . . . . . . . . . . . . . Example of DXXMQSHREDALLCLOB invocation . . . . . . . . . . . . . . . . . . . . . DXXMQSHREDALLCLOB output . . . . . . . . . . . . . . . . . . . . . . . . . . Deprecated: Store large XML documents from an MQ message queue in DB2 tables (DXXMQINSERTALLCLOB) Environment for DXXMQINSERTALLCLOB . . . . . . . . . . . . . . . . . . . . . . . Authorization required for DXXMQINSERTALLCLOB . . . . . . . . . . . . . . . . . . . DXXMQINSERTALLCLOB syntax diagram . . . . . . . . . . . . . . . . . . . . . . . DXXMQINSERTALLCLOB option descriptions . . . . . . . . . . . . . . . . . . . . . . Example of DXXMQINSERTALLCLOB invocation . . . . . . . . . . . . . . . . . . . . . DXXMQINSERTALLCLOB output . . . . . . . . . . . . . . . . . . . . . . . . . . Deprecated: Send XML documents to an MQ message queue (DXXMQGEN) . . . . . . . . . . . . . Environment for DXXMQGEN . . . . . . . . . . . . . . . . . . . . . . . . . . . Authorization required for DXXMQGEN . . . . . . . . . . . . . . . . . . . . . . . . DXXMQGEN syntax diagram . . . . . . . . . . . . . . . . . . . . . . . . . . . DXXMQGEN option descriptions . . . . . . . . . . . . . . . . . . . . . . . . . . Example of DXXMQGEN invocation . . . . . . . . . . . . . . . . . . . . . . . . . DXXMQGEN output . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Deprecated: Send XML documents to an MQ message queue (DXXMQRETRIEVE) . . . . . . . . . . . Environment for DXXMQRETRIEVE . . . . . . . . . . . . . . . . . . . . . . . . . Authorization required for DXXMQRETRIEVE . . . . . . . . . . . . . . . . . . . . . . DXXMQRETRIEVE syntax diagram . . . . . . . . . . . . . . . . . . . . . . . . . DXXMQRETRIEVE option descriptions . . . . . . . . . . . . . . . . . . . . . . . . Example of DXXMQRETRIEVE invocation . . . . . . . . . . . . . . . . . . . . . . . DXXMQRETRIEVE output . . . . . . . . . . . . . . . . . . . . . . . . . . . . Deprecated: Send large XML documents to an MQ message queue (DXXMQGENCLOB) . . . . . . . . . Environment for DXXMQGENCLOB . . . . . . . . . . . . . . . . . . . . . . . . . Authorization required for DXXMQGENCLOB . . . . . . . . . . . . . . . . . . . . . . DXXMQGENCLOB syntax diagram . . . . . . . . . . . . . . . . . . . . . . . . . DXXMQGENCLOB option descriptions . . . . . . . . . . . . . . . . . . . . . . . . Example of DXXMQGENCLOB invocation . . . . . . . . . . . . . . . . . . . . . . . DXXMQGENCLOB output . . . . . . . . . . . . . . . . . . . . . . . . . . . . Deprecated: Send XML documents to an MQ message queue (DXXMQRETRIEVECLOB) . . . . . . . . . Environment for DXXMQRETRIEVECLOB . . . . . . . . . . . . . . . . . . . . . . . Authorization required for DXXMQRETRIEVECLOB . . . . . . . . . . . . . . . . . . . . DXXMQRETRIEVECLOB syntax diagram . . . . . . . . . . . . . . . . . . . . . . .

1153 1153 1154 1154 1154 1154 1155 1156 1156 1156 1156 1156 1157 1157 1158 1158 1158 1159 1159 1159 1160 1160 1161 1161 1161 1161 1161 1162 1163 1163 1163 1163 1164 1164 1165 1165 1165 1166 1166 1166 1166 1168 1169 1169 1169 1169 1169 1170 1171 1173 1173 1173 1173 1173 1174 1175 1176 1176 1176 1177 1177

xviii

Application Programming and SQL Guide

# # #

DXXMQRETRIEVECLOB option descriptions . . Example of DXXMQRETRIEVECLOB invocation . DXXMQRETRIEVECLOB output . . . . . .

. . .

. . .

. . .

. . .

. . .

. . .

. . .

. . .

. . .

. . .

. . .

. . .

. . .

. . .

. . .

. . .

. . .

. . .

. . .

. 1177 . 1179 . 1180

Appendix K. How to use the DB2 library . . . . . . . . . . . . . . . . . . . . 1181 Notices . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1183

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1184 . 1185

Programming interface information . Trademarks . . . . . . . . .

Glossary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1187 Bibliography . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1221 Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . X-1 .

Contents

xix

xx

Application Programming and SQL Guide

About this book

This book discusses how to design and write application programs that access DB2 Universal Database for z/OS (DB2), a highly flexible relational database management system (DBMS). Unless it is stated otherwise, this information assumes that DB2 is running in new-function mode (as opposed to compatibility mode or enabling-new-function mode). Visit the following Web site for information about ordering DB2 books and obtaining other valuable information about DB2 UDB for z/OS: http://publib.boulder.ibm.com/infocenter/imzic

Important In this version of DB2 UDB for z/OS, the DB2 Utilities Suite is available as an optional product. You must separately order and purchase a license to such utilities, and discussion of those utility functions in this publication is not intended to otherwise imply that you have a license to them. See Part 1 of DB2 Utility Guide and Reference for packaging details. The DB2 Utilities Suite is designed to work with the DFSORT program, which you are licensed to use in support of the DB2 utilities even if you do not otherwise license DFSORT for general use. If your primary sort product is not DFSORT, consider the following informational APARs mandatory reading: v II14047/II14213: USE OF DFSORT BY DB2 UTILITIES v II13495: HOW DFSORT TAKES ADVANTAGE OF 64-BIT REAL ARCHITECTURE These informational APARs are periodically updated.

Who should read this book

This book is for DB2 application developers who are familiar with Structured Query Language (SQL) and who know one or more programming languages that DB2 supports.

Terminology and citations

In this information, DB2 Universal Database for z/OS is referred to as "DB2 UDB for z/OS." In cases where the context makes the meaning clear, DB2 UDB for z/OS is referred to as "DB2." When this information refers to titles of books in this library, a short title is used. (For example, "See DB2 SQL Reference" is a citation to IBM DB2 Universal Database for z/OS SQL Reference.) When referring to a DB2 product other than DB2 UDB for z/OS, this information uses the products full name to avoid ambiguity. The following terms are used as indicated: DB2
Copyright IBM Corp. 1983, 2008

Represents either the DB2 licensed program or a particular DB2 subsystem.

xxi

# # # # # #

OMEGAMON Refers to any of the following products: v IBM Tivoli OMEGAMON XE for DB2 Performance Expert on z/OS v IBM Tivoli OMEGAMON XE for DB2 Performance Monitor on z/OS v IBM DB2 Performance Expert for Multiplatforms and Workgroups v IBM DB2 Buffer Pool Analyzer for z/OS C, C++, and C language Represent the C or C++ programming language. CICS Represents CICS Transaction Server for z/OS or CICS Transaction Server for OS/390. IMS Represents the IMS Database Manager or IMS Transaction Manager. MVS Represents the MVS element of the z/OS operating system, which is equivalent to the Base Control Program (BCP) component of the z/OS operating system. RACF Represents the functions that are provided by the RACF component of the z/OS Security Server.

How to read the syntax diagrams

The following rules apply to the syntax diagrams that are used in this book: 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. 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

xxii

Application Programming and SQL Guide

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

If one of the items is the default, it appears above the main path and the remaining choices are 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.

Accessibility
Accessibility features help a user who has a physical disability, such as restricted mobility or limited vision, to use software products. The major accessibility features in z/OS products, including DB2 UDB for z/OS, enable users to: v Use assistive technologies such as screen reader and screen magnifier software v Operate specific or equivalent features by using only a keyboard v Customize display attributes such as color, contrast, and font size Assistive technology products, such as screen readers, function with the DB2 UDB for z/OS user interfaces. Consult the documentation for the assistive technology products for specific information when you use assistive technology to access these interfaces. Online documentation for Version 8 of DB2 UDB for z/OS is available in the Information management software for z/OS solutions information center, which is an accessible format when used with assistive technologies such as screen reader
About this book

xxiii

or screen magnifier software. The Information management software for z/OS solutions information center is available at the following Web site: http://publib.boulder.ibm.com/infocenter/dzichelp

How to send your comments

Your feedback helps IBM to provide quality information. Please send any comments that you have about this book or other DB2 UDB for z/OS documentation. You can use the following methods to provide comments: v Send your comments by e-mail to [email protected] and include the name of the product, the version number of the product, and the number of the book. If you are commenting on specific text, please list the location of the text (for example, a chapter and section title or a help topic title). v You can send comments from the Web. Visit the library Web site at: www.ibm.com/software/db2zos/library.html This Web site has a an online reader comment form that you can use to send comments. v You can also send comments by using the feedback link at the footer of each page in the Information Management Software for z/OS Solutions Information Center at http://publib.boulder.ibm.com/infocenter/db2zhelp.

xxiv

Application Programming and SQL Guide

Summary of changes to this book

| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | The principal changes to this book are: v Chapter 1, Retrieving data, on page 3 explains how to create and use common table expressions in SELECT, CREATE VIEW, and INSERT statements, and also describes how to use common table expressions to create recursive SQL. v Chapter 2, Working with tables and modifying data, on page 19 explains how to select column values as you insert rows into a table by using the SELECT from INSERT statement. v Chapter 6, Basics of coding SQL in an application program, on page 77 contains information on how to use: Host variable arrays, and their indicator arrays, in a multiple-row INSERT statement (in a C or C++, COBOL, or PL/I program). The GET DIAGNOSTICS statement to return diagnostic information about the last SQL statement that was executed (for example, information about input data errors during the execution of a multiple-row INSERT statement). v Chapter 7, Using a cursor to retrieve a set of rows, on page 103 explains how to use: Static and dynamic scrollable cursors. A rowset-positioned cursor in a multiple-row FETCH statement