| 1. Introduction | | | | data into a DB file with field names of: FIELD1, FIELD2, |
| The IBM iSeries computers, still commonly known as | | | | FIELD3,..., and FIELD7. The *END is required. I think you |
| AS/400 systems use the successful IBM DB2 | | | | can leave off the third column if no fields are null |
| database to store and access data which are | | | | capable. |
| organised into files with the following characteristics: | | | | 3. Some User Utilities |
| - They are structured in data fields which usually have | | | | The commands above are useful and normally |
| a fixed length and type | | | | perfectly adequate, but they can be complex |
| - They have external data definitions which can be | | | | especially when the data fields to be copied are not in |
| used by application programs | | | | the same sequence or when one wants to extract |
| - Numeric data can be stored in packed format where | | | | only some data from the text file. |
| each digit is stored in a semibyte. | | | | I was involved in a few system migration exercises |
| - Data are coded in EBCDIC | | | | where such copies between Unix, PCs and AS/400 |
| Other systems such as Linux/Unix and Windows use | | | | systems had to be done frequently and I developed |
| files which are just a sequence of bytes (normally | | | | therefore some utilities to simplify these activities. |
| coded in ASCII) and therefore are known as stream | | | | The utilities have the objective to satisfy following |
| files. Often their fields have not a fixed length and are | | | | requirements: |
| delimited by a special field delimiter character such as | | | | - Support of any separator character used to delimiter |
| a semicolon, a colon or a pipe (field delimited files are | | | | the fields |
| often produced by exporting data originally stored in | | | | - Possibility to copy valid data into fields defined as |
| spreadsheets such as Microsoft Excel or databases | | | | alphanumeric, numeric or packed numeric |
| such as Microsoft Access). | | | | - Possibility to copy data stored in different sequences |
| It is often necessary or useful to transfer files | | | | in the two files. For instance it should be possible to |
| between the IBM iSeries (AS/400) and PCs and Linux | | | | copy fields 1, 3,4 and 6 of the text file into the fields 5, |
| Unix systems, but the different file organisations | | | | 2, 1 and 4 of the target database file. |
| described above often make such transfers | | | | - The utility should be able to store the file fields |
| complicated and painful. | | | | mappings (such as those described above) to allow |
| This article described some approaches to simplify the | | | | the user to simply use the previously entered mapping. |
| work. | | | | I organised the utilities in a few commands and |
| 2. IBM useful commands | | | | programs as follows:a) Command UCPYFTP to |
| The IBM AS/400 uses an integrated file system (IFS) | | | | control the copy between two files. The command |
| that allows to use on the same server different file | | | | requires to enter following parameters: |
| organisations such as those used by Linux/Unix or | | | | AS400 DB File . . . . . . . . . TOFILE ....... |
| Windows and the native AS/400 ones. The native AS | | | | AS400 Library . . . . . . . . . TOLIB *LIBL |
| 400 files are stored in libraries or DB2 collections within | | | | AS400 Member . . . . . . . . . . TOMBR *FIRST |
| the QSYS.LIB. Other file systems exist in the | | | | FTP File . . . . . . . . . . . . FROMFILE ....... |
| QOpenSys (similar to Unix) or the QDLS (used to | | | | FTP Library . . . . . . . . . . FROMLIB ....... |
| store documents and files in PC formats) | | | | Field Separator #T=TAB] . . . . SEPARATOR '|' |
| environments. | | | | Decimal Point . . . . . . . . . DECPOINT '.' |
| The IFS allows to use on the same server Linux/Unix | | | | View Field Mappings (Y/N) . . . VIEWMAP 'N' |
| based applications together with the native AS/400 | | | | Up to Record No. . . . . . . . . UPTOREC 0b) Program |
| applications. | | | | UFMA01L to display and maintain existing file mappings. |
| The AS/400 Operating system provides some useful | | | | The display looks as follows: |
| commands to simplify the data interchanges between | | | | UFMAP30 MAPPING FILE MAINTENANCE 20/02/09 |
| different file systems as described below: | | | | 10:03:38 |
| - CPYFRMIMPF to copy data from IFS to the AS/400 | | | | AS400 Database File: WERCSWKF FTP File: |
| database system | | | | WERCS |
| - CPYTOIMPF to copy data from the AS/400 | | | | Seq AS400 Fld Description Type Len D From |
| database system to IFS | | | | No. Name FldN |
| - CPYFRMSTMF to copy stream files into AS/400 | | | | 1 WK0003 WK_CLIENTE A 9 3 |
| database files | | | | 2 WK0004 WK_CLI_SAP A 10 4 |
| - CPYTPSTMF to copy AS/400 database files to | | | | 3 WK0005 WK_RAGSOC A 40 5 |
| stream files | | | | 4 WK0006 WK_ZIP A 5 9 |
| - CPYTOPCD to copy AS/400 database files to PC | | | | 5 WK0016 WK_ADDRESS1 A 100 17 |
| documents, stored in the QDLS folders | | | | 6 WK0017 WK_ADDRESS2 A 100 19 |
| - CPYFRMPCD to copy PC documents in the QDLS | | | | 7 WK0101 WK_INDI A 30 0 |
| folders into AS/400 database files | | | | 8 WK0102 WK_CAP A 5 0 |
| The CPYTOPCD and CPYFRMPCD commands are | | | | 9 WK0103 WK_LOC A 25 0 |
| specific for the QDLS system and have not many | | | | 10 WK0104 WK_PROV A 2 0 |
| options whereas the other ones allow to use many | | | | Fine |
| options and are more flexible. They look similar, but | | | | F3=Exit F6=Update Mappings F9=Use sequential |
| there are important differences as follows: | | | | mapping F11=Process |
| CPYFRMSTMF converts text files (stream files that | | | | The screen shows that field number 3 of the FTP file |
| are in text format) to physical files. It has no concept | | | | is copied to the first field of the database file called |
| of fields, so it can only write records to program | | | | WK0003, field 4 is copied to WK0004 and so on. The |
| described files (i.e. files that have no fields defined) or | | | | fields for which the number is zero are not copied, but |
| source pfs. | | | | are initialised correctly in the target file (with zeros or |
| CPYFRMIMPF converts text files as well, but it tries to | | | | blanks). |
| interpret fields in the input file and copy them to the | | | | Notice following points: |
| appropriate fields in the output file. You can either | | | | - The file to be copied is called FTP file because |
| import delimited fields (for example, comma separated | | | | normally is sent to the AS/400 by using an FTP |
| value (CSV) files, tab-delimited files, pipe delimited files, | | | | transfer. |
| etc) or you can read input from fixed-position fields | | | | - The user must specify the name and library of both |
| (you have to define the record layout in a "field | | | | the FTP file and the target AS/400 database file. |
| definition file") | | | | - The commands use some defaults for the field |
| An example of the second command is the following: | | | | separator and decimal point, but these can be changed |
| CPYFRMIMPF FROMSTMF('/Fldr1/File1.CSV') | | | | by the user. |
| TOFILE(Lib1/FILE3) MBROPT (*REPLACE) | | | | - When the View Field Mappings parameter is set to |
| RCDDLM(*CRLF) DTAFMT(*FIXED) FLDDFNFILE(Lib1 | | | | 'Y', the user will see previously defined field mappings |
| FILE4) | | | | or enter new mappings. |
| The example above uses a fixed data format (i.e. not | | | | - When the user invokes the command for the first |
| delimited) and uses a field definition file (FILE4) to | | | | time on a new couple of ftp and target files, the |
| describe the text file fields as follows: | | | | program extracts the field definitions of the database |
| - This is comment | | | | file and displays the mapping screen to allow the user |
| - DBFieldname startpos endpos nullIndposfield1 1 12 | | | | to enter the field mappings which are then stored to |
| 13field2 14 24 0field3 25 55 56field4 78 89 90field5 100 | | | | allow a future re-use. |
| 109 0field6 110 119 120field7 121 221 0 | | | | A similar utility called UCPYTOFTP allows to copy the |
| *END | | | | data from an AS/400 database file to a text file to be |
| The above would be needed to import the text file | | | | downloaded to a Linux/Unix or Windows machine. |