Results 1 to 5 of 5

Thread: tablespace scan

  1. #1
    Join Date
    Sep 2003

    Unanswered: tablespace scan

    I an trying to invoke a tablespace scan in order to avoid list prefetch getting invoked. Due to list prefetch the record identifier pool is getting failed, have tried all means to trigger tablespace space for the below sql, I just need one and only one TS scan only. any thought for this,

    query 1:
    Last edited by sahana; 10-07-09 at 20:36.

  2. #2
    Join Date
    May 2003
    Did you try OPTIMIZE FOR 1 ROW at the end of your SQL?
    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
    May 2009
    Provided Answers: 1
    sahana, Instead of trying to force a Table space scan (which most people try to avoid on large data tables), you might want to figure out why the List Prefetch is failing. You might not have enough space allocated to your sort area.

    However, the only way to ensure a Table space scan is to remove any Index(es). Without an Index, you can only have a Table space scan.

    If you Explain the query, it can give you some indication of what DB2 is trying to do. For example, which table/index is it doing a RID sort?

    Making an assumption you have an index on the A table columns CD_LAB_TIME and SAT_NO (at a minimum and in that order), The A table index could be doing a partial Index scan. Of course if the other A columns in your WHERE clause are Indexed and can reduce the rows that qualify better, DB2 may be using them instead. Again, the Explain will provide this information.

    Either way, the qualifying A rows will have all the SAT_NO columns in no particular order. This is the column being joined to the B table. DB2 is (probably) sorting the A and/or the B rows by SAT_NO to make the join more efficient (by getting the RIDs in order so pages are only accessed once to get all possible qualifying rows).

  4. #4
    Join Date
    Sep 2003
    I also tried OPTIMIZE for 1 ROW at the end of the query , no luck. And I ensured all the predicates are Indexed. The list prefetch fails again.
    Last edited by sahana; 10-04-09 at 17:46.

  5. #5
    Join Date
    Dec 2007
    Richmond, VA
    Provided Answers: 5
    On which table are you getting the list prefetch? and what are the index definitions? Might want to share the explain info with us.
    An old trick is to use "OR 0 = 1" to dissuade use of a particular index. Though you should share the above info first and come back to this as a last resort.
    Dave Nance

Posting Permissions

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