Hello-
I am having a hard time getting a stored procedure to work based on dates. Many time we have need to get data cleanced out based on date ranges and this would really help. Any help appreciated.

Env: Windows DB2 V9.5

Here is the script i was having issues with

CREATE PROCEDURE DELCRT_ROWS
(IN tabschema VARCHAR(128), IN tabname VARCHAR(128), IN predicate VARCHAR(1000), IN date1 DATE, IN date2 DATE, IN commitcount INTEGER)
BEGIN
DECLARE SQLCODE INTEGER;
DECLARE txt VARCHAR(10000);
DECLARE stmt STATEMENT;
SET txt = 'DELETE FROM (SELECT 1 FROM "' || tabschema || '"."' || tabname || '" WHERE '
|| predicate || ' BETWEEN ' || date1 || ' AND ' || date2 || ' FETCH FIRST ' || RTRIM(CHAR(commitcount)) || ' ROWS ONLY) AS D';
PREPARE stmt FROM txt;
l: LOOP
EXECUTE stmt;
IF SQLCODE = 100 THEN LEAVE l; END IF;
COMMIT;
END LOOP;
END
@

The error I get is below
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0440N No authorized routine named "||" of type "FUNCTION" having
compatible arguments was found. LINE NUMBER=7. SQLSTATE=42884

SQL0440N No authorized routine named "||" of type "FUNCTION " having compatible arguments was found.

Different idea: how about using range-partitioned tables and detaching whole partitions? Then you could drop the detached partition.

To answer your question: you try to concatenate a string with a date. That won't work and you should cast the date to a string - as you already do with the commitcount.

Dates are not strings; you cannot concatenate dates. In addition to this, you are missing some quotation marks.

Thanks!!!

I am still getting an error after dates are cast to character. Are the dates parsed differently when converted to text; is there a way to see this?

Modified script:

CREATE PROCEDURE DELCRT_ROWS
(IN tabschema VARCHAR(128), IN tabname VARCHAR(128), IN predicate VARCHAR(1000), IN date1 DATE, IN date2 DATE, IN commitcount INTEGER)
BEGIN
DECLARE SQLCODE INTEGER;
DECLARE txt VARCHAR(10000);
DECLARE stmt STATEMENT;
SET txt = 'DELETE FROM (SELECT 1 FROM "' || tabschema || '"."' || tabname || '" WHERE '
|| predicate || ' BETWEEN ' || RTRIM(CHAR(date1)) || ' AND ' || RTRIM(CHAR(date2)) || ' FETCH FIRST ' || RTRIM(CHAR(commitcount)) || ' ROWS ONLY) AS D';
PREPARE stmt FROM txt;
l: LOOP
EXECUTE stmt;
IF SQLCODE = 100 THEN LEAVE l; END IF;
COMMIT;
END LOOP;
END
@

Error message:

CALL U261251.DELCRT_ROWS('U27332','AUD_TRL_CNTL_TOT','P ROD_DT','2006-10-01','2006-10-31', 10)
SQL0401N The data types of the operands for the operation "BETWEEN" are not
compatible. SQLSTATE=42818

Please allow me to quote myself:

Put the quotes around the dates.. now getting a different message

Error

CALL U261251.DELCRT_ROWS('U27332','AUD_TRL_CNTL_TOT','P ROD_DT','2006-10-01','2006-10-31', 10)
SQL0180N The syntax of the string representation of a datetime value is
incorrect. SQLSTATE=22007

Script
CREATE PROCEDURE DELCRT_ROWS
(IN tabschema VARCHAR(128), IN tabname VARCHAR(128), IN predicate VARCHAR(1000), IN date1 DATE, IN date2 DATE, IN commitcount INTEGER)
BEGIN
DECLARE SQLCODE INTEGER;
DECLARE txt VARCHAR(10000);
DECLARE stmt STATEMENT;
SET txt = 'DELETE FROM (SELECT 1 FROM "' || tabschema || '"."' || tabname || '" WHERE '
|| predicate || ' BETWEEN ' || ''' || RTRIM(CHAR(date1) || ''' || ' AND ' || '''
|| RTRIM(CHAR(date2) || ''' || ' FETCH FIRST ' || RTRIM(CHAR(commitcount)) || ' ROWS ONLY) AS D';
PREPARE stmt FROM txt;
l: LOOP
EXECUTE stmt;
IF SQLCODE = 100 THEN LEAVE l; END IF;
COMMIT;
END LOOP;
END
@

You should debug your procedure by returning the statement being generated as OUT parameter. Then you can have a look at the statement and verify that you construct the right thing.

p.s: What I find strange is this:
I guess that your "predicate" should rather be an "expression". Besides, doing what you do is a begging for SQL injections, which is its own class of security problems.

' BETWEEN ' || '''' || RTRIM(CHAR(date1)) || '''' || ' AND ' || '''' || RTRIM(CHAR(date2)) || ''''

Great. It worked perfect. Thanks!!!

I recommend that you learn how to debug such things yourself in the future. You can either return the SQL statement via an out parameter (as I suggested before), or you write it to a log file (http://www.ibm.com/developerworks/da...03stolze.html).