Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2003
    Location
    El Salvador
    Posts
    18

    Question Unanswered: DB2 Stored Procedures Question

    Hello, i'm working with DB2 9.1 over AIX and have an instance called db2inst1. In this instance have two database created (MERCA and TRAMITES). I need to update some tables of the TRAMITES database if a table in MERCA database receive an insert. I'm trying to do this with a trigger in the table of MERCA database, I federate the databases and create the nicknames objects from TRAMITES database in the MERCA database.

    When I create the trigger the UPDATE event inside the trigger don't work and DB2 said that this is not a valid SQL call in the trigger.

    Now I'm trying to implement a trigger in the table of the MERCA database in order to call a Stored Procedure (Java) that connects to the TRAMITES database and update the table that fires the triggers in the TRAMITES database.

    Is this correct??? there is another way to do that I want to realize???

    I will apreciate all the help and guide that you can give me....

    Best regards

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You should be at least able to create a SQL stored procedure to do what you want through the federation and then have the trigger call the stored procedure.

    Andy

  3. #3
    Join Date
    Dec 2003
    Location
    El Salvador
    Posts
    18
    I try to do that you recomend and the SP works fine but when I call the SP from the trigger DB2 said the following:

    DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned:
    SQL0723N An error occurred in a triggered SQL statement in trigger
    "MERCA.ACTUALIZA_". Information returned for the error includes SQLCODE
    "-20136", SQLSTATE "55047" and message tokens
    "MERCA.MOD_REGISTRO_FEDERADO|MOD_REGISTRO". SQLSTATE=09000

    The trigger in the after insert in the table is like the follows:

    CREATE TRIGGER merca.actualiza_tabla AFTER INSERT ON merca.jti001 REFERENCING NEW AS NEWREC FOR EACH ROW MODE DB2SQL
    BEGIN ATOMIC
    DECLARE busca INTEGER;
    SET busca = (SELECT tomo FROM merca.fed_tramites_activos WHERE tomo = NEWREC.tomo AND asiento = NEWREC.asiento);

    IF busca = NEWREC.tomo THEN
    CALL merca.mod_registro_federado (NEWREC.tomo, NEWREC.asiento);
    END IF;

    END;


    I accept sugestions....

    Best Regards....

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Have a look at the CREATE TRIGGER statement:
    The SQL-procedure-statement cannot reference an undefined transition variable (SQLSTATE 42703), a federated object (SQLSTATE 42997), or a declared temporary table (SQLSTATE 42995).
    (http://publib.boulder.ibm.com/infoce...c/r0000931.htm)

    We don't know how your procedure is defined by I guess this restriction on federated procedures applies to your latest error:
    Federated procedures with the MODIFIES SQL DATA access level cannot be invoked inside of triggers, dynamic compound statements, SQL scalar, tables, row functions, and methods.
    (http://publib.boulder.ibm.com/infoce...iyfafsp09.html)
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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