SQL Stored Procedure Issue with a DATE Variable

[Fin]

Using DB2 Express-C 10.1.2 LUW on Win 7 Pro SP1 both 64-Bit, I have the below listed example portion of a SP.

The INSERT statement using the variable V_ACT_DATE that is input at the time of the call statement ie: CALL TEMP_PROC('05/07/2013');

The problem appears to some form of DATE conversion, as if I hard code the date by replacing V_ACT_DATE in the select statement with the actual date ie: '05/07/2013' all is fine. However if I use the variable name in it's place ie: V_ACT_DATE (as written in the code) then all hell breaks loose. The SP finishes successfully, but instead of taking 10 secs to run, it takes 7 minutes and hammers the hell out of my SSD in the TMPDB2ADMIN table space writing over 50gb of temp data to insert some 350k records.

If I run just the select statement query manually using '05/07/2013' it takes mere seconds to run, and barely registers on process explorer, but when the SP is using the variable V_ACT_DATE in the select statement, the CPU hits max, io writes go berserk at 100mb per sec.

I've had a look at the query plan and the manual select uses 2532.57 timerons and completes in 1.5 secs producing 367k records.

The process is designed to report on rules that are following each other on successive dates, and have historically done so more than once. So further processing occurs after this chunk of code to count that dates inserted.

My main problem of course is why is DB2 processing the INSERT statement differently when using '05/07/2013' and V_ACT_DATE which is set at runtime to the same value ?

Many thanks for any help.
Regards Fin.
--============================
CREATE OR REPLACE PROCEDURE TEST_PROC(V_ACT_DATE DATE )

LANGUAGE SQL
MODIFIES SQL DATA
DETERMINISTIC
BEGIN

DECLARE V_INT_DATE VARCHAR(8);
DECLARE INS_MSGS1 VARCHAR(100);
DECLARE INS_MSGS2 VARCHAR(100);
DECLARE INS_MSGS3 VARCHAR(100);

DECLARE GLOBAL TEMPORARY TABLE SESSION.MSGS
( MSG VARCHAR(1024),
TME VARCHAR(20),
INT_DAT INTEGER,
DATE_DATA DATE
)
ON COMMIT PRESERVE ROWS
NOT LOGGED
ON ROLLBACK PRESERVE ROWS
WITH REPLACE
IN TMPDB2ADMIN ;

DECLARE GLOBAL TEMPORARY TABLE SESSION.FOLLOWING_RL_DATES
( D1_RULE_ID INTEGER NOT NULL,
D1_DATE DATE NOT NULL,
D2_RULE_ID INTEGER NOT NULL,
D2_DATE DATE NOT NULL)
ON COMMIT PRESERVE ROWS
NOT LOGGED
ON ROLLBACK PRESERVE ROWS
WITH REPLACE
IN TMPDB2ADMIN ;

SET INS_MSGS1 = 'INSERT INTO SESSION.MSGS (MSG,TME) VALUES ( ?, ? )';
SET INS_MSGS2 = 'INSERT INTO SESSION.MSGS (MSG,TME,INT_DAT) VALUES ( ?, ?, ? )';
SET INS_MSGS3 = 'INSERT INTO SESSION.MSGS (MSG,TME,DATE_DATA) VALUES ( ?, ?, ? )';

PREPARE P_INS_MSGS1 FROM INS_MSGS1;
PREPARE P_INS_MSGS2 FROM INS_MSGS2;
PREPARE P_INS_MSGS3 FROM INS_MSGS3;

EXECUTE P_INS_MSGS1 USING 'Process Started: ',TIME(CURRENT TIMESTAMP);

SET V_INT_DATE = CHAR((VALUES INT(V_ACT_DATE)));

EXECUTE P_INS_MSGS1 USING 'Date Records Started: ',TIME(CURRENT TIMESTAMP);

--SET CURRENT QUERY OPTIMIZATION 5;--

INSERT INTO SESSION.FOLLOWING_RL_DATES( D1_RULE_ID, D1_DATE, D2_RULE_ID, D2_DATE)
SELECT A1.RULE_ID, A1.ACT_DATE, RL2.RULE_ID, RL2.ACT_DATE
FROM RULES_TABLE AS RD2
INNER JOIN BODY_DATES AS A2 ON A2.BODY_ID = RD2.BODY_ID AND A2.ACT_DATE = V_ACT_DATE AND RD2.IMPROVEMENT > 0
INNER JOIN RULE_DATES AS RL2 ON RL2.RULE_ID = RD2.RULE_ID AND RL2.ACT_DATE < V_ACT_DATE AND RL2.OUTCOME = 'S'
INNER JOIN RULE_DATES AS RL1 ON RL1.ACT_DATE = V_ACT_DATE AND RL1.OUTCOME = 'S'
INNER JOIN RULE_DATES AS A1 ON A1.RULE_ID = RL1.RULE_ID AND A1.OUTCOME = 'S'
INNER JOIN PERIOD_TABLE AS PP ON A1.ACT_DATE = PP.ACT_DATE AND PP.D2_DATE = RL2.ACT_DATE;

CREATE INDEX SESSION.R2_FL_RL_DATES_01 ON SESSION.FOLLOWING_RL_DATES
(D1_RULE_ID, D2_RULE_ID )
COMPRESS YES ALLOW REVERSE SCANS;

CALL SYSPROC.ADMIN_CMD('RUNSTATS ON TABLE SESSION.FOLLOWING_RL_DATES AND INDEXES ALL');
COMMIT;


CALL SYSPROC.ADMIN_CMD('
EXPORT TO C:\TEMP\ACTIVE_RULES\LOGS\FOLLOWING_RULES_LOG_'||V_INT_DATE||'.CSV OF DEL
MODIFIED BY NOCHARDEL
VALUES ('' MSG'', '' TME'', ''INT_DAT'', ''DATE_DATA'')
UNION
SELECT MSG, TME, CHAR(INT_DAT), CHAR(DATE_DATA)
FROM SESSION.MSGS
ORDER BY 2,1');

EXECUTE P_INS_MSGS1 USING 'Process Complete ',TIME(CURRENT TIMESTAMP);

COMMIT;
END !
--====================================

[Ian]

On Tuesday, May 7, 2013 5:35:03 PM UTC-7, Fin wrote:

> Using DB2 Express-C 10.1.2 LUW on Win 7 Pro SP1 both 64-Bit, I have
> the below listed example portion of a SP.

> The INSERT statement using the variable V_ACT_DATE that is input at
> the time of the call statement ie: CALL TEMP_PROC('05/07/2013');

> The problem appears to some form of DATE conversion, as if I hard
> code the date by replacing V_ACT_DATE in the select statement with
> the actual date ie: '05/07/2013' all is fine. However if I use the
> variable name in it's place ie: V_ACT_DATE (as written in the code)
> then all hell breaks loose. The SP finishes successfully, but
> instead of taking 10 secs to run, it takes 7 minutes and hammers the
> hell out of my SSD in the TMPDB2ADMIN table space writing over 50gb
> of temp data to insert some 350k records.

I suspect the issue is that when you hard code the date into the
stored procedure, DB2 is able to use distribution statistics (assuming
you've collected them in your RUNSTATS) to choose the query plan, and
the dates you typically run with provide have very low selectivity.

With this knowledge, the optimizer chooses a very effective access
plan when it knows the value of V_ACT_DATE.

However, when you use the variable, the optimizer does not know what
the value of the variable will be at runtime, so it makes an
assumption about how much data will qualify, and then builds the
access plan based on that assumption. If the assumption is wrong
(meaning it thinks a LOT more data will qualify than actually does),
then the access plan may be very inefficient (as you've found).

Because your INSERT statement is a static statement (i.e. it's
compiled when the procedure is created), the best way to resolve this
is to set the REOPT ONCE or REOPT ALWAYS options. These tell DB2 to
recompile the access plan with the actual value of any parameters at
runtime.

You can do this either by rebinding the existing stored procedure
(using the REBIND_ROUTINE_PACKAGE stored procedure with the
appropriate REOPT option), or you can call the stored procedure
SYSPROC.SET_ROUTINE_OPTS('REOPT ONCE') and then recreate your stored
procedure.

[Fin]

Thanks Ian, that did the trick. A further question if I may, I have been perhaps mistakenly using db2rbind following runstats, reorgs, table/index maintenance etc, but now, having read your response and some further reading, understand that was perhaps NOT the thing to do and I should have been rebinding using REBIND_ROUTINE_PACKAGE for existing packages and SET_ROUTINE_OPTS for any new ones ?

I have now set via db2set the registry variable : DB2_SQLROUTINE_PREPOPTS=BLOCKING ALL DEGREE ANY INSERT BUF REOPT ONCE
Having now set that and restarting the instance, what am I supposed to do now to pick up those new values ? Reissue a db2rbind ?

So what is the appropriate action following runstats, reorgs, table/index maintenance etc ? I wrote a quick command to do so :

SELECT 'CALL SYSPROC.REBIND_ROUTINE_PACKAGE (''P'','||PROCNAME||''',''REOPT ONCE'');'
FROM SYSCAT.PROCEDURES
WHERE PROCSCHEMA = 'DB2ADMIN';

However issuing CALL SYSPROC.REBIND_ROUTINE_PACKAGE ('F','func_name','REOPT ONCE'); fails with an SQL0443N error for any function. Procedures worked fine.

Many thanks again for your help. Regards, Fin.