Some Useful AS/400 Integration Utilities

1. Introduction

The following report presents some AS/400 utilities that allow an easies integreation with Linux or PCs applications.

They are briefly described below. If you are interested to have more details send an e-mail to mario@datamission.co.uk

2. File Transfer Utilities

Data transfers are usually done with FTP, but , when AS/400 and Linux machines are involved, some data format conversion is often needed as described below:

  • The AS/400 uses the EBCDIC character set, whereas Linux machines use ASCII. The conversion is normally done automatically by FTP.
  • Numbers are often stored on the AS400 in Packed Decimal format (2 digits per byte) that is not directly supported by Linux. Packed decimal fields must therefore be unpacked when copying data for Linux.
  • In the AS/400 files the decimal point is implied according to the field definition. For instance if a field is 11 digits long with 2 decimals, no decimal point exists in the representation of the numbers, but the system automatically treats the last 2 digits as decimals. When numeric fields with decimals must be transferred to Linux, an explicit decimal point must be added in the correct position.

It is normally difficult to use direct transfers, because of packed fields and fixed length field requirements on the AS/400 files.

We have therefore developed some utilities that allow to make easier transfer from AS/400 database files to stream files and from stream files to the AS/400 database files.

 

2.1 Database File to Stream File Copy

The utility consists of a command (UCRTFTPFIL) and some programs to perform the conversion.

The AS/400 packed fields are converted into numeric fields and an explicit decimal point is added when there are decimals.

Moreover a field separator is added to identify the end of each field.

The command prompt the data in the following mask:

It should be noticed that the user can select the separator (e.g. the pipe character) and the decimal point to use (dot or comma).

 

2.2 Stream File to Database File Copy

The utility assumes that the stream file contains fields separated by a separator. It is not requested to have the fields in the same sequence as those on the AS/400 database file.

 

The mapping of the fields can be entered by using the utility program UFMAP01L, that displays the following screen:

When one select one row by entering a ‘2’, the program displays a list of the fields in database files and allows to enter the sequence number of the corresponding field on the stream file (described on the screen as FTP file because it is normally received by FTP).

See the example below:

In the example above, the field number 1 of the stream file (see From FldNo) is mapped to the field CT0010 of the AS/400 database file, the field number 2 is mapped to CT0030 and the field number 3 is mapped to CT0020.

Once the mappings have been defined, it is possible to perform the copy by using the command UCPYFTP that prompts the user for the following data:

Also in this case the user is required to specify the separator and the decimal point used for the numbers.

It should be mentioned that the field mappings must be specified only once for each couple of files and that the copy command uses them automatically.

3. Spool File Transfer Utility

Spool files on the AS/400 cannot be transferred directly because they have a special format. Normally people use the CPYSPLF command to copy the spool file to a physical file and then they transfer the physical file.

However there is sometimes the need to import a spool file into spreadsheet applications such as Excel and the simple operations described above are not sufficient because each line is considered as a single cell of the spreadsheet.

The utility UEXPSPLF allows to split lines into separate cells and create a physical file that can be imported into the spreadsheet.

The command prompt the data in the following mask:

The user is required to specify the separator that will be used to identify the end of each column , the spool file and physical file where it should be copied.

When the data are entered, the program copies automatically the spool file to a physical file and then diplays the first page as follows:

The user can then identify the different columns, by entering the separator character at the end of each column, and then press F6 to request the production of the separated output file. This can then be easily transferred to the target machine and used to import the data into a spreadsheet.