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.
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
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 table
A 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 UV
Multiload 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.
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.
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.
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 not
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?
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.
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.
We can define the threshold of ET and UV table by using the variables SYSETCNT and SYSUVCNT in the MLOAD error handling steps.