Required and Optional Attributes - Parallel Transporter

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

Valid values are:

• ‘No’ = Stream operator will not use the existing error table (default).
• ‘Yes’ = Stream operator will use the existing error table or create the error table if it does not exist.

  If the error table exists, the Stream operator displays the number of rows in the error table.

  If the structure of the existing error table is not compatible with the error table that the Stream operator expects, the Stream operator terminates the job with an error message.

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.

Valid values are:

• ‘On’ = Stream operator will use ArraySupport for the entire job.
• ‘Off’ = Stream operator will not use ArraySupport.

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:

• Both Teradata Database and CLIv2 support the Array Support feature.
• The DML statement is a single DML statement or an atomic UPSERT statement.
• The job step must have a single DML group if Serialize is On.

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 Teradata Database or Teradata 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.

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.

The CheckpointRowCount values are:

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

  This attribute is only available in the TPT script mode.

Valid values are:

• ‘On’ = all SQL requests, responses, and data are encrypted.
• ‘Off’ = no encryption occurs (default).

• 'integerDate'= the integer DATE data type (default)
• 'ansiDate'= the ANSI fixed-length CHAR(10) DATE data type

Valid values are:

• ‘Yes’ = Stream operator drops the error table when it is empty at the end of a job (default). Teradata PT automatically executes a DROP TABLE statement.
• ‘No’ = Stream operator will not drop the error table even if it is empty at the end of a job.

  If you run many small (short-duration) load jobs on a regular basis, setting DropErrorTable to ‘No’ for these jobs reduces updates to the Teradata Database data dictionary.

  If the error table is not dropped, it can be used when AppendErrorTable is set to ‘Yes’ at the beginning of the next job.

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 Teradata 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.

This number is approximate because the Stream operator sends multiple rows of data at a time to Teradata 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.

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 Teradata Database.

This value is required if LDILoadJob is set to 'Yes' ('Y').

For example, LDILoadGroup = 'Grp1'

The LDILoadJob values are:

• 'No' ('N') = No Isolated Load (default)
• 'Yes' ('Y') = Request Isolated Load. The stream operator will send "BEGIN ISOLATED LOADING ON <target table(s)> USING QUERY_BAND 'LDILoadGroup=<...>;' IN MULTIPLE SESSION;" request to Teradata Database if the target table(s) is (are) qualified. The target table(s) has(have) to be created as "WITH ISOLATED LOADING FOR ALL".

  For example:

  CREATE TABLE   testtable,
  	          FALLBACK,
  	          WITH ISOLATED LOADING FOR ALL
     (COL1       VARCHAR(5),
      COL2       VARCHAR(5));');

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.

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:

• SELECT
• INSERT
• DELETE

The following privileges are required on the database that contains the restart log table.

• DROP
• CREATE

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.

The default macro database is the restart log table database.

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.

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.

• 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.

• '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
• “Ultra’ = ‘Yes” in the Ultra Notification Level column

• 'None'= no event logging is done (default).
• 'Msg'= sends the events to a log.

  On Windows, the events are sent to the event log 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:

  NMEventCkptBeg64

  NMEventCkptEnd64

  NMEventErrorTable64

  NMEventRunStats64

  NMEventImportEnd64

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

  Complete Teradata object names are passed to the notify exit routine for the NMEventInitializeEON event.

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

• 80 bytes, if the NotifyMethod is 'Exit'
• 16 bytes, if NotifyMethod is 'Msg'

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.

• 'No' ('N') = no pack (default)
• 'Yes' ('Y') = determine maximum possible pack factor

The PauseLDI values are:

• 'No' ('N') = The Stream operator will send the "END ISOLATED LOADING FOR QUERY_BAND 'LDILoadGroup=<value>;';" to Teradata Database if "BEGIN ISOLATED LOADING ON <target table(s)> USING QUERY_BAND 'LDILoadGroup=<value>;' has been sent.
• 'Yes' ('Y') = The Stream operator will not send the "END ISOLATED LOADING FOR QUERY_BAND 'LDILoadGroup=<value>;';" to Teradata Database.

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:

• The valid values are integers between 1 and 600.
• The default value is 4, which means four 15-second periods per minute.
• If the statement rate is unlimited, then the Periodicity value is ignored.
• While the job is running, users can change the Periodicity value using the Teradata PT External command interface utility twbcmd.

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.

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 Stream 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 Stream operator constructs the necessary SET QUERY BAND SQL and issues it as part of the Stream operator SQL sessions to communicate the request to the Teradata Database.
• The Stream 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 Stream operator will then terminate the job and report the error to the user.

Valid values are:

• ‘Yes’ (‘Y’) = create the error table as a queue table.
• ‘No’ (‘N’) = create the error table as a non-queue table (default).

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.

Use of the Rate attribute is subject to the following conditions and rules:

• The statement rate must be a positive integer.
• If the statement rate is not specified, the rate is unlimited.
• If the statement rate is less than the statement packing factor, the Stream operator sends requests smaller than the packing factor.
• If the statement rate is invalid, Stream Operator will display an error message and terminate with a return code of 8.
• If Serialization is not in use: the Stream operator will add successive rows to the session input “bucket” until the Pack value is reached. Then it will send the data and go on to the next session in the session list and begin filling the new input bucket. However, if statement rate/periodicity (R/P) is less than the packing factor, then only R/P rows will be added to a session bucket before the contents of the bucket are sent to the Teradata Database.
• If Serialization is in use: The data in the serialization columns is hashed and the resulting value is used as an index to the session list to select the session for the data row. Thus when using Serialization and R/P is less than the Pack factor, the R/P rows sent to the Teradata Database may be scattered across multiple sessions.
• While the job is running, users can change the statement rate value using the Teradata PT External command interface utility twbcmd.

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’ = no override request is sent to the Teradata Database (default)

For more information, see Teradata Replication Services Using Oracle GoldenGate.

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 Teradata 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:

• 'Yes' ('Y') = Use robust restart logic (default). In the robust mode, for each packed request, a number of “partial checkpoint” rows are written to the log between checkpoints. The rows are deleted each time a checkpoint is written. In Robust recovery mode, the Stream operator must next ascertain how much processing has been completed since the last logged checkpoint. This is accomplished by reading back a set of “Partial Checkpoints” from the Teradata Database, sorting them and then reprocessing all transactions that were left incomplete when the job was interrupted.
• 'No' ('N') = Use simple restart logic. In this case, restarts cause the Stream operator to begin where the last checkpoint occurs in the job. Any processing that occurs after the checkpoint is redone. This method does not have the extra overhead of the additional database writes in the robust logic, and should be adequate in certain DML statements that can be repeated without changing the results of the operation.

If uncertain whether to use robust restart logic, it is always safe to set the Robust parameter to 'Yes'.

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.

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.

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.

• '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 function for 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 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.

Valid values:

• 'On' = Enable the Unicode Pass Through feature in the operator.
• 'Off' = (Default) Disable the Unicode Pass Through feature in the operator.