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.
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:
.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 */
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
SET RECORD FORMATTED;
/* All columns with a VARCHAR definition */)
BEGIN LOADING SYSDBA.MYTBL ERRORFILES SYSDBA.MYTBL1_ERR1 SYSDBA.MYTBL1_ERR2 ;
INSERT INTO "mydb"."MYTBL"
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.
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.