where:
| Syntax Element | Description |
|---|---|
| ANSIDATE | Optional keyword specifying a date data type in
standard 10-character, dash-separated format. For example, October
23, 2007 is expressed as: 2007-10-23 |
| BIGINT | Optional keyword specifying an 8-byte binary integer numeric data type. |
| BLOB(lengthBytes) CLOB(lengthBytes) |
Optional keyword specifying an inline LOB (Large Object) column whose data is stored in the data rows defined by the schema just as non-LOB columns data is. An inline LOB is either of data type BLOB (Binary Large Object or CLOB (Character Large Object). lengthBytes specifies the maximum number of bytes of data that an inline LOB column value can contain in a data row and like a VARCHAR column, it can be of any size smaller than lengthBytes. The largest inline LOB supported by Teradata PT is 2,097,088,000 bytes on non-z/OS platforms. The largest inline LOB supported by Teradata PT is 64,000 bytes on z/OS platform. The DataConnector operator reads or writes LOB data from or to an external file. The SQL Selector operator extracts LOB data from the database and puts it into data streams. The SQL Inserter, Stream, and Update operators can read data from the data stream and load it into the database. Teradata PT may transfer inline LOBs in deferred mode from the producer to the consumer if the inline LOBs cannot fit within a single request message. The user does not have to specify anything. TPT will automatically perform the transfer as needed. This feature is only valid on non-z/OS platforms. An 8-byte length indicator precedes the inline LOB data if data rows are coming from a file read by a DataConnector operator. Other operators (Export and Load) cannot process LOB data.
|
| BLOB(lengthBytes) AS DEFERRED BY NAME CLOB(lengthBytes) AS DEFERRED BY NAME |
Optional keyword specifying a deferred LOB (Large Object) column whose data is contained in files rather than in the data rows defined by a Teradata PT schema. A deferred LOB is either of data type BLOB or CLOB. lengthBytes specifies the maximum number of bytes of data that a LOB column value can contain in the database and thus the maximum size of the files whose names are stored in the data rows. The maximum value of lengthBytes for deferred LOBs is 2,097,088,000. The names of the files that contain deferred LOB data are stored in data rows exactly like VARCHAR column values, with a 2-byte length prefix. The row value is either a fully qualified path name or the name of a file that Teradata PT looks for in the directory out of which it executes. The option AS DEFERRED BY NAME, which specifies a Teradata PT LOB file identifier, means that LOB data will be deferred and sent to the database separately from non-LOB data. The presence of the option AS DEFERRED BY NAME indicates that LOB file identifiers are expected in the data stream instead of the LOB data itself. The omission of AS DEFERRED BY NAME in the BLOB or CLOB column definition directs Teradata PT to send BLOB or CLOB data to the database in the data row, just like non-LOB data. The SQL Selector operator is the only producer operator that can extract LOB data from the database. The SQL Inserter, Update, and Stream operators are the only consumer operators that can load deferred LOBs into the database. Other operators (Export and Load) cannot process LOB data. Deferred LOB is not supported on z/OS platform.
|
| BYTE BYTE (lengthBytes) |
Optional keyword specifying a fixed-length column
containing binary data. lengthBytes specifies the column length in bytes. The maximum valid length is 64,000 bytes. If not specified, the default length is 1 byte. |
| BYTEINT | Optional keyword specifying a 1-byte integer, a whole number in the range -128 to +127. |
| CHAR CHARS CHARACTER CHARACTERS CHAR (lengthBytes) CHARS(lengthBytes) CHARACTER(lengthBytes) CHARACTERS(lengthBytes) |
Optional keyword that specifies a fixed-length column containing character data. lengthBytes specifies the column length in bytes. The maximum valid length is 64,000. If not specified, the default length is 1. |
| CHAR VARYING CHARS VARYING CHARACTER VARYING CHARACTERS VARYING CHAR VARYING(lengthBytes) CHARS VARYING(lengthBytes) CHARACTER VARYING(lengthBytes) CHARACTERS VARYING(lengthBytes) |
Optional keyword specifying a variable-length column containing character data. lengthBytes specifies the column length in bytes. The maximum valid length is 64,000. If not specified, the default length is 1. See description of VARCHAR. |
| columnName | Required user-supplied name of a column in the data source or data target described by the schema. Each specified column name must be unique within the schema. |
DECIMAL, DEC, or NUMERIC
|
Optional keyword defining a decimal numeric data type. This data type also defines the length in digits and decimal precision for the data column.
If the lengthDigits and precision values are not specified, the default values are used. Mismatching DECIMAL data are allowed as long as the job adheres to these three rules:
|
| DEFINE SCHEMA | Required keyword phrase specifying the beginning of the schema definition. |
| DELIMITED | Keyword indicating that Teradata PT is to generate the delimited-file format version of the Teradata PT schema based on one of the following: The table name specified:
|
| DESCRIPTION 'descriptionString' | Optional keyword phrase providing a descriptive comment about the defined schema. |
| FLOAT | Optional keyword defining the floating point data type. |
| FROM SELECT | Keyword phrase indicating that TPT is to generate a schema based on the columns in the result table of an SQL SELECT statement, specified either explicitly or indirectly via the SelectStmt attribute of operator operatorName. |
| FROM TABLE | Keyword phrase indicating that Teradata PT is to generate a schema based on the columns of the table identified via the quoted string tableName. |
| GRAPHIC GRAPHIC(lengthChars) |
Optional keyword specifying a fixed-length
column containing 2-byte graphic characters. lengthChars specifies the column length in 2-byte graphic characters. The maximum valid length is 32, 000 characters. If not specified, the default length is 1 character. The byte length of a GRAPHIC column is twice the value of lengthChars. |
| INTDATE | Optional keyword specifying a column containing
data values represented by 4-byte binary integers, calculated as
follows: intdate value = (year - 1900) * 10000 + month * 100 + day For example, the INTDATE column value for october 23, 2007 is 1071023 |
| INTEGER INT |
Optional keyword specifying a four-byte integer data type. |
| INTERVAL DAY INTERVAL DAY(lengthDigits) |
Optional keyword specifying a time interval column in days, where lengthDigits is the number of digits used to express the interval, populated from right to left. Valid values are 1 through 4. The default value is 2. External to the database, the values of INTERVAL columns are represented by character strings of various lengths and formats. Teradata PT, however, currently has no knowledge of these valid INTERVAL lengths and formats, and to Teradata PT, INTERVAL column values are indistinguishable from character strings in general. A job script can contain a character string that is intended to represent the value of an INTERVAL column, but Teradata PT cannot verify its validity. Assuming an INTERVAL column literal string is correctly formatted, it can reliably be compared to the corresponding type of INTERVAL column value in a data row only using either the "equals" (=) or the "not equals" (<>) comparison operators. For details on the valid lengths and formats of string representations of the values of INTERVAL column types, see Teradata Vantage™ - Data Types and Literals, B035-1143.
|
| INTERVAL DAY TO HOUR INTERVAL DAY(lengthDigits) TO HOUR |
Optional keyword specifying a time interval
column in days and hours stated in the named column, where lengthDigits is the number of digits
used to define the interval in days, populated from right to left.
Valid values are 1 through 4. The default value is 2. See Note for INTERVAL DAY. |
| INTERVAL DAY TO MINUTE INTERVAL DAY(lengthDigits) TO MINUTE |
Optional keyword specifying a time interval
column in days, hours, and minutes for the named column, where
lengthDigits is the number
of digits used to express the interval in days, populated from right
to left. Valid values are 1 through 4. The default value is 2. See Note for INTERVAL DAY. |
| INTERVAL DAY TO SECOND INTERVAL DAY(lengthDigits) TO SECOND INTERVAL DAY TO SECOND(precision) INTERVAL DAY(lengthDigits) TO SECOND(precision) |
Optional keyword specifying a time interval
column in days, hours, minutes, and seconds where the following is
true:
See Note for INTERVAL DAY. |
| INTERVAL HOUR INTERVAL HOUR(lengthDigits) |
Optional keyword specifying a time interval
column in hours, where lengthDigits is the number of digits used to express
the hour interval, populated from right to left. Valid values are 1 through 4, with a default value of 2. See Note for INTERVAL DAY. |
| INTERVAL HOUR TO MINUTE INTERVAL HOUR(lengthDigits) TO MINUTE |
Optional keyword specifying a time interval
column in hours and minutes, where lengthDigits is the number of digits used to express
the hour interval, populated from right to left. Valid values are 1 through 4, with a default value of 2. See Note for INTERVAL DAY. |
| INTERVAL HOUR TO SECOND INTERVAL HOUR(lengthDigits) TO SECOND INTERVAL HOUR TO SECOND(precision) INTERVAL HOUR(lengthDigits) TO SECOND (precision) |
Optional keyword specifying a time interval
column in hours, minutes, and seconds where:
See Note for INTERVAL DAY. |
| INTERVAL MINUTE INTERVAL MINUTE(lengthDigits) |
Optional keyword specifying a time interval
column in minutes, where lengthDigits is the number of digits used to express
the minute interval, populated from right to left. Valid values are 1 through 4, with a default value of 2. See Note for INTERVAL DAY. |
| INTERVAL MINUTE TO SECOND INTERVAL MINUTE(lengthDigits) TO SECOND (precision) INTERVAL MINUTE(lengthDigits) TO SECOND INTERVAL MINUTE TO SECOND(precision) |
Optional keyword specifying a time interval
column in minutes and seconds where:
See Note for INTERVAL DAY. |
| INTERVAL SECOND, INTERVAL SECOND(lengthDigits) INTERVAL SECOND(lengthDigits, precision) |
Optional keyword specifying a time interval
column in seconds, optionally to the nearest fraction of a second,
where:
See Note for INTERVAL DAY. |
| INTERVAL MONTH INTERVAL MONTH(lengthDigits) |
Optional keyword specifying a time interval in
months, where lengthDigits is
the number of digits used to express the months, populated from
right to left. Valid values are 1 through 4 with a default value of 2. See Note for INTERVAL DAY. |
| INTERVAL YEAR INTERVAL YEAR(lengthDigits) |
Optional keyword specifying a time interval
column in years, where lengthDigits is the number of digits used to express
the year, populated from right to left. Valid values are 1 through 4, with a default value of 2. See Note for INTERVAL DAY. |
| INTERVAL YEAR TO MONTH INTERVAL YEAR(lengthDigits) TO MONTH |
Optional keyword specifying a time interval
column in years and months, where lengthDigits is the number of digits used to express
the year, populated from right to left. Valid values are 1 through 4, with a default value of 2. See Note for INTERVAL DAY. |
| JSON(lengthBytes) JSON(lengthBytes) AS DEFERRED BY NAME |
Optional keyword specifying an inline JavaScript Object Notation (JSON) column whose data is stored in the data rows defined by the schema just as non-JSON column data is stored. lengthBytes specifies the maximum number of bytes of data that an in-line JSON column can contain in a data row and like a VARCHAR column, it can be any size smaller than lengthBytes. The largest inline JSON column supported by Teradata PT is 16,776,192 bytes on non-z/OS platforms. The largest inline JSON column supported by Teradata PT is 64,000 bytes on z/OS platform. The DataConnector operator reads and writes JSON data from and to an external file. The SQL Selector operator extracts JSON data from the database and puts it into data streams. The SQL Inserter, Update, and Stream operators read data from the data stream and load it into the database. Teradata PT may transfer inline JSON columns in deferred mode from the producer to the consumer if the inline JSON columns cannot fit within a single request message. The user does not have to specify anything. TPT will automatically perform the transfer as needed. This feature is only valid on non-z/OS platforms. An 8-byte length indicator precedes the in-line JSON data if data rows are coming from a file read by the DataConnector operator. Other operators (Export and Load) cannot process JSON data. AS DEFERRED BY NAME Optional keyword specifying a deferred JSON column whose data is contained in files rather than in the data rows defined by a Teradata PT schema. lengthBytes specifies the maximum number of bytes of data that a JSON column can contain in the database and thus the maximum size of the files whose names are stored in the data rows. The maximum value of lengthBytes for deferred JSON files is 16,776,192 bytes. The presence of the option AS DEFERRED BY NAME indicates that JSON file identifiers are expected in the data stream instead of the JSON data itself. The names of the files that contain deferred JSON data are stored in data rows exactly like VARCHAR column values, with a 2-byte length prefix. The row value is either a fully qualified path name or the name of a file that Teradata PT looks for in the directory out of which it executes.
SQL Selector operator is the only producer operator that can extract JSON data from the database. The SQL Inserter operator is the only consumer operator that can load deferred JSON files into the database. Other operators (Export, Load, Update and Stream) cannot process JSON data. Deferred JSON is not supported on z/OS platform.
|
| lengthBytes | Length specification for nonnumeric data types. lengthBytes specification is required for variable-length data types. When lengthBytes is specified for a CHAR or VARCHAR column encoded in UTF-16, it represents the number of bytes occupied by the column values, which is twice the character count that would be specified for length in an equivalent column description of a table schema.
|
| lengthChars | Optional keyword specifying the length in 2-byte graphic characters of the values of a GRAPHIC or VARGRAPHIC column. The byte length of these values is twice the value of lengthChars. |
| MACROCHARSET | Optional clause that defines the database server character set name for the character field. The MACROCHARSET is used to generate a CHARACTER SET <ServerCharsetName> clause for each field of character data type in the Stream Operator macro creation. If there is no MACROCHARSET clause for a field of character type, then the default will be one of the following:
The MACROCHARSET clause applies only to:
|
| macroCharSetIdentifier | Optional identifier that specifies the server
storage character set name the Stream Operator uses to generate a
CHARACTER SET clause for each character column when the Stream
Operator creates the macros that are necessary for loading the data.
The five possible valid values are as
follows:
Values specified for this identifier are meaningful in a DEFINE SCHEMA statement only when the schema is employed by an instance of the Stream operator. |
| METADATA (metadataType) | Optional keyword specifying that the value for this schema column is not included in the source data, but instead is metadata supplied directly by the operator itself. This feature can help with identifying the origin of the data once it has been loaded, and can also help locate invalid source records that cannot be loaded by the database. The following values can be used for "metadataType":
Only one entry for each "metadataType" is allowed. However, a METADATA column can occur anywhere within the schema. This feature is only valid when used with the $FILE_READER operator. All DataConnector format types are supported. See examples 6 and 7 in Examples of DEFINE SCHEMA Statements. |
| NUMBER NUMBER(*) NUMBER(*,scale) NUMBER(precision) NUMBER(precision,scale) |
Optional keyword specifying a column whose
values represent numbers up to 38 digits of precision, optionally up
to precision digits of scale with an optional exponent with the
range of:
|
| PERIOD(DATE) | Optional keyword specifying a column whose
values represent a duration of time, consisting of a beginning and
ending date value. The values of PERIOD columns are internally coded, both within the database and within Teradata PT. There is no character string representation of the value of any PERIOD column type in a job script, and two columns of the same PERIOD type cannot even be compared in a boolean predicate. For details on the internal structure of the values of PERIOD column types and how they are represented when retrieved through BTEQ, see Teradata Vantage™ - Data Types and Literals, B035-1143. |
| PERIOD(TIME) PERIOD(TIME(precision)) |
Optional keyword specifying a column whose
values represent a duration of time, consisting of a beginning and
an ending time value. precision specifies the number of fractional seconds digits, with valued values of 0 through 6. The default value is 6. See Note for PERIOD(DATE). |
| PERIOD (TIME WITH TIME ZONE) PERIOD(TIME(precision)WITH TIME ZONE) |
Optional keyword specifying a column whose
values represent a duration of time, consisting of a beginning and
an ending time-with-time-zone value. precision specifies the number of fractional seconds digits, with valid values of 0 through 6. The default value is 6. See Note for PERIOD(DATE). |
| PERIOD(TIMESTAMP) PERIOD(TIMESTAMP(precision)) |
Optional keyword specifying a column whose
values represent a duration of time, consisting of a beginning and
an ending timestamp value. precision specifies the number of fractional seconds digits, with valid values of 0 through 6. The default value is 6. See Note for PERIOD(DATE). |
| PERIOD(TIMESTAMP WITH TIME ZONE) PERIOD(TIMESTAMP(precision) WITH TIME ZONE) |
Optional keyword specifying a column whose
values represent a duration of time, consisting of a beginning and
an ending timestamp-with-time-zone value. precision specifies the number of fractional seconds digits, with valid values of 0 through 6. The default value is 6. See Note for PERIOD(DATE). |
| lengthDigits | Optional length value in digits for data types
that contain decimal numbers. The range and default value for lengthDigits vary according to the data type to which they are applied. For details see the individual data type descriptions in this table. |
| OF OPERATOR | Keyword phrase indicating that Teradata PT is to generate a schema based on the SQL SELECT statement assigned to the SelectStmt attribute of the script-defined operatorName. |
| operatorName | The name of an operator, defined in a DEFINE
OPERATOR statement, whose SQL SELECT statement is the basis for a
Teradata PT-generated schema. If there are no operators defined in the script, then the operatorName can be the name of an operator template specified in the APPLY-SELECT statement. |
| precision | Optional value that defines the number of digits
to the right of the decimal point. The range and default values for precision vary according to the data type to which they are applied. For details the individual data type descriptions in this table. |
| schemaObjectName | Required internal Teradata PT metadata name of the defined schema object. |
| SMALLINT | Optional keyword specifying a two-byte integer numeric data type. |
| sqlSelectStatement | An SQL SELECT statement whose result table columns are the basis for a Teradata PTgenerated schema. |
| tableName | The name of a database table whose column definitions Teradata PT uses as the basis for generating:
|
| TIME TIME(precision) |
Optional keyword specifying a column whose
values are time values. precision represents the number of digits to the right of the decimal point, indicating a decimal fraction of a second. Valid values for precision are 0 through 6. The default value is 6. In the 4 time-oriented column types - TIME, TIME WITH TIME ZONE, TIMESTAMP and TIMESTAMP WITH TIME ZONE - the default precision for the fractional seconds is 6, not zero. Thus TIME and TIME(6) represent identical columns, and you must specify TIME(0) if the time values will have no fractional seconds. External to the database, the values of these 4 time-oriented columns are represented by character strings of well-defined lengths and formats. Teradata PT, however, has no knowledge of these lengths or formats, and cannot verify the validity of job script character strings intended to represent values of these time-oriented columns. If a script literal string is a valid representation of the value of one of these column types, then all the comparison operators will correctly compare the value to row values of a column of exactly the same time-oriented type. Similarly, all comparison operators will correctly compare the row values of two columns of the same type. |
| TIME WITH TIME ZONE TIME(precision) WITH TIME ZONE |
Optional keyword specifying a column containing
time values that include a time zone offset. precision represents the number of digits to the right of the decimal point; a decimal fraction of a second. Valid values for precision are 0 through 6. The default value is 6. See Note for TIME. |
| USINGEXTENSION (‘usingExtension’) | Optional keyword specifying the USING clause extension for this schema column. For data loading, the operator generates the USING clause based on the schema. The operator sends the USING clause to the database. The USING clause identifies the layout of the data rows. The operator adds the UsingExtension value to the column when generating the USING clause. The operator does not validate the UsingExtension value. The UsingExtension value will be validated by the database. The USINGEXTENSION option applies only to the Teradata PT Update operator when the job is using the Extended MultiLoad Protocol. The USINGEXTENSION option is ignored when the job is using the traditional MultiLoad protocol. Also, the USINGEXTENSION option is ignored for other Teradata PT operators. The USINGEXTENSION option can be used to load data into a temporal table using the Teradata PT Update operator. For more information on how to use the USINGEXTENSION option, see Temporal Tables. |
| VARBYTE VARBYTE(lengthBytes) |
Optional keyword specifying a variable-length column containing binary data. lengthBytes specifies the maximum column length in bytes. If not specified, the default column length is 64,000 bytes, which is the maximum valid length. VARCHAR and VARBYTE data with a length less than or equal to the defined length in the DEFINE SCHEMA definition is allowed. |
| VARCHAR VARCHAR(lengthBytes) |
Optional keyword specifying a variable-length column containing character data. lengthBytes specifies the maximum column length in bytes. If not specified, the default column length is 64,000 bytes, which is the maximum valid length. |
| VARDATE(lengthBytes) FORMATIN(formatString) FORMATOUT(formatString) |
Optional keyword specifying a varying length character-based column whose data is to be reformatted before being loaded into a DateTime column. The FORMATIN string matches the format of the incoming DateTime data. The FORMATOUT string matches the format accepted by the DateTime column into which data is being loaded.lengthBytes must be greater than or equal to the size of the larger format string. See “Using VARDATE Columns to Reformat DateTime Data” in Teradata® Parallel Transporter User Guide, B035-2445 for information on, and examples of, reformatting DateTime data using the VARDATE column data type. |
| VARGRAPHIC VARGRAPHIC(lengthBytes) |
Optional keyword specifying a variable-length
column containing 2-byte graphic characters. lengthBytes specifies the column length in 2-byte graphic characters. If not specified, the default length is 32, 000 characters, which is the maximum valid length. The byte length of VARGRAPHIC column is twice the value of lengthChars. |
| XML XML AS DEFERRED BY NAME |
Optional keyword specifying an inline XML column whose data is stored in the data rows defined by the schema just as non-XML columns data is. Teradata PT defaults to size 2,097,088,000 bytes for inline XML columns on non-z/OS platforms. Teradata PT defaults to size 64,000 bytes for inline XML columns on z/OS platform. The DataConnector operator reads or writes XML data from or to an external file. The SQL Selector operator extracts XML data from the database and puts it into data streams. The SQL Inserter, Update, and Stream operators read data from the data stream and load it into the database. Teradata PT may transfer inline XML columns in deferred mode from the producer to the consumer if the inline XML columns cannot fit within a single request message. The user does not have to specify anything. TPT will automatically perform the transfer as needed. This feature is only valid on non-z/OS platforms. An 8-byte length indicator precedes the inline XML data if data rows are coming from a file read by a DataConnector operator. Other operators (Export and Load) cannot process XML data.
AS DEFERRED BY NAME Option specifying a deferred XML column whose data is contained in files rather than in the data rows defined by a Teradata PT schema. Teradata PT defaults to size 2,097,088,000 bytes for deferred XML columns. The option AS DEFERRED BY NAME, which specifies a Teradata PT XML file identifier, means that XML data is deferred and sent to the database separately from non-XML data. The names of the files that contain deferred XML data are stored in data rows exactly like VARCHAR column values, with a 2-byte length prefix. The row value is either a fully qualified path name or the name of a file that Teradata PT looks for in the directory out of which it executes.
SQL Selector operator is the only producer operator that can extract XML data from the database. The SQL Inserter, Update, and Stream operators can read XML data from the data stream and load it into the database. Other operators (Export and Load) cannot process XML data.
|