Sample FastLoad Script
Let’s look at an actual FastLoad script that you might see in the real
world. In the script below, every comment line is placed inside the
normal Teradata comment syntax, [/*. . . . */]. FastLoad and SQL
commands are written in upper case in order to make them stand out.
In reality, Teradata utilities, like Teradata itself, are by default not
case sensitive. You will also note that when column names are listed
vertically we recommend placing the comma separator in front of the
following column. Coding this way makes reading or debugging the
script easier for everyone. The purpose of this script is to update the
Employee_Profile table in the SQL01 database. The input file used for
the load is named [Link]. Below the sample script each step will be
described in detail.
Normally it is not a good idea to put the DROP and CREATE statements
in a FastLoad script. The reason is that when any of the tables that
FastLoad is using are dropped, the script cannot be restarted. It can
only be rerun from the beginning. Since FastLoad has restart logic built
into it, a restart is normally the better solution if the initial load
attempt should fail. However, for purposes of this example, it shows
the table structure and the description of the data being read.
/* !/bin/ksh* */ Runs from a shell script.
/* ++++++++++++++++++++++++++++*/ Always good to identify
/* FASTLOAD SCRIPT TO LOAD THE */ the script and author in
/* Employee_Profile TABLE */ comments.
/* Version 1.1 */ Since this script does not
/* Created by Coffing Data Warehousing */ drop the target or error
/* ++++++++++++++++++++++++++++*/ tables, it is restartable.
This is a good thing for
/* Setup the FastLoad Parameters */ production jobs.
SESSIONS 100; /*or, the number of sessions supportable*/ Specify the number of
sessions to logon.
TENACITY 4; /* the default is no tenacity, means no retry */ Tenacity is set to 4 hr;
SLEEP 10; /* the default is 6, means retry in 6 minutes */ Wait 10 Min between
retries.
LOGON CW/SQL01,SQL01;
SHOW VERSIONS; /* Shows the Utility’s release number */ Display the version of
FastLoad.
/* Set the Record type to a comma delimited for FastLoad */ Starts with the second
RECORD 2; record.
SET RECORD VARTEXT ‘,’; Specifies if record layout
is vartext with a comma
delimiter.
Notice that all fields are
defined as VARCHAR.
When using VARTEXT,
the fields do not contain
the length field like in
these formats: text,
FastLoad, or
unformatted.
FILE= [Link]; Defines the flat file
name.
/* Optional to show the layout of the input */ SHOW Specifies table to load
and lock.
/* Begin the Load and Insert Process into the */
/* Employee_Profile Table */
BEGIN LOADING SQL01.Employee_Profile Names the error tables.
ERRORFILES SQL01.Emp_Err1, SQL01.Emp_Err2 Sets the number of rows
CHECKPOINT 100000; at which to pause &
record progress in the
restart log before loading
further.
Defines the insert
statement to use for
loading the rows.
END LOADING; Continues loading
process with Phase 2.
LOGOFF; Logs off of Teradata.
Figure 4-3
Step One: Before logging onto Teradata, it is important to specify how
many sessions you need. The syntax is [SESSIONS {n}].
Step Two: Next, you LOGON to the Teradata system. You will quickly
see that the utility commands in FastLoad are similar to those in BTEQ.
FastLoad commands were designed from the underlying commands in
BTEQ. However, unlike BTEQ, most of the FastLoad commands do not
allow a dot [“.”] in front of them and therefore need a semi-colon. At
this point we chose to have Teradata tell us which version of FastLoad
is being used for the load. Why would we recommend this? We do
because as FastLoad’s capabilities get enhanced with newer versions,
the syntax of the scripts may have to be revisited.
Step Three: If the input file is not a FastLoad format, before you
describe the INPUT FILE structure in the DEFINE statement, you must
first set the RECORD layout type for the file being passed by FastLoad.
We have used VARTEXT in our example with a comma delimiter. The
other options are FastLoad, TEXT, UNFORMATTED OR VARTEXT. You
need to know this about your input file ahead of time.
Step Four: Next, comes the DEFINE statement. FastLoad must know
the structure and the name of the flat file to be used as the input FILE,
or source file for the load.
Step Five: FastLoad makes no assumptions from the DROP TABLE
statements with regard to what you want loaded. In the BEGIN
LOADING statement, the script must name the target table and the
two error tables for the load. Did you notice that there is no CREATE
TABLE statement for the error tables in this script? FastLoad will
automatically create them for you once you name them in the script.
In this instance, they are named “Emp_Err1” and “Emp_Err2”.
Phase 1 uses “Emp_Err1” because it comes first and Phase 2 uses
“Emp_Err2”. The names are arbitrary, of course. You may call them
whatever you like. At the same time, they must be unique within a
database, so using a combination of your userid and target table name
helps insure this uniqueness between multiple FastLoad jobs occurring
in the same database.
In the BEGIN LOADING statement we have also included the optional
CHECKPOINT parameter. We included [CHECKPOINT 100000].
Although not required, this optional parameter performs a vital task
with regard to the load. In the old days, children were always told to
focus on the three “R’s’ in grade school (“reading, ‘riting, and
‘rithmatic”). There are two very different, yet equally important, R’s to
consider whenever you run FastLoad. They are RERUN and RESTART.
RERUN means that the job is capable of running all the processing
again from the beginning of the load. RESTART means that the job is
capable of running the processing again from the point where it left off
when the job was interrupted, causing it to fail. When CHECKPOINT is
requested, it allows FastLoad to resume loading from the first row
following the last successful CHECKPOINT. We will learn more about
CHECKPOINT in the section on Restarting FastLoad.
Step Six: FastLoad focuses on its task of loading data blocks to AMPs
like little Yorkshire terrier’s do when playing with a ball! It will not stop
unless you tell it to stop. Therefore, it will not proceed to Phase 2
without the END LOADING command.
In reality, this provides a very valuable capability for FastLoad. Since
the table must be empty at the start of the job, it prevents loading
rows as they arrive from different time zones. However, to accomplish
this processing, simply omit the END LOADING on the load job. Then,
you can run the same FastLoad multiple times and continue loading
the worktables until the last file is received. Then run the last FastLoad
job with an END LOADING and you have partitioned your load jobs into
smaller segments instead of one huge job. This makes FastLoad even
faster!
Of course to make this work, FastLoad must be restartable. Therefore,
you cannot use the DROP or CREATE commands within the script.
Additionally, every script is exactly the same with the exception of the
last one, which contains the END LOADING causing FastLoad to
proceed to Phase 2. That’s a pretty clever way to do a partitioned type
of data load.
Step Seven: All that goes up must come down. And all the sessions
must LOGOFF. This will be the last utility command in your script. At
this point the table lock is released and if there are no rows in the
error tables, they are dropped automatically. However, if a single row
is in one of them, you are responsible to check it, take the appropriate
action and drop the table manually.
2A0C021F000000
When You Can RESTART FastLoad
If all of the following conditions are true, then FastLoad is ALWAYS restartable:
The Error Tables are NOT DROPPED in the script
The Target Table is NOT DROPPED in the script
The Target Table is NOT CREATED in the script
You have defined a checkpoint
So, if you need to drop or create tables, do it in a separate job using BTEQ. Imagine that
you have a table whose data changes so much that you typically drop it monthly and
build it again. Let’s go back to the script we just reviewed above and see how we can
break it into the two parts necessary to make it fully RESTARTABLE. It is broken up
below.
STEP ONE: Run the following SQL statements in Queryman or BTEQ before you
start FastLoad:
DROP TABLE [Link];
DROPS TARGET TABLE
DROP TABLE SQL01.Dept_Err1;
AND ERROR TABLES
DROP TABLE SQL01.Dept_Err2;
CREATES THE
DEPARTMENT TARGET
TABLE IN THE SQL01 DATA
BASE IN TERADATA
Figure 4-6
First, you ensure that the target table and error tables, if they existed previously, are
blown away. If there had been no errors in the error tables, they would be automatically
dropped. If these tables did not exist, you have not lost anything. Next, if needed, you
create the empty table structure needed to receive a FastLoad.
STEP TWO: Run the FastLoad script
This is the portion of the earlier script that carries out these vital steps:
Defines the structure of the flat file
Tells FastLoad where to load the data and store the errors
Specifies the checkpoint so a RESTART will not go back to row one
Loads the data
If these are true, all you need do is resubmit the FastLoad job and it starts loading data
again with the next record after the last checkpoint. Now, with that said, if you did not
request a checkpoint, the output message will normally indicate how many records were
loaded.
You may optionally use the RECORD command to manually restart on the next record
after the one indicated in the message.
Now, if the FastLoad job aborts in Phase 2, you can simply submit a script with only the
BEGIN LOADING and END LOADING. It will then restart right into Phase 2.
When You Cannot RESTART FastLoad
There are two types of FastLoad scripts: those that you can restart and those that you
cannot without modifying the script. If any of the following conditions are true of the
FastLoad script that you are dealing with, it is NOT restartable:
The Error Tables are DROPPED
The Target Table is DROPPED
The Target Table is CREATED
Why might you have to RESTART a FastLoad job, anyway? Perhaps you might
experience a system reset or some glitch that stops the job one half way through it.
Maybe the mainframe went down. Well, it is not really a big deal because FastLoad is so
lightning-fast that you could probably just RERUN the job for small data loads.
However, when you are loading a billion rows, this is not a good idea because it wastes
time. So the most common way to deal with these situations is simply to RESTART the
job. But what if the normal load takes 4 hours, and the glitch occurs when you already
have two thirds of the data rows loaded? In that case, you might want to make sure that
the job is totally restartable. Let’s see how this is done.
Restarting FastLoad — A More In-Depth Look
How the CHECKPOINT Option Works
CHECKPOINT option defines the points in a load job where the FastLoad utility pauses
to record that Teradata has processed a specified number of rows. When the parameter
“CHECKPOINT [n]” is included in the BEGIN LOADING clause the system will stop
loading momentarily at increments of [n] rows.
At each CHECKPOINT, the AMPs will all pause and make sure that everything is
loading smoothly. Then FastLoad sends a checkpoint report (entry) to the
[Link] table. This log contains a list of all currently running FastLoad jobs
and the last successfully reached checkpoint for each job. Should an error occur that
requires the load to restart, FastLoad will merely go back to the last successfully reported
checkpoint prior to the error. It will then restart from the record immediately following
that checkpoint and start building the next block of data to load. If such an error occurs in
Phase 1, with CHECKPOINT 0, FastLoad will always restart from the very first row.
Restarting with CHECKPOINT
Sometimes you may need to restart FastLoad. If the FastLoad script requests a
CHECKPOINT (other than 0), then it is restartable from the last successful checkpoint.
Therefore, if the job fails, simply resubmit the job. Here are the two options: Suppose
Phase 1 halts prematurely; the Data Acquisition phase is incomplete. Resubmit the
FastLoad script. FastLoad will begin from RECORD 1 or the first record past the last
checkpoint. If you wish to manually specify where FastLoad should restart, locate the last
successful checkpoint record by referring to the [Link] table. To
specify where a restart will start from, use the RECORD command. Normally, it is not
necessary to use the RECORD command — let FastLoad automatically determine where
to restart from.
If the interruption occurs in Phase 2, the Data Acquisition phase has already completed.
We know that the error is in the Application Phase. In this case, resubmit the FastLoad
script with only the BEGIN and END LOADING Statements. This will restart in Phase 2
with the sort and building of the target table.
Restarting without CHECKPOINT (i.e., CHECKPOINT 0)
When a failure occurs and the FastLoad Script did not utilize the CHECKPOINT (i.e.,
CHECKPOINT 0), one procedure is to DROP the target table and error tables and rerun
the job. Here are some other options available to you:
Resubmit job again and hope there is enough PERM space for all the rows already sent to
the unsorted target table plus all the rows that are going to be sent again to the same
target table. Other than using space, these rows will be rejected as duplicates. As you can
imagine, this is not the most efficient way since it processes many of the same rows
twice.
If CHECKPOINT wasn’t specified, then CHECKPOINT defaults to 100,000. You can
perform a manual restart using the RECORD statement. If the output print file shows that
checkpoint 100000 occurred, use something like the following command: [RECORD
100001;]. This statement will skip records 1 through 10000 and resume on record
100001.