Connecting to Db2 by using JDBC
In this chapter, we describe how to use Java database connectivity (JDBC) within a Java EE application in CICS Liberty to access data in a Db2 database. Our scenario uses Db2 v12 for z/OS with CICS TS V5.4.
We also show how to configure a CICS Liberty JVM server to use JDBC type 2 and type 4 connectivity. Finally, we describe how to integrate Db2 updates with other CICS transactional updates.
This chapter includes the following topics:
4.1 JDBC overview
JDBC is the Java specification of a standard application programming interface (API) that allows Java programs to access database management systems. The JDBC API consists of a set of interfaces and classes that are written in the Java programming language. The JDBC API is divided into two packages: java.sql and javax.sql, which are included in the Java SE and Java EE platforms.
By using these standard interfaces and classes, programmers can write applications that connect to databases, send queries that are written in structured query language (SQL), and process the results.
Three recent versions of the JDBC 4 specification JDBC 4.0, JDBC 4.1, and JDBC 4.2 are available. JDBC 4.0 is supported in CICS Liberty by using the jdbc-4.0 feature; JDBC 4.1 is supported by using the jdbc-4.1 feature.
4.1.1 JDBC drivers
Because JDBC is a standard specification, one Java program that uses the JDBC API can connect to any database management system (DBMS) if a driver exists for that particular DBMS.
The following commonly used types of JDBC drivers are available for use in CICS Liberty JVM servers:
•Type 2: These drivers use the client libraries of a locally installed database and convert the JDBC method calls into the native calls of the local database driver.
•Type 4: These drivers are pure Java drivers that connect directly to a remote database server over the network.
Db2 for z/OS provides JDBC support through the IBM Data Server Driver for JDBC and SQLJ, which is known as the JCC driver. This driver is a unified driver that combines the type 2 and type 4 connectivity implementations in one driver. The following versions of the JCC driver are available with Db2 for z/OS:
•db2jcc.jar for JDBC 3.0 and earlier support
•db2jcc4.jar for JDBC 3.x and 4.x support
In this chapter, we used the db2jcc4.jar driver.
JDBC connectivity
Although the type 2 and type 4 modes of JDBC connectivity are different, both are supported in the CICS Liberty JVM server environment for connecting to IBM Db2 for z/OS. Which connectivity type is best suited to a specific CICS Liberty application depends on the factors that are listed in Table 4-1.
Table 4-1 Comparison of type2 and type4 JDBC drivers
Qualities of service
JDBC type 2 connectivity
JDBC type 4 connectivity
Connectivity
Must connect to local Db2 database by using the CICS DB2 attachment.
Can connect to any database by using a TCP/IP network.
Performance
Shorter path length offers better response times.1
zIIP offload in the Java driver and in the Db2 DDF can be used to reduce CPU costs.
Security
Security context that is inherited from the CICS transaction.
Credentials are configured in the data source definition.
Transactionality
Integrated with CICS unit-of-work and CICS sync point control.
Requires usage of Connection.commit() or Java Transaction API (JTA) to coordinate updates.
1 For more information about the performance of JDBC type 2 and type 4 connectivity in CICS Liberty, see the IBM Redpaper™ publication IBM CICS Performance Series: Comparing Type 2 and Type 4 JDBC Driver Performance with IBM CICS Transaction Server for z/OS V5.2 Liberty JVM server.
4.1.2 Data sources
Before you can run SQL statements in any SQL program, you must be connected to a database. In the Java platform, the connection set up to a database from a server is known as a data source.
A Java application can establish a connection to a data source by using the JDBC DriverManager or DataSource interfaces, which are part of the java.sql package. The use of the DriverManager interface limits portability and the DataSource is the standard model that is used for most Java EE applications. The use of a DataSource allows the application code to remain version independent of the database connection details, such as the JDBC driver connection type.
When a Java EE application in Liberty connects to a data source by using the DataSource interface, the connection details are typically defined in the Liberty server configuration file. Each application can then refer to this data source by using a logical name, which is located by using the Java Naming and Directory Interface (JNDI).
The DataSource object is obtained by using the JNDI InitialContext.doLookup() method or the resource injection. Having obtained a DataSource instance, the DataSource.getConnection() method is then used to obtain the database connection.
4.1.3 Static and dynamic SQL
JDBC can use dynamic or static SQL. Static SQL uses predefined SQL operations that do not change when the program is run. Dynamic SQL operations are not predefined and the underlying database operations can change when the program is run. Within Java, the use of static SQL requires the use of the SQLJ API, which is a set of programming extensions that allow Java programs to embed SQL statements.
SQLJ requires another compilation step to convert the .sqlj source files into Java code before they are compiled by the Java compiler. The dynamic and static models feature the following key characteristics:
•Dynamic SQL
Because it does not require any special preparation process, Dynamic SQL is easier to develop and deploy than SQLJ.
It also can be written by using standard JDBC calls or by using the Java Persistence Architecture (JPA). JPA simplifies programming at a cost of less control over database events and an increased number of smaller database interactions.
•Static SQL
This model can be more efficient because the database knows what should be done. It also can be more reliable because variable type errors are detected at compilation time.
SQLJ code is compact and partially generated by tools, such as IBM Data Studio. SQLJ also supports more granular security options. The use of prepared statements can help avoid SQL injection attacks.
In this chapter, we use dynamic SQL and the JDBC API, which provides the simplest route to get started with Java access to Db2 data.
4.2 Installing the JDBC Employee application
In this IBM Redbooks publication, we provide a sample JDBC application that is named the JDBC Employee application. This application queries employee information from the sample Db2 EMP table and supports creating, reading, updating, and deleting actions to be performed on the entries in the database.
The source for our application is available for download at the CICSDev GitHub repository. The instructions for deploying the Employee application are provided in the .readme file in the GitHub repository.
The Employee application uses a data source configuration to define the connection to the underlying database. When Db2 for z/OS is used, this connection can be configured to use JDBC type 2 or type 4 connectivity. Sample instructions are provided for configuring and installing the application into CICS Liberty.
Whichever type of JDBC driver is used, you must configure the following resources:
•Liberty features
•Liberty <dataSource> element
•CICS resource definitions:
 – TSMODEL
 – URIMAP
 – TRANSACTION
If you use JDBC type 2 connectivity in CICS, you also must define the following resources:
•DB2CONN
•DB2ENTRY
•DB2TRAN
Instructions for configuring these resources are provided in the following sections.
4.2.1 Liberty features
In addition to the JDBC feature, the Employee application uses the JNDI to locate the DataSource and JavaServer Faces (JSF) technology to build the web pages.
We added the Java EE 7 versions of these Liberty features that are shown in Example 4-1 to the <featureManager> list element in our Liberty server.xml configuration file.
Example 4-1 Liberty features
<featureManager>
<feature>jndi-1.0</feature>
<feature>jsf-2.2</feature>
<feature>jdbc-4.1</feature>
</featureManager>
The Employee web application can be defined into Liberty by using an application element or a CICS bundle project. We used the application element that is shown in Example 4-2 to deploy the Employee application.
Example 4-2 Liberty application element
<application
location="/var/cicsts/SC8CICS2/ITSOJVM1/apps/ employee.jdbc.web.war">
</application>
4.2.2 Data source definition
In addition to the Liberty features and application definition, the data source must be defined in the Liberty server configuration file. This configuration is achieved by using a <dataSource> element and an associated <library> element in the server.xml file.
For more information about the configuration for the two different modes of JDBC connectivity (type 2 and type 4), see 4.3.2, “Configuring server.xml” on page 100 and 4.4.2, “Configuring server.xml” on page 105. Several server.xml samples are provided with the GitHub repository.
4.2.3 CICS resources
Our sample Employee application requires the following CICS resources definitions, which are required for type 2 and type 4 connectivity scenarios:
•TSMODEL
•URIMAP
•TRANSACTION
More CICS resource definitions, including DB2CONN, DB2TRAN, and DB2ENTRY definitions, are required to use type 2 connectivity. For more information, see 4.3.1, “Configuring CICS resources” on page 98.
TSMODEL
Our ITSO Employee application uses a CICS temporary storage queue (TSQ) that is named DB2LOG to log updates that are made to the Db2 database. This TSQ is defined as recoverable so that updates to it are coordinated as part of the CICS unit-of-work.
We defined a TSMODEL that is named DB2LOG, as shown in Figure 4-1. The Recovery attribute was set to Yes to define the TSQ as recoverable by CICS.
Figure 4-1 CICS TSMODEL definition
URIMAP and TRANSACTION
Next, we added a URIMAP and a TRANSACTION definition. These definitions enabled us to map the HTTP requests to our Employee application, such that they ran under a unique transaction ID. Using a URI map and transaction ID allows requests to this application to be easily secured and monitored. Also, when type 2 connectivity is used, the transactions can be mapped to the required DB2TRAN definition, which is used to specify the Db2 plan name.
The URI map definition EMPJDB2 is shown in Figure 4-2. It is used to map the URL path /employee.jdbc.web/* to our transaction ID JDB2. We allowed the Host and Port parameters to default so that these parameters were not part of the filter criteria.
Figure 4-2 URIMAP definition
Next, we created and installed the TRANSACTION definition JDB2 (see Figure 4-3). This definition is a copy of the CJSA definition, which is the default JVM server request processor transaction.
Figure 4-3 TRANSACTION definition JDB2
With these definitions, we installed them into our CICS region by using the CEDA INSTALL GROUP(DB2) command.
4.3 Using JDBC type 2 connectivity
In a CICS Liberty environment, the use of the Db2 JCC driver with type 2 connectivity converts the JDBC requests into their EXEC SQL equivalents. The converted requests flow into the CICS Db2 attachment facility in the same way as EXEC SQL requests from COBOL or other non-Java programs. The customization and tuning options for the CICS Db2 attachment facility apply equally to Java and non-Java programs.
4.3.1 Configuring CICS resources
To add support for JDBC type 2 connectivity, we changed our CICS region’s configuration. This process is described next.
CICS STEPLIB
The first step in adding Db2 support to our CICS region is to make the Db2 libraries available to your CICS region by using the MVS LNKLIST or the region’s STEPLIB. We added the SDSNLOAD and SDSNLOD libraries to the STEPLIB, as shown in Example 4-3.
Example 4-3 CICS region STEPLIB
//STEPLIB DD DSN=CICSTS54.CICS.SDFHAUTH,DISP=SHR
// DD DSN=CICSTS54.CPSM.SEYUAUTH,DISP=SHR
// DD DSN=CICSTS54.CICS.SDFJAUTH,DISP=SHR
// DD DSN=CICSTS54.SDFHLIC,DISP=SHR
// DD DSN=CEE.SCEECICS,DISP=SHR
// DD DSN=DB2AT.SDSNLOAD,DISP=SHR
CICS resource definitions
To install our Employee application by using a Db2 type 2 connection, we configured the following CICS resource definitions:
•DB2CONN
•DB2ENTRY
•DB2TRAN
These resources definitions are described in the following sections.
DB2CONN
Next, we added a CICS DB2CONN resource definition. This resource defines the connection from CICS to the Db2 subsystem (see Figure 4-4).
Figure 4-4 CICS DB2CONN: Part I
The DB2Groupid attribute was set to the group ID of D2AG, which was the DB2 group ID of our Db2 data sharing group (see Figure 4-5).
Figure 4-5 CICS DB2CONN: Part II
The AUTHType attributes and the COMAUTHType attributes were allowed to default to Userid, which signifies that authorization to the database uses the CICS task user ID.
DB2ENTRY and DB2TRAN
Next, we added a DB2ENTRY and DB2TRAN definition, as shown in Figure 4-6 and Figure 4-7 on page 100.
Figure 4-6 CICS DB2ENTRY definition
Figure 4-7 CICS DB2TRAN definition
4.3.2 Configuring server.xml
To configure the use of JDBC type 2 connectivity with CICS Liberty, the following resources must be added:
•The Liberty JSF, and JDBC features that are required by the application. For more information, see 4.2.1, “Liberty features” on page 95.
•A <library> element that refers to the JCC driver and license file.
•A <dataSource> element that defines the connection to the database by using the CICS Db2 attachment.
Our server.xml with this updated configuration is shown in Example 4-4.
Example 4-4 Liberty server.xml: Type 2 dataSource
<featureManager>
<feature>jndi-1.0</feature>
<feature>jsf-2.2</feature>
<feature>jdbc-4.1</feature>
</featureManager>
 
<library id="Db2Lib">
<fileset dir="/usr/lpp/db2c10/db2a/jdbc/classes"
includes="db2jcc4.jar db2jcc_license_cisuz.jar" />
<fileset dir="/usr/lpp/db2c10/db2a/jdbc/lib"
includes="libdb2jcct2zos4_64.so" />
</library>
 
<dataSource id="db2type2"
jndiName="jdbc/sample"
transactional="false">
<jdbcDriver libraryRef="jdbclib" />
<connectionManager agedTimeout="0" />
<properties.db2.jcc driverType="2"
currentSchema="DSN81210" />
</dataSource>
 
<application
location="/var/cicsts/SC8CICS2/ITSOJVM1/apps/ employee.jdbc.web.war">
</application>
 
Note: In releases before CICS TS V5.4, the <dataSource> element was supported for type 4 connectivity to Db2 only. Instead, type 2 connectivity required the use of the CICS provided <cicsts_datasource> and <cicsts_jdbcDriver> elements.
The new type 2 <dataSource> support in CICS TS V5.4 provides a more standard implementation for JDBC support, and is supported in CICS TS V5.3 with APAR PI77502. If applications are migrated from the use of the <cicsts_dataSource> element to the <dataSource> element, the commit processing logic must be reviewed in your JDBC application because of changes in the default behavior for commit on cleanup. For more information, see 4.5, “Transactional support with JDBC” on page 108.
Data source definition
We specified the following attributes on our <dataSource> element:
•jndiName="jdbc/sample"
This attribute is the JNDI name that is used by our Employee application to locate the DataSource object. It is specified on the @Resource annotation on the DataSource field in the DatabaseOperationsManager class.
@Resource(authenticationType=AuthenticationType.CONTAINER, name="jdbc/sample")
private DataSource ds;
The doLookup() method of the JNDI InitialConext class is used to create the DataSource within our application as shown in the following example:
ds = (DataSource)InitialContext.doLookup("jdbc/sample");
•transactional="false"
The <transactional> attribute must be set to false to ensure that the data source updates are not coordinated by the Liberty transaction manager. If this setting is left to default to true, Liberty uses RRS as the transaction coordinator when JDBC type 2 connectivity is used, which does not function correctly in the CICS environment.
Setting this value to false disables transaction coordination by Liberty and allows the updates to be coordinated as part of the CICS unit-of-work through the CICS Db2 attachment.
•jdbcDriver libraryRef="jdbclib"
The libraryRef attribute on the <jdbcDriver> sub-element must reference the <library> element that is used to configure the location of the JCC driver db2jcc4.jar and the accompanying license file db2jcc_license_cisuz.jar.
•connectionManager agedTimeout="0"
The <connectionManager> element should specify agedTimeout=0 to ensure that the Liberty data source connection pooling is disabled because the database connections are pooled instead by using the CICS Db2 attachment.
•properties.db2.jcc
Attributes that are supplied in the <properties.db2.jcc> child element are passed directly to the Db2 JCC driver. We specified the following values:
 – driverType
The driverType attribute must specify 2 to signify the use of JDBC type 2 connectivity with the Db2 JCC driver.
 – currentSchema
The currentSchema specifies the default schema name that is used to qualify unqualified database objects in dynamically prepared SQL statement. We used the value currentSchema="DSN81210" because Db2 V12 uses DSN81210 as the sample schema.
4.3.3 Binding the plan
Finally, we must bind the ITSOJCC plan with a PKLIST of NULLID.*. The ITSOJCC plan was specified as our plan name in our DB2ENTRY definition, as shown in Example 4-5.
Example 4-5 Db2 bind plan for JDBC
//D2A1JCCP JOB (999,POK),'D2A1 INSTALL',CLASS=A,
// MSGCLASS=T,NOTIFY=&SYSUID,TIME=NOLIMIT,REGION=0M
/*JOBPARM SYSAFF=SC80,L=9999
// JCLLIB ORDER=(DB2AM.PROCLIB)
//JOBLIB DD DISP=SHR,DSN=DB2AT.SDSNLOAD
// DD DISP=SHR,DSN=CEE.SCEERUN
//PH01PS02 EXEC PGM=IKJEFT01,DYNAMNBR=20,COND=(4,LT)
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(D2AG)
BIND PLAN(ITSOJCC) OWNER(SYSADM)ACTION(REPLACE)PKLIST(NULLID.*)+
RETAIN CURRENTDATA(NO) ISO(CS) ENCODING(EBCDIC) SQLRULES(DB2)
RUN PROGRAM(DSNTEP2) PLAN(DSNTEP12) +
LIB('DB2AM.RUNLIB.LOAD') PARMS('/ALIGN(MID)')
END
//SYSIN DD *
SET CURRENT SQLID = 'SYSADM';
GRANT EXECUTE, BIND ON PLAN ITSOJCC TO PUBLIC;
//*
4.3.4 Running the application
The Employee web application is accessed on our CICS Liberty server by using the following URL:
http://wtsc80:52080/employee.jdbc.web/
This URL loads the Employee database list form, as shown in Figure 4-8.
Figure 4-8 Employee database list form
From this form, you can disable and enable the use of Java Transaction API (JTA) for commit processing by selecting Toggle JTA. For more information about how to use JTA, see 4.5, “Transactional support with JDBC” on page 108.
Next, you add entries or query entries in the EMP table. If you leave the search field blank and click Search, all of the employees from the table are returned, as shown in Figure 4-9 on page 104.
Figure 4-9 Employee application search results
If the employee information is returned, this result confirms that a connection was made to the DB2 database and that the EMP table was read successfully.
If any errors are retuned by the application, further diagnostics often can be found in the JVM server stderr destination.
4.4 Using JDBC type 4 connectivity
The Liberty JVM server also supports type 4 connectivity. This Type 4 connectivity uses TCP/IP to connect to a remote Db2 subsystem through the support of the Db2 Distributed Data Facility (DDF) instead of the use of the CICS Db2 attachment and associated DB2CONN resource. The DDF is a built-in component of Db2 and provides the network connectivity to and from other servers or clients.
4.4.1 Configuring CICS resources
When JDBC type 4 connectivity is used, it is not necessary to define any specific CICS resource definitions. However, we used the same CICS TRANSACTION, URIMAP, and TSMODEL resource definitions for our JDBC type 4 connectivity configuration as for our type 2 configuration. For more information about how to configure these resources, see 4.2.3, “CICS resources” on page 95.
4.4.2 Configuring server.xml
To configure the use of JDBC type 4 connectivity with CICS Liberty, the following resources must be added:
•The Liberty JSF, JNDI, and JDBC features that are required by the application. For more information, see 4.2.1, “Liberty features” on page 95.
•A <library> element that refers to the JCC driver and license file.
•A <dataSource> element that defines the connection to the database.
Our server.xml with this updated configuration is shown in Example 4-6.
Example 4-6 Liberty server.xml: Type 4 data source
<featureManager>
<feature>jndi-1.0</feature>
<feature>jsf-2.2</feature>
<feature>jdbc-4.1</feature>
</featureManager>
<library id="Db2Lib">
<fileset dir="/usr/lpp/db2c10/db2a/jdbc/classes"
includes="db2jcc4.jar db2jcc_license_cisuz.jar" />
<fileset dir="/usr/lpp/db2c10/db2a/jdbc/lib"
includes="libdb2jcct2zos4_64.so" />
</library>
<dataSource id="db2type4"
jndiName="jdbc/sample"
type="javax.sql.XADataSource">
<jdbcDriver libraryRef="jdbclib" />
<connectionManager maxPoolSize="50" />
<properties.db2.jcc driverType="4"
databaseName="DB2A"
currentSchema="DSN81210"
serverName="localhost"
portNumber="38000"
user="DB2USER"
password="{xor}Oz1tLz4sLA==" />
</dataSource>
<application
location="/var/cicsts/SC8CICS2/ITSOJVM1/apps/ employee.jdbc.web.war">
</application>
The <dataSource> element specifies the following XML attributes:
•jndiName="jdbc/sample"
This attribute is the JNDI name that is used by our Employee application to locate the DataSource object. It is specified on the @Resource annotation on the DataSource field in the DatabaseOperationsManager class, as shown in the following example:
@Resource(authenticationType=AuthenticationType.CONTAINER, name="jdbc/sample")
private DataSource ds;
The doLookup() method of the JNDI InitialConext class is used to create the DataSource within our application, as shown in the following example:
ds = (DataSource)InitialContext.doLookup("jdbc/sample");
•type="javax.sql.XADataSource"
The type attribute specifies the type of DataSource that is provided by the JDBC driver. The following options are available:
 – javax.sql.DataSource
 – javax.sql.ConnectionPoolDataSource
 – javax.sql.XADataSource
We specified javax.sql.XADataSource to enable our DataSource to support the XA two-phase commit protocol, which allows updates to Db2 to be coordinated as part of a JTA transaction. For more information about transaction support, see 4.5, “Transactional support with JDBC” on page 108.
•transactional="true"
The transaction element should be allowed to default to true so that the database updates are managed by the Liberty transaction manager. We did not specify this attribute because the default value is true.
•jdbcDriver libraryRef="jdbclib"
The <jdbcDriver> libraryRef attribute must reference the <library> element that is used to configure the location of the JCC driver db2jcc4.jar and the accompanying license file db2jcc_license_cisuz.jar.
• <connectionManager maxPoolSize />
The <connectionManager> element can be used to control attributes of the connection pool. We set the pool size to 50 to equal the number of configured JVM server threads.
•properties.db2.jcc
The following attributes can be used:
 – driverType
The driverType attribute must be set to 4 to specify the use of JDBC type 4 connectivity with the Db2 JCC driver.
 – databaseName
This attribute is the Db2 location value. On our Db2 subsystem, it was set to the value DB2A.
 – currentSchema
This attribute specifies the default schema name that is used to qualify unqualified database objects in dynamically prepared SQL statement. We used the value currentSchema="DSN81210" because Db2 V12 uses DSN81210 as the sample schema.
 – serverName
This attribute is the TCP/IP host on which the Db2 DDF facility is configured to listen. We used the value localhost because we were connecting to a local Db2 on the same MVS image.
 – portNumber
This attribute is the port on which the Db2 DDF facility is configured to listen. Our Db2 DDF was configured to use port 38000.
The location, server name, and port in use by the DDF can be discovered by using the DB2 command -DISPLAY DDF from the DB2I primary option menu in SDSF, as shown in Example 4-7.
Example 4-7 DISPLAY DDF command output
DSNL080I -D2A1 DSNLTDDF DISPLAY DDF REPORT FOLLOWS:
DSNL081I STATUS=STARTD
DSNL082I LOCATION LUNAME GENERICLU
DSNL083I DB2A USIBMSC.SCPD2A1 -NONE
DSNL084I TCPPORT=38000 SECPORT=38001 RESPORT=38002 IPNAME=-NONE
DSNL085I IPADDR=::9.76.61.131
DSNL086I SQL DOMAIN=wtsc80.cpolab.ibm.com
DSNL086I RESYNC DOMAIN=wtsc80.cpolab.ibm.com
DSNL089I MEMBER IPADDR=::9.76.61.131
DSNL105I CURRENT DDF OPTIONS ARE:
DSNL106I PKGREL = COMMIT
DSNL106I SESSIDLE = 001440
DSNL099I DSNLTDDF DISPLAY DDF REPORT COMPLETE
 – user and password
These attributes supply the user ID and password credentials that are used for authenticating with Db2. The password can be XOR or AES encrypted by using the Liberty securityUtility script. This utility can be started by using the CICS-generated wlpenv script, which is in the JVM server working directory, as shown in Example 4-8.
Example 4-8 Use of wlpenv to run securityUtility
$ [SC80] /cicsts/SC8CICS2/ITSOJVM1: ./wlpenv securityUtility encode <password>
Executing: /usr/lpp/cicsts/cicsts54/wlp/bin/securityUtility encode <password>
JAVA_HOME=/usr/lpp/java/J8.0_64_SR4
WLP_INSTALL_DIR=/usr/lpp/cicsts/cicsts54/wlp
WLP_USER_DIR=/var/cicsts/SC8CICS2/wlp
WLP_OUTPUT_DIR=/var/cicsts/SC8CICS2/wlp/servers
SERVER_NAME=itsowlp1
{xor}Oz1tLz4sLA==
4.4.3 Running the application
The Employee web application is accessed on our CICS Liberty server by using the following URL:
http://wtsc80:52080/employee.jdbc.web/
This URL displays the Employee database list form and should function as before when JDBC type 2 connectivity is used, as described in 4.3.4, “Running the application” on page 103.
4.4.4 Container managed security
The application server can be configured to use container managed authentication aliases to provide the user ID and password credentials for JDBC type 4 data sources. This configuration allows the credentials to be separated from the configuration of the data source and shared between data sources.
To enable this method of authentication, the application must use a resource injection annotation for the JNDI lookup of the DataSource rather than a direct JNDI lookup.
A DataSource resource injection is defined in our application by using the field level annotation in the DatabaseOperationsManager class that is shown in Example 4-9.
Example 4-9 Resource annotation for DataSource
@Resource(authenticationType=AuthenticationType.CONTAINER,
name="jdbc/sample")
private DataSource ds;
To enable the use of the authentication alias, we added an <authData> element to our Liberty server.xml. Then, we updated the <dataSource> element with a containerAuthDataRef attribute that identifies the <authData> element (see Example 4-10).
Example 4-10 server.xml updates for container managed security
<dataSource id="db2type4"
jndiName="jdbc/sample"
type="javax.sql.XADataSource"
containerAuthDataRef="db2user">
<jdbcDriver libraryRef="jdbclib" />
<connectionManager maxPoolSize="50" />
<properties.db2.jcc driverType="4"
databaseName="DB2A"
currentSchema="DSN81210"
serverName="localhost"
portNumber="38000" />
</dataSource>
<authData id="db2user" user="DB2USER" password="{xor}Oz1tLz4sLA==" />
Having made these changes, we restarted our Liberty JVM sever and ran our Employee application by using the updated configuration. It successfully connected to the data source by using the supplied credentials.
4.5 Transactional support with JDBC
Db2 supports two-phase commit transactions for JDBC type 2 and type 4 modes of connectivity. Both modes of operation can be integrated with the CICS unit-of-work. However, the way in which this process is done is different because the commit processing is handled differently for the two connectivity modes in CICS.
In our Employee application, the updateEmployee() method in the DatabaseOperationsManager class is used to control how updates are committed to the CICS TSQ and the database EMP table. The method provides commit processing logic by using Java Transaction API (JTA) or Connection.commit() processing, based on the setting of the Toggle JTA button in the Employee application.
The following section describes how the Employee sample application is written to provide transactional coordination between CICS and Db2.
JDBC type 2
When JDBC type 2 connectivity is used in CICS, all JDBC commit processing is coordinated by using CICS and the CICS Db2 attachment. The Db2 JCC driver converts any JDBC commit calls or rollback calls into a JCICS commit or a JCICS rollback call, which results in a CICS sync point being taken. As a result, database updates are committed and backed out when the CICS unit-of-work performs a sync point or rolls back.
In addition, if the JDBC application issues a Connection.commit() when type 2 connectivity is used, the CICS to perform a sync point that commits the Db2 and CICS updates for the unit-of-work.
The default Db2 behavior when a JDBC data source is used is to set autocommit on, which causes the database manager to perform a commit operation after every SQL statement completes. When used in CICS with type 2 JDBC connectivity, significant extra CICS sync point processing can be generated; therefore, it is set to off by using the setAutoCommit(false) method in our sample (see Example 4-11).
Example 4-11 JDBC commit processing in updateEmployee method
// Open connection to d/b and execute
conn = ds.getConnection();
conn.setAutoCommit(false);
…
statement.execute();
 
// Update recoverable CICS TSQ
TSQ tsq = new TSQ();
tsq.setName("DB2LOG");
String msg = String.format("Added %s with last name: %s",
employee.getEmpNo(), employee.getLastName());
tsq.writeString(msg);
 
// Commit connection causing syncpoint for type 2
conn.commit();
 
Note: By default, the Liberty data source support (which is now the default configuration in CICS TS V5.4) rolls back on cleanup if no commit was issued before the end of the request. Therefore, if Connection.commit() or a JCICS Task.commit() is not issued before the end of the Liberty request, the updates to Db2 roll back, which results in an EXEC CICS SYNCPOINT ROLLBACK being issued by CICS. This behavior can be modified by setting the <dataSource> attribute commitOrRollbackOnCleanup="commit".
As an alternative to the use of the Connection.commit() method, you can also use the JTA to control the commit processing with JDBC type 2 connectivity (see Example 4-12 on page 110). However, the use of JTA is less efficient than the use of the Connection.commit() method because it requires another CICS syncpoint to be taken at the start of the UserTransaction.
JCBC type 4
When JDBC type 4 connectivity is used in CICS, JDBC commit processing is not coordinated by the CICS recovery manager because the CICS Db2 attachment is not involved. Instead, the JTA should be used to create a Java global transaction, which is coordinated by the Liberty transaction manager. This Java transaction can be used to coordinate the CICS unit-of-work and updates to XA-capable resource managers, such as an XA data source. XA is a two-phase commit protocol that is supported by many databases and application servers, including Db2, Liberty, and CICS.
To use XA support with JDBC type 4 connectivity, it is first necessary to ensure that the data source is configured to use XA. Our <dataSource> element was configured with the following attribute to denote it as supporting XA:
type="javax.sql.XADataSource"
In our Employee application, the updateEmployee() method in the DatabaseOperationsManager class is used to control how updates are committed. When JTA is enabled, the updateEmployee() method instantiates a UserTransaction and uses the UserTransaction.commit() method to coordinate the JTA transaction, which then controls the subordinate CICS unit-of-work and JDBC connection.
The default Db2 behavior when a JDBC data source is used is to set autocommit on if no JTA transaction is active, which causes the database manager to perform a commit operation after every SQL statement completes. This feature is set to off by using the setAutoCommit(false) method in our sample for consistency with the type 2 connectivity scenario.
Our example code is shown in Example 4-12.
Example 4-12 JDBC JTA commit processing in updateEmployee method
// Create JTA transaction and syncpoint
UserTransaction utx;
utx = (UserTransaction)
InitialContext.doLookup("java:comp/UserTransaction");
utx.begin();
 
// Open connection to d/b and execute
conn = ds.getConnection();
conn.setAutoCommit(false);
 
…
statement.execute();
 
// Update recoverable CICS TSQ
TSQ tsq = new TSQ();
tsq.setName("DB2LOG");
String msg = String.format("Added %s with last name: %s",
employee.getEmpNo(), employee.getLastName());
tsq.writeString(msg);
 
// Commit JTA transaction and the CICS UOW and XA data source
utx.commit();
 
Note: If a Connection.commit() is used in a JDBC application when JDBC type 4 connectivity is used, only the updates to the Db2 data source are committed. Any updates that are part of the CICS unit-of-work must be committed separately.
4.6 Tracing JDBC
To diagnose problems with JDBC connectivity in CICS, it can be useful to use the Db2 JCC driver trace. We used extra properties on our <dataSource> <properties.db2.jcc> sub-element to activate tracing for data sources that are defined in our CICS Liberty environment, as shown in Example 4-13.
Example 4-13 Enabling JCC trace properties
<properties.db2.jcc driverType="4"
databaseName="DB2A"
traceDirectory="/var/cicsts/SC8CICS2/wlp/servers/itsowlp1/logs/"
traceFile="jcc.trc"
traceFileAppend="false"
traceLevel="-1"
user="DB2USER"
currentSchema="DSN81210"
password="{xor}Oz1tLz4sLA=="
serverName="localhost"
portNumber="38000" />
For more information about the available JCC trace properties, see the Enabling trace for a datasource connection section of the Collecting Data: Tracing with the IBM Data Server Driver for JDBC and SQLJ page of the IBM Support website.
 
..................Content has been hidden....................

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