All,
I will be loading data into a DB2 database, however if I get 'warning' messages and records don't load for whatever reason, I want to capture those records. I am not completely sure how to go about this, I know that the warning messages themselves have the record data in each message, but I don't know how to get that data to a file. Does anyone have an idea on how to go about this? I appreciate the help.
Thanks,
JB
Creating Error Report Files
Moderators: chulett, rschirm, roy
JB,
To catch the rows that are rejected, and capture specific data to a file can be done using constraints. Let's assume that you have one output. You will need to define an additional output link for the rejected data. It makes sense for this to be a sequential stage, although it can be Hash, ODBC etc...
You now have Link1 (target) and Link2 (reject) with the order of execution being Link1 and then Link2. You can define a constraint for Link2 similar to the following -
Link1.REJECTED
Also, ensure that you check the "Reject Row" box for Link2.
REJECTED will be set whenever a row fails either a constraint on Link1 or a write failed on Link1 and the data (whatever you define) will be sent down Link2 and written to the sequential or whatever file you have defined.
This is one way of capturing the information you require.
Hope this helps,
Michael Hester
To catch the rows that are rejected, and capture specific data to a file can be done using constraints. Let's assume that you have one output. You will need to define an additional output link for the rejected data. It makes sense for this to be a sequential stage, although it can be Hash, ODBC etc...
You now have Link1 (target) and Link2 (reject) with the order of execution being Link1 and then Link2. You can define a constraint for Link2 similar to the following -
Link1.REJECTED
Also, ensure that you check the "Reject Row" box for Link2.
REJECTED will be set whenever a row fails either a constraint on Link1 or a write failed on Link1 and the data (whatever you define) will be sent down Link2 and written to the sequential or whatever file you have defined.
This is one way of capturing the information you require.
Hope this helps,
Michael Hester
Stephan,
You are correct. A recommendation might be to check this box so that "all" others will also be caught. It's also a good idea to check this box if you want to check the log (DSGetLogSummary) to interrogate DSJ.LOGREJECT, which indicates that, there was activity on the reject link.
Otherwise, you are certainly correct.
Regards,
Michael Hester
You are correct. A recommendation might be to check this box so that "all" others will also be caught. It's also a good idea to check this box if you want to check the log (DSGetLogSummary) to interrogate DSJ.LOGREJECT, which indicates that, there was activity on the reject link.
Otherwise, you are certainly correct.
Regards,
Michael Hester
One or the other is sufficient. I usually just check the box as it's more obvious to me that way as to what is going on. And as Michael states, that way the reject row count is automatically logged - but be aware it is logged as a 'Warning'.
Don't forget that *all* rejects go down that row, it can become a problem if you have multiple targets and you don't handle the rejects in a 'generic' fashion in that case.
-craig
Don't forget that *all* rejects go down that row, it can become a problem if you have multiple targets and you don't handle the rejects in a 'generic' fashion in that case.
-craig
I noticed (but never tried) the output link variable REJECTED, which, unlike the others, is not preceded by a linkname. Using this as a constraint probably has a similar effect as checking the 'reject row' box, i.e. it will collect those records that were rejected by all other output links (but without issuing a warning if there really was reject activity).
Stephan
Stephan
This is all great, however this is stuff to do in a Transform stage, to use constraints. I am actually talking about after it leaves the Transform and actually atempts an insert into the table, it may run into referencial integrity issues or duplicate record issues, where it just leaves a warning message in the Job log and doesn't actually insert the record. Let me know if I need to clarify more.
JB
JB
JB,
I think using Linkname.REJECTED will do exactly what you require. Beside the records that failed to fulfill the constraint on the output link to your table, it will, as Michael described, collect those records that were rejected by the DBMS itself. You would even be able to record the reason (error code) for the reject (duplicate value for unique field, missing reference, etc) supplied by the DBMS for each record, using an output link variable, called something like Linkname.DBMSCODE.
Stephan
I think using Linkname.REJECTED will do exactly what you require. Beside the records that failed to fulfill the constraint on the output link to your table, it will, as Michael described, collect those records that were rejected by the DBMS itself. You would even be able to record the reason (error code) for the reject (duplicate value for unique field, missing reference, etc) supplied by the DBMS for each record, using an output link variable, called something like Linkname.DBMSCODE.
Stephan
Stephan is correct and there are many types of errors and returns that can be sent down the reject link. If you are still unsure of reject usage please refer to the documentation included with the product, which will go into more detail than we have attempted here. The long and short is that I believe this is the easiest/most correct way, not necessarily the only way, to accomplish what you want.
To test and confirm what has been posted, it would be a simple matter to define a reject link and then attempt to insert or update a row of data that you know will fail and experiment with different variations of constraints.
Also, the REJECTED will be set regardless of the failure, i.e., a constraint failure or target write failure. The Linkname.REJECTEDCODE will be non-zero if the row is rejected because of a write problem.
Regards,
Michael Hester
To test and confirm what has been posted, it would be a simple matter to define a reject link and then attempt to insert or update a row of data that you know will fail and experiment with different variations of constraints.
Also, the REJECTED will be set regardless of the failure, i.e., a constraint failure or target write failure. The Linkname.REJECTEDCODE will be non-zero if the row is rejected because of a write problem.
Regards,
Michael Hester