catching rejected records - parallel job

muascdev · Post by **muascdev** » Wed Jan 10, 2007 7:29 pm

i am trying to catch rejected records while inserting records into oracle database. tried with server job, even though log has the error message, none of the variables like REJECTEDCODE or DBMSCODE had values in it.
it had values when there is an issue with the column values. what i want to catch is 'foreign key constraints or unique key constraints rejetcs'. opened a ticket with IBM, he said server stages have issues, try parallel job. so i created a parallel job , and i need to know how to capture those rejected records. it does have variables like DBMSCODE or REJECTEDCODE . pl tell me how do i capture the rejects.

ray.wurlod · Post by **ray.wurlod** » Wed Jan 10, 2007 7:56 pm

If I recall correctly, you can use a reject link from the Oracle Enterprise stage. This gives you options to add extra columns to the reject link (look in the available options to add).

muascdev · Post by **muascdev** » Wed Jan 10, 2007 8:17 pm

all it has is OTHERWISE. it doesnt have DBMSCODE or REJECTEDCODE

DSguru2B · Post by **DSguru2B** » Wed Jan 10, 2007 8:41 pm

Not in the transformer, in the enterprise stage.

muascdev · Post by **muascdev** » Wed Jan 10, 2007 9:02 pm

i see an option to 'output reject records'. i set it to true. but i dont see an option to specify the reject link anywhere. the other thing i want to do is only insert but not allow updates, i dont see insert option only

ray.wurlod · Post by **ray.wurlod** » Wed Jan 10, 2007 9:11 pm

Having set it to true and drawn the reject link, check the Columns on the reject link - you will see sqlcode there (automatically, not an option as I at first thought).

To do insert only, you need to check first whether the key already exists in the table. Depending upon the size of the table, use a sparse lookup or pre-load a list of keys into something that can serve a reference input link to a Lookup stage. Use a Filter stage to transmit only rows for which the returned key value is null.

muascdev · Post by **muascdev** » Wed Jan 10, 2007 9:20 pm

as soon as I set the option to true and come out of it my stage icon has yellow symbol indicating error. I opened transformer and there is no column added to the reject link or the valid link. i dont know if i am doing anything wrong here

muascdev · Post by **muascdev** » Wed Jan 10, 2007 9:23 pm

oracle enterprise stage doesnt have insert only uption. in the upsert mode all it has update and insert or update only

ray.wurlod · Post by **ray.wurlod** » Thu Jan 11, 2007 1:27 am

Which piece of this was unclear?

ray.wurlod wrote:To do insert only, you need to check first whether the key already exists in the table. Depending upon the size of the table, use a sparse lookup or pre-load a list of keys into something that can serve a reference input link to a Lookup stage. Use a Filter stage to transmit only rows for which the returned key value is null.

johnthomas · Post by **johnthomas** » Thu Jan 11, 2007 4:55 am

Ray ,
I dont think we can have reject captured for oracle enterprise stage, when we use bulkload option.we have to see the log file in scratch directory to find this . Also one important point to note is eventhough there is error and
the error count is below 50 , datastage will report that everything was sucessfull

.This scenario is because datastage doesn not allow us to modify the errors parameter for sqlldr and it sets the errors=50 by default.

ray.wurlod · Post by **ray.wurlod** » Thu Jan 11, 2007 6:18 am

That is true for write mode Load. You must check the log (and maybe bad) file from sqlldr.

However, the OP is using Upsert mode, and bemoans the fact that there's no Insert only option with that.

trobinson · Post by **trobinson** » Thu Jan 11, 2007 7:00 am

Server solution: the DBMSCODE will be available in the transformer after the link that goes to the Oracle OCI stage. This code value can be used in a constraint on a "reject" link to for example a Sequential file. If the DBMSCODE is non-zero, the row that just failed on the above link would flow down the "reject" link.

vmcburney · Post by **vmcburney** » Thu Jan 11, 2007 5:02 pm

I wrote in some detail about trapping rejected rows from the Enterprise database stage in this blog post. The easiest way to work out what is going on in your reject link is to point it to a Peek stage and run the job. You should be able to see the database message fields and values in the Director log. The reject link out of a database stage is only valid if you have the right reject option switched on and have selected an action that supports rejects: inserts, upserts, updates, deletes. The bulk load has its own method of rejecting external to DataStage.

muascdev · Post by **muascdev** » Fri Jan 12, 2007 4:38 pm

all the stages in Datastage Enterprise ediion 7.5.2 cant capture 'unique key constraints or foreign key constraints' even though you see the error in the log. i tried with server DRS , oracle, odbc stages, and also with parallel oracle enterprise, DRS and odbc stages. none of it worked. i did this along with ibm support guy on webex. if u provide a reject link in parallel job, it gives 'unable to identify sql code error'. IBM guy said he will send me a patch.

ray.wurlod · Post by **ray.wurlod** » Fri Jan 12, 2007 6:43 pm

Who is "u" ?

There's no-one of that name enrolled on DSXchange.