Problem on SQL statement and db2diag.log

[lcklee]

Hello All,

I have issue following command in DB2 prompt:
=======================================================
select * from ADM_ANNOUNCE where ann_id not in(select ann_id from ADM_ANNOUNCE_READ where user_id = 12)
=======================================================

and then following log generate in the db2diag.log:
===========================================================
2003-10-03-09.24.55.432344 Instance:db2inst1 Node:000
PID:56968(db2agent (JETDXTS1)) Appid:*LOCAL.db2inst1.031003012107
runtime_interpreter sqlri_tbl_in_mem_alloc Probe:190 Database:JETDXTST

sqlri_tbl_in_mem_alloc(): Active=Y, Reason=0, Mem=64, #InSubq=3, TIMMax#=3, %Fit
InTIM=100.00
===========================================================
The log level is set to 4 and I have perform some testing.
If I issue the SQL statement with "not in", the above log will generate. If I issue with just "in", the above log will not generate. So the "not in" in the SQL have difference with just "in" ??

Please help.

 

[blom0344]

Your uncorrelated subquery is probably running into problems with available memory. This type of uncorrelated subquery using NOT INTO is about the most resources consuming type of query available. Avoid them as possible, ESPECIALLY for large datatables.
Can You rewrite the query using a join? This will definitely resolve the memory problem. As ann_id is available in both tables this should be possible....
Look into the use of 'EXCEPT' how you can handle the NOT IN part :

Select A.X,A.Y,A.Z FROM A,B where A.X = B.X
EXCEPT
Select A.X,A.Y,A.Z FROM A,B where A.X = B.X
and B.Y = .....

T. Blom
Information analyst
tbl@shimano-eu.com

 

[lcklee]

thx so much T. Blom.