Creating a Field Definition File for Use with the CPYFRMIMPF Command

Technote (troubleshooting)


Problem(Abstract)

This document provides instructions and an example for creating a Field Definition File (FDF) that can be used to copy Fixed Format data with the CPYFRMIMPF command.

Resolving the problem

Data, that you want to import into a Database File, can be in either a delimited or fixed format.

Example of Delimited Data in an IFS Stream File:

*********Beginning of data************
"a ","b "
"c ","d "
"e ","f "
"g ","h "
"i ","j "
"k ","l "
"m ","n "
************End of Data***************

Example of Fixed Format Data in an IFS Stream File:

*********Beginning of data************
a b
c d
e f
g h
i j
k l
m n
************End of Data***************                                   
 
If the data, that you want to import, is in fixed format, the CPYFRMIMPF command must use a user-defined Field Definition File to define the layout of the fixed formatted file (or stream file). A Field Definition File is used to describe (to the TOFILE) where fields (in the FROMFILE or FROMSTMF) begin and end.

Example

CPYFRMIMPF FROMSTMF('/dir1/FixedFormatFile.stmf') TOFILE(MYLIB/TOFILE) +
RCDDLM(*CRLF) DTAFMT(*FIXED) FLDDFNFILE(MYLIB/FDF)

The Field Definition File (MYLIB/FDF) can be the following:

o Source physical file
o DDM file
o Distributed physical file
o Program-described physical file
o Externally-described physical file with one field
For this example, the Field Definition File will be an externally described physical file with one field.

The DDS for the FLDDFNFILE(MYLIB/FDF) looked like this:

.....A..........T.Name++++++RLen++TDpB......
*************** Beginning of data **********
R FDFREC
ONEFLD 20
****************** End of data *************
CRTPF FILE(MYLIB/FDF)
SRCFILE(MYLIB/SRC)
SRCMBR(FDF)


The DDS for the TOFILE(MYLIB/TOFILE) looked like this:

.....A..........T.Name++++++RLen++TDpB......
*************** Beginning of data **********
R MYREC
FLDA 10
FLDB 10
****************** End of data *************
CRTPF FILE(MYLIB/TOFILE)
SRCFILE(MYLIB/SRC)
SRCMBR(TOFILE)


UPDDTA can be used to insert records into the Field Definition File ( UPDDTA MYLIB/FDF). The records in the Field Definition File describe the Field Name, Starting and Ending positions of each field, and the Null Character value. The last record to be inserted will contain the value of *END to signify that we are done describing how the data in the FROMFILE or FROMSTMF should be mapped to the fields in the TOFILE. Each column must be separated by a blank character. Each row must be sequentially ordered.

UPDDTA FILE(MYLIB/TOFILE) to insert those 3 records:
_____________________________

WORK WITH DATA IN A FILE
Format . . . . : FDFREC


ONEFLD: FLDA 1 10 0
_____________________________

WORK WITH DATA IN A FILE
Format . . . . : FDFREC


ONEFLD: FLDB 11 20 0
_____________________________


WORK WITH DATA IN A FILE
Format . . . . : FDFREC


ONEFLD: *END
_____________________________


A DSPPFM of MYLIB/FDF looks similar to the following:

*...+....1....+....2
FLDA 1 10 0
FLDB 11 20 0
*END
****** END OF DATA ******
 

A source file created with CRTSRCPF FILE(MYLIB/FDF) MBR(FDF) could be easier:

*************** Beginning of data ***********
FLDA 1 10 0
FLDB 11 20 0
*END
****************** End of data **************




STRSQL / SELECT * FROM MYLIB/TOFILE:

Position to line . . . . .
....+....1....+....2..
FLDA FLDB
a b
c d
e f
g h
i j
k l
m n
******** End of data ********

For additional information, refer to the Copying Between Different Servers section of the iSeries Database File Management manual, SC41-5710.

http://publib.boulder.ibm.com/infocenter/iseries/v5r3/topic/dm/rbal3.pdf

http://publib.boulder.ibm.com/infocenter/iseries/v5r3/index.jsp?topic=/dm/rbal3fixfor.htm

Historical Number

379659213

Rate this page:

(0 users)Average rating

Add comments

Document information


More support for:

IBM i
DB2 for IBM i

Software version:

5.1.0, 5.2.0, 5.3.0, 5.3.5, 5.4.0, 5.4.5, 6.1, 6.1.0, 6.1.1, 7.1, 7.1.0

Operating system(s):

IBM i

Reference #:

N1015514

Modified date:

2013-04-09

Translate my page

Machine Translation

Content navigation