6 General questions about ETL design with DataStage 4

[13531]

Hi folks,

I am doing the design of our ETL process with DataStage. I have some big questions in mind and I can't find answers in DataStage's document. I need help or directions. Thanks in advance.

1. Does DataStage "commit" or "rollback" the result of a load automatically to keep data consistent? If not, do I need to control it by coding? Creating a routine, a stage or whatever?

2. Do I need to design jobs that can rollback the whole load in case something is wrong (e.g. the source data file is wrong)?

3. If only one job fails in a sequence, can I just re-run
that particular job? What is the common way?

4. What's the difference to direct rejected data to file or table?

5. I need to design extra jobs to reload rejected data after. Is that right?

6. Any other big issues should be addressed when designing ETL process?

Your insights are really appreciated.

Max

 

[BocaBurger]

#2. Depends on your business requirements.
#3. Depends on how you wrote the jobs.
#4. Not much, just where do you want the data.
#5 Why would you want to load rejected data?
#6. You need to get the system requirements before you start.

If you have 7.5.1, you can use MapStage and have more control of what your jobs do, as to rollback and commits, where bad data gets sent, etc.



BocaBurger
<===========================||////////////////|0
The pen is mightier than the sword, but the sword hurts more!

 

[johnherman]

1. Generally, you control it. There is a place in the Transaction Handling tab for that Database load step where you can specify "commit after how many records". Commit after 0 records means commit all or nothing, which is the default.

-------------------------
The reasonable man adapts himself to the world. The unreasonable one persists in trying to adapt the world to himself. Therefore all progress depends on the unreasonable man. - George Bernard Shaw

 

[13531]

thanks, I got it.