I have an ODBC Stage that SELECTs rows from a System i table/physical file. This feeds a Transformer stage that puts the rows on respective INSERT/UPDATE/DELETE links to a second ODBC Stage writing to another database. Once all of the records have been processed in the second ODBC Stage, I would like to delete ("prune") all of the records I have read from the original System i table/physical file.
The Transformer Stage (in the middle) has a value used to identify the records to be deleted. I need to have the Transformer to feed that value to a third ODBC Stage to process the deletion, but only after all of the records have been processed in the target database (second ODBC Stage). I know how to pass the value from the Transformer. How do I know when the target database records have all been processed? How can I tell when the second ODBC Stage has finished successfully processing all of its input records?
Structuring a Server Job to Delete/Prune Input Records
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 15
- Joined: Tue Jul 27, 2010 1:33 pm
- Contact:
Re: Structuring a Server Job to Delete/Prune Input Records
If your source is a table, you will have to split your process into two jobs to delete the data from the source. If your source is a physical file then you can write your flagged records from the transformer stage into a sequential file and overwrite your source file as part of your after job subroutine.
Kris
Where's the "Any" key?-Homer Simpson
Where's the "Any" key?-Homer Simpson
You can have your transform stage output records to a flat file; then have an output link on that file which won't get executed until the last row has been written to it.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Participant
- Posts: 246
- Joined: Mon Jun 30, 2008 3:22 am
- Location: New York
- Contact:
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
First-and-foremost, is the source table used by any other system ? If so, modifying its content will impact them. Have you discussed your method with such potential system's owners ?
Second, if the source updated 24 x 7. If so, what about the records which may be modified by the time your system selects from it ? You may loose them.
Best method will be to include a 'modified timestamp' in the source table populated either by the systems creating the source or as a trigger and using this value in your extraction. This way, you do not hinder the source users and ensure that all records are accessed.
This will help for future audit also. I am sure you will face this.
Second, if the source updated 24 x 7. If so, what about the records which may be modified by the time your system selects from it ? You may loose them.
Best method will be to include a 'modified timestamp' in the source table populated either by the systems creating the source or as a trigger and using this value in your extraction. This way, you do not hinder the source users and ensure that all records are accessed.
This will help for future audit also. I am sure you will face this.
-
- Participant
- Posts: 15
- Joined: Tue Jul 27, 2010 1:33 pm
- Contact:
Solution Found!
Thank you to all who responded to this post. I have blogged on the integration of InfoSphere Change Data Capture and DataStage - including the solution to the pruning question I had posted here.
http://www.thefillmoregroup.com/blog/?p=369
http://www.thefillmoregroup.com/blog/?p=369