Reformatting a VB record with multiple variable length field

marc_holmes · New User Joined: 11 Jun 2014 Posts: 9 Location: England

I've got a VB file generated by a DB2 unload.

The first 5 bytes in each record are fixed. Field1.
Bytes 6 and 7 tell me (in hex) how long the next field is ("x1")
The next "x1" bytes are Field2.
The next two btes tell me how long Field3 is, followed by Field3, etc
And there's then one final fixed length field on the end.

e.g.
AAAAA..BBB..CCCCCCC..DDDDD..EEEEFFFF
(the dots represent field lengths for B, C and D and E, so x'0003', x'0007', x'0005' and x'0004' with FFFF being a fixed four byte field)

There are always the same number of fields (1 fixed + 4 variable + 1 fixed) and I know the maximum lengths of the variables.

I need this file either delimited or reformatted so all fields are fixed length.

Any help much apprciated.

Bill Woodger · Posted: Wed Jul 16, 2014 1:11 pm

OK, so you know the lengths... how big a secret is it?

If nothing is longer than 255 bytes, then you are guaranteed to have an X'00' separating each field (except the last).

If the fields can't be rearranged (last to first - whose idea was that?), then first thing will be JFY with SHIFT=RIGHT to jam the record into a fixed position, then JFY with SHIFT=LEFT to leave the fixed-length field behind.

Then you car PARSE using the X'00' and offsetting the start position of the PARSE by one for fields two, three and four (ADDPOS).

Then put together your PARSE'd fields, and pick up the fixed-length field you left after the end of the record.

PeterHolland · Posted: Wed Jul 16, 2014 1:11 pm

I thought that DB2 unload can reformat (delimit) data during unload.

Bill Woodger · Posted: Wed Jul 16, 2014 1:13 pm

An even better idea, Peter. If that can be done, it would be the best way.

marc_holmes · New User Joined: 11 Jun 2014 Posts: 9 Location: England

Unfortunately getting the file done properly is not a goer. It's used by other systems that we can't change and there's no budget for getting a duplicate file created from source... :-(

Bill, the largest of the four can be upto 1500 bytes (x'05DC') so that won't work either.

Bill Woodger · Posted: Wed Jul 16, 2014 1:38 pm

Is your data itself all "character" data?

marc_holmes · New User Joined: 11 Jun 2014 Posts: 9 Location: England

It's a mix of alpha and numeric, with some punctuation.

Bill Woodger · Posted: Wed Jul 16, 2014 2:27 pm

I'll take that as a Yes, then.

Means that X'00' through X'05' can't appear other than in the byte counts, but can appear in either byte of a count.

So, after the JFY stuff, FINDREP to replace the X'00' through X'05' by X'00'. Second FINREP to replace X'0000' by X'00FF' (doesn't matter what the FF part is, as long as it can't appear in character data). This is those where one of the small values happens to appear twice in the same two bytes.

Then proceed as before.

marc_holmes · New User Joined: 11 Jun 2014 Posts: 9 Location: England

Thanks, I'll give it a go.