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.
This example extracts the error information from the Stream operator's error table.
EMP_ID VARCHAR(20), EMP_NUM VARCHAR(10)
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.