IBM iSeries Utilities For Data Interchange

1. Introduction

The IBM iSeries computers, still commonly known as AS/400 systems use the successful IBM DB2 database to store and access data which are organised into files with the following characteristics:

• They are structured in data fields which usually have a fixed length and type
• They have external data definitions which can be used by application programs
• Numeric data can be stored in packed format where each digit is stored in a semibyte.
• Data are coded in EBCDIC

Other systems such as Linux/Unix and Windows use files which are just a sequence of bytes (normally coded in ASCII) and therefore are known as stream files. Often their fields have not a fixed length and are delimited by a special field delimiter character such as a semicolon, a colon or a pipe (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 the IBM iSeries (AS/400) and PCs and Linux/Unix systems, but the different file organisations described above often make such transfers complicated and painful.

This article described some approaches to simplify the work.

2. IBM useful commands

The IBM AS/400 uses an integrated file system (IFS) that allows to use on the same server different file organisations such as those used by Linux/Unix or Windows and the native AS/400 ones. The native AS/400 files are stored in libraries or DB2 collections within the QSYS.LIB. Other file systems exist in the QOpenSys (similar to Unix) or the QDLS (used to store documents and files in PC formats) environments.

The IFS allows to use on the same server Linux/Unix based applications together with the native AS/400 applications.

The AS/400 Operating system provides some useful commands to simplify the data interchanges 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 into AS/400 database files
• CPYTPSTMF to copy AS/400 database files to stream files
• CPYTOPCD to copy AS/400 database files to PC documents, stored in the QDLS folders
• CPYFRMPCD to copy PC documents in the QDLS folders into AS/400 database files

The CPYTOPCD and CPYFRMPCD commands are specific for the QDLS system and have not many options whereas the other ones allow to use many options and are more flexible. They look similar, but there are important differences as follows:

CPYFRMSTMF converts text files (stream files that are in text format) to physical files. It has no concept of fields, so it can only write records to program described files (i.e. files that have no fields defined) or source pfs.

CPYFRMIMPF converts text files as well, but it tries to interpret fields in the input file and copy them to the appropriate fields in the output file. You can either import delimited fields (for example, comma separated value (CSV) files, tab-delimited files, pipe delimited files, etc) or you can read input from fixed-position fields (you have to define the record 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(*FIXED) FLDDFNFILE(Lib1/FILE4)

The example above uses a fixed data format (i.e. not delimited) and uses a field definition file (FILE4) to describe the text file fields as follows:

– This is 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

*END

The above would be needed to import the text file data into a DB file with field names of: FIELD1, FIELD2, FIELD3,…, and FIELD7. The *END is required. I think you can leave off the third column if no fields are null capable.

3. Some User Utilities

The commands above are useful and normally perfectly adequate, but they can be complex especially when the data fields to be copied are not in the same sequence or when one wants to extract only some data from the text file.

I was involved in a few system migration exercises where such copies between Unix, PCs and AS/400 systems had to be done frequently and I developed therefore some utilities to simplify these activities.

The utilities have the objective to satisfy following requirements:

• Support of any separator character used to delimiter the fields
• Possibility to copy valid data into fields defined as alphanumeric, numeric or packed numeric
• Possibility to copy data stored in different sequences in the two files. For instance it should be possible to copy fields 1, 3,4 and 6 of the text file into the fields 5, 2, 1 and 4 of the target database file.
• The utility should be able to store the file fields mappings (such as those described above) to allow the user to simply use the previously entered mapping.

I organised the utilities in a few commands and programs as follows:

a) Command UCPYFTP to control the copy between two files. The command requires to enter following parameters:

AS400 DB File . . . . . . . . . TOFILE …….

AS400 Library . . . . . . . . . TOLIB *LIBL

AS400 Member . . . . . . . . . . TOMBR *FIRST

FTP File . . . . . . . . . . . . FROMFILE …….

FTP Library . . . . . . . . . . FROMLIB …….

Field Separator #T=TAB] . . . . SEPARATOR ‘|’

Decimal Point . . . . . . . . . DECPOINT ‘.’

View Field Mappings (Y/N) . . . VIEWMAP ‘N’

Up to Record No. . . . . . . . . UPTOREC 0

b) Program UFMA01L to display and maintain existing file mappings.

The display looks as follows:

UFMAP30 MAPPING FILE MAINTENANCE 20/02/09 10:03:38

AS400 Database File: WERCSWKF FTP File: WERCS

Seq AS400 Fld Description Type Len D From

No. Name FldN

1 WK0003 WK_CLIENTE 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

Fine

F3=Exit F6=Update Mappings F9=Use sequential mapping F11=Process

The screen shows that field number 3 of the FTP file is copied to the first field of the database file called WK0003, field 4 is copied to WK0004 and so on. The fields for which the number is zero are not copied, but are initialised correctly in the target file (with zeros or blanks).

Notice following points:

• The file to be copied is called FTP file because normally is sent to the AS/400 by using an FTP transfer.
• The user must specify the name and library of both the FTP file and the target AS/400 database file.
• The commands use some defaults for the field separator and decimal point, but these can be changed by the user.
• When the View Field Mappings 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 couple 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 the field mappings which are then stored to allow a future re-use.

A similar utility called UCPYTOFTP allows 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 can be redistributed and/or modified it under the terms of the GNU General Public Licence as published by the Free Software Foundation.

Source by Mario Pesce