Job Hanging while updating sql server table

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
DSRajesh
Premium Member
Premium Member
Posts: 297
Joined: Mon Feb 05, 2007 10:37 pm

Job Hanging while updating sql server table

Post by DSRajesh »

Hi All,

I am trying to select and update the same table in the job and while running the job it is keep on running.

I am trying to update 3 different tables with 3 different odbc connector stages .

job design is like this:

odbc -->Transformer-->odbc

in the first odbc using select statement and in the second odbc using update statement on same table.

I ran with out any locks till last week..while i run today i am able to see the same in development.

Looking forward for any suggestions on this.
RD
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Is this a parallel job? I only asked because you posted in the Server forum.
-craig

"You can never have too many knives" -- Logan Nine Fingers
DSRajesh
Premium Member
Premium Member
Posts: 297
Joined: Mon Feb 05, 2007 10:37 pm

Post by DSRajesh »

sorry,this is the parallel job
RD
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

And so off we go!

Can you specify what kind of locks you are seeing? Posting the actual error messages should help, I would think.
-craig

"You can never have too many knives" -- Logan Nine Fingers
DSRajesh
Premium Member
Premium Member
Posts: 297
Joined: Mon Feb 05, 2007 10:37 pm

Post by DSRajesh »

there is no specific error message but job hangs and running contuously.

do we need to change any setting in odbc connector stage.

currently we have record count as 2000,array size as 2000.isolation level as read commited at source stage.

Looking farward for the suggestions on this
RD
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Well... classic first question would be, if you run this on a single node does the problem go away? Secondly, have you had your DBA monitor the session when it is hung?
-craig

"You can never have too many knives" -- Logan Nine Fingers
asorrell
Posts: 1707
Joined: Fri Apr 04, 2003 2:00 pm
Location: Colleyville, Texas

Post by asorrell »

What kind of database are you accessing? Can the DBA monitor for usage on the file? Typically it isn't the job that is hanging it up, it is someone else accessing the table that is preventing the job from continuing,
Andy Sorrell
Certified DataStage Consultant
IBM Analytics Champion 2009 - 2020
ArndW
Participant
Posts: 16318
Joined: Tue Nov 16, 2004 9:08 am
Location: Germany
Contact:

Post by ArndW »

The subject line indicates SQL Server. With sufficient access rights, you can see the individual threads and locks from the SQL Server Management Studio program.
DSRajesh
Premium Member
Premium Member
Posts: 297
Joined: Mon Feb 05, 2007 10:37 pm

Post by DSRajesh »

yes,There are locks on one of the table.To avoid the locks is there any configuration changes in odbc connector need to change to avoid these locks.
RD
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Where are the locks coming from? If it is from the job itself, have you tried running on a single node as previously noted? That would eliminate the target as the source of the locks but would still lock if they are being generated on the source side.
-craig

"You can never have too many knives" -- Logan Nine Fingers
asorrell
Posts: 1707
Joined: Fri Apr 04, 2003 2:00 pm
Location: Colleyville, Texas

Post by asorrell »

However, assuming the job itself is not the problem and the record lock is from something external, setting Isolation Level to "Read Uncommitted" should work.

If the entire table is locked, then you are probably out of luck, since Read Uncommitted only allows you to "get around" record level locks. Note that this will allow you to read "dirty" data, so use with caution.
Andy Sorrell
Certified DataStage Consultant
IBM Analytics Champion 2009 - 2020
Post Reply