JDBC application development tips

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

Most of the following recommendations assume that you 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, you 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, you 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, you are better off writing specific calls that retrieve just what you want rather than generic calls that retrieve the entire row. There is a high per-field cost.