TABLE GETS LOCKED

nkln@you · Post by **nkln@you** » Mon Mar 28, 2005 3:44 am

I ran a batch job which had 3 update statements on a table A.
The execution of this job took more time than expected and so i i tried to stop th execution,but it did'nt stop and so i used clean up resources option.

then the status of the job was shown as compiled. but when i opened the log for that batch job, i could not see the job aborted or anything

I can only see
Batch::UpdateKey: Set NLS locale to US-ENGLISH,US-ENGLISH,US-ENGLISH,US-ENGLISH,US-ENGLISH

When i try to run those update statements manually again, i colud not see it get completed. i think the table is locked.

I have faced this type of problems many times. De to this my development time is increasing.

Pls suggest me what to do in such cases

ray.wurlod · Post by **ray.wurlod** » Mon Mar 28, 2005 4:35 am

You have not provided enough information.

What kind of table is Table A? (That is, what brand of database?) What leads you to believe that the table (which table?) is locked? Have you checked with your DBA, who can inspect locks?

What error/warning messages are being posted in the DataStage log? Do double click and get the full detail.

Did you try resetting the job? There may be more diagnostic information logged when you do this.

davidnemirovsky · Post by **davidnemirovsky** » Mon Mar 28, 2005 6:22 pm

Just a general comment regarding your post.

Sometimes during DS development incomplete jobs and processes that have incomplete or 'dodgy' code in them can lock tables and routines without unlocking them. When this happens to an internal Datastage component you can unlock it by telneting to the DS server and performing some administration with the dsadm user. If external tables are getting locked you will need to get your DBA to unlock the processes that are holding them.

If you do not realise that your tables/processes are locked this could lead to incorrect diagnosis of your errors.

Although this might not be the cleanest approach, we found (at the site I am currently on) that it was helpful to 'bounce' (reboot) the Datastage server or the Database server to make sure no locked tables or processes are causing a loss of time. We did this approx 2 or 3 times a week.

chinek · Post by **chinek** » Mon Mar 28, 2005 7:10 pm

Sometimes aborting a job does not necessarily kill off any SQL that the job has already initiated in the database so your update statements could still be executing. When you rerun the jobs then obviously those tables are locked by the earlier sql.
Best bet is to get your DBA to also check and kill of any updates sql running in your database before rerunning the jobs.

Nick