Where are the Data Capture Changes info kept in DB2

John Alexander

Where are the Data Capture Changes info kept in DB2

Hi All,

We recently installed 'Golden Gate' software on the mainframe (which was only 2 storprocs) ,
as well as on a Unix/Oracle server, in order to replicate DB2 data to an Oracle.

We then altered over 1000 prod tables to 'Data Capture Changes' so that Golden Gate
can replicate the DB2 DML changes to the target Oracle tables.

My question is where in DB2 is the Ins/Del/Upd & the archlogs they are on for the altered DCC tables kept ?

I had a look in the catalog & the BSDS but could not find this info. ?

Many thanks in advance

ps. we are on DB2 v11

 

John

 

 

Horacio Villa

Where are the Data Capture Changes info kept in DB2
(in response to John Alexander)
On SYSIBM.SYSTABLES, column DATACAPTURE.

Horacio Villa

John Alexander

RE: Where are the Data Capture Changes info kept in DB2
(in response to Horacio Villa)

Thanks Horacio, thats  just a flag which tells me which table has been altered to data capture.

But what I am after is whether or not DB2 keeps track of  the  Ins/Del/Upd activity & the archlog they are on, for the altered tables.  This info must be in DB2 somewhere , which the replication software reads in order to apply the DML changes on the target Oracle tables.

In my testing of Golden Gate , I noticed HSM recalls for migrated archlogs , which were required by Golden Gate 'Trckle Feed' process  in order to apply those DML changes to the Oracle tables. So I figured that this info must be stored somewhere in DB2 , but so far i'v not been able to find it.

Regards

John

Michael Hannan

RE: Where are the Data Capture Changes info kept in DB2
(in response to John Alexander)

John,

Yes for sure, Data Capture Changes causes DB2 to Log enough information in the DB2 Log (and goes to Archive Logs of course), before and after images for tables concerned, to be able to extract this information given suitable software. Note that if tables are compressed then the row before and after images are also compressed, so won't be easy for you to read those entries in the DB2 Log. The before and after entries for Index inserts and deletes are not compressed (so easier to read).

Do a find "DATA CAPTURE CHANGES"in the DB2 SQL Reference manual. You probably don't need to know the very fine detail of how Db2 works internally. It puts in the log whatever extra information is needed, which could include a full row.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

John Alexander

RE: Where are the Data Capture Changes info kept in DB2
(in response to Michael Hannan)

Many thanks Michael, I had a quick search in the Sql Ref , and  'reading between the lines' my take is that DB2  looks like is  storing this info in the logs ,  which Golden Gate has access to & therefore knows which tables have changed & which archlogs to read in order  to apply the changes to the target Oracle tables.

I was hoping to find this info in DB2 catalog  table like  Table_Name ,  Archlog_Name,  DML_Type,  Timestamp  etc, , where I could query it . On second thoughts it makes lots of sense to keep this totally away from the catalog for performance reasons.

Many thanks

John

Michael Arlebrandt

Where are the Data Capture Changes info kept in DB2
(in response to John Alexander)
Hi,

SYSLGRNX can be queried, here is an example

Select TB.DBNAME, TB.TSNAME , TB.CREATOR,TB.NAME
,TIMESTAMP_FORMAT( L.LGRUCDT CONCAT L.LGRUCTM
, 'MMDDYYHH24MISSFF' ) "TIMESTAMP"
, L.LGRUCDT, L.LGRUCTM
, HEX(LGRSRBA) "Start RBA"
, HEX(LGRSPBA) "Stop RBA"
, LGRPART "Partion Number"
, HEX(LGRSLRSN) "Start LRSN"
, HEX(LGRELRSN) "End LRSN"
, HEX(LGRMEMB ) "DS Member"
, HEX(L.LGRDBID) "DBID"
, HEX(L.LGRPSID) "PSID"
from SYSIBM.SYSLGRNX L , SYSIBM.SYSTABLESPACE TS,
SYSIBM.SYSTABLES TB
where HEX(L.LGRDBID) = HEX(TS.DBID)
and HEX(L.LGRPSID) = HEX(TS.PSID)
and TB.DBNAME = TS.DBNAME
and TB.TSNAME = TS.NAME
and TB.TYPE='T'
AND TS.DBNAME LIKE 'DSN8D11A'
AND TS.NAME LIKE 'DSN8S11P'
AND L.LGRUCDT = '041118' -- MMDDYY
ORDER BY 3 DESC
;

Once upon a time a wrote a rexx routine that run this SQL and then did list BSDS to know which archive logs to recall.

Then later a realized that can also be achieved by running a REPORT RECOVERY ☺

//DSNUPROC.SYSIN DD *
REPORT RECOVERY TABLESPACE DSN8D11A.DSN8S11P

Best regards
Michael Arlebrandt
HCL Technologies, Gothenburg, Sweden

From: John Alexander [mailto:[login to unmask email]
Sent: den 13 april 2018 8:21
To: [login to unmask email]
Subject: [DB2-L] - RE: Where are the Data Capture Changes info kept in DB2


Many thanks Michael, I had a quick search in the Sql Ref , and 'reading between the lines' my take is that DB2 looks like is storing this info in the logs , which Golden Gate has access to & therefore knows which tables have changed & which archlogs to read in order to apply the changes to the target Oracle tables.

I was hoping to find this info in DB2 catalog table like Table_Name , Archlog_Name, DML_Type, Timestamp etc, , where I could query it . On second thoughts it makes lots of sense to keep this totally away from the catalog for performance reasons.

Many thanks

John

-----End Original Message-----
::DISCLAIMER::
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
The contents of this e-mail and any attachment(s) are confidential and intended for the named recipient(s) only. E-mail transmission is not guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or may contain viruses in transmission. The e mail and its contents (with or without referred errors) shall therefore not attach any liability on the originator or HCL or its affiliates. Views or opinions, if any, presented in this email are solely those of the author and may not necessarily reflect the views or opinions of HCL or its affiliates. Any form of reproduction, dissemination, copying, disclosure, modification, distribution and / or publication of this message without the prior written consent of authorized representative of HCL is strictly prohibited. If you have received this email in error please delete it and notify the sender immediately. Before opening any email and/or attachments, please check them for viruses and other defects.
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Michael Hannan

RE: Where are the Data Capture Changes info kept in DB2
(in response to John Alexander)

In Reply to John Alexander:

I was hoping to find this info in DB2 catalog  table like  Table_Name ,  Archlog_Name,  DML_Type,  Timestamp  etc, , where I could query it . On second thoughts it makes lots of sense to keep this totally away from the catalog for performance reasons.

You would have to run special performance traces with overhead to capture the actual SQLs (or even their Types) that made changes to the tables in question. So yes this is not recorded in the db2 Catalog. Db2 does keep information about what parts of the log made changes to a table to speed up recovery when required. Also the Db2 Log would make it obvious if a change was caused by INSERT, UPDATE, or DELETE, and may have timing info to show when many affected rows were from the same SQL (I have not checked that).

UPDATE will log a before and after image of a row, INSERT will either have no before image, or a dummy one, DELETE will either have no after image or a dummy one. For index entries, really only Insert and Delete exists. Update of a row can cause one index entry to be removed (marked pseudo deleted) and another inserted, or may not affect the index entry at all.

There are 3rd party products that can read the Db2 Logs, and generate pseudo-SQLs (not exactly same as SQLs that caused them necessarily) that could have been used to make the changes that appear in the Logs. That might be what you seek. I am not going to advertise them here. Your software does have to interpret the Db2 Logs, whether current or archived to send the changes to Oracle, most likely. That is normally the most efficient process, and the purpose of Data Capture Changes. It will have to keep track of the RBA (address) where it is up to in the Logs.

I have heard of sites using triggers to capture table changes to send elsewhere years ago. 

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Edited By:
Michael Hannan[Organization Members] @ Apr 13, 2018 - 09:26 AM (Europe/Berlin)

Raymond Bell

Where are the Data Capture Changes info kept in DB2
(in response to John Alexander)
John,

After the 3rd reading of your question I think I get what you’re asking. It looked like you were asking whether or not Db2 logs insert/update/delete activity, and of course it does – regardless of whether DCC is turned on or off for any given object. But you already know that, and it’s now what you’re asking.

Michael (I think it was) has mentioned it. The BSDS records when an object was opened for update, and when it was closed to further updates. This is also independent of whether DCC is turned on or off for any given object. All DCC does is forces DB2 to write the full before/after image of the changed row, rather than just from the 1st byte changed to the end of the row. Log scraping tools like ‘Golden Gate’ (never heard of that one until today, TBH), Q-Rep, Log Master, etc. all rely on the full row data to be able to replicate the changes elsewhere. But again, you already know that.

So just like DB2 would need to know when an object was possibly updated in the event of a PIT recovery (so it can look in the right places when rolling forward from the last FIC to the appropriate PIT), so these log-scraping tools need to know when an object was possibly updated so they can extract the data changes made. And they both do it via the same mechanism; they query the BSDS. And as Michael pointed out, with Db2 V11 (or was it V10?) you can literally query the BSDS to find out when an object was open for update. Or just use Report Recovery.

Management summary: the answer to, ‘whether or not DB2 keeps track of the Ins/Del/Upd activity & the archlog they are on, for the altered tables’ is Yes – which it does for all tables, not just DCC-altered ones. And the info is in the BSDS.

Cheers,


Raymond

Raymond Bell
Db2
Hosting Services, Technology
Royal Bank of Scotland Group
3rd Floor Regents House
40-42 Islington High Street
London N1 8XL
Mob: +44 (0) 7894 608214
Email: [login to unmask email]<mailto:[login to unmask email]>

The content of this email is confidential unless stated otherwise.
[cid:[login to unmask email]

From: John Alexander [mailto:[login to unmask email]
Sent: 13 April 2018 03:05
To: [login to unmask email]
Subject: [DB2-L] - RE: Where are the Data Capture Changes info kept in DB2


*********************************************
" 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 Horacio, thats just a flag which tells me which table has been altered to data capture.

But what I am after is whether or not DB2 keeps track of the Ins/Del/Upd activity & the archlog they are on, for the altered tables. This info must be in DB2 somewhere , which the replication software reads in order to apply the DML changes on the target Oracle tables.

In my testing of Golden Gate , I noticed HSM recalls for migrated archlogs , which were required by Golden Gate 'Trckle Feed' process in order to apply those DML changes to the Oracle tables. So I figured that this info must be stored somewhere in DB2 , but so far i'v not been able to find it.

Regards

John

-----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 understand that the content of your message may be monitored.

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
Attachments

  • image001.png (6.7k)

David Baldon

Where are the Data Capture Changes info kept in DB2
(in response to Raymond Bell)
An advantage that the vendor tools not being mentioned have is they can get the data from other places when DCC it not turned on for a given table. They also handle compressed tables automatically among other things.

...David

David Baldon
Solution Architect
DB2 Backup & Recovery
BMC Software, Inc.

From: Bell, Raymond (Hosting Services, Technology) [mailto:[login to unmask email]
Sent: Friday, April 13, 2018 3:13 AM
To: '[login to unmask email]' <[login to unmask email]>
Subject: [DB2-L] - RE: Where are the Data Capture Changes info kept in DB2

John,

After the 3rd reading of your question I think I get what you’re asking. It looked like you were asking whether or not Db2 logs insert/update/delete activity, and of course it does – regardless of whether DCC is turned on or off for any given object. But you already know that, and it’s now what you’re asking.

Michael (I think it was) has mentioned it. The BSDS records when an object was opened for update, and when it was closed to further updates. This is also independent of whether DCC is turned on or off for any given object. All DCC does is forces DB2 to write the full before/after image of the changed row, rather than just from the 1st byte changed to the end of the row. Log scraping tools like ‘Golden Gate’ (never heard of that one until today, TBH), Q-Rep, Log Master, etc. all rely on the full row data to be able to replicate the changes elsewhere. But again, you already know that.

So just like DB2 would need to know when an object was possibly updated in the event of a PIT recovery (so it can look in the right places when rolling forward from the last FIC to the appropriate PIT), so these log-scraping tools need to know when an object was possibly updated so they can extract the data changes made. And they both do it via the same mechanism; they query the BSDS. And as Michael pointed out, with Db2 V11 (or was it V10?) you can literally query the BSDS to find out when an object was open for update. Or just use Report Recovery.

Management summary: the answer to, ‘whether or not DB2 keeps track of the Ins/Del/Upd activity & the archlog they are on, for the altered tables’ is Yes – which it does for all tables, not just DCC-altered ones. And the info is in the BSDS.

Cheers,


Raymond

Raymond Bell
Db2
Hosting Services, Technology
Royal Bank of Scotland Group
3rd Floor Regents House
40-42 Islington High Street
London N1 8XL
Mob: +44 (0) 7894 608214
Email: [login to unmask email]<mailto:[login to unmask email]>

The content of this email is confidential unless stated otherwise.
[cid:[login to unmask email]

From: John Alexander [mailto:[login to unmask email]
Sent: 13 April 2018 03:05
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: Where are the Data Capture Changes info kept in DB2


*********************************************
" 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 Horacio, thats just a flag which tells me which table has been altered to data capture.

But what I am after is whether or not DB2 keeps track of the Ins/Del/Upd activity & the archlog they are on, for the altered tables. This info must be in DB2 somewhere , which the replication software reads in order to apply the DML changes on the target Oracle tables.

In my testing of Golden Gate , I noticed HSM recalls for migrated archlogs , which were required by Golden Gate 'Trckle Feed' process in order to apply those DML changes to the Oracle tables. So I figured that this info must be stored somewhere in DB2 , but so far i'v not been able to find it.

Regards

John

-----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 understand that the content of your message may be monitored.

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 https://urldefense.proofpoint.com/v2/url?u=http-3A__www.rbs.com_&d=DwMFaQ&c=UrUhmHsiTVT5qkaA4d_oSzcamb9hmamiCDMzBAEwC7E&r=KigCN-TlaQoAQDZrna0ZVLUX9H9vZpSibySEyujWvHQ&m=nRRSaOsExEkT_bFVAZCaqRhdTZWJIFnWS4FJMZ-1u7k&s=P-xkUl_7-C5F05qGxVdkwEwXdjZHAcEloyVOiwXbkYo&e=
________________________________
Attachment Links: image001.png (7 k) https://urldefense.proofpoint.com/v2/url?u=https-3A__www.idug.org_p_fo_do_-3Fdownload-3D1-26fid-3D9228&d=DwMFaQ&c=UrUhmHsiTVT5qkaA4d_oSzcamb9hmamiCDMzBAEwC7E&r=KigCN-TlaQoAQDZrna0ZVLUX9H9vZpSibySEyujWvHQ&m=nRRSaOsExEkT_bFVAZCaqRhdTZWJIFnWS4FJMZ-1u7k&s=9s589tbZNItie0u50INVPVGqfvunHFjfoaR11VI8opA&e=
Site Links: View post online https://urldefense.proofpoint.com/v2/url?u=https-3A__www.idug.org_p_fo_st_-3Fpost-3D185558-26anc-3Dp185558-23p185558&d=DwMFaQ&c=UrUhmHsiTVT5qkaA4d_oSzcamb9hmamiCDMzBAEwC7E&r=KigCN-TlaQoAQDZrna0ZVLUX9H9vZpSibySEyujWvHQ&m=nRRSaOsExEkT_bFVAZCaqRhdTZWJIFnWS4FJMZ-1u7k&s=XsqkZT0YujP13nNsUxdzRFWySGhdzf5HKIXacln2u3U&e= View mailing list online https://urldefense.proofpoint.com/v2/url?u=https-3A__www.idug.org_p_fo_si_-3Ftopic-3D19&d=DwMFaQ&c=UrUhmHsiTVT5qkaA4d_oSzcamb9hmamiCDMzBAEwC7E&r=KigCN-TlaQoAQDZrna0ZVLUX9H9vZpSibySEyujWvHQ&m=nRRSaOsExEkT_bFVAZCaqRhdTZWJIFnWS4FJMZ-1u7k&s=eSH77OU-slC-H7pqw4JVg-TnhslxkRJwy-_faiwR4A8&e= Start new thread via email<mailto:[login to unmask email]> Unsubscribe from this mailing list<mailto:[login to unmask email]?Subject=Unsubscribe> Manage your subscription https://urldefense.proofpoint.com/v2/url?u=https-3A__www.idug.org_p_us_to_&d=DwMFaQ&c=UrUhmHsiTVT5qkaA4d_oSzcamb9hmamiCDMzBAEwC7E&r=KigCN-TlaQoAQDZrna0ZVLUX9H9vZpSibySEyujWvHQ&m=nRRSaOsExEkT_bFVAZCaqRhdTZWJIFnWS4FJMZ-1u7k&s=W03ciQX5dpoBrhnRhFG4Z04LJNKcNPeNfiBQ-mbZ0Nc&e=

This email has been sent to: [login to unmask email]<mailto:[login to unmask email]>

** ** ** IDUG DB2 Data and Analytics Technical Summit in Chennai, India 2018 ** ** **
---> Chennai, India, March 29, 2018 <---
http://ibm.biz/IDUGChennai2018 https://urldefense.proofpoint.com/v2/url?u=http-3A__ibm.biz_IDUGChennai2018&d=DwMFaQ&c=UrUhmHsiTVT5qkaA4d_oSzcamb9hmamiCDMzBAEwC7E&r=KigCN-TlaQoAQDZrna0ZVLUX9H9vZpSibySEyujWvHQ&m=nRRSaOsExEkT_bFVAZCaqRhdTZWJIFnWS4FJMZ-1u7k&s=xIWr9UKXr0n8ajeUngd0vuvbHphm0D3E9x1y_ywF_p4&e=

Use of this email content is governed by the terms of service at:
http://www.idug.org/p/cm/ld/fid=2 https://urldefense.proofpoint.com/v2/url?u=http-3A__www.idug.org_p_cm_ld_fid-3D2&d=DwMFaQ&c=UrUhmHsiTVT5qkaA4d_oSzcamb9hmamiCDMzBAEwC7E&r=KigCN-TlaQoAQDZrna0ZVLUX9H9vZpSibySEyujWvHQ&m=nRRSaOsExEkT_bFVAZCaqRhdTZWJIFnWS4FJMZ-1u7k&s=vyoa7nh9lWxjDyInIRfZipTj4AlK2RfuJkCA2iWD3TY&e=

________________________________
Attachments

  • image001.png (6.7k)

David Baldon

Where are the Data Capture Changes info kept in DB2
(in response to John Alexander)
John,
Have a look at the IDUG web site for a presentation I did last year in EMEA IDUG. It’s titled What’s Log Got To Do, Got To Do With It? I think it’s pretty much exactly what you’re looking for and a whole lot more.

...David

David Baldon
Solution Architect
DB2 Backup & Recovery
BMC Software, Inc.

From: John Alexander [mailto:[login to unmask email]
Sent: Thursday, April 12, 2018 9:05 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: Where are the Data Capture Changes info kept in DB2


Thanks Horacio, thats just a flag which tells me which table has been altered to data capture.

But what I am after is whether or not DB2 keeps track of the Ins/Del/Upd activity & the archlog they are on, for the altered tables. This info must be in DB2 somewhere , which the replication software reads in order to apply the DML changes on the target Oracle tables.

In my testing of Golden Gate , I noticed HSM recalls for migrated archlogs , which were required by Golden Gate 'Trckle Feed' process in order to apply those DML changes to the Oracle tables. So I figured that this info must be stored somewhere in DB2 , but so far i'v not been able to find it.

Regards

John

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

Chris Tee

Where are the Data Capture Changes info kept in DB2
(in response to Raymond Bell)
Raymond


I think you mean SYSLGRNX not BSDS.


Chris


________________________________
From: Bell, Raymond (Hosting Services, Technology) <[login to unmask email]>
Sent: 13 April 2018 09:13
To: '[login to unmask email]'
Subject: [DB2-L] - RE: Where are the Data Capture Changes info kept in DB2


John,



After the 3rd reading of your question I think I get what you’re asking. It looked like you were asking whether or not Db2 logs insert/update/delete activity, and of course it does – regardless of whether DCC is turned on or off for any given object. But you already know that, and it’s now what you’re asking.



Michael (I think it was) has mentioned it. The BSDS records when an object was opened for update, and when it was closed to further updates. This is also independent of whether DCC is turned on or off for any given object. All DCC does is forces DB2 to write the full before/after image of the changed row, rather than just from the 1st byte changed to the end of the row. Log scraping tools like ‘Golden Gate’ (never heard of that one until today, TBH), Q-Rep, Log Master, etc. all rely on the full row data to be able to replicate the changes elsewhere. But again, you already know that.



So just like DB2 would need to know when an object was possibly updated in the event of a PIT recovery (so it can look in the right places when rolling forward from the last FIC to the appropriate PIT), so these log-scraping tools need to know when an object was possibly updated so they can extract the data changes made. And they both do it via the same mechanism; they query the BSDS. And as Michael pointed out, with Db2 V11 (or was it V10?) you can literally query the BSDS to find out when an object was open for update. Or just use Report Recovery.



Management summary: the answer to, ‘whether or not DB2 keeps track of the Ins/Del/Upd activity & the archlog they are on, for the altered tables’ is Yes – which it does for all tables, not just DCC-altered ones. And the info is in the BSDS.



Cheers,





Raymond



Raymond Bell

Db2

Hosting Services, Technology

Royal Bank of Scotland Group

3rd Floor Regents House

40-42 Islington High Street

London N1 8XL

Mob: +44 (0) 7894 608214

Email: [login to unmask email]<mailto:[login to unmask email]>



The content of this email is confidential unless stated otherwise.

[cid:[login to unmask email]



From: John Alexander [mailto:[login to unmask email]
Sent: 13 April 2018 03:05
To: [login to unmask email]
Subject: [DB2-L] - RE: Where are the Data Capture Changes info kept in DB2



*********************************************
" 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 Horacio, thats just a flag which tells me which table has been altered to data capture.

But what I am after is whether or not DB2 keeps track of the Ins/Del/Upd activity & the archlog they are on, for the altered tables. This info must be in DB2 somewhere , which the replication software reads in order to apply the DML changes on the target Oracle tables.

In my testing of Golden Gate , I noticed HSM recalls for migrated archlogs , which were required by Golden Gate 'Trckle Feed' process in order to apply those DML changes to the Oracle tables. So I figured that this info must be stored somewhere in DB2 , but so far i'v not been able to find it.

Regards

John



-----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 understand that the content of your message may be monitored.



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
-----End Original Message-----

John Alexander

RE: Where are the Data Capture Changes info kept in DB2
(in response to David Baldon)

Michael, many thanks for the SYSLGRNX Sql, it'll give me exactly what I was after, its definitely worth
rexxing this sql to automate querying SYSLGRNX.

Really appreciate all the feedback, which has helped answering this question, which had puzzled me for a few months
now, since we installed Golden Gate, which is definitely not your usual Host DB2 DCC-Replication software.

The software is performing the initial DB2 extract & Oracle load OK, but we just can not get the second step, which is to apply
the DB2 DML changes to Oracle, to work successfully.
So we have shelved the apply DML changes step, & are just running the Extract & Oracle load process, every time we want to refresh the Oracle data.

 

Regards..john

Raymond Bell

Where are the Data Capture Changes info kept in DB2
(in response to Chris Tee)
D'oh! Guilty as charged, M'Lud. :o)

Raymond Bell
Db2
Hosting Services, Technology
Royal Bank of Scotland Group
3rd Floor Regents House
40-42 Islington High Street
London N1 8XL
Mob: +44 (0) 7894 608214
Email: [login to unmask email]<mailto:[login to unmask email]>

The content of this email is confidential unless stated otherwise.
[cid:[login to unmask email]

From: Chris Tee [mailto:[login to unmask email]
Sent: 15 April 2018 08:32
To: [login to unmask email]
Subject: [DB2-L] - RE: Where are the Data Capture Changes info kept in DB2


*********************************************
" 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"
*********************************************


Raymond



I think you mean SYSLGRNX not BSDS.



Chris

________________________________
From: Bell, Raymond (Hosting Services, Technology) <[login to unmask email]<mailto:[login to unmask email]>>
Sent: 13 April 2018 09:13
To: '[login to unmask email]'
Subject: [DB2-L] - RE: Where are the Data Capture Changes info kept in DB2


John,



After the 3rd reading of your question I think I get what you're asking. It looked like you were asking whether or not Db2 logs insert/update/delete activity, and of course it does - regardless of whether DCC is turned on or off for any given object. But you already know that, and it's now what you're asking.



Michael (I think it was) has mentioned it. The BSDS records when an object was opened for update, and when it was closed to further updates. This is also independent of whether DCC is turned on or off for any given object. All DCC does is forces DB2 to write the full before/after image of the changed row, rather than just from the 1st byte changed to the end of the row. Log scraping tools like 'Golden Gate' (never heard of that one until today, TBH), Q-Rep, Log Master, etc. all rely on the full row data to be able to replicate the changes elsewhere. But again, you already know that.



So just like DB2 would need to know when an object was possibly updated in the event of a PIT recovery (so it can look in the right places when rolling forward from the last FIC to the appropriate PIT), so these log-scraping tools need to know when an object was possibly updated so they can extract the data changes made. And they both do it via the same mechanism; they query the BSDS. And as Michael pointed out, with Db2 V11 (or was it V10?) you can literally query the BSDS to find out when an object was open for update. Or just use Report Recovery.



Management summary: the answer to, 'whether or not DB2 keeps track of the Ins/Del/Upd activity & the archlog they are on, for the altered tables' is Yes - which it does for all tables, not just DCC-altered ones. And the info is in the BSDS.



Cheers,





Raymond



Raymond Bell

Db2

Hosting Services, Technology

Royal Bank of Scotland Group

3rd Floor Regents House

40-42 Islington High Street

London N1 8XL

Mob: +44 (0) 7894 608214

Email: [login to unmask email]<mailto:[login to unmask email]>



The content of this email is confidential unless stated otherwise.

[cid:[login to unmask email]



From: John Alexander [mailto:[login to unmask email]
Sent: 13 April 2018 03:05
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: Where are the Data Capture Changes info kept in DB2



*********************************************
" 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 Horacio, thats just a flag which tells me which table has been altered to data capture.

But what I am after is whether or not DB2 keeps track of the Ins/Del/Upd activity & the archlog they are on, for the altered tables. This info must be in DB2 somewhere , which the replication software reads in order to apply the DML changes on the target Oracle tables.

In my testing of Golden Gate , I noticed HSM recalls for migrated archlogs , which were required by Golden Gate 'Trckle Feed' process in order to apply those DML changes to the Oracle tables. So I figured that this info must be stored somewhere in DB2 , but so far i'v not been able to find it.

Regards

John


-----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 understand that the content of your message may be monitored.



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
-----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 understand that the content of your message may be monitored.

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
Attachments

  • image001.png (6.7k)