RESTARTING FastLoad

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.

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 SQL01.Department; DROP TABLE SQL01.Dept_Err1; DROP TABLE SQL01.Dept_Err2;	DROPS TARGET TABLE AND ERROR TABLES
	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.