Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2007
    Posts
    56

    Unanswered: Native SQL procedures

    DB2: V9.1 z/OS

    Using SPUFI, I am trying to create a sample stored procedure from Chapter 15of "DB2 9 for z/OS Stored Procedures: Through the CALL and Beyond"

    I receive the following error:

    DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL ";". SOME SYMBOLS THAT MIGHT
    BE LEGAL ARE: <END-OF-STATEMENT>
    DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE

    The SQL Format on SPUFI defaults panel is set correctly.

    Does anyone have an idea as to what may be wrong?

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    did you set your statement terminator to something other than ; since you presumably have one in your procedure?
    Dave

  3. #3
    Join Date
    Aug 2007
    Posts
    56
    Yes, I used # as was shown in the example.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by citi View Post
    Yes, I used # as was shown in the example.
    What example?

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Aug 2007
    Posts
    56
    CREATE PROCEDURE MEDIAN_RESULT_SET (OUT medianSalary DECIMAL(7,2))
    VERSION MEDIAN_V1
    LANGUAGE SQL
    READS SQL DATA
    DYNAMIC RESULT SETS 1
    BEGIN
    DECLARE v_numRecords INTEGER DEFAULT 1;
    DECLARE v_counter INTEGER DEFAULT 0;
    DECLARE c1 CURSOR FOR
    SELECT salary FROM staff ORDER BY salary;
    DECLARE c2 CURSOR WITH RETURN FOR
    SELECT name, job, salary
    FROM staff
    WHERE salary > medianSalary
    ORDER BY salary;
    DECLARE EXIT HANDLER FOR NOT FOUND
    SET medianSalary = 0;
    SELECT COUNT(*) INTO v_numRecords FROM STAFF;
    OPEN c1;
    WHILE v_counter < (v_numRecords / 2 + 1) DO
    FETCH c1 INTO medianSalary;
    SET v_counter = v_counter + 1;
    END WHILE;
    CLOSE c1;
    OPEN c2;
    END#

  6. #6
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    citi, Did you change your SPUFI defaults for the SQL Terminator to #? This is done is SPUFI with option 5 Change Defaults.

    A better way would be to use the SET TERMINATOR statement:

    --#SET TERMINATOR #

    Type it just as like that. The -- are NOT comments. It is also good practice to set it back to ; with:

    --#SET TERMINATOR ;

    For your example:

    Code:
    --#SET TERMINATOR #
    
    CREATE PROCEDURE MEDIAN_RESULT_SET (OUT medianSalary DECIMAL(7,2))
    VERSION MEDIAN_V1
    LANGUAGE SQL
    READS SQL DATA
    DYNAMIC RESULT SETS 1
    BEGIN
    DECLARE v_numRecords INTEGER DEFAULT 1;
    DECLARE v_counter INTEGER DEFAULT 0;
    DECLARE c1 CURSOR FOR
    SELECT salary FROM staff ORDER BY salary;
    DECLARE c2 CURSOR WITH RETURN FOR
    SELECT name, job, salary
    FROM staff
    WHERE salary > medianSalary
    ORDER BY salary;
    DECLARE EXIT HANDLER FOR NOT FOUND
    SET medianSalary = 0;
    SELECT COUNT(*) INTO v_numRecords FROM STAFF;
    OPEN c1;
    WHILE v_counter < (v_numRecords / 2 + 1) DO
    FETCH c1 INTO medianSalary;
    SET v_counter = v_counter + 1;
    END WHILE;
    CLOSE c1;
    OPEN c2;
    END#
    
    --#SET TERMINATOR ;

  7. #7
    Join Date
    Aug 2007
    Posts
    56
    Yes, I had changed sql terminator to #.

    Using the --#SET parameter worked. I did get a rebind error but will work with the system DBA to straigten out the error.

    Thank you for your help.

Posting Permissions

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