-805 error in Stored Procedure - Help!!!

syed asif

-805 error in Stored Procedure - Help!!!

December 27, 2001 01:50 PM

Hi Group,
We out here have run into a problem using stored
procedure. We have a calling program which calls a
stored procedure program. But when we are trying to
run the calling program we end up getting a SQLCODE of
-805. Both the calling and the called program uses the
same collection id. My question to the group is about
the CURRENT PACKAGESET. Do we need to put that in the
calling program. Also if any of you have had any
problem related to -805 error in stored procedure.
Help is really appriciated.
Regards,
Syed

Tim Lowe

Syed,
If the packages for the program calling your stored procedure, and
for the
stored procedure itself are in the same collection, then I don't
see why
you would need to use "set current packageset".

If the calling program gets an -805 sqlcode, then how is that
related to
the stored procedure? (does it occur just after the stored
procedure is
called?)
What DB2 release are you running? (DB2 V6 on OS/390?)
What environment is the calling program running in? (DB2 connect,
DB2
TSO, IMS, CICS....)
Is there a plan, and if so, what is the plan's pklist? (with
DB2
connect, there is no plan)
What is the COLLID in the Stored Procedure definition?
What is the error message from DSNTIAR when you get the -805
sqlcode?
(Is the version of the package mentioned in the error message in
the
collection?)

Hi Group,
We out here have run into a problem using stored
procedure. We have a calling program which calls a
stored procedure program. But when we are trying to
run the calling program we end up getting a SQLCODE of
-805. Both the calling and the called program uses the
same collection id. My question to the group is about
the CURRENT PACKAGESET. Do we need to put that in the
calling program. Also if any of you have had any
problem related to -805 error in stored procedure.
Help is really appriciated.
Regards,
Syed

Eric Kwai

Syed:
If you are calling the program within the same collection. There
are no
need to do set current packageset. I'll suggest you to compare
the
contoken of the package with the load module. If you can not find a
match,
may be the compile or bind were not successful.

You can extract the contoken of the package from sysibm.syspackage
and
issue the following command while you browse the load module.

>Hi Group,
> We out here have run into a problem using stored
>procedure. We have a calling program which calls a
>stored procedure program. But when we are trying to
>run the calling program we end up getting a SQLCODE of
>-805. Both the calling and the called program uses the
>same collection id. My question to the group is about
>the CURRENT PACKAGESET. Do we need to put that in the
>calling program. Also if any of you have had any
>problem related to -805 error in stored procedure.
>Help is really appriciated.
>Regards,
>Syed
>
>=====
>syed asif fazal
> 954-447-5568(W)
> 954-322-5414(H)
>
>__________________________________________________
>Do You Yahoo!?
>Send your FREE holiday greetings online!
>http://greetings.yahoo.com
>
>
>

Chintala S Kumar

Syed,
Usually -805 Sql error will occur when the load module
timestamp
differs from the package created timestamp. If these two timestamps
doesn't
match, then you will get -805 error. I would advise you to
Re-compile and
Bind your stored procedure source modules freshly. Stop and Start
the WLM
enviroment (if u r using WLM) or SPAS.

I Hope this will solve your problem. I used get the -805 errors in
my
application.

Syed,
If the packages for the program calling your stored procedure, and
for the
stored procedure itself are in the same collection, then I don't
see why
you would need to use "set current packageset".

If the calling program gets an -805 sqlcode, then how is that
related to
the stored procedure? (does it occur just after the stored
procedure is
called?)
What DB2 release are you running? (DB2 V6 on OS/390?)
What environment is the calling program running in? (DB2 connect,
DB2
TSO, IMS, CICS....)
Is there a plan, and if so, what is the plan's pklist? (with
DB2
connect, there is no plan)
What is the COLLID in the Stored Procedure definition?
What is the error message from DSNTIAR when you get the -805
sqlcode?
(Is the version of the package mentioned in the error message in
the
collection?)

Hi Group,
We out here have run into a problem using stored
procedure. We have a calling program which calls a
stored procedure program. But when we are trying to
run the calling program we end up getting a SQLCODE of
-805. Both the calling and the called program uses the
same collection id. My question to the group is about
the CURRENT PACKAGESET. Do we need to put that in the
calling program. Also if any of you have had any
problem related to -805 error in stored procedure.
Help is really appriciated.
Regards,
Syed

syed asif

Hi Tim/Dil/Eric/Chintala,
Well at present we are not using the option 'Set
current packageset'. Just wanted to know if we should
use it in the application. Well the error we are
getting is -805 is from the called program. The
Calling program is able to call the procedure but it
fails while executing the first SQL statement.
Well we out here are using DB2 V6. Both the program
are in TSO environment. The stored procedure is
created using the collection id WA01SPCU. The plan
name is WASP01PU and it has the PKLIST as WA01SPCU.*
which has both the calling and the called routine. We
are using WLM environment and the Procedure is defined
as RESIDENT YES.
I have got couple of suggestion to STOP and START the
Procedure and also to check the load module to match
with the consistency token generated by the DBRM. At
present I havent found out what specific reason code
is associated with the SQL error of -805. Which I
suppose to get from the application team.
Thanks again for the valuable suggestion and hope to
resolve the error. Looking forward to more suggestion
from the group.
Regards,
Syed
--- Chintala S Kumar <[login to unmask email]> wrote:
> Syed,
> Usually -805 Sql error will occur when the load
> module timestamp
> differs from the package created timestamp. If these
> two timestamps doesn't
> match, then you will get -805 error. I would advise
> you to Re-compile and
> Bind your stored procedure source modules freshly.
> Stop and Start the WLM
> enviroment (if u r using WLM) or SPAS.
>
> I Hope this will solve your problem. I used get the
> -805 errors in my
> application.
>
>
> Thanks,
> Surendar
>
>
>
>
> [login to unmask email]
> L.COM To:
> [login to unmask email]
> Sent by: DB2 cc:
> Data Base Subject:
> Re: -805 error in Stored Procedure - Help!!!
> Discussion
> List
> <[login to unmask email]
> M>
>
>
> 12/28/01 02:21
> AM
> Please respond
> to DB2 Data
> Base
> Discussion
> List
>
>
>
>
>
> Syed,
> If the packages for the program calling your stored
> procedure, and for the
> stored procedure itself are in the same collection,
> then I don't see why
> you would need to use "set current packageset".
>
> If the calling program gets an -805 sqlcode, then
> how is that related to
> the stored procedure? (does it occur just after
> the stored procedure is
> called?)
> What DB2 release are you running? (DB2 V6 on
> OS/390?)
> What environment is the calling program running in?
> (DB2 connect, DB2
> TSO, IMS, CICS....)
> Is there a plan, and if so, what is the plan's
> pklist? (with DB2
> connect, there is no plan)
> What is the COLLID in the Stored Procedure
> definition?
> What is the error message from DSNTIAR when you get
> the -805 sqlcode?
> (Is the version of the package mentioned in the
> error message in the
> collection?)
>
> I hope this helps.
>
> Thanks,
> Tim
>
>
>
> syed asif
> <syed_asif_98@ To:
> [login to unmask email]
> YAHOO.COM> cc:
> Sent by: DB2 Subject:
> -805 error in Stored
> Procedure -
> Data Base Help!!!
> Discussion
> List
> <[login to unmask email]
> M>
>
>
> 12/27/2001
> 01:50 PM
> Please respond
> to DB2 Data
> Base
> Discussion
> List
>
>
>
>
>
>
> Hi Group,
> We out here have run into a problem using stored
> procedure. We have a calling program which calls a
> stored procedure program. But when we are trying to
> run the calling program we end up getting a SQLCODE
> of
> -805. Both the calling and the called program uses
> the
> same collection id. My question to the group is
> about
> the CURRENT PACKAGESET. Do we need to put that in
> the
> calling program. Also if any of you have had any
> problem related to -805 error in stored procedure.
> Help is really appriciated.
> Regards,
> Syed
>
> =====
> syed asif fazal
> 954-447-5568(W)
> 954-322-5414(H)
>
> __________________________________________________
> 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
>
>
>
> 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
>
>
>
> 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

James Campbell

- CREATE PROCEDURE ... NO COLLID (default) In this case the PKLIST
of the calling plan is used.
- CREATE PROCEDURE ... COLLID xxxxx In this case xxxxx is
used
- The SP program issues a SET CURRENT PACKAGESET. In
this case the execution of the SET CURRENT PACKAGESET
does not require use of a package, and so future SQL will use
the
named collection.

The -805 message tokens include the consistancy token of the
program that is executing. Use that,
with the REXX that is in DB2-L-DOCUMENTS to determine its
pre-compilation timestamp. The time
is the time on the internal clock - usually sort-of-GMT - so
there
might be an offset to apply - including the one for leap
seconds.

James Campbell

>On 27 Dec 2001, at 19:09, syed asif wrote:

> Hi Tim/Dil/Eric/Chintala,
> Well at present we are not using the option 'Set
> current packageset'. Just wanted to know if we should
> use it in the application. Well the error we are
> getting is -805 is from the called program. The
> Calling program is able to call the procedure but it
> fails while executing the first SQL statement.
> Well we out here are using DB2 V6. Both the program
> are in TSO environment. The stored procedure is
> created using the collection id WA01SPCU. The plan
> name is WASP01PU and it has the PKLIST as WA01SPCU.*
> which has both the calling and the called routine. We
> are using WLM environment and the Procedure is defined
> as RESIDENT YES.
> I have got couple of suggestion to STOP and START the
> Procedure and also to check the load module to match
> with the consistency token generated by the DBRM. At
> present I havent found out what specific reason code
> is associated with the SQL error of -805. Which I
> suppose to get from the application team.
> Thanks again for the valuable suggestion and hope to
> resolve the error. Looking forward to more suggestion
> from the group.
> Regards,
> Syed
> --- Chintala S Kumar <[login to unmask email]> wrote:
> > Syed,
> > Usually -805 Sql error will occur when the load
> > module timestamp
> > differs from the package created timestamp. If these
> > two timestamps doesn't
> > match, then you will get -805 error. I would advise
> > you to Re-compile and
> > Bind your stored procedure source modules freshly.
> > Stop and Start the WLM
> > enviroment (if u r using WLM) or SPAS.
> >
> > I Hope this will solve your problem. I used get the
> > -805 errors in my
> > application.
> >
> >
> > Thanks,
> > Surendar
> >
> >
> >
> >
> > [login to unmask email]
> > L.COM To:
> > [login to unmask email]
> > Sent by: DB2 cc:
> > Data Base Subject:
> > Re: -805 error in Stored Procedure - Help!!!
> > Discussion
> > List
> > <[login to unmask email]
> > M>
> >
> >
> > 12/28/01 02:21
> > AM
> > Please respond
> > to DB2 Data
> > Base
> > Discussion
> > List
> >
> >
> >
> >
> >
> > Syed,
> > If the packages for the program calling your stored
> > procedure, and for the
> > stored procedure itself are in the same collection,
> > then I don't see why
> > you would need to use "set current packageset".
> >
> > If the calling program gets an -805 sqlcode, then
> > how is that related to
> > the stored procedure? (does it occur just after
> > the stored procedure is
> > called?)
> > What DB2 release are you running? (DB2 V6 on
> > OS/390?)
> > What environment is the calling program running in?
> > (DB2 connect, DB2
> > TSO, IMS, CICS....)
> > Is there a plan, and if so, what is the plan's
> > pklist? (with DB2
> > connect, there is no plan)
> > What is the COLLID in the Stored Procedure
> > definition?
> > What is the error message from DSNTIAR when you get
> > the -805 sqlcode?
> > (Is the version of the package mentioned in the
> > error message in the
> > collection?)
> >
> > I hope this helps.
> >
> > Thanks,
> > Tim
> >
> >
> >
> > syed asif
> > <syed_asif_98@ To:
> > [login to unmask email]
> > YAHOO.COM> cc:
> > Sent by: DB2 Subject:
> > -805 error in Stored
> > Procedure -
> > Data Base Help!!!
> > Discussion
> > List
> > <[login to unmask email]
> > M>
> >
> >
> > 12/27/2001
> > 01:50 PM
> > Please respond
> > to DB2 Data
> > Base
> > Discussion
> > List
> >
> >
> >
> >
> >
> >
> > Hi Group,
> > We out here have run into a problem using stored
> > procedure. We have a calling program which calls a
> > stored procedure program. But when we are trying to
> > run the calling program we end up getting a SQLCODE
> > of
> > -805. Both the calling and the called program uses
> > the
> > same collection id. My question to the group is
> > about
> > the CURRENT PACKAGESET. Do we need to put that in
> > the
> > calling program. Also if any of you have had any
> > problem related to -805 error in stored procedure.
> > Help is really appriciated.
> > Regards,
> > Syed
> >
> > =====
> > syed asif fazal
> > 954-447-5568(W)
> > 954-322-5414(H)
> >
> > __________________________________________________
> > 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
> >
> >
> >
> > 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
> >
> >
> >
> > 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
>
>
> __________________________________________________
> Do You Yahoo!?
> Send your FREE holiday greetings online!
> http://greetings.yahoo.com
>
>
> DB2-L webpage at htt
p://www.ryci.com/db2-l. The owners of the list can

Tim Lowe

Syed,
James Campbell had done a good job summarizing the options for
defining the
COLLID
in the stored procedure, and I had asked what you had specified for
the
COLLID in the stored procedure.
And, it does appear likely that this is your problem.
What is the COLLID specified in the CREATE PROCEDURE statement?

RESIDENT YES can be a temporary problem if you recompile (or move a
new
object module), because you are still running the old object. But,
from
what you have said, I don't think that this is your problem.

When the stored procedure gets the bad sqlcode, does it call
DSNTIAR to
format the error message?
The information from DSNTIAR (and the SQLCA) is generally the best
place to
start.

Hi Group,
Thanks for your valuable information. I will be
trying this option of using the STAY RESIDENT as NO
and run the procedure. Also just wanted to know if by
Droping and creating of the stored procedure again
with the option STAY RESIDENT as YES does that dosent
have any affect on the -805 SQL error. Because we had
dropped and created the stored procedure with the same
option of STAY RESIDENT YES few times. Please let me
know.
Thanks again.
Regards,

syed asif

Hi Tim/James,
Thanks for the valuable insight you both and rest of
the gus from the group sent across to my distress
signal. Well we had the option of STAY RESIDENT YES in
the test environment. We have made that option to NO
and the procedure is now kicking and alive..hehehe.
Well I would certainly fall back on all of you again
if I need any further help. Thanks again from me.
Regards,
Syed
--- [login to unmask email] wrote:
> Syed,
> James Campbell had done a good job summarizing the
> options for defining the
> COLLID
> in the stored procedure, and I had asked what you
> had specified for the
> COLLID in the stored procedure.
> And, it does appear likely that this is your
> problem.
> What is the COLLID specified in the CREATE PROCEDURE
> statement?
>
> RESIDENT YES can be a temporary problem if you
> recompile (or move a new
> object module), because you are still running the
> old object. But, from
> what you have said, I don't think that this is your
> problem.
>
> When the stored procedure gets the bad sqlcode, does
> it call DSNTIAR to
> format the error message?
> The information from DSNTIAR (and the SQLCA) is
> generally the best place to
> start.
>
> I hope this helps.
>
> Thanks,
> Tim
>
>
>
> syed asif
> <syed_asif_98@ To:
> [login to unmask email]
> YAHOO.COM> cc:
> Sent by: DB2 Subject:
> Re: -805 error in Stored Procedure -
> Data Base Help!!!
> Discussion
> List
> <[login to unmask email]
> M>
>
>
> 12/28/2001
> 07:55 AM
> Please respond
> to DB2 Data
> Base
> Discussion
> List
>
>
>
>
>
>
> Hi Group,
> Thanks for your valuable information. I will be
> trying this option of using the STAY RESIDENT as NO
> and run the procedure. Also just wanted to know if
> by
> Droping and creating of the stored procedure again
> with the option STAY RESIDENT as YES does that
> dosent
> have any affect on the -805 SQL error. Because we
> had
> dropped and created the stored procedure with the
> same
> option of STAY RESIDENT YES few times. Please let me
> know.
> Thanks again.
> Regards,
>
>
> 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