Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2011
    Posts
    2

    Unanswered: Errors when creating stored procedure

    I am new to DB2 and am trying to create a simple SP that returns two datasets. I am getting a few errors when I try to run the SP. Here is the SP:

    Code:
    CREATE PROCEDURE TESTDB2.USP_SELECT_ALL_DPR_DEALER (IN dealer_ID VARCHAR(100))
    		  DYNAMIC RESULT SETS 1
              LANGUAGE SQL
              NOT DETERMINISTIC
              CALLED ON NULL INPUT
              MODIFIES SQL DATA
              FENCED
              COLLID TESTDB2
              WLM ENVIRONMENT TDB2ENV
              ASUTIME LIMIT 5000000
              RUN OPTIONS 'NOTEST(NONE,*,*,*)'
    ------------------------------------------------------------------------
    -- SQL Stored Procedure 
    ------------------------------------------------------------------------
    P1: BEGIN
    	-- Declare cursor
    	DECLARE dprCursor CURSOR WITH RETURN FOR
    		SELECT rt.dpr_no, rt.dol, rt.formal_name, rt.dmg_cd,
    			rt.dpr_sts_cd, rt.prob_desc_500, rt.arcv_indc
    		FROM bqadprrt as rt
    		WHERE rt.so_cd = dealer_ID
    		ORDER BY rt.dpr_no;
    		
    		SELECT vs.dpr_no, vs.veh_ser_no, vs.tso_split_no, vs.loc,
    			vs.meas_cd, vs.meas_cur, vs.cust_name_abbr,
    			vs.veh_base_mdl_no, vs.date_mfg_rlse
    		FROM bqadprvs as vs, (SELECT dpr_no FROM TestDB2.bqadprrt WHERE so_cd = dealer_ID) as tempDPRNum
    		WHERE vs.dpr_no = tempDPRNum.dpr_no
    		ORDER BY vs.dpr_no;
    
    	-- Cursor left open for client application
    	OPEN dprCursor;	
    END P1
    The first error is "ILLEGAL SYMBOL "<END-OF-STATEMENT>". SOME SYMBOLS THAT MIGHT BE LEGAL ARE:. SQLCODE=-104, SQLSTATE=42601, DRIVER=3.58.81" and the error message is displaying only part of my SP.

    The following is not displayed in the error message:
    Code:
                                               ;
    
    	-- Cursor left open for client application
    	OPEN dprCursor;	
    END P1
    I don't know if this is because there is an error at ";" or if the error message can only display so many characters.

    Here are the other errors:

    Code:
    SELECT vs.dpr_no, vs.veh_ser_no, vs.tso_split_no, vs.loc,
    			vs.meas_cd, vs.meas_cur, vs.cust_name_abbr,
    			vs.veh_base_mdl_no, vs.date_mfg_rlse
    		FROM bqadprvs as vs, (SELECT dpr_no FROM bqadprrt WHERE so_cd = dealer_ID) as tempDPRNum
    		WHERE vs.dpr_no = tempDPRNum.dpr_no
    		ORDER BY vs.dpr_no
    ODFIM.BQADPRVS IS AN UNDEFINED NAME. SQLCODE=-204, SQLSTATE=42704, DRIVER=3.58.81

    Code:
    OPEN dprCursor
    UNACCEPTABLE SQL STATEMENT. SQLCODE=-84, SQLSTATE=42612, DRIVER=3.58.81

    Code:
    END P1
    ILLEGAL SYMBOL "P1". SOME SYMBOLS THAT MIGHT BE LEGAL ARE:. SQLCODE=-104, SQLSTATE=42601, DRIVER=3.58.81

  2. #2
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Passing "create procedure" statements to DB2 can be tricky.
    First of al you need to know that the exact syntax details can be different depending on (1) the DB2 platform: z/OS or Lunix/Unix/Windows, (2) the DB2 version, and (3) the interface used to pass the statement.

    (1) From the syntax you use, especially the "WLM", I assume you use DB2 on z/OS. Is that correct?
    (2) Are you on version 9? Or possibly 10?
    (3) If you are using SPUFI to pass this SQL DDL statement, did you change the statement terminator to something else than ";" ?
    (4) Why would you use the label "P1"?
    (5) What is your second "SELECT" statement for? It will not be part of the cursor definition...
    Last edited by Peter.Vanroose; 06-19-11 at 05:50.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  3. #3
    Join Date
    Jun 2011
    Posts
    2
    Quote Originally Posted by Peter.Vanroose View Post
    Passing "create procedure" statements to DB2 can be tricky.
    First of al you need to know that the exact syntax details can be different depending on (1) the DB2 platform: z/OS or Lunix/Unix/Windows, (2) the DB2 version, and (3) the interface used to pass the statement.

    (1) From the syntax you use, especially the "WLM", I assume you use DB2 on z/OS. Is that correct?
    (2) Are you on version 9? Or possibly 10?
    (3) If you are using SPUFI to pass this SQL DDL statement, did you change the statement terminator to something else than ";" ?
    (4) Why would you use the label "P1"?
    (5) What is your second "SELECT" statement for? It will not be part of the cursor definition...
    Hi Peter. Thanks for the reply. Thanks for the information. I did not know the syntax could be different for platform/version/interface. Good to know.

    1) You are correct. the DB2 is on z/OS.
    2) The database is version 9.1
    3) I am using IBM Data Studio 2.2 and the terminator is set to ";" in preference-> sql development-> SQL and XQuery editor.
    4) The label "P1" was from an example. I wasn't really sure why it was there. I have removed it.
    5) I also removed the cursor. The example I had also had it and I left it in without thinking.

    I am still getting the same errors minus the open cursor error and now that I removed the "P1" label I get:

    Code:
    END
    
    ILLEGAL SYMBOL "<END-OF-STATEMENT>". SOME SYMBOLS THAT MIGHT BE LEGAL ARE:. SQLCODE=-104, SQLSTATE=42601, DRIVER=3.58.81

  4. #4
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Try setting the terminator to someting else than ";", e.g. to "#"
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  5. #5
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Here is a simple (and yet a bit useful) example of a native stored procedure for DB2 9 on z/OS.
    Don't forget to set your terminator to something else than a semicolon!

    Code:
    CREATE PROCEDURE NRTBLS(IN pattern VARCHAR(255), OUT n INT)           
    LANGUAGE SQL                                                          
    READS SQL DATA                                                        
    QUALIFIER SYSIBM                                                      
    DISABLE DEBUG MODE                                                    
    BEGIN
      DECLARE sqlcode INT;
      IF pattern = '' THEN
        SELECT count(*) INTO n FROM systables;
      ELSE
        SELECT count(*) INTO n FROM systables WHERE creator LIKE pattern;
      END IF;
      IF sqlcode <> 0 THEN SET n=-1; END IF;
    END
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Code:
    ...
        SELECT count(*) INTO n FROM systables WHERE creator LIKE pattern;
    ...
    Is the "pattern" supported for pattern-expression on DB2 for z/OS?

    Because, I saw the following description in "DB2 Version 9.1 for z/OS SQL Reference".
    And, an SQL procedure parameter was not included in them.

    LIKE pattern-expression
    An expression that specifies the pattern of characters to be matched.

    The expression can be specified by any one of the following:
    v A constant
    v A special register
    v A host variable (including a LOB locator variable or a file reference variable)
    v A scalar function whose arguments are any of the above (though nested
    function invocations cannot be used)
    v A CAST specification whose arguments are any of the above
    v An expression that concatenates (using CONCAT or ||) any of the above

  7. #7
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by tonkuma View Post
    Is the "pattern" supported for pattern-expression on DB2 for z/OS?
    You're right about that; I wasn't aware of this restriction.
    Sorry for not having tested my example.
    The following was tested, though (and functions as expected):

    Code:
    CREATE PROCEDURE NRTBLS(IN owner VARCHAR(255), OUT n INT)           
    LANGUAGE SQL                                                          
    READS SQL DATA                                                        
    QUALIFIER SYSIBM                                                      
    DISABLE DEBUG MODE                                                    
    BEGIN
      DECLARE sqlcode INT;
      IF owner = '' THEN
        SELECT count(*) INTO n FROM systables;
      ELSE
        SELECT count(*) INTO n FROM systables WHERE creator = owner;
      END IF;
      IF sqlcode <> 0 THEN SET n=-1; END IF;
    END
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

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