All Forums Database
Jeevanpalla 5 posts Joined 01/08
11 Jan 2008
LT,WT,ET,UV tables

In our Application work ( I am new to Teradata), we are loading our mainframe files data into a staging area first using MLOAD and then from there we are loading into our final tables using a BTEQ. those are all monthly updations. Next time before laoding into staging tables they are dropping LT, WT, ET and UV and deleting the previous month data in staging table and then loading the fresh data.My doubt is what are these LT,WT,ET,UV tables and what are the uses of these.

Someshnr 53 posts Joined 06/07
11 Jan 2008

No offense, in one hand you are saying you are new to Teradata but your signature says you are Teradata Certified Designer. By the way, there is no certification called Teradata Certified Designer .. and if you meant Teradata Certified Design Architect then any one would be surprised that you donot know what is UV,ET & WT in Mload.You may please refer to this link to download Mload manual :http://www.info.teradata.com/DataWarehouse/eTeradata-BrowseBy-Results.cfm?pl=&PID=&title=%25&release=&kword=CMLOD&sbrn=7&nm=MultiLoad

leo.issac 184 posts Joined 07/06
13 Jan 2008

I think you are talking about work tables (WT),Error Tables(ET),UV tables- (another error table that stores data with uniqueness violations) and Log Table(LT).Multiload uses these tables for processing the load operation into target tableA log table maintains record of all checkpoints related to the load job, it is essential/madatory to specify a log table in mload job. This table will be useful in case you have a job abort or restart due to any reason.Error tables store information related to the errors.There are two error tables used by Multiload ET nd UVMultiload operates in 5 phase initial,DML,Aquisition,Application and cleanup. after intial and DML phases,It first populates the work tables in aquisition phase.The data from source files is first populated into WT in aquisition phase and later on applied to target tables in application.You should note here that if a multiload job is successful all the above tables will be automatically deleted/dropped by multiload in the cleanup phase.a successful multiload job implies alll source rows from the file are applied successfully to target table without any problem.however, when there is an abort condition or error condition multiload would return an informative error message to user and it does not delete any of the et,uv,wt,lt. A user is also not supposed to delete these tables when there is a abort or error condition in middle of multiload job.once the error is resolved, the user should restart his aborted or paused job to run till completion.If Iam not mistaken , there have been some job aborts in your case which were not resolved, which is why you still have UV,ET,LT and WT tables left in your database.Probably you might have renamed all these tables and started your jobs afresh without resuming the previous aborted jobs.refer to the manual and take appropriate steps to resolve the issue with left over et/uv/lt/wt tables.regarding somesh's comments, its really awful to see people commenting on each other.This forum is intended for knowledge sharing irrespective of members skillsets.it is all about how we can help each other in making ourselves better in Teradata skills.Anyways,appreciate members efforts to raise their doubts.I requewst moderators to take necessary action on such personal comments.also i suggest members to provide genuine feedback rather than providing vague responses like 'refer this thread', 'i think you are right/wrong','maybe i think so','right',worng' etc., just to become a supreme being or just to show off their presence in the forums.

Someshnr 53 posts Joined 06/07
14 Jan 2008

Jeevan,My apology to you if my comment offended you anyway. My intension was to make you aware of your signature and not to underestimate your question. My second line was totally uncalled for and I regret for that.Leo,Jeevan's question was a generic question and not related to a specific problem he faced. That's why I felt that he needs to refer to the Mload manual which would give him good information on Mload ( the link I provided is not a vogue link, it is specific to MLoad manual). You may be aware that many people donot know the location of the resources(atleast at my work) and giving a pointer to the documents is not always a bad idea. Eventhough you mentioned generally, I would like to inform you that I am in this forum to learn and to help; nothing less nothing more.

Jeevanpalla 5 posts Joined 01/08
16 Jan 2008

Thanks Leo for your information, that is really helpful for me.Somesh, I have more of book knowledge and little bit of practical knowledge so when I am working I am getting these kind of doubts. anywhere important thing is at the end whether i got clarified or notso Its ok no Problem.

shubh 16 posts Joined 09/07
11 Feb 2008

Hi Leo, Thanks for all that info.I have a small doubt here.Did you mean to say that ET table has data errors and UV has UPI violations?Also, if there is any error in the data (data error or duplicate records) mload will stop as soon as it sees these error records or will complete the loading process and leave the error records in the error table?Appreciate your response.

Regards,
Shubhangi

leo.issac 184 posts Joined 07/06
18 Feb 2008

Not very sure on this...but,let me give it a shot!When u get a error ,the information is stored in the error table. I beleive mload job will be paused/aborted when there is a run time error/data error which prevents mload to go ahead.How ever,when there is a uniqueness violation , the records will be saved to UV table. In this case mload will not be paused but the process will be delayed as the Uniqueness violation records will be saved in UV table.

Jim Chapman 449 posts Joined 09/04
18 Feb 2008

MultiLoad has an ERRLIMIT option, which specifies the number of errors that will be tolerated without causing the entire load operation to fail. If I remember correctly, the default is 1,000,000.

RMM 20 posts Joined 08/06
22 Feb 2008

Jim,You are right in the context that MLOAD has an ERRLIMIT and the entire load will not get aborted, even i have tried with 600K records and the scripts did not get aborted even after all the records went into error table.But please can you advise where is the threshold limit of 1,000,000 defined as i cannot find the one in teradata manuals. All,We can define the threshold of ET and UV table by using the variables SYSETCNT and SYSUVCNT in the MLOAD error handling steps.Correct me if i am wrong at any point.Rishikesh.

dnoeth 4628 posts Joined 11/04
22 Feb 2008

> the scripts did not get aborted even after all the records went into error table.MLoad will stop after ERRLIMIT errors in the acquisition phase, but not in application phase, because some data has already been modified and MLOad has to finish anyway. The 100000 error limit is for FastLoad, the default in MLoad is unlimited.> We can define the threshold of ET and UV table by using the variables> SYSETCNT and SYSUVCNT in the MLOAD error handling steps.No. It's ERRLIMIT within BEGIN MLOAD for the ET table, there's no limit for the UV table.SYSETCNT and SYSUVCNT can't be set, you can only read it after the END LOAD.Dieter

Dieter

sginnani 3 posts Joined 05/09
11 May 2009

Hi, Can anyone give more insights on how we can stop the mload to run if records are inserted into the ET and/or the UV tables? I need to do this urgently and have very short deadline to achive this. Any early help is highly appreciated. Regards, Shweta

wicik 35 posts Joined 06/12
14 Jun 2012

Hi there...

Now I'm totally new... in teradata. Like second week of my life with it.

Anyway I have question :)

One of many but like first one on this forum.

 

I've been searchin some informations about processes, procedures wchich works on table. I have heard about LT and other from Multiload (google) but actually I dont know how to use them.

 

Sow.. 2 questions :)

1. How/where can I find LogTable of table I am look for?

2. It there any other way to see what processes/jobs operate/use on table I'm interested in?

 

Thanks for any kind of help.

PS: Sorry for my terrible English. I hope, that I will fix it on time ;P

wicik 35 posts Joined 06/12
14 Jun 2012

Adnotation bout 1:

Does all log tables named the same? Like LT_tablename or logtable_tablename? As far as i know, not :P

So where can I find them if i dont know precised name?

 

Adnotation about 2:

I'm searchin something that helps me find DB processes, system jobs which works on tables. Similar thing like log table but more like "which process/job worked on it like a weak ago"  or right now :P

 

Any knowlage might be handy :)

I try to read a lot so, I hope that someday I will be able to help others.

 

Greets.

dnoeth 4628 posts Joined 11/04
15 Jun 2012

For MultiLoad there's no default name for a logtable, it's set within the script.
It's commonly called LT_target_table similar to the defaults for work and error tables (WT/ET/UV_target_table), but those tables can be named explicitly, too.

If there's a naming convention on your site (and everyone is using it) you might have luck.

WT/ET/UV tables can be found using:
SELECT * FROM dbc.TablesV
WHERE RequestText LIKE 'MLOAD%'

For LTs it more complex, but a similar search for a specific request text should work (at least until the LT-DDL changes in a future release), e.g.:
WHERE RequestText LIKE '%LogType%MLoadImpSeq%MLoadSrcSeq%CkptInterval%MLoadCkpt%RunTime%'

Regarding historical loads you need access to the QueryLog, hopefully it's enabled for those loads:
SELECT * FROM dbc.QryLogV WHERE StatementGroup = 'Other MLoad'

Details about the number of rows processed might be found in QryLogSteps.

An easier way to get that information is maintaining a log table with INSERTs using the built-in SYS...CNT variables in a .RUN file after the END MLOAD.
Or check your ETL tool which might mantain similar info.

Dieter
 

Dieter

TDALEXIS 1 post Joined 11/12
29 Nov 2012

Hi,
I would like to know the Error limit for Multi Load. When I submit a job in Mainframe, MLOAD job abend if any error occurs (Data type constraints or unique violation) while loading into a table? 
In that case, what is the errorlimit for ET?
Thanks,
Alex

21 Sep 2013

Hi,
 
Could anyone please give full mload,tpump,fastload,tpts  processing in very consise manner.
 
Thanks,
Abhijeet

M.Saeed Khurram 544 posts Joined 09/12
21 Sep 2013

Hi Abhijeet,
Can you please elaborate what do you mean by full processing? Do you want an explainiation about the processing? OR some sample scripts of these utilities?
 

Khurram

25 Sep 2013

Thanks for ur response.
I just want to know how they are processing data i.e. how they are loading data.
 
Thanks,
Abhijeet

M.Saeed Khurram 544 posts Joined 09/12
25 Sep 2013

You can read the basics about the TPT from the following, And the remaining three (MLOAD, FASTLOAD, TPUMP) processing protocols are covered in TPT as well.
http://teradataworld.wordpress.com/teradata-parallel-transporter-tpt/
 

Khurram

You must sign in to leave a comment.