Multiple OCI Stage Problem?

endyl13 · Post by **endyl13** » Tue Jan 28, 2003 6:28 am

Dear All,

We have a job which maps to two targets. But because of after inserting each targets we have to insert and update a log table and a control table, we made each target with the log and the control table into one Container. So Cont1 for Target1 and Log table and Control Table, Cont2 for Target2 and Log Table and Control Table.

The thing is, if I run the job and map only to Cont1 or Cont2, it will run successfully. But If we map to both COnt1 and Cont2, an 'Abnormal Termination' error will occur.

Anyone can help with this?

We are using DataStage 5.2.1 and Oracle 9.1 on Sun. The Targets are using OCI stage. We insert to Target1,Target2, Log table, and update to Control table.

Thanks in advance...

Endy

Regards

ND

ariear · Post by **ariear** » Tue Jan 28, 2003 12:45 pm

Hello,

Check to see if the total array size of both OCI stages are above 100 - sometimes it can cause abnormal termination

ray.wurlod · Post by **ray.wurlod** » Tue Jan 28, 2003 4:14 pm

What version of the OCI stage are you using?

endyl13 · Post by **endyl13** » Tue Jan 28, 2003 9:05 pm

Hi Ariear and Ray,

On which stages do I have to make the array size to more than 100?
I am using OCI 8 (I think), because we only used the one that come with the installation cd.

Just to explain aout my job design,
Table A is mapped to Table B and Table C.
But everytime we finish inserting any value to table B or C, we have to update the last surrogate key inserted to B or C to a Control Table with each tablename as the Key, and we also need to insert a log record to a Log Table with number of rows inserted to each table.

To simplify the design, we created two containers for each table, ContB and ContC, in both containers we also put in the same two common tables, Control Table and Log Table. And We use Aggreagator Stage to update Control Table and insert Log Table so that only one row will be effected on each update or insertion.
In the transform stage, I make the execution order to ContB first and then ContC.
The problem seems to occur everytime the second Container want to update the Control Table. It will cause Abnormal Termination.
When I checked the value in the Control Table and Log Table, the Value for Table B from ContB had been updated, but not the value of Table C.

I've tried to change the Array Size to 150 for all OCI stages, and it's getting worse. No value updated in the Control Table, but value for TableB was inserted into the Log Table.

Can you explain and help me on this?

Regards...
Endy

Regards

ND

endyl13 · Post by **endyl13** » Tue Jan 28, 2003 9:32 pm

Hi,

Just to revise my previous reply, We're using OCI8i version 4.0

And I just realised, the job can update the Control Table for both ContB and COntC, but the cannot insert a value to the Log Table for ContC. But the Value for TableB on ContB was inserted to the Log.

Kindly help

Regards

ND

ray.wurlod · Post by **ray.wurlod** » Wed Jan 29, 2003 5:19 am

Are any messages logged either by DataStage or by Oracle?
I suspect that it's something to do with control of transactions in Oracle. Is your "rows per transaction" set to 1 in both OCI stages? What happens if your array size is also 1?

Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518

endyl13 · Post by **endyl13** » Wed Jan 29, 2003 5:44 am

Ray,

I've set the 'Rows per transaction' to 1 in both stages.
The same thing happen. ContB can update Control Table and Insert to Log Table, but ContC can only update Control Table but cannot Insert into Log Table.

The same thing still happen when I change the Array Size to 1 or other numbers.

The most frustrating thing is that logically there's nothing wrong with the design, and during the run there's also no message from Oracle (ORAxxxx msg), and DataStage just give 'Abnormal Termination Detected' message.

Is there any other folder or file or option should I check?

Kindly advise...

Regards

ND

endyl13 · Post by **endyl13** » Thu Jan 30, 2003 7:29 pm

Hi all,

I've manage to solve the problem. I was using too many OCI stages, and the same database & table was reused in different Shared Containers.
I changed the design of my job and container so all of them will only write to one OCI stage and read from one OCI stage, and it works fine and faster.
So, if you have the same problem, try minimize the number of OCI stage that you're using. The design won't be nice though

Regards

ND

ray.wurlod · Post by **ray.wurlod** » Thu Jan 30, 2003 10:33 pm

In the Designer you can stretch the size of the stage icons, so that the links are further apart and their labels thus easier to read. This has no effect on the execution of the job. Here's an attempt at an ASCII version: use a fixed width font to view.

+-----+ +-----+ +-----+
| | | | | |
| |----->| |----->| |
| | +-----+ | |
| | | |
| | +-----+ | |
| | | |----->| |
| |----->| |----->| |
| | +-----+ | |
+-----+ ^ +-----+
|
+-----+
| |
| |
+-----+

Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518

chulett · Post by **chulett** » Fri Jan 31, 2003 7:30 am

Yah, it hasn't been intuitively obvious to most of my developers, but once you do it (usually accidentally the first time [:D]) it really helps for stages with lots of links. I usually end up making it look more like a vertical bar than a square, if that makes sense.

-craig

endyl13 · Post by **endyl13** » Fri Jan 31, 2003 8:17 am

Thanks Ray & Craig...

I prefer to do it vertically, the Waterfall way [:D]

[:o)]

Regards

ND