trigger question - blue sky

Stephen R Neff

trigger question - blue sky
Hello,

We are running DB2 V6 in an OS390 platform. We have a requirement to
keep two DB2 subsystems in sync. For example when an add happens in one
subsystem an insert should occur in the other. One idea on how to do this
was to have 3 part name aliases so that an add in one subsystem would
trigger an insert to the other. This is blue skying. I was wondering if this
would work. One problem I thought of is how do you stop the triggered insert
from the add triggering another insert to the subsystem where the add
occurred.

Any comments would be greatly appreciated

Thank You



John McKown

Re: trigger question - blue sky
(in response to Stephen R Neff)
Stephen,
First, let me say that I have *no* experience in this. But I think what you
want is called replication. Try looking at the following sites
(non-exhaustive):

http://www.peerdirect.com/
http://www-4.ibm.com/software/data/dbtools/datarepl.html
http://www-4.ibm.com/software/data/db2/satellite/
http://www.developer.ibm.com/library/data/udbrepl.html


----------------------------------------------------------------------
John McKown
Applications and Solutions Team
Healthaxis



> -----Original Message-----
> From: Neff, Stephen R [SMTP:[login to unmask email]
> Sent: Monday, January 07, 2002 8:37 AM
> To: [login to unmask email]
> Subject: trigger question - blue sky
>
> Hello,
>
> We are running DB2 V6 in an OS390 platform. We have a requirement
> to
> keep two DB2 subsystems in sync. For example when an add happens in one
> subsystem an insert should occur in the other. One idea on how to do this
> was to have 3 part name aliases so that an add in one subsystem would
> trigger an insert to the other. This is blue skying. I was wondering if
> this
> would work. One problem I thought of is how do you stop the triggered
> insert
> from the add triggering another insert to the subsystem where the add
> occurred.
>
> Any comments would be greatly appreciated
>
> Thank You
>
>
>
>
>



Suresh Sane

Re: trigger question - blue sky
(in response to John McKown)
Stephen,

A few thoughts on the questions you have posed.

1. Repeated triggers - I don't think that will be an issue since the
triggers are created at the table level (location. creator, table) - when
replicating form loc A to loc B, you will have a trigger on A but not on B.

2. Depending on the volumes, you may want to explore applicaiton
alternatives (subroutines, cics transations with RMO) or utility
alternatives (log analyzer). In general, Triggers for hoig volume
replication are NOT recommended.

HTH.

Suresh


>From: "Neff, Stephen R" <[login to unmask email]>
>Reply-To: DB2 Data Base Discussion List <[login to unmask email]>
>To: [login to unmask email]
>Subject: trigger question - blue sky
>Date: Mon, 7 Jan 2002 09:37:01 -0500
>
>Hello,
>
> We are running DB2 V6 in an OS390 platform. We have a requirement
>to
>keep two DB2 subsystems in sync. For example when an add happens in one
>subsystem an insert should occur in the other. One idea on how to do this
>was to have 3 part name aliases so that an add in one subsystem would
>trigger an insert to the other. This is blue skying. I was wondering if
>this
>would work. One problem I thought of is how do you stop the triggered
>insert
>from the add triggering another insert to the subsystem where the add
>occurred.
>
>Any comments would be greatly appreciated
>
>Thank You
>
>
>
>
>


_________________________________________________________________
Chat with friends online, try MSN Messenger: http://messenger.msn.com



Walter Jani&#223;en

Re: trigger question - blue sky
(in response to Suresh Sane)
Stephen

Another item is: you cannot use 3 part names in a trigger body. If you want
to update/insert/delete a remote subsystem you have to use stored
procedures in your trigger body or - data replication.



Bob Riehle

trigger question
(in response to Walter Janißen)
I'm trying to get this trigger defined and I'm getting this weird error.

what am I missing?
thanks,
Bob Riehle
CREATE TRIGGER CLNUP_T2 NO CASCADE BEFORE INSERT
ON S_OPTY_POSTN
REFERENCING NEW AS NEW
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
WHEN (NEW.FCST_PROB = 10) THEN
INSERT INTO SITDBA.S_ESCL_REQ
(BT_ROW_ID, RULE_ID, TBL_NAME, CREATED_BY,GROUP_ID VALUES
(NEW.ROW_ID
, '1-5BX'
, 'S_OPTY_POSTN'
, NEW.LAST_UPD_BY
,'1-5BV');

+---------+---------+---------+---------+---------+---------+---------+-

SQLCODE = -104, ERROR: ILLEGAL SYMBOL "". SOME SYMBOLS THAT MIGHT BE
LEGAL ARE: QUOTE
SQLSTATE = 42601 SQLSTATE RETURN CODE
SQLERRP = DSNHLEX SQL PROCEDURE DETECTING ERROR
SQLERRD = 0 0 0 -1 366 0 SQL DIAGNOSTIC INFORMATION
SQLERRD = X'00000000' X'00000000' X'00000000' X'FFFFFFFF'
X'0000016E' X'00000000' SQL DIAGNOSTIC INFORMATION



Bob Riehle

trigger question
(in response to Bob Riehle)
what am I doing wrong?
---------+---------+---------+---------+---------+---------+---------+---------+-----

CREATE TRIGGER SITDBA.CLNUP_T2 NO CASCADE BEFORE INSERT
ON SITDBA.S_OPTY_POSTN
REFERENCING NEW AS NEW
FOR EACH ROW
MODE DB2SQL
BEGIN ATOMIC
WHEN (NEW.FCST_PROB = 10) THEN
SELECT * FROM SYSIBM.SYSDUMMY1;
---------+---------+---------+---------+---------+---------+---------+---------+-----

DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL "". SOME SYMBOLS THAT
MIGHT BE
LEGAL ARE: QUOTE
DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNHLEX SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 0 0 0 -1 438 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'00000000' X'00000000' X'00000000' X'FFFFFFFF'

X'000001B6' X'00000000' SQL DIAGNOSTIC INFORMATION
---------+---------+---------+---------+---------+---------+---------+---------+-----



Sylvie Vuittenez

Re: trigger question
(in response to Bob Riehle)
if you have the keyword BEGIN ATOMIC, I think you also must have the keyword
END
Sylvie VUITTENEZ

----- Original Message -----
From: "Bob Riehle" <[login to unmask email]>
Newsgroups: bit.listserv.db2-l
To: <[login to unmask email]>
Sent: Monday, January 07, 2002 8:44 PM
Subject: trigger question


> I'm trying to get this trigger defined and I'm getting this weird error.
>
> what am I missing?
> thanks,
> Bob Riehle
> CREATE TRIGGER CLNUP_T2 NO CASCADE BEFORE INSERT
> ON S_OPTY_POSTN
> REFERENCING NEW AS NEW
> FOR EACH ROW MODE DB2SQL
> BEGIN ATOMIC
> WHEN (NEW.FCST_PROB = 10) THEN
> INSERT INTO SITDBA.S_ESCL_REQ
> (BT_ROW_ID, RULE_ID, TBL_NAME, CREATED_BY,GROUP_ID VALUES
> (NEW.ROW_ID
> , '1-5BX'
> , 'S_OPTY_POSTN'
> , NEW.LAST_UPD_BY
> ,'1-5BV');
>
> +---------+---------+---------+---------+---------+---------+---------+-
>
> SQLCODE = -104, ERROR: ILLEGAL SYMBOL "". SOME SYMBOLS THAT MIGHT BE
> LEGAL ARE: QUOTE
> SQLSTATE = 42601 SQLSTATE RETURN CODE
> SQLERRP = DSNHLEX SQL PROCEDURE DETECTING ERROR
> SQLERRD = 0 0 0 -1 366 0 SQL DIAGNOSTIC INFORMATION
> SQLERRD = X'00000000' X'00000000' X'00000000' X'FFFFFFFF'
> X'0000016E' X'00000000' SQL DIAGNOSTIC INFORMATION
>
>
>


>



Walter Jani&#223;en

Re: trigger question
(in response to Sylvie Vuittenez)
Hi Bob

The WHEN-clause has to be coded outside the BEGIN ATOMIC. And BEGIN ATOMIC
needs an END.



James Campbell

Re: trigger question
(in response to Walter Janißen)
In addition to the END requirement that others have mentioned:
- you need a close parenthesis after the column names and before
the word VALUES
- you need to change the statement delimiter. Semi-colons are
used to terminate SQL statements within the trigger (and, if you use
them, SQL language Stored Procedures). Hence you cannot use a
semi-colon to terminate the CREATE TRIGGER because, as
seems to be happening here, a semi-colon within the CREATE
TRIGGER is terminating the CREATE TRIGGER statement, and
not just the INSERT. You will have to
-- decide on a character to terminate the CREATE TRIGGER.
Hash/pound(#) is popular, but any character that is otherwise
unused will do
-- add "END #" to the end of the CREATE statement or (if you _do_
have an END statement) replace the semi-colon that occurs after
the END with this character
-- in the (V6 and later version of the) SPUFI panel where you enter
such things as the the number of rows to retrieve, enter this
character in the appropriate place
-- if you use DSNTIAD or DSNTEP2 to enter such statements, read
the Appl Prog and SQL Guide (search on "terminator character") to
see how they do it.

James Campbell

On 7 Jan 2002 at 11:44, Bob Riehle wrote:

> I'm trying to get this trigger defined and I'm getting this weird error.
>
> what am I missing?
> thanks,
> Bob Riehle
> CREATE TRIGGER CLNUP_T2 NO CASCADE BEFORE INSERT
> ON S_OPTY_POSTN
> REFERENCING NEW AS NEW
> FOR EACH ROW MODE DB2SQL
> BEGIN ATOMIC
> WHEN (NEW.FCST_PROB = 10) THEN
> INSERT INTO SITDBA.S_ESCL_REQ
> (BT_ROW_ID, RULE_ID, TBL_NAME, CREATED_BY,GROUP_ID VALUES
> (NEW.ROW_ID
> , '1-5BX'
> , 'S_OPTY_POSTN'
> , NEW.LAST_UPD_BY
> ,'1-5BV');
>
> +---------+---------+---------+---------+---------+---------+---------+-
>
> SQLCODE = -104, ERROR: ILLEGAL SYMBOL "". SOME SYMBOLS THAT MIGHT BE
> LEGAL ARE: QUOTE
> SQLSTATE = 42601 SQLSTATE RETURN CODE
> SQLERRP = DSNHLEX SQL PROCEDURE DETECTING ERROR
> SQLERRD = 0 0 0 -1 366 0 SQL DIAGNOSTIC INFORMATION
> SQLERRD = X'00000000' X'00000000' X'00000000' X'FFFFFFFF'
> X'0000016E' X'00000000' SQL DIAGNOSTIC INFORMATION
>
>
>