Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2010
    Posts
    4

    Unanswered: db2 10- trigger call stored procedure, error 2 SQLCODE -440, SQLSTATE 42884

    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


  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by mominaqeel View Post

    SELECT TEST_PROCEDURE(N.COL3) FROM SYSIBM.SYSDUMMY1;
    Stored procedures are called with the CALL statement.
    ---
    "It does not work" is not a valid problem statement.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •