Use Teradata Parallel Transporter to Move Data Without Landing the Data to Disk

Tools
Tools covers the tools and utilities you use to work with Teradata and its supporting ecosystem. You'll find information on everything from the Teradata Eclipse plug-in to load/extract tools.
Teradata Employee

Use Teradata Parallel Transporter to Move Data Without Landing the Data to Disk

Teradata Parallel Transporter (Teradata PT) supports moving data from a table on a Teradata Database system, or from an ODBC-compliant RDBMS, to a Teradata table on a different Teradata Database system without landing the data to disk.

There are performance benefits, cost savings, and ease of script maintenance associated with using Teradata PT to move data without landing it to disk.

Performance benefits

Unlike the traditional Teradata utilities, Teradata PT can start data exporting and data loading at the same time without having to wait for data exporting to complete. A Teradata PT producer operator, such as the Export operator, can export data from a table and send it to a Teradata PT data stream. A Teradata PT consumer operator, such as the Load operator, can consume the data from the data stream and load it into a Teradata Database table. Data streams consist of in-memory buffers where data is passed from a producer to a consumer operator.

A user of the traditional Teradata utilities can run the Teradata FastExport utility to export data to a disk file and then the Teradata FastLoad utility to load data from the disk file to an empty Teradata Database table. If a user FastExports data into a named pipe, the user can then use FastLoad to read the data from the named pipe.

We should add that when two scripts are needed to move data, there is always the possibility of inadvertently running FastLoad, for example, before FastExport.

Cost savings

When a very large amount of data (greater than 50 gigabytes) is moved through a Teradata PT data stream without landing the data to disk, a Teradata PT user saves on the cost of purchasing additional disk drives. A user can use the disk space that was used for some other purpose.

Single-script maintenance

Unlike the traditional Teradata utilities, a single Teradata PT job script can be coded to move data without landing it to disk. If a user needs to make a change to the Teradata PT job, the user only needs to change the Teradata PT job script.

Traditional Teradata utilities require two scripts: one FastExport script to export the data and one script to load the data.

Sample scripts

Here are two sample Teradata PT job scripts.

In each script, the Export operator extracts the data from the SOURCE_EMP_TABLE table and the Load operator loads the data into an empty table called TARGET_EMP_TABLE.

Both scripts use job variables. But the second script uses more job variables than the first, in addition to concatenating quoted scripts and job variables.

Sample Script 1

DEFINE JOB MOVE_DATA_WITHOUT_LANDING_TO_DISK

DESCRIPTION 'MOVE DATA WITHOUT LANDING THE DATA TO DISK'

(

/*** Schema Definition ***/

DEFINE SCHEMA EMPLOYEE_SCHEMA

DESCRIPTION 'SAMPLE EMPLOYEE SCHEMA'

(

EMP_ID INTEGER,

EMP_NAME CHAR(10)

);

/*** Export Operator Definition ***/

DEFINE OPERATOR EXPORT_OPERATOR

DESCRIPTION 'TERADATA PARALLEL TRANSPORTER EXPORT OPERATOR'

TYPE EXPORT

SCHEMA EMPLOYEE_SCHEMA

ATTRIBUTES

(

VARCHAR PrivateLogName = 'exportoper_privatelog',

INTEGER MaxSessions = 8,

INTEGER MinSessions,

VARCHAR TdpId = @SourceTdpId,

VARCHAR UserName = @SourceUserName,

VARCHAR UserPassword = @SourceUserPassword,

VARCHAR SelectStmt = 'SELECT * FROM SOURCE_EMP_TABLE;'

);

/*** Load Operator Definition ***/

DEFINE OPERATOR LOAD_OPERATOR

DESCRIPTION 'TERADATA PARALLEL TRANSPORTER LOAD OPERATOR'

TYPE LOAD

SCHEMA EMPLOYEE_SCHEMA

ATTRIBUTES

(

VARCHAR PrivateLogName = 'loadoper_privatelog',

INTEGER MaxSessions = 16,

INTEGER MinSessions,

VARCHAR TargetTable = 'TARGET_EMP_TABLE',

VARCHAR TdpId = @TargetTdpId,

VARCHAR UserName = @TargetUserName,

VARCHAR UserPassword = @TargetUserPassword,

VARCHAR ErrorTable1 = 'LOADOPER_ERRTABLE1',

VARCHAR ErrorTable2 = 'LOADOPER_ERRTABLE2',

VARCHAR LogTable = 'LOADOPER_LOGTABLE'

);

/*** Apply Statement ***/

APPLY

('INSERT INTO TARGET_EMP_TABLE (:EMP_ID, :EMP_NAME);')

TO OPERATOR (LOAD_OPERATOR [1])

SELECT * FROM OPERATOR (EXPORT_OPERATOR [2]);

);

Sample job variable file for Sample Script 1

 SourceTdpId          = 'MySourceTdpid'

,SourceUserName = 'MySourceUserName'

,SourceUserPassword = 'MySourceUserPassword'

,TargetTdpId = 'MyTargetTdpId'

,TargetUserName = 'MyTargetUserName'

,TargetUserPassword = 'MyTargetUserPassword'

Sample Script 2

DEFINE JOB MOVE_DATA_WITHOUT_LANDING_TO_DISK

DESCRIPTION 'MOVE DATA WITHOUT LANDING THE DATA TO DISK'

(

/*** Schema Definition ***/

DEFINE SCHEMA EMPLOYEE_SCHEMA

DESCRIPTION 'SAMPLE EMPLOYEE SCHEMA'

(

EMP_ID INTEGER,

EMP_NAME CHAR(10)

);

/*** Export Operator Definition ***/

DEFINE OPERATOR EXPORT_OPERATOR

DESCRIPTION 'TERADATA PARALLEL TRANSPORTER EXPORT OPERATOR'

TYPE EXPORT

SCHEMA EMPLOYEE_SCHEMA

ATTRIBUTES

(

VARCHAR PrivateLogName = 'exportoper_privatelog',

INTEGER MaxSessions = 8,

INTEGER MinSessions,

VARCHAR TdpId = @SourceTdpId,

VARCHAR UserName = @SourceUserName,

VARCHAR UserPassword = @SourceUserPassword,

VARCHAR SelectStmt = 'SELECT * FROM ' || @SourceTable || ';'

);

/*** Load Operator Definition ***/

DEFINE OPERATOR LOAD_OPERATOR

DESCRIPTION 'TERADATA PARALLEL TRANSPORTER LOAD OPERATOR'

TYPE LOAD

SCHEMA EMPLOYEE_SCHEMA

ATTRIBUTES

(

VARCHAR PrivateLogName = 'loadoper_privatelog',

INTEGER MaxSessions = 16,

INTEGER MinSessions,

VARCHAR TargetTable = @TargetTable,

VARCHAR TdpId = @TargetTdpId,

VARCHAR UserName = @TargetUserName,

VARCHAR UserPassword = @TargetUserPassword,

VARCHAR ErrorTable1 = @ErrorTable1,

VARCHAR ErrorTable2 = @ErrorTable2,

VARCHAR LogTable = @LogTable

);

/*** Apply Statement ***/

APPLY

('INSERT INTO ' || @TargetTable || ' (:EMP_ID, :EMP_NAME);')

TO OPERATOR (LOAD_OPERATOR [1])

SELECT * FROM OPERATOR (EXPORT_OPERATOR [2]);

);

Sample job variable file for Sample Script 2

 SourceTdpId          = 'MySourceTdpid'

,SourceUserName = 'MySourceUserName'

,SourceUserPassword = 'MySourceUserPassword'

,TargetTdpId = 'MyTargetTdpId'

,TargetUserName = 'MyTargetUserName'

,TargetUserPassword = 'MyTargetUserPassword'

,SourceTable = 'SOURCE_EMP_TABLE'

,TargetTable = 'TARGET_EMP_TABLE'

,ErrorTable1 = 'LOADOPER_ERRTABLE1'

,ErrorTable2 = 'LOADOPER_ERRTABLE2'

,LogTable = 'LOADOPER_LOGTABLE'

For information on the concatenation of quoted strings and job variables, see Teradata Parallel Transporter User Guide.

130 REPLIES

Re: Use Teradata Parallel Transporter to Move Data Without Landing the Data to Disk

As per this article we can export+load more than 50Gig data using in-memory buffers,
-Is there any limitations of IN-Memory Buffer size or we can process any size/number of data?
-If we process 50Gig data do we need to do any special settings or allot a space for IN-Memory buffer size?
Teradata Employee

Re: Use Teradata Parallel Transporter to Move Data Without Landing the Data to Disk

Teradata Parallel Transporter can process any size/number of data.

No special settings are needed. No space allotment is needed.

The TPT producer operator puts the data on the TPT data stream and the TPT consumer operator gets the data off the TPT data stream.

Re: Use Teradata Parallel Transporter to Move Data Without Landing the Data to Disk

we have mainframe channel attached environment, and we use JCL to connect to TD. We have two seperate Teradata systems: System A, and System B. if we wish to update System B table with System A table we usually run JCL with System A login info, and Fast Export scrip to extract data, and then run JCL with System B login info and run Multi-Load to laod into System B. I figured I cannot use named pipe or TPT via script embedded into JCL since login info have to be provided for both systems and in our environment we usually have one system login info in each JCL (export or Load). let me know if i am wrong... I think its the way Mainframe is setup with System A, and B that i can only connect with one system (as per JCL Job header info). Now there is a way, in my opinion we do have Datastage, so I am pretty sure we can do TPT with Datastage because it allows multiple login parameters. any suggestion/comments???
Teradata Employee

Re: Use Teradata Parallel Transporter to Move Data Without Landing the Data to Disk

You can embed a TPT job script in the JCL. Let me know if you need a sample JCL with the TPT job script embedded.

You can specify login info for System A and System B in a TPT job variable file. See the sample job variable file at the end of this article. In the sample job variable file, the SourceTdpId, SourceUserName, and SourceUserPassword can be the login info for System A. The TargetTdpId, TargetUserName, and TargetUserPassword can be the login info for System B.

In the TPT job script, you can change "TYPE LOAD" to "TYPE UPDATE" to use the Update operator. The TPT Update operator uses the MultiLoad protocol to load the data.

I'm not familiar with Datastage.

Re: Use Teradata Parallel Transporter to Move Data Without Landing the Data to Disk

Hi Tony,
Can yu please send me sample JCL wuth the TPT script embedded...My email Id jasondaniel.007@gmail.com
Teradata Employee

Re: Use Teradata Parallel Transporter to Move Data Without Landing the Data to Disk

Hi, Tony
FastExport the following columns may be used without definition.
SELECT * FROM $ {From_DB}. $ {Tbl_Name};
. END EXPORT;
But if your TPT sentence shall specify all the columns. You do not use it any other way do you have?
DESCRIPTION 'SAMPLE EMPLOYEE SCHEMA'
(
EMP_ID INTEGER,
EMP_NAME CHAR(10)
);
Teradata Employee

Re: Use Teradata Parallel Transporter to Move Data Without Landing the Data to Disk

Currently, TPT requires a schema definition with all of the columns listed. TPT needs to know the record layout in order to process the job.

Re: Use Teradata Parallel Transporter to Move Data Without Landing the Data to Disk

Great article - almost there; I want to copy table from server A to server B. The table does not exist on server B. I want TPT to create table structure that matches server A on server B, and infer the structure when copying data to server B. How do you do this with above technique?
Teradata Employee

Re: Use Teradata Parallel Transporter to Move Data Without Landing the Data to Disk

TPT is a load tool with similar capabilities as the standalone legacy utilities (i.e. FastLoad, MultiLoad, TPump) in that the user is responsible for setting up the tables prior to loading the data.

TPT provides that capability in its script language through the use of the DDL operator.

What you are looking for is basically a table copy (or table move) and the Teradata Data Mover tool is built for that type of scenario.

-- SteveF