Trigger calling INVALID SP - strange behaviour ?

[Paul Reddin]

Hi,

FYI & comment:

We have triggers that call SPs.

If the Stored Procedure is invalidated, e.g a dependent table is dropped.
The SP is marked as invalid, but no error is thrown by the trigger!!

i.e the trigger performs no action, but because there is no error, this can
easily go unnoticed (as it has been in our scenario)

Is this working as designed? surely you would want the trigger to error?

We have coded around this by checking a return code from the SP for NULL (which
it seems to be when the SP is invalid)

i.e
declare rc_sqlstate char(5) default '00000'

call my_stored_proc(...... OUT rc_sqlstate);

if ((rc_sqlstate <> '00000') or (rc_sqlstate is null))THEN
ERROR ....

[Serge Rielau]

Paul Reddin wrote:

Hi,

FYI & comment:

We have triggers that call SPs.

If the Stored Procedure is invalidated, e.g a dependent table is dropped.
The SP is marked as invalid, but no error is thrown by the trigger!!

i.e the trigger performs no action, but because there is no error, this can
easily go unnoticed (as it has been in our scenario)

Is this working as designed? surely you would want the trigger to error?

We have coded around this by checking a return code from the SP for NULL (which
it seems to be when the SP is invalid)

i.e
declare rc_sqlstate char(5) default '00000'

call my_stored_proc(...... OUT rc_sqlstate);

if ((rc_sqlstate <> '00000') or (rc_sqlstate is null))THEN
ERROR ....

Which platform are you on?
What should happin is that the stored procedure tries to rebind upon
CALL. If you haven't recreated the table by that time you should get an
error stating that an implicit rebind failed.
This shoudl be returned by teh CALL statement which shoudl fail the
trigger and roll back teh entire statement.
So... I would expect a:
SQLCODE -723 (trigger failed) with embeddend token: <sqlcode/state of
implicit rebind>, -204 (object not found), <tablename>
Something like that.

Cheres
Serge

[Paul Reddin]

Serge Rielau <sr*****@ca.ibm.com> wrote in message news:<31*************@uni-berlin.de>...

Which platform are you on?
V8.2 Linux (RHAT).
What should happin is that the stored procedure tries to rebind upon
CALL. If you haven't recreated the table by that time you should get an
error stating that an implicit rebind failed.
Doesn't look like it's doing this.

We'll investigate further and raise a PMR. I was keen to know
if anybody else was/has experienced this?


Cheres
Serge

[andreyp#Antispam]

after drop depended table. But on REBIND invalid_package db2 say SUCCESS.
May be it's no good...

And may be You known method how dba can find thats packages (after success
rebind)?

Andy

create procedure test
language sql
begin
select 1 from test;
end

drop table test /* package for sp test in invalid state */

rebind package for sp test /* look on successfully*/