IBM DB2 Universal Driver

1. IBM DB2 Universal Driver Zhang, Jian Wei zhjw@cn.ibm.com

2. Disclaimer • The information contained in this document has not been submitted to any formal IBM review and is distributed on an "As Is" basis without any warranty either express or implied. The use of this information is a customer responsibility. • The measurement results presented here were run in a controlled laboratory environment using specific workloads. While the information here has been reviewed by IBM personnel for accuracy, • there is no guarantee that the same or similar results will be obtained elsewhere. Performance results depend upon the workload and the environment. Customers attempting to adapt this data to their own environments do so at their own risk. • In addition, the materials in this document may be subject to enhancements or Programming Temporary Fixes (PTFs) subsequent to the level used in this study.

3. Agenda • What is JCC Driver • JCC Driver install and configuration • JCC Driver connectivity • JCC Driver log and trace • JCC support to transaction and Savepoint • JCC Driver and Lob/XML • MultiRow Fetch and MultiRow Insert

4. Agenda • What is JCC Driver • JCC Driver install and configuration • JCC Driver connectivity • JCC Driver log and trace • JCC support to transaction and Savepoint • JCC Driver and Lob/XML • MultiRow Fetch and MultiRow Insert

5. Alternative Driver Architectures • Driver maps JDBC calls to another data access API, such as ODBC (Type 1) • Driver written partly in Java and partly in native code. Native code communicates directly with DBMS (Type2) • A pure Java driver that communicates with a proprietary middleware JDBC server using a proprietary protocol (Type 3) • A pure Java driver that communicates directly to the DBMS server using an open protocol such as DRDA (Type 4)

6. Driver Type 1

7. Driver Type 2

8. Driver Type 3

9. Driver Type 4

10. What is JCC Driver • One of new features of DB2 V8 • Universal - Independent of driver-type connectivity or target platform • Not fall into conventional driver-type categories defined by SUN, driver types become connectivity type • Allow for all-java connectivity (Type 4) or JNI-based connectivity (Type 2) in a single driver • Based on DRDA for cross-platform access to DB2 • The new Universal Driver is com.ibm.db2.jcc.DB2Driver • db2jcc.jar • sqlj.zip

11. Universal Driver Architecture - another look SQLJ Application JDBC Application SQLJ runtime abstract JDBC machine Java common layer Java DRDA AR DB2 for z/OS native libraries DB2 for Linux, Unix, Windows native libraries T4 DB2 for z/OS T2 DB2 for LUW (Linux, Unix and Windows) T2

12. Universal Driver Benefits • Single driver across platforms • not a combination of existing drivers • platform specifics pushed to lowest layers • Common JDBC and SQLJ behavior • common abstract JDBC layer maximized • Common SQLJ utilities • Reduce development and maintenance costs • one implementation becomes universally available • One driver is loaded and type 2 and type 4 connections can be obtained (type 2 requires proper native libraries)

13. Type 4 Connectivity • 100% pure Java client • binary portable • ideal for applications and applets/servlets • runs inside the Java security sandbox • supports light client install • no local configuration of DB2 directories • uses Java properties • application uses URL or JNDI lookup • No file system dependencies • Based on DRDA - Distributed Relational Database Architecture • supports direct connectivity to DRDA servers over TCPIP • DB2 for z/OS v6, v7, v8 and v9 • DB2 for Linux, Unix, and Windows v8, v9 • requires DRDA server support for features • leverages optimizations built into DRDA • eliminates applet server required by the T3 driver • Now supports XA functionality (JTA) to z/OS

14. Type 2 Connectivity • Uses RRSAF to connect locally to DB2 for high performance • Interoperable with CICS, IMS transactions • Used by Java Stored Procedures • Compatible with many legacy driver propietary extensions • Provides features not available in legacy driver: • Scrollable Cursors • locator-based LOBs

15. Selecting Type 2 or Type 4 • Some advantages of each • T4 has advantages of 100% Java client • T4 supports XA to DB2 z/OS v7 and v8, XA to DB2 LUW in v 8.2 • T4 performs better for remote connections • T2 LUW supports shared memory and network protocols other than TCPIP • T2 z/OS supports direct native RRSAF-based connectivity • T2 z/OS supports RRS-based transaction management • T2 performs better for local connections • Driver selection based on data souce property or URL • a property named driverType is used to determine which underlying architecture to use • an IBM DataSource implementation is associated with a common Universal Driver regardless of underlying connectivity type • In DB2 for z/OS environment, Type 2 must be used when executing under CICS, IMS, or in a Java stored procedure

16. JDBC Support In Universal Driver • JDBC 1 • Rearchitected code base for JDBC abstract machine • Leverages new DRDA function (i.e. interrupts and extended describe) • JDBC 2 • Scrollable cursors, updatable result sets • Batching, Lob streaming • Distributed transactions and pooling • etc... • JDBC 3 • Multiple result sets, Savepoints, Cursor holdability APIs • Parameter meta data, Auto-generated Keys • etc.... • Limitations • JDBC 2 Structured User-Defined Types, Arrays • JDBC 2 Row Sets coming in JDK 1.5

17. Supported platform, compatibilities and connectivity • JRE1.3 and above clients • DB2 LUW V8 and above • DB2 UDB for z/OS and OS/390 V6/7/8/9 • Cloudscape network server 5.1.8 and above • DB2 for iSeries v5 and above • Type 2 connectivity requires compatibility between db2jcc.jar and native driver DLL.

18. Agenda • What is JCC Driver • JCC Driver install and configuration • JCC Driver connectivity • JCC Driver log and trace • JCC support to transaction and Savepoint • JCC Driver and Lob/XML • MultiRow Fetch and MultiRow Insert

19. JCC driver installation • Client-side prerequisites • Client JVM JRE 1.3.1 or above • db2jcc.jar in class path • SQLJ runtime (optional) • License file db2jcc_license_cisuz.jar • db2jcc_javax.jar – a subset of j2ee interfaces required by driver • Server-side prerequisites • Stored procedures installed for meta data catalog queries and error message text retrieval (PTF for DB2 z/OS v6 and v7) • JDBC/CLI package bind (for DB2 z/OS only) • TCP/IP configuration for type 4 connectivity

20. JDBC package binding utility • Java com.ibm.db2.jcc.DB2Binder or db2jdbcbind • Access to JCC packages granted to public • keepdynamic whether DB2 keeps dynamic SQL statements after commit points • reoptDetermine whether to have DB2 determine an access path at runtime • size The number of internal JCC packages to bind for each DB2 isolation and holdability. • Default collection is NULLID. • jdbcCollection / currentPackageSet / currentPackagePath / pkList / planName

21. JDBC Package Utility (continued) • Three dynamic packages are created per holdability and per isolation by default, which can be adjusted by option size

22. Licensing • Auxiliary license jar to enable connectivity to target servers • db2jcc_license_cisuz.jar • c=cloudscape, i =iSeries, z=z/OS, s=sqlds, u=unix/windows • provided to DB2 Connect licensees only • db2jcc_license_cu.jar • provided with UDB for LUW • db2jcc_license_c.jar

23. Driver Configuration • Global driver properties can be provided through Java system properties or a properties file. • 7 global driver properties supported by T4 • db2.jcc.traceFile • db2.jcc.traceFileAppend • db2.jcc.traceDirectory • db2.jcc.traceLevel • db2.jcc.currentSchema • db2.jcc.currentSQLID • db2.jcc.progressiveStreaming • To override the properties set on connection or DataSource level • db2.jcc.override.* • Property file name : db2.jcc.propertiesFile or DB2JccConfiguration.properties or com/ibm/db2/jcc/DB2JccConfiguration.properties

24. Agenda • What is JCC Driver • JCC Driver install and configuration • JCC Driver connectivity • JCC Driver log and trace • JCC support to transaction and Savepoint • JCC Driver and Lob/XML • MultiRow Fetch and MultiRow Insert

25. Connectivity • Getting a connection • JDBC 1 Database URL Syntax • JDBC 2 Data Source • Security and encryption • Client-reroute/connection-concentrator/sysplex WLB

26. Getting a connection • JDBC connection is established by • Java.sql.DriverManager • Javax.sql.DataSource • DataSource is preferred for better portability • DB2 connection URL using DriverManager • T4: jdbc:db2://<server>[:<port>]/databaseName[:propertyKey=value;...]  • T2: jdbc:db2:databaseName[:propertyKey=value;...]

27. JDBC 2 Data Sources • com.ibm.db2.jcc.DB2BaseDataSource • Abstract parent class for all DB2 data source • com.ibm.db2.jcc.DB2SimpleDataSource • Simplest implementation • com.ibm.db2.jcc.DB2DataSource • with a rudimentary pooling manager. • com.ibm.db2.jcc.DB2ConnectionPoolDataSource • Support connection pooling • com.ibm.db2.jcc.DB2XADataSource • Support both pooling and J2EE JTA implementation

28. Sample of establishing connection using datasource javax.sql.DataSource ds = new com.ibm.db2.jcc.DB2SimpleDataSource(); ((com.ibm.db2.jcc.DB2BaseDataSource) ds).setServerName ("hal); ((com.ibm.db2.jcc.DB2BaseDataSource) ds).setPortNumber (50000); ((com.ibm.db2.jcc.DB2BaseDataSource) ds).setDatabaseName ("sample"); ((com.ibm.db2.jcc.DB2BaseDataSource) ds).setDriverType (4); Connection c = ds.getConnection ("dave", "<password>"); …

29. JDBC 2 Login Timeout • DB2BaseDataSource. setLoginTimeout(int) • loginTimeout property of DataSource • Sets the maximum time in seconds that this data source will wait while attempting to connect to a database. • Value 0(default) means depending on system timeout • T4 supports it

30. Various Security Mechanisms • JCC driver supports Kerberos, not supported by legacy drivers • Set by property securityMechanism • USER_ONLY_SECURITY • CLEAR_TEXT_PASSWORD_SECURITY default • ENCRYPTED_PASSWORD_SECURITY • ENCRYPTED_USER_AND_PASSWORD_SECURITY • KERBEROS_SECURITY • ENCRYPTED_USER_AND_DATA_SECURITY • ENCRYPTED_USER_PASSWORD_AND_DATA_SECURITY • PLUGIN_SECURITY • ENCRYPTED_USER_ONLY_SECURITY

31. Encryption • ibmjceprovider.jar is required for using encryption, using 256 prime size for DES encryption • For z/OS server, icsf should be started using • S icsfrstr • S icsf

32. DB2 LUW V8.2 -- Client Reroute • Automatic, transparent connection to alternate server when primary connection fails • If there is a currently executing SQL statement, it will fail with sqlcode -30108 • Transaction can then be re-driven without re-establishing a connection • Alternate information Stored on client • System database directory • clientRerouteServerListJNDIName property (Java Type 4 driver) • Works with HADR, EE/ESE, EEE/DPF, Replication Automatically sent to client hostname <hhh> port <nnn> New connection to standby automatically established Primary Connection DB2 Engine DB2 Engine PRIMARY SERVER STANDBY SERVER db2 update alternate server for database <dbname> using hostname <hhh> port <nnn>

33. Sample case of client re-route • 1.Create an instance of DB2ClientRerouteServerList, and bind that instance to the JNDI registry.// Create a starting context for naming operationsInitialContext registry = new InitialContext();// Create a DB2ClientRerouteServerList objectDB2ClientRerouteServerList address = new DB2ClientRerouteServerList();// Set the <port> number and <server> name for the primary <server>address.setPrimaryPortNumber(50000);address.setPrimaryServerName("mvs1.sj.ibm.com");// Set the <port> number and <server> name for the alternate <server>int[] <port> = {50002}; String[] <server> = {"mvs3.sj.ibm.com"};address.setAlternatePortNumber(<port>);address.setAlternateServerName(<server>);registry.rebind("serverList", address); • 2. Assign the JNDI name of the DB2ClientRerouteServerList object to DataSource property clientRerouteServerListJNDIName. Example:datasource.setClientRerouteServerListJNDIName("serverList");

34. DB2 for z/OS – Sysplex Routing • With FixPak 10, the type 4 driver is enhanced to provide sysplex routing • Automatically balances work across the members of the parallel sysplex based on feedback from WLM • Activated via setting enableSysplexWLB property in the DataSource. DB2 Member 1 DB2 Member 2 DB2 Member 3 DB2 Member 4 DB2 Member 5

35. DB2 for z/OS – Connection Concentrator • With FixPak 10, the type 4 driver is enhanced to provide connection concentrator support. • Allows a given JVM to map multiple connections onto a smaller number of physical connections. • Automatically re-routes SQL to a new connection if the first SQL statement in a unit of work fails due to a failed connection. • Activated via setting enableConnectionConcentrator property in the DataSource. DB2 Member 1 DB2 Member 2 DB2 Member 3 DB2 Member 4 DB2 Member 5

36. Agenda • What is JCC Driver • JCC Driver install and configuration • JCC Driver connectivity • JCC Driver log and trace • JCC support to transaction and Savepoint • JCC Driver and Lob/XML • MultiRow Fetch and MultiRow Insert

37. JCC versioning APIs • java.sql.DatabaseMetaData and java.sq.Driver methods to obtain driver information • java.sql.DatabaseMetaData • getDriverMajorVersion() • getDriverMinorVersion() • com.ibm.db2.jcc.DB2DatabaseMetaData • getJccDriverBuildNumber() • java.sql.Driver • getMajorVersion(), • getMinorVersion() • com.ibm.db2.jcc.DB2Driver • getJccBuildNumber(), getJccBuildCertification() • java com.ibm.db2.jcc.DB2Jcc –version / -configuration • db2jcc –version / -configuration • com.ibm.db2.jcc.DB2Version • getLoadedDllMajorVersion(), etc...

38. JCC driver versioning • Type 2 connectivity requires compatibility between db2jcc.jar driver version and the native driver DLL version • Compatibility is determined at runtime • Incompatible DLL detected for use with deployed version of the Universal Driver. Unix/Windows Base Level DLL Interface recognized by driver: 40003 zOS DLL Interface recognized by driver: xxxx Actual Loaded DLL Interface Version = 40003

39. Error diagnostics APIs • Uses stored procedures to obtain server side error message text • Set property retrieveMessagesFromServerOnGetMessage=trueso that SQLException.getMessage() get formatted messgae • com.ibm.db2.jcc.DB2Diagnosable and com.ibm.db2.jcc.DB2Sqlca provide a mechanism for obtaining diagnostics and low-level SQLCA information from a DB2 SQLException try { .. } catch (SQLException e) { if (e.instanceof DB2Diagnosable) { DB2Sqlca sqlca = ((DB2Diagnosable)e).getSqlca(); if (sqlca != null) { // Extract SQLCA information and process, see class DB2Sqlca } } }

40. Trace management APIs • Dynamically turned on/off per connection or globally throughout application • 5 flavors of trace log writers • JDBC 2 data source log writer - use DB2BaseDataSource setLogWriter() and setTraceLevel(int traceLevel), setTraceFile(), setTraceDirectory() • JDBC 1 driver manager log writer - use java.sq.DriverManger setLogWriter() • Proprietary connection-level log writer - use com.ibm.db2.jcc.DB2Connection setJccLogWriter() • Proprietary global log writer - use com.ibm.db2.jcc.DB2TraceManager setLogWriter(traceFile, ...), setLogWriter(traceDirectory, ...), setLogWriter(printWriter, ...) • Type 2 z/OS specific: global tracing can be enabled for both Java-side processing and native-side processing via driver configuration without any application or Connection property modification • db2.jcc.override.traceFile=javatrace_filename • db2.jcc.t2zosTraceFile=nativetrace_filename • Trace suspend/resume, active connection tracking • com.ibm.db2.jcc.DB2TraceManager suspendTrace(), resumeTrace() • com.ibm.db2.jcc.DB2TraceManager trackActiveConnections()

41. Trace management APIs (continued) • Directory Trace to multiple files, or trace all connections to a single file • trace file suffixes s _xads_n, cpds_n, _sds_n, _ds_n, _driver_n, _global_n • Adding a trace event notification facility. This will allow users to enable and monitor runtime trace diagnostics programmatically, without actually performinng any stream I/O.

42. Trace levels • multiple levels of trace detail • traceLevel property set using bit-wise arithmetic • default is fully enabled public final static int TRACE_NONE = 0x0; public final static int TRACE_CONNECTION_CALLS = 0x1; public final static int TRACE_STATEMENT_CALLS= 0x2; public final static int TRACE_RESULT_SET_CALLS = 0x4; public final static int TRACE_DRIVER_CONFIGURATION = 0x10; public final static int TRACE_CONNECTS = 0x20; public final static int TRACE_DRDA_FLOWS = 0x40; public final static int TRACE_RESULT_SET_META_DATA = 0x80; public final static int TRACE_PARAMETER_META_DATA = 0x100; public final static int TRACE_DIAGNOSTICS = 0x200; public final static int TRACE_SQLJ = 0x400; public final static int TRACE_XA_CALLS = 0x800; public final static int TRACE_ALL = 0xFFFFFFFF;

43. Example Trace 1 [ibm][db2][jcc][harness] Running test case com.ibm.db2.jcc.harness.dev.core.SimpleQuery [ibm][db2][jcc][harness] Detected driver: IBM DB2 JDBC Universal Driver Architecture 1.3.23 Test Build [ibm][db2][jcc][harness] Attempting connection to v14ec134.svl.ibm.com:446/STL454A [ibm][db2][jcc][harness] Using properties: { traceDirectory=null, traceFile=null, driverType=4, deferPrepares=true, traceLevel=-1, user=admf001, ...} [ibm][db2][jcc] BEGIN TRACE_DRIVER_CONFIGURATION [ibm][db2][jcc] Driver: IBM DB2 JDBC Universal Driver Architecture 1.3.23 Test Build [ibm][db2][jcc] Compatible JRE versions: { 1.3, 1.4 } [ibm][db2][jcc] Target server licensing restrictions: { z/OS: enabled; SQLDS: enabled; iSeries: enabled; DB2 for Unix/Windows: enabled; Cloudscape: enabled } [ibm][db2][jcc] Range checking enabled: true [ibm][db2][jcc] Bug check level: 0xff [ibm][db2][jcc] Default fetch size: 64 [ibm][db2][jcc] Default isolation: 2 [ibm][db2][jcc] Collect performance statistics: false [ibm][db2][jcc] No security manager detected. [ibm][db2][jcc] Detected local client host: oodb6/9.30.28.50 [ibm][db2][jcc] Access to package sun.io is permitted by security manager. [ibm][db2][jcc] Java Runtime Environment version 1.4.0 [ibm][db2][jcc] Java Runtime Environment vendor = Sun Microsystems Inc. [ibm][db2][jcc] Java vendor URL = http://java.sun.com/ [ibm][db2][jcc] Java installation directory = C:\j2sdk1.4.0\jre [ibm][db2][jcc] Java Virtual Machine specification version = 1.0 [ibm][db2][jcc] Java Runtime Environment specification version = 1.4 [ibm][db2][jcc] Java Runtime Environment specification vendor = Sun Microsystems Inc. [ibm][db2][jcc] Java Runtime Environment specification name = Java Platform API Specification [ibm][db2][jcc] Java class format version number = 48.0 [ibm][db2][jcc] Java class path = C:\z\jcc\lib\sqlj.zip;C:\z\jcc\lib\j2ee.jar;... [ibm][db2][jcc] Path of extension directory or directories = C:\j2sdk1.4.0\jre\lib\ext [ibm][db2][jcc] Operating system name = Windows 2000 [ibm][db2][jcc] Operating system architecture = x86 [ibm][db2][jcc] Operating system version = 5.0 [ibm][db2][jcc] File separator ("/" on UNIX) = \ [ibm][db2][jcc] Path separator (":" on UNIX) = ; [ibm][db2][jcc] User's account name = ostler [ibm][db2][jcc] User's home directory = C:\Documents and Settings\Administrator [ibm][db2][jcc] User's current working directory = C:\z\jcc [ibm][db2][jcc] END TRACE_DRIVER_CONFIGURATION

44. Example Trace 2 [ibm][db2][jcc] BEGIN TRACE_CONNECTS [ibm][db2][jcc] Attempting connection to v14ec134.svl.ibm.com:446/STL454A [ibm][db2][jcc] Using properties: { traceDirectory=null, traceFile=null, driverType=4, deferPrepares=true, traceLevel=-1, user=admf001, ... } [ibm][db2][jcc] END TRACE_CONNECTS ... [ibm][db2][jcc][t4][time:1054850797859][thread:main][tracepoint:1][Request.flush] [ibm][db2][jcc][t4] SEND BUFFER: EXCSAT (ASCII) (EBCDIC) [ibm][db2][jcc][t4] 0 1 2 3 4 5 6 7 8 9 A B C D E F 0123456789ABCDEF 0123456789ABCDEF [ibm][db2][jcc][t4] 0000 0053D0410001004D 1041000E115E8482 .S.A...M.A...^.. ..}....(.....;db [ibm][db2][jcc][t4] 0010 F291838394818995 0009116D96968482 ...........m.... 2jccmain..._oodb [ibm][db2][jcc][t4] 0020 F6000E115AC4C2F2 D1C3C340F14BF000 ....Z......@.K.. 6...!DB2JCC 1.0. [ibm][db2][jcc][t4] 0030 1814041403000724 070007240F000714 .......$...$.... ................ [ibm][db2][jcc][t4] 0040 4000061474000500 0C1147D8C4C2F261 @...t.....G....a ..........QDB2/ [ibm][db2][jcc][t4] 0050 D1E5D40026D00100 020020106D000611 ....&..... .m... JVM..}......_... [ibm][db2][jcc][t4] 0060 A2000300162110E2 E3D3F4F5F4C14040 .....!........@@ s......STL454A [ibm][db2][jcc][t4] 0070 4040404040404040 40 @@@@@@@@@ ... [ibm][db2][jcc][Connection@503429] BEGIN TRACE_CONNECTS [ibm][db2][jcc][Connection@503429] Successfully connected to server jdbc:db2://v14ec134.svl.ibm.com:446/STL454A [ibm][db2][jcc][Connection@503429] User: admf001 [ibm][db2][jcc][Connection@503429] Database product name: DB2 [ibm][db2][jcc][Connection@503429] Database product version: DSN07011 [ibm][db2][jcc][Connection@503429] Driver name: IBM DB2 JDBC Universal Driver Architecture [ibm][db2][jcc][Connection@503429] Driver version: 1.3.23 Test Build [ibm][db2][jcc][Connection@503429] DB2 Application Correlator: G91E1C32.G750.00F59A01D105 [ibm][db2][jcc][Connection@503429] END TRACE_CONNECTS [ibm][db2][jcc][t4] DRDA manager levels: { SQLAM=6, AGENT=7, CMNTCPIP=5, RDB=7, SECMGR=6 }

45. Example Trace 3 [ibm][db2][jcc][time:1054850798711][thread:main][Connection@503429] createStatement () called [ibm][db2][jcc][time:1054850798711][thread:main][Connection@503429] createStatement () returned Statement@8b819f [ibm][db2][jcc][time:1054850798711][thread:main][Statement@8b819f] executeQuery (select intNullable from testTable_foo order by intNullable) called [ibm][db2][jcc][ResultSetMetaData@256ea2] BEGIN TRACE_RESULT_SET_META_DATA [ibm][db2][jcc][ResultSetMetaData@256ea2] Result set meta data for statement Statement@8b819f [ibm][db2][jcc][ResultSetMetaData@256ea2] Number of result set columns: 1 [ibm][db2][jcc][ResultSetMetaData@256ea2] isDescribed=true [ibm][db2][jcc][ResultSetMetaData@256ea2] Column 1: { ..., type name=INTEGER, nullable=1, ... } [ibm][db2][jcc][ResultSetMetaData@256ea2] END TRACE_RESULT_SET_META_DATA [ibm][db2][jcc][time:1054850798821][Thread:main][Statement@8b819f] executeQuery () returned ResultSet@dff3a2 [ibm][db2][jcc][time:1054850798821][Thread:main][ResultSet@dff3a2] next () called [ibm][db2][jcc][time:1054850798821][Thread:main][ResultSet@dff3a2] next () returned true [ibm][db2][jcc][time:1054850798821][Thread:main][ResultSet@dff3a2] getInt (1) called [ibm][db2][jcc][time:1054850798831][Thread:main][ResultSet@dff3a2] getInt () returned 1 [ibm][db2][jcc][time:1054850798831][Thread:main][ResultSet@dff3a2] next () called [ibm][db2][jcc][time:1054850798841][Thread:main][ResultSet@dff3a2] next () returned false [ibm][db2][jcc][time:1054850798841][Thread:main][ResultSet@dff3a2] close () called [ibm][db2][jcc][time:1054850798841][Thread:main][Statement@8b819f] close () called [ibm][db2][jcc][time:1054850798841][Thread:main][Connection@503429] getWarnings () returned null [ibm][db2][jcc][time:1054850798851][Thread:main][Connection@503429] commit () called [ibm][db2][jcc][time:1054850799171][Thread:main][Connection@503429] close () called [ibm][db2][jcc][harness] Successfully ran test case com.ibm.db2.jcc.harness.dev.core.SimpleQuery

46. Set client information • user, workstation, application, and accounting can be specified using com.ibm.db2.jcc.DB2Connection methods and will be forwarded to the server on the very next SQL request • setDB2ClientUser (String user) • setDB2ClientWorkstation (String name) • setDB2ClientApplicationInformation (String info) • setDB2ClientAccountingInformation (String info) • information is used in display thread reports and IFC records

47. Default monitoring for JDBC -D71B DIS THREAD(*) NAME ST A REQ ID AUTHID PLAN ASID TOKEN SERVER RA * 952 db2jccThread USRT001DISTSERV 004A 11 V445-G91E81C5.G49D.00F330EEE11F=122 ACCESSING DATA FOR 9.30.129.197 SERVER RA * 112 db2jccThread USRT001DISTSERV 004A 123 V445-G91E81C5.G49D.00F330DF1111=222 ACCESSING DATA FOR 9.30.129.197 SERVER RA * 432 db2jccThread USRT001DISTSERV 004A 432 V445-G91E81C5.G49D.00F330DF736F=432 ACCESSING DATA FOR 9.30.129.197 SERVER RA * 772 db2jccThread USRT001DISTSERV 004A 21 V445-G91E81C5.G49D.00F330DF736F=382 ACCESSING DATA FOR 9.30.129.197 -- All programs typically have the same package name... -- Access paths can change on any given PREPARE

48. Client Instrumentation APIs for JDBC or SQLJ • New Java methods for existing Set Client Information API setClientUser("maryela1") setClientWorkStation("9.30.11.123") setClientApplicationInformation("payment") setClientAccountingInformation(String) • For all users: provides additional monitoring information • For DB2 for z/OS: Strings are included in IFC records -D71B DIS THREAD(*) NAME ST A REQ ID AUTHID PLAN ASID TOKEN SERVER RA * 952 db2jccThread USRT001 DISTSERV 004A 432 V437-WORKSTATION=9.30.11.123, USERID=maryela1, APPLICATION NAME=payment V445-G91E81C5.G49D.00F330DF736F=432 ACCESSING DATA FOR 9.30.129.197 LOCATION: SYPEC15A DB2 PERFORMANCE MONITOR GROUP: N/P ACCOUNTING REPORT SUBSYSTEM: V71A ORDER: TRANSACT #OCCURS ... CLASS1 EL.TIME CLASS2 EL.TIME TRANSACT #DISTRS ... CLASS1 CPUTIME CLASS2 CPUTIME --------------------------- ------- -------------- -------------- payment 4 1.000902 0.005561 4 0.003573 0.002457

49. System monitoring • tracks server (not applicable to DB2 LUW), network, driver, and application times • goal is to identify performance issues • stop watch operation DB2 Server Java App. Univ. Driver SQLJ/JDBC prepareStatement/ executeUpdate executeUpdate executeUpdate

50. System monitoring DB2SystemMonitor monitor=((DB2Connection)conn).getDB2SystemMonitor(); monitor.enable(true); monitor.start(com.ibm.db2.jcc.DB2SystemMonitor.RESET_TIMES); monitor.stop(); monitor.getServerTime() monitor.getNetworkIOTime() monitor.getCoreDriverTime() monitor.getApplicationTime()