DB2 Java Application Performance: Monitoring and SQL Improvements

As your Java workload continues to grow, your application monitoring and SQL performance needs to be improved within the system and application coding.  The following techniques will help you monitor, improve and streamline your DB2 Java application performance.

First, know your application connections and threads.  When doing performance reviews of a client’s DB2 applications, it’s sometimes impossible to tell the different Java application transactions apart.  With many threads coming from multiple intermediate servers there are no distinctions between the oldest application and the newest application.  Both applications are using the web server and performing dynamic SQL against the database information.

One of the best ways to identify and separate the application transactions is to use a separate data sources within the web server for the different applications.  Using a different data source provides the ability to set the proper number of reusable threads for the application.  When you have different data sources you can then further identify your new application by using the four JDBC Java Methods (setClientUser, setClientApplicationInformation, setClientWorkStation, and setClientAccountingInformation) to set unique values for your different dynamic transactions. This enables you to research your DB2 Java Application performance.

By using and setting these different JDBC Methods you can set your application up to have extra settings to isolate your monitoring information quickly and understand which processes are causing application performance issues.  By assigning different values to different portions of your application you can quickly understand where your SQL and your processing flow are executing.  Also, this information can help you identify your processing through DB2 Connect traces and thread displays while researching your DB2 Java application performance.

Second, limit the number of rows returned. One the best ways to improve your DB2 Java application performance is to monitor your process and make sure they are only returning the minimum number of rows desired.  This can be done within the Java DB2 application code by using the FETCH FIRST ## ROWS ONLY.  This SQL FETCH FIRST ## ROWS ONLY phrase within your SQL application prevents your Java application from trying to return thousands of rows to your web page application.  Returning large numbers of rows can have a huge negative performance impact on your DB2 application, so protect your DB2 system performance with FETCH FIRST ##ROWS ONLY.

Also another way to prevent a large SQL result set is by using and setting the JDBC setMaxRows method on your SQL statement.  If your application is potentially returning millions of rows through an SQL statement to produce reports or develop analytics, another way to limit the answer result set is to use setMaxRows.

Connection myConn = dbmsDB2.getConnection();
Statement myStmt = myConn.createStatement();
String sqlStmt = “SELECT PID, NAME, PRICE, PROMOPRICE FROM PRODUCT”;
myStmt.setMaxRows(200);
ResultSet rsProduct = myStmt.executeQuery(sqlStmt);

Finally, another way to improve your DB2 Java application performance is to make sure that your Java coding best practices recommend the use of SQL parameter markers within your application.  Using DB2 SQL parameter markers helps your DB2 Java application performance in several ways.  One way it helps your DB2 performance is by improving the DB2 dynamic SQL package cache access method reuse.  By using DB2 SQL parameters instead of literal values the DB2 system is able to match SQL more efficiently within the cache and reuse the DB2 package access path previously develop for the application.

By reusing the DB2 SQL package cache the DB2 Java application performance can be improved tremendously because reusing an access method is only about 10,000 instructions, while determining a new simple SQL access path can be about 150,000 instructions and to develop a new access path for a complex SQL statement can be even be 10 times more.

By using these techniques it is much easier to analyze and improve your Java workloads so they run more efficiently with less system overhead.
______________________________________________________

Dave Beulke is an internationally recognized DB2 consultant, DB2 trainer and education instructor.  Dave helps his clients improve their strategic direction, dramatically improve DB2 performance and reduce their CPU demand saving millions in their systems, databases and application areas within their mainframe, UNIX and Windows environments.

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>