Since Java is the new main development language for most companies it is vital that all the programmers know the best DB2 Java performance properties and considerations. During system and application Java performance reviews there are many factors that come up that impact performance. Below are several Java DB2 SQL performance factors that can have a major impact on the amount of CPU utilized and the response time of your application.
First, using the latest DB2 JDBC driver is vital for your Java applications, their overall performance and their flexibility for using the latest features. As of this writing, IBM is recommending DB2 Connection Version 10.5 Fix Pack 5 for leveraging the latest DB2 Version 11 for z/OS connections. This version provides many settings that are only available in the latest edition of DB2 V11 for z/OS and within DB2 Connect. This recommendation level progresses over time to the latest DB2 Connect version, so check the IBM DB2 Connect websites for DB2 LUW and for DB2 z/OS to get the latest information.
It is vital to know your shop’s default settings within its various JDBC settings and connection properties. These industry, company, and sometimes, unfortunately, application JDBC driver default settings can have a huge impact on application CPU performance and application integrity.
For example the Java Connection class autoCommit property setting is vital to the process’s behavior. If the autoCommit property is “true,” then after every SQL statement, your process will perform an implicit database commit. The commit will cause extensive CPU to be used to synchronize all the database resources plus cause a commit point within the application service architecture. Know the autoCommit setting and control it within your architecture, system and application. The best start is to turn autoCommit off.
Example: conn.setAutoCommit(false);
Next, controlling the DB2 Java commit scope is vital because of the database integrity and performance issues, and also because of its impact on the various the SQL statements within the process. In most Java processes, a Commit within the process with the default JDBC settings causes all the prepared SQL statements and any open result sets to be implicitly closed. Closing result sets and open cursors unexpectedly can cause reissuing of the DB2 SQL and re-accessing of all the resources unnecessarily.
Two DB2 Java JDBC driver properties within Java JDBC, setResultHoldability and queryCloseImplicit, hold the key as to whether an SQL result set cursor is closed by default or automatically. Depending on whether the defaults are used or your shop has preset these Java JDBC properties, the cursor could be closed once the last result set row is retrieved, or when an implicit or explicit commit is done within your application process.
The JDBC setResultHoldability property can be set to “Hold the Cursor Result Set Open” or not. The setResultHoldability property options to stay open or not can be set at three different levels: the DB2 Java connection, the database, and the SQL statement result set. By using any of three levels and the JDBC setResultHoldability property options below, adjusting the setting is very easily controlled.
Statement.getResultSetHoldability()
DatabaseMetaData.getResultSetHoldability()
Connection.getHoldability()
It is a huge performance impact to reestablish a result set cursor so make sure your application controls the JDBC setResultHoldability property appropriately. It can also be a critical factor for database transactions’ integrity so make sure to understand your shop’s default and application specific settings. The recommendation is to control your Java process commit scope and use JDBC setResultHoldability=2 property setting to automatically close result cursors at your process’s commit points.
Using the JDBC queryCloseImplicit property also will, as the property specifies, close the cursor implicitly when all the result set rows are retrieved. Use the queryCloseImplicit(1) setting and the cursor will automatically be closed, and all the database resources freed.
There are many other Java DB2 JDBC settings that need to be set to get the optimum Java application performance for your unique transactions and Java services. Check the software level of your Java DB2 JBC interface, its default property settings and make sure to check these vital JDBC performance properties. By doing these checks ahead of experiencing performance issues, you can make sure your Java DB2 SQL performance is the best it can be for your overall system, database, and application.
Here’s a list with links to other posts that may help you improve your DB2 performance, security, and ease-of-use:
5 More DB2 SQL Performance Tips
Another 5 More DB2 SQL Performance Tips
5 Big Data SQL Performance Tips – Fixing Generated SQL
DB2 11 SQL Performance Improvements
5 More SQL Performance Tips for your Big Data
Hadoop SQL: 4 Reasons Why BigInsights Is the Best
Vital Java DB2 SQL Performance Considerations
More DB2 Family Security Best Practices Part 7: Preventing SQL Injection
DB2 SQL Security Audits
Sign up for the IDUG DB2 Technical Conference Philadelphia, Pennsylvania this May 4-8th.
- Also plan on attending my presentation 2378 “SQL Performance for a Big Data 22 Billion Row Data Warehouse,” Friday 8:00-9:00 at the IDUG conference.
For more details on any of these items go to www.idug.org.
Dave Beulke is a system strategist, application architect, and performance expert specializing in Big Data, data warehouses, and high performance internet business solutions. He is an IBM Gold Consultant, Information Champion, President of DAMA-NCR, former President of International DB2 User Group, and frequent speaker at national and international conferences. His architectures, designs, and performance tuning techniques help organization better leverage their information assets, saving millions in processing costs.
Leave a Reply