IBM iSeries Utilities For Information Interchange


0
Data Formats (1)
Data Formats (1)

1. Introduction The IBM iSeries computer systems, nevertheless usually recognized as AS/400 systems use the profitable IBM DB2 database to shop 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 variety They have external information definitions which can be made use of 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 (generally coded in ASCII) and for that reason are recognized as stream files. Frequently their fields have not a fixed length and are delimited by a particular field delimiter character such as a semicolon, a colon or a pipe (field delimited files are frequently created by exporting information initially stored in spreadsheets such as Microsoft Excel or databases such as Microsoft Access). It is frequently important or valuable to transfer files involving the IBM iSeries (AS/400) and PCs and Linux/Unix systems, but the distinct file organisations described above frequently make such transfers complex and painful. This write-up described some approaches to simplify the function. two. IBM valuable commands The IBM AS/400 utilizes an integrated file program (IFS) that enables to use on the similar server distinct file organisations such as these made use of 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 (made use of to shop documents and files in Computer formats) environments. The IFS enables to use on the similar server Linux/Unix primarily based applications collectively with the native AS/400 applications. The AS/400 Operating program gives some valuable commands to simplify the information interchanges involving distinct file systems as described beneath: CPYFRMIMPF to copy information from IFS to the AS/400 database program CPYTOIMPF to copy information from the AS/400 database program 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 precise for the QDLS program and have not lots of alternatives whereas the other ones permit to use lots of alternatives and are additional 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 plan described files (i.e. files that have no fields defined) or supply pfs. CPYFRMIMPF converts text files as effectively, but it tries to interpret fields in the input file and copy them to the proper 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 on) 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 necessary to import the text file information into a DB file with field names of: FIELD1, FIELD2, FIELD3,…, and FIELD7. The *Finish is needed. I believe you can leave off the third column if no fields are null capable. three. Some User Utilities The commands above are valuable and generally completely sufficient, but they can be complicated specially when the information fields to be copied are not in the similar sequence or when 1 desires to extract only some information from the text file. I was involved in a handful of program migration workout routines exactly where such copies involving Unix, PCs and AS/400 systems had to be completed often and I created for that reason some utilities to simplify these activities. The utilities have the objective to satisfy following needs: Help of any separator character made use of to delimiter the fields Possibility to copy valid information into fields defined as alphanumeric, numeric or packed numeric Possibility to copy information stored in distinct sequences in the two files. For instance it need to be probable 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 need to be in a position to shop the file fields mappings (such as these described above) to permit the user to merely use the previously entered mapping. I organised the utilities in a handful of commands and applications as follows: a) Command UCPYFTP to manage the copy involving two files. The command calls for to enter following parameters: AS400 DB File . . . . . . . . . TOFILE ……. AS400 Library . . . . . . . . . TOLIB *LIBL AS400 Member . . . . . . . . . . TOMBR *Initial 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 b) Plan UFMA01L to show and sustain current file mappings. The show appears as follows: UFMAP30 MAPPING FILE Upkeep 20/02/09 10:03:38 AS400 Database File: WERCSWKF FTP File: WERCS Seq AS400 Fld Description Kind Len D From No. Name FldN 1 WK0003 WK_CLIENTE A 9 three two WK0004 WK_CLI_SAP A 10 four three WK0005 WK_RAGSOC A 40 five four WK0006 WK_ZIP A five 9 five WK0016 WK_ADDRESS1 A 100 17 six WK0017 WK_ADDRESS2 A 100 19 7 WK0101 WK_INDI A 30 eight WK0102 WK_CAP A five 9 WK0103 WK_LOC A 25 10 WK0104 WK_PROV A two Fine F3=Exit F6=Update Mappings F9=Use sequential mapping F11=Procedure The screen shows that field quantity three of the FTP file is copied to the 1st field of the database file named WK0003, field four is copied to WK0004 and so on. The fields for which the quantity is zero are not copied, but are initialised properly in the target file (with zeros or blanks). Notice following points: The file to be copied is named FTP file for the reason that generally is sent to the AS/400 by making use of an FTP transfer. The user should specify the name and library of each 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 1st time on a new couple of ftp and target files, the plan extracts the field definitions of the database file and displays the mapping screen to permit the user to enter the field mappings which are then stored to permit a future re-use. A related utility named UCPYTOFTP enables to copy the information 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 totally free from my internet site. The applications are totally free software program that can be redistributed and/or modified it below the terms of the GNU Common Public Licence as published by the Free of charge Computer software Foundation. Mario Pesce – Personal computer consultant http://www.datamission.co.uk e-mail: [email protected] weblog: http://mariopesceuk.blogspot.com Post Supply: http://EzineArticles.com/specialist/Mario_Pesce/248968 “


Like it? Share with your friends!

0
David H