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?
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?