bteq export and import for _ET dataparcel

Tools

bteq export and import for _ET dataparcel

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!

26 REPLIES
Teradata Employee

Re: bteq export and import for _ET dataparcel

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.

-- SteveF

Re: bteq export and import for _ET dataparcel

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?

Teradata Employee

Re: bteq export and import for _ET dataparcel

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.

-- SteveF

Re: bteq export and import for _ET dataparcel

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

Teradata Employee

Re: bteq export and import for _ET dataparcel

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.

-- SteveF

Re: bteq export and import for _ET dataparcel

Ah sorry - no it won't do that out of the box.

Re: bteq export and import for _ET dataparcel

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)

Teradata Employee

Re: bteq export and import for _ET dataparcel

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.

-- SteveF

Re: bteq export and import for _ET dataparcel

Thanks for the feedback.