mLOAD DELETE JOB

Tools & Utilities
Enthusiast

mLOAD DELETE JOB

Hi,
As we know worktables are used to carry data which will be applied during application phase.......
But in mload delete there is no data to be acquired so no acquistion phase...
Could you please tell me what is the need of worktable in MLOAD DELETE...
8 REPLIES
Teradata Employee

Re: mLOAD DELETE JOB

To record pending NUSI deletes as the table rows are deleted.
Enthusiast

Re: mLOAD DELETE JOB

Hi Fred,
Thankx for reply...
Theren are some confusions about fastload and multiload
here is my understanding of fastload and multiload operations for a fallback protected table,NUSI(in mload)
I have written confusions after each concerned phase ---please try

Fastload
=========
Acquisition----data is send to AMP in block of 64k where they are de-blocked,hashed and send to correct AMP
Application----data is read ,de-blocked again and sorted on row has sequence....again written to disk
All fallback table data rows are applied after this so as data in primary table is available as soon as
CONFUSION
=========
Is data written to disk in first phase itself??? if is it so why don't we write a sorted data in single pass ...
I mean why would we increase our i/o by reading data block again and sorting it and writing again to disk...
Please throw some insight ..........

Multiload
=======
Acquisition-----data in AMP,de-blocked send to correct AMP ...sorted on ROW hash........

CONFUSION-------
===========
If table is fallback protected de-blocker AMP will send 2 copy of rows 1 to primary AMP and other 2 FALLBACK amp
or
initially only each row will be send twice into blocks.......???

Application
=========
Target is read once and all row hash which matches worktable are applied with appropriate DML tag associated and whole block is written back

CONFUSION
==========
1)changes to both primary and fallback rows are done in parallel???
2)If table is having NUSI.....
changes done to data block is stored in worktable in application phase and applied to NUSI subtable or how it is done?????

Please clarify these doubts.....it wud be really appreciable as we are unable to find exact fundamentals behind this.....
Hoping for a reply from you ,that will clear all doubts

Enthusiast

Re: mLOAD DELETE JOB

Hi Dnoeth/Forum Gurus
Would be appreciable if you throw some light please..............
Enthusiast

Re: mLOAD DELETE JOB

Fastload doesn't have an application phase. It distributes and pre-sorts the data as it arrives. A final merge of the pre-sorted buckets is done at the end of the acquisition phase. Then fallback is created.

Multiload distributes dual (primary and fallback) copies of rows during the acquisition phase.

>> 1)changes to both primary and fallback rows are done in parallel???

AMPs work in parallel, but each AMP updates one block at a time.

>>2)If table is having NUSI.....

>>changes done to data block is stored in worktable in application phase and applied to NUSI subtable or how it is done?????

The stored NUSI updates are sorted and applied as a batch.

Enthusiast

Re: mLOAD DELETE JOB

Hi Jim

Thankx for your reply

In a way your reply shook my knowledge about fastload

Till now we knew that Fastload is having 2 phases

1)Acquistion-----------data dumped in 64k blocks to AMP in round robbin fashion ,hashed by receiver AMP and send to appropriate AMP where they are stored in spool cylinders(not sure where they are written in this phase)

2)Application--------Each AMP deblock the data ,sort it on row hash and write the block to final disk......

But you have written "Fastload doesn't have an application phase. It distributes and pre-sorts the data as it arrives. A final merge of the pre-sorted buckets is done at the end of the acquisition phase. Then fallback is created.""

Could you please clarify how exactly it goes in fastload......
Enthusiast

Re: mLOAD DELETE JOB

What you "knew" about Fastload is simply wrong. Just get it out of your head. What specifically did you find unclear about what I wrote?
Enthusiast

Re: mLOAD DELETE JOB

Fastload doesn't have an application phase. It distributes and pre-sorts the data as it arrives. A final merge of the pre-sorted buckets is done at the end of the acquisition phase. Then fallback is created.

This Sir...... until now we knew that Fastload has both phases acquisition and application...

Could you please clarify how it really goes in Fastload..(Whole process in brief)

Teradata Employee

Re: mLOAD DELETE JOB

Jim has already provided the correct answer. Fastload does have two phases, rather unimaginatively named "Phase 1" and "Phase 2". There is nothing to "apply" so no Application phase; Phase 1 and Phase 2 together would very roughly correspond to MultiLoad Acquisition phase (which loads the worktable).

In Phase 1, whole data buffers are sent to AMPs (round-robin). The receiving AMP deblocks the rows, computes the rowhash, and sends the row to the target AMP. The receiving AMP inserts rows into data blocks in memory, and as memory fills up a group of data blocks are written out to PermSpace. Once written, data blocks are not re-read or modified (e.g. to insert additional rows "where they belong"). You can run multiple Phase 1's to append data to the table prior to moving on to Phase 2, but in this state no SQL operations are allowed.

In Phase 2, those "partially sorted" groups of blocks are merged so that the overall RowHash order is correct for the table (as required by normal SQL operations), and if the table is Fallback protected then the fallback copy is created.