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:

Did you try OPTIMIZE FOR 1 ROW at the end of your SQL?

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).

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.

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