16.20 - CREATE ERROR TABLE - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantage™ Time Series Tables and Operations

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Published
March 2019
Content Type
Programming Reference
Publication ID
B035-1208-162K
Language
English (United States)
Last Update
2019-05-02

You can define an error table for a PTI table. For complete information about CREATE ERROR TABLE syntax, see Teradata Vantage™ SQL Data Definition Language Syntax and Examples, B035-1144.

The system generated time series columns for a PTI table are included in the error table, but without the column attributes specific to time series. The error table columns do not include the NOT NULL GENERATED SYSTEM TIMECOLUMN attributes.

Example: Defining an Error Table for a PTI Table

Following is the table definition for this example.

CREATE TABLE ocean_buoy(
     TD_TIMEBUCKET BIGINT NOT NULL GENERATED SYSTEM TIMECOLUMN,
      TD_TIMECODE TIMESTAMP(6) NOT NULL GENERATED TIMECOLUMN,
      TD_SEQNO INT NOT NULL GENERATED TIMECOLUMN,
      c1 INTEGER,
      c2 INTEGER)
 PRIMARY TIME INDEX(TIMESTAMP(6), DATE '2015-05-02', HOURS(1), COLUMNS(c1,c2), SEQUENCED(20000));

You can use the statement below to create an error table for PTI table ocean_buoy. By default, the error table name begins with the prefix et_ followed by the name of the base table ocean_buoy.

CREATE ERROR TABLE FOR ocean_buoy;

You can use a SHOW TABLE statement to display the error table definition. In the error table, the NOT NULL GENERATED TIMECOLUMN attributes specific to time series are not included for the TD_TIMEBUCKET, TD_TIMECODE, and TD_SEQNO columns, in addition to the SYSTEM attribute for the TD_TIMEBUCKET column.

CREATE MULTISET TABLE et_ocean_buoy ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO,
     MAP = TD_MAP1
    (
      TD_TIMEBUCKET BIGINT,
      TD_TIMECODE TIMESTAMP(6),
      TD_SEQNO INTEGER,
      c1 INTEGER,
      c2 INTEGER,
      ETC_DBQL_QID DECIMAL(18,0) FORMAT '-(18)9' NOT NULL,
      ETC_DMLType CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC,
      ETC_ErrorCode INTEGER NOT NULL,
      ETC_ErrSeq INTEGER NOT NULL,
      ETC_IndexNumber SMALLINT,
      ETC_IdxErrType CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC,
      ETC_RowId BYTE(16),
      ETC_TableId BYTE(6),
      ETC_FieldId SMALLINT,
      ETC_RITableId BYTE(6),
      ETC_RIFieldId SMALLINT,
      ETC_TimeStamp TIMESTAMP(2) NOT NULL,
      ETC_Blob BLOB(2033152))
PRIMARY INDEX mytsinx ( TD_TIMEBUCKET ,c1 ,c2 );