• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 19929
  • Last Modified:

DB2 SQL Stored Procedures

I'm familiar with how to write Oracle stored procedures.  Would someone be willing to write an equivilant DB2 stored procedure for the following:

CREATE OR REPLACE PROCEDURE simple_usp(
     p_DlrNumber     NUMBER(4)) AS

     --declare variables
     v_DlrName   VARCHAR2(45);

BEGIN
     SELECT Dlr_Nme
     INTO v_DlrName
     FROM DealerTable  
     WHERE Dlr_Num = p_DlrNumber;
END;
/

This is a very simple procedure I realize but being visual and not being able to find a similar example in all of the DB2/stored proc documentation I've looked at I just need someone to break it down for me.  

Thanks,

H
0
Sixpach71
Asked:
Sixpach71
  • 5
  • 3
  • 3
  • +1
2 Solutions
 
Kent OlsenData Warehouse Architect / DBACommented:

It's similar, but different.  :)

DROP PROCEDURE simple_usp;
CREATE PROCEDURE simple_usp
 (IN p_dlrNumber integer,
  OUT p_DlrNumber VARCHAR (45)   )
  LANGUAGE SQL
  NOT DETERMINISTIC
  INHERIT SPECIAL REGISTERS
 ------------------------------------------------------------------------
-- SQL Stored Procedure
------------------------------------------------------------------------
P1: BEGIN

--  Insert SQL statements here

  SELECT Dlr_Nme
  INTO p_DlrNumber
  FROM DealerTable
  WHERE Dlr_Num = p_DlrNumber

END P1


Good Luck,
Kent
0
 
Sixpach71Author Commented:
Kent,

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

H
0
 
Dave FordSoftware Developer / Database AdministratorCommented:
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
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
Kent OlsenData Warehouse Architect / DBACommented:

--   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
0
 
Sixpach71Author Commented:
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.
0
 
Dave FordSoftware Developer / Database AdministratorCommented:
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
0
 
Sixpach71Author Commented:
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.



0
 
Dave FordSoftware Developer / Database AdministratorCommented:
The following compiles fine for me:

CREATE procedure Generic (  
   IN p_OpNbr INTEGER,            
   OUT p_OpDescription VARCHAR(40)
)
LANGUAGE SQL                      
NOT DETERMINISTIC                
set option dbgview = *source      

P1: BEGIN                        
      select description          
      into p_OpDescription        
      from deleteme d      
      WHERE                      
         coverageID = p_OpNbr;    
END P1                            
0
 
Sixpach71Author Commented:
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?  
0
 
rbedard65Commented:
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?  
0
 
Sixpach71Author Commented:
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    
0
 
Kent OlsenData Warehouse Architect / DBACommented:

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
0
 
rbedard65Commented:
There must be tools out there so that you can change a procedure and under the covers it does the DROP and CREATE.  The problem with using iSeries navigator is I can copy/paste my SQL statement but I have to redefine all my parameters again which a waste of a developer's time and we all know how valuable our time is.  
0

Featured Post

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

  • 5
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now