Help creating a flat file using CPYTOIMPF

[ShaggyMoose]

I have a fixed format database file I want copied to a stream file or
another database file with a single VARLEN field. I need the data in a
fixed length format that correlates exactly with the DDS described
length of each field in the source file. However, all my attempts at
doing this seem to place extra padding (2 spaces) between each column!
The data is readable, but the format is broken. Any combination of
delimiter(s) doesn't seem to make any difference at all. What I want
to do seems incredibly simple, there must be an easy way to do this! I
am working on V5R3. Thanks for any assistance.

CPYTOIMPF FROMFILE(*LIBL/MY_SRC_FILE *FIRST) TOSTMF('MY_TGT_STREAM')
STMFCODPAG(*PCASCII) RCDDLM(*CRLF) DTAFMT(*FIXED) STRDLM(*NONE)
FLDDLM('')

CPYTOIMPF FROMFILE(*LIBL/MY_SRC_FILE *FIRST) TOFILE(*LIBL/MY_TGT_FILE)
DTAFMT(*FIXED) STRDLM(*NONE) FLDDLM('')

[CRPence]

Fixed format export does not mean to effect a direct correlation
"exactly with the DDS". It is neither reasonable nor even possible.
The point in database export is to change the data stored in the
database in its /internal representation/ into a common format, by
generating a purely text-based representation of the data. The integer
value 0x0000000F is the four byte signed decimal value 15, which when
represented in a /fixed format/ must be the equivalent of "+0000000015"
in its external text representation.

The layout of the /fixed format/ must account for that growth which
enables room for whichever data type. For decimal numeric that will be
room for the sign and decimal separator.

Regards, Chuck
--
All comments provided "as is" with no warranties of any kind
whatsoever and may not represent positions, strategies, nor views of my
employer

[ShaggyMoose]

On Feb 22, 5:00 pm, CRPence <crpe...@vnet.ibm.com> wrote:
> The layout of the /fixed format/ must account for that growth which
> enables room for whichever data type.  For decimal numeric that will be
> room for the sign and decimal separator.

Okay, fair enough. But if the field is declared with 0 decimal places
(as they are), wouldn't that make reserving a decimal point place
redundant? Regardless, is there any way to supress this "padding". I
can't see that there is an easy way to remove it after the file has
been created... Thanks again.

[Ron]

can you make an all character intermediate, or a copy to a 2048 no dds
file *nochk and then to your impf?

[CRPence]

As I recall the implementation is CHAR(numeric_field) so whatever the
implementation is for that SQL function, is what is result is generated.
Only the Integer data types are implied no scale, so the room is
reserved for both NUMERIC and DECIMAL.

So for example, DECIMAL(7, 0) is nine bytes CHAR.

select length(char(decimal(int(1), 7, 0)))
, char(decimal(int(1), 7, 0))
from sysibm.sysdummy1

....+....1....+....2....+
LENGTH CHAR
9 1
******** End of data ****

FWiW a database export feature is not intended to be a report writer,
so a few extra blanks or other nuances are not usually an issue. The
requirement is effectively, that given an FDF so the layout of the data
is properly described, to enable an import. Because the eventual text
transport is typically going to compress the data, the extra storage is
also typically not an issue. And where storage is an issue, a more
appropriate export format would be chosen; i.e. delimited versus fixed.
In V5R4 for delimited, as I recall, there is an option to strip
trailing blanks.

Regards, Chuck
--
All comments provided "as is" with no warranties of any kind
whatsoever and may not represent positions, strategies, nor views of my
employer

[Graybeard]

I would suggest using Ron's suggestion. Do a CRTPF with no DDS and
the proper length. Then do CPYF to the new file with *NOCHK
specified. You now have a flat file (one long field). Try
downloading this file.

[CRPence]

But CPYF FMTOPT(*NOCHK) to a program described [aka flat] file would
be generally valid *only* if all of the fields were character, fixed
length and of the same CCSID. In that case, what benefit is there in
even moving the data to an intermediate copy? That is, its current form
could presumably be transported directly as is, out of its current
location? For example, FTP PUT and GET, CPYTOTAP, SNDNETF, and direct
record-length data-record reads should be perfectly happy with that.

I suppose if the transport were limited only to CPYTOSTMF because
that interface does not support the externally described database file
directly. If something like that, then if an intermediate copy is
acceptable [as can be inferred] and the data is all character, then the
following:

DSPFD *LIBL/MYDATAFILE /* Get MaxRcdLen */
CRTSRCPF QTEMP/zMaxRcdLen RCDLEN(12+MaxRcdLen)
CPYF FROMFILE(*LIBL/MYDATAFILE) TOFILE(QTEMP/zMaxRcdLen)
FMTOPT(*CVTSRC) TOMBR(TheMbr) MBROPT(*REPLACE)
CPYTOSTMF
FROMMBR('/qsys.lib/qtemp.lib/zMaxRcdLn.FILE/TheMbr.MBR')
TOSTMF('MY_TGT_STREAM') STMFOPT(*REPLACE) DBFCCSID(*FILE)
STMFCODPAG(*PCASCII) ENDLINFMT(*CRLF)

Regards, Chuck
--
All comments provided "as is" with no warranties of any kind
whatsoever and may not represent positions, strategies, nor views of my
employer

[Thomas]

ShaggyMoose wrote:

> I have a fixed format database file I want copied to a stream file or
> another database file with a single VARLEN field. I need the data in a
> fixed length format that correlates exactly with the DDS described
> length of each field in the source file.

Consider using the Qshell db2 utility to SELECT the columns and CONCAT
their values (or substrings of character representations) into whatever
widths you need. Redirect Qshell output to a streamfile.

--
Tom Liotta
http://zap.to/tl400

[ShaggyMoose]

I ended up using SQL to convert all columns to CHAR and then using
CPYTOIMPF to append the records to a data file with a single CHAR
VARLEN field. I had to declare the DDS for this file, or I kept ending
up with a CCSID of *HEX.

SQL example: CHAR(CHAR(MY_DEC_FIELD),10) as MY_CHAR_FIELD

Using CPYF with *NOCHK was giving me the error "buffer length is
greater than record length", since the records I am appending are of
different sizes. Would be great if it allowed you to ignore this error
and just pad with blanks. I mean, its a character field! Anyway,
thanks for all the suggestions people. This turned out to be more of a
problem than I anticipated.

[CRPence]

The error could not be ignored by CPYF for a FMTOPT(*NOCHK) when
copying a CHAR() into a VARCHAR(), because the first two bytes of the
CHAR() were being treated as the length of the data; i.e. there was data
loss. The *NOCHK suggests to copy buffer to buffer, where the onus is
on the user to ensure all of the alignment of data within the buffer is
going to be valid for the target format. So ignoring the error would be
enabling data loss and incorrect output.

It is very unclear to me what is really being attempted; for lack of
an example. But what I can infer from what has been stated so far,
there is a lot of unnecessary work being done. For example, if the SQL
was used to generated the fixed data, it could have just as well been
used to effect the VARCHAR() result in the same request.? There should
be no reason to even make a temporary copy, as that SQL statement could
be used directly, either in a program or in a VIEW.

Regards, Chuck
--
All comments provided "as is" with no warranties of any kind
whatsoever and may not represent positions, strategies, nor views of my
employer