ETL Job Validation

This table provides answers to questions about ETL job validation.

Question	Answer
How do I confirm whether a job has run successfully?	Log onto Datastage director and check the status of the job. If the status of the job is "Finished" then the job competed Successfully with no errors or warnings. If the status is finished then you can go and query your target database and check if the number of rows populated in the target table are correct. If the status of the job is Finished (See Log ) then there are some warnings in the job and you need to check the log file to see what the warning is about and fix that particular issue and run it till the job completes with status Finished. Finally if the status is Abort then the job has some Fatal errors. You need to fix those errors and run the job again to make sure the job is in "Finished" status.
How can I test OWS, OWE, and MDW jobs?	OWS: Run the sequencer and check if the job status of the sequencer is in Finished. Then do a data compare between the source and the target table and make sure the data is matching exactly to the result set you have. OWE: Ensure the OWS data is populated first as the source for OWE jobs is OWS. Run the OWE job and make sure the job is in Finished status. Do a data compare with the source and target database to make sure the data that is populated in the target tables is matching to your expected result set. MDW: Ensure the OWS data is populated first as the source for MDW job is OWS. In some cases, the source can be OWE and in such cases the OWE jobs must be executed first before running the MDW jobs. Run the MDW job and make sure the job is in 'Finished' status. Do a data compare with source and target database and check if the data populated in the target database is matching to your expected results.
How do I validate a job execution by verifying the data?	Perform a Database compare. This can be done by handwritten scripts or by third party DBCompare tools.

Question

Answer

How do I confirm whether a job has run successfully?

Log onto Datastage director and check the status of the job. If the status of the job is "Finished" then the job competed Successfully with no errors or warnings. If the status is finished then you can go and query your target database and check if the number of rows populated in the target table are correct.

If the status of the job is Finished (See Log ) then there are some warnings in the job and you need to check the log file to see what the warning is about and fix that particular issue and run it till the job completes with status Finished. Finally if the status is Abort then the job has some Fatal errors. You need to fix those errors and run the job again to make sure the job is in "Finished" status.

How can I test OWS, OWE, and MDW jobs?

OWS: Run the sequencer and check if the job status of the sequencer is in Finished. Then do a data compare between the source and the target table and make sure the data is matching exactly to the result set you have.
OWE: Ensure the OWS data is populated first as the source for OWE jobs is OWS. Run the OWE job and make sure the job is in Finished status. Do a data compare with the source and target database to make sure the data that is populated in the target tables is matching to your expected result set.
MDW: Ensure the OWS data is populated first as the source for MDW job is OWS. In some cases, the source can be OWE and in such cases the OWE jobs must be executed first before running the MDW jobs. Run the MDW job and make sure the job is in 'Finished' status. Do a data compare with source and target database and check if the data populated in the target database is matching to your expected results.

How do I validate a job execution by verifying the data?

Perform a Database compare. This can be done by handwritten scripts or by third party DBCompare tools.