How to interpret or decode the Hostdata column from Teradata error table

Question

The use case is that, there is an Informatica Cloud mapping which loads from SQL Server to Teradata database. If there any failures during the run time of the mapping then that mappings writes all the failed rows to a table in Teradata database. The key column in this error table is HOSTDATA which I assume. I am trying to decode the HOSTDATA column so that if a similar ETL failure happens in the production then it would be helpful in identifying the root cause much quickly. By default HOSTDATA is a column of type VARBYTES.

To decode the HOSTDATA column, converted the column to ASCII and Base 16 format. None of them made any use.

Then tried the below from the Teradata forum.

Then tried to extract the data from the error table using a BTEQ script. For that the data is being exported into a .err file and it is being loaded back into the Teradata database using a fastload script. Fastload is unable to load the data because there is no specific delimiter for the data. There data in the .err file looks gibberish. Snapshot of the data from the .err file:

My end goal is to interpret the Hostdata column in a more human readable way. Any suggestions in this direction are also welcome.

It's my understanding that what you are seeing is the buffer returned by the ODBC driver (sql server in this case) dumped into the err table. My guess, and I don't have an answer here, is that in order to shred this thing into human readable you would to treat this file as an odbc buffer using the ODBC standard and parse your way through the thing byte by byte. I don't know that a tool/library already exists so you don't have to dig through ODBC standards documentation and reinvent the wheel. — JNevill
– JNevill, Commented Mar 3, 2021 at 16:28
btw - from the screenshot I would say this load used FORMATTED record format. — Fred
– Fred, Commented Mar 3, 2021 at 23:01
You pointed me in the right direction. I will explore more about twbertbl. Thanks @Fred. — Teja Goud Kandula
– Teja Goud Kandula, Commented Mar 5, 2021 at 0:56
@Fred, you can post it as an answer (add few details if you wanted to) so that I can choose it. :) — Teja Goud Kandula
– Teja Goud Kandula, Commented Mar 5, 2021 at 9:15

Fred · Accepted Answer · 2021-03-05 17:07:40Z

1

The Error Table Extractor command twbertbl which is part of "Teradata Parallel Transporter Base" software is designed to extract and format HOSTDATA from the error table VARBYTE column.

Based on the screenshot in your question, I suspect you will need to specify FORMATTED as the record format option for twbertbl (default is DELIMITED).

answered Mar 5, 2021 at 17:07

Fred

2,0991 gold badge12 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to interpret or decode the Hostdata column from Teradata error table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related