Design Guidelines for Applications | DB2(R) Universal Database for OS/390 V7.1 Application Certification Guide (IBM DB2 Certification Guide Series)


Team-Fly

	DB2 Universal Database for OS/390 v7.1 Application Certification Guide By Susan Lawson
	Table of Contents

	Chapter 13. Java Programming

Design Guidelines for Applications

There are recommendations for designing applications for best performance when using JDBC/SQLJ. Some of them have been described in the IBM Redpaper "DB2 for z/OS and OS/390 V7 Selected Performance Topics"REDP0162 and are summarized below.

Data Types

There are a few performance gains that can be realized when developing Java applications in regards to data types. While this is also a database design issue, it is important to recognize these options.

Mapping

Java data types need to be mapped to the SQL column data types to achieve best performance. This can help with ensuring the predicates are Stage 1 and indexable and so that the cost of conversion can be minimized. See Table13-2.

Table 13-2. DB2 and Java Data Type Comparision

DB2 Data Type	Java Data Type
SMALLINT	short, Boolean
INTEGER	int
REAL	float
DOUBLE, FLOAT	double
DECIMAL (p,s) or NUMERIC (p,s)	java.math.BigDecimal
CHAR, VARCHAR, GRAPHIC, VARGRAPHIC	String
CHAR, VARCHAR, FOR BIT DATA	Byte[ ]
DATE	java.sql.Date
TIME	java.sql.Time
TIMESTAMP	java.sql.Timestamp

Use of INTEGER and SMALLINT

Storing numbers as INT or SMALLINT when applicable will save on conversion overhead between EBCDIC/ASCII and Unicode. Java runs Unicode and requires that character(CHAR, VARCHAR) data be converted. Numbers do not have to be converted, as they are not dependent on an encoding scheme.

Use of CHAR and VARCHAR

Java does not pad, and this means that trailing blanks are considered as characters . This can be a problem with string columns in DB2 because they are padded with blanks and therefore may not match the Java at predicate evaluation time.

This possibility of a mismatch forces the programmer to address it. There are three options available to address this issue:

Use a Java trim() method to get rid of the trailing blanks so the columns will match. This can make for complex programming and can increase the Java CPU costs. Define the columns as VARCHARs. This will of course incur the DB2 overhead associated with using them, but does eliminate the complex Java code.
Use the RTRIM option on SQL when retrieving the columns. This options reduces the complex Java code without incurring the DB2 overhead associated with VARCHARs.

Programming Guidelines

There are some general guidelines for best practices for developing Java programs. Most of these guidelines center around the performance aspects of using Java.

Autocommit

Autocommit is the default, but it is recommended to execute commits as necessary and turn autocommit off. With autocommit on, a lot of cost is added to the program execution because a commit is taken after each SQL statement. This will occur even in a read-only environment due to the releasing of locks and registrations. Autocommit can be turned off by calling the setAutoCommit method of the Connection object.

 Connection connection = dataSource.getConnection("", "");  connection.setAutoCommit(false);

NOTE

Autocommit can also be turned off when the connection is established; refer to the previous section, "Connecting to a Data Source."

Conservative Selection

The recommendation for selection of DB2 data has always been to only select what data is actually required by the application. Java programming is no exception and actually has a more critical performance impact. Every column retrieved is returned as a Java object.

Explicit Connection Context Objects

A default connection context object is used when the connection context object is not specified. The use of a default connection context is not thread-safe and cannot be used in a multicontext environment such as WebSphere Application Server. The use of the default connect context can also cause application execution bottlenecks. You can also specify the constant KEEP_CONNECTION to indicate that the underlying database connection should be retained when the context is closed.

JDBC and SQLJ Connection Pooling

Connection pooling is part of JDBC 2.0 data source support. Connection pooling is a framework for caching physical data source connections, which are equivalent to DB2 threads. When JDBC reuses physical data source connections, the expensive operations that are required for the creation and subsequent closing of java.sql.Connection objects are minimized. Connection pooling is a built-in part of JDBC 2.0 data source support. Connection pooling support is completely transparent to the JDBC application.

Without connection pooling, each java.sql.Connection object represents a physical connection to the database. When the application establishes a connection to a data source, DB2 creates a new physical connection to the data source. When the application calls the java.sql.Connection.close method, DB2 terminates the physical connection to the data source. In contrast, with connection pooling, a java.sql.Connection object is a temporary, logical representation of the physical data source connection.

The JDBC 2.0 connection pooling framework lets a single physical data source connection be serially reused by logical java.sql.Connection instances. The application can use the logical java.sql.Connection object in exactly the same manner as it uses a java.sql.Connection object when there is no connection pooling support.

With connection pooling, when a JDBC application invokes the DataSource.getConnection method, the data source determines whether an appropriate physical connection exists. If an appropriate physical connection exists, the data source returns a java.sql.Connection instance to the application. When the JDBC application invokes the java.sql.Connection.close method, JDBC does not close the physical data source connection. Instead, JDBC closes only JDBC resources, such as Statement or ResultSet objects. The data source returns the physical connection to the connection pool for reuse.

Connection Pooling within CICS and WebSphere

Currently CICS does not support connection pooling. It is highly recommended when programming for WebSphere that DataSource connection pooling be used. When a user makes a request to the application server that requires interaction with the database, a connection to the database must be made. Connection pooling can reduce the overhead of creating a new connection to the database, because the DataSource might determine it has a free connection in its pool that it can give to the request. When the connection is no longer needed for the request, the database connection will be closed. This signals to the datasource that it can return the connection to its pool for reuse. This also has the benefit of reducing the cost of releasing the connection.

WebSphere connection pooling also has the advantage of caching prepared statements. The application server maintains a cache of previously prepared statements that it makes available to a connection. When a prepared statement is requested , the cached prepared statement is returned. If the statement is not already in the cache, it will be added. The number of statements to cache is a configurable setting. This type of caching reduces the cost of creating prepared statements, which in turn improves response times. Below is an example of using connection pooling with a prepared statement. Note that the context factory for WebSphere 3.5 is com.ibm.ejs.ns.jndi.CNInitialContextFactory and for 4.0 is com.ibm.websphere.naming.WsnInitialContextFactory. Also, the JNDI lookup string is "jdbc/MyDB" for 3.5 and "java:comp/env/jdbc/MyDB" for 4.0. Both lookups will work under version 4.0, but the 4.0 syntax is J2EE 1.2-compliant.

 // Lookup the DataSource  Hashtable env = new Hashtable(); env.put(Context.INITIAL_CONTEXT_FACTORY,         "com.ibm.ejs.ns.jndi.CNInitialContextFactory"); // Create the Initial Naming Context. Context ctx = new InitialContext(env); // Use the naming service to retrieve the DataSource DataSource ds = (DataSource) ctx.lookup("jdbc/MyDB"); // Obtain a connection from the DataSource Connection conn = ds.getConnection("", ""); // Prepare a callable statement CallableStatement cState =             conn.prepareCall("{call myStoredProcedure(?)}"); cState.registerOutParameter(1, java.sql.Types.INTEGER); // Execute the callable statement cState.execute(); // Retrieve the results int newId = cState.getInt(1); // Close the callable statement cState.close(); cState = null; // Close the database connection conn.close(); conn = null; ds = null;

Sometimes the connections in the pool can become stale and need to be refreshed. Connections can become stale for the following reasons:

The application server starts before the database.
The database goes down while the application server is running.
The application closes the connection and then tries to perform a task on the closed connection.
The connection has been orphaned because it was not used by the application within the orphan timeout period.

WebSphere provides an application with the ability to recover from a stale connection with the StaleConnectionException class. Version 3.5 of WebSphere uses the class com.ibm.ejs.cm.portability.StaleConnectionException and Version 4.0 uses com.ibm.websphere.ce.cm.StaleConnectionException.

It is not required of an application to catch the StaleConnectionException, because this class extends SQLException, which is required to be caught or thrown by applications. Catching this exception allows an application to recover from a stale connection by retrying to retrieve enough connections to refresh the pool. There should be a limit set on how many times an application tries to retrieve a connection, because there is CPU cost involved with this type of approach.

Resource Release

Resource links are maintained by the JDBC driver and released only when the connection is lost or the resource is closed. Table 13-3 lists objects that should be explicitly released by an application rather than waiting for the Java garbage collector to release them.

Table 13-3. Objects to be Released by an Application

Object	Description
Connection	A database connection object.
CallableStatement	Object used to execute stored procedures.
PreparedStatement	Object used to execute precompiled SQL.
ResultSet	Object used to represent a database result set.
Statement	Object used for executing static SQL.

To reduce resource costs, the following guidelines should be followed:

Never close connection in a finalize() method. A connection closed in the finalize() method will not be released until the object is garbage collected. This can result in the application running out of database connections.
Do not declare Connection objects as servlet instance variables , because all instances of the servlet will use the same Connection object. This is an issue, since a servlet is multithreaded and can be used to handle multiple client requests .
The data source should be obtained in the init() method of a servlet, because JNDI context lookups are expensive and the data source is nothing more than a factory of connections that does not change.

Db2profc

Java has no concept of length for string data types such as CHAR and VARCHAR. In order to use index matching for the SQL predicates, the definition in the DBRM for SQLJ must match the definition (length and data type) in the DB2 catalog. To do this, the SQLJ serialized profile must be customized using db2proc with the online checker option. It needs to be run on the same platform where the code will be executed.

 Db2proc ... -online=<db2_location_name ...


Team-Fly

Top