0% found this document useful (0 votes)

1K views1,205 pages

Application Programming and SQL

DB2 Application Programming and SQL

Uploaded by

bschuck1

We take content rights seriously. If you suspect this is your content, claim it here.

Available Formats

Download as PDF, TXT or read online on Scribd

0% found this document useful (0 votes)

1K views1,205 pages

Application Programming and SQL

DB2 Application Programming and SQL

Uploaded by

bschuck1

We take content rights seriously. If you suspect this is your content, claim it here.

Available Formats

Download as PDF, TXT or read online on Scribd

You are on page 1/ 1205

DB2 Version 9.

1 for z/OS

Application Programming and SQL Guide

SC18-9841-11

DB2 Version 9.1 for z/OS

Application Programming and SQL Guide

SC18-9841-11

Note Before using this information and the product it supports, be sure to read the general information under Notices at the end of this information.

Twelfth edition (June 2013) This edition applies to DB2 Version 9.1 for z/OS (DB2 V9.1 for z/OS, product number 5635-DB2), DB2 9 for z/OS Value Unit Edition (product number 5697-P12), 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. Specific changes are indicated by a vertical bar to the left of a change. A vertical bar to the left of a figure caption indicates that the figure has changed. Editorial changes that have no technical significance are not noted. Copyright IBM Corporation 1983, 2013. US Government Users Restricted Rights Use, duplication or disclosure restricted by GSA ADP Schedule Contract with IBM Corp.

Contents
About this information. . . . . . . . . . . . . . . . . . . . . . . . . . . . . xiii
Who should read this information . . DB2 Utilities Suite . . . . . . . . Terminology and citations . . . . . Accessibility features for DB2 Version 9.1 How to send your comments . . . . How to read syntax diagrams . . . . . . . for . . . . . . . . z/OS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xiii xiii xiii xiv . xv . xv

Chapter 1. Planning for and designing DB2 applications . . . . . . . . . . . . . . . 1

| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
Application and SQL release incompatibilities . . . . . . . . . . . . . . . . . . . . . . . . 1 Changes in BIND PACKAGE and BIND PLAN defaults . . . . . . . . . . . . . . . . . . . . 1 Plan for the XML data type . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2 Changes to XMLNAMESPACES . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2 Changes to serialization of empty elements . . . . . . . . . . . . . . . . . . . . . . . . 2 Availability of LOB or XML values in JDBC or SQLJ applications with progressive streaming . . . . . . . 2 Adjust applications that depend on error information that is returned from DB2-supplied stored procedures . . 2 Some materialized query tables should be dropped . . . . . . . . . . . . . . . . . . . . . 3 Fully define objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3 Changes to PL/I applications with no DECLARE VARIABLE statements . . . . . . . . . . . . . . 3 Automatic rebind of plans and packages created before DB2 Version 4 . . . . . . . . . . . . . . . 3 Changed behavior for ODBC data conversion for the SQL_BINARY type . . . . . . . . . . . . . . 4 Changed behavior of the INSERT statement with the OVERRIDING USER VALUE clause . . . . . . . . 4 DESCRIBE no longer returns LONG type values . . . . . . . . . . . . . . . . . . . . . . 4 DB2 enforces the restrictions about where a host variable array can be specified . . . . . . . . . . . . 4 DEBUGSESSION system privilege required for continued debugging of SQL procedures . . . . . . . . . 4 Changes to the result length of the DECRYPT function . . . . . . . . . . . . . . . . . . . . 5 COLTYPE column in SYSIBM.SYSCOLUMNS and SYSIBM.SYSCOLUMNS_HIST for LONG column types . . . 5 CREATEDBY column in SYSIBM.SYSDATATYPES, SYSIBM.SYSROUTINES, SYSIBM.SYSSEQUENCES, and SYSIBM.SYSTRIGGERS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5 Drop and re-create SYSPROC.DSNWZP . . . . . . . . . . . . . . . . . . . . . . . . . 5 DB2 returns all DSNWZP output in the same format as DB2 parameters . . . . . . . . . . . . . . 5 DB2 enforces the restriction that row IDs are not compatible with character strings when they are used with a set operator. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5 Database privileges on the DSNDB04 database now give you those privileges on all implicitly created databases 6 Implicitly created objects that are associated with LOB columns require additional privileges . . . . . . . 6 Adjust applications to use LRHCLR instead of LGDISCLR . . . . . . . . . . . . . . . . . . . 6 Changed behavior for the CREATE statement . . . . . . . . . . . . . . . . . . . . . . . 6 The DECLARE statement and the work file database . . . . . . . . . . . . . . . . . . . . . 6 Adjust monitor programs that access OP buffers . . . . . . . . . . . . . . . . . . . . . . 6 Changed behavior for system-required objects . . . . . . . . . . . . . . . . . . . . . . . 7 Changes to INSERT, UPDATE, or DELETE statements on some indexes . . . . . . . . . . . . . . 8 LOBs with a maximum length greater than 1 GB can now be logged . . . . . . . . . . . . . . . 8 DB2 returns an error when a LOB value is specified for an argument to a stored procedure and the argument value is longer than the target parameter and the excess is not trailing blanks . . . . . . . . . . . . 8 Changes to VARCHAR_FORMAT function length attribute . . . . . . . . . . . . . . . . . . . 8 'W' is no longer recognized as a valid format element of the VARCHAR_FORMAT function format string . . . 8 Leading or trailing blanks from the VARCHAR_FORMAT function format string are no longer removed . . . 8 DB2 drops certain indexes when a unique constraint is dropped . . . . . . . . . . . . . . . . . 9 Changes to the maximum size of the row that is used by sort to evaluate aggregate functions . . . . . . . 9 DB2 enforces restriction on specifying a CAST FROM clause for some forms of CREATE FUNCTION statements 9 DB2 enforces restrictions on specifying the AS LOCATOR clause and TABLE LIKE clause . . . . . . . . 9 DB2 enforces restriction on the CCSID parameter for the DECRYPT_BIT and DECRYPT_BINARY functions . . 10 Changed behavior of CREATE PROCEDURE for an SQL procedure . . . . . . . . . . . . . . . 10 Explicitly qualify names of variables, parameters, and columns in SQL procedures . . . . . . . . . . 10

Copyright IBM Corp. 1983, 2013

iii

| | | | | | | | | | | | | | | | | | |

Make any necessary program changes for possibly different values for RETURNED_SQLSTATE and DB2_RETURNED_SQLCODE . . . . . . . . . . . . . . . . . . . . . . . . . SQLSTATE and SQLCODE SQL variables after a GET DIAGNOSTICS statement . . . . . . . . Coding multiple SQL statements in a handler body . . . . . . . . . . . . . . . . . . Unhandled warnings . . . . . . . . . . . . . . . . . . . . . . . . . . . . Change your programs to handle any changed messages from SQL procedures. . . . . . . . . Enhanced data type checking for zero-length characters . . . . . . . . . . . . . . . . Adding a column generates a new table space version . . . . . . . . . . . . . . . . . You cannot add a column and issue SELECT, INSERT, UPDATE, DELETE, or MERGE statements in the commit scope. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . CAST FROM clause of CREATE FUNCTION statement for SQL functions is no longer supported . . GRAPHIC and NOGRAPHIC SQL processing options are deprecated . . . . . . . . . . . . Specifying ALTER DATABASE STOGROUP for work file databases . . . . . . . . . . . . DB2 enforces restrictions about where an INTO clause can be specified . . . . . . . . . . . Precompilation for unsupported compilers . . . . . . . . . . . . . . . . . . . . . Changes for INSTEAD OF triggers . . . . . . . . . . . . . . . . . . . . . . . Qualify user-defined function names . . . . . . . . . . . . . . . . . . . . . . . SQLCODE changes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . SQL reserved words . . . . . . . . . . . . . . . . . . . . . . . . . . . . Determining the value of any SQL processing options that affect the design of your program . . . . . Determining the binding method . . . . . . . . . . . . . . . . . . . . . . . . . Changes that invalidate plans or packages . . . . . . . . . . . . . . . . . . . . . Determining the value of any bind options that affect the design of your program . . . . . . . . Programming applications for performance . . . . . . . . . . . . . . . . . . . . . . Designing your application for recovery . . . . . . . . . . . . . . . . . . . . . . . Unit of work in TSO . . . . . . . . . . . . . . . . . . . . . . . . . . . . Unit of work in CICS . . . . . . . . . . . . . . . . . . . . . . . . . . . . Planning for program recovery in IMS programs . . . . . . . . . . . . . . . . . . . Undoing selected changes within a unit of work by using savepoints . . . . . . . . . . . . Planning for recovery of table spaces that are not logged . . . . . . . . . . . . . . . . Designing your application to access distributed data . . . . . . . . . . . . . . . . . . Remote servers and distributed data . . . . . . . . . . . . . . . . . . . . . . . Advantages of DRDA access. . . . . . . . . . . . . . . . . . . . . . . . . . Preparing for coordinated updates to two or more data sources . . . . . . . . . . . . . . Forcing restricted system rules in your program . . . . . . . . . . . . . . . . . . . Creating a feed in IBM Mashup Center with data from a DB2 for z/OS server . . . . . . . . . .

. . . . . . . . . . . . . . same . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

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

10 10 11 11 11 12 12 12 12 12 12 12 12 13 13 13 13 14 14 17 19 19 21 22 22 23 30 32 33 34 35 35 36 37

Chapter 2. Connecting to DB2 from your application program . . . . . . . . . . . . 39

Invoking the call attachment facility . . . . . . . . . . . . . . . . . . . . . . . . . . . 40 Call attachment facility . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 43 Making the CAF language interface (DSNALI) available . . . . . . . . . . . . . . . . . . . 47 Requirements for programs that use CAF . . . . . . . . . . . . . . . . . . . . . . . . 48 How CAF modifies the content of registers . . . . . . . . . . . . . . . . . . . . . . . . 49 Implicit connections to CAF . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49 CALL DSNALI statement parameter list . . . . . . . . . . . . . . . . . . . . . . . . . 50 Summary of CAF behavior . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 52 CAF connection functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 53 Turning on a CAF trace . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 64 CAF return codes and reason codes . . . . . . . . . . . . . . . . . . . . . . . . . . 64 Sample CAF scenarios . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 65 Examples of invoking CAF . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 66 Invoking the Resource Recovery Services attachment facility . . . . . . . . . . . . . . . . . . . 72 Resource Recovery Services attachment facility . . . . . . . . . . . . . . . . . . . . . . 75 Making the RRSAF language interface (DSNRLI) available . . . . . . . . . . . . . . . . . . 79 Requirements for programs that use RRSAF . . . . . . . . . . . . . . . . . . . . . . . 81 How RRSAF modifies the content of registers . . . . . . . . . . . . . . . . . . . . . . . 81 Implicit connections to RRSAF . . . . . . . . . . . . . . . . . . . . . . . . . . . . 82 CALL DSNRLI statement parameter list . . . . . . . . . . . . . . . . . . . . . . . . . 82 Summary of RRSAF behavior . . . . . . . . . . . . . . . . . . . . . . . . . . . . 83 RRSAF connection functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 85 RRSAF return codes and reason codes . . . . . . . . . . . . . . . . . . . . . . . . . 115

Application Programming and SQL Guide

Sample RRSAF scenarios . . . . . . . . . . . . Program examples for RRSAF . . . . . . . . . . . Controlling the CICS attachment facility from an application . Detecting whether the CICS attachment facility is operational Improving thread reuse in CICS applications . . . . . .

. . . . .

116 118 120 121 122

Chapter 3. Coding SQL statements in application programs: General information . . . 123

Declaring table and view definitions. . . . . . . . . . . . . . . . . . . DCLGEN (declarations generator) . . . . . . . . . . . . . . . . . . Generating table and view declarations by using DCLGEN . . . . . . . . . . Including declarations from DCLGEN in your program . . . . . . . . . . . Example: Adding DCLGEN declarations to a library . . . . . . . . . . . . Defining the items that your program can use to check whether an SQL statement executed Defining SQL descriptor areas . . . . . . . . . . . . . . . . . . . . . Declaring host variables and indicator variables . . . . . . . . . . . . . . . Host variables . . . . . . . . . . . . . . . . . . . . . . . . . Host variable arrays . . . . . . . . . . . . . . . . . . . . . . . Host structures . . . . . . . . . . . . . . . . . . . . . . . . . Indicator variables, arrays, and structures . . . . . . . . . . . . . . . . Setting the CCSID for host variables. . . . . . . . . . . . . . . . . . Determining what caused an error when retrieving data into a host variable . . . . Compatibility of SQL and language data types . . . . . . . . . . . . . . . Embedding SQL statements in your application . . . . . . . . . . . . . . . Delimiting an SQL statement . . . . . . . . . . . . . . . . . . . . Rules for host variables in an SQL statement . . . . . . . . . . . . . . . Retrieving a single row of data into host variables . . . . . . . . . . . . . Determining whether a retrieved value in a host variable is null or truncated . . . . Determining whether a column value is null . . . . . . . . . . . . . . . Updating data by using host variables . . . . . . . . . . . . . . . . . Inserting a single row by using a host variable . . . . . . . . . . . . . . Inserting null values into columns by using indicator variables or arrays . . . . . Host variable arrays in an SQL statement . . . . . . . . . . . . . . . . Retrieving multiple rows of data into host variable arrays . . . . . . . . . . Inserting multiple rows of data from host variable arrays. . . . . . . . . . . Retrieving a single row of data into a host structure . . . . . . . . . . . . Including dynamic SQL in your program . . . . . . . . . . . . . . . . Keeping prepared statements after commit points . . . . . . . . . . . . . Limiting CPU time for dynamic SQL statements by using the resource limit facility . . Checking the execution of SQL statements. . . . . . . . . . . . . . . . . Checking the execution of SQL statements by using the SQLCA . . . . . . . . Checking the execution of SQL statements by using SQLCODE and SQLSTATE . . . Checking the execution of SQL statements by using the WHENEVER statement . . . Checking the execution of SQL statements by using the GET DIAGNOSTICS statement Handling SQL error codes . . . . . . . . . . . . . . . . . . . . . . Arithmetic and conversion errors . . . . . . . . . . . . . . . . . . . Writing applications that enable users to create and modify tables. . . . . . . . . Saving SQL statements that are translated from end user requests . . . . . . . . . XML data in embedded SQL applications . . . . . . . . . . . . . . . . . Host variable data types for XML data in embedded SQL applications . . . . . . XML column updates in embedded SQL applications . . . . . . . . . . . . XML data retrieval in embedded SQL applications . . . . . . . . . . . . . Programming examples . . . . . . . . . . . . . . . . . . . . . . . Examples of programs that call stored procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . successfully . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 124 125 125 133 134 137 137 138 139 139 140 140 141 142 144 146 147 147 148 150 152 153 154 154 155 156 157 157 158 197 199 201 202 206 207 208 214 214 215 215 216 216 221 223 226 227

Chapter 4. Coding SQL statements in assembler application programs . . . . . . . 229

Defining the SQL communications area, SQLSTATE, and SQLCODE Defining SQL descriptor areas in assembler . . . . . . . . Declaring host variables and indicator variables in assembler . . Host variables in assembler . . . . . . . . . . . . Indicator variables in assembler . . . . . . . . . . . in . . . . assembler . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 229 231 231 232 237

Contents

Equivalent SQL and assembler data types . . . . . SQL statements in assembler programs . . . . . . Delimiters in SQL statements in assembler programs Macros for assembler applications . . . . . . Programming examples in assembler . . . . .

. . . . .

238 243 248 248 248

Chapter 5. Coding SQL statements in C application programs . . . . . . . . . . . 249

Defining the SQL communications area, SQLSTATE, and SQLCODE in C . Defining SQL descriptor areas in C . . . . . . . . . . . . . . Declaring host variables and indicator variables in C . . . . . . . . Host variables in C . . . . . . . . . . . . . . . . . . Host variable arrays in C . . . . . . . . . . . . . . . . Host structures in C . . . . . . . . . . . . . . . . . . Indicator variables, indicator arrays, and host structure indicator arrays in Referencing pointer host variables in C programs . . . . . . . . Declaring pointer host variables in C programs . . . . . . . . . Equivalent SQL and C data types. . . . . . . . . . . . . . . SQL statements in C programs . . . . . . . . . . . . . . . Delimiters in SQL statements in C programs . . . . . . . . . . Programming examples in C . . . . . . . . . . . . . . . . . . . . . C . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 249 250 251 251 263 271 273 275 276 278 283 287 287

Chapter 6. Coding SQL statements in COBOL application programs. . . . . . . . . 299

Defining the SQL communications area, SQLSTATE, and SQLCODE in COBOL . . Defining SQL descriptor areas in COBOL . . . . . . . . . . . . . . . Declaring host variables and indicator variables in COBOL . . . . . . . . . Host variables in COBOL . . . . . . . . . . . . . . . . . . . Host variable arrays in COBOL . . . . . . . . . . . . . . . . . Host structures in COBOL . . . . . . . . . . . . . . . . . . . Indicator variables, indicator arrays, and host structure indicator arrays in COBOL Controlling the CCSID for COBOL host variables . . . . . . . . . . . Equivalent SQL and COBOL data types . . . . . . . . . . . . . . . SQL statements in COBOL programs . . . . . . . . . . . . . . . . Delimiters in SQL statements in COBOL programs . . . . . . . . . . . Object-oriented extensions in COBOL . . . . . . . . . . . . . . . Programming examples in COBOL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 299 301 301 302 312 321 326 328 329 334 339 340 340

Chapter 7. Coding SQL statements in Fortran application programs . . . . . . . . . 371

Defining the SQL communications area, SQLSTATE, and SQLCODE Defining SQL descriptor areas in Fortran . . . . . . . . . Declaring host variables and indicator variables in Fortran . . . Host variables in Fortran . . . . . . . . . . . . . Indicator variables in Fortran . . . . . . . . . . . . Equivalent SQL and Fortran data types. . . . . . . . . . SQL statements in Fortran programs. . . . . . . . . . . Delimiters in SQL statements in Fortran programs . . . . . in . . . . . . . Fortran . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 371 372 373 373 376 377 379 381

Chapter 8. Coding SQL statements in PL/I application programs . . . . . . . . . . 383

Defining the SQL communications area, SQLSTATE, and SQLCODE Defining SQL descriptor areas in PL/I . . . . . . . . . . Declaring host variables and indicator variables in PL/I . . . . Host variables in PL/I . . . . . . . . . . . . . . Host variable arrays in PL/I . . . . . . . . . . . . Host structures in PL/I . . . . . . . . . . . . . . Indicator variables in PL/I . . . . . . . . . . . . . Equivalent SQL and PL/I data types . . . . . . . . . . SQL statements in PL/I programs . . . . . . . . . . . Delimiters in SQL statements in PL/I programs . . . . . . Programming examples in PL/I . . . . . . . . . . . in . . . . . . . . . . PL/I . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 383 384 384 385 391 396 398 399 403 407 407

Application Programming and SQL Guide

Chapter 9. Coding SQL statements in REXX application programs . . . . . . . . . 413

Defining the SQL communications area, SQLSTATE, and SQLCODE in REXX . . Defining SQL descriptor areas in REXX. . . . . . . . . . . . . . . Equivalent SQL and REXX data types . . . . . . . . . . . . . . . SQL statements in REXX programs . . . . . . . . . . . . . . . . Delimiters in SQL statements in REXX programs . . . . . . . . . . Accessing the DB2 REXX language support application programming interfaces Ensuring that DB2 correctly interprets character input data in REXX programs Passing the data type of an input data type to DB2 for REXX programs . . . Setting the isolation level of SQL statements in a REXX program . . . . . Retrieving data from DB2 tables in REXX programs . . . . . . . . . Cursors and statement names in REXX . . . . . . . . . . . . . . Programming examples in REXX . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 413 413 414 415 418 418 420 421 421 422 423 424

Chapter 10. Creating and modifying DB2 objects . . . . . . . . . . . . . . . . . 435

Creating tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Data types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Storing LOB data in a table. . . . . . . . . . . . . . . . . . . . . . . . Identity columns . . . . . . . . . . . . . . . . . . . . . . . . . . . Creating tables for data integrity . . . . . . . . . . . . . . . . . . . . . . Creating work tables for the EMP and DEPT sample tables . . . . . . . . . . . . . Creating created temporary tables . . . . . . . . . . . . . . . . . . . . . Creating declared temporary tables . . . . . . . . . . . . . . . . . . . . . Providing a unique key for a table . . . . . . . . . . . . . . . . . . . . . . Fixing tables with incomplete definitions . . . . . . . . . . . . . . . . . . . . Dropping tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . Defining a view . . . . . . . . . . . . . . . . . . . . . . . . . . . . Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Dropping a view . . . . . . . . . . . . . . . . . . . . . . . . . . . . Creating a common table expression. . . . . . . . . . . . . . . . . . . . . . Common table expressions . . . . . . . . . . . . . . . . . . . . . . . . Examples of recursive common table expressions . . . . . . . . . . . . . . . . Creating triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . Invoking a stored procedure or user-defined function from a trigger . . . . . . . . . . Inserting, updating, and deleting data in views by using INSTEAD OF triggers . . . . . . Trigger packages . . . . . . . . . . . . . . . . . . . . . . . . . . . Trigger cascading . . . . . . . . . . . . . . . . . . . . . . . . . . . Order of multiple triggers . . . . . . . . . . . . . . . . . . . . . . . . Interactions between triggers and referential constraints . . . . . . . . . . . . . . Interactions between triggers and tables that have multilevel security with row-level granularity Triggers that return inconsistent results. . . . . . . . . . . . . . . . . . . . Sequence objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . DB2 object relational extensions . . . . . . . . . . . . . . . . . . . . . . . Creating a distinct type . . . . . . . . . . . . . . . . . . . . . . . . . . Distinct types . . . . . . . . . . . . . . . . . . . . . . . . . . . . Example of distinct types, user-defined functions, and LOBs . . . . . . . . . . . . Defining a user-defined function . . . . . . . . . . . . . . . . . . . . . . . User-defined functions . . . . . . . . . . . . . . . . . . . . . . . . . Components of a user-defined function definition . . . . . . . . . . . . . . . . Writing an external user-defined function . . . . . . . . . . . . . . . . . . . Making a user-defined function reentrant . . . . . . . . . . . . . . . . . . . Special registers in a user-defined function or a stored procedure . . . . . . . . . . . Accessing transition tables in a user-defined function or stored procedure . . . . . . . . Preparing an external user-defined function for execution . . . . . . . . . . . . . Abnormal termination of an external user-defined function . . . . . . . . . . . . . Saving information between invocations of a user-defined function by using a scratchpad. . . Example of creating and using a user-defined scalar function . . . . . . . . . . . . User-defined function samples that ship with DB2 . . . . . . . . . . . . . . . . Determining the authorization cache size for stored procedures and user-defined functions . . Creating a stored procedure . . . . . . . . . . . . . . . . . . . . . . . . Stored procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 435 436 438 441 443 453 454 456 459 459 460 460 461 462 462 464 464 468 476 478 480 480 481 482 484 484 487 488 489 489 490 493 495 498 500 523 524 527 530 531 531 532 533 534 535 536

| |

Contents

vii

| | |

Moving stored procedures to a WLM-established environment . . . . . . Creating a native SQL procedure . . . . . . . . . . . . . . . . Migrating an external SQL procedure to a native SQL procedure . . . . . Changing an existing version of a native SQL procedure . . . . . . . . Regenerating an existing version of a native SQL procedure . . . . . . . Removing an existing version of a native SQL procedure . . . . . . . . Creating an external SQL procedure . . . . . . . . . . . . . . . Creating an external stored procedure . . . . . . . . . . . . . . Creating multiple versions of external procedures and external SQL procedures

. . . . . . . . .

549 550 575 577 578 579 579 595 636

Chapter 11. Adding and modifying data . . . . . . . . . . . . . . . . . . . . . 637

|
Inserting data into tables . . . . . . . . . . . Inserting rows by using the INSERT statement . . . Inserting data and updating data in a single operation Selecting values while inserting data . . . . . . Adding data to the end of a table . . . . . . . . Storing data that does not have a tabular format . . . Updating table data . . . . . . . . . . . . . Selecting values while updating data . . . . . . Updating thousands of rows . . . . . . . . . Deleting data from tables . . . . . . . . . . . Selecting values while deleting data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 637 637 643 645 652 652 653 654 655 655 657

| | |

Chapter 12. Accessing data . . . . . . . . . . . . . . . . . . . . . . . . . . 659

Determining which tables you have access to. . . . . . . . . . . . . . Displaying information about the columns for a given table . . . . . . . . . Retrieving data by using the SELECT statement . . . . . . . . . . . . . Selecting derived columns . . . . . . . . . . . . . . . . . . . Selecting XML data . . . . . . . . . . . . . . . . . . . . . Formatting the result table . . . . . . . . . . . . . . . . . . . Combining result tables from multiple SELECT statements . . . . . . . . Summarizing group values . . . . . . . . . . . . . . . . . . . Finding rows that were changed within a specified period of time . . . . . Joining data from more than one table . . . . . . . . . . . . . . . Optimizing retrieval for a small set of rows . . . . . . . . . . . . . Creating recursive SQL by using common table expressions . . . . . . . . Updating data as it is retrieved from the database . . . . . . . . . . . Avoiding decimal arithmetic errors . . . . . . . . . . . . . . . . Implications of using SELECT * . . . . . . . . . . . . . . . . . Subqueries . . . . . . . . . . . . . . . . . . . . . . . . Restrictions when using distinct types with UNION, EXCEPT, and INTERSECT . Comparison of distinct types . . . . . . . . . . . . . . . . . . Nested SQL statements . . . . . . . . . . . . . . . . . . . . Retrieving a set of rows by using a cursor . . . . . . . . . . . . . . . Cursors . . . . . . . . . . . . . . . . . . . . . . . . . Accessing data by using a row-positioned cursor . . . . . . . . . . . Accessing data by using a rowset-positioned cursor . . . . . . . . . . Retrieving rows by using a scrollable cursor . . . . . . . . . . . . . Accessing XML or LOB data quickly by using FETCH WITH CONTINUE . . . Determining the attributes of a cursor by using the SQLCA . . . . . . . . Determining the attributes of a cursor by using the GET DIAGNOSTICS statement Scrolling through previously retrieved data . . . . . . . . . . . . . Updating previously retrieved data . . . . . . . . . . . . . . . . FETCH statement interaction between row and rowset positioning . . . . . Examples of fetching rows by using cursors . . . . . . . . . . . . . Specifying direct row access by using row IDs . . . . . . . . . . . . . ROWID columns . . . . . . . . . . . . . . . . . . . . . . Ways to manipulate LOB data . . . . . . . . . . . . . . . . . . . LOB host variable, LOB locator, and LOB file reference variable declarations . . LOB materialization . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 659 659 660 663 663 664 670 675 677 677 688 690 691 691 692 693 701 702 703 704 705 709 714 720 726 729 729 730 732 732 733 738 739 740 741 747

| | |

viii

Application Programming and SQL Guide

| | |

Saving storage when manipulating LOBs by using LOB locators . Deferring evaluation of a LOB expression to improve performance LOB file reference variables . . . . . . . . . . . . . Referencing a sequence object . . . . . . . . . . . . . . Retrieving thousands of rows . . . . . . . . . . . . . . Determining when a row was changed . . . . . . . . . . . Checking whether an XML column contains a certain value . . . . Accessing DB2 data that is not in a table . . . . . . . . . . Ensuring that queries perform sufficiently . . . . . . . . . . Items to include in a batch DL/I program . . . . . . . . . .

. . . . . . . . . .

747 749 751 753 754 754 755 756 756 757

Chapter 13. Invoking a user-defined function . . . . . . . . . . . . . . . . . . . 761

Determining the authorization ID for invoking user-defined functions . . Ensuring that DB2 executes the intended user-defined function. . . . . How DB2 resolves functions . . . . . . . . . . . . . . . Checking how DB2 resolves functions by using DSN_FUNCTION_TABLE Restrictions when passing arguments with distinct types to functions . . Cases when DB2 casts arguments for a user-defined function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 763 763 764 767 771 772

Chapter 14. Calling a stored procedure from your application . . . . . . . . . . . 775

Passing large output parameters to stored procedures by using indicator variables Data types for calling stored procedures . . . . . . . . . . . . . . Calling a stored procedure from a REXX procedure . . . . . . . . . . Preparing a client program that calls a remote stored procedure . . . . . . How DB2 determines which stored procedure to run . . . . . . . . . . Calling different versions of a stored procedure from a single application . . . Invoking multiple instances of a stored procedure . . . . . . . . . . . Designating the active version of a native SQL procedure . . . . . . . . Temporarily overriding the active version of a native SQL procedure . . . . . Specifying the number of stored procedures that can run concurrently . . . . Retrieving the procedure status . . . . . . . . . . . . . . . . . Writing a program to receive the result sets from a stored procedure . . . . . DB2-supplied stored procedures . . . . . . . . . . . . . . . . . WLM_REFRESH stored procedure . . . . . . . . . . . . . . . WLM_SET_CLIENT_INFO stored procedure . . . . . . . . . . . . DSNACICS stored procedure . . . . . . . . . . . . . . . . . DSNAIMS stored procedure . . . . . . . . . . . . . . . . . DSNAIMS2 stored procedure . . . . . . . . . . . . . . . . . DSNACCOR stored procedure. . . . . . . . . . . . . . . . . Deprecated: DXXMQINSERT stored procedure . . . . . . . . . . . Deprecated: DXXMQSHRED stored procedure . . . . . . . . . . . Deprecated: DXXMQINSERTCLOB stored procedure . . . . . . . . . Deprecated: DXXMQSHREDCLOB stored procedure . . . . . . . . . Deprecated: DXXMQINSERTALL stored procedure . . . . . . . . . . Deprecated: DXXMQSHREDALL stored procedure . . . . . . . . . . Deprecated: DXXMQSHREDALLCLOB stored procedure . . . . . . . . Deprecated: DXXMQINSERTALLCLOB stored procedure . . . . . . . . Deprecated: DXXMQGEN stored procedure . . . . . . . . . . . . Deprecated: DXXMQRETRIEVE stored procedure . . . . . . . . . . Deprecated: DXXMQGENCLOB stored procedure . . . . . . . . . . Deprecated: DXXMQRETRIEVECLOB stored procedure . . . . . . . . XSR_REGISTER stored procedure . . . . . . . . . . . . . . . XSR_ADDSCHEMADOC stored procedure . . . . . . . . . . . . XSR_COMPLETE stored procedure . . . . . . . . . . . . . . . XSR_REMOVE stored procedure . . . . . . . . . . . . . . . . Deprecated: XDBDECOMPXML stored procedure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 780 781 781 785 786 786 788 789 789 790 790 792 797 804 806 808 816 821 825 846 849 851 854 856 858 861 863 866 869 873 877 880 882 883 885 886

| |

Chapter 15. Coding methods for distributed data . . . . . . . . . . . . . . . . . 889

Accessing distributed data by using three-part table names . . . . . . . . Accessing remote declared temporary tables by using three-part table names . . . . . . . . . . . . . . . . . . . . . . 889 . 891

Contents

| | | |

Accessing distributed data by using explicit CONNECT statements . . . . . . . . . . Specifying a location alias name for multiple sites . . . . . . . . . . . . . . . Releasing connections . . . . . . . . . . . . . . . . . . . . . . . . Transmitting mixed data. . . . . . . . . . . . . . . . . . . . . . . . . Identifying the server at run time . . . . . . . . . . . . . . . . . . . . . SQL limitations at dissimilar servers. . . . . . . . . . . . . . . . . . . . . Support for executing long SQL statements in a distributed environment . . . . . . . . Distributed queries against ASCII or Unicode tables . . . . . . . . . . . . . . . Restrictions when using scrollable cursors to access distributed data . . . . . . . . . . Restrictions when using rowset-positioned cursors to access distributed data . . . . . . . WebSphere MQ with DB2 . . . . . . . . . . . . . . . . . . . . . . . . WebSphere MQ messages . . . . . . . . . . . . . . . . . . . . . . . DB2 MQ functions and DB2 MQ XML stored procedures . . . . . . . . . . . . . Generating XML documents from existing tables and sending them to an MQ message queue Shredding XML documents from an MQ message queue . . . . . . . . . . . . . DB2 MQ tables . . . . . . . . . . . . . . . . . . . . . . . . . . . Converting applications to use the MQI functions . . . . . . . . . . . . . . . Basic messaging with WebSphere MQ . . . . . . . . . . . . . . . . . . . Sending messages with WebSphere MQ . . . . . . . . . . . . . . . . . . Retrieving messages with WebSphere MQ . . . . . . . . . . . . . . . . . . Application to application connectivity with WebSphere MQ . . . . . . . . . . . Asynchronous messaging in DB2 for z/OS . . . . . . . . . . . . . . . . .

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

891 892 893 894 894 894 895 895 896 896 896 897 901 907 907 907 916 917 918 919 920 924

Chapter 16. DB2 as a web services consumer and provider . . . . . . . . . . . . 935

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

| The SOAPHTTPNV and SOAPHTTPNC user-defined functions

. . .

. 935 . 937 . 938

Chapter 17. Preparing an application to run on DB2 for z/OS . . . . . . . . . . . . 941

Setting the DB2I defaults . . . . . . . . . . . . . . . . . . . . . . . . Processing SQL statements . . . . . . . . . . . . . . . . . . . . . . . . Processing SQL statements by using the DB2 precompiler . . . . . . . . . . . . Processing SQL statements by using the DB2 coprocessor. . . . . . . . . . . . . Translating command-level statements in a CICS program . . . . . . . . . . . . Differences between the DB2 precompiler and the DB2 coprocessor . . . . . . . . . Options for SQL statement processing . . . . . . . . . . . . . . . . . . . Compiling and link-editing an application. . . . . . . . . . . . . . . . . . . Binding an application . . . . . . . . . . . . . . . . . . . . . . . . . Binding a DBRM to a package. . . . . . . . . . . . . . . . . . . . . . Binding an application plan . . . . . . . . . . . . . . . . . . . . . . Bind process for remote access . . . . . . . . . . . . . . . . . . . . . Binding a batch program . . . . . . . . . . . . . . . . . . . . . . . Conversion of DBRMs that are bound to a plan to DBRMs that are bound to a package . . Converting an existing plan into packages to run remotely . . . . . . . . . . . . Setting the program level . . . . . . . . . . . . . . . . . . . . . . . DYNAMICRULES bind option . . . . . . . . . . . . . . . . . . . . . Determining the authorization cache size for plans . . . . . . . . . . . . . . . Determining the authorization cache size for packages . . . . . . . . . . . . . Dynamic plan selection . . . . . . . . . . . . . . . . . . . . . . . . Rebinding an application . . . . . . . . . . . . . . . . . . . . . . . . Rebinding a package . . . . . . . . . . . . . . . . . . . . . . . . . Rebinding a plan . . . . . . . . . . . . . . . . . . . . . . . . . . Rebinding lists of plans and packages . . . . . . . . . . . . . . . . . . . Generating lists of REBIND commands . . . . . . . . . . . . . . . . . . . Automatic rebinding . . . . . . . . . . . . . . . . . . . . . . . . . Specifying the rules that apply to SQL behavior at run time . . . . . . . . . . . . DB2 program preparation overview . . . . . . . . . . . . . . . . . . . . Input and output data sets for DL/I batch jobs. . . . . . . . . . . . . . . . . DB2-supplied JCL procedures for preparing an application . . . . . . . . . . . . . JCL to include the appropriate interface code when using the DB2-supplied JCL procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 943 . 944 . 946 . 953 . 955 . 956 . 958 . 968 . 969 . 970 . 975 . 980 . 984 . 984 . 985 . 986 . 987 . 989 . 989 . 990 . 991 . 991 . 993 . 993 . 994 . 999 . 1001 . 1002 . 1004 . 1007 . 1007

Application Programming and SQL Guide

Tailoring DB2-supplied JCL procedures for preparing CICS programs DB2I primary option menu . . . . . . . . . . . . . . . DB2I panels that are used for program preparation . . . . . . . DB2 Program Preparation panel . . . . . . . . . . . . . DB2I Defaults Panel 1 . . . . . . . . . . . . . . . . DB2I Defaults Panel 2 . . . . . . . . . . . . . . . . Precompile panel . . . . . . . . . . . . . . . . . . Bind Package panel . . . . . . . . . . . . . . . . . Bind Plan panel . . . . . . . . . . . . . . . . . . Defaults for Bind Package and Defaults for Rebind Package panels . Defaults for Bind Plan and Defaults for Rebind Plan panels . . . System Connection Types panel . . . . . . . . . . . . . Panels for entering lists of values . . . . . . . . . . . . Program Preparation: Compile, Link, and Run panel . . . . . . DB2I panels that are used to rebind and free plans and packages. . . Bind/Rebind/Free Selection panel . . . . . . . . . . . . Rebind Package panel . . . . . . . . . . . . . . . . Rebind Trigger Package panel . . . . . . . . . . . . . Rebind Plan panel . . . . . . . . . . . . . . . . . Free Package panel . . . . . . . . . . . . . . . . . Free Plan panel . . . . . . . . . . . . . . . . . .

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

1008 1010 1011 1012 1016 1019 1020 1022 1025 1029 1031 1033 1034 1035 1037 1038 1039 1041 1043 1045 1046

Chapter 18. Running an application on DB2 for z/OS . . . . . . . . . . . . . . . 1047

DSN command processor . . . . . . . . . . . . . DB2I Run panel . . . . . . . . . . . . . . . . Running a program in TSO foreground . . . . . . . . Running a DB2 REXX application . . . . . . . . . . Invoking programs through the Interactive System Productivity ISPF . . . . . . . . . . . . . . . . . . . Invoking a single SQL program through ISPF and DSN . . Invoking multiple SQL programs through ISPF and DSN . Loading and running a batch program . . . . . . . . Authorization for running a batch DL/I program . . . . Restarting a batch program . . . . . . . . . . . Running stored procedures from the command line processor . Command line processor CALL statement . . . . . . Example of running a batch DB2 application in TSO . . . . Example of calling applications in a command procedure . . . . . . . . . . . . . . Facility . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1047 1048 1049 1050 1050 1051 1052 1053 1054 1055 1056 1058 1058 1059 1060

| |

Chapter 19. Testing and debugging an application program on DB2 for z/OS . . . . 1063
Designing a test data structure . . . . . . . . . . . . . . Analyzing application data needs . . . . . . . . . . . . Authorization for test tables and applications . . . . . . . . Example SQL statements to create a comprehensive test structure . Populating the test tables with data . . . . . . . . . . . . Methods for testing SQL statements . . . . . . . . . . . . Executing SQL by using SPUFI . . . . . . . . . . . . . . SPUFI . . . . . . . . . . . . . . . . . . . . . Content of a SPUFI input data set . . . . . . . . . . . . The SPUFI panel . . . . . . . . . . . . . . . . . . Changing SPUFI defaults . . . . . . . . . . . . . . . Setting the SQL terminator character in a SPUFI input data set . . Controlling toleration of warnings in SPUFI . . . . . . . . . Output from SPUFI . . . . . . . . . . . . . . . . . Testing an external user-defined function . . . . . . . . . . . Testing a user-defined function by using the Debug Tool for z/OS . Testing a user-defined function by routing the debugging messages to Testing a user-defined function by using driver applications . . . Testing a user-defined function by using SQL INSERT statements . Debugging stored procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . SYSPRINT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1063 1063 1065 1065 1066 1067 1067 1071 1072 1072 1074 1079 1080 1081 1082 1083 1084 1085 1085 1085

Contents

| | | | | | |

Debugging stored procedures with the Debug Tool and IBM VisualAge COBOL . . . Debugging a C language stored procedure with the Debug Tool and C/C++ Productivity Debugging stored procedures by using the Unified Debugger . . . . . . . . . . Debugging stored procedures with the Debug Tool for z/OS . . . . . . . . . . Recording stored procedure debugging messages in a file . . . . . . . . . . . Driver applications for debugging procedures . . . . . . . . . . . . . . . DB2 tables that contain debugging information. . . . . . . . . . . . . . . Debugging an application program. . . . . . . . . . . . . . . . . . . . Locating the problem in an application . . . . . . . . . . . . . . . . . Techniques for debugging programs in TSO . . . . . . . . . . . . . . . . Techniques for debugging programs in IMS . . . . . . . . . . . . . . . . Techniques for debugging programs in CICS . . . . . . . . . . . . . . . Finding a violated referential or check constraint . . . . . . . . . . . . . . .

. . . . . Tools for z/OS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

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

. 1086 1087 . 1088 . 1089 . 1091 . 1092 . 1092 . 1092 . 1093 . 1098 . 1098 . 1099 . 1103

Chapter 20. DB2 sample applications and data . . . . . . . . . . . . . . . . . 1105

DB2 sample tables . . . . . . . . . . . . . . . . . . . Activity table (DSN8910.ACT) . . . . . . . . . . . . . . Department table (DSN8910.DEPT) . . . . . . . . . . . . . Employee table (DSN8910.EMP) . . . . . . . . . . . . . . Employee photo and resume table (DSN8910.EMP_PHOTO_RESUME) . Project table (DSN8910.PROJ) . . . . . . . . . . . . . . . Project activity table (DSN8910.PROJACT) . . . . . . . . . . Employee-to-project activity table (DSN8910.EMPPROJACT) . . . . Unicode sample table (DSN8910.DEMO_UNICODE) . . . . . . . Relationships among the sample tables . . . . . . . . . . . Views on the sample tables . . . . . . . . . . . . . . . Storage of sample application tables . . . . . . . . . . . . DB2 sample applications . . . . . . . . . . . . . . . . . Types of sample applications . . . . . . . . . . . . . . . Application languages and environments for the sample applications . Sample applications in TSO . . . . . . . . . . . . . . . Sample applications in IMS . . . . . . . . . . . . . . . Sample applications in CICS . . . . . . . . . . . . . . . DSNTIAUL. . . . . . . . . . . . . . . . . . . . . DSNTIAD . . . . . . . . . . . . . . . . . . . . . DSNTEP2 and DSNTEP4 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1105 1105 1106 1108 1111 1113 1114 1115 1116 1117 1118 1122 1126 1128 1130 1131 1134 1134 1135 1140 1142

Information resources for DB2 for z/OS and related products . . . . . . . . . . . 1149 Notices . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1151
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1152 . 1153 . 1153

Programming interface information. Trademarks. . . . . . . . . Privacy policy considerations . . .

Glossary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1155 Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1157

xii

Application Programming and SQL Guide

About this information

This information discusses how to design and write application programs that access DB2 for z/OS (DB2), a highly flexible relational database management system (DBMS). Visit the following Web site for information about ordering DB2 books and obtaining other valuable information about DB2 for z/OS: http:// publib.boulder.ibm.com/infocenter/imzic This information assumes that your DB2 subsystem is running in Version 9.1 new-function mode. Generally, new functions that are described, including changes to existing functions, statements, and limits, are available only in new-function mode. Two exceptions to this general statement are new and changed utilities and optimization enhancements, which are also available in conversion mode unless stated otherwise.

Who should read this information

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

DB2 Utilities Suite

Important: In this version of DB2 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. The DB2 Utilities Suite can work with DB2 Sort and 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. Related information DB2 utilities packaging (Utility Guide)

Terminology and citations

In this information, DB2 Version 9.1 for z/OS is referred to as "DB2 for z/OS." In cases where the context makes the meaning clear, DB2 for z/OS is referred to as "DB2." When this information refers to titles of DB2 for z/OS books, a short title is used. (For example, "See DB2 SQL Reference" is a citation to IBM DB2 Version 9.1 for z/OS SQL Reference.)

Copyright IBM Corp. 1983, 2013

xiii

When referring to a DB2 product other than DB2 for z/OS, this information uses the product's full name to avoid ambiguity. The following terms are used as indicated: DB2 Represents either the DB2 licensed program or a particular DB2 subsystem. 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. 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.

Accessibility features for DB2 Version 9.1 for z/OS

Accessibility features help a user who has a physical disability, such as restricted mobility or limited vision, to use information technology products successfully.

Accessibility features
The following list includes the major accessibility features in z/OS products, including DB2 Version 9.1 for z/OS. These features support: v Keyboard-only operation. v Interfaces that are commonly used by screen readers and screen magnifiers. v Customization of display attributes such as color, contrast, and font size Tip: The Information Management Software for z/OS Solutions Information Center (which includes information for DB2 Version 9.1 for z/OS) and its related publications are accessibility-enabled for the IBM Home Page Reader. You can operate all features using the keyboard instead of the mouse.

Keyboard navigation
You can access DB2 Version 9.1 for z/OS ISPF panel functions by using a keyboard or keyboard shortcut keys. For information about navigating the DB2 Version 9.1 for z/OS ISPF panels using TSO/E or ISPF, refer to the z/OS TSO/E Primer, the z/OS TSO/E User's Guide, and the z/OS ISPF User's Guide. These guides describe how to navigate each interface, including the use of keyboard shortcuts or function keys (PF keys). Each guide includes the default settings for the PF keys and explains how to modify their functions.

xiv

Application Programming and SQL Guide

Related accessibility information

Online documentation for DB2 Version 9.1 for z/OS is available in the Information Management Software for z/OS Solutions Information Center, which is available at the following website: http://pic.dhe.ibm.com/infocenter/dzichelp/v2r2/index.jsp

IBM and accessibility

See the IBM Accessibility Center at http://www.ibm.com/able for more information about the commitment that IBM has to accessibility.

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 for z/OS documentation. You can use the following methods to provide comments: v Send your comments by email 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 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://pic.dhe.ibm.com/infocenter/dzichelp/v2r2/index.jsp.

How to read syntax diagrams

Certain conventions apply to the syntax diagrams that are used in IBM documentation. Apply the following rules when reading the syntax diagrams that are used in DB2 for z/OS documentation: 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

About this information

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

If choosing one of the items is optional, the entire stack appears below the main path.
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 Sometimes a diagram must be split into fragments. The syntax fragment is shown separately from the main syntax diagram, but the contents of the fragment should be read as if they are on the main path of the diagram.
required_item fragment-name

fragment-name:
required_item optional_name

v With the exception of XPath keywords, keywords appear in uppercase (for example, FROM). Keywords must be spelled exactly as shown. XPath keywords are defined as lowercase names, and must be spelled exactly as shown. Variables appear in all lowercase letters (for example, column-name). They represent user-supplied names or values.

xvi

Application Programming and SQL Guide

v If punctuation marks, parentheses, arithmetic operators, or other such symbols are shown, you must enter them as part of the syntax.

About this information

xvii

xviii

Application Programming and SQL Guide

Chapter 1. Planning for and designing DB2 applications

Before you write or run your program, you need to make some planning and design decisions. These decisions need to be made whether you are writing a new DB2 application or migrating an existing application from a previous release of DB2.

About this task

If you are migrating an existing application from a previous release of DB2, read the application and SQL release incompatibilities and make any necessary changes in the application. If v v v you are writing a new DB2 application, first determine the following items: the value of some of the SQL processing options the binding method the value of some of the bind options

Then make sure that your program implements the appropriate recommendations so that it promotes concurrency, can handle recovery and restart situations, and can efficiently access distributed data. Related tasks: Programming applications for performance (DB2 Performance) Programming for concurrency (DB2 Performance) Writing efficient SQL queries (DB2 Performance) Improving performance for applications that access distributed data (DB2 Performance) Related reference: BIND and REBIND options (DB2 Commands) | | | | | | | | | | | | Plan for the following changes in Version 9.1 that might affect your migration.

Application and SQL release incompatibilities

When you migrate to Version 9.1, be aware of the application and SQL release incompatibilities.

Changes in BIND PACKAGE and BIND PLAN defaults

In DB2 Version 9, the default value for bind option CURRENTDATA is changed from YES to NO. This applies to the BIND PLAN and the BIND PACKAGE subcommands, the CREATE TRIGGER SQL statements for trigger packages, and the CREATE PROCEDURE and the ALTER PROCEDURE ADD VERSION SQL statements for SQL PL procedure packages. Specifying NO for CURRENTDATA is the best option for performance.
Copyright IBM Corp. 1983, 2013

| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |

The default value for bind option ISOLATION is changed from RR to CS. This applies to the BIND PLAN and the remote BIND PACKAGE subcommands. For the BIND PACKAGE subcommand, the current default (plan value) stays. The default change does not apply to implicitly built CTs (for example, DISTSERV CTs). Although you can specify DBPROTOCOL(PRIVATE) for the DBPROTOCOL parameter of the BIND option, DB2 issues a new warning message, DSNT226I. All BIND statements for plans and packages that are bound during the installation or migration process specify the ISOLATION parameter explicitly, except for routines that do not fetch data. The current settings are maintained for compatibility.

Plan for the XML data type

Drop any user-defined data types with the name XML to prevent problems with the new Version 9 built-in XML data type. You can re-create the existing user-defined data types with new names.

Changes to XMLNAMESPACES
In DB2 Version 8, in the XMLNAMESPACES function, if the XML-namespace-uri argument has a value of http://www.w3.org/XML/1998/namespace or http://www.w3.org/2000/xmlns/, DB2 does not issue an error. Starting in Version 9 conversion mode, DB2 issues an error.

Changes to serialization of empty elements

In Version 8, DB2 serializes empty XML elements in a different way than it serializes them in Version 9.1. In Version 8, empty element a is serialized as <a></a>. Starting in Version 9 conversion mode, empty element a is serialized as <a/>.

Availability of LOB or XML values in JDBC or SQLJ applications with progressive streaming
In previous releases, if a JDBC or SQLJ application retrieves LOB data into an application variable, the contents of the application variable are still available after the cursor is moved or closed. Version 9 supports streaming. The IBM Data Server Driver for JDBC and SQLJ uses progressive streaming as the default for retrieval of LOB or XML values. When progressive streaming is in effect, the contents of LOB or XML variables are no longer available after the cursor is moved or closed.

Adjust applications that depend on error information that is returned from DB2-supplied stored procedures
Adjust any applications that call one of the following stored procedures and then check and process the specific SQLCODE or SQLSTATE that is returned by the CALL statement: v SQLJ.INSTALL_JAR v v v v v v SQLJ.REMOVE_JAR SQLJ.REPLACE_JAR SQLJ.DB2_INSTALL_JAR SQLJ.DB2_REPLACE_JAR SQLJ.DB2_REMOVE_JAR SQLJ.DB2_UPDATEJARINFO

Application Programming and SQL Guide

| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |

Starting in Version 9, these stored procedures return more meaningful SQLCODEs and SQLSTATEs than they return in previous releases of DB2. The other input and output parameters of these stored procedures have not changed. For example, the following application needs to change because -20201 is no longer the SQLCODE that is returned. Successful execution (SQLCODE 0) is not affected.
CALL SQLJ.REMOVE_JAR(...) IF (SQLCODE = -20201) THEN DO; ... END;

Some materialized query tables should be dropped

Before migrating to conversion mode, drop all materialized query tables that are based on the SYSIBM.SYSROUTINES catalog table. During migration to conversion mode, if any materialized query tables are based on the SYSIBM.SYSROUTINES catalog table, SQLCODE -750 is issued. Before migrating to enabling-new-function mode, drop all materialized query tables that are based on the SYSIBM.SYSPACKSTMT catalog table. During migration to enabling-new-function mode, if any materialized query tables are based on the SYSIBM.SYSPACKSTMT catalog table, SQLCODE -750 is issued.

Fully define objects

Ensure that you do not have any incomplete object definitions in your DB2 Version 8 catalog. For example, if a table has a primary or unique key defined but the enforcing primary or unique key index does not exist, the table definition is considered incomplete. You need to complete or drop all such objects before you begin migration because their behavior will be different in Version 9.1. For example, if you attempt to create an enforcing primary key index to complete a table definition in Version 9.1 and the residing table space is implicitly created, the index is treated as a regular index instead of an enforcing index.

Changes to PL/I applications with no DECLARE VARIABLE statements

For PL/I applications with no DECLARE VARIABLE statements, the rules for host variables and string constants in the FROM clause of a PREPARE or EXECUTE IMMEDIATE statement have changed. A host variable must be a varying-length string variable that is preceded by a colon. A PL/I string cannot be preceded by a colon.

Automatic rebind of plans and packages created before DB2 Version 4

If you have plans and packages that were bound before DB2 Version 4 and you specified YES or COEXIST in the AUTO BIND field of panel DSNTIPO, DB2 Version 9.1 autobinds these packages. Thus, you might experience an execution delay the first time that such a plan is loaded. Also, DB2 might change the access path due to the autobind, potentially resulting in a more efficient access path. If you specify NO in the AUTO BIND field of panel DSNTIPO, DB2 Version 9.1 returns SQLCODE -908, SQLSTATE 23510 for each attempt to use such a package or plan until it is rebound.

Chapter 1. Planning for and designing DB2 applications

| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |

Changed behavior for ODBC data conversion for the SQL_BINARY type
In releases before Version 9.1, when ODBC applications used the SQL_BINARY type to bind parameter markers, ODBC mapped the SQL_BINARY type to CHAR FOR BIT DATA. In Version 9.1, when the DB2 server is in Version 9.1 new-function mode, ODBC maps SQL_BINARY to BINARY. Because CHAR FOR BIT DATA fields are padded with blanks, and BINARY fields are not padded, applications might experience differences in behavior. For example, in releases before Version 9.1, if the target CHAR FOR BIT DATA column was shorter than the SQL_BINARY input host variable, and the truncated values were blanks, DB2 did not generate an error. In Version 9.1, if the target BINARY column is shorter than the SQL_BINARY input host variable, and the truncated values are hexadecimal zeroes, DB2 generates an error.

Changed behavior of the INSERT statement with the OVERRIDING USER VALUE clause
When the INSERT statement is specified with the OVERRIDING USER VALUE clause, the value for the insert operation is ignored for columns that are defined with the GENERATED BY DEFAULT or GENERATED ALWAYS attribute.

DESCRIBE no longer returns LONG type values

Because DB2 no longer stores LONG type values in the catalog, when you execute a DESCRIBE statement against a column with a LONG VARCHAR or LONG VARGRAPHIC data type, the DESCRIBE statement returns the values as VARCHAR or VARGRAPHIC data types. The DSNTIAUL sample program was updated through APAR PK46518 to account for this change. You need to apply APAR PK46518 and precompile, bind compile, and link-edit DSNTIAUL to make it compatible with the changed DESCRIBE behavior.

DB2 enforces the restrictions about where a host variable array can be specified
host-variable-array is the meta-variable for host variable arrays in syntax diagrams. host-variable-array is included only in the syntax for multi-row FETCH, multi-row INSERT, multi-row MERGE, and EXECUTE in support of a dynamic multi-row INSERT or MERGE statement. host-variable-array is not included in the syntax diagram for expression, so a host variable array cannot be used in other contexts. In previous releases, if you specified host-variable-array in an unsupported context, you received no errors. In Version 9.1, if a host variable array is referenced in an unsupported context, DB2 issues an error. For more information about where you can specify the host-variable-array variable, see Host variable arrays in an SQL statement (DB2 Application programming and SQL).

DEBUGSESSION system privilege required for continued debugging of SQL procedures

After you migrate to new-function mode, users that debug external SQL procedures need the DEBUGSESSION system privilege. (External SQL procedures were previously called SQL procedures in Version 8.) Only users of the new

Application Programming and SQL Guide

| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |

Unified Debugger enabled client platforms need this system privilege. Users of the Version 8 SQL Debugger-enabled client platforms do not need this system privilege.

Changes to the result length of the DECRYPT function

The result length of the DECRYPT function is shortened to 8 bytes less than the length of the input value. If the result expands because of a difference between input and result CCSIDs, you must cast the encrypted data to a larger VARCHAR value before the DECRYPT function is run.

COLTYPE column in SYSIBM.SYSCOLUMNS and SYSIBM.SYSCOLUMNS_HIST for LONG column types

When new tables are created with LONG VARCHAR or LONG VARGRAPHIC columns, the COLTYPE values in SYSIBM.SYSCOLUMNS and SYSIBM.SYSCOLUMNS_HIST contain VARCHAR or VARG.

CREATEDBY column in SYSIBM.SYSDATATYPES, SYSIBM.SYSROUTINES, SYSIBM.SYSSEQUENCES, and SYSIBM.SYSTRIGGERS

The CREATEDBY column might contain a different value than in previous releases of DB2. The column might contain a different value in static CREATE statements for distinct types, functions, and procedures or when a dynamic SQL statement sets the CURRENT SQLID value to a value other than USER.

Drop and re-create SYSPROC.DSNWZP

Drop and re-create the SYSPROC.DSNWZP stored procedure as part of running job DSNTIJSG or alter it to specify READS SQL DATA, as shown in the following SQL statement:
ALTER PROCEDURE SYSPROC.DSNWZP READS SQL DATA;

On data sharing systems, SYSPROC.DSNWZP needs to be dropped and re-created as part of migrating the first member, but not for subsequent members. DSNTIJSG grants execute access on DSNWZP to PUBLIC. If necessary, change PUBLIC to a specific authorization ID.

DB2 returns all DSNWZP output in the same format as DB2 parameters
In previous releases, DSNWZP returned the current setting of several system parameters in a format other than the one used by the system parameter macros. For example, DSN6SPRM expected the setting for EDMPOOL in kilobytes, and DSNWZP returned it i