WebSphere Application Server infrastructure setup
Enterprises today typically use Java as the language of choice when developing enterprise class applications. These applications are typically hosted in an application server environment. WebSphere Application Server is usually the server of choice to host these applications. These applications require access to data. The data typically is on an enterprise class relational database, such as DB2 for z/OS.
Customers have many questions about how best to configure a WebSphere Application Server environment to access DB2 for z/OS. Here are some typical challenges
and questions:
•What do I need to consider when I configure WebSphere Application Server, which accesses DB2 for z/OS?
•How do I configure JDBC type 2 driver access to DB2 for z/OS by using WebSphere Application Server on z/OS?
•I am a DBA. I do not know which application a SQL statement is coming from. What can I configure in my WebSphere Application Server to help me track this statement without an application change?
•What are the preferred practices for JDBC type 4 access to DB2 for z/OS to best use sysplex workload balancing?
•Why is there an XA provider for JDBC type 4 access and nothing like that for JDBC type 2 access to DB2 for z/OS?
•I do not want to grant a user ID that is used in my data source DBADM or has access to DB2 tables. I am worried that the user ID might be compromised. How can I avoid this situation in a WebSphere Application Server environment that is accessing DB2 for z/OS?
•There are many levels of JDBC driver properties; which should I use when?
•What are the preferred practices for WebSphere Application Server connection pool and prepared statement cache settings?
•What do I need to do in WebSphere Application Server to help me classify JDBC type 4 access to DB2 for z/OS in WLM?
•How do I enable JDBC driver tracing in my WebSphere Application Server?
•I am a DBA; how do I make sure that WebSphere Application Server applications are using the correct isolation level?
•How does failover work with a JDBC type 2 connection from WebSphere Application Server on z/OS to DB2 for z/OS?
In this chapter, we build an example environment that we use to provide the answers to
these questions.
This chapter covers the following topics:
5.1 Configuring WebSphere Application Server Network Deployment on z/OS
A WebSphere Application Server Network Deployment configuration (on all platforms) should be set up for high availability and scalability. It is the gold standard of deployments. High availability, also known as resiliency, is the ability of a system to tolerate a number of failures and remain operational. It is achieved by adding redundancy to the infrastructure to manage failures. It is critical that your infrastructure continues to respond to client requests regardless of the circumstances and that you remove all single points of failure. Planning for a highly available system takes planning across all components of your infrastructure because the overall infrastructure is available only when all of the components are available. As part of the planning, you must define the level of high availability that is needed in the infrastructure.
We chose to use WebSphere Application Server on z/OS for our example. Here are the main reasons that we chose WebSphere Application Server on z/OS:
1. WebSphere Application Server on System z has the same features and functions of WebSphere Application Server on other platforms.
2. We want to show the features of the JDBC type 2, which is the only driver that is normally used with WebSphere Application Server on z/OS to access the local DB2 for z/OS.
We used WebSphere Application Server V8.5. We built the WebSphere Application Server Network Deployment topology spread across two LPARS, as shown in Figure 5-1.
Figure 5-1 WebSphere Application Server Network Deployment configuration
The two node cell was built by following the preferred practices recommendations. These recommendations are found in the WebSphere Application Server Information Center and various documents, such as IBM Redbooks publications and techdocs. Here is the link to the Information Center for WebSphere Application Server V8.5:
We built the application server cluster MZSR014, which is spread across two LPARS: SC63 and SC64. The Deployment Manager MZDMGR was built to run on SC64.
The application that we used is the Apache DayTrader Sample application. Information regarding this application can be found at the following website:
This application was installed on the MZSR014 cluster.
For more information about our configuration, see Appendix B, “Configuration and workload” on page 511.
5.2 Configuring WebSphere Application Server for JDBC type 4 XA access
This section looks at the following items:
5.2.1 Defining a DB2 JDBC XA provider
To define a DB2 JDBC XA provider, complete the following steps:
1. In the navigation window of the administration console of WebSphere Application Server, expand Resources. Under resources, expand JDBC and you see the window that is shown
in Figure 5-2.
Figure 5-2 WebSphere navigation window
2. Double-click JDBC providers and you see the window that is shown in Figure 5-3. This window shows a list of existing JDBC providers that are defined on your server.
Figure 5-3 Existing JDBC providers
Resources such as Java Database Connectivity (JDBC) providers, namespace bindings, or shared libraries can be defined at multiple scopes. Resources that are defined at more specific scopes override duplicate resources that are defined at more general scopes:
 – The application scope has precedence over all the other scopes.
 – For WebSphere Application Server Network Deployment, the server scope has precedence over the node, cell, and cluster scopes.
 – For WebSphere Application Server Network Deployment, the cluster scope has precedence over the node and cell scopes.
 – The node scope has precedence over the cell scope.
In this example, select a cell scope. Click New. The window that is shown in
Figure 5-4 opens.
Figure 5-4 New JDBC provider definition
3. In this window, complete the following steps:
a. Select DB2 as the Database type from the drop-down menu.
b. Select DB2 Universal JDBC Driver Provider as the provider type from the
drop-down menu.
c. Select the XA data source from the drop-down menu for the implementation type. The IBM Data Server Driver for JDBC and SQLJ provides a separate implementation class that supports XA transactions. This is true only for JDBC type 4 connections. If the application does not need XA capability, then select the Connection pool data source, which supports normal 1-phase commit transactions.
d. Enter a provider name. In this example, use DB2 Universal JDBC Driver
Provider (XA).
Click Next. The window that is shown in Figure 5-5 opens.
Figure 5-5 Class path definition
4. The purpose of this window is to define the location of the IBM Data Server Driver for JDBC and SQLJ classes. This is done by using variables. The usage of variables provides flexibility so that you can define the location at a single point and use that point for many JDBC providers that can be defined in a WebSphere Application Server. Write down the following variables from the window that is shown in Figure 5-5:
 – DB2UNIVERSAL_JDBC_DRIVER_PATH
 – UNIVERSAL_JDBC_DRIVER_PATH
We show how to define these variables and their values later in this book.
Click Next. The summary window that is shown in Figure 5-6 on page 213 opens.
Figure 5-6 Summary window for JDBC provider
5. Click Finish and then save the changes.
You have defined a JDBC type 4 XA provider successfully.
5.2.2 Defining environment variables at the location of the IBM Data Server Driver for JDBC and SQLJ classes for JDBC type 4 connectivity
To define environment variables at the location of the IBM Data Server Driver for JDBC and SQLJ classes for JDBC type 4 connectivity, complete the following steps:
1. In the navigation window of the administrative console of WebSphere Application Server, expand Environment, as shown in Figure 5-7.
Figure 5-7 Environment window
2. Click WebSphere variables. The window that is shown in Figure 5-8 opens.
Figure 5-8 List of WebSphere variables
3. By default, the variables are defined to WebSphere Application Server at all scopes. The variables do not have specific values defined by default. To see the variables, click the filter icon, as shown in Figure 5-9.
Figure 5-9 Filtering variables
4. Enter DB2 in to the search terms and click Go. A window with the default list of variables opens, as shown in Figure 5-10.
Figure 5-10 List of DB2 related variables
5. The variables are defined at all possible scopes in the cell. Pick the appropriate scope. In this example, pick the DB2UNIVERSAL_JDBC_DRIVER_PATH variable at the
cell scope.
The window that is shown in Figure 5-11 opens.
Figure 5-11 Variable and scope
6. Double-click the variable name. The window that is shown in Figure 5-12 opens.
Figure 5-12 Variable for DB2UNIVERSAL_JDBC_DRIVER_PATH
7. Enter the location of the IBM Data Server Driver for JDBC and SQLJ classes in the value text box. In this example, enter /usr/lpp/db2/d0zg/jdbc/classes, as shown in Figure 5-13.
Figure 5-13 Location of the IBM Data Server Driver for JDBC and SQLJ classes
8. Click Apply and then save your configuration. Repeat the same steps for the UNIVERSAL_JDBC_DRIVER_PATH variable.
You have defined the variables at the cell scope successfully.
5.2.3 Defining a JDBC type 4 XA data source
To define a JDBC type 4 XA data source, complete the following steps:
1. In the navigation window of the administrative console of the WebSphere Application Server, expand Resources and click Data Sources, as shown in Figure 5-14.
Figure 5-14 WebSphere navigation window
The window that is shown in Figure 5-15 opens. This window shows a list of existing JDBC data sources that are defined in your environment.
Figure 5-15 JDBC data sources
2. In this example, select the cell scope. Click New. The window that is shown in
Figure 5-16 opens.
Figure 5-16 Data source definition
3. In this window, enter a name for the data source and the JNDI name. For this example, enter TradeDataSourceXA for the data source name and jdbc/Trade for the JNDI name.
Click Next. The window that is shown in Figure 5-17 opens.
4. In this window, you need a JDBC type 4 XA connection, so select the DB2 Universal JDBC Driver Provider (XA) that was created earlier.
Figure 5-17 Selecting the JDBC provider
Click Next. The window that is shown in Figure 5-18 opens.
Figure 5-18 Database properties
5. In this window, enter the following values
 – For driver type, select 4 from the drop-down menu, which directs WebSphere Application Server to use a JDBC type 4 connection to the database.
 – Enter the DB2 for z/OS location name for the name of the database. The location name is very specific to DB2 for z/OS. In this example, enter DB0Z.
 – The server name is the IP address at which DB2 for z/OS is located. It can be an IP address or a DNS name. In this example, enter 9.12.4.153, which is the group DVIPA address of our DB2 for z/OS data sharing group. In an ideal setup, the server name should be the group DVIPA address of a DB2 for z/OS data sharing group. You must use this value if you want to use the benefits of sysplex workload balancing. You must not use the member-specific VIPA or IP addresses. If you do not have a data sharing group, then use the IP address or DNS name of the DB2 for z/OS instance. These three values are specific to a JDBC type 4 connection. The values that are entered for a JDBC type 2 connection are described later.
 – Enter the port number on which DB2 for z/OS is listening, In this example environment, DB2 use port number 39000.
You can find the values in Figure 5-18 by running DISPLAY DDF. Example 5-1 shows the command and the values in the example z/OS system.
Example 5-1 DISPLAY DDF command for ports
DSNL080I -D0Z2 DSNLTDDF DISPLAY DDF REPORT FOLLOWS:
DSNL081I STATUS=STARTD
DSNL082I LOCATION LUNAME GENERICLU
DSNL083I DB0Z -NONE -NONE
DSNL084I TCPPORT=39000 SECPORT=0 RESPORT=39003 IPNAME=IPDB0Z
DSNL085I IPADDR=::9.12.4.153
DSNL086I SQL DOMAIN=d0zg.itso.ibm.com
DSNL086I RESYNC DOMAIN=d0z2.itso.ibm.com
DSNL087I ALIAS PORT SECPORT STATUS
DSNL088I D0Z2 0 0 STARTD
DSNL089I MEMBER IPADDR=::9.12.4.142
DSNL105I CURRENT DDF OPTIONS ARE:
DSNL106I PKGREL = COMMIT
DSNL099I DSNLTDDF DISPLAY DDF REPORT COMPLETE
Click Next. The window that is shown in Figure 5-19 opens.
Figure 5-19 Security alias setup
6. The information in this window directs WebSphere Application Server about what user ID to use when you connect to DB2 for z/OS. Here is a brief description of what each
ID means:
Authentication alias for XA recovery: This alias is used by WebSphere Application Server when it tries to resolve any in-doubt transactions as part of XA recovery.
Component-managed Authentication Alias: This is the user ID/password that is used to access DB2 with component managed security. The alias must be defined beforehand.
Container-managed Authentication Alias: This is the user ID/password that is used to access DB2 with container managed security. The alias must be
defined beforehand.
These aliases are called J2C aliases. They can be defined by using the
administration console.
Click Next. The window that is shown in Figure 5-20 opens.
Figure 5-20 Summary of data source definition
This window is a summary window, which shows all the different values that you set. Click Finish and save the changes. You have a JDBC type 4 XA data source that is defined.
5.3 Configuring WebSphere Application Server for JDBC type 2 access
This section describes the following items:
5.3.1 Defining a DB2 JDBC provider
To definite a DB2 JDBC provider, complete the following steps:
1. In the navigation window of the administration console of WebSphere Application Server, expand Resources. Under Resources, expand JDBC and you see the window that is shown in Figure 5-21.
Figure 5-21 The administration console window of WebSphere Application Server
2. Double-click JDBC providers and the window that is shown in Figure 5-22 opens. This window shows a list of existing JDBC providers that are defined in your server.
Figure 5-22 List of existing JDBC providers
3. The JDBC provider must be defined with the appropriate scope. See the scope note in Figure 5-22 on page 224. In this example, select the cell scope. Click New and the window that is shown in Figure 5-23 opens.
Figure 5-23 JDBC provider that is defined with the cell scope
In this window, complete the following steps, as shown in Figure 5-24:
a. Select DB2 as the Database type from the drop-down menu.
b. Select DB2 Universal JDBC Driver Provider as the provider type from the
drop-down menu.
c. Select Connection pool data source for the implementation type from the
drop-down menu.
After you select the Connection pool data source as the implementation type, you can repeat data sources for the third time. Data sources that use this provider support only 1-phase commit processing, unless you use driver type 2 with the application server
for z/OS.
If you use the application server for z/OS, driver type 2 uses RRS and supports 2-phase commit processing. The IBM Data Server Driver for JDBC and SQLJ has only one implementation class, which supports both 1-phase and 2-phase commit processing. Hence, it is not necessary to define separate JDBC providers, one each for 1-phase and 2-phase commit processing.
d. Enter a provider name. In this example, use DB2 Universal JDBC Driver Provider.
Figure 5-24 New JDBC provider definition
Click Next. The window that is shown in Figure 5-25 on page 227 opens.
Figure 5-25 Driver classes location
4. The purpose of this window is to define the location of the IBM Data Server Driver for JDBC and SQLJ classes. The one difference with a JDBC type 2 connection on z/OS is the need to define the native library path. All of this is done by using variables.
The usage of variables provides flexibility so that you can define the location in a single point and use that point for many JDBC providers that can be defined in a WebSphere Application Server. Write down the following variables from this window.
 – DB2UNIVERSAL_JDBC_DRIVER_PATH
 – UNIVERSAL_JDBC_DRIVER_PATH
 – DB2UNIVERSAL_JDBC_DRIVER_NATIVEPATH
We show how to define these variables and their values later in this book.
Click Next. The summary window that is shown in Figure 5-26 opens.
Figure 5-26 Summary of new JDBC provider definition
5. Click Finish and then save the changes.
You have created a DB2 Universal JDBC provider that is compatible with type 2 connectivity on z/OS.
5.3.2 Defining environment variables to the location of the IBM Data Server Driver for JDBC and SQLJ classes for JDBC type 2 connectivity
To define environment variables at the location of the IBM Data Server Driver for JDBC and SQLJ classes for JDBC type 2 connectivity, complete the following steps:
1. In the navigation window of the administrative console of WebSphere Application Server, which is shown in Figure 5-21 on page 223, expand Environment, as shown
in Figure 5-27.
Figure 5-27 Environment window
2. Click WebSphere variables. The window that is shown in Figure 5-28 opens.
Figure 5-28 List of WebSphere variables
3. By default, the variables are defined to WebSphere Application Server at all scopes. The variables do not have the values defined by default. To see the variables, click the filter icon, as shown in Figure 5-29.
Figure 5-29 Filter variables
4. Enter DB2 in the search terms and click Go. A window that shows the default list of variables opens, as shown in Figure 5-30.
Figure 5-30 List of available variables
The variables are defined at all possible scopes in the cell. Pick the appropriate scope. In this example, pick the DB2UNIVERSAL_JDBC_DRIVER_PATH variable as the cell scope, as shown in Figure 5-31.
Figure 5-31 Variable cell scope mzcell
5. Double-click the variable name. The window that is shown in Figure 5-32 opens, where no value is set for the Type 2 driver.
Figure 5-32 DB2UNIVERSAL_JDBC_DRIVER_PATH variable
6. Enter the location of the IBM Data Server Driver for JDBC and SQLJ classes in the value text box. In this example, enter /usr/lpp/db2/d0zg/jdbc/classes, as shown in Figure 5-33.
Figure 5-33 Location of the driver classes
7. Click Apply and then save the changes. Repeat the same steps for the UNIVERSAL_JDBC_DRIVER_PATH variable.
8. For JDBC type 2 connectivity, you must define the path of the native libraries by assigning a value to DB2UNIVERSAL_JDBC_DRIVER_NATIVEPATH variable, which points to the location of the native libraries.
Double-click the DB2UNIVERSAL_JDBC_DRIVER_NATIVEPATH variable and the window that is shown in Figure 5-34 on page 233 opens. Enter the location of the native libraries, which in this example is /usr/lpp/db2/d0zg/jdbc/lib/.
Figure 5-34 Location of the native libraries
9. Click Apply and save the changes.
5.3.3 Defining a JDBC type 2 data source
To define a JDBC type 2 data source, complete the following steps:
1. In the navigation window of the administrative console of the WebSphere Application Server, expand Resources and click Data Sources, as shown in Figure 5-35.
Figure 5-35 Administration window for WebSphere
The window that is shown in Figure 5-36 opens, which shows a list of the existing JDBC data sources that are defined in your environment.
Figure 5-36 List of JDBC data sources
In this example, select the cell scope and click New. The window that is shown in
Figure 5-37 opens.
Figure 5-37 Window for entering data source information
2. In this window, enter a name for the data source and the JNDI names. In this example, enter TradeDatasourceType2 for the data source name and jdbc/Trade DataSourceType2 for the JNDI name, as shown in Figure 5-38.
Figure 5-38 Defining the data source and JNDI names
Click Next and the window that is shown in Figure 5-39 opens.
3. In this window, select the DB2 Universal JDBC Driver Provider that was created earlier because you need a JDBC type 2 connection.
Figure 5-39 Selecting the JDBC type 2 Driver
Click Next. The window that is shown in Figure 5-40 opens.
Figure 5-40 Database properties
4. In this window, enter the following values:
 – For driver type, select 2 from the drop-down menu, which directs WebSphere Application Server to use a JDBC type 2 connection to the database.
 – The DB2 for z/OS location name for the name of the database. The location name is very specific to DB2 for z/OS. In this example, enter DB0Z.
 – The server name and port number should be blank.
You can obtain the values for the entries that are shown in Figure 5-40 by running - DISPLAY DDF. Example 5-2 shows the command and the values in the example z/OS system.
Example 5-2 - DISPLAY DDF command to verify DB2 definitions
DSNL080I -D0Z2 DSNLTDDF DISPLAY DDF REPORT FOLLOWS:
DSNL081I STATUS=STARTD
DSNL082I LOCATION LUNAME GENERICLU
DSNL083I DB0Z -NONE -NONE
DSNL084I TCPPORT=39000 SECPORT=0 RESPORT=39003 IPNAME=IPDB0Z
DSNL085I IPADDR=::9.12.4.153
DSNL086I SQL DOMAIN=d0zg.itso.ibm.com
DSNL086I RESYNC DOMAIN=d0z2.itso.ibm.com
DSNL087I ALIAS PORT SECPORT STATUS
DSNL088I D0Z2 0 0 STARTD
DSNL089I MEMBER IPADDR=::9.12.4.142
DSNL105I CURRENT DDF OPTIONS ARE:
DSNL106I PKGREL = COMMIT
DSNL099I DSNLTDDF DISPLAY DDF REPORT COMPLETE
Click Next and the window that is shown in Figure 5-41 on page 237 opens.
5. In this window, enter the authentication alias that should be used by WebSphere Application Server when it connects to DB2 for z/OS. This authentication alias must be defined beforehand. You have two options:
 – Component-managed Authentication Alias:
This is the user ID/password that is used to access DB2 with
component-managed security.
 – Container-managed Authentication Alias:
This is the user ID/password that is used to access DB2 with
container-managed security.
These aliases are known as J2C aliases. They can be defined by using the
administration console.
By default, the user ID that WebSphere Application Server on z/OS runs under is used. This is possible only for a JDBC type 2 connection, which means that the user ID under which WebSphere Application Server runs under must have the appropriate access to the DB2 objects that are used in the application that uses this data source.
We can also override that user ID and provide an authentication alias, which is then used.
In this example, use the TradeDataSourceAuthData authentication alias.
Figure 5-41 Security aliases
Click Next, which opens a summary window that shows all the different values you have set so far, as shown in Figure 5-42.
Figure 5-42 Summary of the type 2 Driver setup
6. Click Finish and then save the changes.
You have a JDBC type 2 data source that is defined.
5.3.4 Configuring a subsystem ID on the data source
WebSphere Application Server on z/OS, when it connects to DB2 for z/OS using a JDBC type 2 connection, does not connect to the DB2 using the location name that is provided by the connection application. It uses the value that is specified by a data source custom property called subsystem ID (ssid).
This property can be set to specify the DB2 subsystem identifier (not the DB2 location name) if the DB2 system is not part of a data sharing group. If DB2 is part of a data sharing group, then specifying the group attach name as the value is recommended because if customers have multiple members of a data sharing group in the same LPAR, specifying the group attach name as the value for the ssid property allows type 2 connections to fail over to the second DB2 member of the same data sharing group in the same LPAR if the one of the DB2 members fails.
The only time when ssid should be used instead of a group attach name is if there is a requirement that the WebSphere Application Server connect to only a specific
DB2 subsystem.
JDBC type 2 connections do not workload balance between multiple DB2 members of a data sharing group in a single LPAR. The connections randomly pick one of the DB2 members to use for all connections and then, if that DB2 member fails, fail over to the second DB2 member of the same data sharing group in the same LPAR. This situation happens only if you specify a group attach name as the value of the ssid data source custom property in WebSphere Application Server.
If you use the ssid property when it is not provided, then the driver uses the ssid that it finds in the DSNHDECP load module. You load DSNHDECP by using the search sequence that specified in the STEPLIB environment variable or the //STEPLIB DD name concatenation. If that DSNHDECP load module does not accurately reflect the correct subsystem, or multiple subsystems are using a generic DSNHDECP, then there might be problems in connecting
to DB2.
Another reason to use the ssid property for JDBC type 2 connections to DB2 from WebSphere Application Server on z/OS is so that a single WebSphere Application Server can connect to multiple DB2 subsystems. Then, different applications that are deployed in the same WebSphere Application Server can connect to different DB2 subsystems in the same LPAR if they use different data sources and the ssid is set on each data source.
To configure the ssid on the data source, complete the following steps:
1. In the navigation window of the administrative console of the WebSphere Application Server, expand Resources, and click Data sources, as shown in Figure 5-43.
Figure 5-43 Administrative console of the WebSphere Application Server
2. The window that is shown in Figure 5-44 opens. This window shows a list of existing JDBC data sources that are defined in your environment. Click the TradeDataSourceType2 JDBC type 2 data source.
Figure 5-44 JDBC type 2 data source selection
3. The window that is shown in Figure 5-45 opens. Click Custom properties under the Additional properties section. The window that is shown in Figure 5-46 opens, which lists all the custom properties that are available to the data source.
Figure 5-45 Selecting Custom properties
Figure 5-46 List of custom properties
4. The property ssid is not defined by default. You can define it. Click New and a new window opens. For the ssid, enter the group attach name or the subsystem ID. In this example, enter the group attach name of the DB2 data sharing group D0ZG.
The group attach name can be obtained by running DISPLAY GROUP. Example 5-3 shows the output from that command and the group attach name.
Example 5-3 DISPLAY GROUP command to verify that the group attach name
DSN7100I -D0Z2 DSN7GCMD
*** BEGIN DISPLAY OF GROUP(DB0ZG ) CATALOG LEVEL(101) MODE(NFM )
PROTOCOL LEVEL(2) GROUP ATTACH NAME(D0ZG)
--------------------------------------------------------------------
DB2 DB2 SYSTEM IRLM
MEMBER ID SUBSYS CMDPREF STATUS LVL NAME SUBSYS IRLMPROC
-------- --- ---- -------- -------- --- -------- ---- --------
D0Z1 1 D0Z1 -D0Z1 ACTIVE 101 SC63 I0Z1 D0Z1IRLM
D0Z2 2 D0Z2 -D0Z2 ACTIVE 101 SC64 I0Z2 D0Z2IRLM
--------------------------------------------------------------------
SCA STRUCTURE SIZE: 8192 KB, STATUS= AC, SCA IN USE: 4 %
LOCK1 STRUCTURE SIZE: 8192 KB
NUMBER LOCK ENTRIES: 2097152
NUMBER LIST ENTRIES: 9324, LIST ENTRIES IN USE: 7
SPT01 INLINE LENGTH: 32138
*** END DISPLAY OF GROUP(DB0ZG )
DSN9022I -D0Z2 DSN7GCMD 'DISPLAY GROUP ' NORMAL COMPLETION
***
5. Enter the group name, as shown in Figure 5-47.
Figure 5-47 General properties definition
6. Click Apply and then save the changes.
Linking to the DB2 libraries
WebSphere Application Server on z/OS, when it is configured to use a JDBC type 2 connection to DB2 for z/OS, also requires access to three DB2 libraries:
•DB2xx.SDSNEXIT
•DB2xx.SDSNLOAD
•DB2xx.SDSNLOD2
WebSphere Application Server can access these libraries in three ways:
•The libraries can be placed in the LINKLIST of the z/OS operating system.
•The libraries can be added to the JCL of the startup procedure of the Application Server Servant by adding a STEPLIB.
•The libraries can be added by modifying the STEPLIB environment variable to include the DSNEXIT, DSNLOAD, and DSNLOD2 libraries.
This example uses the STEPLIB approach and adds the libraries to the servant region proclibs of the Deployment Manager and the WebSphere Application Server, as shown in Example 5-4. You must add to it to the Deployment Manager to test the connection.
Example 5-4 Application Server Servant libraries
//STEPLIB DD DSN=DB0ZT.SDSNEXIT,DISP=SHR
// DD DSN=DB0ZT.SDSNLOAD,DISP=SHR
// DD DSN=DB0ZT.SDSNLOD2,DISP=SHR
You have completed all the required steps to configure WebSphere Application Server for JDBC type 2 access to DB2.
5.4 Configuring WebSphere Application Server for sysplex workload balancing
The section shows how to enable sysplex workload balancing for a JDBC type 4 connection to DB2. This feature is available for both XA and non-XA data sources. This feature is not available on JDBC type 2 connections. This example is for an XA data source.
To configure WebSphere Application Server for sysplex workload balancing, complete the following steps:
1. In the navigation window of the administrative console of the WebSphere Application Server, expand Resources and click Data Sources, as shown in Figure 5-48.
Figure 5-48 Administrative console of the WebSphere Application Server
The window that is shown in Figure 5-49 on page 245 opens. This window shows a list of existing JDBC data sources that are defined in your environment.
Figure 5-49 List of existing JDBC data sources
2. Click TradeDatasourceXA and the window that is shown in Figure 5-50 opens. This window lists all the custom properties that are available to the data source.
Figure 5-50 List of custom properties that are available to the data source
3. The enableSysplexWLB property that is required to enable sysplex workload balancing is not present by default. You can add this property by clicking New. The window that is shown in Figure 5-51 opens.
Figure 5-51 Adding the enableSysplexWLB property
Complete the following steps:
a. Enter enableSysplexWLB for the property name.
b. Enter true for the value.
4. Click Apply and then save the changes. The data source is now enabled for sysplex
workload balancing.
5.5 Configuring client information in WebSphere Application Server
As more applications are written in Java that access data in DB2 for z/OS, the typical challenges that are faced by DBAs are that they often do not know which application the SQL statement comes from. To learn this information, you should set client information on the connection. This client information is passed to DB2 for z/OS and can be used to correlate requests. WebSphere Application Server (on all platforms) and DB2 (on all platforms) support this feature. The JDBC 4.0 specification adopted this functionality by providing an API
There are two places in WebSphere Application Server where you can set the client information properties.
•Data source custom properties
•Resource Reference extended data source properties
These properties can also be set in the application by using the JDBC 4.0 API setClientInfo. This action requires an application change and is typically required in situations in which client information settings can be determined and set only at run time. In all other situations, use data source custom properties or Resource Reference extended data source properties for easier system administration.
The following sections demonstrate how to set these properties in WebSphere Application Server. The approach is the same regardless of whether the application uses a JDBC type 2 or 4 connection (XA or non-XA).
5.5.1 Setting client information on a data source
To set client information on a data source, complete the following steps:
1. In the navigation window of the administrative console of the WebSphere Application Server, expand Resources and click Data Sources, as shown in Figure 5-52.
Figure 5-52 Administrative console of the WebSphere Application Server
The window that is shown in Figure 5-53 opens. This window shows a list of existing JDBC data sources that are defined in your environment.
Figure 5-53 List of existing JDBC data sources
2. Click TradeDatasourceXA and the window that is shown in Figure 5-54 opens.
Figure 5-54 TradeDatasourceXA data source is accessed
3. Click Custom properties. The panel that opens lists all the custom properties that are available. By default, the properties that are available are the ones that are shown
in Figure 5-55.
Figure 5-55 Available properties
4. By default, these properties do not have any values that are specified. You can set all the properties or any combination of them. In this example, set values for all of them. For example, to set a value for clientAccountingInformation, click the clientAccountingInformation property. The window that is shown in Figure 5-56 opens.
Figure 5-56 Set a value for clientAccountingInformation
5. Enter a string that identifies the application. In this example, use TradeClientAccountingInformation as the value, as shown in Figure 5-57.
Figure 5-57 Application identification string
6. Click Apply and then save the changes. Figure 5-58 shows all the values of the properties, which we set by repeating the steps in this section.
Figure 5-58 Properties values
5.5.2 Setting client information by using extended data source properties
In some customer environments, several applications share a single data source. This means the approach of setting client strings on the data source does not help identify the application. To address this issue, WebSphere Application Server allows individual applications to set these properties when they use resource references to access data sources. The approach is the same regardless of the application that is using a JDBC type 2 or 4 connection (XA or non-XA).
WebSphere Application Server requires your code to reference application server resources (such as data sources or J2C connection factories) through logical names, rather than access the resources directly in the Java Naming and Directory Interface (JNDI) name space. These logical names are called resource references.
WebSphere Application Server requires the usage of resource references for the
following reasons:
•If application code looks up a data source directly in the JNDI naming space, every connection that is maintained by that data source inherits the properties that are defined in the application. Then, you create the potential for numerous exceptions if you configure the data source to maintain shared connections among multiple applications. For example, an application that requires a different connection configuration might attempt to access that particular data source, resulting in application failure.
•It relieves the programmer from having to know the name of the actual data source or connection factory at the target application server.
You can set the default isolation level for a data source through resource references. With no resource reference, you get the default for the JDBC driver that you use.
The extended properties are described in the WebSphere Application Server Information Center, which can be found at the following URL:
Using resource reference extended properties
To demonstrate setting the client information by using resource reference extended properties, we used a simple application named D0ZG_WASTestClientInfo, which uses a resource reference. The application uses a JDBC type 4 XA data source.
Complete the following steps:
1. In the navigation window of the administration console of the WebSphere Application Server, expand Applications and Application Types, as shown in Figure 5-59.
Figure 5-59 Administration console of the WebSphere Application Server
2. Click WebSphere enterprise applications. The window that is shown in Figure 5-60 opens. It has a list of all the installed applications in your environment.
Figure 5-60 List all the WebSphere installed applications
3. Click the application on which you want to set the properties. In this example, click D0ZG_WASTestClientInfo. The window that is shown in Figure 5-61 opens and displays information about the application and all the artifacts that it uses.
Figure 5-61 Information of the D0ZG_WASTestClientInfo application
4. Click Resource references. The window that is shown in Figure 5-62 opens. The window displays all the different resource references that are used by the applications. In this example, use only a data source reference.
Figure 5-62 Resource reference for the chosen application
5. The example application uses jdbc/Josef, as shown in Figure 5-62. Select the module by selecting the Select check box, as shown in Figure 5-63.
Figure 5-63 Selecting the module that is used by the application
6. Click Extended Properties. The window that is shown in Figure 5-64 on page 255 opens.
Figure 5-64 Extended properties panel
Enter the following information:
 – Enter clientApplicationInformation for the Name
 – Any string can be used as value. We used dwsClientinformation.
7. To add more properties, click New. A new field displays. In this example, enter clientWorkStation for Name and dwsClientWorkStation for the value, as shown in Figure 5-65. You can add the other properties, such as clientUser and clientAccountingInformation, as well.
Figure 5-65 Entering the application properties
8. Click Apply and then OK. Save the changes. The application is configured and it is easy to identify the application in DB2 for z/OS.
5.5.3 Setting DB2 client information in a WebSphere Java application
If the DB2 client information settings that you must use can be determined only at run time, you might want to consider setting the DB2 client information in your Java application. You can set the DB2 client information from your WebSphere Java application by using the following options:
•Using the JDBC 4.0 setClientInfo Java API
•Using the Java API that is provided by IBM Data Server Driver JDBC and SQLJ
•Using the Java API that is provided by the WebSphere WSConnection class
•Calling the SYSPROC.WLM_SET_CLIENT_INFO stored procedure
For proof of technology (POT), we used Rational Application Developer for WebSphere Software to create the ClientInfo Dynamic Web project, which has the following servlets for setting DB2 client information:
•ClientInfoJDBC30API to use the Java interfaces that are provided by the
DB2Connection class
•ClientInfoJDBC40API to use the java.sql.Connection.setClientInfo interface
•ClientInfoWSAPI to use the Java interfaces that are provided by the WebSphere WSConnection class
•ClientInfoWLM to use the SYSPROC.WLM_SET_CLIENT_INFO external
stored procedure
If any applications set the DB2 client information fields, the values are not reset when the connection is returned to the connection pool. Applications must set these values at the beginning of the transaction to correctly collect and report data based on these fields.
The ClientInfo project servlets are illustrated in Figure 5-66.
Figure 5-66 Rational Application Developer ClientInfo project
The Rational Application Developer ClientInfo project can be downloaded from the web. For more information, see Appendix H, “ClientInfo dynamic web project” on page 573.
The servlets that are illustrated in Figure 5-66 use the same program structure. Each servlet provides a setClientInformationFromJava subroutine to implement the particular code for setting DB2 client information by using the setClientInfo API, the Java interfaces provided by the DB2Connection class, or the WLM_SET_CLIENT_INFO stored procedure.
The servlet structure is illustrated in Example 5-5.
Example 5-5 General servlet structure set DB2 client information sample
package setClientInfoJDBC40API;
 
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;
 
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
 
/**
* Servlet implementation class ClientInfoJDBCAPI
*/
@WebServlet("/JDBC40API")
public class ClientInfoJDBC40API extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public ClientInfoJDBC40API() {
super();
}
 
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
PrintWriter pw = response.getWriter();
response.setContentType("text/html");
pw.println("Hello from ClientInfoJDBC40API Servlet <br/><br/> " );
 
InitialContext ic = null;
DataSource ds = null;
try {
ic = new InitialContext();
ds = (DataSource) ic.lookup("jdbc/Josef"); 1
pw.println("Successfully looked up jdbc/Josef JNDI entry<br/><br/>");
} catch (NamingException e) {
e.printStackTrace();
}
Connection conn = null;
try {
conn = ds.getConnection(); 2
pw.println("Successfully got connection <br/><br/>");
setClientInformationFromJava(conn,pw); 3
pw.println("Running '"+returnSQL()+"' to retrieve current client info settings<br/><br/>");
PreparedStatement statement = conn.prepareStatement(returnSQL()); 4
ResultSet rs = statement.executeQuery();
while (rs.next()) { 5
String clientaccounting = rs.getString(1);
String clientapplication = rs.getString(2);
String clientuserid = rs.getString(3);
String clientworkstation = rs.getString(4);
pw.println("CLIENT_ACCTNG=" + clientaccounting+"<br/>");
pw.println("CLIENT_APPLNAME=" + clientapplication+"<br/>");
pw.println("CLIENT_USERID=" + clientuserid+"<br/>");
pw.println("CLIENT_WRKSTNNAME=" + clientworkstation+"<br/>");
pw.println("<br/>");
}
pw.println("Running '"+returnSQLFunc()+"'<br/><br/>");
PreparedStatement statementfunc = conn.prepareStatement(returnSQLFunc()); 6
ResultSet rsfunc = statementfunc.executeQuery();
while (rsfunc.next()) {
int rowno = rsfunc.getInt(1);
String racfuser = rsfunc.getString(2);
String racfgroup = rsfunc.getString(3);
if (rowno == 1)
pw.println("RACF user "+racfuser+" connected to the following groups:<br/>");
pw.println(rowno+" "+racfgroup+"<br/>");
}
conn.close(); 7
}
catch (SQLException e) {e.printStackTrace();} catch (Exception e) {
e.printStackTrace();
}
}
 
/**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
}
private String returnSQL() { 8
String sql = "SELECT CURRENT CLIENT_ACCTNG, " +
" CURRENT CLIENT_APPLNAME ," +
" CURRENT CLIENT_USERID, " +
" CURRENT CLIENT_WRKSTNNAME " +
"FROM SYSIBM.SYSDUMMY1";
return sql;
}
private String returnSQLFunc() { 9
String sqlfunc =
" WITH " +
" Q1 (RES ) AS " +
" (SELECT f.GRACFGRP() FROM SYSIBM.SYSDUMMY1) , " +
" Q2 AS " +
" (SELECT T.* FROM Q1, " +
" XMLTABLE " +
" ('$D/GROUPS/GROUP' " +
" PASSING XMLPARSE (DOCUMENT RES) AS D " +
" COLUMNS " +
" RACFUser VARCHAR(08) PATH '../USER/text()'," +
" RACFGroup VARCHAR(08) PATH './text()' " +
" ) AS T ) " +
" SELECT ROWNUMBER() OVER () AS ROWNO, Q2.* FROM Q2 " ;
return sqlfunc;
}
public void setClientInformationFromJava(Connection conn, PrintWriter pw) throws Exception 10
{ ....... Java code specific to the method for setting the DB2 client
information goes here ....
}
 
}
Here are the processing steps:
1. Perform the JNDI lookup of jdbc/Josef.
2. Obtain the database connection.
3. Start the setClientInformationFromJava method to set the DB2 client information. The method contains Java code that is specific to the option that is chosen for setting the client information (JDBC 3.0, JDBC 4.0, WebSphere WSConnection class, or SYSPROC.WLM_SET_CLIENT_INFO procedure)
4. Prepare the SQL statement for reading the DB2 client information-related special registers CLIENT_ACCTNG, CURRENT CLIENT_APPLNAME, CURRENT CLIENT_USERID, and CURRENT CLIENT_WRKSTNNAME
5. Fetch and display the result set. This confirms whether setting the DB2 client information worked as expected.
6. Prepare the SQL statement for starting the GRACFGRP UDF. Before we did our testing, we stopped the UDF. As a result, the incoming UDF request was queued by DB2, giving us plenty of time to display the related DB2 thread attributes to confirm the DB2 client information settings.
7. Close the connection. This returns the connection to the application server for reuse.
8. returnSQL returns the first SQL statement to be dynamically prepared.
9. returnSQLFunc returns the second SQL statement to be dynamically prepared.
10. The setClientInformationFromJava contains the Java code that is specific to JDBC 3.0, JDBC 4.0, and WebSphere WSConnection class, or for invoking the SYSPROC.WLM_SET_CLIENT_INFO external stored procedure.
JDBC 4.0 setClientInfo Java API
If the WebSphere Application Server JDBC provider is configured to use the db2jcc4.jar file, you should use the java.sql.Connection.setClientInfo Java API to set the DB2 client information. The Java APIs that are described in “IBM Data Server Driver for JDBC and SQLJ Java API” on page 261 are deprecated in a JDBC 4.0 environment and should not be used. The Java code of the setClientInformationFromJava function that we used in the ClientInfoJDBC40API Java class is illustrated in Example 5-6.
Example 5-6 Using JDBC 4.0 setClientInfo Java API
public void setClientInformationFromJava(Connection conn, PrintWriter pw) throws Exception
{
conn.setClientInfo("ClientUser","JDBC40API_clientuser"); 1
conn.setClientInfo("ClientHostname","JDBC40API_clientworkstation");
conn.setClientInfo("ApplicationName","JDBC40API_clientapplication");
conn.setClientInfo("ClientAccountingInformation","JDBC40API_clientaccounting");
pw.println("successfully invoked setClientInfo JDBC 4.0 API for setting DB2 Client Info to the following values <br/><br/>" ); 2
pw.println(" ClientUser=JDBC40API_clientuser<br/>" );
pw.println(" ClientHostname=JDBC40API_clientworkstation<br/>" );
pw.println(" ApplicationName=JDB40CAPI_clientapplication<br/>" );
pw.println(" ClientAccountingInformation=JDBC40API_clientaccounting<br/><br/>" );
}
The setClientInformationFromJava method that is shown in Example 5-6 on page 259 performs the following major steps:
1. Starts the JDBC 4.0 setClientUser API for setting the DB2 client information.
2. Returns confirmation messages to the browser application.
The ClientInfoJDBC40API servlet returned the processing result that is shown in Figure 5-67.
Figure 5-67 Servlet ClientInfoJDBC40API result
During servlet execution in our example, we used the display thread output that is shown in Figure 5-68 on page 261 to confirm the DB2 client information settings.
DSNV401I -D0Z1 DISPLAY THREAD REPORT FOLLOWS -
DSNV402I -D0Z1 ACTIVE THREADS -
NAME ST A REQ ID AUTHID PLAN ASID TOKEN
SERVER SW * 12 db2jcc_appli DB2R3 DISTSERV 0083 45
V437-WORKSTATION=JDBC40API_clientwo, USERID=JDBC40API_client,
APPLICATION NAME=JDBC40API_clientapplication
V429 CALLING FUNCTION=F.GRACFGRP,
Figure 5-68 Servlet ClientInfoJDBC40API display thread output
IBM Data Server Driver for JDBC and SQLJ Java API
The IBM Data Server Driver for JDBC and SQLJ combines type 2 and type 4 JDBC implementations. The driver is packaged in the following way:
•IBM Data Server Driver for JDBC and SQLJ Version 3.5x, JDBC 3.0 compliant. The db2jcc.jar and sqlj.zip files are available for JDBC 3.0 and earlier support.
•IBM Data Server Driver for JDBC and SQLJ Version 4.x, compliant with JDBC 4.0 or later. The db2jcc4.jar and sqlj4.zip files are available for JDBC 4.0 or later, and JDBC 3.0 or earlier support.
You control the level of JDBC support that you want by specifying the appropriate JAR files in the JDBC provider, as shown in Figure 5-25 on page 227. Both JAR files contain the DB2Connection class to support the following Java APIs for setting DB2 client information:
•setDB2ClientUser(String paramString)
•setDB2ClientWorkstation(String paramString)
•setDB2ClientApplicationInformation(String paramString)
•setDB2ClientAccountingInformation(String paramString)
Because these Java APIs are deprecated in JDBC 4.0, you might want to use the setClientInfo Java API in case your JDBC provider is configured to use the db2jcc4.jar file. For more information about how to use the setClientInfo API, see “JDBC 4.0 setClientInfo Java API” on page 259.
In our example, we use a pass-through mechanism that is provided by the WebSphere WSCallHelper class to invoke the APIs of the DB2Connection class that we need for setting the DB2 client information.
The Java code of the setClientInformationFromJava function that we used in the ClientInfoJDBC30API Java class is illustrated in Example 5-7.
Example 5-7 Using IBM Data Server Driver for JDBC and SQLJ set client information Java APIs
public void setClientInformationFromJava(Connection conn, PrintWriter pw) throws Exception
{
setWorkStationName(conn, "JDB30CAPI_clientworkstation")1;
setApplicationName(conn,"JDBC30API_clientapplication");
setAccounting(conn,"JDBC30API_clientaccounting");
setEndUser(conn,"JDBC30API_clientuser");
pw.println("successfully invoked JDBC 3.0 API for setting DB2 Client Info to the following values <br/><br/>"); 5
pw.println(" ClientUser=JDBC30API_clientuser<br/>");
pw.println(" ClientHostname=JDB30CAPI_clientworkstation<br/>");
pw.println(" ApplicationName=JDBC30API_clientapplication<br/>");
pw.println(" ClientAccountingInformation=JDBC30API_clientaccounting<br/><br/>");
}
public void setWorkStationName(Connection con, String work)
throws SQLException, Exception {
WSCallHelper
.jdbcCall(null, con, "setDB2ClientWorkstation",
new Object[] { new String(work) },
new Class[] { String.class });
}
 
public void setApplicationName(Connection con, String appl) 2
throws SQLException, Exception {
WSCallHelper
.jdbcCall(null, con, "setDB2ClientApplicationInformation",
new Object[] { new String(appl) },
new Class[] { String.class });
}
 
public void setAccounting(Connection con, String accounting) 3
throws SQLException, Exception {
WSCallHelper.jdbcCall(null, con, "setDB2ClientAccountingInformation",
new Object[] { new String(accounting) },
new Class[] { String.class });
}
 
public void setEndUser(Connection con, String endUser) throws SQLException, 4
Exception {
WSCallHelper.jdbcCall(null, con, "setDB2ClientUser",
new Object[] { new String(endUser) },
new Class[] { String.class });
}
 
The setClientInformationFromJava method that is shown in Example 5-7 on page 261 performs the following major steps:
1. Invokes internal methods for further processing.
2. Uses the WSCallHelper method to invoke the DB2Connection.setDB2ClientApplicationInformation interface.
3. Uses the WSCallHelper method to invoke the DB2Connection.setDB2ClientAccountingInformation interface.
4. Uses the WSCallHelper method to invoke the
DB2Connection.setDB2ClientUser interface.
5. Returns confirmation messages to the browser application.
The ClientInfoJDBC30API servlet returned the processing result that is shown in Figure 5-69 on page 263.
Figure 5-69 Servlet ClientInfoJDBC30API result
During servlet execution, we used the display thread output that is shown in Figure 5-70 to confirm the DB2 client information settings.
DSNV401I -D0Z1 DISPLAY THREAD REPORT FOLLOWS -
DSNV402I -D0Z1 ACTIVE THREADS -
NAME ST A REQ ID AUTHID PLAN ASID TOKEN
SERVER SW * 40 db2jcc_appli WASSRV DISTSERV 0083 39
V485-TRUSTED CONTEXT=CTXWASTESTT4,
SYSTEM AUTHID=WASSRV,
ROLE=WASTESTDEFAULTROLE
V437-WORKSTATION=JDB30CAPI_clientwo, USERID=JDBC30API_client,
APPLICATION NAME=JDBC30API_clientapplication
V429 CALLING FUNCTION=F.GRACFGRP,
PROC= , ASID=0000, WLM_ENV=DSNWLMDB0Z_GENERAL
Figure 5-70 Servlet ClientInfoJDBC30API display thread output
Using the Java API that is provided by the WebSphere WSConnection class
Instead of using the DB2Connection object (see “IBM Data Server Driver for JDBC and SQLJ Java API” on page 261), you can use the following Java interface:
com.ibm.websphere.rsadapter.WSConnection.setClientInformation(Properties arg0)
The Java code of the setClientInformationFromJava function that we used in the ClientInfoWSAPI Java class is illustrated in Example 5-8.
Example 5-8 Using the WSConnection class
public void setClientInformationFromJava(WSConnection conn, PrintWriter pw) throws Exception
{
Properties props = new Properties(); 1
props.setProperty(WSConnection.CLIENT_ID, "WSAPI_clientuser"); 2
props.setProperty(WSConnection.CLIENT_LOCATION, "WSAPI_clientworkstation");3
props.setProperty(WSConnection.CLIENT_APPLICATION_NAME, "WSAPI_clientapplication"); 4
conn.setClientInformation(props); 5
pw.println("successfully invoked WSConnection APIs for setting DB2 Client Info to the following values <br/><br/>" );
pw.println(" WSConnection.CLIENT_ID=WSAPI_clientuser<br/>" );
pw.println(" WSConnection.CLIENT_LOCATION=WSAPI_clientworkstation<br/>" );
pw.println(" WSConnection.CLIENT_APPLICATION_NAME=WSAPI_clientapplication<br/></br>" );
}
In Example 5-8, the code performs the following actions:
1. Instantiates the properties object.
2. Sets the WSConnection.CLIENT_ID property.
3. Sets the WSConnection.CLIENT_LOCATION property.
4. Sets the WSConnection.CLIENT_APPLICATION_NAME property.
5. Invoke the WSConnection setClientInformation interface.
The ClientInfoWSAPI servlet returned the processing result shown Figure 5-71 on page 265.
Figure 5-71 Servlet ClientInfoWSAPI result
During servlet execution, we used the display thread output that is shown in Figure 5-72 to confirm the DB2 client information settings.
DSNV401I -D0Z1 DISPLAY THREAD REPORT FOLLOWS -
DSNV402I -D0Z1 ACTIVE THREADS -
NAME ST A REQ ID AUTHID PLAN ASID TOKEN
SERVER SW * 76 db2jcc_appli WASSRV DISTSERV 0083 39
V485-TRUSTED CONTEXT=CTXWASTESTT4,
SYSTEM AUTHID=WASSRV,
ROLE=WASTESTDEFAULTROLE
V437-WORKSTATION=WSAPI_clientworkst, USERID=WSAPI_clientuser,
APPLICATION NAME=WSAPI_clientapplication
V429 CALLING FUNCTION=F.GRACFGRP,
Figure 5-72 Servlet ClientInfoWSAPI display thread output
Calling the SYSPROC.WLM_SET_CLIENT_INFO stored procedure
The Java API that is available for setting the DB2 client information depends on the IBM Data Server Driver for JDBC and SQLJ JAR file that your JDBC Provider is configured for:
•If your JDBC provider uses the db2jcc.jar file, you can use the Java API that is described in “IBM Data Server Driver for JDBC and SQLJ Java API” on page 261.
•If your JDBC provider uses the db2jcc4.jar file, you can use the Java API that is described in “JDBC 4.0 setClientInfo Java API” on page 259 or “IBM Data Server Driver for JDBC and SQLJ Java API” on page 261. Considering that the Java APIs that are described in “IBM Data Server Driver for JDBC and SQLJ Java API” on page 261 are deprecated in JDBC 4.0, your development strategy might force you to change existing applications to use the JDBC 4.0 provided setClientInfo API.
Choosing the correct option for setting the DB2 client information can be difficult because the Java API you use depends on the JDBC driver level that your application is using. You can ignore this dependency by using the WLM_SET_CLIENT_INFO external stored procedure for setting the DB2 client information.
The WLM_SET_CLIENT_INFO external stored procedure load module DSNADMSI uses the RRS DSNRLI SET_CLIENT_ID function to set the client information that is associated with the current connection at the DB2 server. Using this method does not depend on the JDBC driver level, the JDK level, or the type or version of the application server that you are using.
The Java code of the setClientInformationFromJava function that we used in the ClientInfoWLM Java class is illustrated in Example 5-9.
Example 5-9 Using the SYSPROC.WLM_SET_CLIENT_INFO external stored procedure
public void setClientInformationFromJava(Connection conn, PrintWriter pw) throws Exception
{
CallableStatement clientApplCall = null; 1
clientApplCall = conn.prepareCall("CALL SYSPROC.WLM_SET_CLIENT_INFO(?,?,?,?)"); 2
clientApplCall.setString(1, "WLM_clientuser"); 3
clientApplCall.setString(2, "WLM_clientworkstation");
clientApplCall.setString(3, "WLM_clientapplication");
clientApplCall.setString(4, "WLM_clientaccounting");
clientApplCall.executeUpdate(); 4
pw.println("successfully called SYSPROC.WLM_SET_CLIENT_INFO to set DB2 Client Info to the following values:<br/><br/>" ); 5
pw.println(" ClientUser=WLM_clientuser<br/>" );
pw.println(" ClientHostname=WLM_clientworkstation<br/>" );
pw.println(" ApplicationName=WLM_clientapplication<br/>" );
pw.println(" ClientAccountingInformation=WLM_clientaccounting<br/><br/>" );
}
Here are the processing steps for calling the SYSPROC.WLM_SET_CLIENT_INFO:
1. Instantiate the clientApplCall CallableStatement object.
2. Dynamically prepare the CALL statement.
3. Use the java.sql.PreparedStatement.setString method to provide the necessary hostvariable values.
4. Run the SQL CALL statement.
5. Display the variable settings that are used in the CALL statement.
The ClientInfoWLM servlet returned the processing result that is shown in Figure 5-73.
Figure 5-73 Servlet ClientInfoWLM result
During servlet execution, we used the display thread output that is shown in Figure 5-74 to confirm the DB2 client information settings.
DSNV401I -D0Z1 DISPLAY THREAD REPORT FOLLOWS -
DSNV402I -D0Z1 ACTIVE THREADS -
NAME ST A REQ ID AUTHID PLAN ASID TOKEN
SERVER SW * 58 db2jcc_appli WASSRV DISTSERV 0083 39
V485-TRUSTED CONTEXT=CTXWASTESTT4,
SYSTEM AUTHID=WASSRV,
ROLE=WASTESTDEFAULTROLE
V437-WORKSTATION=WLM_clientworkstat, USERID=WLM_clientuser,
APPLICATION NAME=WLM_clientapplication
V429 CALLING FUNCTION=F.GRACFGRP,
PROC= , ASID=0000, WLM_ENV=DSNWLMDB0Z_GENERAL
Figure 5-74 Servlet ClientInfoWLM display thread output
5.6 Configuring the prepared statement cache in WebSphere Application Server
The WebSphere PreparedStatements cache does not store any DB2 specific information. The cache is used solely by WebSphere to reduce processor consumption when you create a Java object.
Complete the following steps:
1. In the navigation window of the administrative console of the WebSphere Application Server, expand Resources and click Data sources, as shown in Figure 5-75.
Figure 5-75 Administrative console of the WebSphere Application Server
The window that is shown in Figure 5-76 on page 269 opens. This window shows a list of the existing JDBC data sources that are defined in your environment.
Figure 5-76 List of existing JDBC data sources
2. Click TradeDatasourceXA and the window that is shown in Figure 5-77 opens.
Figure 5-77 JDBC TradeDatasourceXA resource
3. Click WebSphere Application Server data source properties. The window that is shown in Figure 5-78 opens.
Figure 5-78 Data source properties window
The Statement cache size specifies the number of statements that can be cached per connection. The default size is 10. We used the default in our environment. Applications should configure the value based on how many SQL statements are used.
5.7 Configuring the J2C authentication alias
Java Authentication and Authorization Services (JAAS) is a Java API that is used to establish an authenticated user ID. This API can be invoked in several instances, in particular when connecting to DB2. This connection can be established on behalf of two environments:
•Container (using the user ID of the thread that is running)
•Component/application (The user ID is explicitly passed on the getConnection call.)
Complete the following steps:
1. In the navigation window of the administration console, expand Security, as shown in Figure 5-79 on page 271.
Figure 5-79 WebSphere navigation window
2. Click Global security and the window that is shown in Figure 5-80 opens.
Figure 5-80 Global security
3. Expand Java Authentication and Authorization Service and click the J2C authentication data. The window that is shown in Figure 5-81 opens, which lists the existing J2C authentication aliases that are defined.
Figure 5-81 J2C authentication data
4. Click New and the window that is shown in Figure 5-82 opens.
Figure 5-82 J2C authentication input definition
Enter the following information:
 – A string for the alias. We used TradeDataSourceAuthData.
 – The user ID to be used by the data source to connect to DB2 for z/OS. We
used rajesh.
 – The password.
5. Click Apply, then OK, and then save the changes. This J2C authentication alias can be used with either a JDBC type 2 or type 4 data source (XA or non-XA).
5.8 Configuring connection pool sizes on data sources in WebSphere Application Server
To configure connection pool sizes on data sources in WebSphere Application Server, complete the following steps:
1. In the navigation window of the administrative console of the WebSphere Application Server, expand Resources and click Data sources, as shown in Figure 5-83.
Figure 5-83 WebSphere navigation window
The window that is shown in Figure 5-84 opens. This window shows a list of existing JDBC data sources that are defined in your environment.
Figure 5-84 Data source and JDBC provider association
2. Click TradeDatasourceXA and the window that is shown in Figure 5-85 opens.
Figure 5-85 Data source and provider
3. Click Connection pool properties. The window that is shown in
Figure 5-86 on page 275 opens.
Figure 5-86 Connection pool properties
Connection pooling is a function of WebSphere Application Server. It is not a function of the IBM Data Server Driver for JDBC and SQLJ. The driver does not implement connection pooling.
Here is a brief description of the properties that are shown in Figure 5-86:
Connection Timeout How long to attempt connection creation before a timeout occurs
Max Connections The maximum connections from this JVM instance
Min Connections The minimum number of connections in a pool
Reap Time How often a cleanup of pool is scheduled, in seconds
Unused Timeout How long to let a connection sit in the pool unused
Aged Timeout How long to let a connection live before recycling
Purge Policy After StaleConnection, does the entire pool get purged or only individual connection
In the window that is shown in Figure 5-86, consider using the following
preferred practices:
 – Set the WebSphere Application Server connection unused timeout to a smaller value than the DB2 idle thread timeout to avoid stale connection conditions.
 – Consider setting Min Connections to zero.
 – In DB2 10, you can reduce processor usage by selectively binding the client package with the RELEASE(DEALLOCATE) option.
 – Consider setting the WebSphere Application Server aged timeout to less than five minutes, such as 120 seconds, to reduce long-lived threads.
5.9 Enabling trusted context for applications that are deployed in WebSphere Application Server
A trusted context is an object that the database administrator defines that contains a system authorization ID and a set of trust attributes. The relationship between a database connection and a trusted context is established when the connection to the database server is created, and that relationship remains for the life of the database connection. This feature allows WebSphere Application Server to use the trusted DB2 connection under a different user without reauthenticating the new user.
Trusted context can be enabled at an application level in WebSphere Application Server.
For this example, we use a simple application, D0ZG_WASTestClientInfo, which uses a resource reference. The application uses a JDBC type 4 XA data source.
Complete the following steps:
1. In the navigation window of the administration console of the WebSphere Application Server, expand Applications and Application Types, as shown in Figure 5-87.
Figure 5-87 Administration console of WebSphere Application Server
2. Click WebSphere enterprise applications. The window that is shown in Figure 5-88 opens and shows all the installed applications in your environment.
Figure 5-88 List of installed applications
3. Click the application that on which you want to set the properties. In this example, click D0ZG_WASTestClientInfo. The window that is shown in Figure 5-89 opens. This window displays information about the application and all the artifacts it uses.
Figure 5-89 D0ZG_WASTestClientInfo.properties definition
4. Click Resource references. The window that is shown in Figure 5-90 on page 279 opens. The window lists all the different resource references that are used by the applications. In our example, we use only a data source reference.
Figure 5-90 Resource reference
5. The example, application uses jdbc/Josef, as shown in Figure 5-90. Select the module by selecting the Select check box, as shown in Figure 5-91.
Figure 5-91 Selecting the jdbc/Josef module
6. Click Modify Resource Authentication Method. The window that is shown in
Figure 5-92 opens.
Figure 5-92 Resource Authentication definition
7. Select the Use trusted connections radio button. Then, select a JAAS alias in the drop-down menu, as shown in Figure 5-93. The user ID in the JAAS alias should have only connect privileges to DB2 for z/OS and should be defined as part of the trusted context definition in DB2. In our example, we created a JAAS alias named trustedcontext.
Figure 5-93 JAAS alias trusted connection
8. Click Apply and the window that is shown in Figure 5-94 opens.
Figure 5-94 Trusted context enabled
9. Click OK and save the changes.
5.10 Configuring the JCC properties file in WebSphere Application Server
The IBM Data Server Driver for JDBC and SQLJ has many configuration properties. These properties apply to different application requirements. These properties can mostly be configured as WebSphere Application Server data source custom properties. There are a few properties that are considered global properties that can be specified only in a properties file, which is at the JVM level. This means that these properties apply to all the data sources in the WebSphere Application Server. This section describes how to define this property file for WebSphere Application Server.
Complete the following steps:
1. In the navigation window of the administration console of WebSphere Application Server, expand Server Types, as shown in Figure 5-95.
Figure 5-95 Administration console of WebSphere Application Server
2. Click WebSphere Application servers and the window Figure 5-96 opens and displays the servers that are defined in the environment. In the example environment, we had three servers. We focus on the MZSR014 server.
Figure 5-96 List of available servers
3. Click MZSR014 and the window that is shown in Figure 5-97 opens.
Figure 5-97 Properties of the MZSR014 server
4. Expand Java and Process Management. Click Process definition. The window that is shown in Figure 5-98 opens. This window is specific to WebSphere Application Server on z/OS.
Figure 5-98 Server process definition
5. Click Servant and the window that is shown in Figure 5-99 opens.
Figure 5-99 Configuring the process definition of the application server
6. Click Java Virtual Machine and the window that is shown in Figure 5-100 opens.
Figure 5-100 Java Virtual Machine for the application server
7. Click Custom properties and the window that is shown in Figure 5-101 opens.
Figure 5-101 JVM custom properties
8. Click New and the window that is shown in Figure 5-102 opens. In the name field, enter db2.jcc.propertiesFile. In the value field, enter the location of the properties file. In our example, the properties file is named jcc.properties. It is stored in /u/rajesh.
Figure 5-102 New custom property for JVM
Click Apply, then OK, and then save the changes. The window that is shown in
Figure 5-103 opens.
Figure 5-103 Application server defined
9. Now enter any required properties in the jcc.properties file and restart the server. You can validate that the jcc.properties file was acquired by looking at the following server log:
Trace: 2012/10/04 00:37:31.677 02 t=7E3AE8 c=UNK key=P8 tag= (13007004)
SourceId: com.ibm.ws390.orb.CommonBridge.printProperties
ExtendedMessage: BBOJ0077I: db2.jcc.propertiesFile = /u/rajesh/jcc.properties
When you see the message, you know that the server acquired the jcc.properties file.
5.11 Configuring data source properties (webSphereDefaultlsolationLevel, currentPackagePath, pkList, and keepDynamic)
In this section, we show how to set the following properties at a data source level in WebSphere Application Server:
•websphereDefaultIsolationLevel
•currentPackagePath (for JDBC type 4 connection)
•pkList (for JDBC type 2 connections)
•keepDynamic
5.11.1 websphereDefaultIsolationLevel
Complete the following steps:
1. In the navigation window of the administrative console of the WebSphere Application Server, expand Resources and click Data Sources, as shown in Figure 5-104.
Figure 5-104 Administrative console of the WebSphere Application Server
The window that is shown in Figure 5-105 opens. This window shows a list of existing JDBC data sources that are defined in your environment.
Figure 5-105 List of existing JDBC data sources
2. Select the data source on which you want to set the property. In this example, we select TradeDatasourceXA and the window that is shown in Figure 5-106 opens.
Figure 5-106 Data source TradeDatasourceXA
3. Click Custom properties. The window that is shown in Figure 5-107 on page 291 opens and lists all the custom properties that are available.
Figure 5-107 List of the custom properties
The webSphereDefaultIsolationLevel custom property is available by default, but the default value is not set. WebSphere Application Server uses JDBC TRANSACTION_REPEATABLE_READ, which maps to read stability (RS) in DB2 by default. Applications should choose the appropriate isolation level. In our example, we chose to use TRANSACTION_READ_COMMITTED, which maps to cursor stability (CS) in DB2, as shown in Figure 5-108.
Figure 5-108 Isolation level definition
4. Click webSphereDefaulIsolationLevel and the window shown Figure 5-109 opens.
Figure 5-109 Custom property for default isolation level
5. Enter 2 for the value, which sets cursor stability in DB2. Click Apply, then OK, and
then save the changes.
5.11.2 currentPackagePath
The currentPackagePath custom property is also available by default in WebSphere Application Server. It does not have any value, as shown in Figure 5-110. This property should be used under the following conditions:
•JDBC type 4 connectivity is used to connect to DB2 for z/OS.
•The application has multiple packages that must be accessed and those packages are bound to different collections.
Figure 5-110 No default for currentPackagePath
Click currentPackagePath and the window that is shown in Figure 5-111 opens. Enter a comma-separated collection of names. In this example, the application used packages that were bound to collections MYCOLL1 and MYCOLL2.
Figure 5-111 currentPackagePath
Click Apply, then OK, and then save the changes.
5.11.3 pkList
The pkList custom property is not available by default in WebSphere Application Server. This property should be used under the following conditions:
•JDBC type 2 connectivity is used to DB2 for z/OS.
•The application has multiple packages that must be accessed and those packages are bound to different collections.
Click New and the window that is shown in Figure 5-112 opens. Enter pkList for the name. Enter a comma-separated collection of names for the value. In this example, the application used packages that were bound to collections MYCOLL1 and MYCOLL2.
Figure 5-112 pkList
Click Apply, then OK, and then save the changes.
5.11.4 keepDynamic
The keepDynamic custom property is not available by default in WebSphere Application Server. The default behavior in WebSphere Application Server is to not use this property. This property should be used when you want to use a local cache in DB2, as shown in Figure 5-113.
Figure 5-113 Property keepDynamic
Click keepDynamic and the window that is shown in Figure 5-114 opens. Enter a value of 1 to use the keepDynamic feature in DB2.
Figure 5-114 Custom property keepDynamic
Click Apply, then OK, and then save the changes.
..................Content has been hidden....................

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