Creating a Field Definition File for Use with the CPYFRMIMPF Command

Technote

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






System i Support

IBM disclaims all warranties, whether express or implied, including, but not limited to, the implied warranties of merchantability and fitness for a particular purpose. By furnishing this document, IBM grants no licenses to any related patents or copyrights. Copyright © 1996,1997,1998, 1999, 2000, 2001, 2002, 2003, 2004, 2005, 2006, 2007, 2008, 2009, 2010, 2011, 2012, 2013 IBM Corporation. Any trademarks and product or brand names referenced in this document are the property of their respective owners. Consult the Terms of use link for trademark information.