Sunday 17 November 2013

11.2 Teradata Parallel Transporter - Stream Operator- Additional Attributes


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'


52 comments:

  1. Hi, I am new to Teradata and need help...
    I installed Teradata Studio Express 15.0 and I am unable to create Teradata database connection. should I enter my IP address in "database server name" or my computer's name? Anyways I have tried both and it failed to make a connection.
    Also can I give any name for "user name [Domain]" or should I give my computer's name.
    Or is there a Teradata Database which I should download?
    Please Help...

    ReplyDelete
  2. well this was very nice article and very good information for Teredata learners i was really satisfied see this information we also provide Teredata Online Training

    ReplyDelete
  3. Teradata is a popular Relational Database Management System (RDBMS) suitable for large data warehousing applications. This is great post by admin on Teradata. Also learn Teradata from the best Teradata Online Training in your locality at CatchExperts.com

    ReplyDelete
  4. Teradata does have native export and load utilities, and with scripting you can do ETL within Teradata

    ReplyDelete
  5. This blog is having the general information. Got a creative work and this is very different one.We have to develop our creativity mind.This blog helps for this. Thank you for this blog. This is very interesting and useful.
    Skilled Manpower Services in Chennai

    ReplyDelete


  6. تعد خدمات التنظيف المتعلقه بالمنزل بصفه عامه ان الامور الهامه حاليا لما لها من ترابط وثيق بالصحه والقضاء علي الامراض وخصيصا تنظيف الخزانات والتي تعد من الضروريات حيث يجب تنظيفها بصفه دوري
    شركة تنظيف بالخرج
    فان كنت عميلنا الكريم من الرياض او الخرج او جده فنحن نوفر لك افضل خدمات التنظيف العامه بمعدات حديثه وعماله مدربه

    ReplyDelete
  7. I am beginner in TeraData. Finally, I got my answer after many hours of surfing the web by reading your article. Hope to see more posts on this technology.

    ReplyDelete
  8. Excellent Blog. Its like a handbook . Very well explained. Is it possible to add some example on how to use INMOD/OUTMOD in Tptscript for Export/Load. In the example try to insert an EscapeDelimiter and Delimiter while exporting the data. Example : Input data in the table : 123 , abc\, XYZ in my exported file the data should come as 123|abc\\|XYZ. Note '\' is an escape character & '|' is the delimiter for loading this exported data. Thanks for your help. M Iyer

    ReplyDelete
  9. Really Good article.provided a helpful information.keep updating...
    E-mail marketing company in india

    ReplyDelete
  10. Pretty article! I found some useful information in your blog, it was awesome to read, thanks for sharing this great content to my vision, keep sharing..
    Android App Development Company
    iOS App Development Company

    ReplyDelete
  11. Nice tutorial. The Teradata tutorial was help ful for me. Keep Sharing Tutorials.

    ReplyDelete
  12. This comment has been removed by the author.

    ReplyDelete
  13. The Mobile Accessories is a largest mobile retail Chain dealing in leading international and Indian Brands of mobile phones and accessories headquartered with using special offers and low cost of the latest branded mobile phones. This is amazing offers with some of days.

    Mobile Showrooms in OMR

    ReplyDelete
  14. Your thinking toward the respective issue is awesome also the idea behind the blog is very interesting which would bring a new evolution in respective field. Keep update more information.
    Paper Publishing Sites
    Naas Rated Journals
    Language Translation Services
    Research Paper Writing Service
    Article Writing Services

    ReplyDelete
  15. The information you provided in this Blog is very useful.The information is worth and very useful for the beginners.The Teradata Database is a shared-nothing massively parallel processing (MPP) relational database management system (RDBMS), making it the only commercially available RDBMS designed from the ground up for data warehousing Readmore..

    ReplyDelete
  16. Thank you for sharing valuable information.This article is very useful for me valuable info about
    Teradata Online Training.keep updating.........

    ReplyDelete
  17. Thank you for taking the time and sharing this information with us. It was indeed very helpful and insightful while being straight forward and to the point.
    Best Devops online Training
    Online DevOps Certification Course - Gangboard

    ReplyDelete
  18. After reading your post I understood that last week was with full of surprises and happiness for you. Congratz! Even though the website is work related, you can update small events in your life and share your happiness with us too.
    Data Science Training in Indira nagar
    Data Science Training in btm layout
    Python Training in Kalyan nagar
    Data Science training in Indira nagar
    Data Science Training in Marathahalli | Data Science training in Bangalore

    ReplyDelete
  19. This is really helpful. Thanks for sharing this list. I guess the most important thing is to steer clear away from spam moderators by putting comment that actually MATTER. Anyways, I’ll be using these blogs. Thank you so much!
    Authorized iphone service center in Chennai | iphone service center in chennai | Mobile service center in chennai | Authorized iphone service center in Chennai | iphone service center in chennai | iphone battery replacement

    ReplyDelete
  20. Thanks for sharing this blog post,Nice written skill Medical coding in hyderabad

    ReplyDelete
  21. Hi, I really loved reading this article. By this article i have learnt many things about OBIEE QAs, please keep me updating if there is any update.
    Teradata Online Training
    Teradata Training
    Teradata Online Course keep updating.........

    ReplyDelete
  22. Employees have access to PHI should take the Privacy Security overview training. If you accept Medicare and Medicaid insurance then take Fraud Waste and Abuse (FWA) Prevention Training. Employees in the IT department can consider taking comprehensive HIPAA Security officer training Compliance Training and from HR department can take comprehensive HIPAA Privacy Compliance Training

    ReplyDelete
  23. Thoughtful information's are shared here...Gained lot's of ideas and Knowledge from this site...Thanks for All your Great works...
    Java training in chennai | Java training in annanagar | Java training in omr | Java training in porur | Java training in tambaram | Java training in velachery

    ReplyDelete
  24. This is an awesome motivating article.I am practically satisfied with your great work.You put truly extremely supportive data. Keep it up. Continue blogging. Hoping to perusing your next post
    Java Training in Chennai

    Java Training in Velachery

    Java Training in Tambaram

    Java Training in Porur

    Java Training in Omr

    Java Training in Annanagar

    ReplyDelete
  25. This comment has been removed by the author.

    ReplyDelete
  26. This comment has been removed by the author.

    ReplyDelete
  27. This blog is having the general information. Got a creative work and this is very different one.We have to develop our creativity mind.This blog helps for this. Thank you for this blog. This iDigital Marketing Training in Chennai

    Digital Marketing Training in Velachery

    Digital Marketing Training in Tambaram

    Digital Marketing Training in Porur

    Digital Marketing Training in Omr

    Digital Marketing Training in Annanagar

    s very interesting and useful.

    ReplyDelete
  28. magnificentincense.com
    24K Monkey Classic Incense 10g
    AK-47 – X10 / PREMIUM
    Bizarro Incense
    Buy Black Mamba Incense Online
    Buy WTF Herbal Incense
    Cloud9 Mad Hatter Incense
    Crazy Monkey Incense
    k2 spray on paper
    k2 paper sheets
    Klimax Potpourri 15xxx Coconut(10g)
    Crazy Monkey Incense
    Cloud9 Mad Hatter Incense
    Buy Purple Diesel Incense Online
    Buy Pure Fire Herbal Incense Online
    Buy Kisha Cole Incense (11g) online
    Buy KUSH HERBAL INCENSE online
    Buy Mind Trip Incense Online
    Buy Platinum XXX Herbal Incense online
    buy Orange Platinum Caution 10G
    Buy OMG HERBAL POTPOURRI 10G online

    ReplyDelete
  29. https://k2incenseonlineheadshop.com/
    info@k2incenseonlineheadshop.com
    Buy liquid incense cheap
    Buy liquid incense cheap For Sale At The Best Incense Online Shop
    K2 Spice Spray | Liquid K2 | Liquid Spice | K2 Spray for sale

    ReplyDelete
  30. First You got a great blog .I will be interested in more similar topics. i see you got really very useful topics, i will be always checking your blog thank
    Teradata Training in Bangalore

    ReplyDelete
  31. This blog very easily understandable. Thanks for sharing such an informative post with us. This is a nice post in an interesting line of content.
    Teradata Training in Bangalore

    ReplyDelete
  32. Thanks for this. I really like what you've posted here and wish you the best of luck with this blog and thanks for sharing

    Teradata Training in Bangalore

    ReplyDelete
  33. https://syntheticworldwide.com/
    sales@syntheticworldwide.com

    Buy cheap liquid herbal incense at your best online shop

    ReplyDelete
  34. Teradata Developer Training Get practical exposure on creating and managing the databases and become a master in data analytics from folks IT.

    ReplyDelete
  35. Teradata Developer Training Get practical exposure on creating and managing the databases and become a master in data analytics from folks IT.

    ReplyDelete
  36. to the medical condition of most patients. many people suffering from cancer, chronic pain, anxiety and man more have overcome their illment with cannabis. you can order weed from our store





    ReplyDelete