Working with Utilities in TeraData

 

UTILITIES

A utility is nothing but an application (or) a script (or) collection of statements As a block, it is a tool, which LOADS And UNLOADS The Data There many utilities available our of them the very important are:

BTEQ- Load/Unload

FAST LOAD-LOAD

MULTI LOAD-LOAD

TPUMP-LOAD

TPT[Tera data-parallel Transporter]- Load/Unload

TPT API- Load/unload

OLE LOAD- LOAD/UNLOAD

Etc.  

Utility Modes

Every Utility RUNS in 2ways

• Interactive mode
• Batch mode

a) Interactive mode

Here one by one statement executed after opening the utility window  

b) Batch mode

Collection of statements as a script executed here

live open an editor like a note pad, word pad, etc. and writes the collection of statement

We can save this script any extension[Tat.ddt, out, etc]But tera data Recommending the below extension to differentiate one script from another script  

Utility Execution

There are 3 ways:

1. Open the utility and run one by one statement
2. Go to the corresponding utility and execute the utility script Run command Run File= <File name>;
3. This is the standard way

IN WINDOWS

BTEQ -  Start   Run   cmdàBTEQ<[Script path]>[Log file path]

FLOAD -   Start   Run   cmd FLOAD LOAD<[Script path]>[Log file path]

TPUMP -   Start   Run  cmd TPUMP <[Script path]>[Log file path]

FEXP -   Start   Run   cmd  FEXP <[Script path]>[Log file path]

Mload - Start   Run   cmd   Mload <[Script path]>[Log file path]  

IN UNIX

BTEQ -    $Sh> BTEQ<[ Script path]/TEE< LOGFILE PATH >(OR)

BTEQ -    $Sh> BTEQ<[ Script path >[LOG FILE PATH]

FLOAD -    Sh> FASTLOAD<[ Script path]/TEE< LOGFILE PATH >(OR)

FLOAD -    $Sh> FASTLOAD <[ Script path >[LOG FILE PATH]

MLOAD -    $Sh> Mload <[ Script path]/TEE< LOGFILE PATH >(OR)

MLOAD -    $Sh> Mload <[ Script path >[LOG FILE PATH]

TPUMP -   $Sh> TPUMP <[ Script path]/TEE< LOGFILE PATH >(OR)

TPUMP -    $Sh> TPUMP <[ Script path >[LOG FILE PATH]

FEXP -    $Sh> FEXP <[ Script path]/TEE< LOGFILE PATH >(OR)

FEXP -    $Sh> FEXP <[ Script path >[LOG FILE PATH]  

Note:

TEE Display the LOG Information on the console  as well as Re- Directs it to the specified file  

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

Uses of Log file

• Ensuring Script success or fail. 

GO TO LOGFILE LOG File And Read the Last Statement which contains the Return code, According to the return code we have to take appropriate action  

Return codes and symbols

0   Job complete normally

2   User alert to Log on to the Tera data DBS

4   Working

8   User error

12   Fatal error[Connection Related Issues possible here]

16  If no MSG destination is the available or typical error  

Note:

 Each Return code belongs to a set of error codes

  In Unix to know the script ran success or not without [opening log ile]  

Open log file

• If[$?]=0 means script success
• If[$?]=other than o means script fail  

Utility commands and keywords

• Generally, utility commands storks with’.’[DOT]
• Generally SQL Statements End with;
• comment notation/*  */BTET Mode
• ANSI Mode

Some Important commands and key works

• .ERRORCODE
• .ACTIVITY COUNT
• .ERROR LEVEL
• .REMARK
• .REPEAT

  All utilities

• .ACCEPT
• .SET
• .DISPLA
• .ROUTE MESSAGES
• .IMPORT
• .EXIT
• .QUIT
• .OS CLS
• .SHOW CONTROLS
• .QUIET ON/OFF
• .LOG ON
• .LOG OFF

  ADDITIONAL COMMANDS WITHOUT’’[DOT]

• TENACITY
• SLEEP
• CHECKPOINT
• SESSION
• PACK
• RECORD
• USING
• DEFINE
• LAYOUT
• ERRLIMIT
• RECORD
• .ERRQOUL
• DECLARE
• OS<Command NAME>-- PARTICULAR OS COMMAND

  Note:

  Refer to the material for more syntax, the example of this command in the tera data new and advanced material page

Teradata Interview Questions