Triggers do not execute stored procedure DB2/V7 OS390

HåkanRoll

Triggers do not execute stored procedure DB2/V7 OS390
Hi,

Has anyone experiences in this area we
appreciate all help


Fact:

We are trying to get a trigger to execute a stored procedure.

In batch the SP executes well when a cobol program calls it.

The same cobolprogam updates a tabel that fires a trigger
which calls the same SP.

The trigger executes well except from that we do
not se result from call to the SP.


Sources:

CREATE TABLE TAB1 (COL1 CHAR(20), COL2 TIMESTAMP);
CREATE TABLE TAB2 (COL1 CHAR(20), COL2 TIMESTAMP);
GRANT ALL ON TAB1 TO LCDE1;
GRANT ALL ON TAB2 TO LCDE1;

CREATE TRIGGER TRIGG1
AFTER INSERT ON TAB1
FOR EACH ROW
MODE DB2SQL
BEGIN ATOMIC
INSERT INTO TAB2
VALUES('TRIGGER1 BEFORE',CURRENT TIMESTAMP);
CALL SPROC1;
INSERT INTO TAB2
VALUES('TRIGGER1 AFTER ', CURRENT TIMESTAMP);
END<

CREATE PROCEDURE SPROC1 LANGUAGE SQL NO WLM ENVIRONMENT
P1:
BEGIN
INSERT INTO TAB2
VALUES ( 'FROM SPROC1', CURRENT TIMESTAMP );
END P1

GRANT EXECUTE ON PROCEDURE SPROC1 TO PUBLIC;

**cobol source**

EXEC SQL
INSERT INTO TAB2
VALUES ('BATCH BEFORE', CURRENT TIMESTAMP)
END-EXEC.
EXEC SQL
CALL SPROC1
END-EXEC.
EXEC SQL
INSERT INTO TAB2
VALUES ('BATCH AFTER ', CURRENT TIMESTAMP)
END-EXEC.
EXEC SQL
INSERT INTO TAB1
VALUES ('BATCH TRIGG ', CURRENT TIMESTAMP)
END-EXEC.
EXEC SQL
INSERT INTO TAB2
VALUES ('BATCH AF TRI', CURRENT TIMESTAMP)
END-EXEC.


actual result:

SELECT * FROM TAB2 ORDER BY 2;
---------+---------+---------+---------+---------+--
COL1 COL2
---------+---------+---------+---------+---------+--
BATCH BEFORE 2001-12-20-15.36.24.223552
FROM SPROC1 2001-12-20-15.36.24.289973
BATCH AFTER 2001-12-20-15.36.24.294357
TRIGGER1 BEFORE 2001-12-20-15.36.24.295169
TRIGGER1 AFTER 2001-12-20-15.36.24.295169
BATCH AF TRI 2001-12-20-15.36.24.336239


expected result:

SELECT * FROM TAB2 ORDER BY 2;
---------+---------+---------+---------+---------+--
COL1 COL2
---------+---------+---------+---------+---------+--
BATCH BEFORE 2001-12-20-15.36.24.223552
FROM SPROC1 2001-12-20-15.36.24.289973
BATCH AFTER 2001-12-20-15.36.24.294357
TRIGGER1 BEFORE 2001-12-20-15.36.24.295169

FROM SPROC1 2001-12-20-15.36.24.XXXXXXXXXXX
(this row is missing !!!!!!!!!!!!!!!!!!!!!!!!!!!)

TRIGGER1 AFTER 2001-12-20-15.36.24.295169
BATCH AF TRI 2001-12-20-15.36.24.336239

best regards
Håkan Roll
Sven Olsson



Kirk Hampton

Re: Triggers do not execute stored procedure DB2/V7 OS390
(in response to HåkanRoll)
Hi Hakan and Sven,
we have done several stored procedures executed by triggers, and
they are invariably a pain to diagnose. I suspect you will find a problem
either with collection names that the stored procedure package runs under,
being a different collection than the one that the trigger fired under,
or some
sort of authority (GRANT EXECUTE) problem.
Do you have a monitor such as BMC's Mainview or Candle's OmegaMon
that can run an application trace while the trigger is firing ? We have
found that
sometimes the only way we can see what is going wrong is to use Mainview to
trace the authid or plan that is firing the trigger, then in the trace you
can find the
SQLCODE when the stored procedure fails.

Kirk Hampton
DB2 OS/390 Sysprog
IBM Certified Solutions Expert - DB2 V7 Database Administration OS/390
TXU Energy Services
Dallas, Texas