RE: SQL: how to speed up join tiny file to giant file

Good point, but I think you can do a "correlated sub-query"
Here is some older code I have that may help:
/* ******************************************************************/
/* */
/* General notes about DB2 and updating a table from another table */
/* */
/* 1) DB2 does not support join updates */
/* 2) You can update from another table by using a sub query. */
/* A sub query, called a "subselect" in IBM manuals, is a */
/* select statement contained in parenthesis within another */
/* sql statement. */
/* 3) A sub query runs for EACH ROW of the "outer" statement. */
/* 4) A sub query that supplies a value for an update must */
/* return a single row for the row in the outer query */
/* that is to be updated. */
/* 5) You typically need a where clause in the sub query that */
/* identifies the row(s) in the outer query that are to */
/* be updated. */
/* 6) Sometimes you need a where clause that defines a UNIQUE */
/* KEY in BOTH the select and subselect statement(s). */
/* ******************************************************************/
update usrgdt3/mara2005
set sacact = IFNULL(( select sapact
from qgpl/sapmara600
where sapmara600.sapmat = mara2005.sacmat and
sapmara600.sapplt = mara2005.sacplt and
sapmara600.saploc = mara2005.sacloc and
sapmara600.sapmg = mara2005.sacmg and
sapact is not null
), 0)
64429 rows updated in MARA2005 in USRGDT3.

UPDATE basdbs5/om21 as sl
SET sl.stklev = ( SELECT newstkuni
FROM basdbs5/cf30 as cf
WHERE cf.outnum = sl.outnum AND
cf.cnstyp = '6 ' AND
cf.artnum = sl.artnum AND
cf.vrt = ' ' AND
cf.pri = 0 AND
cf.newstkuni > 0 )
WHERE sl.outnum in ( SELECT outnum
FROM basdbs5/cf30 as cf
WHERE cf.outnum = sl.outnum AND
cf.cnstyp = '6 ' AND
cf.artnum = sl.artnum AND
cf.vrt = ' ' AND
cf.pri = 0 AND
cf.newstkuni > 0 )
UPDATE basdbc5/om01 tgt
SET natoutnum = ( SELECT natoutnum
FROM usrgdt850/om01 src
WHERE tgt.outnum = src.outnum)
WHERE outnum = ( SELECT outnum
FROM usrgdt850/om01 src2
WHERE tgt.outnum = src2.outnum )


-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Stone, Joel
Sent: Thursday, August 13, 2015 10:55 AM
To: Midrange Systems Technical Discussion
Subject: RE: SQL: how to speed up join tiny file to giant file

Thanks, but my goal is to do an update after I get the SELECT functioning properly.

I don't think I can update with a JOIN in v5r4.



-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Luis Rodriguez
Sent: Thursday, August 13, 2015 11:49 AM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Subject: Re: SQL: how to speed up join tiny file to giant file

Joel,

Have you tried using a join? Maybe the optimizer would work better in that case...

Regards,

Luis

Luis Rodriguez