Array Size and its effect on OCI error reporting

chulett · Post by **chulett** » Sat Jul 23, 2005 8:03 am

Is anyone finding that in more recent versions of DataStage and the OCI stages (say, 7.x) that there is still a problem with error reporting and the array size?

In the past, if you wanted ORA errors to be properly reported you needed to leave the Array Size at 1. That way if it said you had three duplicate key violations and dumped the rows back into the log, you knew there actually were three and those were the ones. Bumping the array size up would cause 'mismatches' with reality. One error might be reported as three and the rows bounced back down the reject link may not be the actual problem children.

In the past, I haven't worried alot about this, we routinely do 'all or nothing' loads and the first error aborts the job. Now, a new project wants to allow a certain level (up to a threshold) of these errors to be captured and shoved into an error reporting system. Leaving the array size at 1 for some of the loads we're doing makes things rather... slow. However, I'm afraid that bumping it up for speedier loads may make the error reporting 'less than reliable'.

I was wondering if anyone has had similar experiences or how they are handling things of this nature.

elavenil · Post by **elavenil** » Sat Jul 23, 2005 10:08 am

We are using DS 7.5 and OCI 9i stage and array size is being mentioned as 500. We used to get the number ORA error messages and the number of problematic records matched while running the jobs.

HTWH.

Regards
Saravanan

chulett · Post by **chulett** » Sun Jul 24, 2005 9:22 pm

I built some specific test cases and they are showing a problem with the Array Size set to anything other than 1.

Sent 200 rows to an Oracle target and used an array size of 100. Made sure two of the rows (13,69) in the first 100 would be bad and cause an ORA-01400 'cannot insert null' failure. It was interesting to note that the job's log always showed the correct row failures, however both the reject capturing and job monitor stats were wrong. Didn't seem to matter if the reject row was constrained logically or via the checkbox, it always captured row 100 as the rejected row.

Change the array size to 50 and (surprise) it would capture 50 and 100 as the rejected rows.

Changed it back to an array size of 100 and to reject several rows in the first 100 and it still only captured the last record in the array as the rejected row.

Changed it so that every row was rejected during the run and it still only captured 2 down the reject link - the last one from each array.

You can see this in the job log. In the above case, it logged 100 'ORA-00001 Unique Constraint' and 'The value of the row is' pairs but only one 'DBMS.CODE = ORA-00001' error per hundred - at the end of each hundred.

The only time everything works and matches - job log, rejected records and monitor counts - is if the array size is at 1.

Is there anyone out there with 7.5 that would be willing to repeat the same tests and see if it is still a problem? I'm seeing this behaviour with 7.0.1...

srinagesh · Post by **srinagesh** » Mon Aug 01, 2005 11:01 am

Hi !

Did anyone try to put Array size other than 1 in 7.5 ?
Any good news ? Can we still handle rejects propely with the setting other than 1 ?

mdan · Post by **mdan** » Fri Sep 16, 2005 2:41 pm

Hi,
we have the same problem.

Many times the rejected row is also in the target database, so you cannot trust rejected at all. It seems that in the rejected file is always the last row from the array

Also, REJECTED and REJECTEDCODE are 0 even for incorrect rows ...
DS 7.5.1.1 on AIX, Oracle OCI 9. I'll try it on Windows ...
On the other side, Array Size 1 has a penalty of 20 - 30% in performance.

Probably the solution is to extract information (at least about the violated key or constraint) from DS log => not very useful for large tables (100 columns) - line in log being truncated

chulett wrote:I built some specific test cases and they are showing a problem with the Array Size set to anything other than 1.

Sent 200 rows to an Oracle target and used an array size of 100. Made sure two of the rows (13,69) in the first 100 would be bad and cause an ORA-01400 'cannot insert null' failure. It was interesting to note that the job's log always showed the correct row failures, however both the reject capturing and job monitor stats were wrong. Didn't seem to matter if the reject row was constrained logically or via the checkbox, it always captured row 100 as the rejected row.

Change the array size to 50 and (surprise) it would capture 50 and 100 as the rejected rows.

Changed it back to an array size of 100 and to reject several rows in the first 100 and it still only captured the last record in the array as the rejected row.

Changed it so that every row was rejected during the run and it still only captured 2 down the reject link - the last one from each array.

You can see this in the job log. In the above case, it logged 100 'ORA-00001 Unique Constraint' and 'The value of the row is' pairs but only one 'DBMS.CODE = ORA-00001' error per hundred - at the end of each hundred.

The only time everything works and matches - job log, rejected records and monitor counts - is if the array size is at 1.

Is there anyone out there with 7.5 that would be willing to repeat the same tests and see if it is still a problem? I'm seeing this behaviour with 7.0.1...

mdan · Post by **mdan** » Fri Sep 16, 2005 3:18 pm

tested on nt: no matter which platform the problem is the same - wrong rejected rows.

chulett · Post by **chulett** » Fri Sep 16, 2005 3:27 pm

Nature of the beast, I'm afraid.