i have an unload with a select that will produce one only output record.
this record will be written to a sequential file.
can i split the output record into many records so that none of them is longer than 80 bytes?
i'd like to have everything done by a db2 select, if possible.
example:
the select:
Code:
SELECT COLA, COLB, COLC, COLD
FROM
TABLE WHERE COLX = 123;
produces the only record:
Code:
AAAABBBBCCCCDDDD
I'd like the output to be:
Code:
AAAABBBB
CCCCDDDD
this example is to simplify the question. the actual requirement is that i'd like to create a dfsort SYSIN file with a select and each SYSIN record can only be 80 bytes long.
Since you are using a SELECT, rather than a FETCH, AND since part of the output from the select includes a SORT FIELDS statement, I am led to assume that the select will always return only ONE result row.
If so, then given what you show as output there should be no need to "break up" the output into multiple records - the SORT products do not care how many spaces occur between keywords ( i.e. the COPY keyword and the INCLUDE keyword ), so you can code it all on a single record. So, as long as the total length of the SELECT output is less than 80 bytes, you should be good to go.
Now, if you REALLY need to break the output up into 80-byte records, then it is possible to use some "magic" to accomplish what you wish to achieve, but it requires "lying" to the machine. To wit:
Change your SELECT so that it always generates exactly 160 bytes of output. For example, if COLA is really 10 bytes long:
Code:
SELECT CHAR(' SORT FIELDS=COPY ')
, CHAR(' ') to make 1st rec = 80 bytes
, CHAR(' INCLUDE COND=(1,10,CH,EQ,')
, CHAR(X'7D')
, COLA
, CHAR(X'7D')
, CHAR (') ') to make 2nd rec = 80 bytes
FROM TABLE
WHERE COLB = 123;
Make sure that the output file is defined as RECFM=FB,LRECL=160
Now, for the actual SORT step that will use your generated SYSIN, define the file as RECFM=FB,LRECL=80 ( not 160, this is the 'lie' but the OS will accept it willingly ).
If your REAL output is larger, just be sure to generate exactly 80 bytes for each desired output line, and code the output LRECL as a multiple of 80, but always "lie" to the SORT by "overriding" the LRECL to just 80.
Marso,
I'm not sure that what you posted will actually work - that is, attempting to directly concatenate a DD * with a DD DSN.
The way I've always seen it done ( and have done so successfully myself ) is to code like:
