Application Programming And SQL Guide

2y ago

30 Views

1 Downloads

7.88 MB

1.1K Pages

Report/dmca

Download PDF

Transcription

DB2 10 for z/OSApplication Programming and SQLGuideIBMSC19-2969-13

NotesBefore using this information and the product it supports, be sure to read the general information under “Notices” at theend of this information.October 12, 2017 editionThis edition applies to DB2 10 for z/OS (product number 5605-DB2), DB2 10 for z/OS Value Unit Edition (productnumber 5697-P31), and to any subsequent releases until otherwise indicated in new editions. Make sure you areusing 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 captionindicates that the figure has changed. Editorial changes that have no technical significance are not noted. Copyright IBM Corporation 1983, 2017.US Government Users Restricted Rights – Use, duplication or disclosure restricted by GSA ADP Schedule Contractwith IBM Corp.

ContentsAbout this information . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xvWho should read this information . . . .DB2 Utilities Suite for z/OS . . . . . .Terminology and citations . . . . . .Accessibility features for DB2 10 for z/OS .How to send your comments . . . . .How to read syntax diagrams . . . . . xv. xv. . . . . . . . . . . . . . . . . . . . . . . . xvi. . . . . . . . . . . . . . . . . . . . . . . . xvi. . . . . . . . . . . . . . . . . . . . . . . . xvii. . . . . . . . . . . . . . . . . . . . . . . . xviiChapter 1. Planning for and designing DB2 applications . . . . . . . . . . . . . . . 1 Application and SQL release incompatibilities for migration from Version 8 . . . . . . . . . . . . . . 1Changes in BIND PACKAGE and BIND PLAN defaults (change introduced in DB2 9). . . . . . . . . . 2Plan for the XML data type (change introduced in DB2 9) . . . . . . . . . . . . . . . . . . . 2Changes to XMLNAMESPACES (change introduced in DB2 9). . . . . . . . . . . . . . . . . . 2Changes to serialization of empty elements (change introduced in DB2 9) . . . . . . . . . . . . . . 2Availability of LOB or XML values in JDBC or SQLJ applications with progressive streaming (change introducedin DB2 9) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2Adjust applications that depend on error information that is returned from DB2-supplied stored procedures(change introduced in DB2 9) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3Some materialized query tables should be dropped (change introduced in DB2 9) . . . . . . . . . . . 3Fully define objects (change introduced in DB2 9) . . . . . . . . . . . . . . . . . . . . . . 3Changes to PL/I applications with no DECLARE VARIABLE statements(change introduced in DB2 9) . . . . 4Automatic rebind of plans and packages created before Version 6 . . . . . . . . . . . . . . . . 4IBMREQD is no longer reliable as a release dependency mark . . . . . . . . . . . . . . . . . . 4Changed behavior for ODBC data conversion for the SQL BINARY type(change introduced in DB2 9) . . . . 4Changed behavior of the INSERT statement with the OVERRIDING USER VALUE clause (change introduced inDB2 9 ) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4DESCRIBE no longer returns LONG type values (change introduced in DB2 9) . . . . . . . . . . . . 5DB2 enforces the restrictions about where a host variable array can be specified (change introduced in DB2 9) . 5DEBUGSESSION system privilege required for continued debugging of SQL procedures (change introduced inDB2 9) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5Changes to the result length of the DECRYPT function (change introduced in DB2 9) . . . . . . . . . . 5Changes to the result length and data type of some functions(change introduced in DB2 9) . . . . . . . . 5COLTYPE column in SYSIBM.SYSCOLUMNS and SYSIBM.SYSCOLUMNS HIST for LONG column types(change introduced in DB2 9 ) . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6CREATEDBY column in SYSIBM.SYSDATATYPES, SYSIBM.SYSROUTINES, SYSIBM.SYSSEQUENCES, andSYSIBM.SYSTRIGGERS (change introduced in DB2 9 ) . . . . . . . . . . . . . . . . . . . . 6DB2 returns all DSNWZP output in the same format as DB2 parameters (change introduced in DB2 9) . . . . 6DB2 enforces the restriction that row IDs are not compatible with character strings when they are used with aset operator (change introduced in DB2 9 ) . . . . . . . . . . . . . . . . . . . . . . . . 6Database privileges on the DSNDB04 database now give you those privileges on all implicitly created databases(change introduced in DB2 9 ) . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6Implicitly created objects that are associated with LOB columns require additional privileges (change introducedin DB2 9 ) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6Adjust applications to use LRHCLR instead of LGDISCLR (change introduced in DB2 9) . . . . . . . . . 7Changed behavior for the CREATE statement (change introduced in DB2 9 ) . . . . . . . . . . . . . 7The DECLARE statement and the work file database (change introduced in DB2 9). . . . . . . . . . . 7Adjust monitor programs that access OP buffers (change introduced in DB2 9) . . . . . . . . . . . . 7Changed behavior for system-required objects (change introduced in DB2 9) . . . . . . . . . . . . . 7Changes to INSERT, UPDATE, or DELETE statements on some indexes(change introduced in DB2 9). . . . . 9LOBs with a maximum length greater than 1 GB can now be logged(change introduced in DB2 9) . . . . . 9DB2 returns an error when a LOB value is specified for an argument to a stored procedure and the argumentvalue is longer than the target parameter and the excess is not trailing blanks (change introduced in DB2 9 ) . . 9Changes to string formatting of decimal data . . . . . . . . . . . . . . . . . . . . . . . 9Change to maximum result length of VARCHAR function . . . . . . . . . . . . . . . . . . . 10 Copyright IBM Corp. 1983, 2017iii

Leading or trailing blanks from the VARCHAR FORMAT function format string are no longer removed(change introduced in DB2 9 ) . . . . . . . . . . . . . . . . . . . . . . . . . . . .Changes to VARCHAR FORMAT function length attribute . . . . . . . . . . . . . . . . . .New format element for VARCHAR FORMAT function . . . . . . . . . . . . . . . . . . .Change to result of VARCHAR FORMAT function with “HH12” format element . . . . . . . . . . .Change to result of VARCHAR FORMAT function with “J” format element . . . . . . . . . . . . .New supported data types for VARCHAR FORMAT function . . . . . . . . . . . . . . . . .Change to maximum result length of REPEAT function. . . . . . . . . . . . . . . . . . . .Change to maximum result length of XMLTABLE function . . . . . . . . . . . . . . . . . .DB2 drops certain indexes when a unique constraint is dropped (change introduced in DB2 9 ) . . . . . .DB2 enforces restriction on specifying a CAST FROM clause for some forms of CREATE FUNCTIONstatements(change introduced in Version 9.1) . . . . . . . . . . . . . . . . . . . . . . .DB2 enforces restrictions on specifying the AS LOCATOR clause and TABLE LIKE clause (change introduced inDB2 9) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .DB2 enforces restriction on the CCSID parameter for the DECRYPT BIT and DECRYPT BINARY functions(change introduced in DB2 9) . . . . . . . . . . . . . . . . . . . . . . . . . . . .Changed behavior of CREATE PROCEDURE for an SQL procedure (change introduced in DB2 9) . . . . .Explicitly qualify names of variables, parameters, and columns in SQL procedures (change introduced in DB2 9) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Make any necessary program changes for possibly different values for RETURNED SQLSTATE andDB2 RETURNED SQLCODE (change introduced in DB2 9 ) . . . . . . . . . . . . . . . . . .SQLSTATE and SQLCODE SQL variables after a GET DIAGNOSTICS statement (change introduced in Version9.1) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Coding multiple SQL statements in a handler body (change introduced in DB2 9) . . . . . . . . . . .Unhandled warnings (change introduced in DB2 9 ) . . . . . . . . . . . . . . . . . . . . .Change your programs to handle any changed messages from SQL procedures (change introduced in Version9.1) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Enhanced data type checking for zero-length characters (change introduced in DB2 9) . . . . . . . . .Adding a column generates a new table space version (change introduced in DB2 9) . . . . . . . . . .You cannot add a column and issue SELECT, INSERT, UPDATE, or DELETE statements in the same commitscope . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .CAST FROM clause of CREATE FUNCTION statement for SQL functions is no longer supported (changeintroduced in DB2 9 ) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Specifying ALTER DATABASE STOGROUP for work file databases (change introduced in DB2 9) . . . . .DB2 enforces restrictions about where an INTO clause can be specified (change introduced in DB2 9 ) . . . .Change to how a positive, signed integer in an ORDER BY clause is treated. . . . . . . . . . . . .Binding DBRMs directly into plans is no longer supported . . . . . . . . . . . . . . . . . .Some BIND PLAN and REBIND PLAN command options are no longer supported . . . . . . . . . .Plans and packages should be converted to DRDA protocol . . . . . . . . . . . . . . . . . .Change to GRANT statement . . . . . . . . . . . . . . . . . . . . . . . . . . . .Change to IMMEDWRITE option of BIND PACKAGE command . . . . . . . . . . . . . . . .Changes to conversion of special characters in collection IDs and package names . . . . . . . . . . .Changes to the RELEASE bind option . . . . . . . . . . . . . . . . . . . . . . . . .Database metadata stored procedures are converted to Unicode . . . . . . . . . . . . . . . . .AUTHID is the default owner of packages that are bound by DSNTRIN . . . . . . . . . . . . . .New default DEFINE attribute for dependent objects . . . . . . . . . . . . . . . . . . . .Change for creating partitioned table spaces . . . . . . . . . . . . . . . . . . . . . . .Change to default for CREATE TABLESPACE statements . . . . . . . . . . . . . . . . . . .Upgrade to supported COBOL and PL/I compilers . . . . . . . . . . . . . . . . . . . . .GRAPHIC and NOGRAPHIC SQL processing options are removed. . . . . . . . . . . . . . . .SELECT FROM data change statements in BEFORE triggers no longer supported . . . . . . . . . . .RETURN statement in scalar functions must follow option-list . . . . . . . . . . . . . . . . .Changes to ROUND TIMESTAMP and TRUNC TIMESTAMP functions . . . . . . . . . . . . . .Changes to result of NEXT DAY function . . . . . . . . . . . . . . . . . . . . . . . .Changes to MONTHS BETWEEN function . . . . . . . . . . . . . . . . . . . . . . . .Changes to TIMESTAMPDIFF function . . . . . . . . . . . . . . . . . . . . . . . . .Static SQL applications that use parallelism. . . . . . . . . . . . . . . . . . . . . . . .Enforced SELECT authorization checking for UPDATE and DELETE statements . . . . . . . . . . .Increased limit for work file record length . . . . . . . . . . . . . . . . . . . . . . . .New restrictions for EXPLAIN tables . . . . . . . . . . . . . . . . . . . . . . . . . .MEMBER CLUSTER table spaces indicated by MEMBER CLUSTER column . . . . . . . . . . . . ivApplication Programming and SQL 2222222

Changed values for the modification level in the product signature. . . . . . . . . . . . . . . .Changed behavior for the CREATE FUNCTION statement . . . . . . . . . . . . . . . . . .Different SQLSTATE returned for some DELETE or UPDATE statements . . . . . . . . . . . . . .Changed default behavior of multiple-row inserts for ODBC z/OS applications . . . . . . . . . . .Changes to ALTER TABLESPACE statement error codes . . . . . . . . . . . . . . . . . . .Change to CREATE and ALTER statements . . . . . . . . . . . . . . . . . . . . . . . .Change to DESCRIBE statement . . . . . . . . . . . . . . . . . . . . . . . . . . .New restrictions on using DSNTIAUL . . . . . . . . . . . . . . . . . . . . . . . . .Changes to SYSROUTINES . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Catalog restructured . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Changed data type for an untyped parameter marker . . . . . . . . . . . . . . . . . . . .Changes to handling of special values Infinity, sNaN, and NaN . . . . . . . . . . . . . . . . .Changes for INSTEAD OF triggers . . . . . . . . . . . . . . . . . . . . . . . . . .Change to positioned update or delete statements . . . . . . . . . . . . . . . . . . . . .Change to stored procedure parameter values returned to non-Java clients . . . . . . . . . . . . .Change to results of JDBC method PreparedStatement.setTimestamp . . . . . . . . . . . . . . .Change in how DB2 returns stored procedure output parameter data to remote clients . . . . . . . . .Changes to datetime built-in functions . . . . . . . . . . . . . . . . . . . . . . . . .SQLCODE change for subsequent CAF CONNECT attempts . . . . . . . . . . . . . . . . . .Change to serialization of an empty XML element . . . . . . . . . . . . . . . . . . . . .Data types of output arguments from a stored procedure call in a Java application . . . . . . . . . .Change to IBM Data Server Driver for JDBC and SQLJ handling of TIMESTAMP WITH TIME ZONE data typeDelimiters used for accessing tables on DB2 for Linux, UNIX, and Windows . . . . . . . . . . . .Changes to the LTRIM, RTRIM and STRIP scalar functions . . . . . . . . . . . . . . . . . .Qualify user-defined function names . . . . . . . . . . . . . . . . . . . . . . . . . .SQLCODE changes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .SQL reserved words . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Rebind of packages created before DB2 Version 8 when a view definition contains a literal in the select list . .Application and SQL release incompatibilities for migration from DB2 9 . . . . . . . . . . . . . . .Automatic rebind of plans and packages created before Version 6 . . . . . . . . . . . . . . . .IBMREQD is no longer reliable as a release dependency mark . . . . . . . . . . . . . . . . .Changes to string formatting of decimal data . . . . . . . . . . . . . . . . . . . . . . .Change to maximum result length of VARCHAR function . . . . . . . . . . . . . . . . . . .Changes to VARCHAR FORMAT function length attribute . . . . . . . . . . . . . . . . . .New format element for VARCHAR FORMAT function . . . . . . . . . . . . . . . . . . .Change to result of VARCHAR FORMAT function with “HH12” format element . . . . . . . . . . .Change to result of VARCHAR FORMAT function with “J” format element . . . . . . . . . . . . .New supported data types for VARCHAR FORMAT function . . . . . . . . . . . . . . . . .Change to maximum result length of REPEAT function. . . . . . . . . . . . . . . . . . . .Change to maximum result length of XMLTABLE function . . . . . . . . . . . . . . . . . .Change to how a positive, signed integer in an ORDER BY clause is treated. . . . . . . . . . . . .Binding DBRMs directly into plans is no longer supported . . . . . . . . . . . . . . . . . .Some BIND PLAN and REBIND PLAN command options are no longer supported . . . . . . . . . .Plans and packages should be converted to DRDA protocol . . . . . . . . . . . . . . . . . .Change to GRANT statement . . . . . . . . . . . . . . . . . . . . . . . . . . . .Change to IMMEDWRITE option of BIND PACKAGE command . . . . . . . . . . . . . . . .Changes to conversion of special characters in collection IDs and package names . . . . . . . . . . .Changes to the RELEASE bind option . . . . . . . . . . . . . . . . . . . . . . . . .Database metadata stored procedures are converted to Unicode . . . . . . . . . . . . . . . . .AUTHID is the default owner of packages that are bound by DSNTRIN . . . . . . . . . . . . . .New default DEFINE attribute for dependent objects . . . . . . . . . . . . . . . . . . . .Change for creating partitioned table spaces . . . . . . . . . . . . . . . . . . . . . . .Change to default for CREATE TABLESPACE statements . . . . . . . . . . . . . . . . . . .Change to default SEGSIZE value for universal table spaces . . . . . . . . . . . . . . . . . .Upgrade to supported COBOL and PL/I compilers . . . . . . . . . . . . . . . . . . . . .GRAPHIC and NOGRAPHIC SQL processing options are removed. . . . . . . . . . . . . . . .SELECT FROM data change statements in BEFORE triggers no longer supported . . . . . . . . . . .RETURN statement in scalar functions must follow option-list . . . . . . . . . . . . . . . . .Changed behavior of LOCATE IN STRING function . . . . . . . . . . . . . . . . . . . .Changes to ROUND TIMESTAMP and TRUNC TIMESTAMP functions . . . . . . . . . . . . . .Changes to result of NEXT DAY function . . . . . . . . . . . . . . . . . . . . . . . 9393939404041414141424242424242v

Changes to MONTHS BETWEEN function . . . . . . . . . . . . . . . . . . . .Changes to TIMESTAMPDIFF function . . . . . . . . . . . . . . . . . . . . .Static SQL applications that use parallelism. . . . . . . . . . . . . . . . . . . .Enforced SELECT authorization checking for UPDATE and DELETE statements . . . . . . .Increased limit for work file record length . . . . . . . . . . . . . . . . . . . .New restrictions for EXPLAIN tables . . . . . . . . . . . . . . . . . . . . . .MEMBER CLUSTER table spaces indicated by MEMBER CLUSTER column . . . . . . . .Changed values for the modification level in the product signature. . . . . . . . . . . .Changed behavior for the CREATE FUNCTION statement . . . . . . . . . . . . . .Different SQLSTATE returned for some DELETE or UPDATE statements . . . . . . . . . .Changed default behavior of multiple-row inserts for ODBC z/OS applications . . . . . . .Changes to ALTER TABLESPACE statement error codes . . . . . . . . . . . . . . .Change to CREATE and ALTER statements . . . . . . . . . . . . . . . . . . . .Change to ALTER PROCEDURE statement . . . . . . . . . . . . . . . . . . . .Change to DESCRIBE statement . . . . . . . . . . . . . . . . . . . . . . .New restrictions on using DSNTIAUL . . . . . . . . . . . . . . . . . . . . .Changes to SYSROUTINES . . . . . . . . . . . . . . . . . . . . . . . . .Catalog restructured . . . . . . . . . . . . . . . . . . . . . . . . . . .Changed data type for an untyped parameter marker . . . . . . . . . . . . . . . .Changes to handling of special values Infinity, sNaN, and NaN . . . . . . . . . . . . .Changes for INSTEAD OF triggers . . . . . . . . . . . . . . . . . . . . . .Change to positioned update or delete statements . . . . . . . . . . . . . . . . .Change to stored procedure parameter values returned to non-Java clients . . . . . . . . .Change to results of JDBC method PreparedStatement.setTimestamp . . . . . . . . . . .Change to behavior of comma operator in XQuery path expression. . . . . . . . . . . .Change in how DB2 returns stored procedure output parameter data to remote clients . . . . .Change to IBM Data Server Driver for JDBC and SQLJ handling of TIMESTAMP WITH TIME ZONEChanges to datetime built-in functions . . . . . . . . . . . . . . . . . . . . .SQLCODE change for subsequent CAF CONNECT attempts . . . . . . . . . . . . . .Delimiters used for accessing tables on DB2 for Linux, UNIX, and Windows . . . . . . . .Changes to the LTRIM, RTRIM and STRIP scalar functions . . . . . . . . . . . . . .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 . . . .Changes that invalidate 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 . . . . . . . . . . . . . . . . . . . . . .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 . . . . . . . . . . . . 43. . . . 43. . . . 43. . . . 43. . . . 44. . . . 44. . . . 44. . . . 44. . . . 45. . . . 45. . . . 45. . . . 45. . . . 45. . . . 46. . . . 46. . . . 46. . . . 46. . . . 46. . . . 46. . . . 46. . . . 46. . . . 47. . . . 47. . . . 47. . . . 48. . . . 48data type 51. . . . 51. . . . 51. . . . 52. . . . 52. . . . 54. . . . 54. . . . 54. . . . 55. . . . 55. . . . 58. . . . 58. . . . 60. . . . 61. . . . 61. . . . 62. . . . 69. . . . 71. . . . 72. . . . 73. . . . 73. . . . 74. . . . 75Chapter 2. Connecting to DB2 from your application program . . . . . . . . . . . . 77Invoking the call attachment facility . . . . . . . . . . . . . . . . . . . . . . . . . . . 78Call attachment facility . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 81Making the CAF language interface (DSNALI) available . . . . . . . . . . . . . . . . . . . 84Requirements for programs that use CAF . . . . . . . . . . . . . . . . . . . . . . . . 85How CAF modifies the content of registers . . . . . . . . . . . . . . . . . . . . . . . . 86Implicit connections to CAF . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 86CALL DSNALI statement parameter list . . . . . . . . . . . . . . . . . . . . . . . . . 87Summary of CAF behavior . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 89CAF connection functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 90Turning on a CAF trace . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 102viApplication Programming and SQL Guide

CAF return codes and reason codes . . . . . . . . .Sample CAF scenarios . . . . . . . . . . . . .Examples of invoking CAF . . . . . . . . . . . .Invoking the Resource Recovery Services attachment facility. .Resource Recovery Services attachment facility . . . . .Making the RRSAF language interface (DSNRLI) available .Requirements for programs that use RRSAF . . . . . .How RRSAF modifies the content of registers . . . . .Implicit connections to RRSAF. . . . . . . . . . .CALL DSNRLI statement parameter list . . . . . . .Summary of RRSAF behavior . . . . . . . . . . .RRSAF connection functions . . . . . . . . . . .RRSAF return codes and reason codes . . . . . . . .Sample RRSAF scenarios . . . . . . . . . . . .Program examples for RRSAF . . . . . . . . . . .Universal language interface (DSNULI). . . . . . . . .Link-editing an application with DSNULI . . . . . . .Controlling the CICS attachment facility from an application .Detecting whether the CICS attachment facility is operationalImproving thread reuse in CICS applications . . . . . 59160160161Chapter 3. Overview of programming applications that access DB2 for z/OS data . . . 163 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 executedDefining 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 . . . .Accessing an application defaults module . . . . . . . . . . . . . . . . .Compatibility of SQL and language data types . . . . . . . . . . . . . . .Using host variables in SQL statements. . . . . . . . . . . . . . . . . .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 . . . . . . . . . . . . . .Using host variable arrays in SQL statements. . . . . . . . . . . . . . . .Retrieving multiple rows of data into host variable arrays . . . . . . . . . .Inserting multiple rows of data from host variable arrays. . . . . . . . . . .Inserting null values into columns by using indicator variables or arrays . . . . . .Retrieving a single row of data into a host structure . . . . . . . . . . . . .Including dynamic SQL in your program . . . . . . . . . . . . . . . . .Differences between static and dynamic SQL . . . . . . . . . . . . . . .Possible host languages for dynamic SQL applications. . . . . . . . . . . .Including dynamic SQL for non-SELECT statements in your program . . . . . .Including dynamic SQL for fixed-list SELECT statements in your program . . . . .Including dynamic SQL for varying-list SELECT statements in your program . . . .Dynamically executing an SQL statement by using EXECUTE IMMEDIATE . . . .Dynamically executing an SQL statement by using PREPARE and EXECUTE . . . .Dynamically executing a data change statement . . . . . . . . . . . . . .Dynamically executing a statement with parameter markers by using the SQLDA . .Checking the execution of SQL statements. . . . . . . . . . . . . . . . .Checking the execution of SQL statements by using the SQLCA . . . . . . . . . . . . . . . . . . . . . . . . . . . .successfully . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 202203206223224227229230231vii

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 statementHandling SQL error codes . . . . . . . . . . . . . . . . . . . . . .Arithmetic and conversion errors . . . . . . . . . . . . . . . . . . .Writing applications that enable users to create and modify tables. . . . . . . . .Saving SQL statements that are translated from 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 . . . . . . . . . . . . .Examples programs that call stored procedures . . . . . . . . . . . . . . .235236237242243243243244244249251254Chapter 4. Programming assembler applications that issue SQL statements . . . . . 255Assembler programming examples . . . . . . . . . . .Defining the SQL communications area, SQLSTATE, and SQLCODEDefining SQL descriptor areas in assembler . . . . . . . .Declaring host variables and indicator variables in assembler . .Host variables in assembler . . . . . . . . . . . .Indicator variables in assembler . . . . . . . . . . .Equivalent SQL and assembler data types . . . . . . . . .Macros for assembler applications . . . . . . . . . . .in. . . .assembler. . . . . . . . . . . . . . . . . . .260260261262262267268274Chapter 5. Programming C and C applications that issue SQL statements . . . . . 275C and C programming examples . . . . . . . . . . . . . . . . .Sample dynamic and static SQL in a C program . . . . . . . . . . . .Example C p

A pplica tion Programming and SQL Guide SC19-2969-13 IBM. DB2 10 for z/OS A pplica tion Programming and SQL Guide SC19-2969-13 IBM. Notes Befor e using this information and the pr oduct it su