[DB2-L] A Basic Question on TRIGGERS

Walter Janißen

[DB2-L] A Basic Question on TRIGGERS
Hi

As opposed to that, what Phil said, I think, no packages will be invalidated by dropping a trigger except the trigger package, which will be freed. And as you only drop an insert trigger, insert operations must be quiesced, other update operation has no influence.

The other things, Phil mentioned, I agree with.

I don't know, which control you need over the database, whether dynanic SQL can be executed during the drop/create. But I think, you will not do the change in peak time.

Mit freundlichen Grüßen
Walter Janißen

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Laufzeitarchitektur
Victoriaplatz 2
D-40477 Düsseldorf
Tel.: +49 211 477-2928
Fax: +49 211 477-2615
mailto:[login to unmask email]
http://www.itergo.com < http://www.itergo.com/ >

Vorsitzender des Aufsichtsrats: Jürgen Vetter
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Dr. Michael Regauer, Wolfgang Schön.
Sitz: Düsseldorf | Handelsregister: Amtsgericht Düsseldorf, HRB 37996



________________________________

Von: DB2 Data Base Discussion List [mailto:[login to unmask email] Im Auftrag von Loga .T
Gesendet: Donnerstag, 26. Juni 2008 09:04
An: [login to unmask email]
Betreff: [DB2-L] A Basic Question on TRIGGERS


Hi Lists,





I am asking a very basic question on TRIGGERS... We are using DB2 V8 on Zos , and we have one trigger defined as follows :



CREATE TRIGGER A.trig
AFTER UPDATE OF col1, col2, col3, col4 ON AAA.table1
REFERENCING NEW AS N
OLD AS O
FOR EACH ROW MODE DB2SQL
WHEN (O.col1¬= N.col1OR O.col2 ¬= N.col2 OR O.col3¬= N.col3OR O.col4 ¬= N.col4)
BEGIN ATOMIC
INSERT INTO aaa.table2 (col1, col2, col3, col4,col5 )
VALUES
('aaaaa', ' ', ' ', 1, N.col5);

INSERT INTO aaa.table2 (col1, col2, col3, col4,col5 )
VALUES
('bbbbb', ' ', ' ', 2, N.col5);

.....

...

END



Now we want to remove the INSERT statement marked as bold from the TRIGGER.

I beleive we can to do it only by DROPPING the trigger and then RECREATING it again.



Before we drop and recreate a trigger, can you pls advise what all the precautions we need to take care? and What are the impact analysis we need to prepare before we drop.

And After recreating trigger what are the steps we need to follow. Pls advise.





Thanks ,

Logaa






________________________________

IDUG 2008 - Europe * 13-17 October 2008 * Warsaw, Poland < http://idug.org/lseu >

The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at IDUG.ORG <http://www.idug.org/lsidug> under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information <http://www.idug.org/lsconf> , and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services < http://www.idug.org/lsms >


______________________________________________________________________

* IDUG 08 Warsaw, Poland * 13-17 October 2008 * http://IDUG.ORG/lsEU *
______________________________________________________________________


The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Diwakar Kale

Re: A Basic Question on TRIGGERS
(in response to Walter Janißen)
To add to Phil's comments..

I would put the table in Read Only (R0) mode before I drop and recreate the trigger( to stop DMLs).

Packages are not dependent on triggers, you don't have to worry about other package rebinds.

It's a good idea to rebind the trigger package with EXPLAIN YES after you drop and recreate the trigger, (not required in your case as it's all INSERT's), triggers are created by default with EXPLAIN NO, I usually like to rebind trigger package with EXPLAIN YES to check on access path, triggers SQLs could have the same access path issues like any other embedded SQLs.

Diwakar.

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of Grainger, Phil
Sent: Thursday, June 26, 2008 4:41 AM
To: [login to unmask email]
Subject: Re: [DB2-L] A Basic Question on TRIGGERS

Yes, you are right - you can only change TRIGGER text by dropping and recreating the trigger

It's been a while since I looked, but I THINK dropping a trigger invalidates plans/packages etc, so these may need to be rebound after the recreate

Also, if you have multiple triggers defined on a table, their sequence of execution is in the order they were created. If this sequence is important, then you will have to drop and recreate ALL triggers

Finally, you may need to ensure that no updates/inserts/deletes (as appropriate) happen in the brief period of time when the trigger doesn't exist at all

I think that's all, but others may add more

Phil Grainger
CA

________________________________

From: DB2 Data Base Discussion List on behalf of Loga .T
Sent: Thu 26/06/2008 08:04
To: [login to unmask email]
Subject: [DB2-L] A Basic Question on TRIGGERS


Hi Lists,





I am asking a very basic question on TRIGGERS... We are using DB2 V8 on Zos , and we have one trigger defined as follows :



CREATE TRIGGER A.trig
AFTER UPDATE OF col1, col2, col3, col4 ON AAA.table1
REFERENCING NEW AS N
OLD AS O
FOR EACH ROW MODE DB2SQL
WHEN (O.col1¬= N.col1OR O.col2 ¬= N.col2 OR O.col3¬= N.col3OR O.col4 ¬= N.col4)
BEGIN ATOMIC
INSERT INTO aaa.table2 (col1, col2, col3, col4,col5 )
VALUES
('aaaaa', ' ', ' ', 1, N.col5);

INSERT INTO aaa.table2 (col1, col2, col3, col4,col5 )
VALUES
('bbbbb', ' ', ' ', 2, N.col5);

.....

...

END



Now we want to remove the INSERT statement marked as bold from the TRIGGER.

I beleive we can to do it only by DROPPING the trigger and then RECREATING it again.



Before we drop and recreate a trigger, can you pls advise what all the precautions we need to take care? and What are the impact analysis we need to prepare before we drop.

And After recreating trigger what are the steps we need to follow. Pls advise.





Thanks ,

Logaa






________________________________

IDUG 2008 - Europe * 13-17 October 2008 * Warsaw, Poland < http://idug.org/lseu >

The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at IDUG.ORG <http://www.idug.org/lsidug> under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information <http://www.idug.org/lsconf> , and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services < http://www.idug.org/lsms >


______________________________________________________________________

* IDUG 08 Warsaw, Poland * 13-17 October 2008 * http://IDUG.ORG/lsEU *
______________________________________________________________________


The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

______________________________________________________________________

* IDUG 08 Warsaw, Poland * 13-17 October 2008 * http://IDUG.ORG/lsEU *
______________________________________________________________________


The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Suresh Sane

Re: A Basic Question on TRIGGERS
(in response to Diwakar Kale)

A couple of clarifications/corrections.

1. Invalidating packages/plans - no, it does not happen, no rebind is needed.

2. Re-creating ALL triggers to maintain sequence - partially true. All BEFORE triggers always fire before all AFTER triggers, so you may need to re-create all triggers of the same type (before or after but not all).

Thx
Suresh> Date: Thu, 26 Jun 2008 09:40:50 +0100> From: [login to unmask email]> Subject: Re: [DB2-L] A Basic Question on TRIGGERS> To: [login to unmask email]> > Yes, you are right - you can only change TRIGGER text by dropping and recreating the trigger> > It's been a while since I looked, but I THINK dropping a trigger invalidates plans/packages etc, so these may need to be rebound after the recreate> > Also, if you have multiple triggers defined on a table, their sequence of execution is in the order they were created. If this sequence is important, then you will have to drop and recreate ALL triggers> > Finally, you may need to ensure that no updates/inserts/deletes (as appropriate) happen in the brief period of time when the trigger doesn't exist at all> > I think that's all, but others may add more> > Phil Grainger> CA> > ________________________________> > From: DB2 Data Base Discussion List on behalf of Loga .T> Sent: Thu 26/06/2008 08:04> To: [login to unmask email]> Subject: [DB2-L] A Basic Question on TRIGGERS> > > Hi Lists,> > > > > > I am asking a very basic question on TRIGGERS... We are using DB2 V8 on Zos , and we have one trigger defined as follows :> > > > CREATE TRIGGER A.trig > AFTER UPDATE OF col1, col2, col3, col4 ON AAA.table1 > REFERENCING NEW AS N > OLD AS O > FOR EACH ROW MODE DB2SQL > WHEN (O.col1¬= N.col1OR O.col2 ¬= N.col2 OR O.col3¬= N.col3OR O.col4 ¬= N.col4) > BEGIN ATOMIC > INSERT INTO aaa.table2 (col1, col2, col3, col4,col5 ) > VALUES > ('aaaaa', ' ', ' ', 1, N.col5); > > INSERT INTO aaa.table2 (col1, col2, col3, col4,col5 ) > VALUES > ('bbbbb', ' ', ' ', 2, N.col5); > > .....> > ...> > END> > > > Now we want to remove the INSERT statement marked as bold from the TRIGGER.> > I beleive we can to do it only by DROPPING the trigger and then RECREATING it again.> > > > Before we drop and recreate a trigger, can you pls advise what all the precautions we need to take care? and What are the impact analysis we need to prepare before we drop.> > And After recreating trigger what are the steps we need to follow. Pls advise.> > > > > > Thanks ,> > Logaa> > > > > > > ________________________________> > IDUG 2008 - Europe * 13-17 October 2008 * Warsaw, Poland <http://idug.org/lseu> > > The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at IDUG.ORG <http://www.idug.org/lsidug> under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information <http://www.idug.org/lsconf> , and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services < http://www.idug.org/lsms > > > > ______________________________________________________________________> > * IDUG 08 Warsaw, Poland * 13-17 October 2008 * http://IDUG.ORG/lsEU *> ______________________________________________________________________> > > The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms
_________________________________________________________________
The i’m Talkathon starts 6/24/08.  For now, give amongst yourselves.
http://www.imtalkathon.com?source=TXT_EML_WLH_LearnMore_GiveAmongst
______________________________________________________________________

* IDUG 08 Warsaw, Poland * 13-17 October 2008 * http://IDUG.ORG/lsEU *
______________________________________________________________________


The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Loga . T

Question on TRIGGERS
(in response to Suresh Sane)
Hi List,
 
We are using DB2 v8 in Compatibilty mode. We have a requirement to capture the userid and program that updates table1 and insert the details into table2. We defined the below trigger on Table1 :
 
CREATE TRIGGER A.trig
AFTER UPDATE OF col1 ON AAA.table1
REFERENCING NEW AS N
OLD AS O
FOR EACH ROW MODE DB2SQL
WHEN (O.col1¬= N.col1)
BEGIN ATOMIC
INSERT INTO aaa.table2 (time, userid, programid, col1)
VALUES
(CURRENT TIMESTAMP,USER,CURRENT PACKAGESET,O.COL1);

END
 
The above triggers gets the detail of the userid when we execute UPDATE statement on table 1,  but  it displays trigger schema name as the Program id. We are not getting the actual batch or online program name which is updating table1.
Can you pls help on this.
 
Thanks in Advance,
Logaa
 




______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Mike Hutchinson

Re: Question on TRIGGERS
(in response to Loga . T)
Try seeing if a "SET CURRENT PACKAGESET" statement in the originating update program
will work. I've not actually tried this.

________________________________

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of Loga .T
Sent: Friday, December 19, 2008 2:35 AM
To: [login to unmask email]
Subject: [DB2-L] Question on TRIGGERS


Hi List,

We are using DB2 v8 in Compatibilty mode. We have a requirement to capture the userid and program that updates table1 and insert the details into table2. We defined the below trigger on Table1 :

CREATE TRIGGER A.trig
AFTER UPDATE OF col1 ON AAA.table1
REFERENCING NEW AS N
OLD AS O
FOR EACH ROW MODE DB2SQL
WHEN (O.col1¬= N.col1)
BEGIN ATOMIC
INSERT INTO aaa.table2 (time, userid, programid, col1)
VALUES
(CURRENT TIMESTAMP,USER,CURRENT PACKAGESET,O.COL1);
END

The above triggers gets the detail of the userid when we execute UPDATE statement on table 1, but it displays trigger schema name as the Program id. We are not getting the actual batch or online program name which is updating table1.
Can you pls help on this.

Thanks in Advance,
Logaa



________________________________


IDUG 2009 - North America * May 11-15, 2009 * Denver, CO, USA < http://idug.org/lsNA >

The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at IDUG.ORG <http://www.idug.org/lsidug> under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information <http://www.idug.org/lsconf> , and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services < http://www.idug.org/lsms >


______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Robert Catterall

Re: Question on TRIGGERS
(in response to Mike Hutchinson)
Keep in mind that PACKAGESET is the name of a *package collection* - not of
a package itself. Also, the schema name used in a CREATE TRIGGER statement
becomes the collection ID of the associated trigger package.

A new special register, CURRENT CLIENT_APPLNAME, was introduced with DB2 for
z/OS V8; however, for local apps (i.e., those not using the DB2 Distributed
Data Facility), the only means of setting the value of this special register
(as far as I know) is through the RRS attach facility (not the CICS attach,
not the TSO attach, etc.).

Off the top of my head, I don't know how a trigger would capture the name of
a local (and non-RRSAF-using) package. Various DB2 traces contain package
ID information, but I don't see that this addresses your situation with
respect to getting this information by way of a trigger. As long as you
capture timestamp and user, you could conceivably combine this with
accounting trace information (which includes auth ID and package name and
time information) to identify a package associated with a user ID that
updated data at a certain time.

Robert


On Fri, Dec 19, 2008 at 2:34 AM, Loga .T <[login to unmask email]> wrote:

> Hi List,
>
> We are using DB2 v8 in Compatibilty mode. We have a requirement to capture
> the userid and program that updates table1 and insert the details into
> table2. We defined the below trigger on Table1 :
>
> CREATE TRIGGER A.trig
> AFTER UPDATE OF col1 ON AAA.table1
> REFERENCING NEW AS N
> OLD AS O
> FOR EACH ROW MODE DB2SQL
> WHEN (O.col1¬= N.col1)
> BEGIN ATOMIC
> INSERT INTO aaa.table2 (time, userid, programid, col1)
> VALUES
> (CURRENT TIMESTAMP,USER,CURRENT PACKAGESET,O.COL1);
> END
>
> The above triggers gets the detail of the userid when we execute UPDATE
> statement on table 1, but it displays trigger schema name as the Program
> id. We are not getting the actual batch or online program name which is
> updating table1.
> Can you pls help on this.
>
> Thanks in Advance,
> Logaa
>
>
>
> ------------------------------
>
> *IDUG 2009 - North America * May 11-15, 2009 * Denver, CO, USA * < http://idug.org/lsNA >
>
> The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L
> list archives, FAQ, and delivery preferences are at *IDUG.ORG < http://www.idug.org/lsidug >
> * under the Listserv tab. While at the site, you can also access the IDUG
> Online Learning Center, Tech Library and Code Place, see the latest IDUG *conference
> information < http://www.idug.org/lsconf > *, and much more. * If you have
> not yet signed up for Basic Membership in IDUG, available at no cost, click
> on Member Services < http://www.idug.org/lsms > *
>



--
Robert Catterall
Catterall Consulting
www.catterallconsulting.com

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Agus Kwee

Re: Question on TRIGGERS
(in response to Robert Catterall)
Hi Loga,

To find the member/program-name portion of the 4 part package name used by an executing program, we have to use the new Version 8 statement :
getvariable('SYSIBM.PACKAGE_NAME')
which I have just tested successfully in a batch program.
I also found out from my test that if we issue the statement in an after update trigger,
because the trigger is using its own package to execute its sql statement,
the getvariable('SYSIBM.PACKAGE_NAME) return the member/program-name portion of its
own package name which is the same with the schema name of the trigger.
Right now I do not see any SQL statement or expression that you can use in the trigger
that can obtain the program-name that contain the update statement that triggers the trigger.

Regards,
Agus Kwee
Themis Training
//http:www.themisinc.com

----- Original Message -----
From: "Loga .T"
Date: Friday, December 19, 2008 4:00 am
Subject: [DB2-L] Question on TRIGGERS
To: [login to unmask email]

> Hi List,
>
> We are using DB2 v8 in Compatibilty mode. We have a requirement
> to capture the userid and program that updates table1 and insert
> the details into table2. We defined the below trigger on Table1
> :
>
> CREATE TRIGGER A.trig
> AFTER UPDATE OF col1 ON AAA.table1
> REFERENCING NEW AS N
> OLD AS O
> FOR EACH ROW MODE DB2SQL
> WHEN (O.col1¬= N.col1)
> BEGIN ATOMIC
> INSERT INTO aaa.table2 (time, userid, programid, col1)
> VALUES
> (CURRENT TIMESTAMP,USER,CURRENT PACKAGESET,O.COL1);
>
> END
>
> The above triggers gets the detail of the userid when we execute
> UPDATE statement on table 1, but it displays trigger schema
> name as the Program id. We are not getting the actual batch or
> online program name which is updating table1.
> Can you pls help on this.
>
> Thanks in Advance,
> Logaa
>
>
>
>
>
> ______________________________________________________________________
>
> * IDUG 2009 Denver, CO, USA * May 11-15, 2009 *
> http://IDUG.ORG/lsNA *
> ______________________________________________________________________
>
>
>
> The IDUG DB2-L Listserv is only part of your membership in IDUG.
> The DB2-L list archives, FAQ, and delivery preferences are at
> http://www.idug.org/lsidug under the Listserv tab. While at the
> site, you can also access the IDUG Online Learning Center, Tech
> Library and Code Place, see the latest IDUG conference
> information and much more. If you have not yet signed up for
> Basic Membership in IDUG, available at no cost, click on Member
> Services at http://www.idug.org/lsms

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms