Developing Java applications with DB2 for z/OS
This chapter provides an overview of DB2 10 for z/OS support for Java and describes selected topics about how to use that support. This chapter looks at DB2 support for drivers for Java applications and explains the design principles of dynamic and static SQL. This chapter also shows how to define and configure the IBM DB2 Driver for JDBC and SQLJ in various situations and demonstrates the usage of pureQuery for optimization of
dynamic SQL.
dynamic SQL.
This chapter also provides a short running sample of a stand-alone application that uses DB2 with JPA and explains the differences of stand-alone Java applications and applications in managed environments.
This chapter demonstrates how to get a good dynamic statement cache hit ratio and describes locking.
This chapter covers the following topics:
6.1 Drivers for Java applications
With DB2 support for Java, you can access relational databases from Java application programs. This is done by a driver that implements the Java Database Connectivity (JDBC) 4.0 standard, which is defined by the Java Specification Requests (JSR) 221. JDBC defines the standard application programming interface (API) for accessing relational database systems, such as DB2, from Java. Although it is used decreasingly and directly by programs because of the advent of more generic persistency frameworks such Hibernate and OpenJPA, this API is the fundamental building block for writing DB2 Java applications.
For more information about the specification, go to the following website:
JDBC drivers are client-side adapters (although they could be clients in a server) that convert requests from applications through the usage of the API to a protocol that the
database understands.
database understands.
JDBC implementations normally implement two specification types:
•Type 2
Drivers that are written partly in the Java programming language and partly in native code. They have no network connection and communicate with the database through interprocess communications. Their native code must be installed in the file system on the same machine as the database and can be used only to connect to a local database manager. The driver installation in this case is part of the database installation process. The application run time is notified about the location of the installed native code by looking at the LIBPATH environment variable. The native code can effectively work together with other components of the operating system, such as Workload
Manager (WLM).
Manager (WLM).
•Type 4
Drivers that are written solely in Java and connect through TCP/IP to a local or remote database. However, even a connection to a local database remains a remote
network connection.
network connection.
The other JDBC types are not important regarding Java development. The type of a driver should not be confused with the specification level it implements. Type 4 means network driver and JDBC 4.0 means specification level 4.0.
IBM Data Server Driver for JDBC and SQLJ is a single driver that includes JDBC type 2 and JDBC type 4 behavior and that implements JDBC 4.0 and JDBC 3.0. Which type or version is used depends solely on the configuration options that are made while opening the connection to the database.
From an application point of view, there is no difference between the two types. The API is the same. The Java part of both drivers must be available to application clients in the class path. The application can make type 2 and type 4 connections by using this single driver instance. Type 2 and type 4 connections can be made concurrently.
To work with DB2 for z/OS, the license file db2jcc_license_cisuz.jar must be in
the class path.
the class path.
More information about the driver architecture and its configuration options can be found in Chapter 3, “DB2 configuration options for Java client applications” on page 81.
With the sqlj4.zip file in the class path, the IBM Data Server Driver for JDBC and SQLJ provides SQLJ functions that include JDBC 4.0 and later functions, and JDBC 3.0 and
earlier functions.
earlier functions.
6.2 Dynamic SQL
Since its first release with JDK 1.1 in 1997, the JDBC API as a generic database access technology was intended for dynamic SQL. Even the selection of a specific database is done dynamically because the JDBC driver for that database is assigned and loaded only at run time. According to this programming model, dynamic SQL statements are constructed and prepared also only at run time. They are not known to the application server or the database in advance. Sometimes even the programmer does not know what the results of a dynamic SQL generation routine will be exactly. The dynamic SQL string building can be bypassed and the SQL can be presented to the API as a constant.
Whether hardcoded or generated, the result is always a string with an SQL statement that then is given to the appropriate JDBC API. Both methods are considered dynamic because the database does not know the SQL in advance either way. The generation process can encompass the generation parameters as well or it can include parameter markers (question marks) that can be substituted later through an API call.
In the Java community, programming with dynamic SQL is the prevailing method. JDBC implements the dynamic SQL model. A major advantage is that application development is faster than with other techniques. All database vendors include a JDBC driver in their databases, making JDBC a universal technique that is known to almost every programmer. Although JDBC always uses the same programming principles, it does not allow a fully portable program. Among other advantages, persistency frameworks such as Hibernate or JPA address the portability problem. But even in the form of that new persistency layer, the underlying design schema remains dynamic SQL handled by a JDBC driver.
Although dynamic SQL with raw JDBC API statements is being used less often, there are some situations where it is the most suitable solution:
•The table structure is too complex for JPA entities.
•No entities are involved (for example, in mass updates).
•You are using maintenance or administrative programs.
•When the persistency framework is not powerful enough.
A short code snippet shows the principles of JDBC API coding. We do not go into too much detail because JDBC programming is widely known. Instead, we describe some important design issues that are relevant to other parts of this book.
As you can see in Example 6-1, the DriverManager.getConnection method with its parameters connects to the database. We could have used the Datasource interface as well, if we had used a predefined data source. We then ask the connection object to return a preparedStatement. Afterward, we present the SQL to the statement, leaving two places unclear. We code a “?” as a parameter marker instead. The parameter markers are filled afterward with a concrete value.
Example 6-1 Example of using PreparedStatement.executeQuery
Connection con = DriverManager.getConnection(url, properties);
PreparedStatement pstmt;
...
pstmt = con.prepareStatement(
"UPDATE EMPLOYEE SET PHONENO=? WHERE EMPNO=?");
pstmt.setString(1,"4657"); // Assign value to first parameter
pstmt.setString(2,"000010"); // Assign value to second parameter
numUpd = pstmt.executeUpdate(); // Perform update
pstmt.close();
Then, the prepared statement is run by the database.
In addition, caches are filled. If you ran the statement inside WebSphere Application Server, the JVM’s prepared statement cache is filled or, if the statement was already ran, you get a created statement object. The statement object is built around the statement "UPDATE EMPLOYEE SET PHONENO=? WHERE EMPNO=?". The statement can have different parameters and a different case, but it remains the same statement. The cache includes only the dynamically created Java object for that specific statement. It does not include any DB2
related information.
related information.
On the DB2 side, a cache entry is also created if DB2 is defined that way. No Java object is stored, but access strategy-related information is stored. Both caches complement
each other.
each other.
As an alternative, you can generate a complete SQL string without placeholders for the parameters. It would look like the following string:
"UPDATE EMPLOYEE SET PHONENO='4657' WHERE EMPNO='000010'";
This string results in a new Java statement object and new objects for other employees or phone numbers because you have a new statement instead of a parameter substitution. You can give this string to a prepareStatement for execution, But using a simple createStatement is sufficient, as shown in Example 6-2.
Example 6-2 Example of using createStatement
stmt = con.createStatement();
numUpd = stmt.executeUpdate(
"UPDATE EMPLOYEE SET PHONENO=’4657’ WHERE EMPNO=’000010’"
);
Only parameter markers allow DB2 to use the dynamic statement cache. Otherwise, a dynamic rebind for the mini-plan must be made. As of DB2 10, there are some additional capabilities to caching, as described in 6.7.4, “Literal replacement” on page 330.
6.3 Static SQL
Application development with DB2 with compiled languages is mostly done according to a static SQL model. Static means that the SQL statement is fixed at development time and known to the database. Only call parameters are variable at run time.
SQLJ, the name for static SQL in Java, is based on JDBC APIs by using embedded SQL to access the database. The database normally uses static SQL, but can use dynamic SQL in some cases. Because static SQL prepared in advance, performance is better compared to dynamic SQL. By contrast, dynamic SQL is not known by the system at compile time; parsing, validation, preparation of statements, and determination of the access path in the database is done only at run time. Errors or poorly performing statements might remain undetected until problems in production occur.
With SQLJ, the SQL statements are not part of the Java language. They are marked with #sql in the Java source code, but must be extracted before the Java compiler sees them or they cause Java compile errors. Therefore, the Java class is edited in a <name>.sqlj file that is then processed by the SQLJ translator. How an SQLJ statement is embedded in to the Java source code is illustrated in Example 6-3.
Example 6-3 Sample of an SQLJ statement
MyContext context = new MyContext();
String empno = "000021";
#sql [context] {
SELECT FIRSTNME
INTO :firstname
FROM EMP WHERE EMPNO = :empno
}
return firstname;
The SQLJ translator is the sqlj command. By default, It is in /usr/lpp/db2/jdbc/bin on z/OS and in <install root>SQLLIB/bin on Windows. It replaces all #sql statements with generated Java code and creates new <name>.java files. The SQL is placed into SQLJ serialized profiles, which are <name>.ser files with extracted SQL that are used by the db2sqljcustomize utility. This utility creates (authorization is provided) four packages by default, one for each isolation level in the target DBRM.
This process can detect potential errors early.
Here are the advantages of static SQL:
•SQLJ commands are shorter and easier to read than dynamic SQL commands.
•The syntax is checked at compile time. Errors can be repaired early in the
development process.
development process.
•Query results are type checked.
•Static SQL is less vulnerable to malicious SQL injection.
•A query plan is generated, which normally runs faster than dynamic access.
•A DBA and programmer can better interact with each other through the SQL.
•COBOL programmers are used to the programming model.
•Monitoring and tracing are easier because a selection can be made on the package name.
•The security model for SQLJ is different than for dynamic SQL. The connect user ID must be authorized for every DB2 object that is used by a program with dynamic SQL. If many different authorization IDs are used, this leads to many grants in DB2, making the security model disordered. With static SQL, authorization is made is on the package and not on the DB2 objects that are used by the package. This allows a more granular approach.
There are some SQLJ sample programs that come with the DB2 product. They are in <install root>SQLLIBsamplesjavasqlj for DB2 on Windows or in /usr/lpp/db2/samples/java on z/OS.
Despite all these advantages, only a few Java projects use SQLJ. The more complex application build process might be one reason why this is so. Another reason is that SQLJ remains basically JDBC and has no support for object-relational mapping (ORM). A programmer’s productivity and application maintainability seem to be more important for many projects than advantages in performance and security.
Here are the disadvantages of the static SQL model:
•More build steps are necessary, which might span multiple departments
and areas of responsibility.
and areas of responsibility.
•Good support is needed, for example, the SQLJ editing tools with IBM Rational Application Developer or IBM Data Studio. For Maven, the build manager for Java projects, some additional build steps must be included.
•The SQLJ programming model might not be known by Java programmers. Only few
samples exist.
samples exist.
•There is no support for ORM.
•Portability is reduced because not every database supports static SQL.
WebSphere Application Server offers support for static SQL for Enterprise Java Beans (EJB) 2.x and later entity beans with the ejbdeploy SQLJ option. In EJB 3.0 and later, container- managed (CMP) Enterprise beans are replaced by JPA entities. Although EJB 2.x could be used in later versions of WebSphere Application Server, it is unlikely.
With JPA, this feature is offered through pureQuery and offers you the advantages of both dynamic and static SQL.
6.4 PureQuery optimization
PureQuery is a high performance Java data access platform that helps manage applications that access data.
It has the following features:
•APIs that are built for ease of use and to simplify the usage of preferred practices.
•Development tools, which are delivered in IBM Data Studio full client, for Java and
SQL development.
SQL development.
•A run time for optimizing database access and simplifying management tasks.
All three features can be used or omitted independently from each other.
pureQuery provides an alternative set of APIs. They are similar in concept to JPA and can be used instead of Java Database Connectivity (JDBC) to access DB2. Even if these APIs are not used in your application, the pureQuerys Client optimization feature makes it possible to take advantage of static SQL for existing JDBC applications without modifying existing dynamic source code.
Figure 6-1 on page 303 shows the flow between pureQuery and the database.
The general concept is to collect all dynamic SQL statements of your application at development or deployment time by using pureQuery. The application developer does not need to be involved in this process. The collected statements are then bound into packages in the database. At execution time, the pureQuery run time uses the static SQL from the packages instead of the dynamic SQL to work with DB2. Where dynamic SQL statements cannot be collected or converted, the run time continues to use dynamic SQL.
Figure 6-1 The flow from application to database using pureQuery
There are two ways of collecting the SQL:
•If JPA is used, then WebSphere Application Server or pureQuery is used to examine persistence-units in the persistence.xml file of an application module. Only SQL from named queries is detected.
•SQL statements can be traced and captured at run time. All SQL statements are detected.
To understand the functionality, look at the way SQL is collected for JPA. Either a command or IBM Data Studio can be used.
The wsdb2gen command is in the /bin directory of WebSphere Application Server. To run it, extend the WebSphere class path by using the pdq.jar, pdqmgmt.jar and db2jcc4.jar files that come with IBM Data Studio. A sample command is shown in Example 6-4.
Example 6-4 An example wsdb2gen command
wsdb2gen -pu jpa_db2 -url jdbc:db2://d0zg.itso.ibm.com:39000/DB0Z -user
db2r1 -pw passw0rd
The utility uses the persistence unit name as input, along with other parameters, and generates an output file that contains SQL statements that are required by all entity operations, including persist, remove, update, and find. It also generates the SQL statements that are needed in the running of JPA named queries. Other dynamic SQL cannot be found and us not included in the output.
The ANT task WsJpaDBGenTask provides an alternative to the wsdbgen command.
The output of the command is a file that contains the persistence unit name followed by a suffix of .pdqxml. The pdqxml file is written to the same directory as your persistence.xml file. Alternatively, by using IBM Data Studio, pureQuery tools can be added to your
JPA project.
JPA project.
The jpa_db2_web project (see Appendix I, “Additional material” on page 587) is a small project that illustrates the pureQuery functionality. It simply lists the DB2 Department Table in the SAMPLE database in the browser.
To enable pureQuery support for your project in IBM Data Studio, go to the Java Perspective and right-click the jpa_db2_web project. Then, select Data Access Management → Add Data Access Development support. The window that is shown in Figure 6-2 opens.
Select the Add pureQuery support to project check box, which adds the pureQuery runtime libraries to your build path. The run time has five JAR files with names that start with pdq. The WebSphere Application Server run time must be in the class path as well.
Figure 6-2 Add data access management support to the project
You must define a database connection to the SAMPLE database in this window. It is used to check the SQL statements and prefix table names with the provided schema name in the generated output statements.
The pdqxml file then is generated by right-clicking the persistence.xml file of your project in the Java Perspective. Then, select Data Access Development → Generate pureQueryXML File, as shown in Figure 6-3. A file named jpa_db2.pdqxml, which is named after the persistence-unit name used in that project, is generated.
Figure 6-3 Generate pdqxml files with IBM Data Studio
The pdqxml file can be checked afterward by using a special view that is provided by IBM Data Studio, as shown in Figure 6-4. Collected SQL statements can be run against the defined database, changed, or cleared from the bind process. Then, the generated SQL could be optimized in collaboration with the database administrators.
Figure 6-4 Work with jpa_db2.pdqxml after generation
The pdqxml file must be packaged inside your archive file in the same location as the persistence.xml configuration file, usually the META-INF directory of the module.
The application can now be deployed to the server. However, it works with dynamic SQL unless you bind the database packages. To bind the packages, in the WebSphere Application Server console, click WebSphere enterprise applications, click the application name, and click SQLJ profiles and pureQuery bind files. Alternatively, you can use the AdminTask command, as shown in Example 6-5.
Example 6-5 Bind the packages
AdminTask.processSqljProfiles('[-appName jpa_db2 -url jdbc:db2://d0zg.itso.ibm.com:39000/DB0Z -user db2r1 -password ******** -options -classpath [/u/db2r1/pureQuery/pdq.jar:/u/db2r1/pureQuery/pdqmgmt.jar:/usr/lpp/db2/d0zg/jdbc/classes/db2jcc4.jar ] -profiles [jpa_db2_web.war/WEB-INF/classes/META-INF/jpa_db2.pdqxml ]]')
Be sure that you grant execution authority on the package to public or to the user that is defined for the data source in WebSphere Application Server.
The pureQuery integration that is delivered with WebSphere Application Server requires the addition of the Data Studio pureQuery run time to the JDBC provider, as shown in Example 6-6. It must be purchased separately. In the WebSphere environment, you place the pureQuery JAR files pdq.jar and pdqmgmt.jar in to the DB2 JDBC Driver
Provider class path.
Provider class path.
Example 6-6 Add the pureQuerey run time to JDBC providers class path
${DB2_JCC_DRIVER_PATH}/db2jcc4.jar
${UNIVERSAL_JDBC_DRIVER_PATH}/db2jcc_license_cu.jar
${DB2_JCC_DRIVER_PATH}/db2jcc_license_cisuz.jar
${PUREQUERY_PATH}/pdq.jar
${PUREQUERY_PATH}/pdqmgmt.jar
In WebSphere Application Server, you must use a JPA for the WebSphere Application Server persistence provider. Only this JPA uses static SQL support by using the DB2 pureQuery feature. This is the default in WebSphere. The original Apache OpenJPA driver does not support pureQuery optimization. Be sure not to overwrite this default with a provider statement in your persistence.xml file.
If you ran your application in server MZSR015, you could verify that your SQL is static by activating a trace in the server:
/F MZSR015,TRACEJAVA='JPA=all: openjpa=all: SystemErr=all: SystemOut=all: com.ibm.pdq=all'.
Reset the trace by running /F MZSR015,TRACEINIT.
You can find another pureQuery optimization example at the following website:
6.5 DB2 support for Java stand-alone applications
An application running in WebSphere Application Server almost always uses a managed data source that is predefined in the server. If so, JDBC driver parameters are defined in the application server through the WebSphere data source properties. If you do not run in a managed container, you must set up the connection to the database in the program itself.
Java stand-alone applications are used often. On z/OS, the traditional batch job often is developed in Java.
As an example, Java development cannot be done without frequent JUnit tests, which are Java stand-alone applications. Today, every Java class has a corresponding test class that checks all the methods of the class. A framework that is called JUnit (http://www.junit.org) organizes the tests. After development, the program must build, normally after all its components are checked out of a source code version control system, such as Concurrent Versions System (CVS). The build process includes the creation of Java archives in which the application is packaged. Java archives (JARs), web application archives (WARs), and enterprise archives (EARs) must be built. Many dependencies to other Java archives must be resolved during that process. Then, the application then is deployed automatically to a
Java Platform, Enterprise Edition server.
Java Platform, Enterprise Edition server.
Today, Apache Maven is the open source project that is usually used for this work. It is a Java stand-alone application that runs several times a day.
During the development cycle, database definitions must be provided at several points. Unit tests must check data that comes from a database or the packaging or deployment process must include the preconfigured JDBC driver.
This section shows you some ways of dealing with different configuration options for the usage of IBM DB2 Driver for JDBC and SQLJ for stand-alone applications.
6.5.1 Alternatives for setting the JDBC driver parameters
Even if you develop a Java Platform, Enterprise Edition application that is used with a full server, you most likely use stand-alone Java applications, for example, in unit tests for your JPA entities or other JDBC classes. It is preferable to have the JDBC driver configured the same way for these tests. Hence, this section shows you some possibilities about changing the driver properties.
For example, the currentSchema property is often defined as a JDBC driver property outside of the Java program. This way, the Java class can be used for multiple database schema without having to change the code. This situation also applies to the
defaultIsolationLevel property.
defaultIsolationLevel property.
You can specify driver properties in the following ways:
•As Java system properties during the startup of the JVM. They are called IBM Data Server Driver for JDBC and SQLJ configuration properties because every connection to DB2 on this JVM inherits this configuration.
•Specify IBM Data Server Driver for JDBC and SQLJ properties during the setup of a specific connection.
•Specify connection and runtime properties for JPA programs in the persistence.xml file.
•Change settings for a single unit of work in your program.
Specification at connection setup
There are three different ways to set driver parameters during connection setup:
•Set the java.util.Properties value in the info parameter of a DriverManager.getConnection call, as shown in Example on page 309.
Example 6-7 Setting JDBC driver parameters with java.util.Properties
Properties properties = new Properties();
properties.put("user", "db2r1");
properties.put("password", "pwpwpw");
properties.put("currentSchema", "DSN81010");
properties.put("defaultIsolationLevel", new Integer(
java.sql.Connection.TRANSACTION_READ_COMMITTED).toString());
String url = "jdbc:db2://wtsc63.itso.ibm.com:39000/DB0Z";
Connection conn = DriverManager.getConnection(url, properties);
•Set a java.lang.String value in the url parameter of a DriverManager.getConnection call, as shown in Example 6-8 on page 309.
•Use setXXX methods, where XXX is the unqualified property name, with the first character capitalized when using subclasses of com.ibm.db2.jcc.DB2BaseDataSource. For example, to change the defaultIsolationLevel property, you use the method ds.setDefaultIsolationLevel() before establishing the connection. In this case, the class is no longer portable because you are using the IBM Data Server Driver for JDBC and SQLJ interfaces directly.
These examples focus on the defaultIsolationLevel and currentSchema properties because they are frequently needed during connection setup.
You can find a full list of the properties in DB2 10 for z/OS Application Programming Guide and Reference for Java, SC19-2970.
The isolation level constant in the java.sql.Connection class is an integer. For the properties dictionary, it must be converted to a string.
No implementation-specific classes are used in this example. Thus, portability is ensured.
The IBM Data Server Driver for JDBC and SQLJ supports a number of isolation levels, which correspond to database server isolation levels. Table 6-1 shows the equivalency of standard JDBC and DB2 isolation levels.
Table 6-1 Equivalency of JDBC and DB2 isolation levels
|
JDBC value
|
DB2 isolation level
|
|
java.sql.Connection.TRANSACTION_SERIALIZABLE
|
Repeatable read
|
|
java.sql.Connection.TRANSACTION_REPEATABLE_READ
|
Read stability
|
|
java.sql.Connection.TRANSACTION_READ_COMMITTED
|
Cursor stability
|
|
java.sql.Connection.TRANSACTION_READ_UNCOMMITTED
|
Uncommitted read
|
As of WebSphere Application Server V8.5, the default isolation level is read stability. For a stand-alone JPA, the default is cursor stability.
The driver parameters are set as shown in Example 6-8.
Example 6-8 Setting JDBC driver parameters in the connection url
String user = new String("db2r1");
String password = new String("pwpwpw");
String currentSchema = new String("DSN81010");
String defaultIsolationLevel = new Integer(
java.sql.Connection.TRANSACTION_SERIALIZABLE).toString();
String url = "jdbc:db2://wtsc63.itso.ibm.com:39000/DB0Z:";
String url2 = "user=" + user + ";"
+ "password=" + password + ";"
+ "defaultIsolationLevel=" + defaultIsolationLevel + ";"
+ "currentSchema=" + currentSchema + ";";
Connection conn = DriverManager.getConnection(url + url2);Connection conn = DriverManager.getConnection(url +url2);
Example 6-9 Connection url string
jdbc:db2://wtsc63.itso.ibm.com:39000/DB0Z:user=db2r1;password=pwpwpw;defaultIsolationLevel=8;currentSchema=DSN81010;
In the connection url string, all text after the last “:” is treated as JDBC driver properties, which are optional. If you provide JDBC driver properties in the connection string, do not forget the last “;” because otherwise it will not work.
Specification at JVM start
IBM Data Server Driver for JDBC and SQLJ configuration properties all start with db2.jcc. They are specified as JVM system properties, that is, as -D parameters when starting the JVM. For example, -Ddb2.jcc.currentSchema=DSN81010 defines the default currentSchema for all connections coming from that JVM.
Alternatively, you can point -Ddb2.jcc.propertiesFile=/home/myJcc.properties to a file that contains the properties, for example, db2.jcc.currentSchema=DSN81010 and other properties that you want to be valid for that Java run. If you use a DB2JccConfiguration.properties file without pointing to it at JVM startup, you must include the directory that contains that file in the class path. It is only searched by the driver if -Ddb2.jcc.propertiesFile is not set.
Definition of JDBC properties for JPA applications
With JPA, the META-INF/persistence.xml file is the location where the JPA implementation expects to find its runtime definitions. Provider-specific parameters must be defined here.
For our Java stand-alone example, we use the Apache OpenJPA implementation (http://openjpa.apache.org) because the IBM JPA implementation in WebSphere Application Server is based on OpenJPA. In Example 6-10, you see a persistence.xml file for use in a Java SE environment, as indicated by transaction-type="RESOURCE_LOCAL". In contrast, in a persistence.xml file for use in WebSphere Application Server, it is transaction-type="JTA". In WebSphere Application Server, almost no property is defined, but in Java SE, you must specify connection parameters.
Example 6-10 Example of an OpenJPA persistence.xml file
<?xml version="1.0"?>
<persistence xmlns="http://java.sun.com/xml/ns/persistence"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" version="2.0"
xsi:schemaLocation="http://java.sun.com/xml/ns/persistence
http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd">
<persistence-unit name="jpadb2-zos" transaction-type="RESOURCE_LOCAL">
<provider>org.apache.openjpa.persistence.PersistenceProviderImpl</provider>
<class>ibm.itso.entities.Dept</class>
<class>ibm.itso.entities.Emp</class>
<properties>
<property name="openjpa.RuntimeUnenhancedClasses" value="unsupported" />
<property name="openjpa.jdbc.Schema" value="DSN81010" />
<property name="openjpa.ConnectionDriverName" value="com.ibm.db2.jcc.DB2Driver" />
<property name="openjpa.ConnectionProperties" value="username=db2r1,password=pwpwpw" />
<property name="openjpa.ConnectionURL" value="jdbc:db2://d0zg.itso.ibm.com:39000/DB0Z:clientApplicationInformation=jpaDB2Tests;defaultIsolationLevel=4;" />
<property name="openjpa.Log" value="DefaultLevel=ERROR, SQL=TRACE" />
<property name="openjpa.DataCache" value="false" />
<property name="openjpa.QueryCache" value="false" />
<property name="openjpa.jdbc.DBDictionary" value="db2(batchLimit=100)" />
<property name="openjpa.jdbc.QuerySQLCache" value="false" />
<property name="openjpa.ConnectionFactoryProperties" value="PrettyPrint=true, PrettyPrintLineLength=72"/>
</properties>
</persistence-unit>
</persistence-unit>
</persistence>
The default schema is defined in <property name="openjpa.jdbc.Schema" value="DSN81010" />.
The default isolation level here is part of the connection url. During our tests, the definition openjpaConnectionProperty had no effect.
With OpenJPA, every property starting with openjpa is proprietary.
If the parameter name defaultIsolationLevel has a spelling error, no error message is given. The parameter would be ignored instead and set to the default value.
Here are the connection parameters with a short description of each one:
•javax.persistence.jdbc.driver: Fully qualified name of the driver class
•javax.persistence.jdbc.url: Driver-specific connection URL
•javax.persistence.jdbc.user: User name that is used by the connection
•javax.persistence.jdbc.password: Password that is used for the connection
Specification for a single unit of work
JDBC isolation levels can be set for a unit of work within a JDBC program by using the Connection.setTransactionIsolation method.
6.5.2 Java batch considerations with DB2
For almost all companies in every industry, batch processing is still a fundamental, mission-critical component. It might be tempting for Java developers to reuse classes they have developed for their online transaction processing (OLTP) in batch programs. In some cases, even online transactions are called from batch. This can be successful if there is a dedicated batch window when no users are online and the numbers of transactions are
not high.
not high.
But when you plan a batch process with millions of database updates, there are things to consider. OLTP is triggered by a user with a direct response. To initiate OLTP, users typically complete an entry form or perform other actions through a user interface application component. The user interface component then initiates the associated online transaction with the business logic in the background. When the transaction is complete, the same user interface or other user interface component presents the result of the transaction to the user. The response can be data or can be a message regarding the success or failure of the processing of the input data. The transaction has high priority in the system and normally gets system resources at once. Data is committed after every transaction.
In contrast, batch processes require no user activity. Most batch programs read data from various sources (for example, databases, files, and message queues), process that data, and then store the result. The speed of a single update is not that important but the overall throughput is. The priority in the system is lower because the online work must not be disturbed. The work follows an input - processing - output pattern where the input from one process is often needed for input for another process. If there are abends, restarts of the same job must be possible at any time. In order to not have to start from the beginning, checkpoints should be built in to the application.
A checkpoint is one of the key features that distinguishes bulk jobs from OLTP applications, in that data is committed in chunks, along with other required housekeeping to maintain recovery information for restarting jobs. An extreme example is doing a checkpoint after every record, which equates to how OLTP applications typically work. At the other extreme is doing a checkpoint at the end of the job step, which might not be feasible in most cases because recoveries can be expensive and too many locks can be held in the database for too much time. A checkpoint is somewhere between these two extremes. The checkpoint interval can vary depending on a number of factors, such as whether jobs are run concurrently with OLTP applications, how long locks can be held, the amount of hardware resources available, the SLAs to be met, and the time that is available to meet deadlines. Depending on the technology that is used, there are static ways of setting these checkpoint intervals, but ideally checkpoint intervals can be set dynamically as well.
The application logic should take the following items into consideration:
•Database commits should, if possible, not occur after a single update but only after a group of updates.
•Plan checkpoints at which an application restart can occur.
•If transactions must be made in an OLTP server from a batch program, use WLM service classes that prevent the normal online transactions from being constrained.
•A JDBC batch statement.
•Consider using a WebSphere embeddable EJB container for your batch. It is especially useful if you can then avoid connecting to the WebSphere Application Server that is used for online work. The batch can be assigned to a special WLM service class. All database services such persistence service with JPA, transactions with EJBs, and bean validation are available.
•Consider using a WebSphere Extended Deployment Compute Grid. You can process business transactions cost-effectively by sharing resources and development skills between batch and online transactions (OLTP).
6.5.3 Portability
When you search for a sample of a JDBC program, you many of them that start with the following string:
// Load the driver
Class.forName("com.ibm.db2.jcc.DB2Driver");
Class.forName("com.ibm.db2.jcc.DB2Driver");
This string couples the Java class unnecessarily to a specific implementation and prevents portability. As of JDBC 4, you do not need to load the drive if you have the driver implementation classes in your class path; in DB2, these are in db2jcc4.jar. The java.sql.DriverManager methods find the implementation classes that are using the service location mechanisms. If the connection URL starts with jdbc:db2, the IBM Data Server Driver for JDBC and SQLJ is found.
JDBC 4.0 Drivers must contain the META-INF/services/java.sql.Driver file. This file points to the correct implementation class; for DB2, it is com.ibm.db2.jcc.DB2Driver.
6.5.4 Sample Java SE stand-alone application with JPA and DB2
This section shows a simple Java stand-alone application. It shows the basic things that you need to start with your own application and how the tools in IBM Data Studio can support you. The application is based on the Java Persistence API (JPA) instead of raw JDBC or SQLJ because JPA is the most common approach. Samples using direct JDBC statements can be found in DB2 for z/OS and WebSphere: The Perfect Couple, SG24-6319.
Later in the book, you see a more complex application that runs inside a Java Platform, Enterprise Edition container (see “A short Java Platform, Enterprise Edition example” on page 346). There you can find more background for programming with JPA.
To run the example yourself, you need a local DB2 or a DB2 on z/OS system with an installed SAMPLE database, the IBM DB2 Driver for JDBC and SQLJ (db2jcc4.jar), the license JAR for the specific platform, the OpenJPA implementation openjpa-all-2.2.0.jar, and the logging framework slf4j-simple-1.6.6.jar. For more information about obtaining these items, see Appendix I, “Additional material” on page 587. Some of the JAR files can be found in a WebSphere Application Server installation. You can get one, for example, if you augment IBM Data Studio with the WebSphere Application Server test environment described Appendix C, “Setting up a WebSphere Application Server test environment on IBM Data Studio” on page 523.
Complete the following steps:
1. In IBM Data Studio in the Data Perspective window, click Data Source Explorer create a DB connection. The example that is shown in Figure 6-5 shows a connection to DB2 on z/OS. However, the example should work for any DB2 system that has the sample database installed.
Figure 6-5 Create a data source connection
Use the valid connection parameters for your system. The data source connection inside IBM Data Studio is needed so that you can use the Data Studio tools for the generation of Java JPA entities. If you defined the Java code by typing the class definitions, this step is not needed.
2. Check whether the sample DB is present by scrolling through the hierarchy that opens after you establish the connection, as shown in Figure 6-6. You need the DB to create entities and for the test runs.
Figure 6-6 Check whether you can connect to the sample database
3. In the Java Perspective in the Package Explorer window, create a JPA project named jpa_db2, as shown in Figure 6-7. You can use the default location, and do not need to select a target run time. Check whether the configuration shows Minimal JPA 2.0 configuration. The project does not need to be added to an EAR.
Figure 6-7 Create a JPA project
This should give you a project with the structure shown in Figure 6-8:
– A Java project with a source folder.
– A META-INF directory with a default persistence.xml file.
Figure 6-8 Project structure
In the JPA Perspective window, the Project Explorer provides a special view of the persistence.xml file that is in the META-INF directory. There are default contents that are already generated for the still empty persistence-unit that is named after your project name. In the Java Perspective window, it is shown only as a normal file in META-INF.
Example 6-11 shows the generated JPA definition file.
Example 6-11 Generated persistence.xml
<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.0" xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd">
<persistence-unit name="jpa_db2">
</persistence-unit>
</persistence>
We are now going to generate a Java JPA entity from an existing database table. In JPA terms, this is what is known a bottom-up approach. Complete the following steps:
1. Right-click the project name and select JPA Tools → Generate Entities from Tables, as shown in Figure 6-9.
Figure 6-9 Select a table for the generation of JPA entities
2. Select the correct database connection. It is the one that created in step 1 on page 314. Next, you must select the schema under which the sample database is defined. In this example, it is DSN81010. The table names then display.
3. Select the DEPT table, as shown in Figure 6-10.
Figure 6-10 Select the DEPTtable in the DSN81010 schema
Leave the settings in the Table Associations window at the defaults. If there are relationships among other tables, you can define their associations here. Because you have only one table in our sample, you do not need to specify anything, as shown in Figure 6-11.
Figure 6-11 Relationships to other classes
4. In the Customize Default Entity Generation window, set Key generator to auto. This inserts the annotation @GeneratedValue(strategy=GenerationType.AUTO) in to your generated Java class for the key field deptno. Specify com.ibm.itso.entities in the Packages field, as shown in Figure 6-12.
You do not have to specify a class name because the table name is used as a class name by default. The default behavior can be changed afterward by using special
Java annotations.
Java annotations.
Figure 6-12 Generated class characteristics
This key generator generates a Java package named com.ibm.itso.entities with a class named Dept.java, which is named after the table name. In addition, the persistence.xml file is expanded by one entry, which declares the Dept class as an entity, as shown
in Example 6-12.
in Example 6-12.
Example 6-12 Added Dept class in the persistence.xml file
<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.0" xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd">
<persistence-unit name="jpa_db2">
<class>com.ibm.itso.entities.Dept</class>
</persistence-unit>
</persistence>
The Dept.java file still has syntax errors because the class path is missing some important libraries. We are now going to fix this.
a. Switch to the Java perspective, right-click the project name, and select Build Path → Add External Archives. Add the following archives:
• slf4j-simple-1.6.6.jar
• openjpa-all-2.2.0.jar
• db2jcc4.jar
• db2jcc_license_cu.jar or if you test with DB2 on z/OS db2jcc_license_cisuz.jar
Even after the class path contains the correct libraries, the project cannot be built because one error remains:
Class "com.ibm.itso.entities.Dept" is included in a persistence unit, but is not mapped.
This seems to be an Eclipse-related error and it can be fixed easily.
b. Click Project → Clean and clean the whole workspace or your project.
c. Click Project and verify that Build Automatically is selected so that the project is compiled and rebuilt after the cleaning.
The generated Java source for the Dept entity is shown in Example 6-13. The names for the class and the fields are all taken from the table and column names of the database.
Example 6-13 Generated Dept.java entity
package com.ibm.itso.entities;
import java.io.Serializable;
import javax.persistence.*;
/**
* The persistent class for the DEPT database table.
*
*/
@Entity
public class Dept implements Serializable {
private static final long serialVersionUID = 1L;
@Id
@GeneratedValue(strategy=GenerationType.AUTO)
private String deptno;
private String admrdept;
private String deptname;
private String location;
private String mgrno;
public Dept() {
}
public String getDeptno() {
return this.deptno;
}
public void setDeptno(String deptno) {
this.deptno = deptno;
}
public String getAdmrdept() {
return this.admrdept;
}
public void setAdmrdept(String admrdept) {
this.admrdept = admrdept;
}
public String getDeptname() {
return this.deptname;
}
public void setDeptname(String deptname) {
this.deptname = deptname;
}
public String getLocation() {
return this.location;
}
public void setLocation(String location) {
this.location = location;
}
public String getMgrno() {
return this.mgrno;
}
public void setMgrno(String mgrno) {
this.mgrno = mgrno;
}
If the program had only the Dept class, the program would be ready to run. But the program cannot run because it is a simple Java POJO without a main method. To run the program, you must code a JUnit testdriver. Enable the project to run JUnit tests, as shown in Figure 6-13. Click Build Path → Configure Build Path → Add Library → JUnit and select the JUnit library with the version JUnit4 and add the unit test run time to the class path.
Figure 6-13 Creation of the JUnit test class
Now you are ready to create the test class. Complete the following steps:
1. Right-click the project and select New → Class.
2. For the package name, specify com.ibm.itso.jpa.tests, and for the class name, AllTests.
Example 6-14 Sample test driver class
package com.ibm.itso.jpa.tests;
import static org.junit.Assert.assertEquals;
import java.util.List;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Persistence;
import javax.persistence.TypedQuery;
import org.junit.Before;
import org.junit.Test;
import com.ibm.itso.entities.Dept;
public class AllTests {
protected EntityManagerFactory emf;
protected EntityManager em;
@Before
public void initEmfAndEm() {
emf = Persistence.createEntityManagerFactory("jpa_db2");
em = emf.createEntityManager();
}
@Test
public void getDeptResultListSize() {
TypedQuery<Dept> query1 = em.createQuery("Select d from Dept d",
Dept.class);
List<Dept> results = query1.getResultList();
assertEquals(results.size(), 14);
}
@Test
public void getListOfDepartements() {
TypedQuery<Dept> query1 = em.createQuery("Select d from Dept d",
Dept.class);
List<Dept> results = query1.getResultList();
for (Dept dept : results)
System.out.println(dept.getDeptname());
}
}
Example 6-15 META-INF/persistence.xml update
<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.0" xmlns="http://java.sun.com/xml/ns/persistence"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd">
<persistence-unit name="jpa_db2">
<provider>org.apache.openjpa.persistence.PersistenceProviderImpl</provider>
<class>com.ibm.itso.entities.Dept</class>
<properties>
<property name="openjpa.RuntimeUnenhancedClasses" value="unsupported" />
<property name="openjpa.ConnectionDriverName" value="com.ibm.db2.jcc.DB2Driver" />
<property name="openjpa.jdbc.Schema" value="DSN81010" />
<property name="openjpa.ConnectionProperties" value="username=db2r1,password=pwpwpw" />
<property name="openjpa.ConnectionURL" value="jdbc:db2://d0zg.itso.ibm.com:39000/DB0Z" />
<property name="openjpa.Log" value="DefaultLevel=ERROR" />
</properties>
</persistence-unit>
</persistence>
This action adds connection-specific information properties to the file. You must use your own names.
To start the run, complete the following steps:
1. Right-click AllTests.java in the Package Explorer in the Java Perspective. Click Run AS → JUnit Test.
A new view named JUnit should open. A green or red bar shows you the success or failure of the two tests that are defined in our AllTest.java test driver.
The first test shows a red bar or an exception. The failure has a special reason. Because you run JPA outside of a managed environment, there is something missing called JPA entity enhancement, which is described later in this chapter. This enhancement is normally done by an Java Platform, Enterprise Edition application server automatically. For now, enable it only for your stand-alone environment. The first test run created an entry for the run configuration, which facilitates this action.
2. Click Run → Run Configurations and select JU Alltests.
3. In the Arguments tab, enter -javaagent:C:appsapache-openjpa-2.2.0openjpa-2.2.0.jar with the right path to openjpa-2.2.0.jar for your environment, as shown in Figure 6-14 on page 325.
Figure 6-14 Specify the JPA enhancement javaagent for the unit test
4. Right-click AllTests.java in the Package Explorer again. Click Run AS → JUnit Test.
The JUnit run time now inspects the Alltests class for methods that are annotated with @Test and runs them. Because the DEPT table has 14 rows, the assertEquals(results.size(), 14) statement in the getDeptResultListSize() method succeeds. The second test in the getListOfDepartements() method is not a real test (it has no assert). It prints only the DEPTNAME column of the result set just to show that the objects are created from
the database.
the database.
The overall result of the test is successful, as shown in Figure 6-15.
Figure 6-15 JUnit test success message
In addition, you should see a list of Department Names in the Console window, as shown in Example 6-16.
Example 6-16 JUnit test run console output
SPIFFY COMPUTER SERVICE DIV.
PLANNING
INFORMATION CENTER
DEVELOPMENT CENTER
MANUFACTURING SYSTEMS
ADMINISTRATION SYSTEMS
SUPPORT SERVICES
OPERATIONS
SOFTWARE SUPPORT
BRANCH OFFICE F2
BRANCH OFFICE G2
BRANCH OFFICE H2
BRANCH OFFICE I2
BRANCH OFFICE J2
6.6 JDBC applications in managed environments
A run time is managed if all the resources that your program deals with are defined in the container that encloses the application. Normally, this is an application server such as WebSphere Application Server, but it does not have to be. IMS or CICS also provide a managed infrastructure. Managed environments differ from unmanaged environments in several ways. Generally, in unmanaged environments, you must provide the following items in your application program:
•The loading and configuration of the JDBC-Driver
•The definition of connections
•A security information provision
•Usage of transaction support
Java programs that have this information hardcoded into their classes can run in managed environments because any Java class can use the full capability of the JVM and bypass the server provided functions. This is not a preferred practice, though.
In a managed environment such as WebSphere Application Server, resources such as data sources are predefined in the server environment. They are assigned with a JNDI Name. The database connection in an application program is done by first looking up this JNDI name in the server. The server then gives back a Datasource object that is used by the application or the persistency framework to make the connection. This name (a string) must be coded in the Java program and should be a logical name that is used only inside Java. It should not directly use the JNDI name that is defined in a specific server for the data source, although it works. Instead, it should be a reference to this name that must be mapped at deployment time. This act of association is called binding the resource reference to the
data source.
data source.
For example, the string private String dbName = "java:comp/env/jdbc/sampleRef"; is used to look up the reference jdbc/sampleRef, as declared in web.xml or ebj-jar.xml. java:comp/env/ is an indicator for the server to use a reference and not the real name. The name is valid only inside the Java code and does not need to refer to any existing data sources JNDI name.
Example 6-17 shows a sample declaration of a data source in a web.xml deployment descriptor. This way, the application is coded independently from any information in the server, which ensures portability.
Example 6-17 Resource reference declaration in web.xml
<resource-ref>
<description />
<res-ref-name>jdbc/sampleRef</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
<res-sharing-scope>Shareable</res-sharing-scope>
</resource-ref>
The real data source to be used by this application is declared only at deployment time or, as a special case, in an embedded configuration file. This file, for which you can see an example in Example 6-18, contains the required binding information. Unless it is embedded in the application package, this file is normally generated at deployment time. Its name is ibm-web-bnd.xml or ibm-ejb-jar-bnd.xml and contains the binding-name attribute. It comes from an administrator who defined the server resources.
Example 6-18 Web application bindings file ibm-web-bnd.xml
<?xml version="1.0" encoding="UTF-8"?>
<web-bnd
xmlns="http://websphere.ibm.com/xml/ns/javaee"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://websphere.ibm.com/xml/ns/javaee
http://websphere.ibm.com/xml/ns/javaee/ibm-web-bnd_1_1.xsd"
version="1.1">
<virtual-host name="default_host" />
<resource-ref name="jdbc/sampleRef" binding-name="jdbc/sample" />
</web-bnd>
As of Java EE5, resources can be injected into your program by using Java annotations. The annotation that is used is javax.annotation.Resource. The process of binding references to data sources remains basically the same. Instead of scanning the web.xml file during deployment in search for unresolved references, the server examines the annotations. The reference does not need to be declared in web.xml any more.
The annotation of @Resource(name="jdbc/AccountDB") is equivalent to the traditional java:comp/env/jdbc/AccountDB" lookup and must be mapped at deployment time. In this case, the name is a logical reference.
@Resource(mappedName="jdbc/definedAccountDB") on the other side directly points to the defined resource in the target runtime server without mapping. This non-portable solution works if the resource is defined, but it is not a preferred practice.
6.6.1 Data source connection tests on z/OS
When you define a data source or try to find errors with a data source, connection tests are useful. You test the physical connection and verify that the correct security settings are in place for that data source, as shown in Figure 6-16.
Figure 6-16 Data source connection test
Although this is a well-known feature, there are some implications to using it with WebSphere Application Server on z/OS. While in normal operation the respective servant connects to the database itself, connection tests are sometimes done by other WebSphere Application Server address spaces, depending on the scope that is defined for the data source. The correlation of data source and test connection locality is shown in Table 6-2.
Table 6-2 Correlation of data source scope with the test connection JVM
|
Data source scope
|
JVM where the test connection operation occurs
|
|
Cell
|
Deployment manager.
|
|
Node
|
Node agent process (of the relevant node).
|
|
Cluster
|
Node agent for each node that contains a cluster member.
|
|
Server
|
Server. If the server is unavailable, the test connection operation is tried again in the node agent for the node that contains the application server.
|
|
Consideration: In a network deployment implementation of the application server, you cannot test connections for the following data sources at the node level or cluster level:
•IBM Data Server Driver for JDBC and SQLJ data source with driver type 2
•DB2 Universal JDBC Driver Provider data source with driver type 2
|
The application server issues the following exception for a test connection at the node level:
java.sql.SQLException: Failure in loading T2 native library db2jcct2DSRA0010E: SQL state = null, Error Code = -99,999
Therefore, when you create these data sources at the node scope or cluster scope, you might want to temporarily create the same configurations at a server scope for testing purposes. Run the test connection operation at the server level to determine whether the data source settings are valid for your overall configuration.
6.7 Coding practices for a good DB2 dynamic statement cache hit ratio
Saving prepared statements in a dynamic statement cache can avoid unnecessary preparation processes and thus improve performance. Besides the DB2 dynamic statement cache setting, you should pay attention to SQL program coding, which affects the hit ratio of statement cache.
6.7.1 Eligible SQL statements for caching
SELECT, UPDATE, INSERT, DELETE, and MERGE statements can be saved in the cache.
If JDBC packages are bound with REOPT(ALWAYS), statements cannot be saved in the cache. If JDBC packages are bound with REOPT(ONCE) or REOPT(AUTO), statements can be saved in the cache.
Statements that are sent to an accelerator server cannot be saved in the cache.
6.7.2 SQL comments considerations
There are two types of SQL comments:
•Simple comments: Introduced with two consecutive hyphens (--) and end with the end of
a line.
a line.
•Bracketed comments: Introduced with /* and end with */. A nested bracketed comment means that the comment contains another bracketed comment, for example, /* /* */ */.
The following types of SQL statement text with SQL comments can be saved in the dynamic statement cache:
•SQL statement text with SQL bracketed comments within the text.
•SQL statement text that begins with SQL bracketed comments that are unnested. No single SQL bracketed comment that begins the statement can be greater than 258 bytes.
6.7.3 Conditions for prepared statement reuse
Suppose that S1 and S2 are source statements, and P1 is the prepared version of S1. P1 is in the dynamic statement cache. The following conditions must be met before DB2 can use statement P1 instead of preparing statement S2:
•The authorization ID or role that was used to prepare S1 must be used to prepare S2.
For the conditions that a statement is eligible for reuse, see the following website:
•S1 and S2 must be identical (The exception is literal constant if the PREPARE ATTRIBUTES clause CONCENTRATE STATEMENTS WITH LITERALS is enabled). The statements must pass a character by character comparison and must be the same length. If the PREPARE statement for either statement contains an ATTRIBUTES clause, DB2 concatenates the values in the ATTRIBUTES clause to the statement string before comparing the strings. For example, if A1 is the set of attributes for S1 and A2 is the set of attributes for S2, DB2 compares S1||A1 to S2||A2.
•When the plan or package that contains S2 is bound, the values of these bind options must be the same as when the plan or package that contains S1 was bound:
– CURRENTDATA
– DYNAMICRULES
– ISOLATION
– SQLRULES
– QUALIFIER
– EXTENDEDINDICATOR
•When S2 is prepared, the values of the following special registers must be the same as when S1 was prepared:
– CURRENT DECFLOAT ROUNDING MODE
– CURRENT DEGREE
– CURRENT RULES
– CURRENT PRECISION
– CURRENT REFRESH AGE
– CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION
– CURRENT LOCALE LC_CTYPE
6.7.4 Literal replacement
Before DB2 9, if a dynamic SQL statement is run frequently but the literal constants in it vary, it cannot get the performance benefit of cached statement reuse.
DB2 10 introduces a way for users to get higher cache reuse from dynamic statements that reference literal constants. You can specify the PREPARE ATTRIBUTES clause CONCENTRATE STATEMENTS WITH LITERALS, or set the JDBC driver connection property statementConcentrator=YES to enable it.
If DB2 prepares a SQL statement and CONCENTRATE STATEMENT is enabled, DB2 replaces certain literal constants in the SQL statement text with the ampersand character ('&'), and inserts the modified statement into the dynamic statement cache.
When DB2 runs subsequent dynamic SQL statements, if the first search of the cache does not find an exact match by using the original statement text, DB2 substitutes the ampersand character ('&') for literal constants in the SQL statement text and searches the cache again to find a matching cached statement that also has '&' substituted for the literal constants. If that statement text comparison is successful, DB2 determines whether the literal reusability criteria between the two statements allows for the new statement to share the
cached statement.
cached statement.
The reusability criteria includes, but is not limited to, the immediate usage context, the literal data type, and the data type size of both the new literal instance and the cached literal instance. If DB2 determines that the statement with the new literal instance cannot share the cached statement because of incompatible literal reusability criteria, DB2 inserts, into the cache, a new statement that has both '&' substitution and a different set of literal reusability criteria. This new statement is different from the cached statement, even though both statements have the same statement text with ampersand characters ('&'). Now, both statements are in the cache, but each has different literal reusability criteria that makes these two cached statements unique.
Here is an example:
Assume that DB2 prepares the following SQL where column X is data type decimal:
SELECT X, Y, Z FROM TABLE1 WHERE X < 123 (no cache match)
After the literals are replaced with '&', the cached statement is as follows:
SELECT X, Y, Z FROM TABLE1 WHERE X < & (+ lit 123 reuse info)
Assume that the following new instance of that statement is now being prepared:
SELECT X, Y, Z FROM TABLE1 WHERE X < 1E2
According to the literal reusability criteria, the literal value 1E2 does not match the literal data type reusability of the cached statement. Therefore, DB2 does a full cache prepare for this SELECT statement with literal 1E2 and inserts another instance of this '&' SELECT statement into the cache as follows:
SELECT X, Y, Z FROM TABLE1 WHERE X < & (+ lit 1E2 reuse info)
Now, given the two '&' SELECT statements that are cached, attempt to prepare the same SELECT statement again but with a different literal value instance from the first two cases:
SELECT X, Y, Z FROM TABLE1 WHERE X < 9
DB2 fails to find an exact match for the new SELECT statement with literal '9', replaces literal '9' in the SELECT statement with '&', and does a second search. Both of the two cached statements are reusable with literal value '9', therefore, simply by order of statement insertion into the cache, cached statement for literal 123 is the first cached statement found that satisfies the literal reusability criteria for the new literal value '9'.
6.8 Locking
Here are the factors that influence locking:
6.8.1 Isolation level
WebSphere and DB2 naming conventions for the isolation level do not explicitly map. The translation between the two levels is listed in Table 6-1 on page 309.
The isolation level settings are listed below in order from most to least restrictive. In combination with the executed SQL, these modes determine the lock mode and duration of the locks that are acquired for the transaction.
•TRANSACTION_SERIALIZABLE (Repeatable Read) acquires locks on all rows read by an SQL statement whether they qualify for the result set or not. The locks are held until the transaction is ended through a commit or rollback. Other transactions cannot insert, delete, or update rows that are accessed by an SQL statement executing with RR.
•TRANSACTION_REPEATABLE_READ (Read Stability) acquires locks on all stage 1 qualifying rows and maintains those locks until the application issues a commit or rollback. With RS, other transactions cannot update or delete rows that qualified (during stage 1 processing) for the statement because locks are held. If the application attempts to re-reference the same data later in the transaction, the results will not have been updated or deleted. However, other applications can insert more rows, which is known as a phantom read because subsequent selects against the same data within the same transaction might result in extra rows being returned.
•TRANSACTION_READ_COMMITTED (Cursor Stability) ensures that all data that is returned is committed. When SELECTing from the table, locks are not held for rows or pages for which a cursor is not positioned. DB2 tries to avoid taking locks on non-qualifying rows. If an application attempts to re-reference the same data later in the transaction, there is no guarantee that data has not been updated, inserted, or deleted.
•TRANSACTION_READ_UNCOMMITTED (Uncommitted Read) means that locks are not acquired for queries (SELECT), and the application may return data from another transaction that has not yet been committed or rolled back.
6.8.2 Lock avoidance
Locking carries a cost both for concurrency and processing. Provided certain conditions are met, DB2 can avoid requesting a Read or Share lock on behalf of the application process. This function, which applies only to low-level locks, is referred to as lock avoidance.
Prerequisite of lock avoidance
Users have no direct control over the usage of lock avoidance. Lock avoidance occurs in the following situations:
•There is a read-only or ambiguous cursor with ISOLATION(CS) and CURRENTDATA(NO).
•For any nonqualifying rows that are accessed by queries that are bound with ISOLATION CS or RS.
•When DB2 system managed referential integrity (RI) checks for dependent rows when either the parent key is updated or if the parent key is deleted and the DELETE RESTRICT option is defined.
DB2 supports three types of cursors:
•Read-only cursors
•Updatable cursors
•Ambiguous cursors
If a cursor is defined with the clauses FOR FETCH ONLY or FOR READ ONLY, it is a read-only cursor. If a cursor is defined with the clause FOR UPDATE OF, it is an updatable cursor. A cursor is considered ambiguous if DB2 cannot tell whether it is used for update or read-only purposes. For more information about these three types of cursors, see DB2 9 for z/OS: Resource Serialization and Concurrency Control, SG24-4725.
In a JDBC application, the declaration and processing of a cursor occurs with a different syntax, but the concept is basically the same. Instead of processing a cursor, a PreparedStatement is created and a ResultSet is used to process the results. Example 6-19 shows an updatable cursor, which is a cursor that is not eligible for lock avoidance.
Example 6-19 Updatable cursor in a JDBC application
PreparedStatement p1 = con.prepareStatement("SELECT ACTKWD, ACTDESC FROM " +
"DSN8A10.ACT WHERE ACTNO = 180",ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_UPDATABLE);
ResultSet rs1 = p1.executeQuery();
String s1 = null;
String s2 = null;
while (rs1.next())
{
s1 = rs1.getString(1);
s2 = rs1.getString(2);
if (s1.compareTo("DOC ") == 0)
{
rs1.updateString(2,"Make A Document");
rs1.updateRow();
}
System.out.println("Active Description Is "+rs1.getString(2));
}
Lock avoidance control
The following options can be specified when you bind a JDBC driver:
•CURRENTDATA: With CURRENTDATA(NO), DB2 uses lock avoidance techniques to access the data. Lock avoidance is not considered for qualifying rows if the application is bound
with CURRENTDATA(YES).
with CURRENTDATA(YES).
•ISOLATION: Cursor Stability (CS) increases the concurrency and also the possibility of
lock avoidance.
lock avoidance.
If you use a read-only result set with CURRENTDATA(NO), the stability of the qualifying rows is not protected by the lock. When the row qualifies under the protection of a data page latch, the row is passed to the application, and the latch is released. Therefore, the content of the qualified row might have changed immediately after it was passed to the application. To continue processing further rows in a page, DB2 must latch the page again.
Impact on block fetch and parallelism
Table 6-3 summarizes the impact of the CURRENTDATA option for parallelism and block fetch for distributed applications.
Table 6-3 Impact of CURRENTDATA option
|
Current data required
|
Ambiguous cursor
|
Read-only cursor
|
|
YES
|
Lock avoidance is not considered for ISOLATION(CS) applications.
I/O and CP parallelism are not allowed.
Block fetching does not apply.
|
Lock avoidance is not considered for ISOLATION(CS) applications.
I/O and CP parallelism are allowed.
Block fetching applies.
|
|
NO
|
Lock avoidance is considered for ISOLATION(CS) applications.
I/O and CP parallelism are allowed.
Block fetching applies for distributed applications.
|
|
If your business logic allows, use the CONCUR_READ_ONLY result set (this is the JDBC equivalent for the DB2 'FOR READ ONLY' clause) if there is no update that is intended, along with ISOLATION(CS) and CURRENTDATA(NO).
6.8.3 Optimistic locking
Lock avoidance can improve concurrency and reduce processor consumption, but applications with positioned update intention are not eligible for it. For example, if the data is read from the tables and presented to the users before the update, to make sure that there is data integrity, the lock should be held from read to commit. In Example 6-20, a thread wants to read a record and then update it. If DB2 releases the lock after the SELECT, other threads may modify this record, and then the update might fail to make the changes to the
specified row.
specified row.
Example 6-20 Read a record and then update
SELECT ACTDESC INTO :desc FROM DSN81010.ACT WHERE ACTKWD = 'DOC';
-- Other processing
UPDATE DSN81010.ACT SET ACTDESC = 'MAKE DOCUMENT' WHERE AND ACTKWD = 'DOC';
COMMIT;
To ensure data integrity and reduce locking, you can use optimistic concurrency control.
When an application uses optimistic concurrency control, locks are obtained immediately before the read operation and released immediately after the read. The update locks are obtained immediately before an update operation and held until the end of the process. It minimizes the time for which a resource is unavailable for use by other transactions. Optimistic concurrency control uses the RID and a row change token to test whether data was changed by another transaction since the last read operation, so it can ensure data integrity while limiting the time that locks are held.
Eligible applications for optimistic locking
If an application uses optimistic concurrency control but resource contentions happen frequently, then the update fails, and you must reprocess the failed record. Reprocessing hurts overall performance compared to the performance savings achieved by avoiding
the locks.
the locks.
In general, optimistic concurrency control is appropriate for application processes that do not have concurrent updates on the same resource, such as information only (read-only) web applications, single user applications, or pseudo-conversational OLTP applications, where the data is read from the tables and presented to the users before performing the updates. Optimistic concurrency control is also appropriate for applications accessing tables that are defined with page level locking or higher level lock size when the concurrently running processes are accessing different sets of data.
Using ROW CHANGE TIMESTAMP
To implement optimistic concurrency control, you can establish a row change time stamp column with a CREATE TABLE statement or an ALTER TABLE statement. The column must be defined with one of the following null characteristics:
•NOT NULL GENERATED ALWAYS FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP
•NOT NULL GENERATED BY DEFAULT FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP
After you establish a row change time stamp column, DB2 maintains the contents of this column. When you want to use this change time stamp as a condition when making an update, you can specify an appropriate predicate for this column in a WHERE clause, as shown in Example 6-21.
Example 6-21 Implement optimistic concurrency control by using ROW CHANGE TIMESTAMP
ALTER TABLE DSN81010.ACT ADD COLUMN RCT
NOT NULL GENERATED ALWAYS FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP;
--REORG TABLESPACE
SELECT ACTDESC, ROW CHANGE TIMESTAMP FOR ACT INTO :desc, :rct FROM DSN81010.ACT WHERE ACTKWD = 'DOC';
-- Other processing
UPDATE DSN81010.ACT SET ACTDESC = 'MAKE DOCUMENT'
WHERE ROW CHANGE TIMESTAMP FOR ACT = :rct AND ACTKWD = 'DOC';
-- Other processing
COMMIT;
In this example, DB2 completes the following steps:
1. Acquires a minimal lock before the read to ensure data integrity. The best option is to read with ISOLATION(CS) and CURRENTDATA(NO) to get lock avoidance without sacrificing
data integrity.
data integrity.
2. Selects ROW CHANGE TIMESTAMP along with other pertinent information from the table by employing lock avoidance techniques.
3. Releases the lock immediately after the read or employ lock avoidance techniques by using the ISOLATION (CS) with CURRENTDATA (NO) bind options.
4. Saves the data, particularly the ROW CHANGE TIMESTAMP, for future comparison.
5. Acquires the exclusive locks immediately before the update and holds on to the update until the process ends or commits.
6. During the update, checks whether the data read was changed by another process since it was last read, by comparing the current row change time stamp with that of the
saved values.
saved values.
7. The update succeeds only when it is verified that the ROW CHANGE TIMESTAMP values match the saved ones; otherwise (in case another process changed the value), the update fails with a return code of +100 (row not found for update).
8. The application must reprocess the failed record, if needed.
|
Note: You can use ROW CHANGE TOKEN instead of ROW CHANGE TIMESTAMP in SQL. It takes the last 8 bytes of the DB2 time stamp and returns it as BIGINT.
|
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.