5 More DB2 SQL Performance Tips

Writing good DB2 SQL is always difficult because it is dependent on the database table design and the various table indexes available coming together with the purpose of the processing module,  There are many DB2 SQL performance tips to remember and several are documented in my DB2 SQL performance tips blogs here, here, and here documenting various coding considerations and SQL syntax options to evaluate to optimize your DB2 SQL performance.

Even though in these previous blogs I documented twenty different DB2 SQL performance tips below are five more DB2 SQL performance tips.

1. SELECT from INSERT, UPDATE whenever possible.  This feature is great for getting the values associated with identity, sequence, and any other database column keys while doing an INSERT or UPDATE SQL operation.
   
   This capability provides for the capturing of generated key values needed to be returned to the application during an UPDATE or INSERT statement.  Instead of executing two SQL statements a task can be handled with a one SQL call and have all the application data to continue processing.
   
   
2. Use MERGE to reference the database and make sure the data is there and updated.  Avoid complex program logic to perform the data checking and then update.The DB2 SQL MERGE statement improves application performance by automatically checking to see if the data entry exists. If the data exists, DB2 performs an update; if not, DB2 inserts the data.  This MERGE SQL statement allows DB2 to put the data into a table in a single SQL statement instead of first doing a SQL SELECT and then the appropriate INSERT or UPDATE SQL statement.
   
   The MERGE SQL statement operation can also work with multiple row operations using an input array.  When multiple input rows are used, the phrase NOT ATOMIC CONTINUE ON SQL EXCEPTION can be specified. This phrase allows DB2 to process each row independently. If an error occurs when merging a row, only the error row is backed out and processing continues.  DB2 will take each of the multiple input rows, then determine the correct insert or update operation to integrate it into the table. When there are triggers over the table, each successfully merged row fires the trigger and the appropriate actions are taken. Error rows encountered during the MERGE process don’t fire triggers.
   
   Applications can use the new MERGE SQL statement in a SELECT FROM MERGE statement. With only one SQL statement, DB2 can insert or update multiple rows in the table and return the values back to the application. This capability reduces application complexity and helps eliminate potentially millions of SQL calls in nightly batch processing cycles improving DB2 SQL performance dramatically.
   
   
3. Create indexes on all WHERE clause expressions that are not already index-able.  Companies have unique database data type definitions, processing situations and evolving enhancements.  Create indexes on expressions that are used in your daily SQL processing.
   
   Examine your application’s EXPLAIN output and examine any SQL statement that is not doing an index access.  Then dig deeper to find the unique SQL WHERE clause for those statements.  If there are common scalar, functions, DATE, or other common clauses, create an index on the common WHERE expression.  The new index on expression can improve DB2 SQL performance dramatically and make SQL processing easy, so it is worth investigating all the index on expression limitations.
   
   
4. Use Common Table Expression (CTEs) whenever possible because these expressions can get the data so that the result set can be used repeatedly and efficiently.  CTEs are great because from the single result set they can provide input to multiple different answer sets, grouping sets or a different ordering of the data to be done within a single execution pass of the SQL statement.
   
   CTEs are also good for recursive SQL performance because, again, a single pass of the data can operate multiple times against the result set improving DB2 SQL performance dramatically.
   
   Also, another reason CTEs are good is because sometimes they offer an alternative to creating a DB2 view.  Through a CTE, SQL performance can be maintained while avoiding the overhead and maintenance of a DB2 view to again improve DB2 SQL performance.
   
   
5. Use DB2 TEMP tables for all data that is referenced more than three times in the flow of daily business.  It is more efficient to build the DECLARE GLOBAL TEMPORARY result set table instead of repeating redundant data access.
   
   Using a DECLARE GLOBAL TEMPORARY table also allows defining an appropriately sized table space, tailoring the data clustering, possibly creating indexes and the option of pinning/isolating the data to a particular buffer pool.  Another option also provides the ability to set up the temporary table as NOT LOGGED.  All of these flexible options for DECLARE GLOBAL TEMPORARY tables provide great DB2 SQL performance improvements for all types of online and batch redundant data retrieval requirements.

With so many databases being built with bigger row volumes SQL performance continues to be very important for all types of processing.  Use the previously defined DB2 SQL performance tips along with these five new DB2 SQL performance tips to improve your application elapse time and CPU costs for the best DB2 SQL application performance possible.

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

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