Generated MultiLoad Script File - 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

When the MLSCRIPT option is specified in the EXPORT command, FastExport uses the functional parameters of the export task to generate a MultiLoad script file that can be used later to reload the export data back into Teradata Database.

The following subsections provide an example of a generated MultiLoad script file and describe the changes which might need to made before running the file, including the impact of OUTMOD routines and multiple select statements in FastExport job.

Script File Example

/* Date of extract: MON SEP 24, 2012 */
/* Time of extract: 16:59:15 */
/* Total records extracted for select 1 = 2 */
/* Output record length for select 1 = 41 variable */
/* NOTE: THE SCRIPT BELOW MAY NEED TO BE MODIFIED BEFORE RUNNING. */
.LOGTABLE LOGTABLE165915;
.LOGON slugger/fexp_usr,fexp_usr;
.SET DBASE_TARGETTABLE TO 'fexp_usr';
.SET DBASE_WORKTABLE TO 'fexp_usr';
.SET DBASE_ETTABLE TO 'fexp_usr';
.SET DBASE_UVTABLE TO 'fexp_usr';
.SET TARGETTABLE TO 'TABLE165915';
.BEGIN IMPORT MLOAD
TABLES &DBASE_TARGETTABLE..&TARGETTABLE
WORKTABLES &DBASE_WORKTABLE..WT_&TARGETTABLE
ERRORTABLES &DBASE_ETTABLE..ET_&TARGETTABLE
&DBASE_UVTABLE..UV_&TARGETTABLE;
.LAYOUT DATAIN_LAYOUT;
.FIELD COL001 1 INTEGER;
.FIELD COL002 5 DATE;
.FIELD COL003 9 CHAR(5);
.FIELD COL004 14 VARCHAR(8);
.FIELD COL005 * BYTEINT;
.FIELD COL006 * SMALLINT;
.FIELD COL007 * DECIMAL(5,2);
.FIELD COL008 * BYTE(2);
.FIELD COL009 * VARBYTE(3);
.FIELD COL010 * FLOAT;
.DML LABEL INSERT_DML;
INSERT INTO &DBASE_TARGETTABLE..&TARGETTABLE (
COL001 = :COL001
,COL002 = :COL002
,COL003 = :COL003
,COL004 = :COL004
,COL005 = :COL005
,COL006 = :COL006
,COL007 = :COL007
,COL008 = :COL008
,COL009 = :COL009
,COL010 = :COL010
);
.IMPORT INFILE dedtfm09.dat
FORMAT FASTLOAD
LAYOUT DATAIN_LAYOUT
APPLY INSERT_DML;
.END MLOAD;
.LOGOFF &SYSRC;
/* End of script */

Modifying the MultiLoad Script File

If necessary, modify the following specifications in the generated MultiLoad script file:

  • Target table name
  • Column names in the INSERT statement
  • Field names in the LAYOUT statement
  • Logon string
  • Log table name
  • Database of the following tables:
    • Target tables
    • Work tables
    • Error tables
    • Unique violation tables

Additionally:

  • FastExport generates the MultiLoad script layout based on field sizes and types received from the Teradata Database. If ANSIDATE is used in the DATEFORM command, all ANSI/SQL DateTime TIME, TIMESTAMP, and INTERVAL data types of the layout fields must be modified by converting to fixed-length CHAR data types.

    The ANSI/SQL Date/Time Specification table in FIELD provides the conversion specifications and format examples for each ANSI/SQL DateTime specification.

  • If the FastExport job uses an OUTMOD routine, the layout specifications in the generated MultiLoad script may need to be changed if the OUTMOD routine changes the record length
  • If the FastExport job uses multiple SELECT statements, the response rows must all have the same structure. If the response rows have different structures, then the layout in the generated MultiLoad script will not work. In this case, to use the generated MultiLoad script file option:
    • Use a separate FastExport task for each SELECT statement in a FastExport job
    • In each corresponding EXPORT command, use the MLSCRIPT option with a different fileid specification
Do not execute a FastExport job that generates the MultiLoad script and then execute, in the same batch file, a Multiload job that uses the script generated by the FastExport job. On some OS client platforms, executing the two jobs in the same batch file might result in the Multiload job crashing because the Multiload job may start before the generated script is completed.