DB2 V8.1on z/OS - data change capture

Kannan Perumal

DB2 V8.1on z/OS - data change capture
Hello Experts,

DB2 V8.1 (NFM) on z/OS R9.

I am looking for options to capture the table level changes.
I am thinking about few options, would like to know the pros and cons from
your past experience.
If someone has done some benchmark and would like to share please do.
We would like to keep all the changes (I mean all versions or old values).
I am also looking at tools that are available in the market [of course the
last choice :)].

1. Change data capture - many years ago I used it to replicate not sure how
effective
it will be to capture table level changes. I understand it will capture
all DML changes
we are ok with that as there will be no delete, only insert and very few
updates
yes turning on this will have its own over head. I could see IBM
InfoSphere etc..
2. Having said ""no delete, only insert and very few updates" trigger will
be a good choice.
The volume of the table might not be that big according to the mainframe
standards :)
(2-3 million), tables may have RI
3. Handling it inside the application program logic. Yes this involves
overhead etc..
still worth a visit or not?
4. How about Audit changes? yes with the proper audit class turned on, how
much effort to process?
5. Getting a tool that does it all, I know we have to pay for it
however if that saves our time and give some nice features that is
something worth looking.
I am looking into couple of products, would like to get some real-time
good/bad experience
* How about processing the log? how much effort/effective that will be?
I am looking for the most effective way so that the impact will be mild or
nothing from the performance standpoint.
Any pointers or documentation will be appreciated.

Thanks for all your time and input, as always I appreciate that very much!

______________________________________________________________________

* 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

Phil Grainger

Re: DB2 V8.1on z/OS - data change capture
(in response to Kannan Perumal)
Kannan



I have embedded my comments in your questions, below..... (look for
[pg])



Phil Grainger

CA

Senior Principal Product Manager

Phone: +44 (0)1753 577 733

Mobile: +44 (0)7970 125 752

eMail: [login to unmask email]



Ditton Park
Riding Court Road
Datchet
Slough
SL3 9LL



CA plc a company registered in England and Wales under company
registration number 1282495 with its registered office at the address
set out above. VAT number 697904179.



From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Kannan Perumal
Sent: 17 December 2008 12:40
To: [login to unmask email]
Subject: [DB2-L] DB2 V8.1on z/OS - data change capture



Hello Experts,

DB2 V8.1 (NFM) on z/OS R9.

I am looking for options to capture the table level changes.
I am thinking about few options, would like to know the pros and cons
from your past experience.
If someone has done some benchmark and would like to share please do.
We would like to keep all the changes (I mean all versions or old
values).
I am also looking at tools that are available in the market [of course
the last choice :)].

1. Change data capture - many years ago I used it to replicate not sure
how effective
it will be to capture table level changes. I understand it will
capture all DML changes
we are ok with that as there will be no delete, only insert and very
few updates
yes turning on this will have its own over head. I could see IBM
InfoSphere etc..

[[pg] ] The overhead of Data Capture Changes is actually MUCH lower than
most people imagine. Even more so when you say "very few updates" as DCC
will have NO OVERHEAD WHATSOEVER for Inserts and Deletes!!


2. Having said ""no delete, only insert and very few updates" trigger
will be a good choice.
The volume of the table might not be that big according to the
mainframe standards :)
(2-3 million), tables may have RI

[[pg] ] The problem with triggers to create an audit table of changes is
that you will make a change, DB2 will log that change, then your trigger
makes an "audit insert" and DB2 logs that too - so now you have THREE
copies of your change - 2 in the log and 1 in your audit table


3. Handling it inside the application program logic. Yes this involves
overhead etc..
still worth a visit or not?

[[pg] ] Are you sure everyone will always do it the right way? What
about any changes that are made outside the application. This is
possibly the choice of last resort!


4. How about Audit changes? yes with the proper audit class turned on,
how much effort to process?

[[pg] ] No use I'm afraid - the audit traces tend to only tell you about
the FIRST change a unit of work does


5. Getting a tool that does it all, I know we have to pay for it
however if that saves our time and give some nice features that is
something worth looking.
I am looking into couple of products, would like to get some
real-time good/bad experience
* How about processing the log? how much effort/effective that will be?

[[pg] ] Perhaps I am biased, but this would always be my choice. DB2
(through logging) is already creating your audit trail for you - all you
need to do is make use of it. You could write your own log reader
application (the documentation is pretty thorough, if a little scary)
but then you'd have to maintain it through all your DB2 upgrades and
maintenance. Buying a tool does protect you from that hassle. Yes, there
will be a cost, but there is a cost to all the other choices too - just
because someone doesn't have to write a cheque doesn't mean you haven't
had to spend money to implement your chosen solution. Any of the "big
three" (BMC, CA, IBM)'s auditing tools will likely do what you need (and
much more besides)

I am looking for the most effective way so that the impact will be mild
or nothing from the performance standpoint.

Any pointers or documentation will be appreciated.

Thanks for all your time and input, as always I appreciate that very
much!



________________________________

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

Mark McCormack

DB2 V8.1on z/OS - data change capture
(in response to Phil Grainger)
Kannan,

We have a few tables for which we have set 'data capture changes'.
There is the potential for writing extra log data. Phil Grainger
pointed out that there is no extra logging for inserts and deletes, as
the whole row is logged anyway. You will not get significant extra log
volume unless you make many, many updates where column(s) updated are
much shorter than row length.

There have been a few surprises, however. For example, you cannot:
ALTER TABLE .. ALTER COLUMN
when data capture changes is set. Instead you must:
ALTER TABLE .. DATA CAPTURE NONE
ALTER TABLE .. ALTER COLUMN
reorg tablespace
ALTER TABLE .. DATA CAPTURE CHANGES

Using log files as a source of audit data has its own quirks. DB2
logging is designed for recovery processes, not auditing. We have made
attempts to use log data for auditing via IBM's Log Analysis tool. It
has been frustrating for tables without 'data capture changes'. Showing
the values for all columns in a row before and after a change requires
some data from the log records and some data from the table rows.
Anything that changes RIDs (reorg, load replace) makes that difficult or
impossible, especially for those (like us) who use flashcopy / dasd
volume backups instead of the DB2 copy utility to take normal backups.
If you choose to investigate a vendor product for this purpose, ask
about limitations.

Mark McCormack
State Street Corp.

______________________________________________________________________

* 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