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 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.