8.1 Teradata Parallel Transporter - SQL selector Operator - Basics

SQL Inserter Operator Capabilities:

• SQL Selector is a Producer operator.

• It submits a single SQL SELECT statement to a TD Database to retrieve Data from table.

• SQL selector is different from the Export Operator because :
   
1. SQL selector uses only single session , while  export operator uses multiple sessions.

2. SQL selector uses only single instance, while export operator uses multiple sessions.

3. SQL selector is used when exporting small number of rows.

• Advantages of SQL operator:
   
1. The SQL Selector operator has a Report Mode, known as Field Mode in the BTEQ environment. All data retrieved in this mode is converted to character strings.
    
2. The SQL Selector operator can extract LOB data from Teradata Database.
    
3. The SQL Selector operator does not require an active load job. Instead, standard SQL protocol is used on the single session.
    
4. The SQL Selector operator is the only operator that can retrieve data from Teradata Database in Field Mode and send data to the DataConnector operator in order to have it written to an external target in the VARTEXT (or TEXT) format. The Export operator cannot be used to extract data from a Teradata Database table and write it to an external target in VARTEXT (or TEXT) format.  ( Note : This is possible using EXPORT operator if we cast all the columns in select statements to either VARCHAR or CHAR format)

SQL Selector Operator Definition: Not for LOB Exporting

Following is an example of an SQL Selector operator definition.

Only key attributes are shown (in bold).

DEFINE OPERATOR SQL_SELECTOR

DESCRIPTION 'SQL OPERATOR'

TYPE SELECTOR

SCHEMA PRODUCT_SOURCE_SCHEMA

ATTRIBUTES

(

VARCHAR TdpId,

VARCHAR UserName,

VARCHAR UserPassword,

VARCHAR SelectStmt,

VARCHAR ReportModeOn,

VARCHAR PrivateLogName

);

SQL Selector Operator Definition: For LOB Exporting:

Following is an example of an SQL Selector operator definition when used for exporting Large  Objects (LOBs).

Only key attributes are shown (in bold).

DEFINE OPERATOR SQL_SELECTOR

DESCRIPTION 'SQL OPERATOR'

TYPE SELECTOR

SCHEMA PRODUCT_SOURCE_SCHEMA

ATTRIBUTES

(

VARCHAR TdpId,

VARCHAR UserName,

VARCHAR UserPassword,

VARCHAR SelectStmt,

VARCHAR PrivateLogName

VARCHAR LobDirectoryPath,

VARCHAR LobFileBaseName,

VARCHAR LobFileExtension

);

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	A SELECT request is a Teradata SQL SELECT statement. Following are certain restrictions : Do not specify a WITH clause with a SELECT statement. Use of a WITH or WITH BY clause produces an error message.   Do not specify multiple Teradata SQL SELECT statements. If you specify multiple select statements in the SelectStmt attribute, the operator produces an error message stating that multiple SELECT statements are not allowed, and the job terminates. Note that this is different than EXPORT operator. Do not specify a USING modifier with a SELECT statement. Use of a USING modifier produces an error message. Why does SQL selector does not support use of multiple Teradata SQL statements? The SQL Selector operator does not support the use of multiple Teradata SQL SELECT statements because this offers no value. The SQL Selector operator logs on only one session with the Teradata Database. Multiple SELECT statements are therefore sent as only one request to the Teradata Database. The Teradata Database executes these multiple statements sequentially. Thus, the SQL Selector operator can not take advantage of the parallel processing environment that Teradata PT offers. Instead, break multiple Teradata SQL SELECT statement requests into single SELECT statements carried by multiple SQL Selector operators, and then unite these data sources together with the UNION clause.
LOB Loading Attributes	The following attributes: • LobDirectoryPath • LobFileBaseName • LobFileExtension are only for extracting LOB data in deferred mode. That is, when LOB columns are defined as follows in a schema: BLOB AS DEFERRED BY NAME CLOB AS DEFERRED BY NAME

Checkpointing and Restarting:

The SQL Selector operator takes a checkpoint only when all of the data is sent to the Teradata PT data stream.

Then, on restart, the operator either sends none of the data, all of the data, or terminates with an error message, depending on the status of the data:

• If all of 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 of the data is not sent, then the operator terminates. Restart the job from the

beginning.

• If none of the data is sent, then the operator sends the data.