Using TPT Easy Loader to move data within the database environment

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.
mvu
Teradata Employee

Using TPT Easy Loader to move data within the database environment

If you’re waiting for an easy way to load data from one or more Teradata table(s) into a Teradata table without writing a Teradata PT script, wait no further.  Teradata PT Easy Loader can do it easily.  In the 14.0 release, the tool can load data from a Teradata table or from SELECT statement(s).

You can enter the necessary options on the command line; but, this article will mainly show you how to specify them in a job variable file.  Mistakes can be easily made while typing a long command with many options and values. Plus, it can be easier to modify a job variable file to accommodate different load jobs when needed.

After you create a job variable file, you can execute the simple command with the option -j:

tdload  -j job-variable-file-name

If you are not executing the tdload command in the directory where the job variable file is located, job-variable-file-name must be a full qualified filename.

You can use any text editor to create a job variable file that contains all the necessary options and their corresponding values to be used with Teradata PT Easy Loader.  Each job variable must be defined on a single line separated by commas, using the following format:

option = value [,]

To load data from a table, Teradata PT Easy Loader must know:

  • Teradata Database source table information, e.g. table name, user account and Teradata server name
  • Teradata Database target table information, e.g. table name, user account and Teradata server name

Such information can be defined in a job variable file using the following options:

SourceTable                = ‘sourceTableName’,
SourceTdpId = ‘sourceTdpid’,
SourceUserName = ‘sourceUserName’,
SourceUserPassword = ‘sourcePassword’,
TargetTable = ‘targetTableName’,
TargetTdpId = ‘targetTdpid’,
TargetUserName = ‘targetUserName’,
TargetUserPassword = ‘targetPassword’

If the source table resides in a different database than the default database of the specified Teradata user, you must add the SourceWorkingDatabase option to the file, as follows:

SourceWorkingDatabase   = ‘sourceWorkingDatabase’

If the target table resides in a different database than the default database of the specified Teradata user, you must add the TargetWorkingDatabase option to the file, as follows:

TargetWorkingDatabase   = ‘targetWorkingDatabase’

If the source data comes from multiple tables as the result of one or more SELECT statements, the job variable file should, at least, have the following:

SelectStmt  = ‘selectRequest’

The two options SourceTable and SelectStmt cannot be used simultaneously. Teradata PT Easy Loader will not terminate if both options are specified; instead, it will ignore the option SourceTable and extract data based on the value specified in the option SelectStmt.

There are a few restrictions on the SELECT request specified in the option SelectStmt. The SELECT statement cannot:

  • Specify a USING modifier
  • Access non-data tables. For example, SELECT DATE; is invalid statement.

    However, this is valid: 
    SELECT DATE, col1, col2, col3 FROM source_table;

    In another words, the SELECT statement must have a FROM clause.
  • Contain BLOB (Binary Large Object) or CLOB (Character Large Object) data type.

Since the SELECT statement can have space/blank characters, the entire statement must be enclosed in double quotation marks (") when specified on the command line, as follows:

tdload --SelectStmt  "select * from src_tbl;"

If you prefer to specify all options on the command line, the following command will load data from a Teradata Database source table:

tdload --SourceTable sourceTableName --SourceTdpid sourceTdpid --SourceUserName sourceUserName --SourceUserPassword sourcePassword --TargetTable targetTableName --TargetUserName targetUserName --TargetUserPassword targetPassword --TargetTdpid targetTdpid
 

The following command will load data from a SELECT statement:

tdload --SelectStmt “sel * from src_tbl;” --SourceTdpid sourceTdpid --SourceUserName sourceUserName --SourceUserPassword sourcePassword --TargetTable targetTableName --TargetUserName targetUserName --TargetUserPassword targetPassword -TargetTdpid targetTdpid
 

Since Teradata PT Easy Loader offers short options (-h, -u, and -t) to define a Teradata Database target table, you can enter the following command:

tdload --SelectStmt “sel * from src_tbl;” --SourceTdpid sourceTdpid --SourceUserName sourceUserName --SourceUserPassword sourcePassword -t targetTableName -u targetUserName -p targetPassword -h targetTdpid
 

1 REPLY

Re: Using TPT Easy Loader to move data within the database environment

Hi,

How to mention different File Names in Job variable file in a single TDload command!.

I believe we can load more than 2 files into a single table using Wildcard character. I am unable to identify the character.

I have 2 files, say  S01.txt and S02.txt..

what is the format that we need to declare in the "Filename" variable.