Today at one of our GoldenGate customers we were faced with the following requirement: For one of the tables on the target we needed two additional columns which shall hold the insert and update timestamps for when the row was either inserted or updated. Just to be clear: It was not about getting the timestamps for the inserts or updates as they happened on the source but the target. In this post we’ll look at how GoldenGate can be configured to do the work requested.
All the below examples work with the well known scott/tiger schema. There is one extract running for capturing on the source and one replicat is re-playing the changes on the target (over sqlnet, no integrated mode here). This is the current status of the streams:
Pretty basic, no unusual stuff here. The table we’ll use for the scope of this post is the “project” table which has the following contents in a fresh scott/tiger installation:
Of course the table looks the same on the target:
To prove that the streams are really working lets add an additional row to the source:
… and then check if the row indeed was replicated to the target:
Ok, looks fine, so back to the initial requirement. We need two additional columns on the target:
In our case we needed to set a default value as both column are not allowed to contain NULL values. For the moment the content of the table on the target is:
In real life when the table which will be extended holds millions of rows the following will probably be too simple and you’ll need to spend some time on thinking on how you organize the updates. For the scope of this post this is fine:
From now on we have identical insert and update timestamps for all of the rows on the target:
A final check on the source for being sure that the default values work:
On the target:
All fine. Time to do the GoldenGate work. Obviously the first step is to stop the replicat:
Lets do the insert case in a first step by adding the following line (COLMAP) to the replicat configuration:
Start again:
Looks good from a configuration perspective. Time to start:
On the target we should now see the exact insert date of the record instead of the default value of the column:
Perfect, this works. Lets go on with the update case: All we need is to add the additional column for the update case and populate it:
Then stop and start the replicat to bring the changes into effect. Lets create a new row just to see that this does not have any effect on the insert case:
What we should see on the target are two rows with an exact insert date but a default update date:
Perfect. What about the update itself?
This should result in one exact update date for my fun project 4:
Perfect, works fine, too. Just to be sure that deletes still work lets do a final test:
The two additional columns should not prevent Goldengate from being able to delete the rows, lets see:
The fun project 4 is gone and all works as expected. Hope this helps ….
Cet article Letting GoldenGate automatically maintain the insert and update timestamps on the target est apparu en premier sur Blog dbi services.