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
 
Discussion
jammy19031989 9 comments Joined 07/13
12 Jan 2016

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.
 

You must sign in to leave a comment.