Hello, no way to call an external stored procedure from an SQL trigger, I use RUNSQLSTM for execute and register the trigger, without the CALL to the external proc the trigger runs, with the CALL when I try to register the trigger I always have the error SQL0204 : SQL0204  30       1  VOID_CLP in *N di tipo *N non trovato.

BUT I can see the exernal stored procedure both from the WebSphere dev. studio, Iseries Access and also by a 'select * from sysprocs'. The strange is that I can call the stored procedure interactively from STRSQL, from the Websphere dev. studio and also from an sql script & RUNSQLSTM!!! and the proc void_clp runs correctly!

This happens also if I try to call an IBM stored proc. ie. QSYS.QCMDEXC or other system procs that I've tested.

==> everything good outside a trigger code
==> always SQL0204 inside a trigger

thanks!

   An SQL TRIGGER can call a registered External Stored Procedure,
requested directly or dynamically.  IIRC, only dynamically when not
registered.  While the InfoCenter documentation may be lacking for any
specific examples, the Redbooks document "Stored Procedures, Triggers,
and User-Defined Functions on DB2 Universal Database for iSeries"
sg246503.pdf does have some examples.

   The limited amount of both specific details about the failing script
[i.e. no script source nor the prior CREATE PROCEDURE activity provided,
not even the RUNSQLSTM command specifications] and specifics about what
are the existing registered routines [source and PRTSQLINF] and current
path or library list, limits the ability to respond with worthwhile
comments about what might be the issue.  I would need to make too many
assumptions and guesses about what is merely being implied or alluded.

   I can infer however, both that the routine likely is being invoked
without being qualified with the library\schema name and that the path
is *LIBL.  So if the Library List is not established to include the
library name in which the VOID_CLP routine exists, during the CREATE
TRIGGER request, then the sqlCode -204 would be expected with the two
"*N" replacement values is expected.  That the routine exists, that one
can "see" the routine in the catalogs, is not relevant if the PATH does
not cause routine\function resolution to look for that routine in the
SCHEMA where the routine is /located/.

   Note: Some rules for how a stored procedure is invoked are different
between embedded and dynamic, and whether the procedure is registered or
not.  The non-trigger interfaces described as being tested are all dynamic.

Regards, Chuck

On 26 Oct 2012 15:08, aldossir wrote: