Data Mirror Files

wjfitzgerald · Post by **wjfitzgerald** » Tue Feb 03, 2009 5:41 am

Hi,

As always I hope some one can point me in the right direction, please?

I am processing a number of DataMirror files. Each files contains details of data changes to a particular table. Each file has 3 record types.
I - insert, new record
B - update, data before change was applied
A - update, data after change was applied
(I believe it is possible to give you the before and after data on the one record, but I am not able to get changes made to Data Mirror at the moment)

What I need to do is process all changes to certain fields. As a standard here, Data Mirror reflects all changes on the table and it is up to each user of the file to determine there own action.

so for example if I was looking at a file of customer data, the table only contains:
CUST_NO
CUST_NAME
CUST_ADDRESS
CUST_PHONE

If I want take an action when a customers phone number changes, what is my best way of identifying this change?

Sorry about the convoluted question, just trying to make it as clear as possible.

As always, thanks for the help.

John Fitz

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Tue Feb 03, 2009 6:14 am

Use the primary key of the table to track the change record and update it correspondingly.

wjfitzgerald · Post by **wjfitzgerald** » Tue Feb 03, 2009 6:22 am

Thanks for coming back.

My problem is that in order to determine if the PHONE-NO has changed i need to read two records from the incoming sequential file.

For example, if the incomnig file had 5 records, where the first one was an insert on customer number 1 and the second was the before and after data for a change to the existing customer number 2, where the change is to the address. and finally the before and after data for a change to the existing customer number 3, where the change is to the PHONE_NO.

I would need to read record one, recognise that it is a new customer and therefore need to be procesed.

Then i would need to read record 2 and seeing that it is a before data record, read record 3, by comparing the phone number on both of these i could then determine that the phone number has not changed and so no further action is required on these records

On reading the 4th record and seeing that it is a before data record, read record 5, by comparing the phone number on both of these i could then determine that the phone number has changed for this customer and so this custoemr needs to be processed.

Thanks

John FItz

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Tue Feb 03, 2009 6:27 am

Data Mirror can be configured to trace changes on specific columns.

If the input has more columns that you need, you can do an internal change-compare of redup to identify changes.

Anyway, you can update all columns so to keep up to date.

wjfitzgerald · Post by **wjfitzgerald** » Tue Feb 03, 2009 6:33 am

I appreciate that data mirror can do column specific checks, but that requires, data mirror to put out a file for each individual field yuo want to monitor. If you monitor a set number of fields, anyone of these changing is cause for all those fields to be written to the file.

In event, as i mentioned in the original post. the standard here is to reflect any change to the structure, and i do not have facility to change data mirror.

The action, which i have not defined, is to send a message to a third party system. there is noupdate facility. i am not maintaing data, just passing on approriate info.

Regards,

John FItz