when using DB2 UDB records are dropped No warnings

haimurali · Post by **haimurali** » Mon Mar 13, 2006 9:25 am

I am using two jobs to load two DB2 tables A and B.
Table A's primary key is forein key in Table B.

After loading A, while loading B I am not getting any warnings when there is a referential integrity viloation. The records were dropped/not loaded in B. The job was successful WITHOUT warnings.

Is there a setting I need to do get the warnings in case of Referential integrity viloation when using DB2 UDB stage.

ArndW · Post by **ArndW** » Mon Mar 13, 2006 9:53 am

Put a link to a reject sequential file in the transform stage where you are loading to DB/2. Use the contstraints to push links out to the reject (click on the "..." button to get output variables) as well as to choose which database information to write.

haimurali · Post by **haimurali** » Thu Mar 16, 2006 4:28 pm

The requirement is that the DB2 UDB Stage has to cause the job to Abort in case of referential integrity viloation(due to SQL error from the database) while inserting records.

Currently it is just dropping records that have referential integrity violation and job completes successfully.

rasi · Post by **rasi** » Thu Mar 16, 2006 9:16 pm

Murali

I don't think that there is a setting to abort jobs in case of referential integrity violation. You have implement this logic in your job by doing a lookup to the parent table to find out whether record exist and then insert. You can then take necessary action whether to make the job abort or not.

kumar_s · Post by **kumar_s** » Thu Mar 16, 2006 11:10 pm

It is always better not to abort the flows.
But you can do this by adding a transformer in reject link and you can provide the constraint as abort after 1 row. But need to make sure that all the rejects are due this reason.
Or you can do a post checking with the job log, for the specific SQL error code and issue a warning or can notify.

chulett · Post by **chulett** » Fri Mar 17, 2006 6:32 am

haimurali wrote:Currently it is just dropping records that have referential integrity violation and job completes successfully.

I'd be suprised if it is actually completing "successfully". It should be throwing warnings into the log and have a status of "Finished (see log)" when done. It would abort once the number of warnings exceeds your threshold, the default of which is 50, so I would think you had fewer than that. This time.

So, different options. Run the job with a much lower warning threshold. Add a reject link as Arnd suggested to explicitly capture them. You could even set it to 'Abort after X rows' in the constraint such that 1 was all it took. If you need to capture all of them and then abort at the end, you'll need to get tricky in the after job part of town.

Also note that some stages have a checkbox for 'Treat warnings as fatal' which is yet another way to handle this.

DSguru2B · Post by **DSguru2B** » Fri Mar 17, 2006 6:58 am

chulett wrote: I'd be suprised if it is actually completing "successfully". It should be throwing warnings into the log

Craig, this is possible. I have had this issue earlier and honestly speaking if you see the logs, you see all GREENS.

haimurali
What you can do is that have this job abort in sequence itself. You can go by the abortion of the process at the job level or Sequence level.
You can select maxtimestamp on the rows and compare it with the currenttimestamp. I hope it helps.
Good luck.

DSguru2B · Post by **DSguru2B** » Fri Mar 17, 2006 7:00 am

OR you can update your process control tables if you have one. Those tables are always handy. And based out of that table you can have your sequence be aborted.

chulett · Post by **chulett** » Fri Mar 17, 2006 7:00 am

If that's the case, it must be a DB2 stage/database thing as that's certainly not possible with Oracle and OCI.

kumar_s · Post by **kumar_s** » Fri Mar 17, 2006 7:03 am

DSguru2B wrote:
chulett wrote: I'd be suprised if it is actually completing "successfully". It should be throwing warnings into the log
Craig, this is possible. I have had this issue earlier and honestly speaking if you see the logs, you see all GREENS.

haimurali
What you can do is that have this job abort in sequence itself. You can go by the abortion of the process at the job level or Sequence level.
You can select maxtimestamp on the rows and compare it with the currenttimestamp. I hope it helps.
Good luck.

Hi Can you explain how Timestamp comparison can be useful for Referential Integrity violation.

DSguru2B · Post by **DSguru2B** » Fri Mar 17, 2006 7:31 am

What I am trying to convey is that whatever rejects he gets can be captured in a process control table. He can select the maxtimestamp on that table and compare it with( not Currenttimestamp

but ) with the job start time. If it is greater than that then, he can pass the result to a job abort script or the sequence that is being used to control that job and have it report it.
I am not sure about this but some changes can be made to the message handler, but i guess its for EE/Px only.

I am just advising another way of reporting the violation of referential integrity.

haimurali · Post by **haimurali** » Fri Mar 17, 2006 9:47 am

Chulett, Thank you all for your suggestion.

I was surprised to know that there is no way we can transulate an SQL error (such as referential integrity, data integrity, unique constraints.. etc )to a job to Abort.

Many ETL tools like Informatica and also DataStage PX too Abort jobs due to an SQL Error.

ray.wurlod · Post by **ray.wurlod** » Fri Mar 17, 2006 9:49 am

A job sequence in which all conditions are handled will finish successfully. The very act of coding triggers to handle failures asserts that you want to specify how they're to be handled. You could include a Routine actvity calling UtilityWarningToLog if you want to force a warning message to occur, or (in 7.5 and later) check the job sequence compilation option to log a warning if an activity fails.

chulett · Post by **chulett** » Fri Mar 17, 2006 9:51 am

haimurali wrote:I was surprised to know that there is no way we can transulate an SQL error (such as referential integrity, data integrity, unique constraints.. etc ) to a job to Abort.

This is not at all true. You have a specific issue with DB2 and whatever stage you are using. Perhaps a setting in the database needs to be changed? I don't know DB2 so others more familiar with it should chime in as I'm sure this is solvable...