Hello,

Setup: DB2 v. 9.5 on Linux (64 bit).
I'm loading a rather large amount of data from some import-/staging-
tables into some writable views which point to the end production tables.
A set of of triggers are attached to the views in order to perform
transformations (such as trimming strings, converting strings to
datetime-values or NULL in case the string is invalid, ...).

The process takes a long time -- which is expected, to a certain degree.
However, when I do a "list applications show detail" I consistently see
that Status=Compiling is prevailing for a long time, i.e. more than
½hours.

At http://troels.arvin.dk/db/db2/long-compilation/1/ I've put output from:

- "get snapshot for application ...": appl-snapshot.txt
The application snapshot is taken after the application had
been in the Status=Compiling state for 23 minutes.

- "get db cfg": db-cfg.txt

- "get dbm cfg": dbm-cfg.txt

- a number of other files, such as iostat output

As iostat reports, I/O levels are low. This is confirmed by looking at
the SAN where the server doesn't seem to perform much I/O.

It also seems that application status jumps directly from "Compiling" to
"Commit Active". I'm not sure about this, though (I haven't monitored
this aspect closely yet).

What could be going on?

- Is DB2 lying about the application status? Maybe it's not really
doing all this time compiling, after all?

- Do I need to adjust some memory parameters? (I'd rather not:
I'm hoping that DB2 v. 9.5 should be pretty good at doing this
automatically).



By the way:
On this database, I've taken the risk of leaving the Health Monitor on.
in db2inst1.nfy, I'm getting paragraphs like:
-----------------------------------------------------------------------------
2008-05-22-10.39.15.019204 Instance:db2inst1 Node:000
PID:4122(db2acd) TID:1125919040 Appid:none
Health Monitor HealthIndicator::update Probe:500

ADM10501W Health indicator "Database Heap Utilization" ("db.db_heap_util")
breached the "upper" warning threshold of "85 %" with value "85 %" on
"database" "db2inst1.SOMEDB". Calculation:
"((db.db_heap_cur_size/db.db_heap_max_size)*100);" = "((24903680 / 29425664) *
100)" = "85 %". History (Timestamp, Value, Formula): " (05/22/2008
10:34:15.029741, 84, ((24838144 / 29425664) * 100)), (05/22/2008
10:29:14.481610, 85, ((24903680 / 29425664) * 100)), (05/22/2008
10:24:14.791264, 85, ((24903680 / 29425664) * 100)), (05/22/2008
10:19:14.854542, 84, ((24772608 / 29425664) * 100)), (05/22/2008
10:14:14.692875, 85, ((24903680 / 29425664) * 100)), (05/22/2008
10:09:14.963598, 84, ((24772608 / 29425664) * 100)), (05/22/2008
10:04:14.951185, 84, ((24772608 / 29425664) * 100)), (05/22/2008
09:59:14.564790, 85, ((24903680 / 29425664) * 100)), (05/22/2008
09:54:15.117478, 85, ((24903680 / 29425664) * 100))"
-----------------------------------------------------------------------------

I'm unsure how to react to this.

--
Regards,
Troels Arvin <troels@arvin.dk>
http://troels.arvin.dk/

Troels Arvin wrote:
> Hello,
>
> Setup: DB2 v. 9.5 on Linux (64 bit).
> I'm loading a rather large amount of data from some import-/staging-
> tables into some writable views which point to the end production tables.
> A set of of triggers are attached to the views in order to perform
> transformations (such as trimming strings, converting strings to
> datetime-values or NULL in case the string is invalid, ...).
Can you rpovide db2exfmt output?
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).
Often teh trigger logic can be streamlined to avoid the issue.
If you send me the trigger DDL I may be able to do a quick(!) run
through and provide advise.

Cheers
Serge

PS: RI can also play in here.

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Serge Rielau wrote:
> Can you rpovide db2exfmt output?

Somewhat scrambled (for privacy reasons) output:
http://troels.arvin.dk/db/db2/long-c...-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-c...lain-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.import_helper_view
REFERENCING NEW AS n
FOR EACH ROW INSERT INTO sche_prod.target_table_1 (
primkeycol,
charcol1,
charcol2,
charcol3,
charcol4,
varcharcol1,
charcol5,
charcol6,
smallintcol1
) VALUES (
sche_util.trim_nullify(n.primkeycol),
RTRIM(LTRIM(n.charcol1)),
RTRIM(LTRIM(n.charcol2)),
RTRIM(LTRIM(n.charcol3)),
RTRIM(LTRIM(n.charcol4)),
RTRIM(LTRIM(n.varcharcol1)),
RTRIM(LTRIM(n.charcol5)),
RTRIM(LTRIM(n.charcol6)),
sche_util.trans_dok_bool(n.smallintcol1)
);

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.dk>
http://troels.arvin.dk/

I see two triggers....you only posted one.
$TRIGGER$(SCHE_UTIL.TRIG_IMPORT_VIEW_1),
$TRIGGER$(SCHE_PROD.TRIG_TARGET_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

Earlier, I wrote:

> The process takes a long time -- which is expected, to a certain degree.
> However, when I do a "list applications show detail" I consistently see
> that Status=Compiling is prevailing for a long time, i.e. more than
> ½hours.
[...]
> It also seems that application status jumps directly from "Compiling" to
> "Commit Active". I'm not sure about this, though (I haven't monitored
> this aspect closely yet).

It turns out that the application status flip-flops between "Compiling"
and "UOW Executing", with "Compiling" dominating. At some point, status
changes to "Commit active".

Is the flip-flopping normal for a long-running query? I would assume
a sequence of Compiling->Executing->Commit. But perhaps this is not
the case?

--
Regards,
Troels Arvin <troels@arvin.dk>
http://troels.arvin.dk/