10.1 Teradata Parallel Transporter - SQL inserter Operator - Basics

SQL Inserter Operator Capabilities:

• SQL inserter is a consumer operator.
   
• Used to insert data in the table.

• SQL inserter supports multiple instances and each instance can have one session only. (remember SQL selector didn’t allow multiple instances )

• The target table can be empty or can have data in it.

• To load large amounts of data, it is generally better to use the Load operator, but for smaller load jobs, the SQL Inserter might perform better than most load jobs because it does not need to set up multiple sessions to run.

Also, the SQL Inserter operator does not require an active load job. It simply uses standard SQL protocol on a single session.

SQL Inserter Operator Definition:

Following is an example of a SQL Inserter operator definition.

Only key attributes are shown (in bold).

DEFINE OPERATOR SQL_INSERTER

DESCRIPTION 'Teradata PT SQL Inserter Operator'

TYPE INSERTER --> Note we don’t use the word SQL

SCHEMA *

ATTRIBUTES

(

VARCHAR Tdpid,

VARCHAR UserName,

VARCHAR UserPassword,

VARCHAR PrivateLogName

);

TDPID :	It’s a optional Security attribute that specifies the name of the Teradata database machine(non-mainframe platforms) or TDP(mainframe platforms) for SQL inserter 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.

Data Integrity With SQL inserter:

To protect data integrity, the SQL Inserter operator treats the entire loading job as a single explicit transaction.

If any error is encountered during the insert operation, the SQL Inserter operator backs out all rows of data inserted up to that point.

Checkpointing and Restarting

The SQL Inserter operator takes two basic checkpoints, a start-of-data checkpoint and an endof-data checkpoint, during a load operation sothat the SQL Inserter operator can restart automatically when the load operation is interrupted by a Teradata Database restart, should one occur.

Additional attributes:

Attribute	Required/Optional	Description
AccountId = 'acctid'	O	Same as that in Export Operator
DataEncryption = ‘option’	O	Same as that in Export Operator
DateForm = 'datatype'	O	Same as that in Export Operator
LogonMech = ‘string’	O	Same as that in Export Operator
LogonMechData = ‘data’	O	Same as that in Export Operator
LogSQL = 'option'	O	Same as that in Export Operator
PrivateLogName = 'logname'	O	Same as that in Export Operator
QueryBandSessInfo = ‘Query Band expression’	O	Same as that in Export Operator
TdpId = 'dbcname'	O	Same as that in Export Operator
TraceLevel = 'level'	O	Same as that in Export Operator
WorkingDatabase = ‘dbname’	O	Same as that in Export Operator