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.