I'm not new to SQL but am far from an expert. Most of my experience has been with non-DB2 databases so bear with me. I'm trying to run an sql update query against an AS400 and its taking an incredibly long time to run. Here's the query:

update R35FILES.VCMUDEF set CCF043 = 'Y' where CCCUST in (
select SBCUST
from ( SELECT SBCUST, sum(SBPRIC) as DOLLARS FROM VAIMODSDTA.VSADETL where SBDIV = 23 and (SBCLS = 27 or SBCLS = 28) group by SBCUST )
as shinkoDlrs
where DOLLARS > 2000
)

The VCMUDEF file has approximately 15,000 records and the VSADETL file has 4.7 million records.

If I run this portion of the statement

select SBCUST
from ( SELECT SBCUST, sum(SBPRIC) as DOLLARS FROM VAIMODSDTA.VSADETL where SBDIV = 23 and (SBCLS = 27 or SBCLS = 28) group by SBCUST )
as shinkoDlrs
where DOLLARS > 2000
)

results are returned in 37 seconds. When I run the statement with the update on it, the query runs for several hours and still does not complete (it never errors out ... I just cancel it). Is there something wrong with my logic?

Any help would be appreciated.

Brad

Which indexes do you have?

VCMUDEF is indexed by CCCUST (Customer #).

VSADETL is indexed by SBORD (Order #).

As a general logic question regarding this issue I'm having, does DB2 execute the internal subselects for every record in VCMUDEF? Or does it store the resultant set of Customer Numbers for the update to go against?

Brad,
When you say that select takes 37 seconds. Does it take 37 seconds for you to get the entire resultset or until it returns some records to you? A lot of products that allow you to query the database only get so many records at a time, but how long does it take until the last record is fetched is often quite different.
I would think an index on VSADETL table with the columns SBDIV and SBCLS would be beneficial to you. Also, I would change your sql to something more like:
update R35FILES.VCMUDEF
set CCF043 = 'Y'
where CCCUST in (select SBCUST
from VAIMODSDTA.VSADETL
where SBDIV = 23
and SBCLS IN (27,28)
group by SBCUST
having sum(SBPRIC) > 2000)
Another solution without a new index is there must be some key between these two tables that is indexed so you could try something like:
update R35FILES.VCMUDEF a
set CCF043 = 'Y'
where exists (select SBCUST
from VAIMODSDTA.VSADETL b
where a.key = b.key
and SBDIV = 23
and SBCLS IN (27,28)
group by SBCUST
having sum(SBPRIC) > 2000)
I'm not too sure about the group by/having clause in an exists/not exists clause, never tried it before. Good luck.

Dave Nance

man thta was a brain fart on my part. your a key and b key were in the query cccust and sbcust
Dave