Command Syntax - Parallel Transporter

Teradata Parallel Transporter Reference

Product
Parallel Transporter
Release Number
15.10
Language
English (United States)
Last Update
2018-10-07
dita:id
B035-2436
lifecycle
previous
Product Category
Teradata Tools and Utilities

The following sections provide the syntax and a brief description of the options for each Teradata PT utility command.

tbuild

Purpose  

The tbuild command defines and executes Teradata PT job scripts. The job scripts must conform to Teradata PT syntax rules.

Syntax

The tbuild command takes as its primary argument the file containing the Teradata PT job script. You can specify tbuild options in any order.

where:

 

Syntax Element

Description

-C

Option that provides a more even distribution of data to consumer operators by instructing producer operators and their underlying data streams to ship data blocks to target consumer operators in a cyclical or round-robin manner.

By default, Teradata PT data streams send a block of data to the first ready/available consumer operator found in the producer's channel set array, only switching processes when the first consumer operator is too busy to accept more input. Using the -C option ensures that data are evenly distributed among consumer operators.

-d

Option that enables debug trace functions for all tasks.

Using this option outputs trace messages and return codes of all internal functions invoked on behalf of tbuild. Internal error condition codes and trace messages are usually helpful for debugging issues occurring at the infrastructure level. However, when using this option along with the trace options provided by the operators, a full trace of the job can be obtained.

If this option is not specified, the debug trace function is disabled.

-e characterSet

Option that specifies the character set encoding of the script. This option also ensures that files in big endian and little endian format are processed correctly regardless of the platform’s native encoding.

If not specified, the default character set is 7-bit ASCII compatible on UNIX OS, Windows and EBCDIC on z/OS.

-e characterSet is required if the job script is encoded in UTF-16.

All values are case insensitive and may be used with or without hyphens, that is, UTF-16, UTF16, utf16-be, and any similar notations are all valid. The following values are valid for characterSet:

  • UTF-16
  • (Preferred notation) Endianess defaults to that of the client platform

  • UTF-8
  • (Preferred notation) Specifying UTF-8 is optional. Specifying UTF-8 for a non-UTF-8 script generates an error. Endianess is irrelevant for UTF-8 encoding.

  • UTF-16LE
  • (Preferred notation) Specifies a job script with little-endian encoding. If executed on a big endian platform, script encoding is converted to big-endian encoding before being executed. Not permitted as a client session character set name.

  • UTF-16BE
  • (Preferred notation) Specifies a job script with big-endian encoding. If executed on a little-endian platform, script encoding is converted to little-endian encoding before being executed. Not permitted as a client character set name.

    -f fileName

    Option that specifies the Teradata PT job script file.

    -h value

    -h valueK

    -h valueM

    Option that specifies the size of the shared memory used among the processes of a Teradata PT job.

    Options include:

  • -h value, where value specifies the shared memory size in bytes and can range from 1, 048, 576 bytes to 134, 217, 728 bytes.
  • -h valueK, where valueK specifies the shared memory size in kilobytes and can range from 1024 K (1, 048, 576 bytes) to 131, 072 K (134, 217, 728 bytes).
  • -h valueM, where valueM specifies the shared memory size in megabytes and can range from 1M (1, 048, 576 bytes) to 128 M (134, 217, 728 bytes).
  • For example,

    tbuild -h 9700K

    If the -h option is not specified, Teradata PT allocates 10,000,000 bytes of shared memory.

    If the -h option is invalid, a warning message is issued, and Teradata PT allocates the default memory size of 10,000,000 bytes. The following message is issued if the -h option is invalid:

  • Message: The -h memory size is invalid, and will be ignored.
  • Type: Warning

    Explanation: The memory size value is not numeric.

    Remedy: Correct the -h option value.

    If the value of the -h option falls outside of the allowed range, a warning is issued and the size is adjusted to the appropriate minimum or maximum value before allocation. The following messages are issued if the -h option is out of the allowed range:

  • Message: The -h memory size exceeds the maximum allowed value, and will be lowered to 134217728.
  • Type: Warning

    Explanation: The memory size value exceeds the maximum allowed value.

    Remedy: Correct the -h option value.

  • Message: The -h memory size is less than the minimum allowed value, and will be raised to 1048576.
  • Type: Warning

    Explanation: The memory size value is less than the minimum allowed value.

    Remedy: Correct the -h option value.

    ‑j jobName

    Optional job name, but strongly recommended so each job can have a unique checkpoint file. The default, if you do not enter a jobName, is the user name followed by a hyphen (“-”) and a generated Teradata PT job sequence number as follows:

    <user name>-<job sequence number>

    Note: If multiple jobs are to be simultaneously run under the same logon session, specify a unique name for each job because each job must write its own checkpoint file. Without a unique name, concurrent jobs try to write to the same checkpoint file. This causes the jobs to fail.

    -l latencyInterval

    Optional latency interval, specified in seconds, for flushing stale buffers. Latency interval is used exclusively with the Stream operator.

    If no value is specified, data is read from the data stream until its buffer is full. Then all buffered records are written to the Teradata Database.

    -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.

    -m statisticsInterval

    Option that specifies a time interval, in seconds, in which to collect statistical information about operators.

    If this option is not specified, no statistical information is collected.

    -n

    Option that specifies that the job can continue to run even if a job step returns an error.

  • If a job step receives a non-zero exit code, the job will continue.
  • If a step exits with a status of “failure” (exit code higher than 4), the subsequent steps will bypass the checkpoint file left by the previously failed step.
  • The required syntax is:

    tbuild -f <scriptFile> -n

    If this option is not specified, the job will stop if a step fails.

    -o

    Option that writes the consumer private logs to standard output after a job completes.

    If the -o option is not specified, logs will not be written to standard output.

    -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.

    -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.

    -s jobstepIdentifier

    Option that directs job execution to start at the specified job step.

    Job steps are identified by a job step name in a script or by an implicit job step number that corresponds to the physical order of job steps in a script, such as 1, 2, 3.

    The job will start at the specified job step, skipping over all job steps that come before it in the job script.

    This option also removes checkpoint files:

  • used by any skipped steps
  • used by a previous execution of the same job
  • that would have been carried forward because of a failure or premature termination of one or more of the preceding steps
  • Since checkpoint files subject to these conditions are not valid for subsequent steps, the system will remove them before starting the step specified for the -s option.

    Note: The -s option formerly supported silent mode. Silent mode is no long available.

    -S DD:ddname

    -S dsName

    -S sysoutClass

    Option that enables tbuild to write both the private and public logs to a specified location for z/OS platforms. Choose from the following logDestination options:

  • To specify a ddname: tbuild -S logDestination
  • where logDestination is DD:<ddname>, the target ddname for the log file.

  • To specify a dsname: tbuild -S logDestination
  • where logDestination is <dsname>, the target dataset name for the logfile.

    Note: A fully qualified dataset name is indicated by enclosing the name in single quote marks.

  • To specify a SYSOUT class: tbuild -S logDestination
  • where logDestination is <sysout class>, the SYSOUT class for the log file.

    If the -S option is not specified, tbuild does not write the private or public log to a specified location. However, tbuild still writes the logs to the dataset referenced by the JOBLOG DD statement.

    -t

    Option that enables the trace option for all tasks.

    If this option is not specified, trace is disabled.

    -u jobVariables

    Option that allows you to specify job variable values on the command line.

    Use this option to specify job variable assignments on the command line for the current execution of the job script.

    Note: There are multiple sources of job variable assignments within a job script, which are invoked in a priority order hierarchy. For more information, see “Using Job Variables” and related topics in the Teradata Parallel Transporter User Guide.

    In the following example, UsrID and Pwd are defined as job variables and they are used to supply runtime values for the UserName and UserPassword attributes:

    ATTRIBUTES
    (
       VARCHAR UserName = @UsrID,
       VARCHAR UserPassword = @Pwd
    );

    The following command supplies runtime values for these job variables:

    tbuild -f scriptFileName -u "UsrID = 'John Doe', Pwd = 'ABC123' "

    -v jobVariablesFile

    Option that allows job variable values to be specified in an external file.

    This is similar to the -u option for assigning values at run time to job variables specified in a script, except that the variable values are stored in an external file that is referenced by tbuild through the following command:

    tbuild -f scriptFileName -v jobVariablesFile

    Where jobVariableFile is a file which contains the values for substitution:

    UsrID = 'John Doe',
    Pwd = 'ABC123'

    -V

    Option that displays the Teradata PT version number without running a job.

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

    -z checkpointInterval

    Option that specifies a time interval, in seconds, between checkpoints.

    If this option is not specified, there will be no interval checkpointing, unless a checkpoint interval is specified in the job script.

    If a checkpoint interval is specified in both places, the -z option specification takes precedence.

    tdload

    Purpose  

    The Teradata PT Easy Loader command, tdload, loads data into a Teradata Database table from a comma-delimited flat file or another Teradata Database table without requiring a Teradata PT job script to be written.

    Syntax

    tdload has the following syntax:

    where jobOptions are:

     

    Syntax Element

    Description

    -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.

    Note: 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.

    -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.

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

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

    -u userName

    Required. The logon id of the Teradata 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.

    -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.

    --FileInstances number

    The number of instances used to read the data file.

    The default value is 1.

    --help

    Displays help.

    --LoadInstances number

    The number of instances used to load data.

    The default value is 1.

    --SelectStmt “SELECT statement(s);”

    SELECT statement performs data selection from Teradata 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:

    tload --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 Teradata user.

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

    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 Teradata 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 Teradata Database is known to the network.

    --SourceTraceLevel value

    Enables trace messages in the operator that reads data from flat file(s).

    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 Teradata user with access privileges to the source table.

    --SourceUserPassword password

    The password of the specified Teradata user.

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

    --SourceWorkingDatabase databaseName

    The database where the source table is located.

    --TargetAccountId accountId

    The account associated with the specified Teradata user.

    --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 Teradata 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 Teradata user with access privileges to the target table.

    --TargetUserPassword password

    The password of the specified Teradata user.

    --TargetWorkingDatabase databaseName

    The database containing the target table.

    Note: This option is required if the target table resides in a database that is different from the default database of the specified Teradata user.

    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.

    tdload and the Teradata PT Protocols

    The tdload command has the intelligence to determine the most appropriate load operator (for example, Load, Update, Stream or Inserter) or export operator (for example, Export) to be used for the Easy Loader job based on the status of the target table. For example,

  • If the target table is empty and has no secondary indexes, tdload knows to use the Load operator to load data.
  • If the target table is not empty, tdload may use the Update operator to load data.
  • If a value is specified for either the --SourceTable option or --SelectStmt option, tdload knows to use the Export operator to extract data from Teradata table(s).
  • Note: Any attributes not supported as part of the allowed set of tdload command line options (specified above) must be specified in the job variables file. Because tdload internally calls tbuild, these attributes get passed as part of the job variables file to the tbuild command.

    If values are specified for both the --SourceTable option and the --SelectStmt option, tdload ignores the value of --SourceTable option. A warning message is returned to the console and the job continues with the schema inferred from the SELECT statement.

    Defining a Flat File Source Table

    To specify a flat file source table for tdload, specify either the -f option or the --SourceFileName option.

    The following combination of options are mutually exclusive:

  • -f and --SourceTable
  • -f and --SelectStmt
  • An error is returned if either one of these combinations is specified on the tdload command line or in the job variables file.

    Defining a Teradata Database Source Table

    To specify a Teradata Database source table for tdload, you must specify either the --SourceTable option or the --SelectStmt option and the following job options on the command line or in the job variables file:

  • --SourceTdpId
  • --SourceUserName
  • --SourceUserPassword
  • You are not required to specify any of the following job options (either on the command line or in a the job variables file) when defining a Teradata Database data source:

  • --SourceAccountId
  • --SourceWorkingDatabase
  • --SourceMaxSessions
  • --SourceMinSessions
  • --SourceInstances
  • Note: If both the SourceTable option and the --SelectStmt option are defined, the --SourceTable option is ignored and a warning message is returned to the console.

    Inferring the Data Schema

  • When moving data from a flat file to a Teradata Database table, the data schema is inferred from the target table.
  • When moving data between Teradata tables, the data schema is inferred from either:
  • The Source table specified in the --SourceTable option, or
  • The SQL SELECT statement defined in the --SelectStmt option.
  • An error is returned if no target table is specified.

    If the source data does not match the inferred schema, the Easy Loader job terminates with an error.

    tdload and Checkpoint Files

    Teradata PT automatically deletes the checkpoint files of successful jobs, but retains the checkpoint files of unsuccessful jobs. If a previously run job is unsuccessful, you can either let the checkpoint files be reused by that failed job or delete them so they are not picked up by any other job executed under the same login name.

    To avoid problems, you must either carefully maintain the checkpoint files of unsuccessful jobs, or, preferably, always specify a job name using the jobname option.

    tdlog

    Purpose  

    The tdlog command is used to extract the logs produced by a running job.

    Syntax

    The Teradata PT command, tdlog, has the following syntax:

    where:

     

    Syntax Element

    Description

    jobId

    Unique job identifier. This value is provided in the console output generated by the tdload command. The tdlog command tdlog jobId is equivalent to tlogview -j jobId -f "*" -g.

    Either command generates the contents of all private logs. Also, the purpose of the command is to obtain the contents of the log of running and terminated jobs.

    tlogview

    Purpose  

    The tlogview command displays the contents of the log files produced from running a Teradata PT job. All logs contain a banner that displays the version number of the active Teradata Database for diagnostic purposes.

    Syntax

    The order of the options is not important.

    where:

     

    Syntax Element

    Description

    -e characterSet

    Option to specify that the tlogview output, including tlogview error messages, will be displayed in UTF-16.

    The only valid value for characterSet is UTF16 (no hyphen).

    If the -e characterSet is not specified, tlogview defaults to displaying in UTF-8.

    -f "*"

    Option that requests the output of the public log and all private logs.

    -f "*"-g

    Option that sorts the output of each private log separately, ordered by logname. The -g attribute must be used with "*", as shown in the following example:

    tlogview –j JobID –f  "*" –g 

    This command gets all the public and private logs, grouped by log name.

    Note: If the TraceLevel attribute is operational, the sort is done by operator instance.

    -f TWB_STATUS

    Option that identifies metadata, query job statistics, and status.

    If this option is not specified, no metadata, statistics, or status are identified.

    -f TWB _SRCTGT

    Option that identifies metadata, the query source, and target information.

    If this option is not specified, no metadata, source, or target are identified.

    -h

    Option that requests the job start time.

    If this option is specified, tlogview prints the job start time as the first line on the output.

    -j jobId

    Option that identifies the log output from a running or terminated Teradata PT job.

    Use this option when viewing the log identified by jobId. The job can be running or terminated.

  • When the -W option is not used with this option for a running job, tlogview displays the log information produced to that point and terminates.
  • When the -W option is used with this option, tlogview displays the log information produced to that point, then it waits for more log information to be produced by the job, terminating when the target job terminates.
  • If the job is already terminated, tlogview displays the log information and terminates. In general, always use the -j option unless you move or rename the log file. In this case, use the -l option and specify the new location.

    The -j and -l options are mutually exclusive.

    -l logFileName

    Option that identifies a physical log file.

    Use this option to view the log identified by a physical file name from a Teradata PT job. Log files are usually named with extensions of .out.

    The -j and -l options are mutually exclusive.

    -L logFilePath

    Option that helps specify the path to the physical log file.

    If this option is not specified, then tlogview will look for the physical log file in the TPT logs directory, by default.

    -f logNameList

    Option that identifies a “from list,” which describes what log reports to view.

    The default, if nothing is specified, is to view only public log reports. If both private and public log reports are requested, the “from list” must include the term “public” in the list. Listed items must be separated by commas, as shown in the following example:

    tlogview –j jobID –f  PUBLIC,privateLog1,privateLog2 

    This command gets all the public logs and private logs 1 and 2.

    -o outputFileName

    Option that designates a specific output file.

    If this option is specified, tlogview writes the output to the specified file. If this option is not specified, output is written to standard output.

    -p

    Option that lists the names of the private log reports contained in a target job log.

    -v logviewFormat

    Option that specifies the log view format.

    Use the following values to specify the output format of log messages:

  • %A for Task ID
  • %D for Date Time
  • %E for Destination
  • %G for Message Catalog
  • %L for Delta Time
  • %M for Message Text
  • %N for Node Name
  • %P for Process ID
  • %T for Task Name
  • %U for Message Number
  • For example, the following command displays log messages with time stamps, task names, and message texts separated by “-” characters:

       tlogview -j <jobID> -v "%D-%T-%M"

    -W

    Option used in conjunction with the -j jobId option to tell tlogview to continue displaying additional log information until the target job terminates.

    -w filterCriteria

    Option that specifies the filter criteria.

    The filter criteria allows tlogview to do filtering on the log messages. Only those log messages satisfying the filter criteria are output from tlogview.

    Without the -w option, tlogview selects all messages in the public and private logs.

    For example, the following command displays log messages where Task Name equals “SELECT_2[0001]”.

    tlogview -j <jobID> -w ‘TASKNAME=”SELECT_2[0001]”’

    twbcmd

    Purpose  

    There are two types of commands available through twbcmd, the External Command Interface.

  • Job-level commands: these can be used to:
  • Create checkpoints
  • Pause and then resume a job
  • Retrieve job status
  • Terminate a job
  • Enable job tracing at any time during job execution
  • Operator-level commands: these can be used to:
  • Set the statement rate for the Stream operator within the job
  • Set the periodicity for the Stream operator within a job
  • Syntax for Job-level Commands

    The following syntax is required for job-level commands using twbcmd.

    where:

     

    Syntax Element

    Description

    JOB CHECKPOINT

    Takes an immediate checkpoint, then continues the job.

    jobId

    jobId is the jobname followed by a “-”, then followed by the Teradata PT-generated sequence number. jobName specifies the name of the target job.

    - L jobLogDirectory

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

    This option is only applicable when - L jobLogDirectory is specified in the tbuild or tdload command. 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.

    JOB PAUSE

    Takes an immediate checkpoint, then suspends processing.

    JOB RESUME

    Resumes a paused job.

    JOB STATUS

    Writes a status record for each active operator instance to the TWB_STATUS log, and displays row processing statistics while continuing the job.

    JOB TERMINATE

    Takes an immediate checkpoint, then terminates the job. The job retains the checkpoint files, and is therefore restartable.

    JOB TRACELEVEL

    Enables job tracing at any time during job execution.

    One of the following arguments is required when enabling job tracing:

  • ALL
  • BASE
  • EXEC
  • NONE
  • ALL

    Enables both BASE and EXEC job tracing during job execution.

    BASE

    Enables job tracing of all job processes during job execution.

    EXEC

    Enables execution tracing of all operator processes during job execution.

    NONE

    Disables job tracing during job execution.

    Syntax for Operator-level Commands

    The following syntax is required for operator-level commands using twbcmd.

    where:

     

    Syntax Element

    Description

    jobId

    jobId is the jobName followed by a “-”, then followed by the Teradata PT-generated sequence number.

    jobName specifies the name of the job targeted by the command.

    operatorCommandId

    Specifies the identity of a Stream operator copy and allows changes to the Rate value for that copy only, while the job is running. When a job step contains multiple Stream operator copies with differing rate values, Teradata PT will automatically use the lowest rate value for all instances.

    Use the operatorCommandID to identify an operator copy and change the rate value.

    Note: To change the Rate value of a Stream operator copy, you must declare the operatorCommandID attribute in the Stream operator DEFINE OPERATOR statement.

    Use one of the following two methods to change the Rate:

  • Assign a value such as ‘step#1’ to the OperatorCommandID attribute in the Stream operator copy that requires the Rate change (in the APPLY statement). Then use the following command:
  • twbcmd <jobid> step#1 RATE=2000
  • If a value was NOT assigned for the OperatorCommandID attribute in the Stream operator copy that requires the Rate change, the system will generate a default ID composed of the operatorName and a process ID number. Process ID numbers for operators appear on the command screen when the job step that contains the operator begins to execute.
  • An example of a system-generated ID would be similar to the following: my_operator_name3456

    Use the following command to identify an operator by default ID and change the rate value:

    twbcmd <jobid> my_operator_name3456 RATE=2000

    rate=statementRate

    Option that specifies the maximum number of DML statements per minute the Stream operator can submit to the Teradata Database.

    Use the twbcmd Rate option to slow down a Teradata PT job for other higher priority jobs and to speed it up again after the priority job has completed.

    The statement rate can also be specified in a DEFINE OPERATOR statement, by using the Stream operator Rate attribute. When both values are present, the twbcmd rate value will supersede the Stream operator attribute Rate value.

    Note: When a job step contains multiple occurrences of the Stream operator with differing Rate values, Teradata PT will automatically use the lowest rate value for all instances.

    The specified Rate value must be either:

  • a whole number greater than zero
  • or,

  • unlimited
  • Note: The default statement rate, if not set using either the Stream operator Rate attribute or by twbcmd, is unlimited. Specifying ‘unlimited’ for the twbcmd Rate value means you are changing the value back to the default after having set the value in the Stream operator.

    When the twbcmd Rate option is used, the Stream operator changes the statement rate to the new value and displays a message showing the new value.

    If the specified rate is greater than the packing factor, the Stream operator will send the number of rows equal to the packing factor.

    periodicity=periodicity

    Option that specifies that the DML statements sent by the Stream operator to the Teradata Database be as evenly distributed as possible over each one minute interval. periodicity sets the number of sub-intervals per minute.

    For instance, if the rate is 1600 and the periodicity is 10, then the maximum number of statements submitted is 160 (1600/10) every 6 (60/10) seconds.

    Valid values are between 1 and 600.

    The default value is 4, that is, four 15 second intervals per minute.

    If the statement rate is unlimited, then periodicity will be ignored.

    Note: The periodicity can also be specified in a DEFINE OPERATOR statement, by using the Stream operator Periodicity attribute. When both values are present, periodicity will supersede the Stream operator Periodicity attribute value.

    Usage Notes  

    Use the following twbcmd syntax examples for commands on all platforms except z/OS.

  • For job-level commands:
  •    twbcmd <jobid> <command>

    where jobId is the identification of the target job, and command is the name of the external command that you want to execute.

  • For operator-level commands:
  •    twbcmd <jobid> <operator command Id> <attribute name>=<value>

    or, where more than one attribute is specified,

       twbcmd <jobid> <operator command Id> <attribute name>=<value>, <attribute name>=<value>

    where jobId is the name of the target job, operatorCommandId is “Stream” (the only operator that supports these commands), attributeName is the attribute you want to employ, and value is the specified rate or periodicity.

    On z/OS, use MODIFY syntax as follows.

  • For job-level commands:
  •    F <jobid>,APPL=<command>

    Quotation marks are not necessary around the command name. If quotes are present, they are sent as part of the command.

  • For operator-level commands:
  •    F <jobid>,APPL=<operator name> <attribute name>=<value>

    or, where more than one attribute is specified,

       F <jobid>,APPL=<operator name> <attribute name>=<value>, <attribute>=<value>

    twbertbl

    Purpose  

    The Teradata PT Error Table Extractor command, twbertbl, extracts the error information from the acquisition phase error table.

    The Teradata PT Error Table Extractor can be used to extract the error information to a data file. The user can correct the data in the data file and reload the data.

    Syntax

    where:

     

    Syntax Element

    Description

    -u UserName

    User name of the Teradata logon account.

    -p Password

    Password of the Teradata logon account.

    If omitted, the twbertbl tool will prompt you for the password on non-z/OS platforms.

    If omitted, the twbertbl tool will terminate with an error on z/OS platforms.

    The password is case sensitive

    -t ErrorTableName

    Name of the error table.

    The error table name can be fully qualified.

    -n NumberOfLoaded
    Columns

    Number of columns sent to the Teradata Database in the original load job.

    The -n option is required for the DELIMITED and TEXT record formats.

    The -n option is not required for the BINARY, FORMATTED, and UNFORMATTED record formats.

    -h TdpId

    TdpId of the Teradata Database.

    -a AccountId

    Account id of the Teradata logon account.

    -b DatabaseName

    Database name for the error table.

    If the database name is specified, the twbertbl tool will prepend the database name to the error table name.

    -c ClientSessionCharSet

    Client session character set.

    The default is 'ASCII' on non-z/OS platforms.

    The default is 'EBCDIC' on z/OS platforms.

    -o OutputFileName

    Name of the output file that stores the error table information.

    The default is <ErrorTableName>.txt.

    The output file name is case sensitive.

    -m MaxNumberOfRows
    ToProcess

    Maximum number of rows to write to the output file.

    The default is unlimited.

    -d DelimiterCharacter

    Single-byte character delimiter used to separate the fields in the output file.

    The default is the pip character '|'.

    The delimiter character is case sensitive.

    The -d option is applicable when the record format is DELIMITED.

    The -d option is not applicable when the record format is BINARY, FORMATTED, TEXT, or UNFORMATTED.

    -q QuotedFields

    Option to quote the fields in the output file.

    Value can be 'Y' or 'N'.

    The default is 'N'.

    If the value is 'Y', all fields will be quoted.

    The -q option is applicable when the record format is DELIMITED.

    The -q option is not applicable when the record format is BINARY, FORMATTED, TEXT, or UNFORMATTED.

    -e EndiannessOfLoad
    Job

    Endianness of the error data in the error table.

    Value can be 'BE' or 'LE'.

    'BE' means big-endian.

    'LE' means little-endian.

    If omitted, the twbertbl tool will attempt to determine the endianness of the error data in the error table.

    -r RecordFormat

    Record format of the error data in the error table.

    Value can be 'DELIMITED', 'BINARY', 'FORMATTED', 'TEXT', or 'UNFORMATTED.

    The default is 'DELIMITED'.

    -x TraceFile

    Name of the trace file for the twbertbl tool.

    By default, trace is off.

    The trace file name is case sensitive.

    Note: All values for the options are case insensitive unless otherwise specified.

    Usage Notes

    If the -q option is set to 'Y', all fields will be quoted. Here is an example of the output file:

    "EMP_NUM"|"2683"|"100"|""128""

    When the record format is BINARY, FORMATTED, TEXT, or UNFORMATTED, only the error data parcel or error host data will be extracted. The DBS error code, error field, import sequence number, DML sequence number, statement sequence number, apply sequence number, and row sequence number will not be extracted.

    Job Output Example

    The following is an example of the job’s output:

    ===================================================================
    =                                                                 =
    =                  TERADATA PARALLEL TRANSPORTER                  =
    =                                                                 =
    =            ERROR TABLE EXTRACTION VERSION 15.10.00.00           =
    =                                                                 =
    =   COPYRIGHT 2014, TERADATA CORPORATION. ALL RIGHTS RESERVED.    =
    =                                                                 =
    ===================================================================
     
    **** 13:04:26 Processing starting at: Fri Feb 22 13:04:26 2013
     
    ===================================================================
    =                                                                 =
    =                      Module Identification                      =
    =                                                                 =
    ===================================================================
    ERROR TABLE EXTRACTOR for Linux release 2.6.16.60-0.91.TDC.1.R.2-		smp on machine
    ErtblMain : 15.10.00.01
    ErtblCLI  : 15.10.00.01
    ErtblMsgs : 15.10.00.01
    ErtblUtil : 15.10.00.01
    PXICU     : 15.10.00.00
    TDICU     : 15.10.00.00
    CLIv2     : 15.10.00.00
     
    ===================================================================
    =                                                                 =
    =                Command Line Argument Information                =
    =                                                                 =
    ===================================================================
     
    **** 13:04:26 The user specified the following command line arguments:
     
    -h tdpid -u user -p <not displayable> -t SAMPLE_ERRTABLE1 -n 10
     
    ===================================================================
    =                                                                 =
    =                           Job Options                           =
    =                                                                 =
    ===================================================================
     
    **** 13:04:26 Options in effect for this job:
    Output file:            'SAMPLE_ERRTABLE1.txt'
    Output limit:           No limit in effect
    Record format:          'DELIMITED'
    Text delimiter:         '|'
    Quoted fields:          'N' (Y for Yes, N for No)
     
    ===================================================================
    =                                                                 =
    =                       Session Connection                        =
    =                                                                 =
    ===================================================================
     
    Connecting to RDBMS:       'tdpid'
    Connecting with UserId:    'user'
    Session character set:     'ASCII'
     
    **** 13:04:26 Connecting a session
    **** 13:04:27 Session connected
     
    ===================================================================
    =                                                                 =
    =                  Teradata Database Information                  =
    =                                                                 =
    ===================================================================
     
    **** 13:04:27 Teradata Database Version: '15.10.00.00      '
    **** 13:04:27 Teradata Database Release: '15.10.00.00      '
     
    ===================================================================
    =                                                                 =
    =                     Error Table Information                     =
    =                                                                 =
    ===================================================================
     
    Error table: 'SAMPLE_ERRTABLE1'
    Number of columns sent to RDBMS in the original load job: 10
     
    **** 13:04:27 The error table is a FastLoad protocol error table
    **** 13:04:27 The endianness of the error data in the error table is
         little-endian
     
    ===================================================================
    =                                                                 =
    =                    Selecting The Error Table                    =
    =                                                                 =
    ===================================================================
     
    **** 13:04:27 This job is submitting the following request:
     
    SELECT CAST(ERRORFIELDNAME AS VARCHAR(128)),CAST(ERRORCODE AS VARCHAR(10)),DATAPARCEL FROM SAMPLE_ERRTABLE1 ORDER BY ERRORCODE;
     
    **** 13:04:27 The request has been completed
    **** 13:04:27 Number of row(s) returned: 40
     
    **** 13:04:27 Starting to retrieve data from the RDBMS
    **** 13:04:27 Finished retrieving data
     
    **** 13:04:27 Number of row(s) writtten to the output file: 40
     
    ===================================================================
    =                                                                 =
    =                        Logoff/Disconnect                        =
    =                                                                 =
    ===================================================================
     
    **** 13:04:27 Disconnecting session
    **** 13:04:27 Session disconnected
     
    **** 13:04:27 Total processor time used = '0.06 Second(s)'
    .    Start : Fri Feb 22 13:04:26 2013
    .    End   : Fri Feb 22 13:04:27 2013
    .    Highest return code encountered = '0'.
    **** 13:04:27 This job terminated

    Note: The output sample above is for example purposes only. Your output will be different.

    Examples

    Example 1

    This example extracts the error information from the Load operator's acquisition phase error table. This example uses the sample schema for the Load operator job:

    EMP_ID   VARCHAR(20),
    EMP_NUM VARCHAR(10)

    This example will use this sample content for the Load operator's acquisition phase error table:

    ErrorCode ErrorFieldName DataParcel
    --------- -------------- ----------------------
    2683 EMP_NUM 0003003130300300313238
  • Use the following twbertbl syntax example for commands on all platforms except z/OS:
  • twbertbl -h <TdpId> -u <UserName> -t <ErrorTableName>
    -n <NumberOfLoadedColumns> -o <OutputFileName>
  • On z/OS, use the following syntax example inside a JCL:
  • //TWBERTBL EXEC PGM=TWBERTBL,
    // PARAM='-h TDP0 -u MyUser -p MyPass -n 2 -t LoadOpErrTbl1
    // -o DD:DATA'
  • After the extraction, the contents of the output file name will look like this, separated by a delimiter character:
  • EMP_NUM|2683|100|128

    The first field in the output file name is the field name that caused each error.

    The second field is the Teradata Database error code for each error.

    The rest of the fields are the column values of the data records for each error.

    Example 2

    This example extracts the error information from the Update operator's acquisition phase error table.

    This example will use this sample schema for the Update operator job:

    EMP_ID   VARCHAR(20),
    EMP_NUM VARCHAR(10)

    This example will use this sample content for the Update operator's acquisition phase error table:

    ImportSeq DMLSeq SMTSeq ApplySeq SourceSeq
    --------- ------ ------ -------- ---------
    1 1 1 1 1
    ErrorCode ErrorField HostData
    --------- ---------- ----------------------
    2683 EMP_NUM 0003003130300300313238
  • Use the following twbertbl syntax example for commands on all platforms except z/OS:
  • twbertbl -h <TdpId> -u <UserName> -t <ErrorTableName>
    -n <NumberOfLoadedColumns> -o <OutputFileName>
  • On z/OS, use the following syntax example inside a JCL:
  • //TWBERTBL EXEC PGM=TWBERTBL,
    // PARAM='-h TDP0 -u MyUser -p MyPass -n 2 -t UpdateOpErrTbl1
    // -o DD:DATA'
  • After the extraction, the contents of the output file name will look like this, separated by a delimiter character:
  • 1|1|1|1|1|EMP_NUM|2683|100|128

    The first field in the output file name is the import sequence number that caused each error.

    The second field is the DML sequence number that caused each error.

    The third field is the statement sequence number that caused each error.

    The fourth field is the apply sequence number that caused each error.

    The fifth field is the row sequence number that caused each error.

    The sixth field is the Teradata Database error code for each error.

    The seventh field is the field name that caused each error.

    The rest of the fields are the column values of the data records for each error.

    Restrictions and Limitations

  • The Teradata PT Error Table Extractor supports the acquisition phase error table for only these Teradata PT operators:
  • Load operator:
  • Update operator:
  • The Teradata PT Error Table Extractor cannot extract the error information from a Stream operator error table, because the Teradata PT Error Table Extractor does not know the layout of the Stream operator error table.

  • The schema for the Teradata PT Load and Update operator jobs must have all VARCHAR columns when the record format is DELIMITED. For other record formats, the Load and Update jobs can have different column data types.
  • The Teradata PT Error Table Extractor does not support the extraction of the application phase error tables.
  • The Teradata PT Error Table Extractor supports only a single-byte delimiter character.
  • On z/OS, the Teradata PT Error Table Extractor does not support the extraction of the acquisition phase error tables which were created on non-z/OS platforms, because the character encoding on z/OS is different from non-z/OS platforms.
  • On z/OS, the Teradata PT Error Table Extractor can only support the extraction of the acquisition phase error tables that were created from jobs run from the z/OS platforms.

  • On non-z/OS platforms, the Teradata PT Error Table Extractor does not support the extraction of the acquisition phase error tables which were created on z/OS, because the character encoding on non-z/OS is different from z/OS platforms.
  • On non-z/OS platforms, the Teradata PT Error Table Extractor can only support the extraction of the acquisition phase error tables that were created from jobs run from the non-z/OS platform.

  • On z/OS, the Teradata PT Error Table Extractor does not prompt the user for the password if the -u Password option is not specified. You must specify the -u Password option on z/OS.
  • The Teradata PT Error Table Extractor is not certified to extract the error information from the acquisition phase error table for these Teradata utilities.
  • FastLoad
  • MultiLoad
  • TPump
  • However, the Teradata PT Error Table Extractor can extract the error information from the acquisition phase error table for the Teradata FastLoad and MultiLoad utilities if the data for the FastLoad or MultiLoad job was sent in the indicator mode. If the data was sent in the non-indicator mode, then the Teradata PT Error Table Extractor cannot extract the error information.

    The Teradata PT Error Table Extractor cannot extract the error information from a TPump error table, because the Teradata PT Error Table Extractor does not know the layout of the TPump error table.

    twbkill

    Purpose  

    The twbkill command terminates all tasks running as part of an application.

    When you start Teradata PT using the tbuild command, Teradata PT automatically starts tasks that run in the background. Using the twbkill command with the name of the job terminates all tasks running as part of the application.

    Note: On UNIX systems, the twbkill user must either be superuser or have the same user ID as the target Teradata PT job. This command is not available on z/OS systems.

    Syntax

    where:

     

    Syntax Element

    Description

    jobId

    Job ID.

    To run multiple jobs concurrently, use a unique job name to qualify each jobs, thus avoiding the generation of duplicate names for checkpoint files. For more information, see “Setting Checkpoint Options” and “Restarting a Job From the Last Checkpoint Taken,” in theTeradata Parallel Transporter User Guide.

    - L jobLogDirectory

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

    This option is only applicable when - L jobLogDirectory is specified in the tbuild or tdload command. 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.

    Example

    Use the following twbkill command:

       twbkill wilson-235

    To terminate all tasks in the designated Teradata PT job. An error message results if the termination is not successful. This command creates the following output:

       # twbkill
       Using job directory /home/wilson/jobs
       wilson-235 killed

    twbrmcp

    Purpose  

    The twbrmcp command removes all checkpoint files associated with userId or jobId.

    For information on checkpointing Teradata Parallel Transporter User Guide.

    In the event of a job failure, the checkpoint files for the job are retained in the checkpoint directory to support job restart from the last checkpoint taken. If the job needs to restart from the beginning, then the checkpoint file(s) for that job must be removed.

    The twbrmcp command specifies which checkpoint files to remove by requiring the use of either the name of the job associated with the checkpoint files or the userId under which the job was executed.

    Syntax

    Specify one of the two available options to select the files to be removed.

    where:

     

    Syntax Element

    Description

    userId

    Option to remove all of the checkpoint files associated with the logon userId.

    jobId

    Option to remove all of the checkpoint files associated with the jobname specified in the tbuild command for a job.

    Example

    The following examples use the twbrmcp command.

  • The following twbrmcp command removes checkpoint files associated with a job and returns output verifying the removal:
  • $ twbrmcp MyJob
    Checkpoint file /opt/tbuild/client/<version>/tbuild/checkpoint/MyJobLVCP has been removed
    Checkpoint file /opt/tbuild/client/<version>/tbuild/checkpoint/MyJobCPD1 has been removed
    Checkpoint file /opt/tbuild/client/<version>/tbuild/checkpoint/MyJobCPD1 has been removed
  • The following twbrmcp command removes checkpoint files associated with a user and returns output verifying the removal:
  • $ twbrmcp ay160001
    Checkpoint file /opt/tbuild/client/<version>/tbuild/checkpoint/ay160001LVCP has been removed
    Checkpoint file /opt/tbuild/client/<version>/tbuild/checkpoint/ay160001CPD1 has been removed
    Checkpoint file /opt/tbuild/client/<version>/tbuild/checkpoint/ay160001CPD1 has been removed
  • The following twbrmcp command, used without an attribute, returns information on what parameters are required to designate a checkpoint file for removal.
  • cs4400s3:/ > twbrmcp
    Usage: twbrmcp LogonID
           twbrmcp JobName
    Parameter description:
    LogonID  The user logon ID
    JobName  The job name specified for the 'tbuild' command

    Note: The twbrmcp command is not available on z/OS.

    twbstat

    Purpose  

    The twbstat command displays the names of currently active Teradata PT jobs.

    This command is not available on z/OS systems.

    Syntax

    where:

     

    Syntax Element

    Description

    - L jobLogDirectory

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

    This option is only applicable when - L jobLogDirectory is specified in the tbuild or tdload command. 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.

    Example

    The following example uses the twbstat command. It returns a list of the Teradata PT jobs currently running on the system.

    The following twbstat command:

       #twbstat

    Creates the following output:

       Using job directory/home/cl151001/jobs
       Jobs running: 3
       cl151001-112
       lol42000-133
       dcc13370-147