Application Programming Guide and Reference for Java

[Pages:679]DB2 10 for z/OS

Application Programming Guide and Reference for Java

IBM

SC19-2970-12

DB2 10 for z/OS

Application Programming Guide and Reference for Java

IBM

SC19-2970-12

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

October 12, 2017 edition This edition applies to DB2 10 for z/OS (product number 5605-DB2), DB2 10 for z/OS Value Unit Edition (product number 5697-P31), 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 1998, 2017. US Government Users Restricted Rights ? Use, duplication or disclosure restricted by GSA ADP Schedule Contract with IBM Corp.

Contents

About this information . . . . . . . . . . . . . . . . . . . . . . . . . . . . . ix

Who should read this information . . . . . . . . . . . . . . . . . . . . . . . . . . . . ix DB2 Utilities Suite for z/OS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . ix Terminology and citations. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . ix Accessibility features for DB2 10 for z/OS . . . . . . . . . . . . . . . . . . . . . . . . . x How to send your comments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . x How to read syntax diagrams . . . . . . . . . . . . . . . . . . . . . . . . . . . . . x

Chapter 1. Java application development for IBM data servers . . . . . . . . . . . . 1

Chapter 2. Supported drivers for JDBC and SQLJ. . . . . . . . . . . . . . . . . . 3

JDBC driver and database version compatibility . . . . . . . . . . . . . . . . . . . . . . . 4 How to find the IBM Data Server Driver for JDBC and SQLJ version . . . . . . . . . . . . . . . . 5 | DB2 for z/OS and IBM Data Server Driver for JDBC and SQLJ levels . . . . . . . . . . . . . . . . 6 DB2 and IBM Data Server Driver for JDBC and SQLJ levels . . . . . . . . . . . . . . . . . . . 6

Chapter 3. JDBC application programming. . . . . . . . . . . . . . . . . . . . . 9

Example of a simple JDBC application . . . . . . . . . . . . . . . . . . . . . . . . . . . 9 How JDBC applications connect to a data source . . . . . . . . . . . . . . . . . . . . . . . 11

Connecting to a data source using the DriverManager interface with the IBM Data Server Driver for JDBC and SQLJ . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13 Connecting to a data source using the DataSource interface . . . . . . . . . . . . . . . . . . 20 How to determine which type of IBM Data Server Driver for JDBC and SQLJ connectivity to use . . . . . 22 JDBC connection objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23 Creating and deploying DataSource objects . . . . . . . . . . . . . . . . . . . . . . . . 24 Java packages for JDBC support . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25 Learning about a data source using DatabaseMetaData methods. . . . . . . . . . . . . . . . . . 26 | DatabaseMetaData methods for identifying the type of data server . . . . . . . . . . . . . . . . 27 Variables in JDBC applications . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28 Comments in a JDBC application . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29 JDBC interfaces for executing SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29 Creating and modifying database objects using the Statement.executeUpdate method . . . . . . . . . 30 Updating data in tables using the PreparedStatement.executeUpdate method . . . . . . . . . . . . 31 JDBC executeUpdate methods against a DB2 for z/OS server. . . . . . . . . . . . . . . . . . 33 Making batch updates in JDBC applications . . . . . . . . . . . . . . . . . . . . . . . 33 Learning about parameters in a PreparedStatement using ParameterMetaData methods . . . . . . . . . 37 Data retrieval in JDBC applications . . . . . . . . . . . . . . . . . . . . . . . . . . 39 Calling stored procedures in JDBC applications . . . . . . . . . . . . . . . . . . . . . . 53 LOBs in JDBC applications with the IBM Data Server Driver for JDBC and SQLJ . . . . . . . . . . . 59 ROWIDs in JDBC with the IBM Data Server Driver for JDBC and SQLJ . . . . . . . . . . . . . . 65 Update and retrieval of timestamps with time zone information in JDBC applications . . . . . . . . . 66 Distinct types in JDBC applications . . . . . . . . . . . . . . . . . . . . . . . . . . 75 Savepoints in JDBC applications . . . . . . . . . . . . . . . . . . . . . . . . . . . 75 Retrieval of automatically generated keys in JDBC applications . . . . . . . . . . . . . . . . . 77 Named parameter markers in JDBC applications . . . . . . . . . . . . . . . . . . . . . . 80 | Retrieving JSON documents from a ResultSet . . . . . . . . . . . . . . . . . . . . . . . 84 Providing extended client information to the data source with IBM Data Server Driver for JDBC and SQLJ-only methods . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 87 Providing extended client information to the data source with client info properties . . . . . . . . . . 88 Extended parameter information with the IBM Data Server Driver for JDBC and SQLJ . . . . . . . . . . 91 Using DB2PreparedStatement methods or constants to provide extended parameter information . . . . . . 92 Using DB2ResultSet methods or DB2PreparedStatement constants to provide extended parameter information 94 XML data in JDBC applications . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 95 XML column updates in JDBC applications . . . . . . . . . . . . . . . . . . . . . . . . 96 XML data retrieval in JDBC applications. . . . . . . . . . . . . . . . . . . . . . . . . 98

? Copyright IBM Corp. 1998, 2017

iii

Invocation of routines with XML parameters in Java applications . . . . . . . . . . . . . . . . 102 Binary XML format in Java applications . . . . . . . . . . . . . . . . . . . . . . . . 103 Java support for XML schema registration and removal . . . . . . . . . . . . . . . . . . . 105 Bidirectional (BiDi) text layout transformation . . . . . . . . . . . . . . . . . . . . . . . 107 Inserting data from file reference variables into tables in JDBC applications . . . . . . . . . . . . . 109 Transaction control in JDBC applications . . . . . . . . . . . . . . . . . . . . . . . . . 111 IBM Data Server Driver for JDBC and SQLJ isolation levels . . . . . . . . . . . . . . . . . . 111 Committing or rolling back JDBC transactions . . . . . . . . . . . . . . . . . . . . . . 112 Default JDBC autocommit modes . . . . . . . . . . . . . . . . . . . . . . . . . . . 112 Exceptions and warnings under the IBM Data Server Driver for JDBC and SQLJ . . . . . . . . . . . . 113 Handling an SQLException under the IBM Data Server Driver for JDBC and SQLJ . . . . . . . . . . 116 Handling an SQLWarning under the IBM Data Server Driver for JDBC and SQLJ. . . . . . . . . . . 119 Retrieving information from a BatchUpdateException . . . . . . . . . . . . . . . . . . . . 120 Memory use for IBM Data Server Driver for IBM Data Server Driver for JDBC and SQLJ type 2 connectivity on DB2 for z/OS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 122 Disconnecting from data sources in JDBC applications. . . . . . . . . . . . . . . . . . . . . 123

Chapter 4. SQLJ application programming . . . . . . . . . . . . . . . . . . . . 125

Example of a simple SQLJ application . . . . . . . . . . . . . . . . . . . . . . . . . . 125 Connecting to a data source using SQLJ . . . . . . . . . . . . . . . . . . . . . . . . . 127

SQLJ connection technique 1: JDBC DriverManager interface . . . . . . . . . . . . . . . . . 127 SQLJ connection technique 2: JDBC DriverManager interface . . . . . . . . . . . . . . . . . 129 SQLJ connection technique 3: JDBC DataSource interface . . . . . . . . . . . . . . . . . . . 130 SQLJ connection technique 4: JDBC DataSource interface . . . . . . . . . . . . . . . . . . . 132 SQLJ connection technique 5: Use a previously created connection context . . . . . . . . . . . . . 133 SQLJ connection technique 6: Use the default connection . . . . . . . . . . . . . . . . . . . 134 Java packages for SQLJ support . . . . . . . . . . . . . . . . . . . . . . . . . . . . 134 Variables in SQLJ applications . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 135 Indicator variables in SQLJ applications . . . . . . . . . . . . . . . . . . . . . . . . . 136 Comments in an SQLJ application . . . . . . . . . . . . . . . . . . . . . . . . . . . 140 SQL statement execution in SQLJ applications . . . . . . . . . . . . . . . . . . . . . . . 140 Creating and modifying database objects in an SQLJ application . . . . . . . . . . . . . . . . 141 Performing positioned UPDATE and DELETE operations in an SQLJ application . . . . . . . . . . . 141 Data retrieval in SQLJ applications . . . . . . . . . . . . . . . . . . . . . . . . . . 151 Calling stored procedures in SQLJ applications . . . . . . . . . . . . . . . . . . . . . . 162 LOBs in SQLJ applications with the IBM Data Server Driver for JDBC and SQLJ . . . . . . . . . . . 164 SQLJ and JDBC in the same application . . . . . . . . . . . . . . . . . . . . . . . . 166 Controlling the execution of SQL statements in SQLJ . . . . . . . . . . . . . . . . . . . . 170 ROWIDs in SQLJ with the IBM Data Server Driver for JDBC and SQLJ . . . . . . . . . . . . . . 170 TIMESTAMP WITH TIME ZONE values in SQLJ applications . . . . . . . . . . . . . . . . . 172 Distinct types in SQLJ applications . . . . . . . . . . . . . . . . . . . . . . . . . . 173 Savepoints in SQLJ applications . . . . . . . . . . . . . . . . . . . . . . . . . . . 174 XML data in SQLJ applications . . . . . . . . . . . . . . . . . . . . . . . . . . . . 175 XML column updates in SQLJ applications . . . . . . . . . . . . . . . . . . . . . . . 176 XML data retrieval in SQLJ applications . . . . . . . . . . . . . . . . . . . . . . . . 178 XMLCAST in SQLJ applications . . . . . . . . . . . . . . . . . . . . . . . . . . . 179 Inserting data from file reference variables into tables in SQLJ applications . . . . . . . . . . . . . . 180 SQLJ utilization of SDK for Java Version 5 function. . . . . . . . . . . . . . . . . . . . . . 181 Transaction control in SQLJ applications . . . . . . . . . . . . . . . . . . . . . . . . . 183 Setting the isolation level for an SQLJ transaction . . . . . . . . . . . . . . . . . . . . . 184 Committing or rolling back SQLJ transactions . . . . . . . . . . . . . . . . . . . . . . 184 Handling SQL errors and warnings in SQLJ applications . . . . . . . . . . . . . . . . . . . . 184 Handling SQL errors in an SQLJ application . . . . . . . . . . . . . . . . . . . . . . . 185 Handling SQL warnings in an SQLJ application . . . . . . . . . . . . . . . . . . . . . . 185 Closing the connection to a data source in an SQLJ application . . . . . . . . . . . . . . . . . . 186

Chapter 5. Java stored procedures and user-defined functions . . . . . . . . . . . 189

Setting up the environment for Java routines . . . . . . . . . . . . . . . . . . . . . . . . 189 Setting up the WLM application environment for Java routines. . . . . . . . . . . . . . . . . 190 Runtime environment for Java routines . . . . . . . . . . . . . . . . . . . . . . . . . 193

iv Application Programming Guide and Reference for Java

Defining Java routines and JAR files to DB2 . . . . . . . . . . . . . . . . . . . . . . . . 196 Definition of a Java routine to DB2 . . . . . . . . . . . . . . . . . . . . . . . . . . 197 Definition of a JAR file for a Java routine to DB2 . . . . . . . . . . . . . . . . . . . . . 201

Java routine programming . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 201 Differences between Java routines and stand-alone Java programs . . . . . . . . . . . . . . . . 202 Differences between Java routines and other routines . . . . . . . . . . . . . . . . . . . . 202 Static and non-final variables in a Java routine . . . . . . . . . . . . . . . . . . . . . . 203 Writing a Java stored procedure to return result sets . . . . . . . . . . . . . . . . . . . . 204

Techniques for testing a Java routine . . . . . . . . . . . . . . . . . . . . . . . . . . 205

Chapter 6. Preparing and running JDBC and SQLJ programs. . . . . . . . . . . . 209

Program preparation for JDBC programs . . . . . . . . . . . . . . . . . . . . . . . . . 209 Program preparation for SQLJ programs . . . . . . . . . . . . . . . . . . . . . . . . . 209 Binding SQLJ applications to access multiple database servers . . . . . . . . . . . . . . . . . . 210 Program preparation for Java routines . . . . . . . . . . . . . . . . . . . . . . . . . . 212

Preparation of Java routines with no SQLJ clauses . . . . . . . . . . . . . . . . . . . . . 212 Preparation of Java routines with SQLJ clauses . . . . . . . . . . . . . . . . . . . . . . 214 Creating JAR files for Java routines . . . . . . . . . . . . . . . . . . . . . . . . . . 217 Running JDBC and SQLJ programs . . . . . . . . . . . . . . . . . . . . . . . . . . . 218

Chapter 7. JDBC and SQLJ reference information. . . . . . . . . . . . . . . . . 219

Data types that map to database data types in Java applications . . . . . . . . . . . . . . . . . 219 Date, time, and timestamp values that can cause problems in JDBC and SQLJ applications . . . . . . . 226 Data loss for timestamp data in JDBC and SQLJ applications . . . . . . . . . . . . . . . . . 230 Retrieval of special values from DECFLOAT columns in Java applications . . . . . . . . . . . . . 231 Use of PreparedStatement.setTimestamp to set values in TIMESTAMP WITH TIME ZONE columns . . . . 233

Properties for the IBM Data Server Driver for JDBC and SQLJ . . . . . . . . . . . . . . . . . . 234 Common IBM Data Server Driver for JDBC and SQLJ properties for all supported database products . . . . 235 Common IBM Data Server Driver for JDBC and SQLJ properties for DB2 servers. . . . . . . . . . . 263 Common IBM Data Server Driver for JDBC and SQLJ properties for DB2 for z/OS and IBM Informix. . . . 276 Common IBM Data Server Driver for JDBC and SQLJ properties for IBM Informix and Db2 on Linux, UNIX, and Windows systems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 278 IBM Data Server Driver for JDBC and SQLJ properties for Db2 on Linux, UNIX, and Windows systems . . . 278 IBM Data Server Driver for JDBC and SQLJ properties for DB2 for z/OS . . . . . . . . . . . . . 282 IBM Data Server Driver for JDBC and SQLJ properties for IBM Informix . . . . . . . . . . . . . 290

IBM Data Server Driver for JDBC and SQLJ configuration properties . . . . . . . . . . . . . . . . 295 Driver support for JDBC APIs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 320 IBM Data Server Driver for JDBC and SQLJ support for SQL escape syntax . . . . . . . . . . . . . 346 SQLJ statement reference information . . . . . . . . . . . . . . . . . . . . . . . . . . 346

SQLJ clause . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 346 SQLJ host-expression . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 346 SQLJ implements-clause . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 348 SQLJ with-clause . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 348 SQLJ connection-declaration-clause . . . . . . . . . . . . . . . . . . . . . . . . . . 350 SQLJ iterator-declaration-clause . . . . . . . . . . . . . . . . . . . . . . . . . . . 351 SQLJ executable-clause . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 353 SQLJ context-clause . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 353 SQLJ statement-clause . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 354 SQLJ SET-TRANSACTION-clause . . . . . . . . . . . . . . . . . . . . . . . . . . 356 SQLJ assignment-clause . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 357 SQLJ iterator-conversion-clause . . . . . . . . . . . . . . . . . . . . . . . . . . . 358 Interfaces and classes in the sqlj.runtime package . . . . . . . . . . . . . . . . . . . . . . 358 sqlj.runtime.ConnectionContext interface . . . . . . . . . . . . . . . . . . . . . . . . 359 sqlj.runtime.ForUpdate interface . . . . . . . . . . . . . . . . . . . . . . . . . . . 364 sqlj.runtime.NamedIterator interface. . . . . . . . . . . . . . . . . . . . . . . . . . 364 sqlj.runtime.PositionedIterator interface. . . . . . . . . . . . . . . . . . . . . . . . . 365 sqlj.runtime.ResultSetIterator interface . . . . . . . . . . . . . . . . . . . . . . . . . 365 sqlj.runtime.Scrollable interface . . . . . . . . . . . . . . . . . . . . . . . . . . . 368 sqlj.runtime.AsciiStream class . . . . . . . . . . . . . . . . . . . . . . . . . . . . 370 sqlj.runtime.BinaryStream class . . . . . . . . . . . . . . . . . . . . . . . . . . . 371

Contents v

sqlj.runtime.CharacterStream class . . . . . . . . . . . . . . . . . . . . . . . . . . 371 sqlj.runtime.ExecutionContext class . . . . . . . . . . . . . . . . . . . . . . . . . . 372 sqlj.runtime.SQLNullException class . . . . . . . . . . . . . . . . . . . . . . . . . . 381 sqlj.runtime.StreamWrapper class . . . . . . . . . . . . . . . . . . . . . . . . . . . 381 sqlj.runtime.UnicodeStream class . . . . . . . . . . . . . . . . . . . . . . . . . . . 382 IBM Data Server Driver for JDBC and SQLJ extensions to JDBC . . . . . . . . . . . . . . . . . 383 DBBatchUpdateException interface . . . . . . . . . . . . . . . . . . . . . . . . . . 385 DB2BaseDataSource class . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 386 | DB2Binder class . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 392 DB2BlobFileReference class . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 393 DB2CallableStatement interface . . . . . . . . . . . . . . . . . . . . . . . . . . . 393 DB2ClientRerouteServerList class . . . . . . . . . . . . . . . . . . . . . . . . . . . 400 DB2ClobFileReference class. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 401 DB2Connection interface . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 401 DB2ConnectionPoolDataSource class . . . . . . . . . . . . . . . . . . . . . . . . . 420 DB2DatabaseMetaData interface . . . . . . . . . . . . . . . . . . . . . . . . . . . 422 DB2Diagnosable interface . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 423 DB2DataSource class . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 424 DB2Driver class . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 425 DB2ExceptionFormatter class . . . . . . . . . . . . . . . . . . . . . . . . . . . . 425 DB2FileReference class . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 426 DB2JCCPlugin class . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 427 | DB2JSONResultSet interface . . . . . . . . . . . . . . . . . . . . . . . . . . . . 428 DB2ParameterMetaData interface . . . . . . . . . . . . . . . . . . . . . . . . . . . 429 DB2PooledConnection class . . . . . . . . . . . . . . . . . . . . . . . . . . . . 430 DB2PoolMonitor class . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 432 DB2PreparedStatement interface . . . . . . . . . . . . . . . . . . . . . . . . . . . 435 DB2ResultSet interface . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 450 DB2ResultSetMetaData interface . . . . . . . . . . . . . . . . . . . . . . . . . . . 454 DB2RowID interface . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 455 DB2SimpleDataSource class . . . . . . . . . . . . . . . . . . . . . . . . . . . . 455 DB2Sqlca class . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 456 DB2Statement interface . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 457 DB2SystemMonitor interface . . . . . . . . . . . . . . . . . . . . . . . . . . . . 459 DB2TraceManager class . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 462 DB2TraceManagerMXBean interface . . . . . . . . . . . . . . . . . . . . . . . . . . 466 DB2Types class . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 469 | DB2Version class . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 469 DB2XADataSource class . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 471 DB2Xml interface . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 473 DB2XmlAsBlobFileReference class . . . . . . . . . . . . . . . . . . . . . . . . . . 475 DB2XmlAsClobFileReference class . . . . . . . . . . . . . . . . . . . . . . . . . . 475 DBTimestamp class . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 476 JDBC differences between versions of the IBM Data Server Driver for JDBC and SQLJ . . . . . . . . . . 478 IBM Netezza connection through the IBM Data Server Driver for JDBC and SQLJ . . . . . . . . . . 486 Examples of ResultSetMetaData.getColumnName and ResultSetMetaData.getColumnLabel values . . . . . . 487 Error codes issued by the IBM Data Server Driver for JDBC and SQLJ . . . . . . . . . . . . . . . 489 SQLSTATEs issued by the IBM Data Server Driver for JDBC and SQLJ . . . . . . . . . . . . . . . 496 Commands for SQLJ program preparation. . . . . . . . . . . . . . . . . . . . . . . . . 498 sqlj - SQLJ translator . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 498 db2sqljcustomize - SQLJ profile customizer . . . . . . . . . . . . . . . . . . . . . . . 502 db2sqljbind - SQLJ profile binder . . . . . . . . . . . . . . . . . . . . . . . . . . . 513 db2sqljprint - SQLJ profile printer . . . . . . . . . . . . . . . . . . . . . . . . . . 517

Chapter 8. Installing the IBM Data Server Driver for JDBC and SQLJ on DB2 for z/OS

Installing the IBM Data Server Driver for JDBC and SQLJ as part of a DB2 installation . . . . . . . . . Jobs for loading the IBM Data Server Driver for JDBC and SQLJ libraries . . . . . . . . . . . . Environment variables for the IBM Data Server Driver for JDBC and SQLJ . . . . . . . . . . . . Customization of IBM Data Server Driver for JDBC and SQLJ configuration properties . . . . . . . . Enabling the DB2-supplied stored procedures used by the IBM Data Server Driver for JDBC and SQLJ . . DB2Binder utility . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

519

. 519 . 520 . 520 . 522 . 523 . 526

vi Application Programming Guide and Reference for Java

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

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download