40 DB2 for z/OS and OS/390 Version 7 Selected Performance Topics
4.4 Guidelines for high performance when using JDBC/SQLJ
These guidelines can be divided into three groups:
򐂰 Designing the application
򐂰 Identifying the required system levels
򐂰 Environment tuning
4.4.1 Design guidelines for applications
In this section we list the recommendations about coding and preparation of SQLJ and JDBC
program.
Map Java data types to DB2 data types
For optimal performance, we recommend that you map the Java data types used to the SQL
column data types. The primary reason for this is to provide for efficient predicate processing:
indexable and Stage 1. The other reason is to minimize data conversion cost.
Table 4-2 provides the recommended mapping between Java data types and SQL column
data types.
PQ45186 JDBC 2.0 driver
Support for Datasource function
Support for global transactions
Performance enhancement for Java 2 with persistent reusable JVM
PQ48383 Significant performance enhancement dealing with ResultSet and iterator data retrieval. Rows from a
SELECT result set are now being returned to the driver using the byte array approach.
More performance enhancements for Java 2 with persistent reusable JVM:
򐂰 Caching of SQLJ profiles for the life of the driver
򐂰 Addition of a ibmJVMTidyUp() method to our driver
򐂰 Added use of isResettableJVM() method
򐂰 Deep-copy of user provided objects to avoid trace reset events
PQ54756 Performance enhancement of setxxx() methods using the byte array approach.
Performance enhancement processing change for input String data.
General performance and storage usage improvements due to changes in the driver to:
򐂰 Avoid unnecessary creation of Objects.
򐂰 Release Java Objects for garbage collection when SQLJ/JDBC Objects are closed, rather than
waiting for finalization.
Added new diagnostics for determining which SQLJ/JDBC driver build level is being used.
APAR Enhancements
Chapter 4. Java support 41
Table 4-2 Mapping DB2 data types to Java data types
The JDBC/SQLJ driver uses getxxx() methods to retrieve the value of a column from the
database. JDBC API defines that each getxxx() method returns a matching Java object. For
example getString() returns a String object. The processing cost of each getxxx() method is
mainly determined by the cost of the object constructor call. Returning values of Java native
data type like Integer is much cheaper than returning complex objects like a Timestamp
object.
Figure 4-6 shows the relative cost of all getxxx() methods compared to getShort(). Retrieving
a Date column is about 21 times more expensive than retrieving a short column. Based on
this information the database can be designed for high performance.
.
Figure 4-6 Relative cost of getxxx() methods
DB2 data type Java data type Comment
SMALLINT short, boolean No direct mapping for bit in DB2
INTEGER int
REAL float Single precision
DOUBLE, FLOAT double Double precision
DECIMAL(p, s) or
NUMERIC(p, s)
java.math.BigDecimal with p=precision, s=scale
keep scale and precision in Java
CHAR, VARCHAR,
GRAPHIC,
VARGRAPHIC
String
CHAR, VARCHAR FOR
BIT DATA
Byte[]
DATE java.sql.Date
TIME java.sql.Time
TIMESTAMP java.sql.Timestamp
getShort()
getInt()
getFloat()
getDouble()
getBoolean()
getDate()
getTime()
getTimeStamp(
getBigDecimal
SBCS
getString() cha
getString() var
0
5
10
15
20
25
Times
Relative cost of getxxx() processing

Get DB2 for z/OS and OS/390 Version 7 Selected Performance Topics now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.