Working with ERROR HANDLING in TeraData

 

ERROR HANDLING

To prevent from known and unknown error at rentine and doing our user-defined action is called error handling

we need to set an error level for handling the error  

Syntax

.Set error level<error code>Severity <Integer>  

Ex:

.Set error level 6784 severity 4

.Set error level 807severity 8

.Set error level 2121 severity 12

.Set error level 5678 (or)unknown severity 16  

 

Inclined to build a profession as Teradata Developer? Then here is the blog post on, explore Teradata Training

Note:

HIGHEST Return code=0 means script success

/*Rum File c:BTEQconn. TxT;*/

.Logon 127.0.0.1/DBC,DBC;

.Set session 4;

Database Vinayaka;

.Set error level 3807 Severity 4

.Set error level 6066 Severity 8

Select*From party22;

Insert into party(1,’vinay’,34567865);

.IF error level =4 then insert into Translog1(‘vubat1’’ Table is ’not created”);

Insert into party 22(12345678912345678,’Vinay’);

.If error level -8 then. Remark ’Range invalid,’ don’t load the table;

.Logoff     

FAQs.

1. What is the main usage of BTEQ?

2. There is a file with 100 Records I want to load 60 Records without loading the 1st 20 Records

 

Skip =20 , Repeat =60

By using skip=20 and repeating =60 there is a table with 10000 Records, but I Require Top 10 income person Details in the file

1) Select

Order by PI Desc:

Limit=10  

 

3) How do you import multiple files And multiple tables

Ans: .LOGON By writing multiple import statement we perform the operation    

4) How do you import multiple files and multiple tables

Ans: 

.logon

Import infile file1

Using<clause>

Insert/update <statement1>

Import infile file2

using<clause>

insont /undate<statey>  

5) General error in the Lab?

1.The file specified does not exist

Ans:- If the input file path/script path  wrong we get these types of error  

6) Record length not matched with structure (date parcel does not match)

Ans:-

We get this at the time of  in the below scenario

a)If multiple ends of the file marks are those

b)If the user class structures no. of column and data structure.

c)Not matching input record effect  

7. the values specified    

FAST LOAD

• It is a bulk load facility which loads a large volume of data(High value of data)
• Compare to all utilities it Runs FASTER
• It runs in 2 phases
• It captures the errors records into an error table
• It is fully automatic Restartable and checkpoint configurable
• It loads only one table at a time and in the table should be empty
• It does not support duplicate records[NUPT OR multiset duplicate]
• It processes the data block by block
• It performs insert operation between begin loading and end loading[Before begin loading we can write DROP, Delete, create, etc commands]
• It supports input module programming[INMOD]
• Maximum 15 FEXP Script we can execute at a time

NAVIGATION

Start  Run   FAST LOAD

Start    programs    Tera data Client   Tera data fast load

  FAST LOAD Table

It uses an error table and one log table  

a)ERROR Table one

Generally, the below error move into the error Table one

• Data conversion error
• Constrains violation
• Unavailable Amp error

Structure: Party

   

 

b)Error table Two

If the table is having UPI And we try to store duplicate Records and those Records move into error table2

Structure

Similar to the Target table  

Teradata DBA Interview Questions

c) FAST LOG Table

FAST Load Internal take log table for implementing Restart ability

Whenever Script fails the system stores the failed point in the fast log table

After rectifying the error, if we restart the FAST Load script it starts from the last point in the fast log table

Once script executed succeed to content in the fast log table related to target table deleted

This table is available sysadmin Select* From SYSADMIN.FASTLOG;  

FAST LOAD RECORDS FORMULA:

Number of records in Source file= Number of Records Loaded to target(2)

+Number of Records loaded error  Table(1

+Number of Records Loaded error table(1)

+Number of duplicate records log file(1)

 

     

FAST LOAD STRUCTURE:

.Set Record<Records Format>

.LOGON<>

<Create/DROP/Delete Commands>

Begin loading<Data base name. table name>  phase1 starts

Error files <Error table1>,< Error table2>

Check point<Integer>

Define<Structure>

File=<Filename>

Insert<STMT>

End Loading  phases starts.

LOGOFF    

Real-time usages of FASTLOAD

To load data into an empty table at the time of initial load

To load data into stage table[Truncate and load tables]

Checkpoint

Checkpoint is either Records based or Time based

We need to take a checkpoint was, it is happening for every 10 to15 minutes

It provides better Restorability

No checkpoint

{-1-1core}  reads 30 minutes

{1- core}  write 25 minutes

After Rectifying the error if you start, it starts from 1^st Record ”Reading”

While writing failed at 99,99,998^th  Record            

 

Check points – Available

10 Lacks       check point

{1- 10L(Read)}   {1- 10L(write)}  1 checkpoint

{10L +1-20L(Read)}{101 - 202}  2 check point

…….

…….

{90 L+1- 1 Crore (Read)} {90 C+ 1- 1 Crore(Fail)} 9 check point

  After Rectifying the error if you start, it start from last Record 90L+ Reading[Because of checkpoint in Log table]  

Recommending

If records<41c then check point 100000

If records>41c then checkpoint 50000