Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2016
    Posts
    2

    Post Unanswered: DB2 Stored Procedure Failing to Commit

    Hi, Can someone please help me with this stored procedure, I am getting following error when trying to create it. Please let me know if you need any details. Thanks

    SQL0104N An unexpected token "<cursor declaration>" was found following "".
    Expected tokens may include: "<SQL statement>". LINE NUMBER=11.
    SQLSTATE=42601

    CREATE PROCEDURE PURGETASKS(IN maxDeleteCount INTEGER,IN maxCommitCount INTEGER)
    LANGUAGE SQL
    BEGIN ATOMIC
    DECLARE taskId INTEGER;
    DECLARE totalDeletedCount INTEGER DEFAULT 0;
    DECLARE totalUncommittedRows INTEGER DEFAULT 0;
    DECLARE EOF INT DEFAULT 0;
    DECLARE rowsFetched BIGINT;
    LOOP:
    WHILE totalDeletedCount < maxDeleteCount
    DO
    EXEC SQL DECLARE myCursor CURSOR FOR
    SELECT TASK_ID FROM VIRTUSER.LSW_TASK
    WHERE close_datetime < CURRENT DATE AND status = 32 FETCH FIRST 5000 ROWS ONLY;

    DECLARE CONTINUE HANDLER FOR NOT FOUND
    SET EOF = 1;

    IF EOF = 1 THEN
    LEAVE LOOP;
    END IF;

    SET STMT = 'CALL VIRTUSER.LSW_ERASE_TASK(?)';
    PREPARE S1 FROM STMT;
    OPEN myCursor;
    SET rowsFetched = CURSOR_ROWCOUNT(myCursor);
    IF rowsFetched != maxCommitCount AND rowsFetched > 0 THEN
    SET maxCommitCount = rowsFetched;
    END IF;
    WHILE EOF = 0 DO
    FETCH FROM myCursor INTO taskId;
    EXECUTE S1 USING taskId;
    SET totalUncommittedRows = totalUncommittedRows + 1;
    SET totalDeletedCount = totalDeletedCount + 1;
    IF totalUncommittedRows >= maxCommitCount THEN
    COMMIT:
    SET totalUncommittedRows = 0;
    END IF;
    END WHILE;
    CLOSE myCursor;
    END WHILE LOOP;
    END
    @

  2. #2
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi,

    Inlined compound statement (starts with BEGIN ATOMIC) doesn't allow many things in comparison to compiled compound (starts with BEGIN).

    Inlined:
    http://www.ibm.com/support/knowledge.../r0004240.html

    Compiled:
    http://www.ibm.com/support/knowledge.../r0004239.html
    Regards,
    Mark.

  3. #3
    Join Date
    Jun 2016
    Posts
    2

    Post DB2 Stored Procedure Failing to Commit

    Thanks Mark, but I am still receiving error:
    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0104N An unexpected token "SQL" was found following "DeleteCount DO
    EXEC". Expected tokens may include: ":". LINE NUMBER=12. SQLSTATE=4260

    Any inputs would be helpful, i have removed atomic from begin(thats the only change I have done)

    CREATE PROCEDURE PURGETASKS(IN maxDeleteCount INTEGER,IN maxCommitCount INTEGER)
    LANGUAGE SQL
    BEGIN
    DECLARE taskId INTEGER;
    DECLARE totalDeletedCount INTEGER DEFAULT 0;
    DECLARE totalUncommittedRows INTEGER DEFAULT 0;
    DECLARE EOF INT DEFAULT 0;
    DECLARE rowsFetched BIGINT;
    LOOP:
    WHILE totalDeletedCount < maxDeleteCount
    DO
    EXEC SQL DECLARE myCursor CURSOR FOR
    SELECT TASK_ID FROM VIRTUSER.LSW_TASK
    WHERE close_datetime < CURRENT DATE AND status = 32 FETCH FIRST 5000 ROWS ONLY;

    DECLARE CONTINUE HANDLER FOR NOT FOUND
    SET EOF = 1;

    IF EOF = 1 THEN
    LEAVE LOOP;
    END IF;

    SET STMT = 'CALL VIRTUSER.LSW_ERASE_TASK(?)';
    PREPARE S1 FROM STMT;
    OPEN myCursor;
    SET rowsFetched = CURSOR_ROWCOUNT(myCursor);
    IF rowsFetched != maxCommitCount AND rowsFetched > 0 THEN
    SET maxCommitCount = rowsFetched;
    END IF;
    WHILE EOF = 0 DO
    FETCH FROM myCursor INTO taskId;
    EXECUTE S1 USING taskId;
    SET totalUncommittedRows = totalUncommittedRows + 1;
    SET totalDeletedCount = totalDeletedCount + 1;
    IF totalUncommittedRows >= maxCommitCount THEN
    COMMIT:
    SET totalUncommittedRows = 0;
    END IF;
    END WHILE;
    CLOSE myCursor;
    END WHILE LOOP;
    END
    @

  4. #4
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    There is a number of problems in the code.

    1. There is no such an expression "EXEC SQL" in a compound statement. Remove this substring from the code.
    2. You can't declare a cursor or a handler wherever you want in the code. You have to declare them before the first SQL-procedure-statement. The sequence of declarations is ordered. See the structure of the compiled compound statement at the link above.
    3. What's the ':' sign after the COMMIT statement? Must be ';'.
    4. I didn't try to understand the logic, but be aware of the following: if you issue COMMIT, then 'WITHOUT HOLD' cursors are closed.
    Regards,
    Mark.

Tags for this Thread

Posting Permissions

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