16.10 - Required and Optional Attributes - Parallel Transporter

Teradata Parallel Transporter Reference

Product
Parallel Transporter
Release Number
16.10
Published
July 2017
Content Type
Programming Reference
Publication ID
B035-2436-077K
Language
English (United States)

Use the attribute definition list syntax in the Teradata PT DEFINE OPERATOR statement to declare the required and optional attribute values for the Load operator.









where:

Load Operator Attribute Definitions 
Syntax Element Description
AccountId = 'acctId' Optional attribute that specifies the account associated with the specified user name.

If omitted, it defaults to the account identifier of the immediate owner database.

BufferSize = KBytes Optional attribute that specifies the output buffer size, in kilobytes, that is used for sending Load parcels to the Teradata Database.

The output buffer size and the size of the rows in the Load table determine the maximum number of rows that can be included in each parcels to the Teradata Database. A larger buffer size reduces processing overhead by including more data in each parcels.

Allowable values are 1 through 16384, but if you specify a value of 16384, the actual buffer size gets set to 16775552 bytes, which is less than the full 16MB. If the value less than 1, an error message results and job terminates.

The default buffer size is 1024K bytes. The maximum allowed buffer size is usually 16384 Kbytes. Values are evaluated when the connection to the Teradata Database is made.

If the supplied buffer size is too large, the operator scales it back to the maximum allowed buffer size.

CheckpointRowCount - ‘option Optional attribute that tells the Load operator to enable or disable outputting the rows sent at checkpoint(s).

The CheckpointRowCount values are:

  • 'No' ('N') = The Load operator will not output rows sent at checkpoint(s) (default);
  • 'Yes' ('Y') = The Load operator will output rows sent at checkpoint(s)

This attribute is only available in the TPT script mode.

DataEncryption = ‘option’ Optional attribute that enables full security encryption of SQL requests, responses, and data.

Valid values are:

  • ‘On’ = all SQL requests, responses, and data are encrypted.
  • ‘Off’ = no encryption occurs (default).
DateForm = 'option' Optional attribute that specifies the DATE data type for the Load operator job. Valid values are:
  • 'integerDate' = the integer DATE data type (default)
  • 'ansiDate' = the ANSI fixed-length CHAR(10) DATE data type
DropErrorTable = ‘option Optional attribute that specifies whether or not error tables are dropped upon successful completion of the load job, even if the error tables are empty.

Valid values are:

  • ‘Yes’ = Load operator will drop the error table only if empty (default). Teradata PT automatically executes a DROP TABLE statement.

    If you run many load jobs on a regular basis, checking ‘Yes’ would cause lots of updates to the Data Dictionary, resulting in performance problems.

  • ‘No’ = Load operator will not drop the error table, even if empty. The User must manually execute a DROP TABLE statement on the error table.

    If the table is not dropped prior to running a Teradata PT job that would use tables by the same name, running that job may result in a DBS error or in unpredictable results.

DropLogTable = ‘option Optional attribute that specifies whether or not restart log table is dropped upon successful complete of the load job.

Valid values are:

  • ‘Yes’ = Load operator will drop the restart log table (default). Teradata PT automatically executes a DROP TABLE statement.

    You can only set value to ‘Yes’ for restart jobs. For details Teradata Parallel Transporter User Guide.

  • ‘No’ = Load operator will not drop the restart log table. The user must manually execute a DROP TABLE statement on the restart log table.

    If the table is not dropped prior to running a Teradata PT job that would use the table by the same name, running that job may result in a DBS error or in unpredictable results.

ErrorLimit = errorLimit Optional attribute that specifies the approximate number of records that can be stored in one of the error tables before the Load operator job is terminated.

The ErrorLimit specification must be greater than 0. Specifying an invalid value will cause the Load operator to terminate. By default, ErrorLimit value is unlimited.

The ErrorLimit specification applies to each instance of the Load operator.

ErrorTable1 = 'errorTable1Name' Optional attribute that specifies the name of the first error table. ErrorTable1 contains records rejected because of:
  • Data conversion errors
  • Constraint violations
  • AMP configuration changes

These types of errors always occur during the acquisition phase of the Load operator job.

ErrorTable1 must be a new table name. Do not use a name that duplicates the name of an existing table unless you are restarting a paused Load operator job.

The default name for ErrorTable1 is ttname_ET.

For more information, see ErrorTable1 and Auto-Generation of Error Tables.

ErrorTable2 = 'errorTable2Name' Optional attribute that specifies the name of the second error table. ErrorTable2 contains records that violated the unique primary index constraint. This type of error always occurs during the application phase of the Load operator job.

ErrorTable2 must be a new table name. Do not use a name that duplicates the name of an existing table unless you are restarting a Load operator job.

The default name for ErrorTable2 is ttname_UV.

For more information, see ErrorTable2 and Auto-Generation of Error Tables.

LogonMech = ‘string’ Optional attribute that specifies which logon mechanism to use.

Specification of this attribute may be required for some authentication methods.

The job terminates if the attribute exceeds 8 bytes.

For information on specification requirements for LogonMech “Logon Security” in Teradata Parallel Transporter User Guide (B035-2445).

LogonMechData = ‘data Optional attribute that passes along additional logon data.

Specification of this attribute is required for some external authentication methods.

For information on specification requirements for LogonMechData “Logon Security” in Teradata Parallel Transporter User Guide (B035-2445).

LogSQL = 'option' Optional attribute that controls how much of the job’s SQL to enter into the log.

Valid options are:

  • 'Yes' = output the full SQL to the log. The maximum length is 1M.
  • 'No' = do not output SQL to the log.
  • No value or attribute omitted = accept the pre-defined limit, which displays up to 32K of SQL if all of the SQL is less than 32K. If the SQL to be logged exceeds 32K, truncate the display to the first 32K bytes.
LogTable = 'logTableName' Optional attribute that specifies the name of the restart log table that stores checkpoint information for restarting a job.

If running a new job, specify a new table name that is different from the name of any existing table). The Load operator then creates a new restart log table.

If restarting a paused job, then the restart log table must exist, and the Load operator restarts the job from the last checkpoint. The restarted job will continue using the existing restart log table.

When a job successfully completes, the operator drops the restart log table. Failure to specify a restart log table will cause the job to terminate.

The following privileges are required on the restart log table:

  • SELECT
  • INSERT
  • DELETE

Users must also have DROP and CREATE privileges on the database that contains the restart log table.

The Load operator automatically maintains the restart log table. Manipulating the restart log table in any way invalidates the restart capability.

If the restart log table name is not fully qualified, it is created under the user's default (logon) database. If the WorkingDatabase attribute is used, you MUST fully qualify the restart log table name, even if the restart log table is going to reside in the default (logon) database.

MaxSessions = maxSessions Optional attribute that specifies the maximum number of sessions to log on.

The MaxSessions value must be greater than 0. Specifying a value less than 1 terminates the job.

The default is one session per available AMP. The maximum value cannot exceed the number of available AMPS.

MinSessions = minSessions Optional attribute that specifies the minimum number of sessions required for the Load operator job to continue.

The MinSessions value must be greater than 0 and less than or equal to the maximum number of Load operator sessions. Specifying a value less than 1 (the default) terminates the job.

NotifyExit = 'inmodName' Optional attribute that specifies the name of the user-defined notify exit routine with an entry point named _dynamn. If no value is supplied, the following default name is used:
  • libnotfyext.dll for Windows platforms
  • libnotfyext.dylib for the Apple OS X platform
  • libnotfyext.so for all other UNIX platforms
  • NOTFYEXT for z/OS platforms

See Deprecated Syntax for information about providing your own notify exit routine.

NotifyExitIsDLL = 'option' Optional attribute (for z/OS systems only) that specifies whether the notify exit routine is built as a DLL (shared library) or not. Valid values are:
  • 'Yes' (or 'Y') = notify exit routine is built as a DLL (default).
  • 'No' or ('N') = notify exit routine is not built as a DLL.

Any other value terminates the job.

NotifyLevel = 'notifyLevel' Optional attribute that specifies the level at which certain events are reported. Valid values are:
  • 'Off' = no notification of events is provided (default)
  • 'Low' = 'Yes' in the Low Notification Level column
  • 'Med' = 'Yes' in the Medium Notification Level column
  • 'High' = 'Yes' in the High Notification Level column
NotifyMethod = 'notifyMethod' Optional attribute that specifies the method for reporting events. The methods are:
  • 'None' = no event logging is done (default).
  • 'Msg' = sends the events to a log.

    On Windows, the events are sent to the EventLog that can be viewed using the Event Viewer. The messages are sent to the application log.

    On Solaris, AIX, Linux, and HP-UX platforms, the destination of the events is dependent upon the setting specified in the /etc/syslog.conf file.

    On SLES11, the destination of the events is dependent upon the setting specified in the /etc/syslog-ng.conf file.

    On z/OS systems, events are sent to the job log.

  • 'Exit' = sends the events to a user-defined notify exit routine. Row count information is in 4-byte unsigned integer values.
  • 'Exit64' = sends the events to a user-defined notify exit routine. Row count information is in 8-byte unsigned integer values for these events:

    NFEventCheckPoint64

    NFEventPhaseIEnd64

    NFEventPhaseIIEnd64

    NFEventDropErrTableI64

    NFEventDropErrTableII64

  • 'ExitEON' = sends the events to a user-defined notify exit routine.

    Complete Teradata object names are passed to the notify exit routine for these events:

    NFEventInitializeEON

    NFEventPhaseIBeginEON

    In addition ExitEON sends row count information in 8-byte unsigned integer values.

NotifyString = 'notifyString' Optional attribute that specifies a user-defined string to precede all messages sent to the system log. This string is also sent to the user-defined notify exit routine. The maximum length of the string is:
  • 80 bytes, if NotifyMethod is 'Exit'
  • 16 bytes, if NotifyMethod is 'Msg'
PauseAcq = 'option' Optional attribute that specifies whether to pause the Load operator job after the acquisition phase or enter the application phase. Values are:
  • 'N[o]' for normal Load operator jobs, to distribute all of the rows that were sent to the Teradata Database during the acquisition phase to their final destination on the AMPs (default).
  • 'Y[es]' to pause after the completion of the acquisition phase and skip the application phase.

Specifying any other value terminates the job.

The absence of any value for the PauseAcq attribute means that the Load operator job will execute both the acquisition phase and the application phase without pausing. This will distribute all of the rows that were sent to the Teradata Database during the acquisition phase to their final destination on the AMPs.

PrivateLogName = 'logName' Optional attribute that specifies the name of a log that is maintained by the Teradata PT Logger inside the public log. The private log contains all of the output provided by the operator.

The private log can be viewed using the tlogview command as follows, where jobId is the Teradata PT job name and privateLogName is the value for the operator’s PrivateLogName attribute:

tlogview -j  jobid  -f  privatelogname

If the private log is not specified, all output is stored in the public log.

By default, no diagnostic trace messages are produced. Diagnostic trace messages are produced only when the user sets a valid value for the TraceLevel attribute.

For more information about the tlogview command, see Teradata PT Utility Commands.

QueryBandSessInfo = ‘queryBandExpression Optional attribute that specifies the Query Band for the duration of the job sessions.

The queryBandExpression is a set of name=value pairs, separated by a semicolon and ending with a semicolon. The user defines the Query Band expression, which will look similar to the following example:

‘org=Finance;load=daily;location=west;’

QueryBandSessInfo may also be specified as an ARRAY attribute.

For information on the rules for creating a Query Band expression SQL Data Definition Language Syntax and Examples (B035-1144) and SQL Data Definition Language Detailed Topics (B035-1184).

The value of the QueryBandSessInfo attribute is displayed in the Load operator private log.

Use of the QueryBandSessInfo attribute is subject to the following rules:

  • By default, Query Band is off until a valid value appears for the QueryBandSessInfo attribute.
  • If the QueryBandSessInfo attribute contains a value, the Load operator constructs the necessary SET QUERY BAND SQL and issues it as part of the Load operator SQL sessions to communicate the request to the Teradata Database.
  • The Load Operator does not check the Query Band expression, but passes the expression to Teradata Database as is.
  • If the version of Teradata Database against which the job is being run does not support the Query Band feature, no Query Banding will take place. However, the operator will ignore the error and run the rest of the job.
  • If there is a syntax error in the Query Band expression, Teradata Database will return an error. The Load operator will then terminate the job and report the error to the user.
TargetTable = 'targetTableName' Required attribute that specifies the name of the Load target table to receive the data from the client system.

A target table must already exist on a Teradata Database and be empty, with no defined secondary or join indexes, before a Load job is run. You cannot use a name that duplicates the name of an existing table unless you are restarting a paused Load operator job.

For target tables defined as NoPI, the restriction that the target table must be empty does not apply.

VARCHAR TASMFASTFAIL = 'value' Optional attribute that enables FASTFAIL feature.

Valid values are:

  • 'Yes' or ‘Y’ = Enable FastFail feature. The job will terminate gracefully when it is supposed to be held by DBS.
  • 'No' or ‘N’ = FastFail feature is not enabled (default). The job will appear to hang if the TASM rules dictate that a job should be held for any reason.
TenacityHours = hours Optional attribute that specifies the number of hours that the Load operator continues trying to log on when the maximum number of load/unload operations are already running on the Teradata Database.

The default value is 4 hours. To enable the tenacity feature, hours must be greater than 0. Specifying a value of 0 disables the tenacity feature. Specifying a value of less than 0 terminates the Load job.

TenacitySleep = minutes Optional attribute that specifies the number of minutes that the Load operator pauses before retrying to log on when the maximum number of load/export operations are already running on the Teradata Database.

The minutes value must be greater than 0. If you specify a value less than 1, the Load operator responds with an error message and terminates the job. The default is 6 minutes.

TdpId = 'dbcName' Optional attribute that specifies the name of the Teradata Database machine (non-mainframe platforms) or TDP (mainframe platforms) for the Load operator job.

The dbcName can be up to 256 characters and can be a domain server name.

If you do not specify the value for the TdpId attribute, the operator uses the default TdpId established for the user by the system administrator.

On a mainframe, a single-character TdpId is supported. When only one character is specified, it is assumed to be an abbreviation for a four-character TdpId that begins with TDP.
TraceLevel = 'level' Optional attribute that specifies the types of diagnostic messages that are written by each instance of the operator to the public log (or private log, if one is specified using the PrivateLogName attribute). The diagnostic trace function provides more detailed information in the log file to aid in problem tracking and diagnosis. The trace levels are:
  • 'None' = TraceLevel turned off (default).
  • 'CLI' = enables the tracing function for CLI-related activities (interaction with the Teradata Database)
  • 'PX' = enables the tracing function for activities related to the Teradata PT infrastructure
  • 'Oper' = enables the tracing function for operator-specific activities
  • 'Notify' = enables the tracing of activities related to the Notify feature
  • 'All' = enables tracing for all of the above activities

The VARCHAR ARRAY can specify more than one value, for example:

VARCHAR TraceLevel = 'CLI'
VARCHAR TraceLevel = 'OPER'
VARCHAR ARRAY TraceLevel = [ 'CLI' ]
VARCHAR ARRAY TraceLevel = [ 'CLI', 'OPER' ]
The TraceLevel attribute is provided as a diagnostic aid only. The amount and type of additional information provided by this attribute changes to meet evolving needs from release to release.
TreatDBSRestartAsFatal= 'option' Optional attribute that tells the operator whether or not to terminate the job when a Teradata Database restart occurs.

The TreatDBSRestartAsFatal values are:

  • ‘No’ (‘N’) = The operator will not terminate if a Teradata Database restart occurs (default). The Teradata Database restart will be treated as a retryable one.
  • ‘Yes’ (‘Y’) = The operator will terminate if a Teradata Database restart occurs.
UnicodePassThrough = 'value' Optional attribute that tells the operator to enable or disable the Unicode Pass Through feature.

Valid values:

  • 'On' = Enable the Unicode Pass Through feature in the operator.
  • 'Off' = (Default) Disable the Unicode Pass Through feature in the operator.
When a TPT job is using the UTF8 or UTF16 session character set, the UnicodePassThrough attribute can be set to 'On' to allow the operator to load data with Unicode pass through characters.
UserName = 'userId' Attribute that specifies the Teradata Database user name.

Use of this attribute is not compatible with some external authentication logon methods.

For more information on UserName specification requirements, see “Logon Security” in the Teradata Parallel Transporter User Guide (B035-2445).

UserPassword = 'password' Attribute that specifies the password associated with the user name.

Use of this attribute is not compatible with some external authentication logon methods.

For more information on password specification requirements, see “Logon Security” in the Teradata Parallel Transporter User Guide (B035-2445).

WildcardInsert= ‘option’ Optional attribute that builds an INSERT statement from the table definition.

Use this attribute to load all of the columns in a table when the columns contain user-defined types (UDTs).

Valid values are:

  • ‘Y[es]’ = builds an INSERT statement.
  • ‘N[o]’ = no activity (default).

If you set this attribute to Yes when a valid, fully supported INSERT statement already exists, an error results.

A valid tableName matches the name of the table used in the TargetTable attribute and the semicolon is in the last non-whitespace character in the supplied DML statement as follows:

INS[ERT] [INTO] <tablename> ;
WorkingDatabase = 'databaseName' Optional attribute that specifies a database other than the logon database as the default database.

The name of the database that is specified with this attribute is used in the Teradata SQL DATABASE statement that is sent by the operator immediately after connecting the two SQL sessions.

If WorkingDatabase is not specified, the default database associated with the logged on user is assumed for all unqualified table names.