Colleagues,

I'm trying to troubleshoot some performance issues pertaining to an
SP. Despite setting/calling

CALL SET_ROUTINE_OPTS(GET_ROUTINE_OPTS()||'EXPLAIN ALL');
SET CURRENT EXPLAIN MODE EXPLAIN;

before executing the SP, none of the SQL associated with any of the
cursor FOR loops within it is showing up in the EXPLAIN tables and
therefore in db2exfmt. Note that all of the other (dynamic) SQL in the
SP is explained.

Here's a snippet of one of my cursor FOR loops, in case it helps:

FOR L_DUP_IDS AS
SELECT
ID
FROM
SESSION.A_TABLE
GROUP BY
ID
HAVING
COUNT(REP) > 1

DO

SET V_MAX_REP = (SELECT MAX(E.REP) FROM SESSION.A_TABLE E
WHERE E._ID = L_DUP_IDS.ID);--

....

Anyone know how I can solve this?

Thanks,

--Jeff

jefftyzzer wrote:
SQL referring to SESSION temps doesn't get compiled until execution time.

Cheers
Serge

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

On Jun 30, 6:33*pm, Serge Rielau <srie... (AT) ca (DOT) ibm.com> wrote:
Thank you for your reply, Serge, but forgive me: what do you mean by
"(drive) the procedure"?

--Jeff

jefftyzzer wrote:


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

On Jul 1, 5:06*pm, Serge Rielau <srie... (AT) ca (DOT) ibm.com> wrote:
Thanks. Another question related to cursor FOR LOOPs.

Consider this completely contrived example:

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;--

Again, this is not the real SQL but (for very good reasons) it follows
the same pattern: cursor FOR LOOPs (sometimes one nested inside
another) doing thousands of index-based updates against large-ish (say
500K-1M rows) DGTTs using host variables (e.g., the L_REPS.X and
L_REPS.Y).

We are seeing a HUGE (from minutes to hours) negative performance
impact on the SP when the package this SQL is part of is bound with
REOPT ALWAYS vs. being left at the default. Is it reasonable to you
that recompiling the access plan with each iteration (right?) of the
loop could have such a dramatic effect? Can anyone ballpark the
general overhead of the recompilation of small updates like the ones
above?

Environment: "DB2 v8.1.1.96", "s050811", "U803920", and FixPak "10";
AIX 5.3)

Thanks,

--Jeff

For such a simple single row UPDATE? Absolutely reasonable.
The compiler can easily dominate the cost for such a simple statement.
I am somewhat surprised that you have REOPT ALWAYS as default.

Also I think there is a lot of room to improve this code.
One way would be to use a dynamic compound instead (that would require
you do use dynamic SQL). Essentially te FOR LOOP and UPDATE stay the
same, but you wrap it into a BEGIN ATOMIC block and EXECUTE [IMMEDIATE].
As a result the entire loop gets poured into a single plan and should
execute much faster.

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

On Jul 2, 5:02*pm, Serge Rielau <srie... (AT) ca (DOT) ibm.com> wrote:
Sorry--REOPT ONCE is the default, but when the package is instead
rebound with ALWAYS, calamity ensues. As to your suggested
improvement, I'm all ears. As I mentioned, the SQL I included here is
just meant to facilitate discussion. If it's OK with you, I'd like to
email you an example of the real SQL/PL, as I don't completely
understand your recommendation.

--Jeff

On Jul 2, 5:20*pm, jefftyzzer <jefftyz... (AT) sbcglobal (DOT) net> wrote:
Before I spam your Inbox--and for the benefit of other posters--let me
see if this is what you mean, using a modified example from the v9
docs:

DECLARE V_SQL VARCHAR(40);--

BEGIN ATOMIC
FOR row AS
SELECT pk, c1 FROM source
DO
SET V_SQL = 'UPDATE SESSION.T SET C = '||ROW.C1||' WHERE X= '||
ROW.PK;--
EXECUTE IMMEDIATE V_SQL;--
END FOR;--
END;

Again, this code is just an example for pedagogical purposes only--I
grant that there are better (set-oriented) ways to update table A
based on values from B.

Thanks again,

--Jeff

DECLARE txt VARCHAR(32000);
SET txt = 'BEING ATOMIC '
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.

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

On Jul 2, 11:44*pm, Serge Rielau <srie... (AT) ca (DOT) ibm.com> wrote:
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.

Thanks very much,

--Jeff