The IBM iSeries computer systems, nonetheless typically identified as AS/400 systems use the effective IBM DB2 database to retailer and access information which are organised into files with the following traits:
- They are structured in information fields which typically have a fixed length and kind
- They have external information definitions which can be employed by application applications
- Numeric information can be stored in packed format exactly where each and every digit is stored in a semibyte.
- Information are coded in EBCDIC
Other systems such as Linux/Unix and Windows use files which are just a sequence of bytes (typically coded in ASCII) and hence are identified as stream files. Usually their fields have not a fixed length and are delimited by a specific field delimiter character such as a semicolon, a colon or a pipe (field delimited files are normally made by exporting information initially stored in spreadsheets such as Microsoft Excel or databases such as Microsoft Access).
It is normally important or helpful to transfer files involving the IBM iSeries (AS/400) and PCs and Linux/Unix systems, but the various file organisations described above normally make such transfers difficult and painful.
This short article described some approaches to simplify the function.
2. IBM helpful commands
The IBM AS/400 utilizes an integrated file technique (IFS) that enables to use on the very same server various file organisations such as these employed by Linux/Unix or Windows and the native AS/400 ones. The native AS/400 files are stored in libraries or DB2 collections inside the QSYS.LIB. Other file systems exist in the QOpenSys (related to Unix) or the QDLS (employed to retailer documents and files in Computer formats) environments.
The IFS enables to use on the very same server Linux/Unix primarily based applications collectively with the native AS/400 applications.
The AS/400 Operating technique supplies some helpful commands to simplify the information interchanges involving various file systems as described beneath:
- CPYFRMIMPF to copy information from IFS to the AS/400 database technique
- CPYTOIMPF to copy information from the AS/400 database technique 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 Computer documents, stored in the QDLS folders
- CPYFRMPCD to copy Computer documents in the QDLS folders into AS/400 database files
The CPYTOPCD and CPYFRMPCD commands are distinct for the QDLS technique and have not lots of alternatives whereas the other ones permit to use lots of alternatives and are extra versatile. They appear related, but there are critical variations as follows:
CPYFRMSTMF converts text files (stream files that are in text format) to physical files. It has no notion of fields, so it can only create records to system described files (i.e. files that have no fields defined) or supply pfs.
CPYFRMIMPF converts text files as properly, but it tries to interpret fields in the input file and copy them to the suitable fields in the output file. You can either import delimited fields (for instance, comma separated worth (CSV) files, tab-delimited files, pipe delimited files, and so forth) or you can study input from fixed-position fields (you have to define the record layout in a “field definition file”)
An instance 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 instance above utilizes a fixed information format (i.e. not delimited) and utilizes 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 field3 25 55 56 field4 78 89 90 field5 100 109 field6 110 119 120 field7 121 221 *Finish
The above would be required to import the text file information into a DB file with field names of: FIELD1, FIELD2, FIELD3,…, and FIELD7. The *Finish is necessary. I believe you can leave off the third column if no fields are null capable.
3. Some User Utilities
The commands above are helpful and typically completely sufficient, but they can be complicated in particular when the information fields to be copied are not in the very same sequence or when a single desires to extract only some information from the text file.
I was involved in a handful of technique migration workouts exactly where such copies involving Unix, PCs and AS/400 systems had to be completed regularly and I created hence some utilities to simplify these activities.
The utilities have the objective to satisfy following needs:
- Assistance of any separator character employed to delimiter the fields
- Possibility to copy valid information into fields defined as alphanumeric, numeric or packed numeric
- Possibility to copy information stored in various sequences in the two files. For instance it ought to be attainable to copy fields 1, three,four and six of the text file into the fields five, two, 1 and four of the target database file.
- The utility ought to be in a position to retailer the file fields mappings (such as these described above) to permit the user to basically use the previously entered mapping.