This article examines ways of using DB2 Version 8 and DB2 9 SQL and application features to help reduce the number of trips we make across the address spaces in DB2 z/OS. The quantity of SQL calls made can sometimes be more detrimental to performance than poor quality SQL. Don’t be fooled by low response times per statement and high bufferpool hit ratios!
Generic application design has led to an increase in the number of SQL statements issued. Every SQL call represents overhead due to cross-memory communication on the mainframe and cross-network traffic and cross-memory communication from remote clients as shown in Figure 1. Modern, generic techniques, such as Service-Oriented Architecture (SOA) development, increase the quantity of SQL statements issued. Often, the use of system-generated keys (or surrogate keys) and programmatic joins propagate this. These techniques are popular when organizations commit to the perceived flexibility of object-relational designs.
While these designs may seem easy and reusable, they result in too many generic calls to DB2. Every call to DB2 represents CPU and elapsed time consumed. Is there a way we can reduce this time with DB2 for z/OS SQL features? Sure, we can reduce the CPU and elapsed time consumption by reducing the calls across address spaces and the network by using SQL and application features in DB2 V8 and DB2 9.
V8 SQL and Application Features
Let’s examine some of the features in V8 that we’ve successfully used to reduce the number of calls made to DB2, as well as gain additional performance benefits and functionality.
The SQL language has become more orthogonal with V8, meaning we can put expressions almost anywhere in the SQL statement. With V8, we can put a SELECT statement in a SELECT, a WHERE clause, or even in a CASE expression. An SQL statement is an expression. Scalar fullselects in an SQL statement can be a performance advantage, as they can help us reduce the quantity of SQL statements issued, use correlations for transaction performance, and create more advanced SQL statements to solve business problems.
The focus here is SQL reduction, so we’ll look at a few examples of how scalar fullselects can be used. The following statement shows an example of putting a SELECT in the SELECT list, which finds the maximum dollar amount for an account by selecting from the ACCT_HIST table. This information is presented in the SELECT list along with the details on the ACCT_NUM and ACCT_RATING_CDE: