Examples of DEFINE SCHEMA Statements - Parallel Transporter

Teradata Parallel Transporter Reference

Product
Parallel Transporter
Release Number
15.10
Language
English (United States)
Last Update
2018-10-07
dita:id
B035-2436
lifecycle
previous
Product Category
Teradata Tools and Utilities

Examples of DEFINE SCHEMA Statements

Example 1

DEFINE SCHEMA PRODUCT_SOURCE_SCHEMA
   DESCRIPTION 'PRODUCT INFORMATION'
(
   PRODUCT_NAME                 VARCHAR(24),
   PRODUCT_CODE                 INTEGER,
   PRODUCT_DESCRIPTION          VARCHAR(512),
   PRODUCT_COST                 INTEGER,
   PRODUCT_PRICE                INTEGER,
   PRODUCT_WARRANTY             		CLOB(50000),
   PRODUCT_PICTURE              BLOB (40000000) AS DEFERRED BY NAME
);

Example 2

DEFINE SCHEMA FREQ_FLYER_SRC
DESCRIPTION 'SCHEMA FOR FREQUENT FLYER SOURCE FILE'
(
   RES_ID                       VARCHAR(14),
   FLIGHT_NO                    SMALLINT,
   ORIGIN                       CHAR(3),
   DEST                         CHAR(3),
   TKT_ID                       CHAR(12),
   TKT_ISSUE_DATE               ANSIDATE,
   TKT_AMT                      DECIMAL(5,2),
   TRAVEL_AGCY_ID               CHAR(4),
   FREQ_MILES_ID                CHAR(8)
);

Example 3

DEFINE SCHEMA PRODUCT_SOURCE_SCHEMA
DESCRIPTION 'PRODUCT INFORMATION'
(
   PRODUCT_CODE                INTEGER,
   PRODUCT_NAME                VARCHAR(24)   MACROCHARSET UNICODE,
   PRODUCT_DESCRIPTION         VARCHAR(512)  MACROCHARSET LATIN,
   PRODUCT_TYPE                CHAR(2)       MACROCHARSET KANJI,
   PRODUCT_COST                INTEGER,
   PRODUCT_PRICE               INTEGER
);

Purpose  

The DEFINE OPERATOR statement defines a Teradata PT operator object. Operator objects identify the Teradata PT operators, that is, the job components that actually perform the movement of data from sources to targets, as well as other supporting job functions.

Teradata PT provides a set of standard (or predefined) operators. These operators can be identified by means of the TYPE keyword as follows:

 

TYPE

Definition

DATACONNECTOR PRODUCER

DataConnector operator used as a producer

DATACONNECTOR CONSUMER

DataConnector operator used as a consumer

DDL

DDL operator

EXPORT

Export operator

FASTEXPORT OUTMOD

FastExport OUTMOD Adapter operator

FASTLOAD INMOD

FastLoad INMOD Adapter operator

INSERTER

SQL Inserter operator

LOAD

Load operator

MULTILOAD INMOD

MultiLoad INMOD Adapter operator

ODBC

ODBC operator

OS COMMAND

OS Command operator

SCHEMAMAPPER

Schema Mapping operator

SELECTOR

SQL Selector operator

STREAM

Stream operator

UPDATE

Update operator

For details on the capabilities, attributes, and required syntax for each of these standard Teradata PT operators chapters 3 through 16.

Teradata PT allows you to define operators using a more generic use of the TYPE keyword. For example, those who wish to create their own operators must define TYPE using the following TYPE specifications:

 

TYPE

Definition

PRODUCER

Operators that produce a data stream from data received from a data source.

CONSUMER

Operators that consume data from a data stream and load it into a data target.

FILTER

Operators that filter data moving between a data source and a data target.

STANDALONE

Operators that work alone without receiving data from, or sending data to, other operators.

For details on creating customer-coded operators Teradata Parallel Transporter Operator Programmer Guide.

Teradata PT has internal knowledge of its standard operators, such as the operator library file and its message catalog file, so that these properties need not be specified in the DEFINE OPERATOR statement.

Teradata PT has no such knowledge about a customer-coded operator, so the former must be specified via the EXTERNAL NAME keyword phrase and the latter via the MSGCATALOG attribute, if a standard name is not used. See MSGCATALOG “catalog-name” in Appendix B.