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 attribute values for the SQL Selector operator.







where:

SQL Selector Attribute Descriptions 
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.

AddBOMToFile = 'option' Optional attribute that specifies whether or not the UTF byte-order-mark (BOM) will be added at the beginning of an XML, JSON, or CLOB output data file.

Valid values are:

  • 'Y’ or ‘Yes' = The Selector operator adds the appropriate UTF BOM at the beginning of an XML, JSON or CLOB output data file.
  • 'N’ or ‘No' = The Selector operator does not prefix the appropriate UTF Byte-Order-Mark (BOM) at the beginning of an XML (JSON or CLOB) output data file (default).

AddBOMToFile can only be used when following two conditions are met:

  1. The job schema has one or more XML, JSON, or CLOB column(s) defined AS DEFERRED BY NAME.
  2. The client character set for the load job is the Unicode character set.

If these two conditions are not met, the values specified in the attribute are ignored.

The operator does not add a BOM to a data file extracted from a BLOB column in deferred mode.

Because the default behavior is not to prefix a BOM when extracting data from XML, JSON, or CLOB columns, you should set the attribute RemoveBOMFromFile to 'No' if you use the Selector to extract XML, JSON, or CLOB data and the Inserter operator to load the data in deferred mode. For details about RemoveBOMFromFile attribute SQL Inserter Operator.

Teradata strongly recommends that you specify XMLSERIALIZE on selected XML columns so that the byte-order-mark (BOM) matches the XML encoding when using the client UTF-16 character set.
ARRAY Optional keyword that specifies more than one attribute value.
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 SQL Selector operator job. The values are:
  • 'integerDate' = the integer DATE data type (default)
  • 'ansiDate' = the ANSI fixed-length CHAR(10) DATE data type
INTEGER Keyword that specifies INTEGER as the data type of the defined attribute.
IsolationLevel = 'option' Optional attribute that specifies a lock access value that determines the isolation level for all queries in a session.

Valid values:

  • RU = read uncommitted
  • SR = (default) serializable

When the database successfully changes the isolation level, the following message is written to the private log:

Session Isolation Level: RU
LobDirectoryPath = 'pathName' Optional attribute that specifies the complete path name of an existing directory where all LOB, JSON, and XML data files will be written.
LobFileBaseName = 'fileName' Optional attribute that defines a character string that will be prefixed to the names of LOB, JSON, and XML data files.

The file names created by the SQL Selector operator are in the following format:

<column-name>_<job-id>_p<#>_r<#>

where:

  • # that follows “p” is the identification of the SQL Selector copy
  • # that follows “r” is the row order returned from Teradata Database.

For example, if we have the following schema:

DEFINE SCHEMA <schema-name>
  (
    COL2 BLOB AS DEFERRED BY NAME,
    COL3 CLOB AS DEFERRED BY NAME,
    COL4 XML AS DEFERRED BY NAME,
    COL5 JSON(1000000) AS DEFERRED BY NAME,
  );

where LobFileBaseName has the value “my_test”,

then the files names will be:

  • my_test_COL2_<job-id>_p1_r1
  • my_test_COL3_<job-id>_p1_r1
  • my_text_COL4_<job-id>_pl_r1
  • my_text_COL5_<job-id>_pl_r1
  • my_test COL2_<job-id>_p1_r2
  • my_test_COL3_<job-id>_p1_r2
  • my_test_COL4_<job-id>_pl_r2
  • my_test_COL5_<job-id>_pl_r2
  • my_test_COL2_<job-id>_p1_r3
  • my_test_COL3_<job-id>_p1_r3
  • my_test_COL4_<job-id>_pl_r3
  • my_test_COL5_<job-id>_pl_r3

and so on.

LobFileExtension = 'fileExtension' Optional attribute that specifies the extension for LOB, JSON, and XML data file names.

Examples of ‘file-extensions’ include:

  • ‘jpg’: indicates that a file ‘ccc.jpg’ is a picture file.
  • ‘gif’: indicates that the file ‘ddd.gif’ is a picture file.
  • ‘html’: indicates that the file ‘aaa.html’ is an html file.
  • * 'json': indicates that the file is a JSON file.
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 32 K. If the SQL to be logged exceeds 32K, truncate the display to the first 32 K bytes.
MaxDecimalDigits = maxDecimalDigits Optional attribute that specifies the maximum number of digits in the DECIMAL data type that can be exported.

The default value is 38.

If the attribute has a value of q where 38>=q>=1, then any returned DECIMAL (n[,m]) data item with n>q is implicitly CAST to DECIMAL; overflows are handled the same as an explicit CAST.

On mainframe-attached platforms, it is recommended that the MaxDecimalDigits attribute not exceed 31 to avoid representations that exceed the capacity of the native instruction set.

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 PrivateLogName attribute:

tlogview -j jobid -f privatelogname

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

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 SQL Selector 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 SQL Selector operator constructs the necessary SET QUERY BAND SQL to communicate the request to the Teradata Database.
  • The SQL Selector 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 SQL Selector operator will then terminate the job and report the error to the user.
ReportModeOn = 'option' Optional attribute that specifies whether or not to use the field report mode.

This feature allows you to extract data from the Teradata Database in character form, and then change it into the “delimited” (VARTEXT) format using the DataConnector operator and save it to a text file. When the data is exported in character format (field report mode), columns defined in the schema must be VARCHAR.

The values are:

  • 'Yes' ('Y') = Report mode (returns data in character format)
  • 'No' ('N') = Indicator mode (default)
SelectStmt = 'SELECT statement(s);' Required attribute that specifies a Teradata SQL SELECT statement or statements that return row data in the form of a result table.

The SQL Selector operator can submit a single SELECT statement or multiple SELECT statements. All specified SELECT statements are treated as a single request to be sent to the Teradata Database. The entire request cannot be larger than 1 MB (Teradata Database limitation).

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

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

If you do not specify 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
  • '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 will change 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 export 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).

VARCHAR Keyword that specifies VARCHAR as the data type of the defined attribute.
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.