DB2 configuration options for Java client applications
In this chapter we provide background information about the configuration options available in DB2 for z/OS when developing Java applications. We describe the connections available when accessing a data sharing group, the system parameters available to control the network, the main Java drivers option, and the suggested configuration options for
high availability.
This chapter covers the following topics:
3.1 The DB2 configuration
A Sysplex is a set of z/OS systems that communicate and cooperate with each other through specialized hardware components and software services. A collection of one or more DB2 subsystems that share DB2 data is called a data sharing group. With data sharing, applications that run on more than one DB2 for z/OS subsystem can read from and write to the same set of data concurrently.
The data sharing group uses coupling facilities as hardware assist for efficient concurrency and coherency control. One or more coupling facilities provide high-speed caching and lock processing for the data sharing group. The Sysplex, together with the Workload Manager (WLM), dynamic virtual IP address (DVIPA), and the Sysplex Distributor, allow a client to access a DB2 for z/OS database over TCP/IP with network resilience, and distribute the work among the DB2 subsystems within the data sharing group.
Figure 3-1 shows the possible connections to a data sharing group.
Figure 3-1 Connectivity and data sharing
This section provides recommendations for configuring the TCP/IP network and the DB2 subsystems.
3.1.1 Configuring the TCP/IP network
DB2 requires that all members of a data sharing group use the same port number to receive incoming SQL requests. The well-known DB2 registered port 446 is the recommended DRDA port using for SQL processing. Additionally, DB2 requires that each member of a data sharing group has a resynchronization port number that is unique within the Parallel Sysplex. The resync port is used by a requester in two situations. One is when the SQL connection fails leaving in-doubt threads, and the requester and server need to resynchronize after the error. The other one is for other connections used to interrupt SQL processing on a different application connection. Obviously, resynchronization needs to occur with the specific DB2 member with which the requester was in session, so this member must be reachable through a specific IP address (the member-specific DVIPA in this case).
In Figure 3-2 on page 84, there are three DB2 members DB2A, DB2B, and DB2C in the data sharing group with the group location named DB2LOC. These resync addresses are represented by ports 5001, 5002, and 5003, for the three DB2 members DB2A, DB2B, and DB2C, respectively.
Example 3-1 on page 84 first shows to register the well-known DRDA port 446 and a unique resynchronization port with TCP/IP on each member’s z/OS system as shown in the TCP/IP PORT configuration profile statement. On each z/OS system where the DB2 member resides, replicate the TCP/IP PORT configuration profile statement.
Secondly, it shows the VIPADYNAMIC statement to define the group DVIPA for the DB2 data sharing group. The group DVIPA must be defined with the VIPADEFINE and VIPADISTRIBUTE statements on the TCP/IP stacks that are associated with the z/OS systems on which the Sysplex Distributor executes.
The group DVIPA must be defined with the VIPABACKUP statement on the TCP/IP stacks for DVIPA takeover. Note that the VIPABACKUP statements are coded with the MOVEABLE IMMEDIATE keywords, and that the VIPADISTRIBUTE statements are also specified on the backup TCP/IP stacks. This allows for the group DVIPA to be activated on one of the backup stacks if it is not active anywhere else in the Sysplex. For example, if z/OS-1 has not been started when z/OS-2 or z/OS-3 start, then group DVIPA is activated on one of the backup stacks. To allow for failover, the member-specific DVIPAs are defined with the VIPARANGE statement on all TCP/IP stacks.
Example 3-2 on page 85 shows how to use the DSNJU003 utility to define the group location name, the DRDA port, the resync port, the member-specific DVIPA, and the group DVIPA. These changes are applied to the bootstrap data sets (BSDSs).
Figure 3-2 shows three DB2 members and configured resync addresses with unique port numbers for location DB2LOC.
Figure 3-2 DB2 members and configured resync addresses with unique port numbers
This is the network flow as it occurs in Figure 3-2:
1. The initial connection uses the group DVIPA, Vx, on port 446 identified as the DRDA port in each members TCP/IP PORT statement.
2. The Syplex Distributor dispatches the initial connection request to the member with the lightest workload (DB2B in this case).
3. Resynchronization information (5001 for DB2A, 5002 for DB2B, and 5003 for DB2C) and a list of a unique IP address of each member and WLM weight information in the data sharing group are returned to the requester (V1:W1 for DB2A, V2:W2 for DB2B, and V3:W3 for DB2C).
4. Then the subsequent connection attempts are made to the DB2 group by using the member-specific DVIPAs that are returned. In this case the subsequent SQL requests are distributed to DB2A member and DB2C member according to the WLM weight information that DB2A and DB2C have the most apparent capacity at the time.
Example 3-1 Port and VIPA definitions for three DB2 members
z/OS-1 TCP/IP configuration setting
PORT
446 TCP DB2ADIST SHAREPORT
446 TCP DB2BDIST SHAREPORT
446 TCP DB2CDIST SHAREPORT
5001 TCP DB2ADIST
5002 TCP DB2BDIST
5003 TCP DB2CDIST
VIPADYNAMIC
VIPARANGE 255.255.255.255 V1
VIPARANGE 255.255.255.255 V2
VIPARANGE 255.255.255.255 V3
VIPADEFINE 255.255.255.255 Vx
VIPADISTRIBUTE DEFINE Vx
PORT 446
DESTIP ALL
ENDVIPADYNAMIC
 
z/OS-2 TCP/IP configuration setting
PORT
446 TCP DB2ADIST SHAREPORT
446 TCP DB2BDIST SHAREPORT
446 TCP DB2CDIST SHAREPORT
5001 TCP DB2ADIST
5002 TCP DB2BDIST
5003 TCP DB2CDIST
VIPADYNAMIC
VIPARANGE 255.255.255.255 V1
VIPARANGE 255.255.255.255 V2
VIPARANGE 255.255.255.255 V3
VIPABACKUP 1 MOVE IMMED 255.255.255.255 Vx
VIPADISTRIBUTE DEFINE Vx
PORT 446
DESTIP ALL
ENDVIPADYNAMIC
 
z/OS-3 TCP/IP configuration setting
PORT
446 TCP DB2ADIST SHAREPORT
446 TCP DB2BDIST SHAREPORT
446 TCP DB2CDIST SHAREPORT
5001 TCP DB2ADIST
5002 TCP DB2BDIST
5003 TCP DB2CDIST
VIPADYNAMIC
VIPARANGE 255.255.255.255 V1
VIPARANGE 255.255.255.255 V2
VIPARANGE 255.255.255.255 V3
VIPABACKUP 2 MOVE IMMED 255.255.255.255 Vx
VIPADISTRIBUTE DEFINE Vx
PORT 446
DESTIP ALL
ENDVIPADYNAMIC
Example 3-2 BSDS definition for three DB2 members
DB2A
DDF LOCATION=DB2LOC,PORT=446,RESPORT=5001,IPV4=V1,GRPIPV4=Vx
DB2B
DDF LOCATION=DB2LOC,PORT=446,RESPORT=5002,IPV4=V2,GRPIPV4=Vx
DB2C
DDF LOCATION=DB2LOC,PORT=446,RESPORT=5003,IPV4=V3,GRPIPV4=Vx
DB2 10 for z/OS provides a feature that enables users to manage and define subsets of members in a data sharing group dynamically, without stopping and restarting DDF or DB2. This is so called dynamic location aliases and this is done by using the MODIFY DDF command. Before you can define the dynamic location aliases, DB2 must be started, but DDF may or may not be started. DB2 10 supports up to 40 dynamic location aliases. You can manage dynamic location aliases by issuing the MODIFY DDF command to stop or cancel the alias, modify its configuration, and restart it, all without stopping DDF or DB2.
3.1.2 Configuring the DB2 subsystems
The following are the general guidelines for setting DB2 installation parameters that impact the utilization of the connections and threads in DB2:
•DSN6FAC CMTSTAT
Recommends to set to INACTIVE. CMTSTAT=INACTIVE setting enables threads to be pooled after threads successfully commit or roll back a transaction. It then allows the threads to be reused y other connections.
•DSN6SYSP MAXDBAT
Maximum number of database access threads (DBATs) that can be active concurrently. This value should be set considerably. In many cases, the maximum value is determined by the available storage in DBM1 address space.
•DSN6SYSP CONDBAT
Maximum number of concurrent inbound connections to DB2. This includes active and inactive connections. This value might be large and should accommodate the number of inactive connections concurrently that would connect to the subsystem at any point.
•DSN6FAC IDTHTOIN
Maximum amount of time in seconds that an active server thread is allowed to remain idle.The DB2 default 2 minutes is recommended.
•DSN6FAC TCPKPALV (1 to 65534)
Time to execute the longest SQL statement.
DB2 needs to enable threads to be pooled by setting the CMTSTAT to INACTIVE. An inactive connection uses less storage and frees up DB2 resources associated with the transaction when a thread commits a transaction. When connections are disassociated from the thread, the thread is allowed to be pooled and reused for other connections. This provides better resource utilization because there are typically a small number of threads that can be used to service a large number of connections. You can allow threads to be pooled to
improve performance.
MAXDBAT constrains the total number of threads available to process remote SQL requests. If a request for a new connection to DB2 is received and MAXDBAT has been reached, the request is queued, waiting for a thread to become available to process the request. MAXDBAT generally should be set conservatively. It is usually constrained by the available DBM1 storage.
Specify the maximum number of concurrent remote connections by setting the CONDBAT installation parameter. This value must be greater than or equal to MAXDBAT. When a request to allocate a new connection to DB2 is received, and CONDBAT has been reached, the connection request is rejected. The value should be the largest number of pooled connections that would connect to the DB2 member at any point in time. Active threads that have not committed their work in a timely fashion are canceled after IDTHTOIN expires; locks and cursors are released. Inactive connections and in-doubt threads are not subject to time-out. Threads are checked every two minutes to see if they have exceeded the time-out value. If the timeout value is less than two minutes, the thread might not be canceled if it has been inactive for more than the time-out value but less than two minutes.
The quicker DB2 can detect the communication error and return the thread to the pool, the lower the chance to reach MAXDBAT. In cases where the z/OS TCP/IP KeepAlive value in the TCP/IP configuration is not appropriate for the DB2 subsystem, you can use the TCPKPALV as an override.
In addition to defining the IP addresses to TCP/IP, the member and group DVIPA corresponding host names are required to be defined prior to starting DDF. DDF recovery processing may require the use of these names during in-doubt resolution after a subsystem failure. You define the host names by configuring the hlq.HOSTS.LOCAL data set, the /etc/hosts file in the hierarchical file system (HFS), or the domain name server (DNS).
For a more general description of DB2 set up, see 4.3.1, “DB2 connectivity installation parameters” on page 138.
For adding more granular functions to the system level parameters, see 4.3.17, “Using DB2 profiles” on page 180.
3.2 IBM Data Server Drivers and Clients
The IBM strategy is to remove the reliance on the DB2 Connect modules and replace DB2 Connect with the IBM Data Server Drivers or Clients. Although DB2 Connect licenses (in the form of DB2 Connect license files) are still required, you can replace DB2 Connect modules with the IBM Data Server Drivers or Clients and receive equivalent or superior function. In addition, you can reduce complexity, improve performance, and deploy application solutions with smaller footprints for your business users.
With DB2 for LUW Version 9.5 Fix Pack 3 and above you can implement the DRDA requester functions for your distributed applications with varied degrees of granularity. Instead of the current function and large footprint of DB2 Connect, there are several types of IBM data server clients and drivers available. Each provides a particular type of support.
The IBM data server client and driver types are as follows:
•IBM Data Server Driver Package
•IBM Data Server Driver for JDBC and SQLJ
•IBM Data Server Driver for ODBC and CLI
•IBM Data Server Runtime Client
•IBM Data Server Client
Table 3-1 shows the details of what is contained in each offering.
Table 3-1 IBM Data Server Drivers and Clients comparison
Product
Smallest footprint
JDBC and SQLJ
ODBC and CLI
OLE DB and .NET
Open Source
CLP
DBA, Dev, GUI tools
IBM Data Server Driver for JDBC and SQLJ
X
X
 
 
 
 
 
IBM Data Server Driver for ODBC and CLI
X
 
X
 
 
 
 
IBM Data Server Driver Package
 
X
X
X
X
 
 
IBM Data Server Runtime Client
 
X
X
X
X
X
 
IBM Data Server Client
 
 
X
X
X
X
X
X
In this book we discuss IBM Data Server Driver for JDBC and SQLJ for Java applications. We describe the main connectivity options using IBM Data Server Driver for JDBC and SQLJ for WebSphere to connect to DB2 for z/OS system.
You can download the IBM Data Server Drivers and Clients from the IBM download site (http://www.ibm.com/support/docview.wss?rs=4020&uid=swg21385217) where you can see Table 3-2, which can help in identifying the package you need.
Table 3-2 IBM Data Server Client Packages: Latest downloads (DB2 10)
Driver package
Description
IBM Data Server Driver Package (DS Driver)
This package contains drivers and libraries for various programming language environments. It provides support for Java (JDBC and SQLJ), C/C++ (ODBC and CLI), .NET drivers and database drivers for open source languages like PHP and Ruby. It also includes an interactive client tool called CLPPlus that is capable of executing SQL statements and scripts, and can generate custom reports.
IBM Data Server Driver for JDBC and SQLJ (JCC Driver)
Provides support for JDBC and SQLJ for client applications developed in Java. Supports JDBC 3 and JDBC 4 standard. Also called as JCC driver.
IBM Data Server Driver for ODBC and CLI (CLI Driver)
This is the smallest of all the client packages and provides support for Open Database Connectivity (ODBC) and Call Level Interface (CLI) libraries for the C/C++ client applications.
IBM Data Server Runtime Client
This package is a superset of Data Server Driver package. It includes many DB2 specific utilities and libraries. It includes DB2 Command Line Processor (CLP) tool.
IBM Data Server Client
This is the all-in-one client package and includes all the client tools and libraries available. It includes DB2 Control Center, a graphical client tool that can be used to manage DB2 Servers. It also includes add-ins for Visual Studio.
IBM Database Add-Ins for Visual Studio
This package contains the add-ins for Visual Studio for .NET tooling support.
3.2.1 Connectivity options for IBM Data Server Driver for JDBC and SQLJ
IBM Data Server Driver for JDBC and SQLJ supports two types of connectivity: type 4 connectivity and type 2 connectivity.
For the DriverManager interface, you specify the type of connectivity through the URL in the DriverManager.getConnection method. For the DataSource interface, you specify the type of connectivity through the driverType property.
Connecting to DB2 using IBM Data Server Driver for JDBC and SQLJ type 4 connectivity
This configuration option is recommended for Java applications exist on a distributed platform that access the DB2 data remotely.
Type 4 driver is coded entirely in Java providing portability advantage and platform independence. It provides better performance for remote Java applications with type 4 connectivity. Also type 4 driver accesses DB2 system through TCP/IP and provides sysplex workload balancing support.
IBM ships two streams of the type 4 driver with the IBM Data Server Driver for JDBC and SQLJ product:
1. Version 3.5x is JDBC 3.0-compliant. It is packaged as db2jcc.jar and sqlj.zip and provides JDBC 3.0 and earlier support.
2. Version 4.x is JDBC 3.0-compliant and supports some JDBC 4.0 functions. It is packaged as db2jcc4.jar and sqlj4.zip.
The type 4 driver provides support for distributed transaction management. This support implements the Java 2 Platform, Enterprise Edition (J2EE), Java Transaction Service (JTS), and Java Transaction API (JTA) specifications, which conform to the X/Open standard for distributed transactions (Distributed Transaction Processing: The XA Specification).
Figure 3-3 shows types of type 4 connectivity with IBM Data Server Driver for JDBC
and SQLJ.
Figure 3-3 Various type 4 connectivity with IBM Data Server Driver for JDBC and SQLJ
Connecting to DB2 using IBM Data Server Driver for JDBC and SQLJ type 2 connectivity
This configuration option is suitable especially for Java applications that run on the same z/OS system or System z logical partition (LPAR) and access DB2 data locally.
Type 2 driver is needed for running Java stored procedures on DB2 for z/OS.
The DB2 JDBC type 2 Driver for LUW (DB2 JDBC type 2 Driver) is deprecated. Move your Java applications to use the IBM Data Server Driver for JDBC and SQLJ.
Figure 3-4 shows types of type 2 connectivity with IBM Data Server Driver for JDBC and SQLJ.
Figure 3-4 Type 2 connectivity with IBM Data Server Driver for JDBC and SQLJ
3.2.2 Limited block fetch extended to the JCC type 2 drivers
Over the past several DB2 versions, processing between the DB2 DDF and DBM1 address space was optimized and zIIP redirection has significantly reduced chargeable central processors consumption. Other improvements have included:
•Limited block fetch
•LOB progressive streaming
•Implicit CLOSE
These improvements were not available to local Java and ODBC applications that did not always perform faster compared to the same application called remotely. These improvement to remote Java applications were described in theDB2 9 for z/OS Performance Topics, SG24-74733 and the DB2 Version 9.1 for z/OS Application Programming and SQL Guide, SC18-9841. Refer to these documents for details about LOB progressive streaming and implicit CLOSE.
With DB2 10, many of these improvements are implemented for local Java applications using ODBC or JDBC. You can expect significant performance improvement for applications with the following queries:
• Queries that return more than 1 row
• Queries that return LOBs
Limited block fetch (LBF) support has been extended to the JCC type 2 drivers on z/OS.
This technology, already available in the JCC type 4 and the distributed ODBC/CLI drivers, can provide dramatic improvements for applications involving large result set transfers; IBM observed more than 160% improvements in elapsed time and more than 170% improvements in CPU time in applications getting the advantages of this enhancement. This change leverages the drivers' functionalities and removes an inhibiting factor to the deployment of the Type 2 drivers for z based Java applications. The JCC type 2 driver gets installed or updated automatically when DB2 10 is installed.
This improvement is enabled by default, available in DB2 10 CM mode and there is no configuration required. It is not supported in JDBC/SQLJ stored procedures.
The number of rows returned per call depends on the buffer size (32767 to 262143 bytes with DB2 10), which is controlled by the queryDataSize property. queryDataSize specifies a hint that is used to control the amount of query data, in bytes, that is returned from the data source on each fetch operation. This value can be used to optimize the application by controlling the number of trips to the data source that are required to retrieve data.
Appropriate tuning of the DataSource property queryDataSize can improve performance by reducing the number of messages required between DB2 10 and a Java application when using JDBC type 2 driver and running on z/OS. This property also applies to the JDBC type 4 driver. Consider using a queryDataSize value bigger than 32 KB for large result sets if the utilization of a bigger buffer reduces the number of messages between DB2 and
the application.
Regression is possible for simple OLTP transactions with single row result sets. In this case, LBF can be disabled through the configuration keyword: db2.jcc.override.enableT2zosLBF=2
3.3 High availability configuration options
The Sysplex built in features of the DB2 Connect clients provides the highest availability and fault tolerance possible with minimum configuration and application impact. This support is available for applications that use Java clients (JDBC, SQLJ, or pureQuery), or non-Java clients (ODBC, CLI, .NET, OLE DB, PHP, Ruby, or embedded SQL).
3.3.1 How to make your client application sysplex aware
A DB2 data sharing group is accessed using its group location name, a Sysplex wide dynamic virtual IP address, and the group port. The Sysplex IP address routes to all members in the group. It called the DB2 group IP address. This address is used to make the initial connection to the group. The group IP address should be distributed allowing connections to work as long a one member is started. This eliminates the initial connection point of failure. After it is connected to the group, the z/OS Workload Manger (WLM) provides a server list containing members used by the client in its routing decisions. This list is cached in the client. Some servers might not appear in the list due to WLM balancing decisions. The list returns automatically on connection boundaries and optionally on transaction boundaries.
A client configuration parameter can be set to ensure the list stays current. The default life span of the cached server list is 10 seconds. This list contains the member IP address and WLM weight for each data sharing group member. With this information, the client distributes transactions in a balanced manner, and seamlessly reroutes work even when there is a network failure, a member failure, a member slowdown, or when a member is quiesced
for maintenance.
3.3.2 The difference between connections and transports
Sysplex workload balancing (WLB) feature supports transaction-level workload balancing for connections accessing a DB2 data sharing group. When a client is enabled with sysplex workload balancing, balancing decisions are performed at the start of each transaction. If just using the z/OS Sysplex Distributor, balancing decisions are performed at the start of each connection. Typically connection level balancing is not effective for most DB2 applications because connections have a long life.
After sysplex WLB is enabled in the data server driver client, application connections are no longer physical connections to DB2. Only when a connection is in use, a physical connection to DB2 is active. While a connection is not in use, the driver pools these connections. This pool of driver maintained connections are called transports. Transports are only associated with an application when a new transaction is started. A single transport can be used by many application connections. DB2 identifies unused transports as inactive connections. When a transaction is started, DB2 associates a thread with the inactive connection and associates to a thread called an active data base access thread (DBAT).
Figure 3-5 is an example of the Java driver but both clients have the same feature.
Figure 3-5 Java driver
At the start of each new transaction, the client reads the cached server list to identify a member that has unused capacity, and looks in the transport pool for an idle transport that is tied to the member. An idle transport is a transport that has no associated connection. If an idle transport is available, the client associates the connection with the transport. If after a user-configurable timeout period (db2.jcc.maxTransportObjectWaitTime for a Java client or maxTransportWaitTime for other clients), no idle transport is available in the transport pool and no new transport can be allocated because the transport pool reached its limit, an exception is returned to the application.
When the transaction runs, it accesses the server that is tied to the transport. When the transaction ends, the client verifies with the server that transport reuse is allowed for the connection. If the server identifies that the transport reuse is allowed, the server returns a list of SET statements for special registers that apply to the execution environment for the connection. The client caches these as SQL SET statements, which it replays to reconstruct the execution environment when the connection is associated with a new transport.
The server does not allow a transport to be reused if the connection has persistent resources opened such as an open held cursors or a global temporary tables that must maintained with the application until these resources are closed or dropped. To improve Sysplex workload balancing, it is important the application close any held cursors or drop any temporary tables when no longer needed. This allows database access threads to be effectively utilized by other applications.
3.3.3 What JCC client properties need to be changed
When enabling a JCC client to utilize Sysplex workload balancing and automatic client reroute, it is important to review and set the client properties that are associated with both functions. Starting in DB2 Connect V9.7 Fix Pack 6, JCC client starts to set the recommended defaults to the associated properties. Earlier JCC client driver levels, the client property defaults are not applicable to DB2 for z/OS and need to be reviewed and changed.
Generally, it is recommended to have the application always review and set the client properties. Proper setting of this information allows better isolation of problems, better classification of work which allows workload balancing to perform more efficiently. The DBA can quickly use client info to isolate issues to the specific client and even to the specific transaction.
Client information properties are managed by the application and need to be set prior to running the first SQL statement in each transaction if you want to use the client strings for WLM classification. The more granular you set and manage these properties, the more effective they are in managing the workload. On DB2, the client information can be used by WLM to classify work, is displayed in DB2 messages and is included in DB2 accounting data.
It is recommended to not use client affinities when accessing DB2 for z/OS Client affinities is not applicable to a DB2 data sharing environment, because all members of a data sharing group can access data concurrently.
How to enable Sysplex workload balancing and automatic client reroute for Java client
You should always configure Sysplex workload balancing and automatic client reroute together. When you configure a JCC client to use Sysplex WLB, automatic client reroute is also enabled by default. Therefore, you need to change JCC client properties related to automatic client reroute to control the reroute operation. Setting the enableSysplexWLB property to true for the JCC driver enables the Sysplex feature.
Table 3-3 shows the suggested property values for Java client enabled with Sysplex feature.
For details, see DB2 10 for z/OS Application Programming Guide and Reference for Java, SC19-2970.
Table 3-3 Java client Sysplex property definitions
Property
Suggested value
Description
enableSysplexWLB
true
Enable Sysplex workload balancing and seamless automatic client reroute.
maxTransportObjects
Set to the number of concurrent transactions times the number of DB2 members
Maximum number of connections that the requester can make to the data sharing group.
maxTransportObjectIdleTime
30 seconds
Maximum elapsed time in seconds before an idle transport is dropped.
maxTransportObjectWaitTime
1 second
Time in seconds that the client waits for a transport to become available. When an application waits for longer than this value, the global transport object pool throws an SQLException.
maxRefreshInterval
30 seconds
Maximum amount of time in seconds between refreshes of the client copy of the server list.
memberConnectTimeout
1 second
Number of seconds that client application wait before routing to the next IP address in the server list.
maxRetriesForClientReroute
5 times
Number of times to retry after a connection failure before retrying the connection string
resultSetHoldability
CLOSE_CURSORS_AT_COMMIT(2)
Controls whether the cursor stays open across commit. This value overrides the default holdability for the connection.
queryCloseImplicit
QUERY_CLOSE_IMPLICIT_COMMIT(3)
Closes the cursor at the server after all the result sets are exhausted.
InterruptProcessingMode
INTERRUPT_PROCESSING_MODE_CLOSE_SOCKET(2)
This property is used to specify when an application executes the Statement.cancel method. Connection is dropped and the transaction is rolled back.
queryTimeoutInterruptProcessingMode
INTERRUPT_PROCESSING_MODE_CLOSE_SOCKET(2)
This property is used to specify when the query timeout interval for a Statement object expires. Connection is dropped and the transaction is rolled back.
How to collect DB2 group connection information
The DB2 -DISPLAY DDF DETAIL command issued on a member can be used to obtain the DB2 group and member information needed to construct the connection string for both the non-Java and Java drivers.
Example 3-3 shows the output of -DISPLAY DDF DETAIL command.
Example 3-3 DISPLAY DDF DETAIL output
14.10.29 STC00311 DSNL080I @ DSNLTDDF DISPLAY DDF REPORT FOLLOWS:
DSNL081I STATUS=STARTD
DSNL082I LOCATION LUNAME GENERICLU
DSNL083I STLEC1 USIBMSY.SYEC1DB2 USIBMSY.SYEC1GLU
DSNL084I TCPPORT=446 SECPORT=0 RESPORT=5001 IPNAME=NONE
DSNL085I IPADDR=::9.30.119.22
DSNL086I SQL DOMAIN=dvipa22.vmec.svl.ibm.com
DSNL086I RESYNC DOMAIN=dvipa23.vmec.svl.ibm.com
DSNL087I ALIAS PORT SECPORT STATUS
DSNL088I STLEC1ALIASSUB12 5052 0 STATIC
DSNL089I MEMBER IPADDR=::9.30.119.23
DSNL090I DT=I CONDBAT= 25000 MDBAT= 300
DSNL092I ADBAT= 0 QUEDBAT= 0 INADBAT= 0 CONQUED= 0
DSNL093I DSCDBAT= 0 INACONN= 0
DSNL100I LOCATION SERVER LIST:
DSNL101I WT IPADDR IPADDR
DSNL102I 21 ::9.30.119.23
DSNL102I 21 ::9.30.119.26
DSNL102I 21 ::9.30.119.28
DSNL105I CURRENT DDF OPTIONS ARE:
DSNL106I PKGREL = COMMIT
DSNL099I DSNLTDDF DISPLAY DDF REPORT COMPLETE
The following is the explanation of various messages extracted from the -DISPLAY DDF DETAIL command above:
DSNL083I STLEC1                                            Group location
DSNL085I IPADDR=::9.30.119.22                            Group distributed DVIPA address
DSNL084I TCPPORT=446 RESPORT=5001                    Group port and the resync port
DSNL088I STLEC1ALIASSUB12 5052 0 STATIC          Defined DB2 location alias and port
DSNL089I MEMBER IPADDR=::9.30.119.23               Member IP address in the location alias
How to configure the Java client with high availability
There are two aspects that you need to consider:
•First, you need to set the global properties file. See also 5.10, “Configuring the JCC properties file in WebSphere Application Server” on page 282 for the WebSphere side.
•Second, you need to set the data source properties.
Setting the global properties file
The java Sysplex configuration properties are set in a global property file DB2Jccconfiguration.Properties. The file lets you set Sysplex property values that have driver-wide scope. Those settings apply across applications and DataSource instances. You can change the settings without having to change application source code or DataSource characteristics. The JDBC and SQLJan example of settings for the DB2JccConfiguration.properties file.
Example 3-4 Sample settings for the DB2JccConfiguration.properties
db2.jcc.maxRefreshInterval=30
db2.jcc.minTransportObjects=0
db2.jcc.maxTransportObjects=1000
db2.jcc.maxTransportObjectWaitTime=1
db2.jcc.maxTransportObjectIdleTime=30
Setting the data source properties
Example 3-5 shows the recommended data source properties using a sample
Java application.
Example 3-5 Data source properties using a sample Java application
public class SampleDS
{
public static void main(String[] args) throws SQLException
{
DB2SimpleDataSource ds = new DB2SimpleDataSource();
ds.setServerName("DB2IP.ibm.com");
ds.setPortNumber(12345);
ds.setDatabaseName("DB2ServerName");
ds.setUser("USERID");
ds.setPassword("PASSWORD");
// High availability properties
ds.setEnableSysplexWLB(true);
ds.setLoginTimeout(3);
ds.setMaxRetriesForClientReroute(5);
ds.setInterruptProcessingMode(DB2BaseDataSource.
INTERRUPT_PROCESSING_MODE_CLOSE_SOCKET);
// Performance and storage consumption properties
ds.setProgressiveStreaming(DB2BaseDataSource.YES);
 
// Thread Utilization properties
ds.setResultSetHoldability(DB2BaseDataSource.CLOSE_CURSORS_AT_COMMIT);
ds.setQueryCloseImplicit(DB2BaseDataSource.QUERY_CLOSE_IMPLICIT_YES);
 
try
{
DB2Connection con = (DB2Connection)ds.getConnection();
// Thread Utilization properties
con.setAutoCommit(false);
 
// Problem determination correlation settings
con.setDB2ClientApplicationInformation("This is a sample application");
con.setDB2ClientUser("A Test End User1");
con.setDB2ClientWorkstation("A Test End Wrkstn1");
 
PreparedStatement ps;
String insertsql = "INSERT INTO TABLE1 VALUES (?,?)";
ps = con.prepareStatement(insertsql);
for (int i =1;i<=200; i++){
ps.setInt(1,i+1);
ps.setString(2,i+"Test Sample : This is a Long Test String"+i);
ps.addBatch(); // Add batch processing
}
ps.executeBatch(); // Execute batch processing
String psSQL = "SELECT * FROM TABLE1";
ps = con.prepareStatement (psSQL);
// Performance impact properties on SQL level
ps.setFetchSize(199);
ps.setMaxRows(199);
ResultSet rs = ps.executeQuery ();
while (rs.next()){
//fetch to the end;
};
 
rs.close(); // Close cursor when done using it
ps.close();
con.commit(); // Commit on regular basis
}
catch(Exception e)
{
System.out.println("main() Exception: " + e.getMessage());
}
}
}
For setting the data source properties in WebSphere Application Server, see 5.3.3, “Defining a JDBC type 2 data source” on page 233.
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset