datastage load data into oracle rac ,and hang the session

richardzhi · Post by **richardzhi** » Tue Oct 13, 2009 10:55 pm

We have two node's oracle rac, ordinary we run etl job to load data into temp table smoothly. But occassionly, the job will be hung and no error returned. if we check the oracle session wait events, it 's row cache lock . But I think no other app concurrently access this temp table . My oracle rac db is 10.2.0.4 and running on HPUX 11.31, and etl datastage is 7.5 and running on one aix box. Becaus it happened by randomcity , how I can avoid this problem ?

thanks advance

ray.wurlod · Post by **ray.wurlod** » Tue Oct 13, 2009 11:07 pm

Welcome aboard. What errors are logged in DataStage when this problem occurs? Have you had your DBA investigate which sessions own the locks?

richardzhi · Post by **richardzhi** » Wed Oct 14, 2009 12:08 am

thanks, ray
The datastage don't return any error and just hung, if I terminate the job, i can see that a session is holding the lock on the temp table. Since it happend in occassionly and the locked table is also randomly, I want to know how to avoid the problem occur, and what mechanism when datastage use oracle enterprise to load file data to temp table in oracle rac. If i have two node, does datastage lock the table two times?

thanks your replay and wish get more instruction.

chulett · Post by **chulett** » Wed Oct 14, 2009 6:01 am

Has nothing to do with RAC, so put that out of your mind. Yes, multiple nodes create multiple connections and locks so you need to be careful how your data is partitioned to avoid situations like that. Does the issue not occur if you run the jobs on 1 node only?

richardzhi · Post by **richardzhi** » Wed Oct 28, 2009 9:17 am

I am sure that it's not about table partition.if i run job in one node, I don't meet that problem. I just load a file into one table,this is a small table, not used by anyone rahter by datastage job. I just assume that datastage create 2 connections to two node in the rac,one lock table and another also try to lock this tablemso it's blocked. So if I just use one connect to one node, maybe I wouldn't meet this problem, Any one can supply the advice?

thanks

chulett · Post by **chulett** » Wed Oct 28, 2009 9:22 am

Didn't say anything about your table partioning, rather specifically how your data is partition over multiple processing nodes. And the fact that it works on 1 node is a big clue. Have you tried Hash (rather than Auto) partitioning?

richardzhi · Post by **richardzhi** » Thu Oct 29, 2009 1:56 am

my question is :
1。 my datastage runs in aix server alone, and my db run two hp server. Should I have to setup environment variable ,such as put the line : APT_ORACLE_NO_OPS=1 in profile? if not, is datastage is aware automatically?
2. if I use odbc entry as following paragraph:

WORKDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = a01-vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = a02p-vip)(PORT = 1521))
(LOAD_BALANCE = yes)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = preqdb)
)
)

datastage use oracle enterprise load file data to table, do datastage try to lock target table concurrently in two nodes?