Processing DB2 Unload / DSNTIAUL data with DFSORT – VARCHAR Fields

(Originally posted 2005-02-21.)

In our process we unload the DB2 Catalog with DSNTIAUL and then use the resulting files in our analysis. When prototyping new code, or just plain trying different queries out, we use DFSORT.

However VARCHARs pose a bit of a problem, encoded as they are with a 2-byte length and padded with trailing nulls to the VARCHAR’s maximum length. If you want to include a record with a specific value shorter than the maximum length for that field you have to code something like

INCLUDE COND=(1,7,EQ,C'SYSPLAN')

where the field is 8 bytes wide. It would be much nicer if you could take advantage of the Symbol name for the full 8-byte field:
INCLUDE COND=(MYFIELD,EQ,C'SYSPLAN')


You could if the padding had been done with blanks (spaces) as DFSORT can cope with that. But we have nulls here, instead.

So here’s a snippet of code that uses the new IFTHEN support to convert trailing chars of a 4-byte field to something else:


INREC IFTHEN=(WHEN=(1,4,CH,EQ,C' '),HIT=NEXT,
OVERLAY=(1:C'....')),
IFTHEN=(WHEN=(2,3,CH,EQ,C' '),HIT=NEXT,
OVERLAY=(2:C'...')),
IFTHEN=(WHEN=(3,2,CH,EQ,C' '),HIT=NEXT,
OVERLAY=(3:C'..')),
IFTHEN=(WHEN=(4,1,CH,EQ,C' '),
OVERLAY=(4:C'.'))


Note: this example converts trailing blanks to full stops. But it could just as easily convert between any two code points.

Actually, in the above case you don’t actually need the HIT=NEXT for this simple example. It was extracted from a real example where I was doing this for two fields. And if you do have the HIT=NEXT version you can reduce each IFTHEN to acting on a single character.

I’ve tried informal tests with dozens of IFTHENs strung together and it didn’t seem to cost much.