DB2 v11 upgrade in CM mode (Drop and recreate Triggers)

JITINDER CHOUDHARY

DB2 v11 upgrade in CM mode (Drop and recreate Triggers)

Could you please suggest how triggers need to drop and recreate after DB2 v11 CM upgrade. It should be drop and recreate in the same order based on time stamp it was created or we can drop and recreate in any order.

Thanks in Advance!!!

Raymond Bell

DB2 v11 upgrade in CM mode (Drop and recreate Triggers)
(in response to JITINDER CHOUDHARY)
Jitinder,

I’m 99% sure triggers are fired in the order they’re created. And I’m 100% sure the behaviour is documented in the SQL Reference:

Multiple triggers:
Multiple triggers that have the same triggering SQL operation and
activation time can be defined on a table. The triggers are activated in the
order in which they were created. For example, the trigger that was created
first is executed first; the trigger that was created second is executed
second.

So I might up my 99% to 100% if that’s OK.

So technically you can recreate the triggers in any order you like, but if you want them to fire in the same order as before you dropped them they need to be created in the order they were originally. Any decent change management/DDL generation product should be able to do this for you.

Cheers,


Raymond

Raymond Bell
DB2 Database Administrator | IT Operations | Technology | RBS

From: JITINDER CHOUDHARY [mailto:[login to unmask email]
Sent: 27 September 2017 15:58
To: [login to unmask email]
Subject: [DB2-L] - DB2 v11 upgrade in CM mode (Drop and recreate Triggers)


*********************************************
" This message originates from outside our organisation. Consider carefully whether you should click on any links, open any attachments or reply. If in doubt, forward to ~ Phishing"
*********************************************


Could you please suggest how triggers need to drop and recreate after DB2 v11 CM upgrade. It should be drop and recreate in the same order based on time stamp it was created or we can drop and recreate in any order.

Thanks in Advance!!!

-----End Original Message-----
The Royal Bank of Scotland plc. Registered in Scotland No 90312. Registered Office: 36 St Andrew Square, Edinburgh EH2 2YB. The Royal Bank of Scotland is authorised by the Prudential Regulation Authority, and regulated by the Financial Conduct Authority and Prudential Regulation Authority. The Royal Bank of Scotland N.V. is authorised and regulated by the De Nederlandsche Bank and has its seat at Amsterdam, the Netherlands, and is registered in the Commercial Register under number 33002587. Registered Office: Gustav Mahlerlaan 350, Amsterdam, The Netherlands. The Royal Bank of Scotland N.V. and The Royal Bank of Scotland plc are authorised to act as agent for each other in certain jurisdictions.

National Westminster Bank Plc. Registered in England No. 929027. Registered Office: 135 Bishopsgate, London EC2M 3UR. National Westminster Bank Plc is authorised by the Prudential Regulation Authority, and regulated by the Financial Conduct Authority and the Prudential Regulation Authority.

The Royal Bank of Scotland plc and National Westminster Bank Plc are authorised to act as agent for each other.

This e-mail message is confidential and for use by the addressee only. If the message is received by anyone other than the addressee, please return the message to the sender by replying to it and then delete the message from your computer. Internet e-mails are not necessarily secure. The Royal Bank of Scotland plc, The Royal Bank of Scotland N.V., National Westminster Bank Plc or any affiliated entity (“RBS” or “us”) does not accept responsibility for changes made to this message after it was sent. RBS may monitor e-mails for business and operational purposes. By replying to this message you give your consent to the monitoring of your e-mail communications with us.

Whilst all reasonable care has been taken to avoid the transmission of viruses, it is the responsibility of the recipient to ensure that the onward transmission, opening or use of this message and any attachments will not adversely affect its systems or data. No responsibility is accepted by RBS in this regard and the recipient should carry out such virus and other checks as it considers appropriate.

Visit our website at www.rbs.com http://www.rbs.com

Philip Sevetson

DB2 v11 upgrade in CM mode (Drop and recreate Triggers)
(in response to JITINDER CHOUDHARY)
**please note my email address change**
Jtinder,

Do you have some reason why you might want to recreate triggers in an order other than creation timestamp? Anything else seems likely to invite unnecessary problems.

Philip Sevetson
Computer Systems Manager
5 Manhattan West (33rd St at 10th Ave)
New York, NY 10001-2632
212-857-1688 w
917-991-7052 c
212-857-1659 f
[cid:[login to unmask email]

From: JITINDER CHOUDHARY [mailto:[login to unmask email]
Sent: Wednesday, September 27, 2017 10:58 AM
To: [login to unmask email]
Subject: [DB2-L] - DB2 v11 upgrade in CM mode (Drop and recreate Triggers)


Could you please suggest how triggers need to drop and recreate after DB2 v11 CM upgrade. It should be drop and recreate in the same order based on time stamp it was created or we can drop and recreate in any order.

Thanks in Advance!!!

-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**
Attachments

  • image001.png (3.3k)

JITINDER CHOUDHARY

RE: DB2 v11 upgrade in CM mode (Drop and recreate Triggers)
(in response to Philip Sevetson)

Hi Philip,

We are doing this as part of DB2 v11 upgrade.

JITINDER CHOUDHARY

RE: DB2 v11 upgrade in CM mode (Drop and recreate Triggers)
(in response to Raymond Bell)

Thanks Raymond for quick reply!!!

Need one more help. Could you please share the report job that will help us to segregate the triggers based on time stamp. 

Thanks in Advance!!!

Raymond Bell

DB2 v11 upgrade in CM mode (Drop and recreate Triggers)
(in response to JITINDER CHOUDHARY)
The report job? You just query the Catalog and see when the triggers were created. You should be able to work out which Catalog table by the type of object you want information for.


Raymond Bell
DB2 Database Administrator | IT Operations | Technology | RBS

From: JITINDER CHOUDHARY [mailto:[login to unmask email]
Sent: 27 September 2017 16:17
To: [login to unmask email]
Subject: [DB2-L] - RE: DB2 v11 upgrade in CM mode (Drop and recreate Triggers)


*********************************************
" This message originates from outside our organisation. Consider carefully whether you should click on any links, open any attachments or reply. If in doubt, forward to ~ Phishing"
*********************************************


Thanks Raymond for quick reply!!!

Need one more help. Could you please share the report job that will help us to segregate the triggers based on time stamp.

Thanks in Advance!!!

-----End Original Message-----
The Royal Bank of Scotland plc. Registered in Scotland No 90312. Registered Office: 36 St Andrew Square, Edinburgh EH2 2YB. The Royal Bank of Scotland is authorised by the Prudential Regulation Authority, and regulated by the Financial Conduct Authority and Prudential Regulation Authority. The Royal Bank of Scotland N.V. is authorised and regulated by the De Nederlandsche Bank and has its seat at Amsterdam, the Netherlands, and is registered in the Commercial Register under number 33002587. Registered Office: Gustav Mahlerlaan 350, Amsterdam, The Netherlands. The Royal Bank of Scotland N.V. and The Royal Bank of Scotland plc are authorised to act as agent for each other in certain jurisdictions.

National Westminster Bank Plc. Registered in England No. 929027. Registered Office: 135 Bishopsgate, London EC2M 3UR. National Westminster Bank Plc is authorised by the Prudential Regulation Authority, and regulated by the Financial Conduct Authority and the Prudential Regulation Authority.

The Royal Bank of Scotland plc and National Westminster Bank Plc are authorised to act as agent for each other.

This e-mail message is confidential and for use by the addressee only. If the message is received by anyone other than the addressee, please return the message to the sender by replying to it and then delete the message from your computer. Internet e-mails are not necessarily secure. The Royal Bank of Scotland plc, The Royal Bank of Scotland N.V., National Westminster Bank Plc or any affiliated entity (“RBS” or “us”) does not accept responsibility for changes made to this message after it was sent. RBS may monitor e-mails for business and operational purposes. By replying to this message you give your consent to the monitoring of your e-mail communications with us.

Whilst all reasonable care has been taken to avoid the transmission of viruses, it is the responsibility of the recipient to ensure that the onward transmission, opening or use of this message and any attachments will not adversely affect its systems or data. No responsibility is accepted by RBS in this regard and the recipient should carry out such virus and other checks as it considers appropriate.

Visit our website at www.rbs.com http://www.rbs.com

JITINDER CHOUDHARY

RE: DB2 v11 upgrade in CM mode (Drop and recreate Triggers)
(in response to JITINDER CHOUDHARY)

Hi Experts, Thanks for your response. We have few triggers which appears on "REPORT 12" - Each package listed below is a trigger package that has an invalid
statement section number (SECTNOI) entry in the SYSIBM.SYSPACKSTMT
catalog table. Further the report says " I need to drop and recreate
affected triggers in order to correct the SECTNOI entry; Results are
otherwise unpredictable." Should I need to drop and re-create the triggers that appears in the report or I must look out for dependent triggers as well. Can you help me with a SQL or by any means where I can get the associated DDLs with less manual intervention.

Appreciate your time and inputs.

Raymond Bell

DB2 v11 upgrade in CM mode (Drop and recreate Triggers)
(in response to JITINDER CHOUDHARY)
Right, so this is a V11 pre-migration report? In other words, a list of stuff that might go Pete Tong during, or shortly after, migration if you don’t do something about it. So not being recently familiar with the pre-migration report I’d guess when it says, ‘I need to drop and recreate the affected triggers’ that it means, ‘I need to drop and recreate the affected triggers’. Pretty clear.

If you don’t have any vendor tooling to help, the other option is, I’m afraid, manual intervention. You’ll have to build the DDL for the triggers yourself somehow, or trust a library where the definitions are hopefully stored as being current (good luck with that; either stored or current).

At this point I’d probably step back and go see your DB2 (sorry; Db2) DBA. They should know how to get trigger definitions out of the Catalog.

Cheers,


Raymond

Raymond Bell
DB2 Database Administrator | IT Operations | Technology | RBS

From: JITINDER CHOUDHARY [mailto:[login to unmask email]
Sent: 27 September 2017 16:21
To: [login to unmask email]
Subject: [DB2-L] - RE: DB2 v11 upgrade in CM mode (Drop and recreate Triggers)


*********************************************
" This message originates from outside our organisation. Consider carefully whether you should click on any links, open any attachments or reply. If in doubt, forward to ~ Phishing"
*********************************************


Hi Experts, Thanks for your response. We have few triggers which appears on "REPORT 12" - Each package listed below is a trigger package that has an invalid
statement section number (SECTNOI) entry in the SYSIBM.SYSPACKSTMT
catalog table. Further the report says " I need to drop and recreate
affected triggers in order to correct the SECTNOI entry; Results are
otherwise unpredictable." Should I need to drop and re-create the triggers that appears in the report or I must look out for dependent triggers as well. Can you help me with a SQL or by any means where I can get the associated DDLs with less manual intervention.

Appreciate your time and inputs.

-----End Original Message-----
The Royal Bank of Scotland plc. Registered in Scotland No 90312. Registered Office: 36 St Andrew Square, Edinburgh EH2 2YB. The Royal Bank of Scotland is authorised by the Prudential Regulation Authority, and regulated by the Financial Conduct Authority and Prudential Regulation Authority. The Royal Bank of Scotland N.V. is authorised and regulated by the De Nederlandsche Bank and has its seat at Amsterdam, the Netherlands, and is registered in the Commercial Register under number 33002587. Registered Office: Gustav Mahlerlaan 350, Amsterdam, The Netherlands. The Royal Bank of Scotland N.V. and The Royal Bank of Scotland plc are authorised to act as agent for each other in certain jurisdictions.

National Westminster Bank Plc. Registered in England No. 929027. Registered Office: 135 Bishopsgate, London EC2M 3UR. National Westminster Bank Plc is authorised by the Prudential Regulation Authority, and regulated by the Financial Conduct Authority and the Prudential Regulation Authority.

The Royal Bank of Scotland plc and National Westminster Bank Plc are authorised to act as agent for each other.

This e-mail message is confidential and for use by the addressee only. If the message is received by anyone other than the addressee, please return the message to the sender by replying to it and then delete the message from your computer. Internet e-mails are not necessarily secure. The Royal Bank of Scotland plc, The Royal Bank of Scotland N.V., National Westminster Bank Plc or any affiliated entity (“RBS” or “us”) does not accept responsibility for changes made to this message after it was sent. RBS may monitor e-mails for business and operational purposes. By replying to this message you give your consent to the monitoring of your e-mail communications with us.

Whilst all reasonable care has been taken to avoid the transmission of viruses, it is the responsibility of the recipient to ensure that the onward transmission, opening or use of this message and any attachments will not adversely affect its systems or data. No responsibility is accepted by RBS in this regard and the recipient should carry out such virus and other checks as it considers appropriate.

Visit our website at www.rbs.com http://www.rbs.com

Ruediger Kurtz

AW: DB2 v11 upgrade in CM mode (Drop and recreate Triggers)
(in response to JITINDER CHOUDHARY)
Jitinder,

it’s the „unpredictable“ that kept us away from cascading triggers.

As long as you haven’t implemented some of those, just drop / create them and all will be well.
Otherwise … the catalog is the lace to search. If you (or your DBAs) have some sort of catalog visibility tool handy generating the DDL is not much work, otherwise it might get complicated.
And, by the way, we have a library where all trigger definitions are stored. We haven’t got a history of how a trigger might have looked ages ago, but the current definition is just fine for us.

Regards

Ruediger


Rüdiger Kurtz
Abteilung Informatik – Betrieb

HUK-COBURG
Bahnhofsplatz
96444 Coburg
Telefon: 09561 96-44148
Telefax: 09561 96-44104
E-Mail: [login to unmask email]
Internet: www.huk.de
________________________________
HUK-COBURG Haftpflicht-Unterstützungs-Kasse kraftfahrender Beamter Deutschlands a. G. in Coburg
Reg.-Gericht Coburg HRB 100; St.-Nr. 9212/101/00021
Sitz der Gesellschaft: Bahnhofsplatz, 96444 Coburg
Vorsitzender des Aufsichtsrats: Prof. Dr. Heinrich R. Schradin.
Vorstand: Klaus-Jürgen Heitmann (Sprecher), Stefan Gronbach, Dr. Hans Olav Herøy, Dr. Jörg Rheinländer (stv.), Sarah Rössler, Daniel Thomas (stv.).
________________________________
Diese Nachricht enthält vertrauliche und/oder rechtlich geschützte Informationen.
Wenn Sie nicht der richtige Adressat sind oder diese Nachricht irrtümlich erhalten haben,
informieren Sie bitte sofort den Absender und vernichten Sie diese Nachricht.
Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Nachricht ist nicht gestattet.

This information may contain confidential and/or privileged information.
If you are not the intended recipient (or have received this information in error) please notify the
sender immediately and destroy this information.
Any unauthorized copying, disclosure or distribution of the material in this information is strictly forbidden.
________________________________
Von: JITINDER CHOUDHARY [mailto:[login to unmask email]
Gesendet: Mittwoch, 27. September 2017 17:21
An: [login to unmask email]
Betreff: [DB2-L] - RE: DB2 v11 upgrade in CM mode (Drop and recreate Triggers)


Hi Experts, Thanks for your response. We have few triggers which appears on "REPORT 12" - Each package listed below is a trigger package that has an invalid
statement section number (SECTNOI) entry in the SYSIBM.SYSPACKSTMT
catalog table. Further the report says " I need to drop and recreate
affected triggers in order to correct the SECTNOI entry; Results are
otherwise unpredictable." Should I need to drop and re-create the triggers that appears in the report or I must look out for dependent triggers as well. Can you help me with a SQL or by any means where I can get the associated DDLs with less manual intervention.

Appreciate your time and inputs.

-----End Original Message-----