Errors when creating stored procedure

kenshinofkin · #1 (**permalink**) 06-17-11, 16:45

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

Peter.Vanroose · #2 (**permalink**) 06-19-11, 04:45

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...

kenshinofkin · #3 (**permalink**) 06-20-11, 12:30

Quote:

Originally Posted by Peter.Vanroose

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

Peter.Vanroose · #4 (**permalink**) 06-21-11, 03:23

Try setting the terminator to someting else than ";", e.g. to "#"

Peter.Vanroose · #5 (**permalink**) 06-21-11, 03:50

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

tonkuma · #6 (**permalink**) 06-22-11, 08:51

Quote:

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.

Quote:

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

Peter.Vanroose · #7 (**permalink**) 06-23-11, 02:56

Quote:

Originally Posted by tonkuma

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

Internet Services

Web Development

Digital Marketing

Consumer Tech