DB2 Express-C on XP sp3.

I have a standalone DB with no online users [just me] that I use for
processing association data mining rules. When I get the rules from
the engine I process them through various SPs to give them dates, text
values, and parsing the rule bodies etc. All this works very well in
the DB2 environment. Having got dates for the various rules, I then
need to process historic data on such rules seeing which work together
etc so I need to process historic dates.

So, imagine I have the following SP. I have a function that returns a
table with various data that requires RULE_ID, ACT_DATE as inputs. It
will return some 20 rows of 5 fields that I need to insert into a
table for each rule. I have 2.2m rules to process in this manner. Now
the rules were mined in 3 separate phases using different start dates
over different periods so the function is written using the DATE input
to restrict results based on said date. I have restricted the cursor
to 100 rows and to the mining_period of 52 for testing only.

CREATE PROCEDURE INS_SUM_CONF()
LANGUAGE SQL
MODIFIES SQL DATA
NOT DETERMINISTIC
BEGIN

DECLARE V_RULE_ID INTEGER;
DECLARE V_ACT_DATE DATE;
DECLARE V_MP SMALLINT;
DECLARE V_COMMIT_CNT INTEGER;
DECLARE STM_INS VARCHAR(1000);
DECLARE STM_INS52 VARCHAR(1000);
DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
DECLARE V_EXIST INTEGER;
DECLARE rec_exist CONDITION FOR SQLSTATE '23505';

DECLARE RID_CURSOR CURSOR WITH HOLD FOR
SELECT RULE_ID, MINING_PERIOD
FROM RULE_DATA
WHERE MINING_PERIOD = 52
FETCH FIRST 100 ROWS ONLY
FOR FETCH ONLY WITH UR;

DECLARE CONTINUE HANDLER FOR rec_exist SET V_EXIST = 1;

-- SET V_ACT_DATE = '01/02/2001';
-- SET V_ACT_DATE = '04/20/2006';
SET V_ACT_DATE = (SELECT ACT_DATE FROM RULE_DATES WHERE ACT_DATE =
'01/02/2001');

-- SET STM_INS = 'INSERT INTO CONF_HIST (ACT_DATE, RULE_ID, CNT,
SUM_DIFF, SUM_CONF) SELECT ACT_DATE, RULE_ID, CNT, SUM_DIFF, SUM_CONF
FROM TABLE(GET_SUM_CONF_HIST(?,?))';
SET STM_INS = 'INSERT INTO CONF_HIST SELECT * FROM
TABLE(GET_SUM_CONF_HIST( ? , ''01/02/2001'' ))';
SET STM_INS52 = 'INSERT INTO CONF_HIST SELECT * FROM
TABLE(GET_SUM_CONF_HIST( ? , ''04/20/2006'' ))';

PREPARE P_STM_INS FROM STM_INS;
PREPARE P_STM_INS52 FROM STM_INS52;
OPEN RID_CURSOR;
FETCH RID_CURSOR INTO V_RULE_ID, V_MP;
WHILE (SQLSTATE = '00000')
DO
IF V_MP = 52 THEN EXECUTE P_STM_INS52 USING V_RULE_ID ;
ELSE EXECUTE P_STM_INS USING V_RULE_ID ;
END IF;

IF V_COMMIT_CNT > 1000 THEN
COMMIT;
SET V_COMMIT_CNT = 0;
ELSE SET V_COMMIT_CNT = V_COMMIT_CNT + 1;
END IF;
FETCH RID_CURSOR INTO V_RULE_ID, V_MP;
END WHILE;
COMMIT;
CLOSE RID_CURSOR WITH RELEASE;
COMMIT;
END !

Now the problem. I had to write two [2] separate STM_INS statements
because DB2 would not let me set a parameter marker with the ACT_DATE
date field and V_ACT_DATE variable. When I say it wouldn't let me that
is not strictly correct, it would work fine but when running the SP it
would hang for over 5 mins PER RULE_ID before completing successfully.
NOTE: It did not fail, just hung with CPU usage ticking over at 5%
like it was disc bound or something. No errors, no messages in
db2diag.log, nothing.

So when I rewrote the above procedure to use the DATE as hard coded in
the STM_INS rather than the variable V_ACT_DATE, now it blitzes 1000
records in no time at all. My question is WHY ?

I have removed the few lines to set V_ACT_DATE based on MINING_PERIOD
of the cursor mining_period because when I EXECUTE P_STM_INS USING
V_RULE_ID, V_ACT_DATE the entire process bogs down but no errors, no
indication of what is wrong, just hangs for ages. I tried setting
V_ACT_DATE several different ways, manually, by select statement,
nothing seems to work til I hard coded into STM_INS. The table
function GET_SUM_CONF_HIST works just fine when manually selected,
it's almost as if it is getting confused when offering the V_ACT_DATE
variable, but it still works, EVENTUALLY without error. Now if it had
failed, that would be one thing, but it doesn't, just takes forever to
complete even a single rule_id.

I'm very confused as to this behavior and would be most appreciative
is someone could please point out exactly what I am doing wrong.

Many thanks, Fin.

Your SP looks like test purpose only.
So, I couldn't see your real requirements of the SP.

Hore are some questions...

1) What relationship is there between RULE_DATA table and RULE_DATES
table?
You got RULE_ID and MINING_PERIOD from RULE_DATA
and V_ACT_DATE from RULE_DATES.

2) What is the role of mining_period(smallint) in the SP?
Is it to choose V_ACT_DATE? But, between what values?

3) COMMIT in WHILE loop would never be executed.
Because, you fetched 100 rows in RID_CURSOR
and V_COMMIT_CNT was incremented by 1 for every FETCH from
RID_CURSOR.
Then V_COMMIT_CNT would be less than 100.
While you COMMITed, if V_COMMIT_CNT > 1000.

Anyway, I thoght that the body of your sample SP would be simplified
to one statement, like this...

CREATE PROCEDURE INS_SUM_CONF()
LANGUAGE SQL
MODIFIES SQL DATA
NOT DETERMINISTIC
BEGIN

INSERT INTO CONF_HIST
SELECT gsch.*
FROM (SELECT RULE_ID, MINING_PERIOD AS v_mp
FROM RULE_DATA
WHERE MINING_PERIOD = 52
FETCH FIRST 100 ROWS ONLY
FOR FETCH ONLY WITH UR
) rd
, LATERAL
(SELECT *
FROM TABLE
(GET_SUM_CONF_HIST( rd.rule_id
, CASE
WHEN rd.v_mp = 52 THEN
DATE('04/20/2006')
ELSE DATE('01/02/2001')
END
)
)
) gsch;

END !



I want to test by executing sub-select in INSERT statement(like the
following one)
and want to try by changing something in "WHERE MINING_PERIOD = 52".

SELECT rd.RULE_ID, rd.MINING_PERIOD
, COUNT(*)
FROM (SELECT RULE_ID, MINING_PERIOD AS v_mp
FROM RULE_DATA
WHERE MINING_PERIOD = 52
FETCH FIRST 100 ROWS ONLY
FOR FETCH ONLY WITH UR
) rd
, LATERAL
(SELECT *
FROM TABLE
(GET_SUM_CONF_HIST( rd.rule_id
, CASE
WHEN rd.v_mp = 52 THEN
DATE('04/20/2006')
ELSE DATE('01/02/2001')
END
)
)
) gsch
GROUP BY
rd.RULE_ID, rd.MINING_PERIOD
;

Thanks for responding Tonkuma, appreciate it.

Yes, but it has the important bits.

RULE_DATA is unique by rule_id, had 2.2m records and has approx 20
fields but is joined to resulting RULE_DATES by the same RULE_ID.
Other than that, no relationship. When queried, I simply INNER JOIN
the two to get the dates for specific rules.

Yes, is that a problem ? Rule_dates contains unique single business
date records for entire
dataset going back 20 years. It also allows days 2-5 calculation for
sequential mining
when rule_body is say day1 and rule_head is say day2.

MINING_PERIOD determines the period [# months] over which the
association rule was mined so I can
determine what V_ACT_DATE [start date] to use in the date calculation.

There are 2.2m rule_id records, I only fetched first 100 rows only to
test. During testing I actually set it
to WHERE RULE_ID = X so it would only pull 1 single record. That was
so I could see what was going on
when it was taking so damn long to complete. The auto commit will trip
just fine when running the
complete dataset.

The real issue, is the problem ONLY occurs when I assign a Parameter
Marker to the variable V_ACT_DATE.
I really didn't want to have to hard code the date value is all, just
in case I had to use MANY different date values
with many different MINING_PERIODS values. It works just fine as is
when the STM_INS is hard coded with a date,
but takes an inordinate amount of time when the using V_ACT_DATE, just
doesn't make any sense that that would be
the case.

If the Prepare is

EXECUTE P_STM_INS USING V_RULE_ID it works fine, however when
EXECUTE P_STM_INS USING V_RULE_ID, V_ACT_DATE it does not, it doesn't
fail but take 5 mins PER RECORD.

That is where I am having issues.

Your SQL will probably work just fine but I have 2.2m rules to process
so I need the auto commit logic or will run into issues
with log files etc. Considering the function returns some 20-30 rows
per rule_id we are going to end up with in excess of 60m
rows in the resulting table. My database is simply not set up with
sufficient resources to handle an insert of such magnitude.

I was just interested in why the problem with V_ACT_DATE and Parameter
Markers is all. Just seems to me almost
like a Bug in DB2 with the way it was behaving. I have used similar
logic before with dates in parameter markers and
never had an issue, this is the first time I have struck this problem.

OK, so here's more to the mystery.

It doesn't so much seem to be a problem with the SP but rather the
calling of the function. I have done some more testing
and when calling the function with a normal select as in 'SELECT *
FROM TABLE(GET_SUM_CONF_HIST(135516,'01/02/2001'))'
it works fine from say clp.

When I change the date from '01/02/2001' to something out of range
that will produce a null result it works fine too from the SP, nice
and fast response.

So this begs the question, why from clp it works and gives me 16
records in .5 sec does it take 5 mins to give a result when
called from a SP with a Parameter Marker ?

Did you confirmed that by tests with completely same SPs except the
EXECUTE statements?

A reason that I want to see so strict evidence was...
You faced the fact which was something out of your thought,
so real cause of problem may(or may not) be different from your
assumption.

For example, you can compare SPs of which differences were only
executed prepared statements.
Something like...
1) Add
DECLARE STM_INS_2p VARCHAR(1000);
DECLARE STM_INS52_2p VARCHAR(1000);

SET STM_INS_2p = 'INSERT INTO CONF_HIST SELECT * FROM
TABLE(GET_SUM_CONF_HIST( ? , ? ))';
SET STM_INS52_2p = 'INSERT INTO CONF_HIST SELECT * FROM
TABLE(GET_SUM_CONF_HIST( ? , ? ))';

PREPARE P_STM_INS_2p FROM STM_INS_2p;
PREPARE P_STM_INS52_2p FROM STM_INS52_2p;

to the SP just same as in your original post,

2) then compare execution time of
(SP1)
IF V_MP = 52 THEN EXECUTE P_STM_INS52 USING
V_RULE_ID ;
ELSE EXECUTE P_STM_INS USING V_RULE_ID ;
END IF;

and

(SP2)
IF V_MP = 52 THEN EXECUTE P_STM_INS52_2p USING
V_RULE_ID, V_ACT_DATE ;
ELSE EXECUTE P_STM_INS_2p USING V_RULE_ID,
V_ACT_DATE ;
END IF;

3) Then conapre inserted rows in CONF_HIST, after executing SP1 and
SP2.

Yes I did.

I agree, had me stumped for days until I started getting desperate and
changing
things I would not normally consider.


This will not work as you get a SQL0313N number of variables not = to
those required.
There are 2 parameter markers in the sql statement.

This takes over 5 mins.

So, with 1 single rule in the select cursor, using:

SET STM_INS_2p = 'INSERT INTO CONF_HIST SELECT * FROM
TABLE(GET_SUM_CONF_HIST( ? , ? ))';
SET STM_INS52_2p = 'INSERT INTO CONF_HIST SELECT * FROM
TABLE(GET_SUM_CONF_HIST( ? , ''01/02/2001'' ))';

PREPARE P_STM_INS_2p FROM STM_INS_2p;
PREPARE P_STM_INS52_2p FROM STM_INS52_2p;

SET V_ACT_DATE = '01/02/2001';

(SP1)
IF V_MP = 52 THEN EXECUTE P_STM_INS52 USING
V_RULE_ID ;
ELSE EXECUTE P_STM_INS52 USING V_RULE_ID ;
END IF;
This takes .5 sec.

(SP2)
IF V_MP = 52 THEN EXECUTE P_STM_INS USING V_RULE_ID,
V_ACT_DATE ;
ELSE EXECUTE P_STM_INS USING V_RULE_ID, V_ACT_DATE ;
END IF;
This takes 5 mins !.

Both result in exactly the same data in the insert table. 11 rows.

So adding the V_ACT_DATE to the execute is causing lord knows what to
happen, I have absolutely no idea.
Completely mystified to be honest.

I saw in your original SP that STM_INS and STM_INS52 took one
parameter marker, like this...

SET STM_INS = 'INSERT INTO CONF_HIST SELECT * FROM
TABLE(GET_SUM_CONF_HIST( ? , ''01/02/2001'' ))';
SET STM_INS52 = 'INSERT INTO CONF_HIST SELECT * FROM
TABLE(GET_SUM_CONF_HIST( ? , ''04/20/2006'' ))';


1) How about casting second parameter?

SET STM_INS_2p = 'INSERT INTO CONF_HIST SELECT * FROM
TABLE(GET_SUM_CONF_HIST( ? , CAST(? AS DATE) ))';


2) Are there more than two GET_SUM_CONF_HIST functions, one of them
takes second parameter as DATE and another takes second parameter as
VARCHAR or CHAR.

Sorry, my mistake confusing you, I was using your example above.

Works, takes 5 mins still.

Nope, 1 single function only with inputs INTEGER and DATE for
v_rule_id and v_act_date respectively.

Function works just fine with SELECT * FROM
TABLE(GET_SUM_CONF_HIST(135516,'01/02/2001')) manually. Now that said,
the function
itself is expensive, 15600 timerons. Nothing I can do about that I am
afraid. Now whether the optimizer is trying to do something with it
when it runs from a SP rather than say clp, I don't know. I can only
assume this as the strange thing is that it still works, just takes
forever. I have tried setting
QUERYOPT via CALL SYSPROC.SET_ROUTINE_OPTS to different values, with
the same result lengthy result.