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.

2. WHERE clause:

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

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