Difference Between NORMAL Delete and M LOAD DELETE
M LOAD Delete Runs Faster than Normal Delete Because of Below Reason:
- No ROLLBACK so No TRANSIENT JOURNAL Overhead
- Fully Automatic Restorable
- RUNS in phases
- Delete Based on UPI Not permitted
MLOAD PROCESSING And PHASES
It processes the data Block by Block
Preliminary phase[BASK SETUP]
Target Tablesà A, B, C
3 ETS
3 UVS
3 WTS
LOG TABLE
“INITIAL LEVEL LOCKS”
(ACQUISIONLOCKS/APPLICATION LOCKS)
DML TRANSACTION PHASE[TO SENDING DML STATEMENTS TO RDBMS]
MLOAD Syntaxes
DML Syntaxes
DML Syntaxes MOVE TO WT TASK
ACQUISITION PTIASE[SENDING INPUT DATA TO AMPS]
Input dataà” WT” Access work on Target
Note: No AQuision phase for delete task “ET” Tabke
APPLICATION PHASE[SEND AMPS DATA TO TABLES]
WT 
Target tasks
“Write lock”
“UV TABLES”
Inclined to build a profession as Teradata Developer? Then here is the blog post on, explore Teradata Training
TASK CLEAN UP PHASE[CLEARING PROCESS]
DROP ETS
UVS
WTS
LOGTABLES
RELEASES” LOCKS”
Note: Refer to the material for detail description of phases
MULTI LOAD LOCKS
A)ACCESS LOCK
Applied in the acquisition phase
b)Write Lock
Applied in the application phase
C)AQuision Lock
Applied in the initial phase available till AQuision phase During this locks any DML operation including drip can be performed on the target table
D)APPLICATION LOCK
Applied in the initial phase available until the application phase during these locks, select operation including DR can be performed.
E)EXCLUSIVE LACK
This Lock is useful to release the locks on the target table by using the release Mload command
CHECKPOINT
Syntax - Checkpoint time/Records
<60 times based
>60 Records based
Ex- checkpoint 30;
For every 30 minutes it takes a checkpoint 50000;
For every 50000 Records, it takes a checkpoint
SESSIONS
Syntax: Sessions max-min Ex: Sessions 4 2
It takes max 4, min2 sessions
Errlimit
- Without percentage:
Ex - errlimit10; for 10errors script fails
- with percentage
Ex: errlimit 1000 1;
For every 1000 Records, it checks for 1% fo errors, if it crosses script fails
AMPCHECK ALL/NONE/APPLY
APPLY - till the application phase continuous after that it stops the process
NONE - it continuous of execution with Down AMP If fallback amp are available
All - it cannot continue its execution with down amp
EX - DELETING THE data by using delete TASK
For an in-depth knowledge, click on below