Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2004
    Posts
    9

    Unhappy Unanswered: DB2 FETCH performance issue

    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!

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Jan 2004
    Posts
    9
    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.
    Last edited by Grumpy; 02-06-04 at 16:07.

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Jan 2004
    Posts
    9
    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?

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •