By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,581 Members | 1,990 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,581 IT Pros & Developers. It's quick & easy.

Able to Create a Procedure in DB2 V8.1 But Unable to Run it using CALL statement

P: n/a
Hi All,

I have DB2 8.1 on my system. I have created a procedure as follows.
CREATE PROCEDURE DB2ER000.XYZ() LANGUAGE SQL BEGIN INSERT INTO
DB2ER000.A

( C,D,E) VALUES (10,20,30);
END

It got created. But While calling the procedure using the statement

CALL DB2ER000.XYZ I am getting the following error.

DB2 SQL error: SQLCODE: -440, SQLSTATE: 42884, SQLERRMC:XYZ;PROCEDURE
Message: No authorized routine named "XYZ" of type "PROCEDURE" having
compatible arguments was found. More exceptions ...DB2 SQL error:
SQLCODE: -727, SQLSTATE: 56098, SQLERRMC: 2;-440;42884;XYZ|PROCEDURE

Can you please help me why this is happening.
I have tried creating this procedure with no arguments so many times,
but not able to run.

Thanks, Basha.

Jul 14 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
<ba*************@gmail.comwrote in message
news:11**********************@h48g2000cwc.googlegr oups.com...
Hi All,

I have DB2 8.1 on my system. I have created a procedure as follows.
CREATE PROCEDURE DB2ER000.XYZ() LANGUAGE SQL BEGIN INSERT INTO
DB2ER000.A

( C,D,E) VALUES (10,20,30);
END

It got created. But While calling the procedure using the statement

CALL DB2ER000.XYZ I am getting the following error.

DB2 SQL error: SQLCODE: -440, SQLSTATE: 42884, SQLERRMC:XYZ;PROCEDURE
Message: No authorized routine named "XYZ" of type "PROCEDURE" having
compatible arguments was found. More exceptions ...DB2 SQL error:
SQLCODE: -727, SQLSTATE: 56098, SQLERRMC: 2;-440;42884;XYZ|PROCEDURE

Can you please help me why this is happening.
I have tried creating this procedure with no arguments so many times,
but not able to run.

Thanks, Basha.
Note: When creating the procedure you need to set the statement terminator
to @. Also note the MODIFIES SQL DATA.

CREATE PROCEDURE DB2ER000.XYZ()
LANGUAGE SQL
MODIFIES SQL DATA
BEGIN
INSERT INTO DB2ER000.A (C,D,E) VALUES (10,20,30);
END@

------------------------------------------------------------------------------
CALL DB2ER000.XYZ

Return Status = 0
Jul 14 '06 #2

P: n/a
ba*************@gmail.com wrote:
Hi All,

I have DB2 8.1 on my system. I have created a procedure as follows.
CREATE PROCEDURE DB2ER000.XYZ() LANGUAGE SQL BEGIN INSERT INTO
DB2ER000.A

( C,D,E) VALUES (10,20,30);
END

It got created. But While calling the procedure using the statement

CALL DB2ER000.XYZ I am getting the following error.

DB2 SQL error: SQLCODE: -440, SQLSTATE: 42884, SQLERRMC:XYZ;PROCEDURE
Message: No authorized routine named "XYZ" of type "PROCEDURE" having
compatible arguments was found. More exceptions ...DB2 SQL error:
SQLCODE: -727, SQLSTATE: 56098, SQLERRMC: 2;-440;42884;XYZ|PROCEDURE

Can you please help me why this is happening.
I have tried creating this procedure with no arguments so many times,
but not able to run.
Are you running this in a DB2 with DPF system and you have clock skew?
Given a match in the procedure name and teh signature, there are two
remaining conditions for a routine to be found:
EXECUTE privilege of the caller and time.
If the CALL was "typed" in the procedures past then it won't be able to
find it.
So one scenario is that your catalog node is running ahead in time and
your coordinator won't be able to find the procedure until it has caught
up.

Cheers
Serge

PS: I find the fact that you're getting a -727 rc=2 on this mildly
disturbing. It suggests that the call at some point did successfully
compiler.
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Jul 14 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.