Long ago, a learned colleague (Prem Mehra) introduced me to the concept of unnecessary SQL and his oft-quoted truism: "There is no better-performing SQL than the SQL that is not executed." In this three-part series, we are going to look at SQL that is totally unnecessary and should be eliminated, SQL that is executed far more times than necessary, and SQL that should be replaced with newer, better-performing SQL. The goal? To reduce connects to DB2 and, if possible, to eliminate some connections completely. The other goal? To learn about the latest solutions in IBM DB2 8 and DB2 9 for these old problems. So with that in mind, welcome to part 1 of my series on eliminating or reducing connects to DB2.

COUNT(*): Problem 1

There are many fundamental performance rules that should be obeyed when writing programs. One is to eliminate all unnecessary SELECT COUNTs in a program. Another is to reduce the number of executions of COUNTs if they cannot be totally eliminated. Let's look at a few examples:

Select count(*) into :hvcount
from employee_master
Where status_flag = 'T'
and hiredate <= current date - 90 days

Followed by:

Update employee_master
Set status_flag = 'P'
Where status_flag = 'T'
and hiredate <= current date - 90 days

Whenever I see SQL like this, my internal warning buzzer goes off. Why is there a COUNT before the UPDATE? I check further. Is the host variable ever examined? If it's 0, what is the action? What if the COUNT is greater than 0? Is the next action dependent upon the content of :hvcount?

Often the COUNT is totally unnecessary and can be completely eliminated. In this case, the programmer may want to know how many temporary (T) employees will be updated to permanent (P) status when/if the UPDATE statement is executed.

Two things are wrong with this approach. First, unless you are using ISOLATION RR or have exclusive maintenance access to the table, the COUNT of the number of qualified rows read and the actual number of rows that will be updated might differ. Rows could be deleted, inserted, or updated between the COUNT and your actual UPDATE.

However, the other-and far more important- problem is that the COUNT is unnecessary because DB2 counts the rows as they are updated and returns the count in SQLERRD(3) of the SQL Communications Area (SQLCA). As you test your programs, check to see if this SQLCA field contains the COUNT information that you need.

As far as I know, DB2 counts your rows as you do maintenance whenever it can. However, I do know of at least two exceptions:

1. When you DELETE all of the rows from a table (i.e., no WHERE clause) in a segmented tablespace
2. When you DELETE all of the rows from a table in a universal tablespace (new in DB2 9)

When doing a mass DELETE from a table that is in either of these two types of tablespaces, DB2 has no need to individually address the rows and therefore does not count them- with three exceptions:

1. When DATA CAPTURE is on
2. When a VALIDPROC is involved
3. When row-level security is being used

However, mass DELETEs are most commonly used for work tables or temporary tables, which are highly unlikely to employ any of these three exception categories.

A new feature of DB2 9 allows you to truncate a table, thereby "reinitializing" it (either by resetting the High-Used Relative Byte Address [HURBA] or by deleting and redefining the underlying VSAM data set- much like a LOAD with an empty INPUT data set). This, like the mass DELETE, does not count the rows for you.

Back to top

COUNT(*): Problem 2

Let's look at another real-life example of an improper/inadvisable/inappropriate use of

Select count(*) into :hvcount
from employee_master
Where status_flag = 'T'
and hiredate <= current date - 90 days
Followed by coded logic:
If :hvcount = 0, then get next, else if :hvcount =
1, then
Select col1, col2… into :hvcol1, :hvcol2
from employee_master
where status_flag = 'T'
and hiredate <= current date - 90 days
Else if :hvcount > 1, then
Declare mycursor cursor for
Select col1, col2… into :hvcol1, :hvcol2…
from employee_master
where status_flag = 'T'
and hiredate <= current date - 90 days
Open mycursor …
Fetch mycursor into :hvcol1, :hvcol2…

Let me reassure you: I am not manufacturing these examples. Here the programmer has very honorable intentions: to use a lower- CPU, shorter path-length singleton Searched SELECT if only one row qualifies, and to use a higher-CPU, longer path-length CURSOR if and only if >1 row qualifies.

But reading the row(s) to count them and then (again) reading the row(s) to process them is not the answer. Double dipping is not good, even if the pages are already in the buffer pool. How many times have I heard that? Just because the pages may be in the buffer pool does not mean the second read is free. Now that DB2 is one serious, flying, crunching maintenance machine, eliminating connects is one of the few places we can see huge reductions in CPU and GET PAGE overhead.

So, what is a better approach for this problem? The programmer is trying to avoid a higher overhead cursor. Therefore, think: How often is there only one row? Ninety-eight percent of the time, you say? Then do the singleton SELECT first. Then, if and only if you receive an -811 ("multiple rows returned to a Singleton Select") error message, open a cursor. Conversely, if the majority of the time more than one row will be found, then open a cursor immediately without worrying about the small payback from a singleton SELECT. In other words, do what is most common first, and don't do the COUNT.

Back to top

COUNT(*): Problem 3

<!-- Please limit code lines to 90 characters max. -->
<!-- The following line is the max length: -->
|-------10--------20--------30--------40--------50--------60--------70--------80--------90
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
Select count(*) into :hvcount
from employee_master
Where status_flag = 'T'
and hiredate <= current date - 90 days

This COUNT is followed by this logic: divide :hvcount by 10 (the number of rows that fit on a screen) to see how many screens of data there are. Why? So that each screen, including the initial one, can display "Page 1 of n" for the user. Here we have an "uh-oh, can't be eliminated" requirement. But is there a better way to find out how many 10-row screens will qualify if our user actually, and maybe unrealistically, hits PF8 until there are no more rows/screens?

Yes. First of all, ask, "How many screens are there usually?" If the answer is one screen or less than a full screen, then don't count the rows before you read them. Instead, open the cursor, fetch the rows, and count the rows as you read them. If you reach an End of File before you hit row 11, you know your answer without connecting to do the COUNT. And you have eliminated all of the GET PAGEs (maybe READ I/Os) incurred when you made DB2 do the COUNT.

What if there are usually between 11 and 30 rows that qualify? Then read those rows, too, counting as you FETCH. If you hit +100 before or as you read row 31, then save those rows in some area that hangs around between screen displays (e.g., in CICS, the COMMAREA). Put "Page 1 of 3" on page one and eliminate the instructions that would have been needed for the PF8s to display "Page 2 of 3" and "Page 3 of 3".

If more than three pages of rows qualify, then store what the user will realistically look at in the COMMAREA (or wherever). Then, and only then, do the COUNT. You have deferred the COUNT until the last possible moment. You have avoided the double read in most situations. Most important, you have a good program with great logic that others can replicate to write their programs.

Back to top

We get it. What's next?

For those of you who work with DB2 daily and know a thousand examples and exceptions to my suggestions, I thank you for being in the trenches of the "It depends!" world of reality- and for sharing your insights and wisdom. For the rest of you, the next column will include more examples of unnecessary SQL and the whys, along with better and newer solutions for replacing the common code that is out there. Stay tuned for part 2.

About the author

Comments (Undergoing maintenance)

Back to top

Trademarks  |  My developerWorks terms and conditions

Table of contents

• COUNT(*): Problem 1
• COUNT(*): Problem 2
• COUNT(*): Problem 3
• We get it. What's next?
• About the author
• Comments

Next steps from IBM

• Try: DB2 Enterprise
• Demo: DB2 serving ATOM feed
• Webcast: A smarter way to successful SOA - Information as a trusted strategic asset
• Briefing: Information on Demand
• Buy: DB2 Enterprise

My developerWorks community

Dig deeper into Information Management on developerWorks

DB2 pureScale

See how true transparent scaling architecture can be achieved on distributed platforms

Special offers