Convert HostData to actual record from Error Table

Tools

Convert HostData to actual record from Error Table

Hi,

We have some jobs loading data from TPT which creates ET for duplicate records.

Does anyone know how to convert Hostdata values into actual record from ET? I have limitations, can not user fastexport or Varbyte2Varchar UDF.

Does anyone has any idea or utility which can convert this Hex data into actual record? I tried different approaches to convert Hex to Text, but it seems it is not simple conversion.

Urgent help will be highly appreciated.

2 REPLIES
Teradata Employee

Re: Convert HostData to actual record from Error Table

I think you are referring to the DataParcel column in the error tables and how to view it. If that is the case, below is a method for that. You can use TPT instead of Fastload.

Given a Fastload error table, use the following bteq script to export the dataparcel column to a file:

.Sessions 6;

.LOGON tdpid/sysdba,pwd;

DATABASE "sysdba";

.export DATA file = MYTBL.err;

.set width 64000;

.set recordmode on ;

.set titledashes off ;

  SELECT dataparcel (title '')  from SYSDBA.MYTBL_err1 ;  /* NOTE: replace SYSDBA.MYTBL_err1 with appropriate error table */

.export reset;

.quit;

Then, use the following fastload script, that loads to the sysdba. MYTBL table (which I assume has the same definition as the original target table that was fastloaded with error).

Essentially, this uses RECORD FORMATTED. The data file is MYTBL.err which was exported in step 1

SESSIONS 2;

TENACITY 4;

SLEEP 3;

LOGON TDPID/sysdba,pwd;

ERRLIMIT 1000;

SET RECORD FORMATTED; 

DEFINE

     Dummy0 (Smallint),

     Dummy (INTEGER),

    "LOCID" (VARCHAR(20),nullif=''),

/* All columns with a VARCHAR definition */)

FILE=MYTBL.err;

BEGIN LOADING SYSDBA.MYTBL ERRORFILES  SYSDBA.MYTBL1_ERR1 SYSDBA.MYTBL1_ERR2 ;

INSERT INTO "mydb"."MYTBL"

    (

    "LOCID",

    )

VALUES

    (

    :"LOCID" ,

   …

    );

END LOADING;

LOGOFF;

Each row of MYTBL_err has the following format:

1. First 2 bytes are dataparcel length (this goes into dummy0)

2. N bits with NULL indicators - one per column, rounded to the next byte. (in this example 30 bits rounded to 4 bytes). (This goes in dummy1).

3. VARCHAR(xx) definition for each column for the table (30 in this example).

This will load the error rows into sysdba.MYTBL which can be selected for viewing.

Teradata Employee

Re: Convert HostData to actual record from Error Table

This will only work if your original data was delimited.

This will not work if your data was originally loaded from one of the binary record formats.

You have to also be careful because the data is stored in a VARBYTE which means it does not perform any character set (or target architecture) conversion. Make sure you extract the data on a machine that has the same target architecture (and character set) as the original job.

-- SteveF