Oracle Connector issuing LOCK TABLE with EXCLUSIVE MODE

[vputta]

Hi- I am using Oracle Connector stage in Datastage 8.1 job to BULK Load the data. The job is running on 4 node parallel. When I am executing the job, the Oracle Connector stage is issuing 4 LOCK TABLE statements with EXCLUSIVE MODE. Because of this the job is failing with below error-

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired.

How to change the property to issue LOCK TABLE statement with out EXCLUSIVE MODE? Is it in the Stage or somewhere in Oracle Connector Configuration? I want the locks to be Non Exclusive.

Thanks

[vputta]

Any help is highly appreciated.

[asorrell]

Posting a relevant section from the Connectivity Guide for Oracle Databases:

Chapter 3 - Oracle Connector

Controlling Bulk Record Loading
If the connector stage is configured to run in parallel on more than one processing
node, each of the processing nodes establishes a separate Oracle session and loads
data to the target table concurrently. In this scenario, if the Allow concurrent load
sessions property is set to No it prevents multiple processing nodes from
concurrently loading data to the same segment in the database. This situation
might lead to the Oracle error ORA-00054, wherein the processing nodes try to
load data to a segment while another processing node is loading data to the same
segment. To avoid this situation, the Allow concurrent load sessions property
can be set to Yes.

Sometimes, the connector stage is configured to load data from multiple processing
node to a partitioned Oracle table and the stage is configured to partition the input
data by setting the Partition type option on the Partitioning tab. In this scenario,
and the supported table partitioning types, each processing node loads data to its
assigned partition segment or a set of subpartition segments and the processing
nodes do not compete for access to the segment. In this scenario, setting Allow
concurrent load sessions property to No does not prevent the connector stage from
loading data in parallel from multiple processing nodes but prevents other
applications from concurrently loading data to the segments accessed by this
connector stage.

Personally I prefer option #2 (use correct partitioning and keep it set to "No") as a safer alternative.