Stored proc receiving -805 executing from trigger

Donna Domovic

Stored proc receiving -805 executing from trigger
Hello,

We have a situation where we are doing processing in a REXX exec which
causes a DB2 trigger to be executed. The DB2 trigger then calls a Cobol
stored procedure. When this happens, we receive the following message:

DSNT408I SQLCODE = -805, ERROR: DBRM OR PACKAGE NAME
DB2TLOC.TESTCOLL.STORPRC-
1.183249E003BEC507 NOT FOUND IN PLAN DSNREXX. REASON
02
DSNT418I SQLSTATE = 51002 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXEPM SQL PROCEDURE DETECTING
ERROR
DSNT416I SQLERRD = -250 0 0 -1 0 0 SQL DIAGNOSTIC
INFORMATION
DSNT416I SQLERRD = X'FFFFFF06' X'00000000' X'00000000'
X'FFFFFFFF'
X'00000000' X'00000000' SQL DIAGNOSTIC INFORMATION


Does a stored procedure called in this manner need to be included in the
DSNREXX plan?

Thanks,
Donna Domovic

IMPORTANT NOTICE:

IDUG is pleased to announce a series of upgrades to the DB2-L discussion listserv that are being implemented to improve reliability and the overall user experience of DB2-L. These changes are coming on November 30th. Details at http://www.idug.org

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Agus Kwee

Re: Stored proc receiving -805 executing from trigger
(in response to Donna Domovic)
Donna,

Last month I received a similar SQLCODE = -805 with REASON = 02 during the invocation of a Cobol Stored procedure by a DB2 trigger.
The problem was solved when I changed the collection id of the Cobol Stored Procedure to the schema name of the trigger. Did you bind your Cobol Stored Procedure with the same collection-id
that was listed in the error message: TESTCOLL?

Agus Kwee
Themis Training
http://www.themisinc.com

----- Original Message -----
From: Donna Domovic
Date: Thursday, November 29, 2007 4:10 pm
Subject: [DB2-L] Stored proc receiving -805 executing from trigger
To: [login to unmask email]

> Hello,
>
> We have a situation where we are doing processing in a REXX exec
> which
> causes a DB2 trigger to be executed. The DB2 trigger then calls
> a Cobol
> stored procedure. When this happens, we receive the following
> message:
> DSNT408I SQLCODE = -805, ERROR: DBRM OR PACKAGE NAME
> DB2TLOC.TESTCOLL.STORPRC-
> 1.183249E003BEC507 NOT FOUND IN PLAN DSNREXX. REASON
> 02
> DSNT418I SQLSTATE = 51002 SQLSTATE RETURN CODE
>
> DSNT415I SQLERRP = DSNXEPM SQL PROCEDURE DETECTING
> ERROR
> DSNT416I SQLERRD = -250 0 0 -1 0 0 SQL DIAGNOSTIC
> INFORMATION
> DSNT416I SQLERRD = X'FFFFFF06' X'00000000' X'00000000'
> X'FFFFFFFF'
> X'00000000' X'00000000' SQL DIAGNOSTIC INFORMATION
>
>
>
> Does a stored procedure called in this manner need to be
> included in the
> DSNREXX plan?
>
> Thanks,
> Donna Domovic
>
> IMPORTANT NOTICE:
>
> IDUG is pleased to announce a series of upgrades to the DB2-L
> discussion listserv that are being implemented to improve
> reliability and the overall user experience of DB2-L. These
> changes are coming on November 30th. Details at http://www.idug.org
>
> -----------------------------------------------------------------
> ----------------
> Welcome to the IDUG DB2-L list. To unsubscribe, go to the
> archives and home page at http://www.idugdb2-l.org/archives/db2-
> l.html. From that page select "Join or Leave the list". The IDUG
> DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins
> can be reached at [login to unmask email] Find out the
> latest on IDUG conferences at http://conferences.idug.org/index.cfm
>

IMPORTANT NOTICE:

IDUG is pleased to announce a series of upgrades to the DB2-L discussion listserv that are being implemented to improve reliability and the overall user experience of DB2-L. These changes are coming on November 30th. Details at http://www.idug.org

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Donna Domovic

Re: Stored proc receiving -805 executing from trigger
(in response to Agus Kwee)
Agus,

The COLLID listed in the message was actually for the Cobol stored procedure
but the trigger was using a different schema.

We'll give your suggestion a try and see what happens.

Thanks again!
Donna Domovic

IMPORTANT NOTICE:

IDUG is pleased to announce a series of upgrades to the DB2-L discussion listserv that are being implemented to improve reliability and the overall user experience of DB2-L. These changes are coming on November 30th. Details at http://www.idug.org

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Donna Domovic

Re: Stored proc receiving -805 executing from trigger
(in response to Donna Domovic)
Agus,

Thanks for the tip. It resolved the problem and everything is running smoothly.

Thanks,
Donna Domovic

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms