Sixpach71
asked on
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
ASKER
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.
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
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
ASKER
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
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?
ASKER
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
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
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.
ASKER
Thanks for the response. I'll give it a shot today and then post back.
H