Difference Between NORMAL Delete and M LOAD DELETE in TeraData
Difference Between NORMAL Delete and M LOAD DELETEM LOAD Delete Runs Faster than Normal Delete Recause of Relow Reason:
- No ROLL BACK so No TRANSIENT JOURNAL Over head
- Fully Automatic Restorable
- RUNS in phases
- Delete Based on UPI Not permitted
MLOAD PROCESSING And PHASES
It processes the data Block by Block
Priliminery 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
ACCEQUISTION 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”
TASK CLEAN UP PHASE[CLEARING PROCESS]
DROP ETS
UVS
WTS
LOGTABLES
RELEASES”LOCKS”
Note
Refer to the material for detail description of phases
MULTILOAD LOCKS
A)ACCESS LOCK
Applied in AQuision phase
b)Write Lock
Applied in application phase
C)AQuision Lock
Applied in the initial phase available till AQuision phase During this locks any DML operation including drip can be perform on the target table
D)APPLICATION LOCK
Applied in the initial phase available till application phase during this locks, select operation including DR can be performed.
E)EXELUSIVE LACK
This Lock are useful to release the locks one the target table by using release Mload command
CHECKPOINT
Syntax - Checkpoint time/Records
<60 time based
>60 Records based
Ex- checkpoint 30;
For every 30 minutes it takes a checkpoint check point 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 application phase continuous after that it stops the process
NONE - it continuous of execution with Down AMP If fallbock amp are available
All - it cannot continuous it execution with down amp
EX - DELETING THE data by using delete TASK