Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2011
    Posts
    18

    Question Unanswered: Trigger for two databases DB2

    Hi. I have 2 databases, Old and New. My new software will use the New db, but i cant stop the Old db for now. All inserts, updates and deletes that happen in my New db, i need that be maked in Old db. I was thinking in create a trigger, but how i can access another database through of the trigger?
    What is the solution to the trigger can function in both databases? Remember that the two databases are DB2.

    I found some solutions that indicated the use of Federated Systems, but from what I read about it, the Federated Systems works only with Select.
    Last edited by kbum; 03-30-11 at 11:43.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    That's right, I don't think you can update federated nicknames from within a trigger. Looks like your only option is some sort of replication, either standard SQL replication or a home-grown variety (e.g. record changes locally in the new database by triggers, then pull changed data from the old database via federated nicknames).

  3. #3
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Using federated, you can do data changes on the remote db, but the nickname cannot be in a trigger or cannot take part in a 2-phase transaction, ie, data change is allowed only on the remote table in the uow.

    Coming to your problem, How soon do you want the old database to reflect the new one?

    If you want to keep it as close as possible, then sql replication or q replication are the options. the former one is easy to setup and maintain.

    If you want the 'replication' once a few hours or once a day, then triggers may be an option.

    When using a trigger, instead of recording every event on a row in the audit table, ie , one row for insert, another row for an update etc, you can have one row for every changed row. ie, if you have only the final image of the row in the audit table. You can export the data from the new database and import using insert_update in the old db.

    But if you have too many changes in the tables, your performance may suffer if using triggers.

    HTH

    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  4. #4
    Join Date
    Mar 2011
    Posts
    18
    Currently there is a software company that uses the database "Old. " I'm developing a new modeling software and a new database "New". The new software will be developed and released by the parties, ie, the two databases will work together. By developing a part of the new software, I'm creating new tables for the New Database. But the old software, this table is also used in various other parts. When there is an INSERT, DELETE or UPDATE int the old software, that such action is necessary to run in the table in the database New. If any INSERT, DELETE or UPDATE is made ​​in new software, I need this action to be executed in the database Old too. When the new software is ready, the idea is to disable the old database and leave only the new database active.

  5. #5
    Join Date
    Mar 2011
    Posts
    18
    Forgot to mention that the system is too large. There are about 5000 tables in the database.

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
  •