Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

DB2 SQL Stored Procedures

Posted on 2005-04-01
13
Medium Priority
?
19,908 Views
Last Modified: 2011-10-03
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
Comment
Question by:Sixpach71
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 3
  • +1
13 Comments
 
LVL 46

Accepted Solution

by:
Kent Olsen earned 800 total points
ID: 13684778

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
 

Author Comment

by:Sixpach71
ID: 13698928
Kent,

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

H
0
 
LVL 18

Expert Comment

by:Dave Ford
ID: 13699865
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
Congratulations! You’re Certified – Now What?

Starting a new career can be overwhelming. Becoming certified in your field of expertise is a great start, but where do you go from here?  Here are some tips to help you on your career journey.

 
LVL 46

Expert Comment

by:Kent Olsen
ID: 13699907

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

Author Comment

by:Sixpach71
ID: 13700036
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
 
LVL 18

Expert Comment

by:Dave Ford
ID: 13700080
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
 

Author Comment

by:Sixpach71
ID: 13700327
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
 
LVL 18

Assisted Solution

by:Dave Ford
Dave Ford earned 1200 total points
ID: 13700628
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
 

Author Comment

by:Sixpach71
ID: 13701576
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
 

Expert Comment

by:rbedard65
ID: 14022760
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
 

Author Comment

by:Sixpach71
ID: 14029837
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
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 14029922

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
 

Expert Comment

by:rbedard65
ID: 14030219
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

How to Create Failover DNS Record Sets in Route 53

Route 53 has the ability to easily configure DNS record sets specifically for failover scenarios. These failover record sets can be configured to failover to full-blown deployments in other regions or to a static HTML page that informs your customers of the issue.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question