Hi. Working with DB2 UDB v6.1 (OS/390) in a distributed environment. Recently, we've observed that the main FETCH (i.e. retrieving the records to process) for a batch program is "slow" / consuming a great deal of time. Granted, FETCHes, like every DB operation consume time/resources, but in this case it's to the point where we took notice.

My question is: is there anything that can affect the performance of a FETCH statement? perhaps the manner in which the SQL is coded? or the columns being retrieved?

Thanks!

Yes, there are lots of things that can affect the performance. Please explain in detail:

1. What you mean by distributed environment.
2. Whether you have other fetches in same distributed environment that do not have the performance problem.
3. The SQL statements involved
4. The Table and index DDL of all tables in the SQL
5. When the last time you ran runstats utility on the table and indexes
6. The approximate number of rows of the table and distribution counts of columns in the predicate (where clause).
7. The bind parameters used.

Hi - sorry for the delay in responding. We ended up eliminating a join to a very large / commonly accessed table which improved performance considerably.

My question was more of a theoretical one...Namely, I understand many of the factors that go into SQL performance...What I was more interested in knowing is if FETCH operations include the execution of the SQL at times (i.e. the execution of the SQL and building of the results set is not just limited to the OPEN)?...My understanding is that DB2 only builds a result set if need be (e.g. if it has to sort, etc..) otherwise, it accesses the data with each FETCH (or something to that effect) - loosely speaking. Is that the case?

Thanks the for help - it is appreciated.

I think you basically have the right idea.

In some cases (such as when you have an ORDER BY that is different that the index used to access the data), DB2 must read all the rows in the cursor before the first row is returned to the program with the first FETCH. DB2 builds a temporary table in order to do this (this is not a global temp table which you can access). Then DB2 fetches each row from the temporary table as it is requested. This is sometimes called a "materialized" cursor.

There other reasons that the cursor may be materialized, such as a defining a scrollable cursor in version 7 (for OS/390).

In other cases, no temporary table needs to be created and the rows are fetched from the table referenced by the cursor.

Thanks!

Have you ever found it to be advantageous to force a CURSOR to materialize even if it's not the most optimal method for retrieving the data? - i.e. so you can build te results set just once and that's it?

DB2 for OS/390 version 7 has something called a scrollable cursor that might come in handy. Because it always materializes the answer set in a temporary table, it has some extended features.

The WITH HOLD option on a cursor is also useful to keep the cursor open after a commit.

There is a good discussion of cursors in the Application Programming Guide.