By using best practices to help maximize the efficiency
of Java™ Database Connectivity
(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 right 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.