DB2 v9 z/OS

Did see some postings already on this topic but no clear answers yet
First query : IN LIST with 120 values (returns 593 rows)
Second query : IN LIST with 130 values (returns 668 rows)

Same query with IN LIST of 120 values compared to same query with IN LIST of 130 values having a performance degradation with factor 10 as
an index is used in the first select (and data pages fetched from tablespace) while the second query performs a table scan .
Monitoring showed first query takes 0.116495 seconds while second query
takes 1.694264 seconds .

The only way I managed to force an index scan with the second query with 130 values in the IN LIST (or more) is to add OPTIMIZE FOR 1 ROWS
(this still returns the 668 rows in case of IN LIST with these 130 values but
the actual elapsed time went from 1.694264 sec to 0.028218 sec)

SQL & DDL :
SELECT col1, col2, col3, col4, col5
FROM table
WHERE col2 IN ( ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?
, ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?
, ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?
, ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?
, ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?
, ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?
, ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?
, ? , ? , ? ) AND
col3 IN ('?', '?','?','?','?','?','?')

Table :
Column Name Col No Col Type Length Scale Null Def FP Col Card
------------------ ------ -------- ------ ------ ---- --- -- -----------
col1 1 DECIMAL 13 0 N N N 152855
col2 2 DECIMAL 9 0 N N N 29639
col3 3 CHAR 4 0 N N N 7
col4 4 DECIMAL 9 0 N N N 12288
col5 5 DATE 4 0 Y Y N 1


unique index (col2, col3, col5) :
Column Name Seq No O Col Type Length Scale Null Def FP Col Card
------------------ ------ - -------- ------ ------ ---- --- -- -----------
col2 1 A DECIMAL 9 0 N N N 29639
col3 2 A CHAR 4 0 N N N 7
col5 3 A DATE 4 0 Y Y N 1


Anyone on z/OS v9 experienced same issues already ?

Hey Swiss,
This is nothing new, I ran across it back in V5. It seems you already have your solution. Another option would be the runstats that are being collected. Ensure the Keycard and freqval are being collected. Also, if you take a look at the DB2-L archives over on IDUG.org There have been several discussions on the topic over the last several years that Pat Bossman and Terry Purcell(both work on the optimizer) have weighed in on. I always try to see if I can turn an IN list into and EXISTS/NOT EXISTS when possible.
Dave Nance

Thanks Dave . Am only on db2/Zos since v7 .. will check the idug.org