All Forums Tools
JimTepin 8 posts Joined 08/11
18 Sep 2015
FastExport Remove Binary/Indicator Values in Outmod

This is an old question, and I believe the only solution to be an outmod, but I have never coded one (linux environment).  I am actually exporting SQL (from DBQL).  BTEQ will not honor the carriage return/line feeds with it's export, and FastExport adds the (sometimes) binary field lengths to each record.  Given the theoretical limits of what I am exporting, SQL, I don't want to cast every record as some monster CHAR field.
Because the resulting file has linefeeds, I can't remove the data "post process".  I have found various ways to remove binary data, but those first 2 characters are not always non-printable.
Does anyone actually have an outmod (assuming that is the only way to glory) for linux that will work?

jlasheras 6 posts Joined 08/12
18 Sep 2015

Hi Jim,
Those first 2 bytes (not characters) generated by fexp are what was called a VLI - record variable length indicator.
It's a 16-bit data structure containing an integer in binary form equal to the number of bytes in the record.
Sometimes it may look like having one or two "printable" characters, just because the binary structure matches a character internal representation.
This means the exported file has variable length records, the VLI is used to navigate each data block, i.e. next record begins in current record initial relative position plus the VLI value.
The file has variable length records because the SELECT parameter in the fexp script projects variable length columns, like for instance anyone defined as VARCHAR.
You can avoid the VLI if you take care to CAST all variable length columns to the projected maximum size.
To write and implement an outmod is fun, I did that sometimes, if you would really still want to take a look at one I may post a sample, but I don't know if it's worth to use an outmod to solve your problem.
Thank you

JimTepin 8 posts Joined 08/11
18 Sep 2015

Thanks for the response...
I did wind up going the route of casting as CHAR(32000)... But did so begrudgingly :).  I guess it's easier to truncate white space than to solve this riddle with an inmod.
But, it would be nice, in this day and age of large text (with line feeds), to have a simple bulk utility that can extract such data without so much trouble.

Fred 1096 posts Joined 08/04
19 Sep 2015

The OUTMOD coding for this is pretty trivial. Start with the example in the manual. For the "process response record" case, instead of setting input and output lengths both to zero (which means "omit this record"), set output length = input length-2. Then memcpy that many bytes to the output buffer from the input buffer+2. I would also remove the sample printf's from the other cases, but that's up to you.

pbvillaflores 1 post Joined 07/15
30 Aug 2016

Is there an example of OUTMOD somewhere that I can reference?

Ivyuan 63 posts Joined 01/10
30 Aug 2016

There are some sample OUTMOD routines in Teradata FastExport manual(B035-2410) Appendix C.

You must sign in to leave a comment.