MVSFORUMS.com

agredell · Beginner Joined: 04 May 2006 Posts: 27 Topics: 6

I am creating a bunch of files (DB2 Unload with concatenation of fields in order to FTP to a vendor) of various sizes. The vendor can't take the length field, so I'm going to ship them FB, but I need to strip the first two (length) bytes off of every record in every file. Can someone help me figure out how? Thanks, in advance!
_________________
Alan Gredell

kolusu · Posted: Mon May 08, 2006 9:25 am Post subject:

agredell,

Why would a DB2 Unload create a VB file? Unload creates a Fixed block file.

agredell · Beginner Joined: 04 May 2006 Posts: 27 Topics: 6

Some of the columns are variable length, here is a snippet of the select

agredell · Beginner Joined: 04 May 2006 Posts: 27 Topics: 6

I should add that I'm using DSNTIAUL, not REORG UNLOAD ONLY.
_________________
Alan Gredell

kolusu · Posted: Mon May 08, 2006 11:00 am Post subject:

agredell,

You are getting the 2 extra bytes because you are not using CHAR function on constants like ", |. change your sql to the following

agredell · Beginner Joined: 04 May 2006 Posts: 27 Topics: 6

Thanks for trying, kolusu, but CHAR didn't work that way. I still got a variable length record, though all of the records are of the same length. I have gotten the results I need through using SORT with an OUTREC parm (I haven't used SORT in over 20 years!). My last challenge is to append an ASCII CR/LF (Carriage Return/Line Feed) character as the end of each line! I see in the OUTREC parm how that is done, but don't know the correct value to use.
_________________
Alan Gredell

kolusu · Posted: Mon May 08, 2006 3:15 pm Post subject:

agredell · Beginner Joined: 04 May 2006 Posts: 27 Topics: 6

I must have miscoded a line somewhere. I tried just one column, and it worked like you said; added a second column, it still worked like you said. I will slowly build and test until I find either a specific column that messes it up, or assume that I had performed a "finger check" when coding this stuff.

But I still need to add an ASCII CR/LF at the end. Got any ideas for that?

Thanks a bunch for sticking with this!
_________________
Alan Gredell

kolusu · Posted: Mon May 08, 2006 7:00 pm Post subject:

agredell · Beginner Joined: 04 May 2006 Posts: 27 Topics: 6

Kolusu, here is an update! The problem causing variable length output was occurring anytime a record exceeded 254 bytes. Using commas instead of CONCAT was of some help when I reach that size for a field. I tried using commas instead of CONCAT, but that puts a low-values character between my data fields, which is unacceptable output for the vendor. The CR/LF suggestion you made is putting out the correct code, but there is an additional byte, with low-values, after that, which I can't explain. Wow, I didn't know unload was so rough!!!
_________________
Alan Gredell

kolusu · Posted: Tue May 09, 2006 2:12 pm Post subject:

agredell,

agredcell,

I just don't understand as to why you need CONCAT on all fields. If your intention is to enclose the columns within double quotes and put a seperator after that you can do it without using CONCAT function.

ex: if you db2 table has 2 columns name and website with following data

agredell · Beginner Joined: 04 May 2006 Posts: 27 Topics: 6

But when I do that (I have also tried just as you suggest), it comes out more like:

kolusu · Posted: Tue May 09, 2006 2:29 pm Post subject:

agredell · Beginner Joined: 04 May 2006 Posts: 27 Topics: 6

You are very patient with me! All of the fields are nullable, but I have already checked which actually contain null values (this particular table is very stable, it is the time period table for a data warehouse). I use this SQL statement:

kolusu · Posted: Tue May 09, 2006 5:06 pm Post subject: