IBM iSeries computers, still known as AS / 400 systems, use the successful IBM DB2 database to store and access data organized in files with the following characteristics:
- They are structured in data fields that usually have a fixed length and type
- They have external data definitions that can be used by application programs
- Numerical data can be stored in packaged format, with each digit stored in a semi-byte.
- Data is encoded in EBCDIC
Other systems such as Linux / Unix and Windows use files that are only a sequence of bytes (usually encoded in ASCII) and are therefore known as power files. Often, their fields do not have a fixed length and are delimited by a special field delimiter such as a semicolon, colon or tube (field delimited files are often produced by exporting data originally stored in spreadsheets such as Microsoft Excel or databases such as Microsoft Access).
It is often necessary or useful to transfer files between IBM iSeries (AS / 400) and PCs and Linux / Unix systems, but the various file organizations described above often make such transfers complicated and painful.
This article described some approaches to simplify work.
2. IBM useful commands
IBM AS / 400 uses an integrated file system (IFS) that allows different file organizations to be used on the same server, such as those used by Linux / Unix or Windows and the original AS / 400. The original AS / 400 files are saved in libraries or DB2 collections in QSYS.LIB. Other file systems are found in QOpenSys (similar to Unix) or QDLS (used to store documents and files in PC formats) environments.
IFS allows using on the same server Linux / Unix based applications along with the original AS / 400 applications.
The AS / 400 operating system contains some useful commands to simplify the exchange of data between different file systems as described below:
- CPYFRMIMPF to copy data from IFS to the AS / 400 database system
- CPYTOIMPF to copy data from the AS / 400 database system to IFS
- CPYFRMSTMF to copy stream files to AS / 400 database files
- CPYTPSTMF to copy AS / 400 database files to stream files
- CPYTOPCD for copying AS / 400 database files to PC documents stored in QDLS folders
- CPYFRMPCD to copy PC documents in the QDLS folders to AS / 400 database files
CPYTOPCD and CPYFRMPCD commands are specific to the QDLS system and do not have many options, while the others allow many settings and are more flexible. They look similar, but there are important differences as follows:
CPYFRMSTMF converts text files (stream files in text format) into physical files. It has no field term, so it can only write records to programmed files (i.e. files that have no fields defined) or source-pfs.
CPYFRMIMPF also converts text files, but it tries to interpret fields in the input file and copy them to the relevant fields in the output file. You can either import delimited fields (e.g., comma separated value (CSV) files, tab delimited files, pipe delimited files, etc.) or you can read input from fixed position fields (you must define the post layout in a “field definition file”)
An example of the second command is the following:
CPYFRMIMPF FROMSTMF (‘/ Fldr1 / File1.CSV’) TOFILE (Lib1 / FILE3) MBROPT (* REPLACE) RCDDLM (* CRLF) DTAFMT (* FAST) FLDDFNFILE (Lib1 / FILE4)
The example above uses a fixed data format (ie, not delimited) and uses a field definition file (FILE4) to describe the text file fields as follows:
– This is a comment
– DBFieldname startpos endpos nullIndpos
field1 1 12 13
field2 14 24 0
field3 25 55 56
field4 78 89 90
field5 100 109 0
field6 110 119 120
field7 121 221 0
The above would be necessary to import the text file data into a DB file with field names: FIELD1, FIELD2, FIELD3, … and FIELD7. * END is required. I think you can leave the third column if no fields are null.
3. Some Utilities
The commands above are useful and usually perfectly appropriate, but they can be complex, especially when the data fields to be copied are not in the same sequence or when you just want to extract some data from the text file.
I was involved in a couple of system migration exercises where such copies between Unix, PCs and AS / 400 systems needed to be done frequently, and so I developed some tools to simplify these activities.
The utilities aim to meet the following requirements:
- Support for any delimiter used to delimit the fields
- Ability to copy valid data to fields defined as alphanumeric, numeric, or packaged numerically
- Ability to copy data stored in different sequences into the two files. For example, it should be possible to copy fields 1, 3,4 and 6 in the text file to fields 5, 2, 1 and 4 in the target database file.
- The utility must be able to save file field mappings (such as those described above) to allow the user to simply use the previously entered mapping
I organized the utilities into a few commands and programs as follows:
a) Command UCPYFTP to check the copy between two files. The command requires entering the following parameters:
AS400 DB file. . . . . . . . . TOFILE …….
AS400 Library. . . . . . . . . TOLIB * LIBL
AS400 member. . . . . . . . . . TOMBR * FIRST
FTP file. . . . . . . . . . . . FRAFIL …….
FTP directory. . . . . . . . . . FROMLIB …….
Field Separator # T = TAB]. . . . SEPARATOR ‘|’
Decimal point. . . . . . . . . DECPOINT ‘.’
Show field mapping (open / long). . . VIEWMAP ‘N’
Up to record number. . . . . . . UPTOREC 0
b) Program UFMA01L to display and maintain existing movie mappings.
The display looks like this:
UFMAP30 MAPPING MAINTENANCE 02/20/09 10:03:38 AM
AS400 database file: WERCSWKF FTP file: WERCS
Seq AS400 Fld Description Type Len D Off
No. Name FldN
1 WK0003 WK_CLIENT A 9 3
2 WK0004 WK_CLI_SAP A 10 4
3 WK0005 WK_RAGSOC A 40 5
4 WK0006 WK_ZIP A 5 9
5 WK0016 WK_ADDRESS1 A 100 17
6 WK0017 WK_ADDRESS2 A 100 19
7 WK0101 WK_INDI A 30 0
8 WK0102 WK_CAP A 5 0
9 WK0103 WK_LOC A 25 0
10 WK0104 WK_PROV A 2 0
F3 = Exit F6 = Update mapping F9 = Use sequential mapping F11 = Process
The screen shows that field number 3 in the FTP file is copied to the first field in the database file called WK0003, field 4 is copied to WK0004 and so on. The fields where the number is zero are not copied, but are initialized correctly in the target file (zero or empty).
Note the following points:
- The file to be copied is called FTP file because it is usually sent to AS / 400 using an FTP transfer.
- The user must specify the name and directory for both the FTP file and the target AS / 400 database file.
- The commands use some default settings for the field separator and decimal point, but these can be changed by the user.
- When the Show Field Mapping parameter is set to ‘Y’, the user will see previously defined field mappings or enter new mappings.
- When the user invokes the command for the first time on a new pair of ftp and target files, the program extracts the field definitions of the database file and displays the mapping screen to allow the user to enter field mappings that are then saved to allow for future reuse.
A similar utility called UCPYTOFTP allows you to copy the data from an AS / 400 database file to a text file to be downloaded to a Linux / Unix or Windows machine.
The utilities and their documentation can be downloaded for free from my website. The programs are free software that may be redistributed and / or modified under the terms of the GNU General Public License as published by the Free Software Foundation.