Db2 z/OS V12 M100 Preserving Row Change Timestamp using Db2 LOAD without ALTER to target table.

Karen Lachance

Db2 z/OS V12 M100 Preserving Row Change Timestamp using Db2 LOAD without ALTER to target table.

Under Db2 z/OS V12 M100, How do I preserve Row Change Timestamps while loading data from one table to another without ALTER column DROP Row Change Timestamp attribute type?

Sam Baugh

Db2 z/OS V12 M100 Preserving Row Change Timestamp using Db2 LOAD without ALTER to target table.
(in response to Karen Lachance)
Change the GENERATED ALWAYS to GENERATED BY DEFAULT

On Thu, Jul 16, 2020 at 9:24 AM Karen Lachance <[login to unmask email]> wrote:

> Under Db2 z/OS V12 M100, How do I preserve Row Change Timestamps while
> loading data from one table to another without ALTER column DROP Row Change
> Timestamp attribute type?
>
> -----End Original Message-----
>

Ermanno Bertolotti

R: Db2 z/OS V12 M100 Preserving Row Change Timestamp using Db2 LOAD without ALTER to target table.
(in response to Sam Baugh)
Hi.

It’s correct, Now I needed GENERATED BY DEFAULT

Ibm as opened this apar “PH25572: LOAD WITH OVERRIDE FOR GENERATED ALWAYS ROW CHANGE TIMESTAMP”
https://www.ibm.com/support/pages/apar/PH25572



Ermanno Bertolotti

Intesa Sanpaolo
Group Chief IT, Digital and Innovation Officer Area
CIDIO Direzione Cent Sistemi Informativi
DC Sist Infrastrutture Tecnologiche
DC Sist Prog Svil Infrastr Elaborative – CC Database
Via Langhirano 1, 43125 Parma (PR)
Tel +39 0521.974130
Cell +39 348.6723316
Fax +39 0521.918337
[login to unmask email]<mailto:[login to unmask email]> - [login to unmask email]<mailto:[login to unmask email]>

Segui Intesa Sanpaolo su:
[cid:[login to unmask email] https://www.facebook.com/intesasanpaolo>[cid:[login to unmask email]<https://twitter.com/intesasanpaolo>[cid:[login to unmask email]<https://www.youtube.com/user/intesasanpaolo>[cid:[login to unmask email]<https://www.linkedin.com/company/intesa-sanpaolo>[cid:[login to unmask email]<https://www.instagram.com/intesasanpaolo
www.intesasanpaolo.com http://www.intesasanpaolo.com

Da: Sam Baugh <[login to unmask email]>
Inviato: giovedì 16 luglio 2020 17:49
A: [login to unmask email]
Oggetto: [DB2-L] - RE: Db2 z/OS V12 M100 Preserving Row Change Timestamp using Db2 LOAD without ALTER to target table.

Change the GENERATED ALWAYS to GENERATED BY DEFAULT

On Thu, Jul 16, 2020 at 9:24 AM Karen Lachance <[login to unmask email]<mailto:[login to unmask email]>> wrote:

Under Db2 z/OS V12 M100, How do I preserve Row Change Timestamps while loading data from one table to another without ALTER column DROP Row Change Timestamp attribute type?

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


Prima di stampare, pensa all'ambiente ** Think about the environment before printing

________________________________

Il presente messaggio, inclusi gli eventuali allegati, ha natura aziendale e potrebbe contenere informazioni confidenziali e/o riservate. Chiunque lo ricevesse per errore, è pregato di avvisare tempestivamente il mittente e di cancellarlo.
E’ strettamente vietata qualsiasi forma di utilizzo, riproduzione o diffusione non autorizzata del contenuto di questo messaggio o di parte di esso.
Pur essendo state assunte le dovute precauzioni per ridurre al minimo il rischio di trasmissione di virus, si suggerisce di effettuare gli opportuni controlli sui documenti allegati al presente messaggio. Non si assume alcuna responsabilità per eventuali danni o perdite derivanti dalla presenza di virus.

Per lo svolgimento delle attività di investimento nel Regno Unito, la società è autorizzata da Banca d'Italia ed è soggetta alla vigilanza limitata della Financial Conduct Authority ( FCA ) e della Prudential Regulation Authority ( PRA ) . Maggiori informazioni in merito ai poteri di vigilanza della Financial Conduct Authority ( FCA ) e della Prudential Regulation Authority ( PRA ) sono a disposizione previa richiesta.

Nel Regno Unito Intesa Sanpaolo S.p.A. opera attraverso la filiale di Londra, sita in 90 Queen Street, London EC4N 1SA, registrata in Inghilterra & Galles sotto No.FC016201, Branch No.BR000036

In osservanza dei requisiti imposti da Internal Revenue Service (Agenzia delle Entrate degli Stati Uniti), qualunque discussione relativa a temi di natura fiscale contenuta in questo messaggio o nei suoi allegati non è intesa né è stata scritta per essere utilizzata, né può essere utilizzata, per (i) evitare l’imposizione di gravami fiscali secondo il codice tributario vigente negli Stati Uniti o (ii) per promuovere, sollecitare o raccomandare una operazione finanziaria o altra transazione indirizzata a un altro destinatario.

Nella Repubblica d’Irlanda, Intesa Sanpaolo Bank Ireland plc è regolamentata dalla Banca Centrale d’Irlanda ed è parte del Gruppo Bancario Intesa Sanpaolo S.p.A. Registrata in Irlanda come società numero 125216 – IVA Reg. IE4817418C IE, sita in 2nd Floor, International House, 3 Harbourmaster Place, IFSC, Dublino 1, D01 K8F1, Irlanda.

***

________________________________

This email (including any attachment) is a corporate message and may contain confidential and/or privileged and/or proprietary information. If you have received this email in error, please notify the sender immediately, do not use or share it and destroy this email. Any unauthorised use, copying or disclosure of the material in this email or of parts hereof (including reliance thereon) is strictly forbidden.
We have taken precautions to minimize the risk of transmitting software viruses but nevertheless advise you to carry out your own virus checks on any attachment of this message. We accept no liability for loss or damage caused by software viruses.

For the conduct of investment business in the UK, the Company is authorised by Banca d’Italia and subject to limited regulation in the UK by the Financial Conduct Authority ( FCA ) and the Prudential Regulation Authority ( PRA ). Details about the extent of our regulation by the Financial Conduct Authority ( FCA ) and the Prudential Regulation Authority ( PRA ) are available from us on request.

In the UK Intesa Sanpaolo S.p.A. operates through its London Branch, located at 90 Queen Street, London EC4N 1SA. Registered in England & Wales under No.FC016201, Branch No.BR000036

To comply with requirements imposed by the IRS, we inform you that any discussion of U.S. federal tax issues contained herein (including any attachments) was not intended or written to be used, and cannot be used by you, for the purpose of (i) avoiding penalties under the Internal Revenue Code or (ii) promoting, marketing or recommending any transaction or matter addressed herein to another party.

In the Republic of Ireland, Intesa Sanpaolo Bank Ireland plc is regulated by the Central Bank of Ireland and is a member of the Intesa Sanpaolo Group. It is registered in Ireland as company no.125216 – VAT Reg. No. IE 4817418C and located at 2nd Floor, International House, 3 Harbourmaster Place, IFSC, Dublin 1, D01 K8F1, Ireland.
Attachments

  • image001.jpg (1k)
  • image002.jpg (<1k)
  • image003.jpg (1.1k)
  • image004.jpg (1k)
  • image005.jpg (1.1k)

Karen Lachance

RE: Db2 z/OS V12 M100 Preserving Row Change Timestamp using Db2 LOAD without ALTER to target table.
(in response to Sam Baugh)

Thanks Sam.

I wanted to avoid an ALTER which is restricted to DBAs with DBADM authority.  The LOAD utility is run by a customer without DBADM authority and plans to run the utility more than once.

Martin &#197;lund

RE: Db2 z/OS V12 M100 Preserving Row Change Timestamp using Db2 LOAD without ALTER to target table.
(in response to Karen Lachance)

Hi!

If the source and target tables look the same you can use unload and load in internal format. This way you can load the original values regardless of if the column is generated always or by default.

Regards Martin 

Stefan Kolev

RE: Db2 z/OS V12 M100 Preserving Row Change Timestamp using Db2 LOAD without ALTER to target table.
(in response to Karen Lachance)

Specify IDENTITYOVERRIDE in your load card.

Stefan Kolev | Database Services | SS&C

Martin &#197;lund

RE: Db2 z/OS V12 M100 Preserving Row Change Timestamp using Db2 LOAD without ALTER to target table.
(in response to Stefan Kolev)

IDENTITYOVERRIDE could not be used in this context. It only overrides columns defined as IDENTITY GENERATED ALWAYS and not ROW CHANGE TIMESTAMP. 

Regards Martin 

Kate Wheat

RE: Db2 z/OS V12 M100 Preserving Row Change Timestamp using Db2 LOAD without ALTER to target table.
(in response to Karen Lachance)

See the first item here:
https://www.ibm.com/support/knowledgecenter/SSEPEK_12.0.0/wnew/src/tpc/db2z_12_enhancements.html

"LOAD can override row change timestamp columns that are defined as GENERATED ALWAYS
(PH25572 – July 2020) (PH25572 - July 2020)"

Bill Gallagher

[External] Db2 z/OS V12 M100 Preserving Row Change Timestamp using Db2 LOAD without ALTER to target
(in response to Kate Wheat)
That should do the trick, but unfortunately the PTF for this APAR is not estimated to be available until 9/30.

Bill Gallagher | Senior Systems Engineer, DBA

From: Kate Wheat <[login to unmask email]>
Sent: Monday, July 20, 2020 9:22 AM
To: [login to unmask email]
Subject: [External] [DB2-L] - RE: Db2 z/OS V12 M100 Preserving Row Change Timestamp using Db2 LOAD without ALTER to target table.

***External Sender - Please Exercise Caution***


See the first item here:
https://www.ibm.com/support/knowledgecenter/SSEPEK_12.0.0/wnew/src/tpc/db2z_12_enhancements.html

"LOAD can override row change timestamp columns that are defined as GENERATED ALWAYS
(PH25572 – July 2020) (PH25572 - July 2020)"

-----End Original Message-----
________________________________
This message (including any attachments) may contain confidential, proprietary, privileged and/or private information. The information is intended to be for the use of the individual or entity designated above. If you are not the intended recipient of this message, please notify the sender immediately, and delete the message and any attachments. Any disclosure, reproduction, distribution or other use of this message or any attachments by an individual or entity other than the intended recipient is prohibited.

TRVDiscDefault::1201

Stefan Kolev

RE: Db2 z/OS V12 M100 Preserving Row Change Timestamp using Db2 LOAD without ALTER to target table.
(in response to Martin Ålund)

Thank you for pointing that out!  It appears the option to use in this case is OVERRIDE(ROWCHANGE) --> https://www.ibm.com/support/knowledgecenter/SSEPEK_12.0.0/ugref/src/tpc/db2z_loadrowidcolumns.html

Stefan Kolev | Database Services | SS&C

Karen Lachance

RE: Db2 z/OS V12 M100 Preserving Row Change Timestamp using Db2 LOAD without ALTER to target table.
(in response to Sam Baugh)

Thanks Sam.  After several attempts to not alter the table, I went with altering the Row Change Timestamp attribute type from Generated Always to Generated by Default.  I look forward to PTFs going on at our site to test OVERRIDE(ROWCHANGE).

Thanks again.