Use the attribute definition list syntax in the Teradata PT DEFINE OPERATOR statement to declare the required and optional attribute values for the Stream operator.
where:
Syntax Element | Description |
---|---|
AccountId = 'acctId' | Optional attribute that specifies
the account associated with the user name. If omitted, it defaults to the account identifier of the immediate owner database. |
AppendErrorTable = ‘option’ | Optional attribute that specifies whether or not the Stream Operator will use the existing error table. Valid values are:
By default, the Stream operator terminates the job with an error message if the error table already exists. On a job restart, the Stream operator will not terminate the job with an error message when the error table exists. |
ArraySupport = ‘option’ | Optional attribute that specifies whether or not the Stream operator will use the ArraySupport feature for the job. Valid values are:
Even if the value for ArraySupport is not specified, Array Support is still enabled and the Stream operator will use it for the entire job if the following criteria are met:
If any of these criteria are not met, then the default value is 'Off' and the Stream operator will not use Array Support. If the value for ArraySupport is set to ‘On’ and either the database or CLIv2 does not support the Array Support feature, the Stream operator will terminate with a fatal error. If the ARRAYSUPPORT DML option is used as part of the APPLY statement for a job, the DML value will override the value specified for the Stream operator ArraySupport attribute. |
Buffers = buffers | Optional attribute that specifies
whether to increase the number of request buffers. The range of values is a lower limit of 2 and no upper limit. The default value is 3. The maximum number of request buffers that may be allocated is the number of buffers multiplied by the number of connected sessions (Buffers * connected_sessions). Request buffers are a global resource, so buffers are assigned to any session as needed, and then returned to a free pool. At any point in time, the number of request buffers assigned to a session can vary from zero to Buffers * connected_sessions. |
CheckpointRowCount = ‘option’ | Optional attribute that tells the Stream operator to enable or disable outputting the rows sent at checkpoints. Valid values are:
|
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 on z/OS.
|
DataEncryption =' option' | Optional attribute that enables full security encryption of SQL requests, responses, and transmitted data. Valid values are:
|
DateForm = 'option' | Optional attribute that specifies
the DATE data type for the Stream operator job. The values are:
|
DeleteLobDataFiles = 'option' | Optional attribute that specifies whether to delete deferred LOB data files from the Data Connector Producer once the rows are committed to the database. Valid values for 'option' are:
Specifying any other value results in an error. |
DropErrorTable = 'option' | Optional attribute that specifies whether or not the Stream Operator will drop the error table at the end of a job. Valid values are:
|
DropMacro = 'option' | Optional attribute that instructs the Stream operator whether to drop macros or keep them for future use.VARCHAR DropMacro = '<Y[es]|N[o]>' By default, the Stream operator drops macros at the end of a successful job. When the value of DropMacro is N or No, the macros remain in the database until a DROP MACRO statement is issued against them. Kept macros are maintained by the database until they are explicitly dropped, so be sure to check for obsolete macros periodically and drop them. |
ErrorLimit = limit | Optional attribute that the database specifies the approximate number of records that can be stored in the error table before the Stream operator job is terminated. This number is approximate because the Stream operator sends multiple rows of data at a time to the database. By the time Teradata PT processes the message indicating that the error limit has been exceeded, it may have loaded more records into the error table than the actual number specified in the error limit. The ErrorLimit specification must be greater than 0. Specifying an invalid value will cause the Stream operator job to terminate. By default, the ErrorLimit value is unlimited. The ErrorLimit specification applies to each instance of the Stream operator.
|
ErrorTable = 'errorTableName' | Optional attribute that specifies the name of the error table. This table contains information concerning data conversion errors, constraint violations, and other error conditions. Prefix the error table name with a database name as a qualifier. This means that because the database might contain a lot of PERM space, that space does not need to be increased for all databases with tables involved in the load. If the database for the error table is not specified, the table is placed in the database associated with the user logon. By default, the error table must be a new table. This default can be changed. If the AppendErrorTable attribute is set to 'Yes' or 'Y', then the error table can be a new or existing table. Do not use a name that duplicates the name of an existing table unless you are restarting a Stream Operator job or reusing the error table by setting the AppendErrorTable attribute to 'Yes' or 'Y'. If the name is not supplied, it is created by the Stream Operator. User-supplied names for error tables must not exceed the maximum allowable size for table names on the database. |
LDILoadGroup = 'value' | Optional attribute that tells the
Stream operator to LDILoadGroup value. This value is required if LDILoadJob is set to 'Yes' ('Y'). For example, LDILoadGroup = 'Grp1' |
LDILoadJob = 'option' | Optional attribute that tells the Stream operator to enable or disable the Isolated Load feature. The LDILoadJob values are:
|
LDIMacroTableName = ['table1', 'table2' ... | Optional attribute that specifies the LDI table names for the predefined macros. It is required when predefined macros are used, LDILoadJob is set to 'Y[es]' and LDILoadGroup is specified with a value. |
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, see “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, see “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:
|
LogTable = 'logTableName' | Optional attribute that specifies the name of the restart log table for checkpoint information. The restart log table must be a unique table name and must not exist, unless restarting from a paused job. If the restart log table does not exist, the Stream operator creates it. If it exists, the Stream operator restarts from the last checkpoint. Failure to specify a restart log table terminates the job. The following privileges are required on the restart log table:
The following privileges are required on the database that contains the restart log table:
The Stream 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. |
MacroDatabase = 'dbName' | Optional attribute that specifies
the database to contain any macros used by the Stream operator. The default macro database is the restart log table 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 for each operator instance. The main instance calculates an even distribution of the Stream operator sessions among the number of instances. For example, if there are 4 instances and 16 Stream operator sessions, then each instance will log on 4 Stream operator sessions. |
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 Stream operator sessions. Specifying a value less than 1 terminates the job. The default is 1. |
NotifyExit = 'inmodName' | 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:
See Deprecated Syntax for information about providing your own notify exit routine. |
NotifyLevel = 'notifyLevel' | Optional attribute that specifies
the level at which certain events are reported. The valid values
are:
|
NotifyMethod = 'notifyMethod' | Optional attribute that specifies
the method for reporting events. The methods are:
|
NotifyString = 'notifyString' | Optional attribute that provides 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:
|
OperatorCommandID = 'commandId' | Although you can specify rate and periodicity values using Stream operator attributes, you may not know the optimal values for a specific job step until after the job has begun running. OperatorCommandId allows you to identify a specific reference of a Stream operator to which you can assign new rate or periodicity values after the job has begun, using twbcmd: Teradata PT will generate a default value for OperatorCommandId composed of <operator object name> + <process Id> for each copy of the operator in the APPLY specification. If you want to assign another identifier, do the following: Declare the operatorCommandId attribute in the DEFINE OPERATOR statement for the Stream operator. You can optionally assign a value to the OperatorCommandID attribute in a referenced copy of the Stream operator (in an APPLY statement). If no value is assigned, Teradata PT will provide a system-generated value. A useful OperatorCommandID value might be the number of the job step in which you want to change the Rate, as follows: APPLY <dml1> TO OPERATOR ( Stream_Oper[2] ATTRIBUTES ( OperatorCommandID = ‘ratestep#1’)), APPLY <dml2> TO OPERATOR ( Stream_Oper[3] ATTRIBUTES ( OperatorCommandID = ‘ratestep#2’)), Use the twbcmd utility to assign a Rate value to a specific Stream operator copy. For information on use of the twbcmd utility to change the Rate value, see twbcmd. |
Pack = number | Optional attribute that specifies the number of statements to pack into a multiple statement request. The maximum value is 2400. The default value is 20. When the job is loading 1 or more deferred LOB/JSON/XML columns, the largest possible pack factor is 4096 because the database can support up to 4096 spool files per request.
|
PackMaximum = 'option' | Optional attribute that requests
the Stream operator to dynamically determine the maximum possible
pack factor for the current Stream job. The PackMaximum values
are:
|
PauseLDI =' option' | Optional attribute that tells the Stream operator to pause the Isolated Load feature. The PauseLDI values are:
|
Periodicity = periodicity | Option that specifies that the DML statements sent by the Stream operator to the database will be as evenly distributed as possible over each one minute interval. The periodicity value sets the number of sub-intervals per minute. Periodicity facilitates the orderly and efficient use of system resources. For example: If the statement rate is 1600 and the periodicity value is 10, then the maximum number of statements processed is 160 (1600/10) statements every 6 (60/10) seconds. Use of the Periodicity attribute is subject to the following conditions and rules:
|
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 Stream 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 Stream 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. 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, 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 Stream operator private log. Use of the QueryBandSessInfo attribute is subject to the following rules:
|
QueueErrorTable = 'option' | Optional attribute that specifies whether the error table is a queue table. Valid values are:
This attribute is unique to the Stream operator and to the error table in the Stream operator. This attribute is especially useful in capturing errors that result when using the SELECT and CONSUME database operation, which returns rows and DELETEs them. Using an error table as a queue table can eliminate the need to delete the rows in the error table. |
Rate = statementRate | Option that specifies the maximum number of DML statements per minute the Stream operator can submit to the database. Use of the Rate attribute is subject to the following conditions and rules:
|
ReplicationOverride = 'option' | Optional attribute that overrides the normal replication services controls for an active session. Valid values:
For more information, see Teradata Replication Services Using Oracle GoldenGate. The user ID that is logged in by the operator must have the REPLCONTROL privilege when setting the value for this attribute.
|
Robust = 'option' | Optional attribute that specifies whether or not to use robust restart logic for recovery/restart operations. In “robust mode,” one database row is written in the log restart table for every request issued. This collection of rows in the restart log table can be referred to as the request log. Because a request is guaranteed by the database to either completely finish or completely roll back, the request log will always accurately reflect the completion status of an import. The Robust values are:
If uncertain whether to use robust restart logic, it is always safe to set the Robust parameter to 'Yes'. |
RoleName = 'role name' | Optional attribute that implements security in the database. 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 main control and data SQL sessions after the sessions are connected.
|
TdpId = 'dbcName' | Optional attribute that specifies the name of the database machine (non-mainframe platforms) or TDP (mainframe platforms) for the Stream 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.
|
TenacityHours = hours | Optional attribute that specifies the number of hours that the Stream operator continues trying to log on when the maximum number of sessions are already running on the database. The default value is 4 hours. To enable the tenacity feature, the hours value must be greater than 0. Specifying a value of 0 disables the tenacity feature. Specifying a value of less than 0 terminates the Stream operator job. |
TenacitySleep = minutes | Optional attribute that specifies the number of minutes that the Stream operator job pauses before retrying a log on operation when the maximum number of sessions are already running on the database. The minutes value must be greater than 0. If you specify a value less than 1, the Stream operator responds with an error message and terminates the job. The default is 6 minutes. |
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 main control and data SQL sessions after the sessions are connected. Here are some examples:
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 each instance
of the operator to the 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:
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.
|
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 main control and data SQL sessions after the sessions are connected.
|
TreatDBSRestartAsFatal= ‘option’ | Optional attribute that tells the operator whether or not to terminate the job when a database restart occurs. The TreatDBSRestartAsFatal values are:
|
UnicodePassThrough = ‘value’ | Optional attribute that tells the operator to enable or disable the Unicode Pass Through feature. Valid values:
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 user name. Use of this attribute is not compatible with some external authentication logon methods.
|
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.
|
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. |