Teradata Utilities – FASTLOAD part 2

Phases of Fastload

Fastload divides its job into two phases


1)       Phase 1 – Acquisition

2)      Phase 2 – Application

Phase 1 – Acquisition

• The main objective of this phase is to send rows of data file from HOST computer to Teradata AMPs as fast as possible.
• Rows of the data file are packed into 64k blocks and send it to PE.
• PE will parse the SQL of Fastload and send the Explain plan to each AMP. It will by default create 1 session per AMP, so if your system has 200 AMP then it will make 200 sessions for 1 Fastload job.

Tip – It is advisable to restrict the session with the help of .SESSION command so that Fastload job don’t end up taking all the available resources of the system.

• After creation of sessions the 64k blocks data is passed to AMP with the help of PE and BYNET where it is quickly hashed according to its PI value.
• Based on this row hash value the rows are then redistributed to its proper AMP. Internal redistribution takes place within AMP, so that each AMP gets the correct row. To know more about this redistribution based on row hash please refer –Primary Index in Teradata
• Now each row is placed in its proper AMP, but they are not sorted till now.
• Any error in this phase will be recorded in the Error table 1.

Phase 2 – Application

• The main objective of this phase is to store each row into the actual target table.
• The rows are sorted by AMP which is temporarily stored in its DISK during phase 1
• These sorted rows then send to actual target table where they will reside permanently.
• All these operation are BLOCK level operation thus giving more speed when we compare it ROW level operation.
• Any error in this phase will be stored in Error table 2.

Limitations of FAST LOAD 

There are few limitations which we have on Fastload.

These limitations are necessary for Fastload to load data with the lightning fast speed into tables.

• NO SECONDARY INDEXES ARE ALLOWED ON TARGET TABLE – Fastload can load tables only with primary indexes defined on it. If we have a secondary index on the table then Fastload will not load that table. We get an error message if we load such type of table –

If secondary index exist already, we need to drop them.

After loading the table through Fastload we can easily recreate them on the table.

CREATE INDEX (Column-name/s) ON dbname.tablename —— > creates Non Unique Secondary Index

or

CREATE UNIQUE INDEX (Column-name/s) ON dbname.tablename —— > creates Unique Secondary Index

TIP – Fastload Allows loading of PPI table.

• NO REFERENTIAL INTEGRITY IS ALLOWED – Fastload cannot load data into tables that are defined with Referential Integrity (RI). This would require too much system checking to prevent referential constraints to a different table

• NO TRIGGERS ARE ALLOWED AT LOAD TIME – Fastload is much too focused on speed to pay attention to the needs of other tables, which is what Triggers are all about. Additionally, these require more than one AMP and more than one table. Fastload does one table only. Simply ALTER the Triggers to the DISABLED status prior to using Fastload.

• DUPLICATE ROWS ARE NOT SUPPORTED – Multiset tables are a table that allow duplicate rows — that is when the values in every column are identical. When Fastload finds duplicate rows, they are discarded. While Fastload can load data into a multi-set table, Fastload will not load duplicate rows into a multi-set table because Fastload discards duplicate rows

To know more about Fastload check below mentioned links –


Fastload Part 1

Fastload part 3

Fastload Part 4