Troubleshooting
Problem
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.
Symptom
The CPYFRMIMPF works very well with a comma delimited file - but you may not have a comma (etc.) delimited IFS file.
Cause
The CPYFRMIMPF command needs information on which data belongs in which columns within your IBM i table (physical file) columns. A comma delimited (etc.) file provides that information.
Diagnosing The Problem
Your CPYFRMIMPF command puts the each entire row of the IFS file in the first column of your IBM i table (physical file)
Resolving The Problem
Data on the IFS can be in either a delimited or fixed format.
Example of Delimited Data in an IFS stream file:
*********Beginning of data************ George,Washington,1789 John,Adams,1797 Thomas,Jefferson,1801 James,Madison,1809 James,Monroe,1817 ************End of Data***************
Example of Fixed Format Data in an IFS stream file:
*********Beginning of data************ John Quincy Adams 1825 Andrew Jackson 1829 Martin Van Buren 1837 ************End of Data***************
Example: First we will do a (comma) delimited IFS stream file and then we'll add the records fixed format IFS stream file to it.
1) Create an IBM i table for the data in the IFS stream files above (8 total names)
CREATE TABLE US_PRES (FIRSTNAME CHAR (12) CCSID 37, LASTNAME CHAR (17) CCSID 37, FIRSTYEAR CHAR ( 4) CCSID 37 )
2) Use CPYFRMIMPF for the (comma) delimited IFS stream file with the first 5 names
CPYFRMIMPF FROMSTMF('/-your complete IFS path-/us_presidents.csv')
TOFILE((-your lib-/US_PRES)
TOCCSID(37)
RCDDLM(-your row delimiter-)
Notes:
1. The TOCCSID may or may not be needed depending on the code page of your IFS stream file as well as your CCSID of the TOFILE.
2. The Record Delimiter (RCDDLM) will vary on your IFS data.
3. It's best to use a complete absolute IFS path (i.e. start with the leading /, etc.)
3) At this point a query of your US_PRES table results in:
FIRSTNAME LASTNAME FIRSTYEAR George Washington 1789 John Adams 1797 Thomas Jefferson 1801 James Madison 1809 James Monroe 1817 ******** End of data ********
4) Now let's create a FLDDFNFILE for the fixed IFS data
STRSEU SRCFILE(-your lib-/QCLSRC)
SRCMBR(CPYFRMIMPF)
TYPE(TXT)
TEXT('FLDDFNFILE file for CPYFRMIMPF')
5) We'll "define" how the fixed IFS data should be parsed
*************** Beginning of data *** FIRSTNAME 1 12 0 LASTNAME 13 30 0 FIRSTYEAR 31 34 0 *END ****************** End of data ******
6) Using that FLDDFNFILE we just created, use another CPYFRMIMPF command to add in those other three rows into our US_PRES table
CPYFRMIMPF FROMSTMF('/-your complete IFS path-/us_presidents.txt')
TOFILE(-your lib-/US_PRES)
TOCCSID(37)
RCDDLM(-your row delimiter-)
DTAFMT(*FIXED)
FLDDFNFILE(-your lib-/QCLSRC CPYFRMIMPF)
Notes:
1. The TOCCSID may or may not be needed depending on the code page of your IFS stream file as well as your CCSID of the TOFILE.
2. The Record Delimiter (RCDDLM) will vary on your IFS data.
3. It's best to use a complete absolute IFS path (i.e. start with the leading /, etc.)
7) At this point a query of your US_PRES table results in:
FIRSTNAME LASTNAME FIRSTYEAR John Quincy Adams 1825 Andrew Jackson 1829 Martin Van Buren 1837 George Washington 1789 John Adams 1797 Thomas Jefferson 1801 James Madison 1809 James Monroe 1817 ******** 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
Was this topic helpful?
Document Information
Modified date:
18 December 2019
UID
nas8N1015514