Table locks when loading through Teradata Enterprise stage

Sakthi_Sst · Post by **Sakthi_Sst** » Fri Dec 29, 2006 2:48 am

Hi All,
I have an issue with Teradata Enterprise stage:
Issue:
1.I am trying to load a table using a Teardata Enterprise stage through a DS job.
2.But when the job is aborted manually or automatically(1st time) and when i compile and try to run the job for the second time the job aborts again(2nd time) with director log saying "OPeration not allowed <table name> is being loaded" .

3.When i go to teradata to check the row count, even it gives me the same error "OPeration not allowed <table name> is being loaded" and iam unable to release the table,it is not allowing me to even drop that table//My user id has all the permissions with the table.

4.I have relaesed(cleaned up) all the ds resources which is holding the table through DS Director and through DS Adminsitrator using "ds.tools" command in the command prompt.

5.But still iam unable to run the job.

6.So as an alternative,I replaced the Teradata Enterprise stage with a Teradata Mulitload stage and when the job is aborted for the 1st time and try to run the job for the 2nd time the job aborts, it gives me an error message "multiload task is still running on this <table name>" and iam unable to release the table using the "Release Mload" command.

My question's would be :
1.Is that any of the hidden Datastage resource holding the table ? If yes how do i release that?
2.If it is teradata table lock is there a command to release the table ?3.How do i proceed this job?
4.Is ther any workaround for this?
5.Can i use two Teradata Multiload stages in a single job which populates two different tables?

Thanks & Regards,
Sakthi.

ray.wurlod · Post by **ray.wurlod** » Fri Dec 29, 2006 3:05 am

Cleanup is required at the Teradata end. Discuss with your DBA.

DSguru2B · Post by **DSguru2B** » Fri Dec 29, 2006 8:32 am

You should never kill a job thats accessing a database. Things like this start happening. Ask your dba to kill that particular thread, generated by your id.

rwierdsm · Post by **rwierdsm** » Fri Dec 29, 2006 9:16 am

Hi Sakthi,

Have a look at this link

viewtopic.php?t=96485&highlight=greg+en ... e+teradata

Rob W.

Sakthi_Sst · Post by **Sakthi_Sst** » Tue Jan 02, 2007 3:51 am

Hi All,

Dsguru,

1st time the job got aborted by itself due to some reason when i was in UAT,from that point i came to know about this issue.
2nd time i was simulating the issue to find a workaround for that.
So everybody suggest that the issue is with teradata end and not with DS end(ie.DS is not holding any of the resources of the table)

Thanks & Regards,
Sakthi.

kumar_s · Post by **kumar_s** » Tue Jan 02, 2007 4:03 am

What happend to the page alignment?
As mentioned, you can check the process that keep track on the given table from the database end. Your DBA might help you to kill those process. But you may need to check if this repeats again for other tables as well.

kumar_s · Post by **kumar_s** » Tue Jan 02, 2007 4:03 am

What happend to the page alignment?
As mentioned, you can check the process that keep track on the given table from the database end. Your DBA might help you to kill those process. But you may need to check if this repeats again for other tables as well.

Krazykoolrohit · Post by **Krazykoolrohit** » Tue Jan 02, 2007 11:02 am

delete the temporary tables manually thru GUI. This is a bug in teradata i guess, wherein the temporary tables dont get deleted automatically.

After you delete them, release the mload.

As said above, this is teradata issue. Discussing with your DBA would have helped

Sakthi_Sst · Post by **Sakthi_Sst** » Tue Jan 02, 2007 11:57 pm

Hi Krazykoolrohit,

Iam loading a single table (ie) a target table named XYZ through multi load stage.

1.Could you kindly guide me about Which temporary table do i delete before releasing the Mload ?

2.Is that Error tables and Log tables you mean by temporary tables?

3.If it is Error and Log tables i have deleted that also.

Thanks & Regards,
Sakthi.

Sakthi_Sst · Post by **Sakthi_Sst** » Thu Jan 11, 2007 11:51 pm

Hi Experts,

I have still not come out of this issue.

Let me rephrase and give you all the current scenario:

1.when i use Mload stage to load the table and when it is aborted,Iam unable to release the table immediately.
Iam able to release the table only after 20 mins.This actaully kills my production time.

2.I have checked with my Teradata end and they gave nothing to do with this from their end.

3.Similarly when i run a Mload script to load the same table and even if the script is aborted iam able to release the table in few seconds.But this doesnt happen when done using the DS JOB.

I have released all the locks and resources which is holding the table from the DS end.

Is there any solutions or workarounds for this problem?

Thanks & Regards,

DSguru2B · Post by **DSguru2B** » Fri Jan 12, 2007 8:02 am

You need to get together with your DBA and do some analysis and monitoring. Run the job, let it fail. Ask the DBA what does he see at his end. How many threads alive, how many still alive after the job aborts etc etc etc.

ray.wurlod · Post by **ray.wurlod** » Fri Jan 12, 2007 3:37 pm

Multiload does set table locks, as per documentation. You could switch (within the Multiload stage) to using TPump, which only sets record level locks.

Sakthi_Sst · Post by **Sakthi_Sst** » Sat Jan 13, 2007 12:57 am

Hi Ray,

1.I changed the Load Utility option from Mload to Tpump
But the job gets aborted giving an

" Fatal Error: Fatal: Unable to start TPump process : TPump failed: Check TPump report /etlfs10/PPAS/PPAS_LOG/PPAS_Subs_Forf_Hist.txt for details "

When i try to check the log details in the text file it gives an error
"tpump: No such file or directory"

Do i need to change anyother settings in tpump,Plz guide me on this?

Thanks & Regards,

chulett · Post by **chulett** » Sat Jan 13, 2007 8:33 am

I would assume all that means is that the tpump command isn't in your path. There must be installation / configuration instructions for that stage, have you followed all the steps there to configure it properly?

If you need to involve your Teradata folks in this as well, don't be afraid to do so.

pathmaker · Post by **pathmaker** » Sat Jan 13, 2007 8:50 am

your datastage might be installed on cluster. logon to apropreate system where /etlfs10/ is mounted use mount command to make sure you have that file system available

coming to your issue .. Teradata utilities create Log table error tables during the load process ,if you happen to fail during the process it will not clean up the log files assuming that you will be restarting from where ever you have failed. this will not happen in case of teradata ent stage.

in this case you have to drop the log table; the name of the log table that you need to delete would be what ever you have specified on the stage if you dont it will create a log table with the base table name .log or you can find that from report file looks like you have not spefcified any , you should be able find the table on the same database that you are loading to or if you are using teradata utility database it will create there

delete the log table and then drop and recreate your actual table this is the only way you can restart your job

this is teradata behaviour and has nothing to do with DataStage

coming to teradata ent stage you need to contact IBM you will get a patch for this

-pratap

DSXchange

Table locks when loading through Teradata Enterprise stage

Table locks when loading through Teradata Enterprise stage

Re: Table locks when loading through Teradata Enterprise sta

Re: Table locks when loading through Teradata Enterprise sta