DB2 Trigger To Insert/Update Records Into Different Database

Contents hide

1 DB2 trigger to Insert/Update records into different database

Problem Description:

I want to create a trigger on one database’s table and want to add that `records into another database’s table.
Let us suppose, I have one table on first database, which has 5 rows and 2 columns. Another side I have one table on another
database, which has 3 rows and 2 columns, where 3 rows of another database’s table are exact same as 3 rows of the first database’s table.

I know, how to trigger the Insert/Update table on the same database. But how to trigger table from one database to another database?

Below is the code for triggering the tables in same database.

database_1 —> schema_1 —> table_1

|col1  col2|
_____|_____
|1a   1b   |
|2a   2b   |
|3a   3b   |
|4a   4b   |
|5a   5b   |

database_2 —> schema_2 —> table_2

|col1  col2|
_____|_____
|1a   1b   |
|2a   2b   |
|3a   3b   |


CREATE OR REPLACE TRIGGER "SCHEMA_1"."TRG_table_1_AFTER_UPDATE" 
    AFTER UPDATE ON "SCHEMA_1"."table_1"
    REFERENCING NEW AS new_row
    FOR EACH ROW
    NOT SECURED


Insert into SCHEMA_2.TABLE_2(col1, col2, col3) 
VALUES (new_row.val1, new_row.val2, new_row.val3);
END

Solution – 1

No way to do it with triggers.

The way to update tables in another database is use of nicknames.
But CREATE TRIGGER statement states:

SQL-procedure-statement
Specifies the SQL statement that is to be part of the triggered action. A searched update, searched delete, insert, or merge operation
on nicknames inside compound SQL is not supported.

and

A procedure that contains a reference to a nickname in a searched
UPDATE statement, a searched DELETE statement, or an INSERT statement
is not supported (SQLSTATE 25000).

You may use some procedural logic with, say, 2PC-enabled federated servers, but not triggers.
Enabling two-phase commit for federated transactions

Update:
You should familiarize yourself with the concept of Federation in Db2 firstly.
The key technical topics for Db2 -> Db2 federation are:
Enabling the federated server to access data sources (update dbm cfg parameter if needed and restart the federated server instance).
Configuring remote Db2 data source information:

On federation server:

CREATE WRAPPER DRDA;

-- MYREMDB the alias of a cataloged remote database
CREATE SERVER MYSERVER
   TYPE DB2/UDB 
   VERSION '11.5' 
   WRAPPER "DRDA"
AUTHORIZATION some_user PASSWORD "some_password"
OPTIONS
(
  DBNAME  'MYREMDB'
, DB2_TWO_PHASE_COMMIT  'Y'
-- may be other options like:
, DB2_MAXIMAL_PUSHDOWN 'Y'
);

-- User mapping for some MY_LOCAL_USER
-- all work from MY_LOCAL_USER with remote tables will be with
-- this MY_REMOTE_USER account.
-- The corresponding GRANT statements must be run on
-- MY_LOCAL_USER locally and MY_REMOTE_USER remotely
-- to work with the corresponding tables
CREATE USER MAPPING FOR MY_LOCAL_USER
SERVER MYSERVER
OPTIONS
(
   REMOTE_AUTHID  'my_remote_user'
 , REMOTE_PASSWORD  'my_remote_password'
);

-- Create a nickname or use 3-part name directly in your statements
-- MYSERVER.MY_REMOTE_SCHEMA.MY_REMOTE_TABLE
CREATE NICKNAME MY_SCHEMA.MY_REMOTE_TABLE_NICKNAME
FOR MYSERVER.MY_REMOTE_SCHEMA.MY_REMOTE_TABLE;

-- Usage
-- Switch the autocommit off in your session
-- Both statements are either committed or rolled back successfully in their databases
-- because of 2PC option (DB2_TWO_PHASE_COMMIT) of the server MYSERVER
-- disregarding of what or where fails
INSERT INTO MY_LOCAL_TABLE ...;
INSERT INTO MY_SCHEMA.MY_REMOTE_TABLE_NICKNAME ...;
-- OR
-- INSERT INTO MYSERVER.MY_REMOTE_SCHEMA.MY_REMOTE_TABLE ...;
COMMIT;

Rate this post