Export Text File via Teradata FastExport

access_time 3 months ago visibility142 comment 0

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.

infoDue to limitations in FastLoad, it can not be exported as a standard CSV file. If you want to export a standard CSV file, please use TPT.

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

info Last modified by Administrator at 3 months ago copyright This page is subject to Site terms.
Like this article?
Share on

Please log in or register to comment.

account_circle Log in person_add Register

Log in with external accounts

Want to publish your article on Kontext?

Learn more

Kontext Column

Created for everyone to publish data, programming and cloud related articles.
Follow three steps to create your columns.


Learn more arrow_forward

More from Kontext

local_offer SQL local_offer SQL Server local_offer t-sql local_offer teradata local_offer teradata-sql-query

visibility 42899
thumb_up 0
access_time 6 years ago

SELECT is one of the most commonly used statements. In this tutorial, I will cover the following items: Two of the principal query clauses—FROM and SELECT Data Types Built-in functions CASE expressions and variations like ISNULL and COALESCE. * The function names mentioned above are ...

local_offer teradata local_offer teradata-utilities

visibility 50
thumb_up 0
access_time 3 months ago

BTEQ is a Teradata utility tool that can be used to run Teradata SQL statements incl. DDL, DML, etc. It can also be used to import data from text file into Teradata databases. Like TPT and FASTLOAD, it can run in both batch and interactive modes. This article demonstrates how to load XML file into ...

local_offer teradata

visibility 638
thumb_up 0
access_time 4 years ago

This post summarizes the tutorials I created in the past about Teradata. It is trying to help you gain the basic SQL skills with Teradata especially if you are a new beginner or you just move from SQL Server or Oracle. It also includes some guides about setting up your own Teradata server (latest Teradata Express version TDExpress16.10.00.03_Sles11_40GB.7z).

About column

Tutorials and information about Teradata.

rss_feed Subscribe RSS