Attributes in Details:
Attributes
|
Required/Optional
|
Description
|
AppendErrorTable
= ‘value’
|
O
|
Optional
attribute that specifies whether or not the Stream Operator will use the
existing error table.
Valid
values are:
• ‘No’ = The Stream
operator will not use the existing error table
(default).
• ‘Yes’ = The 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 ,(ie when AppendErrorTable = 'NO'),
the Stream operator terminates the job with an error message if the error
table already exists.
|
ArraySupport
= ‘option’
|
O
|
Optional
attribute that specifies whether or not the Stream operator
will
use the ArraySupport feature for the job.
Valid
values are:
•
‘On’ = The Stream operator will use ArraySupport for the entire job.
•
‘Off’ = The 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.
Note that ArraySupport can be specified at two
places : -
1) As an attribute 2) ARRAYSUPPORT DML operation. |
Buffers
= buffers
|
O
|
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.
|
DropErrorTable=
'value'
|
O
|
Optional
attribute that specifies whether or not the
Stream Operator
will drop the error table at the end of a job.
Valid
values are:
•
‘Yes’ = The 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’ = The Stream operator will not drop the error table even if it is
empty
at the end of a job.
If the error table is not dropped, it can be used
when AppendErrorTable is set to ‘Yes’ at the beginning of the next job.
|
DropMacro
= 'value'
|
O
|
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 Streamoperator 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.
|
ErrorLimit
= limit
|
O
|
Optional
attribute that Teradata 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 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.
|
ErrorTable
= 'etname'
|
O
|
Optional
attribute that specifies the name of the error table.
This
table contains information concerning data conversion errors, constraint
violations, and other error conditions.
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.
If
the name is not supplied, it is created by the Stream Operator.
User-supplied
names for error tables must not exceed 30 bytes
|
LogTable
= 'ltname'
|
R
|
Required
attribute that specifies the name of the restart log table for
checkpoint
information.
If the restart log table does not exist, the
Stream operator creates it. If it exists, the Stream operator restarts from
the last checkpoint.
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. Otherwise, you may specify a working
database
using the WorkingDatabase attribute.
|
MacroDatabase
= 'dbname'
|
O
|
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
|
O
|
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.
(note
this is different that export,load ,update operator)
The
sessions are distributed across instances.
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 operatorsessions, then each
instance will log on 4 Stream operator sessions.
|
MinSessions
= minsessions
|
O
|
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
|
OperatorCommandID
=‘commandID’
|
O
|
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:
1
Declare the operatorCommandId attribute in the DEFINE OPERATOR statement for
the STream operator.
2 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, Te r a d a t a P Twill 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’)),
3 Use
the twbcmdutility to assign a Rate value to a specific
Stream
operator copy.
|
Pack
= number
|
O
|
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.
|
PackMaximum
= 'packmax'
|
O
|
Optional
attribute that requests the Stream operator to
dynamically
determine the maximum possible pack factor for the
current Stream
job.
The
PackMaximum values are:
•
'No' ('N') = No pack (default)
•
'Yes' ('Y') = Determine maximum possible pack factor
|
Periodicity
= periodicity
|
O
|
Option
that specifies that the DML statements sent by the Stream operator to the
Teradata 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:
• 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. So this attribute is related to the Rate
attribute.
•
While the job is running, users can change the Periodicity value
using
the Teradata PT External command interface utility twbcmd.
|
QueueErrorTable
= ‘option’
|
O
|
Optional
attribute that specifies whether the error table is a queue table.
Valid
values are:
• ‘Yes’ (‘Y’) = Create the error
table as a queue table.
• ‘No’ (‘N’) =
(Default) Create the error table as a
non-queue table.
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
= statement rate
|
O
|
Option that specifies the maximum number of DML
statements per minute the Stream operator can submit to the Teradata
Database.
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.
•
While the job is running, users can change the statement rate value
using
the Teradata PT External command interface utility twbcmd.
|
Robust
= 'robust'
|
O
|
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.(1
for each multistatement request.. So letter entries with higher pack
attribute)
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. (note that by default checkpoint is taken before and after
the data is sent )
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.
|
TenacityHours
= hours
|
O
|
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 Teradata 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
|
O
|
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 Teradata
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.
|
WorkingDatabase
= 'dbname'
|
O
|
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.
|
UserName
= 'userid'
|
R
|
Same
as in Export operator.
|
UserPassword
= 'password'
|
R
|
Same
as in Export operator.
|
TraceLevel
= 'level'
|
O
|
Same
as in Export operator.
|
QueryBandSessInfo
=
‘<Query
Band
expression>’
|
O
|
Same
as in Export operator.
|
LogonMech
= ‘string’
|
O
|
Same
as in Export operator.
|
LogonMechData
= ‘data’
|
O
|
Same
as in Export operator.
|
LogSQL
= 'option'
|
O
|
Same
as in Export operator.
|
DataEncryption
= ‘option’
|
O
|
Same
as in Export operator.
|
DateForm
= 'datatype'
|
O
|
Same
as in Export operator.
|
AccountId
= 'acctid'
|
O
|
Optional
attribute that specifies the account associated with the user
name.
If
omitted, it defaults to the account identifier of the immediate owner
database.
|
NotifyExit
= 'inmodname'
|
O
|
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.dllfor Windows platforms
•
libnotfyext.slfor HP-UX platforms
•
libnotfyext.sofor all other UNIX platforms
•
NOTFYEXTfor z/OS platforms
|
NotifyLevel
= 'notifylevel'
|
O
|
Optional
attribute that specifies the level at which certain events are
reported.
The 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
•
“Ultra’ = ‘Yes” in the Ultra Notification Level column
|
NotifyMethod
= 'notifymethod'
|
O
|
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 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.conffile.
• On
z/OS systems, events are sent to the job log.
•
'Exit'= sends the events to a user-defined notify exit routine.
|
NotifyString
= 'notifystring'
|
O
|
Optional
attribute that provides a user-defined string to precede all
messages
sent to the system log. This string is also sent to the userdefined notify
exit routine. The maximum length of the string is:
• 80
bytes, if the NotifyMethod is 'Exit'
• 16
bytes, if NotifyMethod is 'Msg'
|