FASTLOAD

   

An Introduction to FastLoad

Why it is called “FAST”Load

FastLoad is known for its lightning-like speed in loading vast amounts of data from flat files from a host into empty tables in Teradata. Part of this speed is achieved because it does not use the Transient Journal. You will see some more of the reasons enumerated below. But, regardless of the reasons that it is fast, know that FastLoad was developed to load millions of rows into a table.

The way FastLoad works can be illustrated by home construction, of all things! Let’s look at three scenarios from the construction industry to provide an amazing picture of how the data gets loaded.

Scenario One: Builders prefer to start with an empty lot and construct a house on it, from the foundation right on up to the roof. There is no pre-existing construction, just a smooth, graded lot. The fewer barriers there are to deal with, the quicker the new construction can progress. Building custom or spec houses this way is the fastest way to build them. Similarly, FastLoad likes to start with an empty table, like an empty lot, and then populate it with rows of data from another source. Because the target table is empty, this method is typically the fastest way to load data. FastLoad will never attempt to insert rows into a table that already holds data.

Scenario Two: The second scenario in this analogy is when someone buys the perfect piece of land on which to build a home, but the lot already has a house on it. In this case, the person may determine that it is quicker and more advantageous just to demolish the old house and start fresh from the ground up — allowing for brand new construction. FastLoad also likes this approach to loading data. It can just 1) drop the existing table, which deletes the rows, 2) replace its structure, and then 3) populate it with the latest and greatest data. When dealing with huge volumes of new rows, this process will run much quicker than using MultiLoad to populate the existing table. Another option is to DELETE all the data rows from a populated target table and reload it. This requires less updating of the Data Dictionary than dropping and recreating a table. In either case, the result is a perfectly empty target table that FastLoad requires!

Scenario Three: Sometimes, a customer has a good house already but wants to remodel a portion of it or to add an additional room. This kind of work takes more time than the work described in Scenario One. Such work requires some tearing out of existing construction in order to build the new section. Besides, the builder never knows what he will encounter beneath the surface of the existing home. So you can easily see that remodeling or additions can take more time than new construction. In the same way, existing tables with data may need to be updated by adding new rows of data. To load populated tables quickly with large amounts of data while maintaining the data currently held in those tables, you would choose MultiLoad instead of FastLoad. MultiLoad is designed for this task but, like renovating or adding onto an existing house, it may take more time.

How FastLoad Works

What makes FastLoad perform so well when it is loading millions or even billions of rows? It is because FastLoad assembles data into 64K blocks (64,000 bytes) to load it and can use multiple sessions simultaneously, taking further advantage of Teradata’s parallel processing.

This is different from BTEQ and TPump, which load data at the row level. It has been said, “If you have it, flaunt it!” FastLoad does not like to brag, but it takes full advantage of Teradata’s parallel architecture. In fact, FastLoad will create a Teradata session for each AMP (Access Module Processor — the software processor in Teradata responsible for reading and writing data to the disks) in order to maximize parallel processing. This advantage is passed along to the FastLoad user in terms of awesome performance. Teradata is the only data warehouse product in the world that loads data, processes data and backs up data in parallel.

FastLoad Has Some Limits

There are more reasons why FastLoad is so fast. Many of these become restrictions and therefore, cannot slow it down. For instance, can you imagine a sprinter wearing cowboy boots in a race? Of course, not! Because of its speed, FastLoad, too, must travel light! This means that it will have limitations that may or may not apply to other load utilities. Remembering this short list will save you much frustration from failed loads and angry colleagues. It may even foster your reputation as a smooth operator!

Rule #1: No Secondary Indexes are allowed on the Target Table. High performance will only allow FastLoad to utilize Primary Indexes when loading. The reason for this is that Primary (UPI and NUPI) indexes are used in Teradata to distribute the rows evenly across the AMPs and build only data rows. A secondary index is stored in a subtable block and many times on a different AMP from the data row. This would slow FastLoad down and they would have to call it: get ready now, HalfFastLoad. Therefore, FastLoad does not support them. If Secondary Indexes exist already, just drop them. You may easily recreate them after completing the load.

Rule #2: 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. FastLoad only does one table. In short, RI constraints will need to be dropped from the target table prior to the use of FastLoad.

Rule #3: 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.

Rule #4: Duplicate Rows (in Multi-Set Tables) are not supported. Multi-set tables are tables 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!

Rule #5: No AMPs may go down (i.e., go offline) while FastLoad is processing. The down AMP must be repaired before the load process can be restarted. Other than this, FastLoad can recover from system glitches and perform restarts. We will discuss Restarts later in this chapter.

Rule #6: No more than one data type conversion is allowed per column during a FastLoad. Why just one? Data type conversion is highly resource intensive job on the system, which requires a “search and replace” effort. And that takes more time. Enough said!

Three Key Requirements for FastLoad to Run

FastLoad can be run from either MVS/ Channel (mainframe) or Network (LAN) host. In either case, FastLoad requires three key components. They are a log table, an empty target table and two error tables. The user must name these at the beginning of each script.

Log Table: FastLoad needs a place to record information on its progress during a load. It uses the table called Fastlog in the SYSADMIN database. This table contains one row for every FastLoad running on the system. In order for your FastLoad to use this table, you need INSERT, UPDATE and DELETE privileges on that table.

Empty Target Table: We have already mentioned the absolute need for the target table to be empty. FastLoad does not care how this is accomplished. After an initial load of an empty target table, you are now looking at a populated table that will likely need to be maintained.

If you require the phenomenal speed of FastLoad, it is usually preferable, both for the sake of speed and for less interaction with the Data Dictionary, just to delete all the rows from that table and then reload it with fresh data. The syntax DELETE <databasename>.<tablename> should be used for this. But sometimes, as in some of our FastLoad sample scripts (see Figure 4-2), you want to drop that table and recreate it versus using the DELETE option. To do this, FastLoad has the ability to run the DDL statements DROP TABLE and CREATE TABLE. The problem with putting DDL in the script is that is no longer restartable and you are required to rerun the FastLoad from the beginning. Otherwise, we recommend that you have a script for an initial run and a different script for a restart.

Two Error Tables: Each FastLoad requires two error tables. These are error tables that will only be populated should errors occur during the load process. These are required by the FastLoad utility, which will automatically create them for you; all you must do is to name them. The first error table is for any translation errors or constraint violations. For example, a row with a column containing a wrong data type would be reported to the first error table. The second error table is for errors caused by duplicate values for Unique Primary Indexes (UPI). FastLoad will load just one occurrence for every UPI. The other occurrences will be stored in this table. However, if the entire row is a duplicate, FastLoad counts it but does not store the row. These tables may be analyzed later for troubleshooting should errors occur during the load. For specifics on how you can troubleshoot, see the section below titled, “What Happens When FastLoad Finishes.”

Maximum of 15 Loads

The Teradata RDBMS will only run a maximum number of fifteen FastLoads, MultiLoads, or FastExports at the same time. This maximum is determined by a value stored in the DBS Control record. It can be any value from 0 to 15. When Teradata is first installed, this value is set to 5 concurrent jobs.

Since these utilities all use the large blocking of rows, it hits a saturation point where Teradata will protect the amount system resources available by queuing up the extra load. For example, if the maximum number of jobs are currently running on the system and you attempt to run one more, that job will not be started. You should view this limit as a safety control. Here is a tip for remembering how the load limit applies: If the name of the load utility contains either the word “Fast” or the word “Load”, then there can be only a total of fifteen of them running at any one time.