WAS connectivity to DB2 for z/OS JDBC and Structured Query Language for Java (SQLJ)

1. WAS connectivity to DB2 for z/OS JDBC and Structured Query Language for Java (SQLJ)

2. After completing this unit, you should be able to: Understand the different JDBC types Describe the JDBC drivers provided with DB2 for z/OS Describe the “IBM DB2 Universal Driver for SQLJ and JDBC”: Architecture Connectivity type 2 and 4 XA support (two phase commit) JDBC and SQLJ Driver Manager interface and Data Source classes Describe the SQLJ customization process How to configure the connection from WAS to DB2 for z/OS Unit Objectives

3. Summary of JDBC Driver Types

4. IBM DB2 Universal Driver for SQLJ and JDBC • What is it? • Architected as an abstract JDBC processor • Independent of driver-type connectivity or target platform. • As an abstract machine, driver types become connectivity types • Architecture-neutral JDBC driver for: • Distributed DB2 access (Type 4 connectivity) and • Local DB2 access (Type 2 connectivity)

5. DB2 Universal Driver Objectives • Provided functions of DB2 UDB for Linux, UNIX, and Windows and those for z/OS are the same • Improves Family compatibility - true portability • Functionality enhancements for Type 2 and Type 4 drivers • Fully compliant with JDBC 3.0 standard • Provides a full Java application development process for SQLJ • Ease of installation and deployment for Type 4 driver • No dependencies on a runtime of DLL • Installation merely a copy of a .jar and .zip file • Reduces the client footprint for Linux, UNIX, and Windows platforms • Delivers functional and performance enhancements quicker • Improves trace capabilities

6. Java Universal Driver Architecture

7. Supported Platforms and Connectivity • V1.0 supported Type 4 connectivity to most DB2s • Bundled with DB2 for LUW v8.1, WAS, and Cloudscape • V1.2 added Type 2 connectivity to DB2 for LUW, type 4 connectivity to iSeries • Bundled with DB2 for LUW v8.1 fix pack 2 • V1.3 added minimum JDBC 3 compliance • Bundled with DB2 for LUW v8.1 fix pack 3 • V1.4 added Beta Type 2 connectivity to z/OS v8 • V1.5 added full JDBC 3 compliance • Bundled with DB2 for LUW v8.1 fix pack 4 • V1.6 added Beta Type 4 XA (2 phase commit) connectivity to DB2 for z/OS V7 and V8 • V1.7 added Type 4 XA connectivity to DB2 V7 z/OS • V2.1 GA of z/OS Type 2 JNI-based local connectivity • Released via APAR PQ80841 / PTF UQ85607 for DB2 for z/OS and OS/390 V7 • Production release via APAR PQ85053 / PTF UQ85928 for DB2 for z/OS V8 • V2.3 Added official support of T4 XA connectivity from DB2 for z/OS to DB2 for LUW • Available with APAR PQ93458

8. JCC Connectivity Options

9. z/OS Application Connectivity to DB2 for z/OS and OS/390

10. Licensing

11. JDBC Drivers Provided with DB2 for z/OS

12. Migration to the Universal Driver • New Java Universal Driver will eventually replace existing legacy implementations of DB2 JDBC drivers • Subtle behavioral differences from legacy drivers are to be expected • Existing drivers do not have the same behavior • Impossible to match JDBC behavior of LUW and z/OS simultaneously in all cases • Migration should be done gradually as there will be differences • db2sqljupgrade utility for DB2 for z/OS serialized profiles

13. Getting a Connection using JCC

14. Connecting to a DB2 • Via a DriverManager interface • A driver must be registered (specific) • No connection pooling • Application code is NOT portable • (For batch...) • Via a Data source • JDBC 2.x Standard Extension • (Used with WAS 3.5, v4, v5)

15. DriverManager URL Syntax

16. Data Source Classes

17. Java API Enhancements • Scrollable cursor support • Batched updates support • Improved Java SQL error information • Native DB2 server SQL error messages • Java API for set client information (SQLESETI) • Built-in DB2SystemMonitor class (application-driver-network-server) • Multiple open result sets • SAVEPOINT support • Auto-generated keys • Enhanced LOB support

18. Why Use SQLJ? • Static SQL performance for Java applications • Significant performance advantage over JDBC • Static SQL authorization model • Provides Java with a stronger authorization model • Errors detected earlier • Productivity/manageability • Less code written by the application programmer • Resulting code is easier to maintain • Static SQL packages for accounting/monitoring • Static SQL locks in access path, so that access path changes don't occur without a conscious choice

19. Comparing SQLJ and JDBC Coding • Retrieving a single row from DB2 • SQLJ String addr; String name; .... #sql [ctx] {SELECT address INTO :addr FROM emp WHERE name =:name}; • JDBC String addr; String name; .... PreparedStatement pstmt = con.prepareStatement( “SELECT address FROM emp WHERE name = ?”); pstmt.setString(1, name); ResultSet names = pstmt.executeQuery(); names.next(); addr = names.getString(1); names.close(); pstmt.close();

20. Old SQLJ Preparation Process

21. New SQLJ Preparation Process

22. WAS Connectivity to DB2 for z/OS • DB2 Universal Driver for SQLJ and JDBC installation • WAS V5 configuration to use the DB2 Universal Driver

23. DB2 Universal Driver for SQLJ and JDBC Installation • Required Environment Variables: • PATH • $JDBC_HOME/bin • CLASSPATH • $JDBC_HOME/classes/db2jcc.jar • $JDBC_HOME/classes/db2jcc_javax.jar • $JDBC_HOME/classes/sqlj.zip • $JDBC_HOME/classes/db2jcc_licence_cisuz.jar • LIBPATH (for type 2) • $JDBC_HOME/lib • STEPLIB (for type 2) • SDSNEXIT,SDSNLOAD,SDSNLOD2 • Create the DB2 Universal Driver stored procedures and metadata • Check DESCSTAT=YES in zParms • Run DSNTIJMS job from SDSNSAMP • Bind the DB2 Universal Driver packages with the DB2Binder utility • java com.ibm.db2.jcc.DB2Binder -url jdbc:db2: //server:port/database -user userid -password password • Run the DB2T4XAIndoubtUtil utility for DB2 V7 • java com.ibm.db2.jcc.DB2T4XAIndoubtUtil -url jdbc:db2: //server:port/database -user userid -password password

24. WAS V5 Configuration to Use the DB2 Universal Driver • Configure the DB2 Universal JDBC Driver. • Define a DB2 Universal JDBC Driver Provider for WebSphere Application Server for z/OS. • Define a DB2 Universal JDBC Driver Provider DataSource.

25. Configure the DB2 Universal JDBC Driver

26. Define the DB2 Universal JDBC Driver Provider

27. Define a DB2 Universal JDBC Driver Provider Data Source

28. Having completed this unit, you should be able to: Understand the different JDBC types Describe the JDBC drivers provided with DB2 for z/OS Describe the “IBM DB2 Universal Driver for SQLJ and JDBC”: Architecture Connectivity type 2 and 4 XA support (two phase commit) JDBC and SQLJ Driver Manager interface and Data Source classes Describe the SQLJ customization process How to configure the connection from WAS to DB2 for z/OS Unit Summary