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: (Preferred notation) Endianess defaults to that of the client platform (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. (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. (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: 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: 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: Type: Warning Explanation: The memory size value exceeds the maximum allowed value. Remedy: Correct the -h option value. 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. 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: 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: tbuild -S logDestination where tbuild -S logDestination where Note: A fully qualified dataset name is indicated by enclosing the name in single quote marks. tbuild -S logDestination where 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. An error is returned if both of these options are specified on the tdload command line. 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:
SELECT statement requests cannot: 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. An error is returned if both of these options are specified on the tdload command line. 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: |
--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: |
--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,
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:
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:
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:
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
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. 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: 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.
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 |
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: twbcmd <jobid> step#1 RATE=2000
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: or, 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.
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.
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.
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.
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 |
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 |
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 |
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),
This example will use this sample content for the Load operator's acquisition phase error table:
ErrorCode ErrorFieldName DataParcel
--------- -------------- ----------------------
twbertbl -h <TdpId> -u <UserName> -t <ErrorTableName>
//TWBERTBL EXEC PGM=TWBERTBL,
// PARAM='-h TDP0 -u MyUser -p MyPass -n 2 -t LoadOpErrTbl1
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),
This example will use this sample content for the Update operator's acquisition phase error table:
ImportSeq DMLSeq SMTSeq ApplySeq SourceSeq
--------- ------ ------ -------- ---------
ErrorCode ErrorField HostData
--------- ---------- ----------------------
twbertbl -h <TdpId> -u <UserName> -t <ErrorTableName>
//TWBERTBL EXEC PGM=TWBERTBL,
// PARAM='-h TDP0 -u MyUser -p MyPass -n 2 -t UpdateOpErrTbl1
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 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.
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 can only support the extraction of the acquisition phase error tables that were created from jobs run from the non-z/OS platform.
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.
$ 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
$ 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
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