Using Checkpoints in a Single Export Job - FastExport

Teradata FastExport Reference

Product
FastExport
Release Number
16.10
Published
May 2017
Language
English (United States)
Last Update
2018-05-22
dita:mapPath
fmk1488824663357.ditamap
dita:ditavalPath
Audience_PDF_include.ditaval
dita:id
B035-2410
lifecycle
previous
Product Category
Teradata Tools and Utilities

FastExport has a built-in feature for saving the position information for imported and exported data sources. This feature is useful with SELECT statements that retrieve a very large answer set, preventing FastExport from re-exporting data if a system restart occurs.

For example, assume a table of 5,000,000 rows exists, and the following single SELECT statement will be used to export the data to a file:

.EXPORT OUTFILE DATAFILE;
 SEL * FROM DATA_TABLE;
.END EXPORT;

If a system restart occurs while this statement is processing, the entire export process must start over from the beginning.

Avoid this type of scenario by breaking the single SELECT statement into multiple statements using WHERE clauses and storing boundary values in an INFILE.

Break up the preceding single SELECT statement similar to the following example:

SEL * FROM DATA_TABLE WHERE FIELD1 BETWEEN       1 AND 1000000;
SEL * FROM DATA_TABLE WHERE FIELD1 BETWEEN 1000001 AND 2000000;
SEL * FROM DATA_TABLE WHERE FIELD1 BETWEEN 2000001 AND 3000000;
SEL * FROM DATA_TABLE WHERE FIELD1 BETWEEN 3000001 AND 4000000;
SEL * FROM DATA_TABLE WHERE FIELD1 BETWEEN 4000001 AND 5000000;

Place all boundary values in INFILE as follows:
---------------------------------------------------------------
FIRST FIELD SECOND FIELD
---------------------------------------------------------------
FIRST RECORD                 1    1000000
SECOND RECORD          1000001    2000000
THIRD RECORD           2000001    3000000
FOURTH RECORD          3000001    4000000
FIFTH RECORD           4000001    5000000
---------------------------------------------------------------

Then, as FastExport executes the multiple statements in the second example, they are sent one at a time. In addition, the position for the import and export data sources is saved.

If a system restart occurs while one of the statements is processing, FastExport does not restart the entire export process from the beginning as it does when a single SELECT statement is used. Instead, FastExport restarts the export process only from the statement that failed.

The following sample script uses the data from the INFILE for the WHERE clause and creates the multiple statements shown in the second example.

This script can be used with FastExport running on all operating systems.

.LOGTABLE LOG_TBL;                /* define restart log             */

.LOGON TDPR/username, password;   /* DBC logon string               */

.BEGIN EXPORT ;                   /* specify export function        */

.LAYOUT BOUNDRIES;                /* define the input data values   */
.FIELD FROMVALUE * INTEGER;       /* for SELECT constraint clause   */
.FIELD TOVALUE * INTEGER;

.IMPORT INFILE INDATAFILE         /* define the file that contains  */
          LAYOUT BOUNDRIES;       /* the input data values          */
		  
.EXPORT OUTFILE OUTPUTFILE ;      /* identify the destination source*/
                                  /* for exported data              */
SEL * FROM DATA_TABLE             /* provide the SQL SELECT         */
                                  /* statement                      */
WHERE COLUMN1 BETWEEN             /* with values provided by the    */
                                  /* IMPORT command                 */
:FROMVALUE AND :TOVALUE ;

.END EXPORT;                      /* terminate the export operation */

.LOGOFF;                          /* disconnect from the DBS        */