We are considering writing a tool to do this and so I have been taking a look at the contents of the DataParcel field.
The DBS places the record, just as the client software sends it, into that field in the table.
That field is a VARBYTE. This means there is no translation taking place by the DBS. The data is in its raw form.
When a customer tries to load the data from a delimited data file, the client application converts the data to a series of VARCHAR fields. Each VARCHAR field has a 2-byte length indicator.
The 2-byte field length indicators will be byte-ordered according to the platform architecture from which the job originated.
Also, the entire record has a 2-byte record length. And if the data was specified in indicator mode, the data will also have indicator bytes.
BTEQ is going to extract out the raw data from the field and write it out. BTEQ, will be providing the record length (and parcel length) indicators and will byte-order them according to the platform architecture on which BTEQ is running.
Thus, you need to know quite a bit of information in order to process that data back to delimited records. You will need to know how to process the indicator bytes, you will need to know how to process the 2-byte field lengths and then process the data and place delimiter characters.
BTEQ cannot so this. You will have to do a lot of this on your own.
So currently there is no tool which can extract the dataparcel without the indicator bytes ?
Fast Export / Import or anything like that?
Does anyone have a process for identifying, then reprocessing the failed records before the next daily incremental load?
No, there is currently no tool.
Although, it is not really that difficult for you to do it on your own. But you either have to have a hex editor or write a simple C program to process the exported data from BTEQ.
Hi
We've just released an alpha version of a tool which can import/export data from teradata to csv - it might be what you're looking for. We have only tested it on our own machines, so ymmv but you can get more info from https://github.com/xlfe/dwhwrapper
I just read the notes on that tool.
Are you telling me that for any Teradata FastLoad job that resulted in rows ending up in the error table, that your tool will be able to issue this command:
$ dwh get output.csv 'select DataParcel from <error table name>'
and the tool would be able to properly extract out the data from that VARBYTE field and write out the data in CSV format?
Please show me an example.
Ah sorry - no it won't do that out of the box.
It does decode/encode Fastexport binary format ('VARCHAR','CHAR','DECIMAL','FLOAT', 'INTEGER','SMALLINT','DATE','BYTEINT' currently only) including nulls/indicdata, but you would need to supply the field types (and do some python hacking) because it currently assumes that the fields are defined by the select statement (which is not the case in your example)
The topic of the thread was about processing data in a FastLoad error table.
As much as I appreciate your desire to promote your conversion tool, it would not work for the problem described in this thread.
It has quite a few limitations.
Thanks for the feedback.
So just to confirm there is no way to decode DataParcel field in _ET table, right?
If so this is very inconvenient because I have no idea how to find out which 50K records failed out of my million record load and why...
For example, I see the error has something to do with a timestamp field but I cannot understand why as there is no way to look at the actual erroneous input data.
Seems like this enhancement should get hundreds of votes...
Well, there is a way to decode the data (we are working on a tool for that right now). The tool, however, will work with jobs run by TPT.
However, you do need to know how the data was originally sent to the DBS.
If the data was provided to the loading tool in "VARTEXT" (delimited) format, the decoding of the DataParcel data is quite easy.
If the data was sent as binary data, then you would need to convert the data.
Not impossible. But you will need to know the layout of the data as it was sent to Teradata so that you can parse through to the correct column.
The error code tells you the DBS error. The ErrorFieldName tells you which field it was.
Well, the file is tab delimited text file but when I look at the DataParcel column it looks binary to me.
I know what field has a problem but the data looks binary so I cannot figure out where my column is.
We use tdload to load the data. So how do I decode it?
If the file was TAB delimited, then the data was sent in character format (just a series of VARCHARs).
So, I believe DataParcel will first contain a 2-byte record length, followed by the indicator bytes, followed by a series of VARCHARs. Each VARCHAR is made up of a 2-byte length, followed by the character data.
There will be 1 indicator byte for every 8 columns of data sent to the database.
From this information you should be able to extract out the data.
If you scan past the record length and the indicator bytes, and interpret each 2-byte VARCHAR length, I think you will see the characters pretty easily.
Hi, I'm new to Teradata and need to do the following:
I'm on a Unix system, using verision 13.0.
We're inserting data into the "Stage" table from a delimited text file (bell is the delimeter \007)
1. export the dataparcel field from the _ET table to a file (delimited text if possible)
2. fix the contents of the file
3. import the corrected file into the "Stage" table
This is my bteq export command:
.SET RECORDMODE ON
.EXPORT DATA FILE=bteq_ET_SELECT.out
LOCKING ROW FOR ACCESS
SELECT DataParcel
FROM DEV_CONTR_STG.R_SLS_ORD_TYP_ET
;
.EXPORT RESET;
.LOGOFF;
.QUIT;
Results:
SQp^F000186^Z2011-05-12-21.20.21.613001^Z2011-05-12- 21.20.21.613001^HDEVETL
SQp^F000192^Z2011-05-12-21.23.56.659506^Z2011-05-12- 21.23.56.659506^HDEVETL
I've tried different combinations using RECORDMODE and INDICDATA with .SET separator "|" , but can never export the DataParcel as delimited text.
There are null fields in the DataParcel, but these null fields don't show up as fields at all in the dataparcel output file. (e.g. when I use SQL Assistant to view the dataparcel, I do see the null fields)
I read in another post that you don't need to specify the 1st field above on the import because BTEQ adds the "fast load" format to the beginning of the record and to each field in the record.
----
Then I try to import my "exported file" from above
.IMPORT DATA FILE=bteq_ET_SELECT.out
.REPEAT *
using(fld1 varchar(6),
fld2 varchar(26),
fld3 varchar(26),
fld4 varchar(20)
)
insert into dev_contr_stg.ttd_err_tst
(sls_ord_typ_cd,row_insrt_tms,row_updt_tms,row_usr_id)
values (:fld1,
:fld2,
:fld3,
:fld4);
.LOGOFF;
.QUIT;
*** Starting Row 0 at Mon Feb 20 17:32:35 2012
*** Failure 2673 The source parcel length does not match data that was defined.
Statement# 1, Info =1
*** Total elapsed time was 1 second.
I've tried different definitions, I've tried even exporting valid data from a table & importing it BUT I get the same error.
Thanks for any help!