Why Select/Delete/Update with IN clause scanning entire table/index?

I have created a table
Cretate table TAB1(id int not null primary key, dept int);
Inserted 6 rows with values(1,1; 2,2; 3,3 ; 4,4 ; 5,5; 6,6).

In first transaction(T1).
delete from TAB1 where id =3 ; DB2 locks 3rd row with X lock.

In second transaction(T2)->
Select * from TAB1 where id IN (2,6); DB2 is waiting for T1 to be commited.

When I execute separately, it works fine.
Select * from TAB1 where id =2;
select * from TAB1 where id = 6;

Why DB2 waits for another transcation to be completed even though it is not requesting same ids?

Thanks

I guess you already know why - because the optimizer believes it is more efficient. And it probably is.

You can stop reading now.

If your question were "how to tell the optimizer not to scan the entire table", I would say "try ALTER TABLE ... VOLATILE CARDINALITY".

Setting this environment variable might also help (requires instance restart):

db2set DB2_EVALUNCOMMITTED=ON

While you are at it, you should also set these:

db2set DB2_SKIPINSERTED=ON
db2set DB2_SKIPDELETED=ON

Thanks for your reply. I am little confused why DB2 behaves differently when I use IN clause with multiple parameters and just with one parameter.
In the above example,
Select * from TAB1 where id IN(2,6) It's trying to read every row by requesting share lock. It will in the wait state because other session holds "X" lock on row 3.
versus
select * from TAB1 where id IN(2)
select * from TAB1 where id IN(6)
Avove statements return data without any wait.


I was expecting with IN(2,6) clause, it should read only rows 2 and 6. Why it is trying to read every row in the table/index.

Thanks

In order to have some idea how to answer that, it would helpful to know the verson of DB2 you are using (including fixpack level), the nubmer of rows in the table, the number of unique values in the column called "id", exactly what runstats command you have run, etc, etc, etc.

C:\IBM\SQLLIB\BIN>db2level
DB21085I Instance "DB2" uses "32" bits and DB2 code release "SQL09052" with
level identifier "03030107".
Informational tokens are "DB2 v9.5.200.315", "s080811", "WR21411", and Fix Pac

"2".
Product is installed at "C:\IBM\SQLLIB" with DB2 Copy Name "DB2COPY".

My sample table is
Cretate table TAB1(id int not null primary key, dept int);
Inserted 6 rows with values(1,1; 2,2; 3,3 ; 4,4 ; 5,5; 6,6).

Thanks

The answer is due to the number of rows in the table and the number you are fetching and the fact that you do not have an index with id,dept. Db2 knows that it has to access 33% of the table and if it were to use the index it would have to hit 33% of the rows in the index and then, also, access 33% of the data page, so it is cheaper and less CPU intensive to just scan the table. Once you have more data in your table and the stats are current it should begin using the index for your IN clause. Though, if your IN clause gets too large, you may go back to a scan of all rows.
Some of the options above will get DB2 to use the index even though there are only 6 rows in the table, but you will see increased CPU time, I'm sure.
Dave

Already there is an index on ID since it's a primary key. I tried by creating index only dept and also combination on (ID,DEPT), but I see the same issue.

I am running Select * from TAB1 where ID IN(2,6)-> it should access only indexes 2 and 6 since there is an index on ID. Is it correct?

There are 30 rows in the table TAB1, and I am trying to access only 2 rows. Select statement goes in the wait state if I run delete on any id from other session before my select st. for example delete from t3 where id=1;

If I run Select * from T3 where ID IN(6), it comes back fine.

Thanks,