Give PC applications access to AS/400 data.

Brief: The capability of transferring data files between the AS/400 and attached PCs is one of the major PC Support functions. The file download function (AS/400 to PC) in particular gives access to the relational database features built into OS/400. This article describes how to use the interactive file transfer programs and the available options.

After workstation emulation and printing, file transfer to and from PCs is probably the most widely used PC Support function. With file transfer support, you can transfer, display or print an AS/400 file at your PC, or you can upload a file from your PC to the AS/400. By its nature, file transfer enables an "arm's length" version of cooperative processing, in that the data that is downloaded or uploaded is a copy from the source of the data. Although file transfers may work identically for each of PC Support's different versions, my experience-and the treatement in this article-concentrates on file transfer functions for the DOS version of PC Support.

If your requirements include the ability to add, update or delete individual records within your database, you will need to consider alternatives to file transfer. Such alternatives might include using the PC Support Data Queues API functions, writing true cooperative processing programs using router-level techniques, or purchasing one of the growing number of cooperative processing programs available.

Three main components make up the AS/400-to-PC file transfer function. First is the front end, which closely resembles an SQL front end. Here you choose the file or files to be transferred, the fields within the files, and selection and sorting criteria. The second main component is the specification of what to do with the data: display, print or output to a PC file. The third component concerns user convenience options that let you easily run the same or similar transfer again, or save a definition for future PC-to-AS/400 file transfers.

PC-to-AS/400 file transfer has fewer options. It specifies the From and To files, whether the AS/400 object should be created and, optionally, a description file (saved during an AS/400-to-PC transfer) to map fields.

Getting Started

Before using file transfer, you must have PC Support configured and operational for the PC that will be using the function. By operational, I mean that you must be able to load the adapter handler program used by your PC and establish a connection to your AS/400 with the PC Support router program. You do not need to start an emulated display or printer session (Workstation Function) in order to use the file transfer function, although you can use emulated sessions concurrently with file transfer if you want.

An important part of configuring any PC Support function is deciding where the programs are to reside: on the PC or on the AS/400 in a shared folder. If you will be working with file transfer frequently, you should configure your PC so that the programs are copied from the shared folder to your PC hard disk. You should especially consider storing these programs on the PC if you will be using the PC for remote access to your AS/400.

You indicate where the programs should be located using the Configure PC Support (CFGPCS) program. In that program, select the General Options function; within General Options, select the Location of PC Support Functions option. You can select one or both of the file transfer functions (download and upload) to be resident on your PC. In this article, I assume that both file transfer programs are resident on your PC hard disk.

Once you have configured your PC and selected the location of the programs, you are ready to work with the file transfer function. First, start your PC Support connection; then go to the directory where the file transfer programs are located (usually C:PCS).

The Three Types of File Transfer

PC Support supplies three different ways to use the file transfer function. These include the prompted method that is described in this article, the batch transfer method and the API method.

The batch transfer method uses data files that contain parameters describing the transfer. You can use the prompted method to create the parameter files that are needed for the batch method. The batch method is typically used when you need to run the same file transfer request many times; for example, an end- of-day transfer that downloads transactions to your PC.

As with all of the other PC Support functions, there are DOS, Windows and OS/2 APIs that can be used in PC programs. Those programs are usually written in C, Pascal or PC Assem-bler and enable a PC program to directly access and control the transfer process. You can use those APIs to create your own version of the transfer request, perhaps creating a customized prompt program rather than the program supplied.

Prompted File Download

To show you how to use the prompted file transfer programs, we will work through an example. The example does not cover all of the options that are available with these programs, but does show enough so that you will be able to start working with file transfer.

The example shows how to transfer data from the AS/400 to the PC. To start the transfer, enter the command RTOPC at your PC's DOS prompt or select Transfer Data from PCSMENU. 1 shows the parameters used with this program and with the RFROMPC program, used to upload to the AS/400. Be sure that the command or the menu is located in your current directory or in one of your PATH directories.

The example shows how to transfer data from the AS/400 to the PC. To start the transfer, enter the command RTOPC at your PC's DOS prompt or select Transfer Data from PCSMENU. Figure 1 shows the parameters used with this program and with the RFROMPC program, used to upload to the AS/400. Be sure that the command or the menu is located in your current directory or in one of your PATH directories.

When you see the no current transfer display (2), press Enter or F10 to select the Create menu option. The next display is the transfer request options display, shown in 3. In the figure, the parameter values are already filled in for this example. The example shows how to download records from file QSYS/QA-DBXREF, the system-maintained database cross-reference file. This file contains a record for each data-base file on your system.

When you see the no current transfer display (Figure 2), press Enter or F10 to select the Create menu option. The next display is the transfer request options display, shown in Figure 3. In the figure, the parameter values are already filled in for this example. The example shows how to download records from file QSYS/QA-DBXREF, the system-maintained database cross-reference file. This file contains a record for each data-base file on your system.

The first parameter is the system name where the file is located. If you are linked to more than one AS/400, you can press F4 to prompt for the names of the systems that you can use. This parameter defaults to the local system that your PC connects to.

The From parameter is used to specify the name of the database file that you want to use. In 3, I have specified the file name fully, with the library, file name and member name. Although only the file name is required, you may have to specify the library name so that the file can be located. If the file is a multiple-member file, you will need to specify the member name. For multiple-format logical files, you also need to specify which one of the record formats contains the data to be transferred.

The From parameter is used to specify the name of the database file that you want to use. In Figure 3, I have specified the file name fully, with the library, file name and member name. Although only the file name is required, you may have to specify the library name so that the file can be located. If the file is a multiple-member file, you will need to specify the member name. For multiple-format logical files, you also need to specify which one of the record formats contains the data to be transferred.

The From parameter supports context-sensitive prompting. If the parameter is blank and you press F4, you are shown a list of libraries in the user portion of the library list. If the library name is in the parameter, the list of files within the library is presented when you press F4. Similarly, the list of members within the file is shown when the parameter contains the file name.

You can specify from one to 32 files to be downloaded. When you use more than one file, the prompt screen changes to show a Join By parameter. You must specify one or more field names that are present in each of the files; those fields are used to join records from the files. (For more information about join logical files, see "Multi-format and Join Logicals," MC, November 1992 and "Get Relational and Join," MC, February 1992.) This article does not describe how to download from more than one file at a time.

The next three parameters, Select, Where and Order By, are used to select fields and to select and sort records from the database file. These parameters are specified in SQL statement format. In this article, we will work with only the simplest cases, although you should be aware that these parameters can become quite involved.

The Select parameter specifies the fields that are to be included within each record that is downloaded. You can use the default special value (*) to indicate that all fields are to be included, or you can list the specific fields that you want to use. Up to 256 fields can be listed or included. If the record format includes more than 256 fields, you cannot use the asterisk special value, but instead must list the fields. Prompting within the Select parameter displays the fields that are in the record format of the file in the From parameter. In the example, six fields have been selected from the QADBXREF file.

The Where parameter is used to include or omit records in the database file. In the example, records will be included only if field DBXFIL is not equal to blanks. The Where parameter conditions can become quite complex with AND and OR logical conditions, especially considering that logical parenthetical expressions are supported.

The last of the SQL-type parameters used in the example is the Order By parameter. This is used to sort downloaded records by the values in fields included in the database file. You can sort each field in ascending or descending order; the default is ascending order.

Where Does the Data Go?

You use the next part of prompted file transfer to indicate what will happen with the data as it is transferred to your PC. You have three options: display the data, print it or write it to a disk file. These options are specified in the Output Device parameter.

The display option is the simplest. When you use this option, the downloaded data appears in columnar format in a window on the prompt display. The field names become column headers. The display resembles a subfile display, in that you can page forward and backward through it. In addition, you can scroll right and left if needed to see all of the fields.

You can print a simple report by selecting the printer as the output device. When you select the printer, additional parameters are shown. The printer that is used is a PC-attached printer, not an AS/400-attached printer. However, you can use a PC printer even if that printer is also used in a Workstation Function (WSF) session as an emulated printer.

The last and most complicated output option is the transfer to disk option. This option includes several other parameters, shown in 4. (This figure includes more parameters than the screen can display at one time. You will need to use the Page keys to see all of the parameters.)

The last and most complicated output option is the transfer to disk option. This option includes several other parameters, shown in Figure 4. (This figure includes more parameters than the screen can display at one time. You will need to use the Page keys to see all of the parameters.)

Use the To parameter to specify the name of the PC file that is to contain the downloaded data. This file can be located either on a floppy disk or on your PC's hard disk. A valid DOS drive, path and file name must be supplied.

The Replace Old File parameter indicates what should happen if you already have a PC file by the same name as the To file. If you select Yes, the downloaded data replaces the data in the old file. If you choose No and a PC file of the same name exists, a pop-up window is displayed. The window prompts you to press Enter to replace the file or Escape to cancel the download. Data is always replaced in an existing file; you cannot use the download program to add to an existing PC file.

The third additional parameter, PC File Type, is the most involved. The parameter is used to select a format for the downloaded data. The format that you select depends upon how you will use the data. The table in 5 describes each of the PC file type options. If you plan to import the downloaded data into a PC program, you must check the documentation for the program to determine its import capabilities. Many database and word processing programs can work with the ASCII text or BASIC random type files. Spreadsheet programs can generally import a DIF file.

The third additional parameter, PC File Type, is the most involved. The parameter is used to select a format for the downloaded data. The format that you select depends upon how you will use the data. The table in Figure 5 describes each of the PC file type options. If you plan to import the downloaded data into a PC program, you must check the documentation for the program to determine its import capabilities. Many database and word processing programs can work with the ASCII text or BASIC random type files. Spreadsheet programs can generally import a DIF file.

The Show Format of Transferred Data parameter is used to display or print a list of the fields included in the records downloaded to the PC. This list is useful if you are writing a PC program that uses the transferred data. With the list, you can create a structure within the PC program that maps to the format of the data (PC programs generally describe records in terms of the lengths and attributes of the fields in the record, not in terms of From and To positions).

The last two parameters are especially useful if you intend to work with the transferred data and then upload it to the AS/400. The Save Transfer Description parameter indicates whether or not you want to save the list of transferred fields and other download options. If you select Yes for this parameter, you must also provide the name of the description file in the last parameter. If you later want to upload the file to the AS/400 (using the RFROMPC program), you can use the saved description file to describe the format of the data file to the AS/400. This ensures that fields in the uploaded file are mapped correctly to fields in the AS/400 database file. If you don't anticipate uploading the file to the AS/400, you will probably have no use for the description file, and can select No for the Save Transfer Description parameter.

After entering your parameter selections, you run the transfer by selecting the Run menu option or by pressing F5. If the parameters are valid, the transfer request will begin. If you have incorrect parameters, an error message window is displayed and you are given the chance to correct the error.

Uploading to the AS/400

Uploading a file from the PC to the AS/400 is simpler than downloading, primarily because there are fewer parameters. To upload, you specify the destination file (on the AS/400), the source file (on the PC), the file description file (if any) or the type of PC file, and the member-processing option on the AS/400.

To initiate a prompted file upload, enter the command RFROMPC at your PC DOS prompt. After the initial displays, the Transfer Request display appears. 6 contains a sample of that display. (This figure shows more parameters than the display can show at one time. Use the Page keys to see the additional parameters when you view this display.) The sample display shows how an RPG source file on the PC can be transferred to a source file on the AS/400.

To initiate a prompted file upload, enter the command RFROMPC at your PC DOS prompt. After the initial displays, the Transfer Request display appears. Figure 6 contains a sample of that display. (This figure shows more parameters than the display can show at one time. Use the Page keys to see the additional parameters when you view this display.) The sample display shows how an RPG source file on the PC can be transferred to a source file on the AS/400.

The System Name parameter is used in the same way as on the RTOPC display. The second parameter, To, indicates the file and member name to which the PC file is to be transferred. This parameter is used with the last parameter shown in the figure, Create AS/400 Objects.

You can specify file and member names that do not exist, and have the transfer program create the file and member for you. Alternatively, the file transfer program will replace records in an existing member with data from the PC file. There is no option to add records to an existing member; if you need to do that, you will have to upload the PC file to an AS/400 work file and then copy records from the work file to the database file that you want to add to.

If you are uploading to a database file (not a source member), you can use a saved PC file description file to describe the format of the uploaded data. To use a file description, specify Yes for the Use PC File Description parameter, and enter the name of the description file you saved when downloading. You only need to create the description file (identified with the DOS file name extension .FDF) one time, unless the download/upload field selections or ordering changes.

If you are not uploading with a description file, you will have to specify the PC file type. The selections that you can use are the same as those used for the download program.

If you don't use a description file, the file is created on the AS/400 as a source physical file.

An additional parameter, not shown in the figure, is the Member Text parameter. This is prompted for if you select the Create member or Create file and member options on the Create AS/400 Objects parameter. You can enter a 50-character member description for a new member.

Working with Source Code

I have had great success using the file download and upload programs to work with source members on the PC. You should especially consider this if you work from a remote PC, where response time is slower because of communications overhead. Using the download program, you can download one or more source members to your PC, then edit them with a PC-based editor. Although most PC editing programs do not include RPG statement format support, some commercially available editors provide that function. After editing, you can use the upload program to send the source members back to an AS/400 source file, from which they can be compiled.

Other File Transfer Options

After you become familiar with the prompted file transfer programs, you can look into the batch mode programs. Batch mode means that rather than display a prompt screen where you fill in parameter values, you invoke the file transfer programs and provide the name of a stored parameter file.

You can experiment with this technique using the prompting programs. Two menu options available on both the download and upload displays are Save and Recall File Transfer Requests. You would typically work with the prompt screens to create the file transfer request, run the transfer, verify that it works correctly and then select the Save option to save all of the transfer options. The default extension for the transfer request file is .TTO.

You can use the saved transfer request later from either the prompt programs or the batch programs. To use the request in the prompt programs, you use the Recall menu option and specify the name of a valid transfer request file. All of the para-meter values are automatically filled in. You can then make changes or run the transfer request.

To run file transfers in batch mode, use the RTOPCB and RFROM-PCB commands, to transfer to and from the PC respectively. Each of these programs requires the name of a saved transfer request file as a parameter. By creating and saving transfer request files, you can create simple DOS .BAT files that can be given to your users. By running those batch files, they can use the download and upload programs without being confronted by the prompt displays that you work with.

Alternatives to PC Support File Transfer

The file transfer programs provided with PC Support are adequate for many file transfer purposes, but there may be cases where the programs do not provide a required feature or are too difficult to use as is. Some alternatives that you may need to consider include writing additional AS/400 or PC programs to format data, writing cooperative processing programs that work more directly with your data, or investigating products from other vendors.

The first alternative is useful when you must perform editing or additional file processing. This is typical when the PC is used for data entry. For example, a PC may automatically gather data throughout the day or support a data entry program. At the end of the day, you can upload the transaction file created by the PC application to the AS/400 into a work file. You would then use AS/400 programs to edit and correct the data before ultimately adding the data to your AS/400 database.

The second alternative, writing cooperative processing programs, is very complicated but may be required when you need real-time access to either AS/400 or PC data. There is quite a bit of overhead associated with both the download and the upload process. This overhead makes file transfer unsatisfactory for interactive inquiries where response time is critical. As mentioned earlier, you can consider using the Data Queues API support or writing APPC programs for the PC and the AS/400 to improve response time for random inquiries. As you may have gathered from the examples shown here, file transfer is oriented more toward working with sets of records rather than individual records, as is characteristic of inquiry applications.

Finally, other vendors offer file transfer programs. Some of these programs work with and require PC Support, whereas others use vendor-created techniques to transfer data. One of the main complaints about the PC Support transfer programs is the types of PC file formats that are supported. You may find, for example, that vendor-supplied programs are able to put data into a format that is directly usable by popular PC programs (for example, formatting data into .DBF format, as used in dBase and other database programs, rather than having to import data within those programs). Vendor-supplied programs typically provide database and record selection prompting similar to PC Support, since all of the major file transfer products seem to have adopted SQL-like prompting as the standard.

Additional Information

The documentation provided with PC Support is in manuals and machine- readable formats. The manuals include the PC Support user's guide for DOS, which describes how to run the file transfer programs and the options available. The technical reference for PC Support (DOS and OS/2) includes a great deal more detail about the parameters used with the transfer programs. In particular, the file formats supported by PC Support file transfer are described in detail in this manual. The PC Support/400 API reference contains in-depth information you'll need if you intend to write PC programs to access the transfer function APIs.

Finally, you can use the PC Support help function on your PC. To get help for the file transfer commands, you can enter PCSHELP RTOPC or PCSHELP RFROMPC. (You should be in your PCS directory when you use the PCSHELP program.) The help function provides a quick overview of the programs and the parameters used with them.

Craig Pelkie can be contacted through Midrange Computing.

References

PC Support/400: DOS and OS/2 Technical Reference (SC41-8091, CD-ROM QBKA6701). PC Support/400: DOS User's Guide (SC41-8199, CD-ROM QBKA6201). PC Support/400: Application Program Interface Reference (SC41-8254, CD-ROM QBKA6101).

TechTalk: PC Support File Transfer

Figure 1 Transfer Program Parameters

 RTOPC [filename<.TTO>] [/C or /M] [/H or /S] [/Z] RFROMPC [filename<.TTO>] [/C or /M] [/H or /S] [/Z] filename<.TTO> is the name of a previously created transfer request file /C: use color display mode /M: use monochrome display mode /H: use high-speed display writing /S: use low-speed display writing /Z: suppress display of IBM logo and information messages when starting 
 
 TechTalk: PC Support File Transfer
 
 Figure 2 Transfer "No Current Request" Display
 
 Create Recall Exit Help ------------------------------------------------------------------------ AS/400 System-to-PC Transfer There is no current transfer request at this time. You can use the create or recall actions to establish the current transfer request. To select an action from the top of the display, press F10. ------------------------------------------------------------------------ Enter Esc=Cancel F1=Help F3=Exit F10=Actions 
 
 TechTalk: PC Support File Transfer
 
 Figure 3 RTOPC Create Transfer Request, Part 1
 
 Create an AS/400 System-to-PC Transfer Request Select options and then press Enter System name. . . . . . . . [AS400 ] FROM . . . . . . . . . . . [QSYS/QADBXREF(QADBXREF) ] SELECT . . . . . . . . . . [dbxlib,dbxfil,dbxown,dbxtxt,dbxatr,dbxtyp ] WHERE. . . . . . . . . . . [dbxfil <> ' ' ] Fieldname(s) ORDER BY . . . . . . . . . [dbxlib,dbxfil ] Output device. . . . . . . 1. Display 2. Printer 3. Disk ------------------------------------------------------------------------ Enter Esc=Cancel F1=Help F3=Exit F4=Prompt F9=Insert Line Shift+F4=Group functions 
 
 TechTalk: PC Support File Transfer
 
 Figure 4 Options when Using DISK as the Output Device, Part
 
 Create an AS/400 System-to-PC Transfer Request More: Select options and then press Enter To . . . . . . . . . . . . [c:PCSxferfile.txt > Replace old file . . . . . . . . . 1. Yes 2. No PC file type . . . . . . . 1. ASCII text 2. DOS random 3. BASIC sequential 4. BASIC random 5. DIF(TM) 6. No conversion 7. DOS random type 2 Show format of transferred data . . . . . 1. Display 2. Print 3. None Save transfer description. . . . . . . . 1. Yes 2. No Description file name. . . . . . . . . [c:PCSxferfile.FDF > ------------------------------------------------------------------------ Enter Esc=Cancel F1=Help F3=Exit Shift+F4=Group functions Spacebar 
 
 TechTalk: PC Support File Transfer
 
 Figure 5 PC File Types Used in RTOPC and RFROMPC (CHART)
 
 (chart headings) File Type : Field Delimiter : Record Delimiter : Numeric Fields : EBCDIC/ASCII Translation : Field Length : Record Length : Notes: ASCII None Carriage return (x'0D'),Line feed (x'0A') Binary/Zoned/Packed: changed to ASCII numeric, minus sign to left Yes Fixed Variable BASIC random None None Binary/Zoned/Packed: changed to corresponding BASIC numeric types Yes Fixed Fixed BASIC sequential Comma (x'2C') Carriage return (x'0D'), Line feed (x'0A') Binary/Zoned/Packed: hanged to ASCII numeric, minus sign to left Yes Variable Variable DIF N/A N/A Binary/Zoned/Packed: changed to ASCII numeric, minus sign to left Yes N/A N/A Used for transferring to/from spreadsheet applications DOS random None None Binary: changed to PC format; Zoned: only sign half-byte is changed; Packed: only sign half-byte is changed Yes Fixed Fixed DOS random type 2 None None Binary: change to PC format; Zoned: only sign half-byte is changed; Packed: no changes Yes Fixed Fixed Used for SAA compliance with IBM PC language compilers No conversion None None No changes No Fixed Fixed