Execution plans for cursor FOR loops

#11

jefftyzzer wrote:

Quote:

On Jul 2, 11:44 pm, Serge Rielau <srie... (AT) ca (DOT) ibm.com> wrote:
DECLARE txt VARCHAR(32000);
SET txt = 'BEING ATOMIC '
|| 'FOR L_REPS AS '
|| ' SELECT X, Y FROM Z '
|| ' DO '
|| ' UPDATE A SET C = N WHERE A.C = L_REPS.X; '
|| ' UPDATE B SET C = N WHERE B.C = L_REPS.X; '
|| ' UPDATE C SET C = N WHERE C.C = L_REPS.X; '
|| ' UPDATE Z SET C = L_REPS.Y WHERE Z.C = L_REPS.X; '
|| ' END FOR; END'

EXECUTE IMMEDIATE txt;
If you EXPLAIN PLAN on this beats you should see a join between the FOR
LOOP query and either a FILTER or a UNION operator where each arm is an
UPDATE.

Incredible. I had no idea that you could put (procedural) control
structures in dynamic SQL like that. I'll let you know how it pans
out.
Hehe, that was my internship project thirteen years ago...

"inline SQL PL"

--
Serge Rielau
SQL Architect DB2 for LUW
IBM Toronto Lab

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Execution plans for cursor FOR loops

comp.databases.ibm-db2 comp.databases.ibm-db2

Re: Execution plans for cursor FOR loops - 07-06-2009 , 05:54 AM