Results 1 to 10 of 10
  1. #1
    Join Date
    Mar 2009
    Posts
    16

    Unanswered: Stored Procedure for row delete date criteria based

    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.

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Dates are not strings; you cannot concatenate dates. In addition to this, you are missing some quotation marks.
    ---
    "It does not work" is not a valid problem statement.

  4. #4
    Join Date
    Mar 2009
    Posts
    16
    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


  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by sujith_g

    I am still getting an error after dates are cast to character.
    Please allow me to quote myself:
    Quote Originally Posted by n_i
    In addition to this, you are missing some quotation marks.
    ---
    "It does not work" is not a valid problem statement.

  6. #6
    Join Date
    Mar 2009
    Posts
    16
    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
    @

  7. #7
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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:
    Code:
    predicate BETWEEN date1 AND date2
    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    ' BETWEEN ' || '''' || RTRIM(CHAR(date1)) || '''' || ' AND ' || '''' || RTRIM(CHAR(date2)) || ''''
    Last edited by tonkuma; 03-19-09 at 00:42.

  9. #9
    Join Date
    Mar 2009
    Posts
    16

    Thumbs up

    Great. It worked perfect. Thanks!!!

  10. #10
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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).
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •