Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Trigger / Stored Procedure problem

Status
Not open for further replies.

sjolin01

Technical User
Jul 1, 2003
4
US
I am trying to get a simple trigger that calls a stored procedure to work. Both the trigger and the stored procedure get created successfully using SPUFI. However, I am running into an error when I insert the row to fire the trigger. It seems that DB2 can't access or load the procedure being called. Here is the trigger definition I am using.

SET CURRENT SQLID = 'KZ7MPR';
--#SET TERMINATOR #
CREATE TRIGGER SC29_I AFTER INSERT ON GC01SCMY.SC29
FOR EACH ROW
MODE DB2SQL
BEGIN ATOMIC
CALL JUNK();
END#

The procedure it calls 'JUNK' is defined as

SET CURRENT SQLID = 'KZ7MPR';
--#SET TERMINATOR #
CREATE PROCEDURE JUNK()
LANGUAGE SQL
MODIFIES SQL DATA
NO WLM ENVIRONMENT
BEGIN
INSERT INTO SD29 (
SYS_PARM_NM,
SYS_PARM_DESC,
PARM_VALUE,
DB_TXN_SEQ_NBR,
DB_ACT_CD )
VALUES(
'AA',
'AA',
'AA',
1,
'A');
END#


When I try to fire the trigger by inserting a row into the SC29 table, I get the following...

---------+---------+---------+---------+---------+---------+---------+------
INSERT INTO GC01SCMY.SC29 VALUES('SK','SK','SK');

---------+---------+---------+---------+---------+---------+---------+------
DSNT408I SQLCODE = -723, ERROR: AN ERROR OCCURRED IN A TRIGGERED SQL
STATEMENT
IN TRIGGER KZ7MPR.SC29_I, SECTION NUMBER 2.

INFORMATION RETURNED: SQLCODE -444, SQLSTATE 42724, AND MESSAGE
TOKENS
JUNK,

DSNT418I SQLSTATE = 09000 SQLSTATE RETURN CODE

DSNT415I SQLERRP = DSNX9CAC SQL PROCEDURE DETECTING ERROR

DSNT416I SQLERRD = 0 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION

DSNT416I SQLERRD = X'00000000' X'00000000' X'00000000' X'FFFFFFFF'

X'00000000' X'00000000' SQL DIAGNOSTIC INFORMATION

---------+---------+---------+---------+---------+---------+---------+------
DSNE618I ROLLBACK PERFORMED, SQLCODE IS 0
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
---------+---------+---------+---------+---------+---------+---------+------
DSNE601I SQL STATEMENTS ASSUMED TO BE BETWEEN COLUMNS 1 AND 72
DSNE620I NUMBER OF SQL STATEMENTS PROCESSED IS 1
DSNE621I NUMBER OF INPUT RECORDS READ IS 1
DSNE622I NUMBER OF OUTPUT RECORDS WRITTEN IS 30

When I lookup the SQLSTATE code of 42724 it says Unable to access an external program used for a user-defined function or a procedure. Any ideas what this could be?
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top