Hi,
Recently, when we were presenting a DataStage solution which contains a simple Oracle to Oracle load of a large table, we were bombarded with a question saying that there could be insertion issues. And their argument is that because of those issues, if the "write" fails, then we need to do a "read" again on the database and then insert the data. So, we were suggested to "write" the data into a flat file first and then do a Bulk load into the table, reading from the flat file so that even if the insert fails, there is no need to read again from the database.
In this regard, I need to know how robust is a DataStage job (OCI to OCI load)? How frequently or under what circumstances the insertion may fail? And is it a better approach to use the Flat file approach than a DataStage stage approach?
Cheers,
Mohan
Insertion issues
Moderators: chulett, rschirm, roy
-
ray.wurlod
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
You should be able to detect within DataStage that the write has failed, and capture the row that failed to be inserted (read about reject links).
What makes "them" think that the second INSERT would succeed when the first INSERT had failed?
What makes "them" think that the second INSERT would succeed when the first INSERT had failed?
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
There are overtly paranoid people in this little world of our.
DataStage is a very robust tool, despite its flaws. As one of the client said, after bombarding DataStage with billions of rows, forcing the poor UNIX box onto its knees for hours at a time, with nary a blimp -- "DataStage is definitely a very solid platform."
If something fails, you will know it, and can deal with it. If there is a bug within the program, you will know it, and will have a challenge dealing with it. If there is a bug within the framework, you will know it, but you will not know how to deal with it.
In this case, as long as the Table to Table load is not to the same table, you will usually be just fine, unless you're dealing with a crappy database setup, a crappy network setup, a crappy hardware setup, ... and so on forth.
No matter how much you assure them, the paranoia will remain, and they will find every opportunity to validate this paranoia.
Use a dataset stage to land the data if this will satisfy their worries.
DataStage is a very robust tool, despite its flaws. As one of the client said, after bombarding DataStage with billions of rows, forcing the poor UNIX box onto its knees for hours at a time, with nary a blimp -- "DataStage is definitely a very solid platform."
If something fails, you will know it, and can deal with it. If there is a bug within the program, you will know it, and will have a challenge dealing with it. If there is a bug within the framework, you will know it, but you will not know how to deal with it.
In this case, as long as the Table to Table load is not to the same table, you will usually be just fine, unless you're dealing with a crappy database setup, a crappy network setup, a crappy hardware setup, ... and so on forth.
No matter how much you assure them, the paranoia will remain, and they will find every opportunity to validate this paranoia.
Use a dataset stage to land the data if this will satisfy their worries.