Required and Optional Attributes|SQL Inserter Operator - Required and Optional Attributes - Parallel Transporter

Teradata® Parallel Transporter Reference - 20.00
Deployment
VantageCloud
VantageCore
Edition
IntelliFlex
Lake
Enterprise
VMware
Product
Parallel Transporter
Release Number
20.00
Published
October 2023
ft:locale
en-US
ft:lastEdition
2023-12-06
dita:mapPath
mjn1691132485167.ditamap
dita:ditavalPath
obe1474387269547.ditaval
dita:id
ogv1478610452101
lifecycle
latest
Product Category
Teradata Tools and Utilities

Use the attribute definition list syntax in the Teradata PT DEFINE OPERATOR statement to declare the required attribute values for the SQL Inserter operator.



where:

SQL Inserter Operator 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.
ARRAY Optional keyword that specifies more than one attribute value.
ConnectString = 'connectionString' Optional attribute that specifies the connection string. The connection string will be passed to CLI. CLI will validate the connection string.

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

The TPT Connection String feature is available on all platforms, except for the TDP variant of TPT on z/OS.
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 Inserter operator job where:
  • 'integerDate' = the integer DATE data type (default)
  • 'ansiDate' = the ANSI fixed-length CHAR(10) DATE data type
DeleteLobDataFiles = 'option' Optional attribute that specifies whether to delete deferred LOB data files from the Data Connecter Producer once the rows are committed to database.
Valid values are:
  • ‘Yes’ (or ‘Y’) = delete deferred mode LOB data files once rows are committed to the database.
  • ‘No’ (or ‘N’) = do not delete deferred mode LOB data files (default).

Specifying any other value results in an error.
DeleteTempLobFiles Optional attribute that tells the operator whether or not to delete the temporary LOB directory and the temporary LOB files in the directory at cleanup.

Valid values are:

  • 'Y[es]' = Tells the operator to delete the temporary LOB directory at cleanup. This is the default.
  • 'N[o]' = Tells the operator not to delete the temporary LOB directory at cleanup. The user is responsible for deleting the temporary LOB directory.
When the temporary LOB files exist in a remote NFS mount directory, the suggestion is to set this attribute to 'No' and the operator will not spend time deleting the temporary LOB files.
Temporary LOB files will be created in the temporary LOB directory.

The naming convention for the temporary LOB directory will be as follows:

  • When the producer's LobDirectoryPath attribute is set, the name of the temporary LOB directory will be this: <LobDirectoryPath>/<job id>_s<job step number>_TempLOBDir
  • When the producer's LobDirectoryPath attribute is not set, the name of the temporary LOB directory will be this: <current working dir>/<job id>_s<job step number>_TempLOBDir

This attribute is not supported on z/OS.
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” 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 the SQL is less than 32K. If the SQL to be logged exceeds 32K, truncate the display to the first 32K bytes.
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 value is one session for each operator instance.

The main instance calculates an even distribution of the Inserter operator sessions among the number of instances. For example, if there are 4 instances and 16 sessions, each instance will log on 4 sessions.

  • If multiple sessions are used and the data has duplicate primary index values, this could result in a potential d deadlock 2631 error. To avoid this error, do not use multiple sessions with the TPT SQL Inserter operator.
  • If multiple sessions are used and the data accesses the same row-level lock on the database, the job can result in a hang. To avoid a hang, do not use multiple sessions with the TPT SQL Inserter operator. Use only one session.
MinSessions = ‘minSessions’ Optional attribute that specifies the minimum number of sessions required for the Stream operator job to continue.

The MinSessions value must be greater than 0 and less than or equal to the maximum number of sessions defined in the attribute MaxSessions.

Specifying a value less than 1 terminates the job.
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 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.

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, see Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144 and Teradata Vantage™ - SQL Data Definition Language Detailed Topics, B035-1184.

The value of the QueryBandSessInfo attribute is displayed in the SQL Inserter 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 Inserter operator constructs the necessary SET QUERY BAND SQL to communicate the request to the database.
  • The SQL Inserter operator does not check the Query Band expression, but passes the expression to the database as is.
  • If the version of the 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, the database will return an error. The SQL Inserter operator will then terminate the job and report the error to the user.
ReplicationOverride = 'option' Optional attribute that overrides the normal replication services controls for an active session.
Valid values:
  • ‘On’ = override normal replication services controls for the active session.
  • ‘Off’ = override of normal replication services is turned off for the active session (when change data capture is active).
  • ‘None’ = (Default) No override request is sent to the database.

For more information, see Teradata Replication Services Using Oracle GoldenGate (B035-1152).

The user ID that is logged in by the operator must have the REPLCONTROL privilege when setting the value for this attribute.
RemoveBOMFromFile = 'option' Optional attribute that specifies whether to look for and remove the UTF byte-order-mark (BOM) from the beginning of an XML, JSON, or CLOB data file
Valid values are:
  • 'Y’ or ‘Yes' = The Inserter operator looks for the UTF BOM at the beginning of an XML, JSON, or CLOB data file and, if found, removes it before processing (default)

    If the found BOM is not valid for the client Unicode character set, the operator displays an error message and terminates the job.

    If the BOM is not found, the Inserter operator processes the file without an error message or warning message being logged to the private log or to the console

  • 'N’ or ‘No' = The Inserter operator does not look for the UTF BOM.

    Teradata recommends that RemoveBOMFromFile be set to 'No' when moving XML, JSON or CLOB data in deferred mode encoding in the Unicode character set from one table to another. This can improve loading performance when large amount of data (for example, million rows) are inserted in a table in the deferred mode.

RemoveBOMFromFile can only be used when the following two conditions are met:
  • The job schema has one or more XML, JSON and/or CLOB columns defined AS DEFERRED BY NAME.
  • 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.
RoleName = 'role name'

Optional attribute that implements security in a database environment. The operator will prepend the value with "SET ROLE ". The syntax will be sent to the database as follows:

SET ROLE <role name>;

For example:

SET ROLE All;

For details of "SET ROLE" command use, see Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.

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

VARCHAR ARRAY RoleName = ['role name1', 'role name2'],

The operator will send the request to the database on the SQL sessions after the sessions are connected.

  • The operator does not validate the value for this attribute. The operator passes the value to the database as is. The database will validate the value. The operator will terminate the job with an error when the validation fails.
  • C-style comments are allowed in the value and will be passed to the database.
  • ANSI-style comments are not supported in the value. The operator can terminate the job with a syntax error when the value contains an ANSI-style comment.
  • A semicolon is not allowed in the value, because the operator allows only a single statement per request. The operator will terminate the job with an operator error when the value contains a semicolon.
TdpId = 'dbcName' Optional attribute that specifies the name of the database machine (non-mainframe platforms) or TDP (mainframe platforms) for the insert operation.

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.
TimeZoneSessInfo = 'timeZoneValue' Optional attribute that allows you to change the default time zone displacement for the duration of the operator's job session.

When you provide a value for this attribute, the operator will build the SET TIME ZONE <timeZoneValue>; SQL request.

The operator will send the request to the database on the SQL sessions after the sessions are connected.

Here are some examples:
  • Example 1: This example sets the session default time zone displacement to LOCAL, which is the system default time zone:

    VARCHAR TimeZoneSessInfo = 'LOCAL'

  • Example 2: This example sets the session default time zone displacement to USER, which is the default time zone for the logged on user:

    VARCHAR TimeZoneSessInfo = 'USER'

  • Example 3: This example sets the session default time zone displacement to a simple constant time zone string expression:

    VARCHAR TimeZoneSessInfo = '''America Pacific'''

    Any single quote character (') inside the value must be entered as two consecutive single quote characters in a TPT job script. This ensures the correct value will be sent to the database.
  • The operator does not validate the value for this attribute. The operator passes the value to the database as is. The database will validate the value. The operator will terminate the job with an error when the validation fails.
  • C-style comments are allowed in the value and will be passed to the database.
  • ANSI-style comments are not supported in the value. The operator will terminate the job with a syntax error when the value contains an ANSI-style comment.
  • A semicolon is not allowed in the value, because the operator allows only a single statement in the "SET TIME ZONE SQL" request. The operator will terminate the job with an operator error when the value contains a semicolon.

For more information on SET TIME ZONE SQL, see Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.
TraceLevel = 'level' Optional attribute that specifies the types of diagnostic messages that are written by 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 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 the mentioned 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.
TransformGroup = 'transformgroupname' Optional attribute that supports changing the active transform for Teradata Complex Data Types (CDTs). The value is the <udt name> <transform group name>, and the operator will prepend the hardcoded string "SET TRANSFORM GROUP FOR TYPE ". The syntax sent to RDBMS is as follows: 
SET TRANSFORM GROUP FOR TYPE <udt name> <transform group name>;
For example:
"SET TRANSFORM GROUP FOR TYPE JSON CHARACTER SET LATIN TD_JSON_VARCHAR;"
The VARCHAR ARRAY can specify more than one value, for example:
VARCHAR ARRAY TransformGroup = ['JSON CHARACTER SET LATIN TD_JSON_VARCHAR',
                                'ST_GEOMETRY TD_GEO_VARCHAR'],

The operator will send the request to the database on the SQL sessions after the sessions are connected.

  • The operator does not validate the value for this attribute. The operator passes the value to the database as is. The database will validate the value. The operator will terminate the job with an error when the validation fails.
  • C-style comments are allowed in the value and will be passed to the database.
  • ANSI-style comments are not supported in the value. The operator can terminate the job with a syntax error when the value contains an ANSI-style comment.
  • A semicolon is not allowed in the value, because the operator allows only a single statement per request. The operator will terminate the job with an operator error when the value contains a semicolon.
TreatDBSRestartAsFatal= ‘option’ Optional attribute that tells the operator whether to terminate the job when a database restart occurs.
The TreatDBSRestartAsFatal values are:
  • ‘No’ (‘N’) = The operator will not terminate if a database restart occurs (default). The database restart will be treated as a retryable one.
  • ‘Yes’ (‘Y’) = The operator will terminate if a 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.
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 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 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.