16.10 - Required and Optional Attributes - Parallel Transporter

Teradata Parallel Transporter Reference

Product
Parallel Transporter
Release Number
16.10
Published
July 2017
Content Type
Programming Reference
Publication ID
B035-2436-077K
Language
English (United States)

Use the attribute definition list syntax in the Teradata PT DEFINE OPERATOR statement to declare the required and optional attribute values for the ODBC operator.





where:

ODBC Operator Attribute Definitions 
Syntax Element... Description
AddBOMToFile = ‘option’ Optional attribute that specifies whether or not the UTF byte-order-mark (BOM) will be added at the beginning of an XML, JSON, or CLOB output data file.

Valid values are:

  • 'Y’ or ‘Yes' = The ODBC operator adds the appropriate UTF BOM at the beginning of an XML, JSON or CLOB output data file.
  • 'N’ or ‘No' = The ODBC operator does not prefix the appropriate UTF Byte-Order-Mark (BOM) at the beginning of an XML (JSON or CLOB) output data file (default).

AddBOMToFile can only be used when following two conditions are met:

The job schema has one or more XML, JSON, or CLOB column(s) defined AS DEFERRED BY NAME.

The client character set for the load job is the Unicode character set.

If these two conditions are not met, the values specified in the attribute are ignored.

The operator does not add a BOM to a data file extracted from a BLOB column in deferred mode.
ConnectString = 'connectString' Optional attribute that specifies an alternative method for connecting to the data source.

If specified, the settings in this attribute supersede any settings in the DSNname, UserName, and UserPassword attributes. This string can contain any valid connect and/or driver information that is acceptable to the ODBC driver used.

The settings in this attribute override the default settings in the ODBC initialization file.

DataBlockSize = KBytes Optional attribute that allows you to fine tune ODBC operator performance by adjusting dynamically the data block size of the buffer that will hold multiple rows with a single fetch call.

The size of the row affects the number of rows that can be fetched into that data block.

For information on attribute limitations, see DataBlockSize.

DSNname = 'dsnName' Optional attribute that specifies the name of the data source.

Only the system data source name can be used. User data source name does not work.

This name is also placed in the ODBC initialization file and can be used as a label in that file for providing connection information.

The setting in this attribute overrides the default setting in the ODBC initialization file. The ConnectString attribute overrides this setting.

LobDirectoryPath = ‘pathName Optional attribute that specifies the complete path name of an existing directory where all LOB, JSON, and XML data files will be written.
LobFileBaseName = ‘fileName Optional attribute that defines a character string that will be prefixed to the names of LOB, JSON, and XML data files. The file names created by the ODBC operator are in the following format:
<column-name>_<job-id>_p<#>_r<#>

where:

  • # that follows “p” is the identification of the ODBC Operator copy.
  • # that follows “r” is the row order returned from the Database.

For example, if you have the following schema:

DEFINE SCHEMA <schema-name>

(
COL2 BLOB AS DEFERRED BY NAME,
COL3 CLOB AS DEFERRED BY NAME,
COL4 XML AS DEFERRED BY NAME,
COL5 JSON(1000000) AS DEFERRED BY NAME,
 );

where LobFileBaseName has the value “my_test”, then the file names will be:

  • my_test_COL2_<job-id>_p1_r1
  • my_test_COL3_<job-id>_p1_r1
  • my_text_COL4_<job-id>_pl_r1
  • my_text_COL5_<job-id>_pl_r1
  • my_test COL2_<job-id>_p1_r2
  • my_test_COL3_<job-id>_p1_r2
  • my_test_COL4_<job-id>_pl_r2
  • my_test_COL5_<job-id>_pl_r2
  • my_test_COL2_<job-id>_p1_r3
  • my_test_COL3_<job-id>_p1_r3
  • my_test_COL4_<job-id>_pl_r3
  • my_test_COL5_<job-id>_pl_r3

and so on.

The files created will not have any extension unless specified in the LobFileExtension attribute.

LobFileExtension = ‘fileExtension Optional attribute that specifies the extension for LOB, JSON, and XML data file names.

Examples of ‘file-extensions’ include:

  • ‘jpg’: indicates that a file ‘ccc.jpg’ is a picture file.
  • ‘gif ’: indicates that the file ‘ddd.gif ’ is a picture file.
  • ‘html’: indicates that the file ‘aaa.html’ is an html file.
  • 'json': indicates that the file is a JSON file.
PrivateLogName = 'logName' 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 output provided by the operator.

The private log can be viewed using the tlogview command as follows, where jovbid is the Teradata PT job name and privatelogname is the value for the operator’s PrivateLogName attribute:

   tlogview -j jobid -f privatelogname

If the private log is not specified, all output is stored in the public log.

For more information about the tlogview command, see Teradata PT Utility Commands.

SelectStmt = 'SELECT statement(s);' Required attribute that specifies a SQL SELECT statement that is sent to the ODBC-compliant data source. Row data is returned in the form of a result table.

The operator does not parse the statement for validity. It is sent in its entirety without any type of processing.

The SELECT statement can be any SQL SELECT statement whose syntax is supported by the database against which the ODBC operator connects.

Not all databases support multistatement SELECTs. Therefore, because different data sources support different syntax, refer to the Teradata SQL reference for the specified ODBC-compliant data source, then enter only a valid SQL SELECT statement for the SelectStmt attribute.

TraceLevel = 'level' Optional attribute that specifies the types of diagnostic messages that are written by each instance of the operator to the public log (or private log, if one was specified using the PrivateLogName attribute). The diagnostic trace function provides more detailed information in the log file to aid in problem tracking and diagnosis.

The valid trace levels are:

  • 'None' = TraceLevel turned off (default).
  • 'PX' = enables the tracing function for activities related to the Teradata PT infrastructure
  • 'Oper' = enables the tracing function for operator-specific activities
  • 'All' = enables tracing for all of the above activities

The VARCHAR ARRAY can specify more than one value, for example:

 VARCHAR TraceLevel = 'PX'
   VARCHAR TraceLevel = 'OPER'
   VARCHAR ARRAY TraceLevel = [ 'PX' ]
   VARCHAR ARRAY TraceLevel = [ 'PX', 'OPER' ]
The TraceLevel attribute is provided as a diagnostic aid only. The amount and type of additional information provided by this attribute changes to meet evolving needs from release to release.
TruncateData = 'option' Attribute that specifies whether data is to be truncated, when it arrives from the data source, to fit into the Teradata-type of column.

This attribute is needed because databases other than Teradata (such as Oracle or DB2) might not use Teradata column types. For example, the maximum size of the DB2 column type LONG, which is equivalent to a VARCHAR column, is 2 MB. Teradata’s maximum column size is 1MB. You can extract data from that column (just because it is defined as 2 MB in size does not mean the actual data is that large), but if the actual data exceeds 1MB, the ODBC operator needs to know whether to truncate that data or to stop with an error.

The values are

  • 'Yes' ( or 'Y') = truncate data (default).
  • 'No' (or 'N') = do not truncate data.
UserName = 'userId' Optional attribute that specifies the user name of the account or database in the data source.

If not specified, then the ODBC driver looks in the initialization file for the user name information.

The setting in this attribute overrides the default setting in the ODBC initialization file. The ConnectString attribute overrides this setting.

UserPassword = 'password' Optional attribute that specifies the password associated with the UserName of the account or database in the data source.

If not specified, then the ODBC driver will look in the ODBC initialization file for the user password information.

The setting in this attribute will override the default setting in the ODBC initialization file. The ConnectString attribute will override this setting.