Export Text File via Teradata FastExport

Teradata FastExport is a command utility tool that can transfer large amount of data from Teradata database to a file.  One of the commonly used scenarios is to export data from a table or view to a text file and then load the export file into a different server. 

Teradata environment

Follow the following series to install Teradata and utility tools if you don't have a Teradata environment to work with.

Install Teradata Tools & Utilities

Ensure all utilities are installed.

Example table

An example table named TestDb.FASTLOAD_CSV3 has been created previously via FastLoad. This tutorial will export this file as a text file.

The table includes the following records:

FastExport job

A typical FastExport job includes the following steps:

1. Log on to Teradata Database.
2. Retrieve the specified data from Teradata Database.
3. Export the data to the specified file or OUTMOD routine on a client system.
4. Log off of Teradata Database.

Create the job script file

Create a job file named export.fastexport. The content looks like the following:

.LOGTABLE TestDb.FASTLOAD_CSV3_LOG_TBL;
.LOGON 192.168.119.128/DBC,DBC;
DATABASE TestDb;
.BEGIN EXPORT;
	.EXPORT OUTFILE F:\tmp\test_fastexport.csv MODE RECORD FORMAT TEXT;
	
	SEL CAST(ID AS VARCHAR(10)) AS ID,
		CAST(Col_Attr AS VARCHAR(1)) AS Col_Attr,
		CAST(Col_Value AS VARCHAR(10)) AS Col_Value,
		CAST(CreatedDate AS VARCHAR(10)) AS CreatedDate,
		Comments
	FROM FASTLOAD_CSV3;

.END EXPORT;
.LOGOFF ;

The above statement export the data as text file.

Run FastExport script

Command fexp can be used to run the script job:

fexp < export.fastexport

The following is the sample output:

     ========================================================================
     =                                                                      =
     =          FastExport Utility    Release FEXP.16.10.00.03              =
     =          Platform WIN32                                              =
     =          PID      16984                                              =
     =                                                                      =
     ========================================================================
     =                                                                      =
     =     Copyright 1990-2017 Teradata Corporation. ALL RIGHTS RESERVED.   =
     =                                                                      =
     ========================================================================
**** 12:06:28 UTY2411 Processing start date: MON SEP 14, 2020
     ========================================================================
     =                                                                      =
     =          Logon/Connection                                            =
     =                                                                      =
     ========================================================================
0001 .LOGTABLE TestDb.FASTLOAD_CSV3_LOG_TBL;
0002 .LOGON 192.168.119.128/DBC,;
**** 12:06:29 UTY8400 Teradata Database Release: 16.10.00.03
**** 12:06:29 UTY8400 Teradata Database Version: 16.10.00.03
**** 12:06:29 UTY8400 Default character set: ASCII
**** 12:06:29 UTY8400 Current RDBMS has UDT support
**** 12:06:29 UTY8400 Current RDBMS has Large Decimal support
**** 12:06:29 UTY8400 Current RDBMS has Statement Info Parcel support
**** 12:06:29 UTY8400 Current RDBMS has FEXP w/o Spooling support
**** 12:06:29 UTY8400 Current RDBMS has TASM support
**** 12:06:29 UTY8400 Maximum supported buffer size: 1M
**** 12:06:29 UTY8400 Data Encryption supported by RDBMS server
**** 12:06:29 UTY6211 A successful connect was made to the RDBMS.
**** 12:06:29 UTY6217 Logtable 'TestDb.FASTLOAD_CSV3_LOG_TBL' has been created.
     ========================================================================
     =                                                                      =
     =          Processing Control Statements                               =
     =                                                                      =
     ========================================================================
0003 DATABASE TestDb;
**** 12:06:29 UTY1016 'DATABASE' request successful.
0004 .BEGIN EXPORT;
0005    .EXPORT OUTFILE F:\tmp\test_fastexport.csv MODE RECORD FORMAT TEXT;
0006
        SEL CAST(ID AS VARCHAR(10)) AS ID,
                CAST(Col_Attr AS VARCHAR(1)) AS Col_Attr,
                CAST(Col_Value AS VARCHAR(10)) AS Col_Value,
                CAST(CreatedDate AS VARCHAR(10)) AS CreatedDate,
                Comments
        FROM TestDb.FASTLOAD_CSV3;

0007 .END EXPORT;
     ========================================================================
     =                                                                      =
     =          FastExport Initial Phase                                    =
     =                                                                      =
     ========================================================================
**** 12:06:29 UTY8700 Options in effect for this FastExport task:
     .       Sessions:    4 session(s).
     .       Mode:        RECORD
     .       Blocksize:   64330 bytes.
     .       Outlimit:    No limit in effect.
**** 12:06:29 UTY8715 FastExport is submitting the following request:
     Select NULL from TestDb.FASTLOAD_CSV3_LOG_TBL where (LogType = 220) and
     (Seq = 1) and (FExptSeq = 0);
**** 12:06:29 UTY8715 FastExport is submitting the following request:
     SET QUERY_BAND='UTILITYNAME=FASTEXP;' UPDATE FOR SESSION;
**** 12:06:29 UTY8715 FastExport is submitting the following request:
     CHECK WORKLOAD FOR BT;BEGIN FASTEXPORT;
**** 12:06:29 UTY8715 FastExport is submitting the following request:
     CHECK WORKLOAD FOR
        SEL CAST(ID AS VARCHAR(10)) AS ID,
                CAST(Col_Attr AS VARCHAR(1)) AS Col_Attr,
                CAST(Col_Value AS VARCHAR(10)) AS Col_Value,
                CAST(CreatedDate AS VARCHAR(10)) AS CreatedDate,
                Comments
        FROM FASTLOAD_CSV3;
**** 12:06:29 UTY8715 FastExport is submitting the following request:
     CHECK WORKLOAD END;
**** 12:06:29 UTY0844 Session count 2 returned by the DBS overrides
     user-requested session count.
**** 12:06:29 UTY8705 EXPORT session(s) requested: 2.
**** 12:06:29 UTY8706 EXPORT session(s) connected: 2.
**** 12:06:29 UTY8715 FastExport is submitting the following request:
     BT;BEGIN FASTEXPORT;
**** 12:06:29 UTY8715 FastExport is submitting the following request:
     SELECT MiscInt1 (INTEGER), MiscInt2 (INTEGER), MiscInt3 (INTEGER),FExptSeq
     (INTEGER), FExptCkpt (VARBYTE(1024)) from TestDb.FASTLOAD_CSV3_LOG_TBL
     WHERE (LogType = 210) and (Seq = 1) and (FExptSeq IN (SELECT MAX(FExptSeq)
     from TestDb.FASTLOAD_CSV3_LOG_TBL where (LogType = 210) and (Seq = 1)));
**** 12:06:29 UTY8715 FastExport is submitting the following request:
     SELECT MiscInt1 (INTEGER), MiscInt2 (INTEGER),  MiscInt3 (INTEGER),FExptSeq
     (INTEGER), FExptCkpt (VARBYTE(1024)) from TestDb.FASTLOAD_CSV3_LOG_TBL
     WHERE (LogType = 212) and (Seq = 1) and (FExptSeq IN (SELECT MAX(FExptSeq)
     from TestDb.FASTLOAD_CSV3_LOG_TBL where (LogType = 212) and (Seq = 1)));
**** 12:06:29 UTY8715 FastExport is submitting the following request:

        SEL CAST(ID AS VARCHAR(10)) AS ID,
                CAST(Col_Attr AS VARCHAR(1)) AS Col_Attr,
                CAST(Col_Value AS VARCHAR(10)) AS Col_Value,
                CAST(CreatedDate AS VARCHAR(10)) AS CreatedDate,
                Comments
        FROM TestDb.FASTLOAD_CSV3;
**** 12:06:29 UTY8724 Select request submitted to the RDBMS.
**** 12:06:29 UTY8725 Select execution completed. 2 data blocks generated.
**** 12:06:29 UTY8756 Retrieval Rows statistics:
              Elapsed time:   00:00:00 (hh:mm:ss)
              CPU time:       0.015625 Seconds
              MB/sec:         N/A
              MB/cpusec:      0.0134888
**** 12:06:29 UTY8715 FastExport is submitting the following request:
     INS TestDb.FASTLOAD_CSV3_LOG_TBL (LogType, Seq) VALUES (220, 1)
**** 12:06:29 UTY8715 FastExport is submitting the following request:
     END FASTEXPORT;ET;
**** 12:06:30 UTY8710 Processing complete for this FastExport task.
     ========================================================================
     =                                                                      =
     =          FastExport Task Complete                                    =
     =                                                                      =
     ========================================================================
**** 12:06:30 UTY1024 Session modal request, 'DATABASE', re-executed.
**** 12:06:30 UTY1024 Session modal request, 'SET
     QUERY_BAND='UTILITYNAME=FASTEXP;' UPDATE FOR SESSION;', re-executed.
**** 12:06:30 UTY8722 4 total records written to output file.
0008 .LOGOFF ;
     ========================================================================
     =                                                                      =
     =          Logoff/Disconnect                                           =
     =                                                                      =
     ========================================================================
**** 12:06:30 UTY6216 The restart log table has been dropped.
**** 12:06:30 UTY6212 A successful disconnect was made from the RDBMS.
**** 12:06:30 UTY2410 Total processor time used = '0.3125 Seconds'
     .       Start : 12:06:28 - MON SEP 14, 2020
     .       End   : 12:06:30 - MON SEP 14, 2020
     .       Highest return code encountered = '0'.

References

Teradata FastLoad Reference