Dropped records

[RSchibi]

I loaded a sequential data set into a db2 table. Three of the records did not load since a date field had invalid data ('**********'). I have corrected the date problem. My concern is that the job did not abort, but had a successful completion. The director did say that the job had a binary load error. When we go production, we will not be looking at the director. We depend on the job to abort if something is wrong.

I am looking for a way to count the number of records in the input dataset & the number of records that loaded, then compare them (without manual intervention).

Or is there a way to capture records dropped from a load job?

I appreciate any help getting started!

[nivas]

Hi

Develop a job control and in that we can get the link info using
DSGetLinkInfo routine. If the in and out row count is not equal we can abort the job.

Using the constraints putting a condition for the date val also we can trap the invalid data into a error file.

There are many ways. Generally there is a Audit process which controls these issues. THis Audit process we need to develop.

thanks
Nivas

[T42]

It is a known issue with Ascential that errors from DB2 are not identified by DataStage as an error. I do not know if it was fixed in 7.1r2 (haven't tested that one yet on my side), or 7.5.

Please report this to Ascential Support so they can be aware of this being a problem for your company.

[dsxuserrio]

Hi
If you have Data Quality Check scripts you can add two lines to check it.

USe dsrecords <datastaname> to get the count of records in the dtaaste

selct count(*) from table where timstamp > previous run

Thanks
dsxuserrio

[GIDs]

I loaded a sequential data set into a db2 table. Three of the records did not load since a date field had invalid data ('**********'). I have corrected the date problem. My concern is that the job did not abort, but had a successful completion. The director did say that the job had a binary load error. When we go production, we will not be looking at the director. We depend on the job to abort if something is wrong.

I am looking for a way to count the number of records in the input dataset & the number of records that loaded, then compare them (without manual intervention).

Or is there a way to capture records dropped from a load job?

I appreciate any help getting started!

have you tried dragging a reject link off of your target DB stage?

[vigneshra]

Hi

Write a post-job DataStage basic subroutine that counts the number of records from the source link and the database write link. Then match the counts and if they do not match conclude that there is some error. I don't think reject capture will help you in this regard because the records are not actually getting rejected at that stage.

-------------------------
Vignesh.

[GIDs]

Hi

Write a post-job DataStage basic subroutine that counts the number of records from the source link and the database write link. Then match the counts and if they do not match conclude that there is some error. I don't think reject capture will help you in this regard because the records are not actually getting rejected at that stage.

-------------------------
Vignesh.

The way DS works is, for every record that hits the DB, the SQL return code does get sent back to the DS API, thus, the DB rejects can be captured on the reject link.

We have this working on an ORA target stage, which has a property 'Output reject records' set this to true, drag an output link from the stage, this should do it.

The only case where this does not work is when you use the LOAD method in place of the UPSERT ot UPDATE methods. This is because the data gets temporarily landed onto disk, while on its way to the DB, thru SQLLDR and thus DS does not have any kind of control.