Examples – twbertbl - Parallel Transporter

Teradata® Parallel Transporter Reference

Product
Parallel Transporter
Release Number
17.10
Published
February 2022
Language
English (United States)
Last Update
2023-11-29
dita:mapPath
abr1608578396711.ditamap
dita:ditavalPath
obe1474387269547.ditaval
dita:id
ogv1478610452101
Product Category
Teradata Tools and Utilities

Example 1

This example extracts the error information from the Load operator's acquisition phase error table. This example uses the sample schema for the Load operator job:

EMP_ID   VARCHAR(20),
EMP_NUM  VARCHAR(10)

This example uses this sample content for the Load operator's acquisition phase error table:

ErrorCode ErrorFieldName DataParcel
--------- -------------- ----------------------
2683      EMP_NUM        0003003130300300313238
  • Use the following twbertbl syntax example for commands on all platforms except z/OS:
    twbertbl -h <TdpId> -u <UserName> -t <ErrorTableName>
          -n <NumberOfLoadedColumns> -o <OutputFileName>
  • On z/OS, use the following syntax example inside a JCL:
    //TWBERTBL EXEC PGM=TWBERTBL,
    // PARAM='-h TDP0 -u MyUser -p MyPass -n 2 -t LoadOpErrTbl1
    //               -o DD:DATA'
  • After the extraction, the contents of the output file name will look like this, separated by a delimiter character:
    EMP_NUM|2683|100|128

    The first field in the output file name is the field name that caused each error.

    The second field is the error code for each error.

    The rest of the fields are the column values of the data records for each error.

Example 2

This example extracts the error information from the Update operator's acquisition phase error table.

This example uses this sample schema for the Update operator job:

EMP_ID   VARCHAR(20),
EMP_NUM  VARCHAR(10)

This example uses this sample content for the Update operator's acquisition phase error table:

ImportSeq DMLSeq SMTSeq ApplySeq SourceSeq
--------- ------ ------ -------- ---------
1         1      1      1        1

ErrorCode ErrorField HostData
--------- ---------- ----------------------
2683      EMP_NUM    0003003130300300313238
  • Use the following twbertbl syntax example for commands on all platforms except z/OS:
    twbertbl -h <TdpId> -u <UserName> -t <ErrorTableName>
          -n <NumberOfLoadedColumns> -o <OutputFileName>
  • On z/OS, use the following syntax example inside a JCL:
    //TWBERTBL EXEC PGM=TWBERTBL,
    // PARAM='-h TDP0 -u MyUser -p MyPass -n 2 -t UpdateOpErrTbl1
    //               -o DD:DATA'
  • After the extraction, the contents of the output file name will look like this, separated by a delimiter character:
    1|1|1|1|1|EMP_NUM|2683|100|128

    The first field in the output file name is the import sequence number that caused each error.

    The second field is the DML sequence number that caused each error.

    The third field is the statement sequence number that caused each error.

    The fourth field is the apply sequence number that caused each error.

    The fifth field is the row sequence number that caused each error.

    The sixth field is the error code for each error.

    The seventh field is the field name that caused each error.

    The rest of the fields are the column values of the data records for each error.

Example 3

This example extracts the error information from the Load operator's acquisition phase error table using Multi-byte character delimiter.

This example uses this sample schema for the Load operator job:

EMP_ID   VARCHAR(20),
EMP_NUM  VARCHAR(10)

This example uses this sample content for the Load operator's acquisition phase error table:

ErrorCode ErrorFieldName DataParcel
--------- -------------- ----------------------
2683      EMP_NUM        0003003130300300313238
  • Use the following twbertbl syntax example for commands on all platforms except z/OS:
    twbertbl -h <TdpId> -u <UserName> -t <ErrorTableName>
          -n <NumberOfLoadedColumns> -o <OutputFileName> -d <Multi-byte
          characters delimiter>
  • On z/OS, use the following syntax example inside a JCL:
    //TWBERTBL EXEC PGM=TWBERTBL,
    // PARAM='-h TDP0 -u MyUser -p MyPass -n 2 -t UpdateOpErrTbl1
    // -o DD:DATA -d aaa'
  • After the extraction, the contents of the output file name will look like this, separated by multi-byte characters delimiter ‘aaa’:
    EMP_NUMaaa2683aaa100aaa128

    The first field in the output file name is the field name that caused each error.

    The second field is the error code for each error.

    The rest of the fields are the column values of the data records for each error.

Example 4

This example extracts the error information from the Stream operator's error table.

This example uses this sample schema for the Stream operator job:
EMP_ID VARCHAR(20),
EMP_NUM VARCHAR(10)
This example uses this sample content for the Stream operator's error table:
LOADSTARTTIME              DMLSEQ SMTSEQ SOURCESEQ DATASEQ ERRORCODE
-------------------------- ------ ------ --------- ------- ---------
2018-01-24 17:35:26.000000 1      1      1         1       2617

ERRORMSG                                    
--------------------------------------------------------------------------------
Overflow occurred computing an expression involving STREAM_TARGET_EMP_TABLE.COL2

ERRORFIELD HOSTDATA
---------- ----------------------------------------------------
0          000B0020202020202020202020310A0031323820202020202020

ROWINSERTTIME
--------------------------
2018-01-24 17:35:29.360000
  • Use the following twbertbl syntax example for commands on all platforms except z/OS:
    twbertbl -h <TdpId> -u <UserName> -t <ErrorTableName>
             -n <NumberOfLoadedColumns> -o <OutputFileName>
  • On z/OS, use the following syntax example inside a JCL:
    //TWBERTBL EXEC PGM=TWBERTBL,
    // PARAM='-h TDP0 -u MyUser -p MyPass -n 2 -t StreamOpErrTbl
    //        -o DD:DATA'
  • After the extraction, the contents of the output file name will look like this, separated by a delimiter character:
    2018-01-24 17:35:26.000000|2018-01-24 17:35:29.360000|1|1|1|1|0|2617|Overflow occurred computing an expression involving STREAM_TARGET_EMP_TABLE.COL2|          1|128

The first field in the output file name is the Queue Insertion TimeStamp (QITS). The value indicates when the job started. On restart, it indicates when the job restarted.

The second field indicates when the row was inserted into the Stream operator error table.

The third field is sequence number assigned to the DML group in which the error occurred.

The fourth field is the sequence number of the DML statement within the DML group (as indicated by the previous column DMLSeq) being executed when the error occurred.

The fifth field is the sequence number assigned to the row from the input source (the DataSeq number) in which the error occurred.

The sixth field is the sequence number assigned to the input source in which the error occurred.

The seventh field is the field that caused the error. This field is always 0, because, currently, the database does not have a way to tell the TPT Stream operator which field in the data error record caused the error. However, the error message can indicate the column name that caused the data error.

The eighth field is the error code.

The ninth field is the error message.

The rest of the fields are the column values of the data records for each error.