DRS rollback not occuring with IPC stage

news78 · Post by **news78** » Mon Feb 12, 2007 10:30 am

My job looks similar to as follows:

	               
                             [IPC] -> [DRS]
                                 |
			         |
[SequentialFile] --> [Transformer]  
			         |
			         |
		               [IPC] -> [DRS]

Note in the job, the ipc links are coming out of the transformer, for some reason I am not able to show that here.

Now, the requirement is if any error occurs while loading records, there should be a rollback of all records inserted until then. The Tx Size has been set to 0 in both DRS stage. In one scenario, am getting 10 records, the 10th is an record which is has error in it. But I see that 9 records are loaded in the DB even though the job aborts at the 10th record.

I tried the same without the IPC stages and the rollback occurs as expected. Seems with the IPC, the rollback is not occuring. Any advice?

DSguru2B · Post by **DSguru2B** » Mon Feb 12, 2007 11:07 am

Get rid of the IPC stage.

news78 · Post by **news78** » Mon Feb 12, 2007 12:19 pm

DSguru2B wrote:Get rid of the IPC stage.

Is there any other option? I need the IPC stage as I can see faster loads with IPC in place.

narasimha · Post by **narasimha** » Mon Feb 12, 2007 12:23 pm

What is your target database?

news78 · Post by **news78** » Mon Feb 12, 2007 12:38 pm

narasimha wrote:What is your target database?

Oracle

I_Server_Whale · Post by **I_Server_Whale** » Mon Feb 12, 2007 12:43 pm

news78 wrote:
narasimha wrote:What is your target database?
Oracle

Have you tried the native stage for Oracle? The ORAOCI plugin stage?

narasimha · Post by **narasimha** » Mon Feb 12, 2007 12:55 pm

I_Server_Whale wrote:Have you tried the native stage for Oracle? The ORAOCI plugin stage?

That would be faster, if bulk loading is an option is is even quicker!

chulett · Post by **chulett** » Mon Feb 12, 2007 12:58 pm

Actually, the DRS stage set to 'Oracle' does use the native OCI interface under the covers from what I recall.

I_Server_Whale · Post by **I_Server_Whale** » Mon Feb 12, 2007 1:08 pm

chulett wrote:Actually, the DRS stage set to 'Oracle' does use the native OCI interface under the covers from what I recall.

Totally agree

. But still, doesn't the DRS stage have a similar overhead like the ODBC stage as compared to the native OCI interface? I was promoting the OCI stage as opposed to DRS in order to avoid the overhead costs.

DSguru2B · Post by **DSguru2B** » Mon Feb 12, 2007 1:15 pm

Get rid of the IPC stage. Go to the job properties , performance tab, uncheck 'project defaults', check Enable row buffer and choose Inter process. Run you job. See if you still have the rollback problem.

narasimha · Post by **narasimha** » Mon Feb 12, 2007 1:16 pm

Not sure if there is a difference in using DRS with Oracle specified as the "database type" and actually using the Oracle OCI stage?
Of course using DRS Stage with ODBC as the "database type" would be less performant.

DSguru2B · Post by **DSguru2B** » Mon Feb 12, 2007 1:25 pm

You can also get faster loads by increasing the array size.

chulett · Post by **chulett** » Mon Feb 12, 2007 1:29 pm

I_Server_Whale wrote:
chulett wrote:Actually, the DRS stage set to 'Oracle' does use the native OCI interface under the covers from what I recall.
Totally agree . But still, doesn't the DRS stage have a similar overhead like the ODBC stage as compared to the native OCI interface? I was promoting the OCI stage as opposed to DRS in order to avoid the overhead costs.

I don't believe so, but then I've never put any time or effort into investigating the DRS stage. I just slavishly stick with the OCI ones.

I_Server_Whale · Post by **I_Server_Whale** » Mon Feb 12, 2007 1:29 pm

narasimha wrote:Not sure if there is a difference in using DRS with Oracle specified as the "database type" and actually using the Oracle OCI stage?
Of course using DRS Stage with ODBC as the "database type" would be less performant.

I still think DRS = ODBC stage, except that the database type can be dynamic (assigned through a job parameter) in the DRS stage?

narasimha · Post by **narasimha** » Mon Feb 12, 2007 1:34 pm

I_Server_Whale wrote:I still think DRS = ODBC stage, except that the database type can be dynamic (assigned through a job parameter) in the DRS stage?

If you specify the "Database type" as Oracle it uses the Native Interface and does not use the ODBC.
There is no involvement of ODBC in this case. IMHO