DSXchange

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.

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).

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

Not in the transformer, in the enterprise stage.

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

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.

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

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

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.

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.

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.

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.

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.

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.

Who is "u" ?

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