Table lock

reddy · Post by **reddy** » Wed Jul 27, 2005 2:32 pm

Hello Guys,

I created single Transform with 2 links one for update and another for Insert .like this
TX------>UPDATE
|
|
INSERT
I am updating and inserting SQL SERVER DATABASE.

while running the job it's locking the table.

I am getting following message:

LoadCustomerMasterPDROETest..Transformer_39.DSLink41: DSD.BCIClose call to SQLSetConnectOption(SQL.PRIVATE.TX) failed.
SQLSTATE=08S01, DBMS.CODE=0
[DataStage][SQL Client][ODBC][Microsoft][ODBC SQL Server Driver]Communication link failure

Thanks for help
Narasa

bryan · Post by **bryan** » Wed Jul 27, 2005 3:54 pm

hi reddy

Try changing the commit interval

I remember I set the update to 1 , by defualt its 0.

It will help people if you tell them whats your database and your commit intervals.

Let us know what were your commit intervals incase if this helps.

chucksmith · Post by **chucksmith** » Wed Jul 27, 2005 7:03 pm

But, a faster solution may be to stage one or both output links to sequential files, then you can apply your inserts followed by your updates with reasonable transaction sizes.