Joba are keep on running without loading single record into

chulett · Post by **chulett** » Sun Sep 06, 2009 6:58 am

How many nodes are you running the job on? Do you have the same issue if you run on a single node?

Shashwat · Post by **Shashwat** » Mon Sep 07, 2009 12:43 am

chulett wrote:How many nodes are you running the job on? Do you have the same issue if you run on a single node? ...

Jobs is running on two nodes

keshav0307 · Post by **keshav0307** » Mon Sep 07, 2009 2:53 am

What is commit size?
Do hash partition on the unique key column.

chulett · Post by **chulett** » Mon Sep 07, 2009 8:05 am

Please answer both questions.

rajeshgundala · Post by **rajeshgundala** » Wed Sep 09, 2009 4:56 am

Could you check whether there are any indexes other than normal/unique index is present in that table into which you are loading. If there are bitmap index it would cause performance degrade.

We faced similar kind of isue and droping the bitmapindex and then running the jobs helped us.

Regards

om.ranjan · Post by **om.ranjan** » Wed Sep 09, 2009 11:14 pm

keshav0307 wrote:What is commit size?
Do hash partition on the unique key column.

I have introduced a environment variable $APT_ORAUPSERT_COMMIT_ROW_INTERVAL and set commit interval value 500, with this value initially job worked fine, but
When I increase number of input records (up to 10000) again it getting into infinite conditions.

When I tried with commit interval value with 50 it working fine when I tried with 2 millions of records it getting into infinite conditions.

Now my concern is if I will specify commit interval as 50 or less then I/O operations will increase drastically, in turn it may slow down complete process.

I didn't specify any partition on unique key, I left with DataStage default.

I cheaked with DBA, they confirmed that database side there is no errors.

Please recommend.

Thanks,
Ranjan

ArndW · Post by **ArndW** » Thu Sep 10, 2009 12:52 am

While the DBA might be correct in stating that there are no errors in the database, he or she should be checking the locks, particularly deadlocks.

mekrreddy · Post by **mekrreddy** » Thu Sep 10, 2009 8:01 pm

I am also facing the same issue, but our DBA said there is a deadlock when trying to upsert. we are working on this.. no success yet..

keshav0307 · Post by **keshav0307** » Thu Sep 10, 2009 8:06 pm

Do you have the same issue if you run on a single node?

for upsert you must have unique index on the table, define that unique column(s) as key in the table metadata, and hash partition on that key

om.ranjan · Post by **om.ranjan** » Fri Sep 11, 2009 2:36 pm

keshav0307 wrote:Do you have the same issue if you run on a single node?

for upsert you must have unique index on the table, define that unique column(s) as key in the table metadata, and hash partition on that key

I'm already specified unique index column as a key in DataStage; when I tried with Hash partition, it doesn't work.

Please suggest...

Thanks
Ranjan

om.ranjan · Post by **om.ranjan** » Thu Oct 01, 2009 11:10 am

om.ranjan wrote:
keshav0307 wrote:Do you have the same issue if you run on a single node?

for upsert you must have unique index on the table, define that unique column(s) as key in the table metadata, and hash partition on that key
I'm already specified unique index column as a key in DataStage; when I tried with Hash partition, it doesn't work.

Please suggest...

Thanks
Ranjan

......................................................................................................

when I changed execution mode from PARALLEL to SEQUENTIAL in Target ORACLE ENTERPRISE STAGE (Stage->Advanced->Execution Mode->Sequential) It's working fine...

Thanks to all
Ranjan