Syntax - Parallel Transporter

Teradata® Parallel Transporter Reference

Product
Parallel Transporter
Release Number
17.10
Published
February 2022
Language
English (United States)
Last Update
2023-11-29
dita:mapPath
abr1608578396711.ditamap
dita:ditavalPath
obe1474387269547.ditaval
dita:id
ogv1478610452101
Product Category
Teradata Tools and Utilities

tdload has the following syntax:



where jobOptions are:

Syntax Element Description
AutoPickSessInstance ON/OFF Tells TPT Easy Loader to pick the session/instance count automatically. The default value of this option is OFF.

For more details, refer to "Picking Sessions and Instances Automatically in Teradata PT Easy Loader" in Teradata® Parallel Transporter User Guide, B035-2445.

-c characterSet The character set encoding of the flat file. This option sets the client session character set.

Specify this option if the flat file is not an ASCII file.

-d "delimiterCharacter" The delimiter character used to separate the fields of the data records in the delimited format flat file.
The default delimiter character is a comma ('',''). This is different from the usage in Teradata PT scripts where the default delimiter character is the pipe character (''|''). You must specify this option if the delimiter character in your flat file is not a comma.
-f fileName Required. The name of the flat file containing the data to be loaded.

If you are not executing the tdload command in the directory where the flat file is stored, fileName must be a fully qualified flat file name.

  • The -f (or --SourceFileName) option and the --SourceTable option are mutually exclusive.

    An error is returned if both of these options are specified on the tdload command line.

  • The -f (or --SourceFileName) option and the --SelectStmt option are mutually exclusive.

    An error is returned if both of these options are specified on the tdload command line.

-h tdpId The name by which the Teradata Data Warehouse Appliance 2xxx is known to the network.

If the option is not specified, the default host name will be used.

Recommendation: Specify this option to make sure you are connecting to the correct system.

-I ConfigurationFileName Specifies a desired configuration file when it's not possible to access the following files:
  • The global configuration file twbcfg.ini, located under TPT install directory
  • The local configuration file .twbcfg.ini, located under the user's home directory
The following global parameters can be defined in a user-specified configuration file:
  • GlobalAttributeFile
  • CheckpointDirectory
  • LogDirectory
These parameter definitions must be specified using the following syntax:
<parameter> = <single-quoted string>
For example, on a Unix system:
CheckpointDirectory='/opt/teradata/client/16.20/tbuild/checkpoint'
LogDirectory='/opt/teradata/client/16.20/tbuild/logs'
The -I option is only supported on Windows and Unix platforms.
-j jobVariablesFile The name of the job variables file.

If you are not executing the tdload command in the directory where the job variables file is stored, jobVariablesFile must be a fully qualified filename.

The job variables file may be saved in ASCII, UTF-8, or UTF-16, both with and without a UTF byte order mark.

-L jobLogDirectory Option that designates the location of the Teradata PT files created during job execution.

jobLogDirectory is the full path name of the directory in which the Teradata PT files are stored.

This option is not supported on z/OS.

-p password The password of the specified user.

If the option is not specified, tdload prompts you for a password.

- R restartLimit Option that overrides the default value of five tries at automatic (job) restart.

If you specify -R, enter a value or the system will reject the command and return an error.

The restartLimit value can be any whole number greater than zero or equal to zero. The value zero prevents automatic job restart.

- r checkpointDirectory Option that specifies that checkpoint files are to be stored in a directory called “CheckpointDirectory.”

If the -r option is not specified, then checkpoint files will be stored in the default checkpoint directory that is defined in the Teradata PT configuration file.

-S Saves the Teradata PT script generated by the command.
-t tableName Required. The name of the target table.
If the target table resides in a database that is different from the default database of the specified user, you must also use the --TargetWorkingDatabase option.
-u userName Required. The logon id of the user with access privileges to the target table.
-v Option that displays the version number of tdload without running a job.

Do not use with any other option. The option works only on UNIX and Windows platforms.

-w restartWaitPeriod Specifies a time interval, in seconds, between two restarts.

If this option is not specified, there will be no wait period between auto restarts.

The valid restart wait period value must be a positive integer between 1 and 86400 (seconds).

-x Enables debugging.
- z checkpointInterval Option that specifies a time interval in seconds between checkpoints.

If this option is not specified, there will be no interval checkpointing.

The valid checkpoint interval values are between 0 and 86400 (seconds).

--help Displays help.
--DefaultStagingTable The --DefaultStagingTable option tells TPT Easy Loader to create a NOPI staging table and load data to the staging table instead of the target table. The staging table will be created on the target database.

The name of the staging table will be:

<target table>_STG

When data is coming from a file, TPT Easy Loader obtains the table definition from the target table, convert the non-VARCHAR columns to equivalent VARCHAR columns, and create the staging table which comprises all VARCHAR columns.

This will allow data that might otherwise cause issues and/or errors, like data conversion errors, to be loaded into the VARCHAR columns in the staging table.

After the data is loaded into the staging table, the user can create the necessary INSERT-SELECT statement to move the data from the staging table to the target table.

When data is from a source table, TPT Easy Loader obtains the table definition from the source table, convert the non-VARCHAR columns to equivalent VARCHAR columns, and create the staging table which comprises all VARCHAR columns.

When data is from a SELECT request of a source table, TPT Easy Loader obtains the schema of the SELECT request, convert the non-VARCHAR columns to equivalent VARCHAR columns, and create the staging table which comprises all VARCHAR columns.

  • TPT Easy Loader will use Load operator to load to the staging table, because Load operator uses the fastest protocol (FastLoad) to load data into an empty table.
  • If the total length of the target table and the "_STG" suffix exceeds the database maximum size for a table name, TPT Easy Loader will truncate the target table name and add the "_STG" suffix, so that the resulting length of the staging table does not exceed the database maximum size for a table name.
  • The staging table must not exist. If the staging table already exists, TPT Easy Loader will terminate the job with an error message.
  • The user can directly load to the target table without loading to the staging table by not specifying the --DefaultStagingTable option.
  • The --StagingTable and --DefaultStagingTable options are mutually exclusive. TPT Easy Loader will terminate the job with an error message if both options are specified.
  • The --DefaultStagingTable option cannot be specified in the job variable file. It can only be specified on the command line. A job variable file requires a value and the --DefaultStagingTable option does not have a value.
--NoLoadSlot This option tells TPT Easy Loader not to use a load slot. It implies this on both source and target machines if there are 2 of them; otherwise, just the source or just the target.

This option can be overridden by specifying "Load/Update/Export/Stream" prefixes to any of the job variables.

The --NoLoadSlot, --StagingTable, and --DefaultStagingTable options are mutually exclusive. TPT Easy Loader will terminate the job with the following error message if these options are specified:
TPT_INFRA: TPT05565: Error: Option --NoLoadSlot is mutually exclusive with --StagingTable and --DefaultStagingTable.
The --NoLoadSlot option cannot be specified in the job variable file. It can only be specified on the command line. A job variable file requires a value and the --NoLoadSlot option does not have a value.
<long_option> value Easy Loader supports all standard job variables which are defined within the operator templates as well as generic job variables. These generic job variables are derived from operator templates and allow Easy Loader to automatically make operator decisions. To construct a generic job variable, simply remove the operator name from the template job variable, and add ‘Source’ or ‘Target’ depending on if the operator is a producer or consumer, respectively.

For example, the trace level job variable for the Load operator is “LoadTraceLevel”, to make this generic, specify it as “TargetTraceLevel”. If Load is the consumer operator chosen by Easy Loader, “TargetTraceLevel” will be automatically mapped to the load operator.

See the Teradata PT Easy Loader section in Teradata® Parallel Transporter User Guide, B035-2445 for more examples and instructions.

Where commonly used long options are:

Long Option Description
--InsertStmt "INSERT statement;" The Insert statement to be used for the loader operator.

tdload --InsertStmt "INSERT <table> (:COL1, :COL2);"

This option allows for the data coming from the producer to be sent selectively and in a different order than how it is defined in the source schema. Otherwise, the $INSERT macro is used, and the schema of the source table must then need to be compatible with the schema of the target table.

--SelectStmt “SELECT statements;” SELECT statement performs data selection from database tables.

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

tdload --SelectStmt "sel * from src_tbl;"

SELECT statement requests cannot:
  • Specify a USING modifier.
  • Access non-data tables, such as SELECT DATE or SELECT USER.
  • Be satisfied by one or two AMPs, such as a SELECT statement that accesses rows based on the primary index or unique secondary index of a table.
  • Contain BLOB (Binary Large Object) or CLOB (Character Large Object) data types.
  • Contain JSON (JavaScript Object Notation) data type.
  • Contain XML data type.

The --SelectStmt option and the -f (or --SourceFileName) option are mutually exclusive. An error is returned if both of these are define on the tdload command line.

If both the --SelectStmt option and the -- SourceTable option are defined, the --SourceTable option is ignored and a warning message is returned to the console.

--SourceAccountId accountId The account associated with the specified user.
--SourceConnectString "connectionString" Specifies the connection string when extracting data from tables. The connection string will be passed to CLI. CLI will validate the connection string.

For information on connection string, see Teradata® Call-Level Interface Version 2 Reference for Workstation-Attached Systems, B035-2418.

--SourceDataEncryption On | Off Enables full encryption of SQL requests, responses, and data when extracting data from tables.
Valid values are:
  • On = All SQL requests, responses, and data are encrypted.
  • Off = No encryption occurs (default).
This option does not include logon encryption.
--SourceFileName fileName Required. The name of the flat file that contains the data to be loaded.
  • The --SourceFileName (or -f) option and the --SourceTable option are mutually exclusive.

    An error is returned if both of these options are specified on the tdload command line.

  • The --SourceFileName (or -f) option and the --SelectStmt option are mutually exclusive.

    An error is returned if both of these options are specified on the tdload command line.

--SourceInstances number The number of instances used to extract data from the database.

The default value is 1.

--SourceMaxSessions number The maximum sessions to be used in extracting data from tables.

The default value is 32.

--SourceMinSessions number The minimum sessions to be used in extracting data from tables.

The default value is 1.

--SourceTable tableName The name of the source table.

Note: If the source table resides in a database that is different from the default database of the specified user, you must also use the --SourceWorkingDatabase option.

The option --SourceTable and the -f (or --SourceFileName) option are mutually exclusive. An error is returned if both of them are specified on the tdload command line.

If both the --SourceTable option and the --SelectStmt option are defined, the option --SourceTable is ignored and a warning message is returned to the console.

--SourceTextDelimiter "delimiterCharacter" The delimited character used to separate the fields of the data records in the delimited format flat file.
--SourceTdpId tdpId The name by which the database is known to the network.
--SourceTraceLevel value Enables trace messages in the operator that reads data from flat files.
Valid values are:
  • 'Yes' = tdload sets the value 'all' to the attribute 'TraceLevel' of the appropriate operator.
  • 'No' = TraceLevel is turned off (default).
--SourceUserName userName The logon id of the user with access privileges to the source table.
--SourceUserPassword password The password of the specified user.

If the option is not specified, tdload will prompt you for a password.

--SourceWorkingDatabase databaseName The database where the source table is located.
--StagingTable stagingTableName The --StagingTable option tells TPT Easy Loader to create a NOPI staging table with the given <staging table name> and load data to the staging table instead of the target table.

The staging table will be created on the target database.

When data is coming from a file, TPT Easy Loader obtains the table definition from the target table, convert the non-VARCHAR columns to equivalent VARCHAR columns, and create the staging table which comprises all VARCHAR columns.

This will allow data that might otherwise cause issues or errors, like data conversion errors, to be loaded into the VARCHAR columns in the staging table.

After the data is loaded into the staging table, the user can create the necessary INSERT-SELECT statement to move the data from the staging table to the target table.

When data is from a source table, TPT Easy Loader obtains the table definition from the source table, converts the non-VARCHAR columns to equivalent VARCHAR columns, and creates the staging table which will comprise all VARCHAR columns.

When data is from a SELECT request of a source table, TPT Easy Loader will obtain the schema of the SELECT request, convert the non-VARCHAR columns to equivalent VARCHAR columns, and create the staging table which will comprise all VARCHAR columns.

  • TPT Easy Loader will use Load operator to load to the staging table, because Load operator uses the fastest protocol (FastLoad) to load data into an empty table.
  • The staging table must not exist. If the staging table already exists, TPT Easy Loader will terminate the job with an error message.
  • The staging table can be qualified with a database name.
  • The user can directly load to the target table without loading to the staging table by not specifying the --StagingTable option.
  • The --StagingTable and --DefaultStagingTable options are mutually exclusive. TPT Easy Loader will terminate the job with an error message if both options are specified.
  • The --StagingTable option can be specified in the job variable file as follows:

    StagingTable = 'tablename'

    If you specify a value for the --StagingTable command line option and a value for --StagingTable option in the job variable file, the value on the command line option takes precedence.

--SharedMemorySize

The --SharedMemorySize option allows user to specify a desired amount of shared memory to be allocated for a running job. The value provided is then used in the option (-h) of the tbuild command.

Teradata PT Easy Loader does not perform any error checking for the SharedMemorySize value; the value provided to tdload command is passed directly to tbuild command as is.
--TargetAccountId accountId The account associated with the specified user.
--TargetConnectString "connectionString" Specifies the connection string in the load job. The connection string will be passed to CLI. CLI will validate the connection string.

For information on connection string, see Teradata® Call-Level Interface Version 2 Reference for Workstation-Attached Systems, B035-2418.

--SourceDataEncryption On | Off Enables full encryption of SQL requests, responses, and data when extracting data from tables.
Valid values are:
  • On = All SQL requests, responses, and data are encrypted.
  • Off = No encryption occurs (default).
This option does not include logon encryption.
--TargetErrorLimit number The maximum errors allowed in data records.

When the number of errors encountered exceeds this number, the load job terminates.

The default value is 1.

--TargetMaxSessions number The maximum sessions to be used in the load job.

The default value is 32.

--TargetMinSessions number The minimum sessions to be used in the load job.

The default value is 1.

--TargetTable tableName Required. The name of the target table.
--TargetTdpId tdpId The name by which the database is known to the network.
--TargetTraceLevel value Enables trace messages in the operator that loads data to the target table.
Valid values are:
  • 'Yes' = tdload sets the value 'all' to the attribute TraceLevel of the appropriate operator.
  • 'No' = TraceLevel is turned off (default).
--TargetUserName userName Required. The logon id of the user with access privileges to the target table.
--TargetUserPassword password The password of the specified user.
--TargetWorkingDatabase databaseName The database containing the target table.
This option is required if the target table resides in a database that is different from the default database of the specified user.
“--< long_option> value” is any of the generic or operator template job variables. For more information on usage, see the Teradata PT Easy Loader section of Teradata® Parallel Transporter User Guide, B035-2445.

where:

Option Specifies
jobName A unique name that identifies the load job.

Recommendation: Teradata strongly recommends that you specify names for your jobs when multiple load jobs are running simultaneously.