Saturday, 9 November 2013

7.1 Teradata Parallel Transporter Export Operator - Basics

Export Operator Capabilities:

  • It’s a producer operator.

  • Uses Teradata FastExport Protocol.

  • Performs high speed data transfer using block transfer over multiple sessions.

  • Data exported using Export Operator can be used

  • By a consumer operator, such as a Load, Update, Stream, or SQL Inserter operator to load data to a Teradata Database table.
     
  • By a DataConnector operator as a consumer operator, the data can be written to a flat file, passed to an access module provided by Teradata, or passed to a custom access module created by an end user.




Comparison between Export Operator and Fast Export Utility:

Fast Export utility Feature
Teradata PT Operator Support
Access Modules
Supported using Data Connector Operator.
ANSI Date
Supported.
BlockSize Specification
Supported.
Character Set
Supported using the USING CHARACTER SET clause before the DEFINE JOB statement.
Checkpoint/Restart
Supported.
Configuration File
Supported using the tbuild command line attribute option (-v)
DECIMALDIGITS option
Supported.
Indicator Mode
Supported using Data Connector operator.
Maximum/Minimum sessions
Supported. --> Using Maxsessions and Minsessions attribute
Multiple Teradata SQL Select Statements
Supported with Same layouts.
Record Format
Supported using DataConnector Operator
Nonindicator Mode
Supported using DataConnector Operator
OUTFILE option
Supported using DataConnector Operator
OUTLIMIT
Supported.  --> Using the OUTLIMIT attribute
OUTMOD routines
Supported, using the FastExport OUTMOD Adapter operator.
RUN FILE command
Supported by Te r a d a t a P Tscript language
Show version information
Supported.
No spooling
Supported.  --> Using SpoolMode attribute explain below
SQL database command
Supported.  --> using Workingdatabase attribute
SYSTEM command.
Supported using the OS command operator.
Tenacity
Supported.  --> Using the TenacityHours attribute
DISPLAY command
Not Supported.
Environment Variables
Not Supported.
IF-ELSE-ENDIF commands
Not supported.
INMOD routines
Not supported.
IMPORT command
Not supported.
ROUTE MESSAGES command
Not supported.
SET command
Not supported.




Export Operator Definition:

Following is an example of the Export Operator Definition:

DEFINE OPERATOR EXPORT
DESCRIPTION 'EXPORT OPERATOR'
TYPE EXPORT
SCHEMA PRODUCT_SOURCE_SCHEMA
ATTRIBUTES
(
VARCHAR TdpId,
VARCHAR UserName,
VARCHAR UserPassword,
VARCHAR SelectStmt,
VARCHAR PrivateLogName
);

All the above shown attributes are key attributes.
In addition, other optional operator attributes can be specified depending on how the operator will be used.

TDPID :
It’s a optional Security attribute that specifies the name of the Teradata database machine(non-mainframe platforms) or TDP(mainframe platforms) for Export operator job.
 
If we do not specify the value for the TdpId attribute, the operator uses the default TdpID established for the user by the system administrator.
UserName:
Attribute that specifies the user name on the Teradata Database.
UserPassword :
Attribute that specifies the password associated with the user name.
PrivateLogName
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 messages produced by the
operator.
SelectStmt
Its is a required attribute which specifies the select query used by the export operator to perform data selection.

A select request within the Export script can
 
1) have multiple select statements. -->
 
The Teradata Database might execute the requests in parallel, but response are still returned in the order of the requests, for the first statement first, then for the second, and so on.
If the structure of response rows differs, an error results and the job terminates.

2) can be optionally preceded by Locking modifier -->
 
The specified lock remains in effect during the execution of all statements within the request that contains the modifier.

Following is a valid SELECT request using the LOCKING modifier:

LOCKING TABLE MYTABLE FOR ACCESS SELECT COL1, COL2 FROM MYTABLE;

Note that the LOCKING modifier can precede the SELECT statement.

3 )  and can use a ORDER BY clause  --> We can specify order by clause when using single instance of ORDER BY clause.

Following is a valid ORDER BY clause:
SELECT COL1, COL2 FROM MYTABLE ORDER BY COL1;


However a select request cannot:
1) Specify a USING modifier.
2) Access non data tables such as SELECT DATE or SELECT user.
3) Be satisfied by one or two AMPs, such as SELECT statement which accesses rows based on the primary index or unique secondary index of a table.
4) Contain character large object (CLOB) or binary large object (BLOB) data types.

 




Limits Output:

  1. OUTLIMIT:

  • Use OUTLIMIT to limit the number of rows that a export job returns.
  • Note that OUTLIMIT expects a value greater than zero as input, otherwise it returns an error.
  • If OUTLIMIT is not specified, the export operator returns all records.
  • OUTLIMIT controls the number of rows exported for each instance.

  1. WHERE clause:

The WHERE clause can limit the number of rows that are exported by specifying conditions that must be met.

  1. SAMPLE function:

The SAMPLE function is an SQL function that can limit the number of random rows returned.



Check pointing and Restarting:

The Export operator takes a checkpoint only when all data is sent to the data stream. If a restart occurs, the operator either must send all of the data or none of the data depending on whether the checkpoint has taken place.
 
• If all the data is sent, then the operator displays the following message and does not resend any of the data:
Restart indicates that this export job completed.
 
• If all the data is not sent, the operator terminates. Restart the job from the beginning.
 
• If none of the data is sent, the operator sends the data.

The Export operator does not support a user-defined restart log table.

If a checkpoint interval is specified on the tbuild command line, the checkpoints incurred between the start of data loading and the end of data loading are ignored by the Export operator.


1 comment:


  1. Excellent Blog very imperative good content, this article is useful to beginners and real time
    employees.Thank u for sharing...

    Teradata Online Training

    ReplyDelete