Taking Advantage of Relational Technology

Last time we looked at the application design of a major DB2 system. Now, let’s delve a little further and look at the SQL used in these applications.

Taking Advantage of Relational Technology

During reviews of various client systems, analyzing the SQL used is very helpful for understanding how sophisticated the shop and system are at leveraging relational technology. When the shop and system are well designed, the tables, indexes and application properly utilize object designs through effective object reuse and consistent design patterns.  All of these application components are exposed when the application access methods are designed and formulated into SQL.  Usually if a system is designed properly the SQL contains good access paths. SQL with good access paths use the following:

• SQL Joins
• SQL Sub queries
• Table expressions
• CASE expressions
• Limited fetch

The more work done within in the DB2 relational technology engine, usually the faster the data can be turned into information for application reports, web pages, etc.  At a recent client there were zero table expressions, limited fetches and very few of any of the other SQL options to leverage DB2 and relational technology.  We rewrote one of the applications with an SQL Join and an SQL Table expression and dramatically reduced its elapsed time and CPU requirements.

Use SQL Joins

Analysis of another client’s system showed that the application programmers were not using SQL Joins in any of their applications. Most of the application data processing focused on retrieving one row at a time and often from only one table.

The basis of relational technology is set processing or processing a large number of rows through a single SQL statement. Application programmers need to understand and leverage relational technology so that the application programs process more data with each interaction with the database.  Often when SQL is used to process single database rows, the application program can be rewritten with SQL Joins of tables using indexes and proper WHERE clauses and dramatically cut the CPU and elapsed processing time.  Using any of the several of SQL Join methods also optimizes I/O and CPU usage of the application by pushing the work from the program into the DB2 engine.

Experience with several past clients has shown significant elapsed time process improvement and tremendous CPU savings by rewriting application with SQL Joins.  For example, an application designed with poor SQL was rewritten with SQL Joins and went from 12 hours of execution time to only 5 minutes.

Experience has shown that applications that yield the biggest improvements are those that are executed often and open cursors to access a large number of rows.  Once these applications are identified, an applications team can redesign these programs to use more efficient relational technology and SQL join set processing.

Summary

It is important to drive processing to the SQL engine as much as possible. Review applications in your company for:

• Lack of SQL Joins
• SQL Sub queries
• Table expressions
• CASE expressions
• Limited fetch

________________________________________

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.