Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2010
    Posts
    2

    Question Unanswered: DB2 Syntax for calling stored procedure within a trigger

    Hi,

    Running DB2 v9.7 with full text search enabled and I am new to databases

    I am trying to create an insert\update\delete trigger(s) on a table that contains a XML column.

    Within that trigger I am trying to call a stored procedure which would update an index for that XML column.

    This is what I have done so far:

    I have created the index and am able to update it manually using:

    Code:
    db2ts "UPDATE INDEX myschema.myindex FOR TEXT"
    I am able to test the indexing by performing an db2-fn:xmlcolumn-contains function and successfully retrieve the data I am after.

    I tried updating the index using the following stored procedure:

    Code:
    CALL SYSPROC.SYSTS_UPDATE('myschema', 'myindex', '', 'en_US', ?)  ;
    That worked as expected.

    I then tried to create a trigger that does nothing, but get triggered on an insert to my XML column.

    That worked fine as well. I than attempted to combine the trigger with the stored procedure call with the following code:

    Code:
    CONNECT TO <database-name>^
    CREATE TRIGGER <schema-name>."INDEXTRIGGER" AFTER INSERT ON <schema-name>.<table-name> REFERENCING NEW AS N FOR EACH ROW MODE DB2SQL 
    WHEN ( N.CID > 0 ) // *NOTE
    BEGIN ATOMIC
    CALL SYSPROC.SYSTS_UPDATE('myschema', 'myindex', '', 'en_US', ?) ;
    END^
    CONNECT RESET^
    *I have no idea what condition to specify that would always evaluate to true besides testing that my auto increment “CID” (which has a default value of 1) is always greater than 0
    I got the following error message:

    SQL0418N A statement contains a use of
    an untyped parameter marker or a null value that is not valid.
    LINE NUMBER=4. SQLSTATE=42610
    If I understand this right some\all of the parameters within the call to the stored procedure are untyped and possibly need to be cast to whatever type the should be.

    As I said I new to databases let alone to triggers\stored procedures. So I have no idea what I need to do to resolve this problem.

    Could someone please tell me what I need to change within the call to the stored procedure to get it working within a trigger.

    If the call worked on its own when run in the CLR why is it not working when placed within a trigger? Is the error message telling me that: at time of execution there is no way to know what the type of the parameters ‘…’ is and thus they need to be CAST to …?

    Any help would be appreciated.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    BEGIN ATOMIC
    CALL SYSPROC.SYSTS_UPDATE('myschema', 'myindex', '', 'en_US', ?) ;
    END^
    You need to declare a variable which will serve as the output parameter of the stored procedure, the one you indicate with a question mark when calling the SP from the command line processor. I doubt you can declare variables in a trigger though, so you may have to create a wrapper SP, without parameters, to call SYSPROC.SYSTS_UPDATE.

  3. #3
    Join Date
    Oct 2010
    Posts
    2
    That did the job.

    I tried before posting to declare the parameters but within the trigger and it was telling me that I couldn’t do that. I should have stuck with it and done it in a separate procedure.

    I did the following and it worked.

    Code:
    CREATE PROCEDURE SP1
    LANGUAGE SQL
    BEGIN ATOMIC
    DECLARE param5 VARCHAR(10000);
    CALL SYSPROC.SYSTS_UPDATE('myschema', 'myindex', '', 'en_US', param5) ;
    End
    Than called that procedure from within my trigger.

    Code:
    CONNECT TO <database-name>
    CREATE TRIGGER <schema-name>."INDEXTRIGGER" AFTER INSERT ON <schema-name>.<table-name> REFERENCING NEW AS N FOR EACH ROW MODE DB2SQL
    WHEN (N.CID > 0)
    BEGIN ATOMIC
    CALL <shema-name>.SP1
    END^
    CONNECT RESET^
    Thanks for your help

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
  •