Topic
  • 1 reply
  • Latest Post - ‏2013-02-27T21:55:47Z by Rob_Strong
SystemAdmin
SystemAdmin
3105 Posts

Pinned topic db2 10- trigger calling stored procedure, error 2 SQLCODE -440, SQLSTATE 42

‏2013-02-27T19:42:48Z |
Hi,
Need help badly. I cannot figure this out.
We are moving to use DB2 triggers with DB2 10 z/os.
As per documentation, trigger cannot have and declare statements and a stored procedure needs to be called.
Well, I'm getting error SQLCODE -440, SQLSTATE 42884, while finding my defined stored procedure.
My triggers is as follows.

CREATE TRIGGER TEST_INDICATORS
AFTER INSERT ON TABLE1
REFERENCING NEW AS N
FOR EACH ROW MODE DB2SQL
WHEN (N.COL1 = '9999-12-31')
BEGIN ATOMIC

SELECT TEST_PROCEDURE(N.COL3) FROM SYSIBM.SYSDUMMY1;
END

Here TEST_PROCEDURE is compiled and tested.
When I try to deploy trigger, I get following error
Create stored procedure returns SQLCODE: -20100, SQLSTATE: 56059.
PRODE.OPL_MEMBER_INDICATORS: 0: AN ERROR OCCURRED WHEN BINDING A TRIGGERED SQL STATEMENT. INFORMATION RETURNED: SECTION NUMBER : 2 SQLCODE -440, SQLSTATE 42884, AND MESSAGE TOKENS FUNCTION,TEST_PROCEDURE. SQLCODE=-20100, SQLSTATE=56059, DRIVER=4.12.79

Surprisingly, it is happening with user defined SPs only. If I call any existing function like DB2MQ.MQSEND, it works fine like if I change trigger to
CREATE TRIGGER TEST_INDICATORS
AFTER INSERT ON TABLE1
REFERENCING NEW AS N
FOR EACH ROW MODE DB2SQL
WHEN (N.COL1 = '9999-12-31')
BEGIN ATOMIC

SELECT DB2MQ.MQSEND(N.COL3) FROM SYSIBM.SYSDUMMY1;
END
Updated on 2013-02-27T21:55:47Z at 2013-02-27T21:55:47Z by Rob_Strong
  • Rob_Strong
    Rob_Strong
    5 Posts

    Re: db2 10- trigger calling stored procedure, error 2 SQLCODE -440, SQLSTATE 42

    ‏2013-02-27T21:55:47Z  
    Try using just a CALL of the procedure in the trigger rather than this SELECT. The -440 error message indicate that it is having difficulty finding the FUNCTION, but yours is not a function. Procedures are generally CALLed.