Define Error Table for a PTI Table Example | Teradata Vantage - 17.00 - Example: Defining an Error Table for a PTI Table - Teradata Database

Teradata Vantageā„¢ - Time Series Tables and Operations

prodname
Advanced SQL Engine
Teradata Database
vrm_release
17.00
created_date
June 2020
category
Programming Reference
featnum
B035-1208-170K

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 );