DB2 SQL Stored Procedures

Kent,

Thanks for the response.  I'll give it a shot today and then post back.

H

Yo Sixpach71,

If memory serves, you're running on an iSeries.

To my knowledge, the "INHERIT SPECIAL REGISTERS" clause does not apply on iSeries.  (someone please let me know if that's incorrect.)

Regards,
DaveSlash

--   INHERIT SPECIAL REGISTERS

:)

I didn't realize that Sixpach is on an i-series.  I wonder if the parser throws an error or ignores the clause?


Kent

Kent,  I changed the table that I'm using because I know that the SELECT will result in only one record being returned.  Here is what I tried as well as the error messages I'm getting. Any help on resolving would be great.

DROP PROCEDURE Crys_Pgm.Simple_usp;
CREATE PROCEDURE Crys_Pgm.Simple_usp
      (IN p_OpNbr INTEGER,
      OUT p_OpDescription VARCHAR(40))
LANGUAGE SQL
NOT DETERMINISTIC
INHERIT SPECIAL REGISTERS
P1:  BEGIN
            SELECT SESCSPL.OPS.OPSNAM
            INTO p_OpDescription
            FROM SESCSPL.OPS
            WHERE SESCSPL.OPS.OPSID = p_OpNbr;
END P1

---Error Messages

END P1
[SQL0104] Token P1 was not valid. Valid tokens: DECLARE. Cause . . . . . :   A syntax error was detected at token P1.  Token P1 is not a valid token.  A partial list of valid tokens is DECLARE.  This list assumes that the statement is correct up to the token.  The error may be earlier in the statement, but the syntax of the statement appears to be valid up to this point. Recovery  . . . :   Do one or more of the following and try the request again: -- Verify the SQL statement in the area of the token P1. Correct the statement.  The error could be a missing comma or quotation mark, it could be a misspelled word, or it could be related to the order of clauses. -- If the error token is <END-OF-STATEMENT>, correct the SQL statement because it does not end with a valid clause.
CREATE PROCEDURE Crys_Pgm.Simple_usp
 (IN p_OpNbr INTEGER,
 OUT p_OpDescription VARCHAR(40))
LANGUAGE SQL
NOT DETERMINISTIC
INHERIT SPECIAL REGISTERS
P1:  BEGIN
  SELECT SESCSPL.OPS.OPSNAM
  INTO p_OpDescription
  FROM SESCSPL.OPS
  WHERE SESCSPL.OPS.OPSID = p_OpNbr
[SQL0104] Token <END-OF-STATEMENT> was not valid. Valid tokens: ;. Cause . . . . . :   A syntax error was detected at token <END-OF-STATEMENT>.  Token <END-OF-STATEMENT> is not a valid token.  A partial list of valid tokens is ;.  This list assumes that the statement is correct up to the token.  The error may be earlier in the statement, but the syntax of the statement appears to be valid up to this point. Recovery  . . . :   Do one or more of the following and try the request again: -- Verify the SQL statement in the area of the token <END-OF-STATEMENT>. Correct the statement.  The error could be a missing comma or quotation mark, it could be a misspelled word, or it could be related to the order of clauses. -- If the error token is <END-OF-STATEMENT>, correct the SQL statement because it does not end with a valid clause.
DROP PROCEDURE Crys_Pgm.Simple_usp
[SQL0204] SIMPLE_USP in CRYS_PGM type *N not found. Cause . . . . . :   SIMPLE_USP in CRYS_PGM type *N was not found. If this is an ALTER TABLE statement and the type is *N, a constraint or partition was not found. If this is not an ALTER TABLE statement and the type is *N, a function, procedure, or trigger was not found. If a function was not found, SIMPLE_USP is the service program that contains the function.  The function will not be found unless the external name and usage name do not match exactly.  Examine the job log for a message that gives more details on which function name is being searched for and the name that did not match. Recovery  . . . :   Change the name and try the request again.  If the object is a node group, ensure that the DB2 Multisystem product is installed on your system and create a nodegroup with the CRTNODGRP CL command. If an external function was not found, be sure that the case of the EXTERNAL NAME on the CREATE FUNCTION statement exactly matches the case of the name exported by the service program.

Remove the INHERIT SPECIAL REGISTERS clause, and it should work.

I tried using the INHERIT SPECIAL REGISTERS clause in my iSeries stored procedure, and I got:

SQL0104  30       8  Position 11 Token SPECIAL was not valid.

HTH,
DaveSlash

Yup, I'm using an iSeries.  

I tried creating the procedure again after taking out the INHERIT statement.  These are the messages that were returned.  

Thanks,

H

END P1
[SQL0104] Token P1 was not valid. Valid tokens: DECLARE. Cause . . . . . :   A syntax error was detected at token P1.  Token P1 is not a valid token.  A partial list of valid tokens is DECLARE.  This list assumes that the statement is correct up to the token.  The error may be earlier in the statement, but the syntax of the statement appears to be valid up to this point. Recovery  . . . :   Do one or more of the following and try the request again: -- Verify the SQL statement in the area of the token P1. Correct the statement.  The error could be a missing comma or quotation mark, it could be a misspelled word, or it could be related to the order of clauses. -- If the error token is <END-OF-STATEMENT>, correct the SQL statement because it does not end with a valid clause.
CREATE PROCEDURE Crys_Pgm.Simple_usp
 (IN p_OpNbr INTEGER,
 OUT p_OpDescription VARCHAR(40))
LANGUAGE SQL
NOT DETERMINISTIC
P1:  BEGIN
  SELECT SESCSPL.OPS.OPSNAM
  INTO p_OpDescription
  FROM SESCSPL.OPS
  WHERE SESCSPL.OPS.OPSID = p_OpNbr
[SQL0104] Token <END-OF-STATEMENT> was not valid. Valid tokens: ;. Cause . . . . . :   A syntax error was detected at token <END-OF-STATEMENT>.  Token <END-OF-STATEMENT> is not a valid token.  A partial list of valid tokens is ;.  This list assumes that the statement is correct up to the token.  The error may be earlier in the statement, but the syntax of the statement appears to be valid up to this point. Recovery  . . . :   Do one or more of the following and try the request again: -- Verify the SQL statement in the area of the token <END-OF-STATEMENT>. Correct the statement.  The error could be a missing comma or quotation mark, it could be a misspelled word, or it could be related to the order of clauses. -- If the error token is <END-OF-STATEMENT>, correct the SQL statement because it does not end with a valid clause.
DROP PROCEDURE Crys_Pgm.Simple_usp
[SQL0204] SIMPLE_USP in CRYS_PGM type *N not found. Cause . . . . . :   SIMPLE_USP in CRYS_PGM type *N was not found. If this is an ALTER TABLE statement and the type is *N, a constraint or partition was not found. If this is not an ALTER TABLE statement and the type is *N, a function, procedure, or trigger was not found. If a function was not found, SIMPLE_USP is the service program that contains the function.  The function will not be found unless the external name and usage name do not match exactly.  Examine the job log for a message that gives more details on which function name is being searched for and the name that did not match. Recovery  . . . :   Change the name and try the request again.  If the object is a node group, ensure that the DB2 Multisystem product is installed on your system and create a nodegroup with the CRTNODGRP CL command. If an external function was not found, be sure that the case of the EXTERNAL NAME on the CREATE FUNCTION statement exactly matches the case of the name exported by the service program.

Okay, I got the procedure to compile but I had to do it through the SQL Script Editor in iSeries Navigator.  I was initially trying it through the SQL tool used in Surveyor (product of Linoma Software).  Go figure.  

I appreciate the help.

One more question that is related...In Oracle you can do a DBMS_OUPUT.PUT_LINE to return messages or results.  Is there a similar option available with DB2?  

Hey Sixpach71, I also am using iseries navigator to create SQL stored procedures on the the iSeries but I can't figure out how to CHANGE an existing one.  All I can do is delete and create.  Any idea how to change a stored procedure?  

I don't think you can change a stored procedure on the iSeries, at least from what I've read and surmized in working with Navigator.  As you've mentioned, your only apparent option is to delete the procedure and then create it again.  I wondered about this also as Oracle has the option of 'CREATE OR REPLACE...' when writing procedures or functions which saves you a step.

H    

You will need to "drop" and "create" the procedure.  Note that if you have more than one procedure with the same name, you'll have to drop by the specific name.


Kent