Stored Procedure - SQL Error -440

syed asif

Stored Procedure - SQL Error -440
Hi Group,
We are trying to use stored procedure in our
application. But we have run into a problem while
compiling it. We have a Called program(Stored
Procedure) which has a collection id WXXX01CU. Which
we are able to successfuly Bind it. But the Calling
Program which has collection id WYYYY01CU is bound we
are getting -440 error.
Now my question is do we need to bind both the called
and calling program in the same collection WXXX01CU or
as we have done above. Also how do we fix the -440 SQL
Error. The program/procedure is in Cobol.
Regards,
Syed

__________________________________________________
Do You Yahoo!?
Send your FREE holiday greetings online!
http://greetings.yahoo.com



Cindy Andersen

Re: Stored Procedure - SQL Error -440
(in response to syed asif)
Does the id running the Calling Program have authority to EXECUTE the Stored
Procedure WXXX01CU? If not, you will get SQLCODE -440.

You can use different collections, but make sure the DB2 plan for the
calling program has a PKLIST the includes both collections.

Cindy Andersen

> -----Original Message-----
> From: syed asif [SMTP:[login to unmask email]
> Sent: Monday, December 24, 2001 10:36 AM
> To: [login to unmask email]
> Subject: Stored Procedure - SQL Error -440
>
> Hi Group,
> We are trying to use stored procedure in our
> application. But we have run into a problem while
> compiling it. We have a Called program(Stored
> Procedure) which has a collection id WXXX01CU. Which
> we are able to successfuly Bind it. But the Calling
> Program which has collection id WYYYY01CU is bound we
> are getting -440 error.
> Now my question is do we need to bind both the called
> and calling program in the same collection WXXX01CU or
> as we have done above. Also how do we fix the -440 SQL
> Error. The program/procedure is in Cobol.
> Regards,
> Syed
>
> __________________________________________________
> Do You Yahoo!?
> Send your FREE holiday greetings online!
> http://greetings.yahoo.com
>
>
>
>
>



Murari Selvakesavan

Re: Stored Procedure - SQL Error -440
(in response to Cindy Andersen)
Syed,
It is not a necessity that the calling program and the called stored procedure
should be bound under the same collection id. All it matters it that to make sure
the respecitve collection ids are included in the PKLIST(collidpgm.*,collidsp.*)
when you bind the PLAN that you use for execution. We in our shop have a practise
to bind all common application independent stored procedures under one specific
collection id and bind the plan one time with collectionSP id.* along with the
collectionPGM id.*, this way every new additon of SPs is made available to any
application program to make use of it if required.

Also, the PATH parameter in the package bind determines which SCHEMA
for the SP to be executed when you do not have a qualified reference to the SP in
the program. So make sure to bind the package with the right SCHEMA name as
well.

Murari Selvakesavan.
DBA Group
First Health Services Corp.





>>> [login to unmask email] 12/24/01 11:35AM >>>
Hi Group,
We are trying to use stored procedure in our
application. But we have run into a problem while
compiling it. We have a Called program(Stored
Procedure) which has a collection id WXXX01CU. Which
we are able to successfuly Bind it. But the Calling
Program which has collection id WYYYY01CU is bound we
are getting -440 error.
Now my question is do we need to bind both the called
and calling program in the same collection WXXX01CU or
as we have done above. Also how do we fix the -440 SQL
Error. The program/procedure is in Cobol.
Regards,
Syed

__________________________________________________
Do You Yahoo!?
Send your FREE holiday greetings online!
http://greetings.yahoo.com




This message, including any attachments, is intended solely for the use of the named
recipient(s) and may contain confidential and/or privileged information. Any
unauthorized review, use, disclosure or distribution of this communications is expressly
prohibited. If you are not the intended recipient, please contact the sender by reply e-mail
and destroy any and all copies of the original message. Thank you.

syed asif

Re: Stored Procedure - SQL Error -440
(in response to Murari Selvakesavan)
Hi Muraru,
Thanks for the information. Can you let me know how
to set the schema name. I have the stored procedure
created under the schema name WASP01CU.
Also I am getting this message during binding.
-------------------------------------------------------
DSNX200I -DB3A BIND SQL ERROR

USING WOJI AUTHORITY

PLAN=(NOT APPLICABLE)

DBRM=ASP002

STATEMENT=118

SQLCODE=-440

SQLSTATE=42884

TOKENS=PROCEDURE.SPROC5

CSECT NAME=DSNXOSTP

RDS CODE=-100

DSNT233I -DB3A UNSUCCESSFUL BIND FOR PACKAGE =
USODN1DASDB3A.WASP01CU.ASP002.()
DSN

END

READY

SE 'THE BIND FOR WSLMBIND WAS UNSUCCESSFUL'
USER(WSLM) LOGON
Regards,
Syed
-------------------------------------------------------

--- Murari Selvakesavan <[login to unmask email]>
wrote:
> Syed,
> It is not a necessity that the calling program and
> the called stored procedure
> should be bound under the same collection id. All it
> matters it that to make sure
> the respecitve collection ids are included in the
> PKLIST(collidpgm.*,collidsp.*)
> when you bind the PLAN that you use for execution.
> We in our shop have a practise
> to bind all common application independent stored
> procedures under one specific
> collection id and bind the plan one time with
> collectionSP id.* along with the
> collectionPGM id.*, this way every new additon of
> SPs is made available to any
> application program to make use of it if required.
>
> Also, the PATH parameter in the package bind
> determines which SCHEMA
> for the SP to be executed when you do not have a
> qualified reference to the SP in
> the program. So make sure to bind the package with
> the right SCHEMA name as
> well.
>
> Murari Selvakesavan.
> DBA Group
> First Health Services Corp.
>
>
>
>
>
> >>> [login to unmask email] 12/24/01 11:35AM >>>
> Hi Group,
> We are trying to use stored procedure in our
> application. But we have run into a problem while
> compiling it. We have a Called program(Stored
> Procedure) which has a collection id WXXX01CU. Which
> we are able to successfuly Bind it. But the Calling
> Program which has collection id WYYYY01CU is bound
> we
> are getting -440 error.
> Now my question is do we need to bind both the
> called
> and calling program in the same collection WXXX01CU
> or
> as we have done above. Also how do we fix the -440
> SQL
> Error. The program/procedure is in Cobol.
> Regards,
> Syed
>
> __________________________________________________
> Do You Yahoo!?
> Send your FREE holiday greetings online!
> http://greetings.yahoo.com
>
>
> To change your subscription options or to cancel
> your subscription visit the DB2-L webpage at
> http://www.ryci.com/db2-l. The owners of the list
> can
>
> This message, including any attachments, is intended
> solely for the use of the named
> recipient(s) and may contain confidential and/or
> privileged information. Any
> unauthorized review, use, disclosure or distribution
> of this communications is expressly
> prohibited. If you are not the intended recipient,
> please contact the sender by reply e-mail
> and destroy any and all copies of the original
> message. Thank you.
>
>


__________________________________________________
Do You Yahoo!?
Send your FREE holiday greetings online!
http://greetings.yahoo.com