(ZOS) JDBC application development tips

By using best practices to help maximize the efficiency of JDBC queries, we can potentially increase application performance.

Most of the following recommendations assume that we use DB2 on z/OS .

• Program according to the most current JDBC specifications.

• Use prepared statements to allow dynamic statement cache of DB2 on z/OS.
• Do not include literals in the prepared statements; use a parameter marker "?" to allow dynamic statement cache of DB2 on z/OS.

• Use the correct getXxx method by each data type of DB2.
• Turn auto commit off when just read-only operations are performed.

• Use explicit connection context objects.

• When coding an iterator, we have a choice of named or positioned. Positioned iterators have the better performance potential.
• Close prepared statements before reusing the statement handle to prepare a different SQL statement within the same connection.
• As a bean developer, we have the choice of using JDBC or Structured Query language in Java (SQLJ) queries. JDBC makes use of dynamic SQL whereas SQLJ generally is static and uses pre-prepared plans. SQLJ requires an extra step to create and bind the plan whereas JDBC does not. SQLJ, as a general rule, is faster than JDBC.

• With JDBC and SQLJ, we are better off writing specific calls that retrieve just what we want rather than generic calls that retrieve the entire row. There is a high per-field cost.

Developing a Java EE client application

Enterprise bean development best practices

JDBC application cursor holdability support