Re: Long compilations

[Troels Arvin]

Serge Rielau wrote:

Can you rpovide db2exfmt output?

Somewhat scrambled (for privacy reasons) output:

http://troels.arvin.dk/db/db2/long-compilation/1/statement-explained.txt

(My scrambling _may_ have broken internal references)

One scenario may be that you have a recursion in your triggers. DB@ for
LUW expands triggers into teh graph. So your explain plan may look much
bigger than you might anticipate (and thus compile time is much higher).

I don't use common-table-expression recursion, if that's what you are
referring to. If you mean "triggers calling other triggers": There are
cases of this, but I'm pretty sure that there aren't any cycles.

I'm not surprised that the artistic look of the query looks like this:

http://troels.arvin.dk/db/db2/long-compilation/1/explain-graph.png

And I'm actually glad that DB2 takes a break to really think hard about
an efficient way to proceed (as there is much I/O involved).
- However, I'm surprised that compilation takes _that_ long, so I'm
thinking that I might somehow give DB2 a hand by expanding some
compilation-related heap/memory pool, or perform other parameter massage.

Also: EXPLAIN finishes rather quickly, i.e. within a minute. So why is
the compiler taking so much longer?

If you
send me the trigger DDL I may be able to do a quick(!) run through and
provide advise.

CREATE TRIGGER sche_util.trig_ import_view_1
INSTEAD OF INSERT ON sche_util.impor t_helper_view
REFERENCING NEW AS n
FOR EACH ROW INSERT INTO sche_prod.targe t_table_1 (
primkeycol,
charcol1,
charcol2,
charcol3,
charcol4,
varcharcol1,
charcol5,
charcol6,
smallintcol1
) VALUES (
sche_util.trim_ nullify(n.primk eycol),
RTRIM(LTRIM(n.c harcol1)),
RTRIM(LTRIM(n.c harcol2)),
RTRIM(LTRIM(n.c harcol3)),
RTRIM(LTRIM(n.c harcol4)),
RTRIM(LTRIM(n.v archarcol1)),
RTRIM(LTRIM(n.c harcol5)),
RTRIM(LTRIM(n.c harcol6)),
sche_util.trans _dok_bool(n.sma llintcol1)
);

Inserting into target_table_1 will trigger functions which encrypt some
values, and call stuff like this for selected columns:

http://troels.arvin.dk/db/db2/code/db2-date-massage.sql

I'm thinking that some of the functions involved perhaps ought to be java-
based functions(?), but I'm afraid of java-based routines because I've
yet to understand if such routines are backed/restored up as part of
normal DB2 backup/recover operations; and I'm afraid what will happen to
java-based routines when DB2 is upgraded.

--
Regards,
Troels Arvin <troels@arvin.d k>

http://troels.arvin.dk/

[Serge Rielau]

Re: Long compilations

I see two triggers....you only posted one.
$TRIGGER$(SCHE_ UTIL.TRIG_IMPOR T_VIEW_1),
$TRIGGER$(SCHE_ PROD.TRIG_TARGE T_TABLE_I),
What makes the SQL Functions ugly is the IF THEN ELSE logic.
I understand you want to avoid the CALL if the input is NULL,
but it sure makes things more ugly.

Overall I do not see why this compilation should take 30min.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab