"Positioned Update " for correlated queries

Sitaram Vijay kartik

"Positioned Update " for correlated queries
Hi all:

I need to update a table which currently has only 1.5
million rows. Currently a "searched update" is
performed and the query runs for hours and doesn't
finish... the query is:

UPDATE F2_T_lesq
SET (amt_prov)
=
VALUE(
(
SELECT F2_T_lesq.amt_prov-
(F2_T_lesq.amt_prov-SUM(t1.amt_prov_c)-ld.amt_prov)
FROM F3_T_temp1_prov t1, F2_T_ldir ld
WHERE t1.bu_id= F2_T_lesq.bu_id AND
t1.aut_nbr= F2_T_lesq.aut_nbr AND
t1.bu_id=ld.bu_id AND
t1.aut_nbr=ld.aut_nbr AND
F2_T_lesq.audit_nbr='0' AND
t1.estado_c=60 AND
t1.ref_nbr = 0 AND
t1.tipo_car='H'
GROUP BY t1.bu_id, t1.aut_nbr, ld.amt_prov,
F2_T_lesq.amt_prov HAVING
F2_T_lesq.amt_prov<SUM(t1.amt_prov_c)+ld.amt_prov
),
amt_prov)
WHERE aut_nbr<>0
;


I would like to write a "Positioned Update" for this
using cursors. Could anyone give me an example for
"positioned update" for a correlated query like the
one mentioned above.

I can use db2 SQL store procedures to do this.. i am
new to this ! any kind of assitance is greatly
appreciated.

Many thanks to all in advance..

Regards
Vijay

__________________________________________________
Do You Yahoo!?
Check out Yahoo! Shopping and Yahoo! Auctions for all of
your unique holiday gifts! Buy at http://shopping.yahoo.com
or bid at http://auctions.yahoo.com



Terry Purcell

Re: "Positioned Update " for correlated queries
(in response to Sitaram Vijay kartik)
Vijay,

Rather than trying to re-do the entire query another way, why not improve
the existing query???

I must admit to being a little confused as to what you are trying to do, but
will offer the following suggestions:

- You have a WHERE clause predicate "F2_T_lesq.audit_nbr='0'" in the
subquery. Should this be part of the outer query?
- Rather than coding a VALUE clause, why not simply avoid attempting to
update if the row qualifies by adding this logic to the WHERE clause also.
- You should be able to remove the GROUP BY since the correlation predicates
are passed in.
- Then I'd look at the access path chosen, to see what other reasons could
could be causing such a slow response.

So it may look something like:

UPDATE F2_T_lesq
SET (amt_prov)
=
(SELECT MIN(F2_T_lesq.amt_prov) - SUM(t1.amt_prov_c)- SUM(ld.amt_prov)
FROM F3_T_temp1_prov t1, F2_T_ldir ld
WHERE t1.bu_id= F2_T_lesq.bu_id AND
t1.aut_nbr= F2_T_lesq.aut_nbr AND
t1.bu_id=ld.bu_id AND
t1.aut_nbr=ld.aut_nbr AND
t1.estado_c=60 AND
t1.ref_nbr = 0 AND
t1.tipo_car='H'
)
WHERE aut_nbr<>0
and audit_nbr = '0'
and amt_prov <
(select SUM(t3.amt_prov_c + t4.amt_prov)
from F3_T_temp1_prov t3, F2_T_ldir t4
WHERE t2.bu_id= F2_T_lesq.bu_id AND
t2.aut_nbr= F2_T_lesq.aut_nbr AND
t2.bu_id=t4.bu_id AND
t2.aut_nbr=t4.aut_nbr AND
t2.estado_c=60 AND
t2.ref_nbr = 0 AND
t2.tipo_car='H')

This is not guaranteed to give you the correct result, because it is merely
my interpretation of what you have sent.
Regards
Terry Purcell
Yevich Lawson & Assoc Inc (YL&A)
IBM Gold Consultant - DB2
IBM Certified Solutions Expert
DB2 V7 Database Administration OS/390
http://www.ylassoc.com


-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On Behalf Of
Sitaram Vijay kartik
Sent: Tuesday, December 18, 2001 4:06 PM
To: [login to unmask email]
Subject: "Positioned Update " for correlated queries


Hi all:

I need to update a table which currently has only 1.5
million rows. Currently a "searched update" is
performed and the query runs for hours and doesn't
finish... the query is:

UPDATE F2_T_lesq
SET (amt_prov)
=
VALUE(
(SELECT F2_T_lesq.amt_prov-
(F2_T_lesq.amt_prov-SUM(t1.amt_prov_c)-ld.amt_prov)
FROM F3_T_temp1_prov t1, F2_T_ldir ld
WHERE t1.bu_id= F2_T_lesq.bu_id AND
t1.aut_nbr= F2_T_lesq.aut_nbr AND
t1.bu_id=ld.bu_id AND
t1.aut_nbr=ld.aut_nbr AND
F2_T_lesq.audit_nbr='0' AND
t1.estado_c=60 AND
t1.ref_nbr = 0 AND
t1.tipo_car='H'
GROUP BY t1.bu_id, t1.aut_nbr, ld.amt_prov, F2_T_lesq.amt_prov
HAVING F2_T_lesq.amt_prov <SUM(t1.amt_prov_c)+l d.amt_prov),amt_prov)
WHERE aut_nbr<>0
;


I would like to write a "Positioned Update" for this
using cursors. Could anyone give me an example for
"positioned update" for a correlated query like the
one mentioned above.

I can use db2 SQL store procedures to do this.. i am
new to this ! any kind of assitance is greatly
appreciated.

Many thanks to all in advance..

Regards
Vijay

__________________________________________________
Do You Yahoo!?
Check out Yahoo! Shopping and Yahoo! Auctions for all of
your unique holiday gifts! Buy at http://shopping.yahoo.com
or bid at http://auctions.yahoo.com