Error logging using Oracle OCI

davidthree · Post by **davidthree** » Tue Nov 11, 2003 3:41 am

Hi

I've been using the Oracle OCI stage in DataStage 5.2.2. The stage writes out a number of rows to various tables in the database, and rolls back all rows should an error be encountered when writing any one row (eg. value too long for column).

In the transformer which feeds the OCI stage, I also have a reject row with no constraints on it. In the case that rows fail constraints within the transformer, rows are passed to this reject file. However, in the case that a row fails at the database, no row is written to the reject link.

I understand from reading past posts that this functionality may not be implemented in some version of DataStage. Assuming this is the case for 5.2.2, can anyone offer advice on a way in which I could remedy the problem? I don't want to use an ODBC stage, as Oracle doesn't support any ODBC clients on UNIX. Also, there are many database constraints and so validating data before it reaches OCI would be very expensive.

Thanks for your help

David

rasi · Post by **rasi** » Tue Nov 11, 2003 5:37 am

Hi David,

If you want the reject then you need to change the output sequence in your transformer. Change the output sequence to reject first and then to the table you will get the records.

Cheers
Rasi

davidthree · Post by **davidthree** » Tue Nov 11, 2003 6:07 am

Hi Rasi

That approach returns all rows into the reject file, whether they are rejected by Oracle or not. I just want to get the rows that are rejected by the database.

David

chulett · Post by **chulett** » Tue Nov 11, 2003 6:42 am

It's not so much as that functionality isn't implemented in some versions of DataStage, more like it's broken.

If that's the case with your current version (and you should be able to very that with Support I would think) then you are basically out of luck. Broke is broke and from what I remember, once a row goes down that OCI link, it ain't never coming back.

I believe that your only recourse in a situation like that is to upgrade. Or, as you said, switch to ODBC.

trobinson · Post by **trobinson** » Tue Nov 11, 2003 8:23 am

We are 5.2.2, Oracle 9.02 on HP-UX 11.11. I imagine it is close enough to your installation for the following to apply.

1. You can use ODBC UNIX with Oracle. Not a problem.

2. Try changing your REJECT link to have a contraint where the DBMSCODE <> 0. Sometimes Oracle will return the ORA- code that caused the SQL to fail. You may be able to trap it with DBMSCODE and write it to a reject file. Personally I never use reject links. Don't trust 'em. I always interrogate the DBMSCode and decide what to do at that point. BTW - There are Oracle errors that cannot be trapped, unfortunately. Mostly these will abort your job anyway.

davidthree · Post by **davidthree** » Tue Nov 11, 2003 9:39 am

The DBMS code is null in most cases, so this approach doesn't work (writes all rows to the reject file). However, I tried some similar approaches and it seems that, although the unconstrained reject row fails to work without any constraint, the Linkname.REJECTED variable is still set correctly.

I've therefore implemented a constraint on the reject row along the lines of:

Linkname1.REJECTED OR Linkname2.REJECTED OR Linkname3.REJECTED OR ...... and so on

This causes rows rejected by the database and only rows rejected by the database to be written to the reject file. (Perfect!)

I don't have any constraints on my rows, but I guess if you did, you could simply add the logical negative of these to the end of the reject constraint with another OR. (Alternatively have a seperate reject file.)

Thanks to all.