This document explains how to transfer a file from a PC to a database file on the IBM System i system.
Resolving the problem
|Important Note: This document discusses Client Access for Microsoft Windows 95 and Windows NT, Client Access Express, IBM iSeries Access, and IBM System i Access products. These names essentially refer to the same product; however, the functionality and name changed over the last several releases. For the purposes of this document, the terms Client Access, Client Access Express, iSeries Access, and System i Access can be used interchangeably. Where a difference is important, the version of the product is used to identify the differences.|
When transferring files from the PC to the IBM System i system, there are several things that should be known:
|o||What is the file type (format) of the file on the PC?|
|o||If transferring the file to an existing file on the System i, is the file on the System i system a flat file (one field X number of characters long) or a database file (multiple fields)?|
The following examples will use the following files:
C:\WINDOWS\SYSTEM.INI - Flat Text file
C:\QCUSTCDT.CSV - Comma-Separated Variable database file
|o||System i system File:
MYLIB/SYSTEM - will end up having the contents of SYSTEM.INI
MYLIB/NEWCUSTS - will end up having data from QCUSTCDT.CSV
Flat Data File Upload to the System i System
This is the most basic transfer. This transfer takes any ASCII PC file and transfers the contents to the System i system. A file is not required on the System i system as the transfer handles the file creation. The first step is to open the Data Transfer to System i screen.
This can be done using one of the following methods:
|o||Open the iSeries Access group of icons, and select the Data Transfer To iSeries Server icon.|
|o||Using menus, go to Start, Programs, IBM iSeries Access for Windows, and Data Transfer To iSeries Server.|
|o||Right-click on the Desktop, select New, and select Data Transfer To iSeries Server.|
|o||From a PC5250 emulation screen, click on the Send button (or use the menu options under Transfer).|
The initial data transfer screen looks similar to the following:
We will be transferring C:\WINDOWS\SYSTEM.INI to MYLIB/SYSTEM. When the parameters are filled in, the window looks similar to the following:
Next, we will ensure all of the System i system details are as desired. Clicking on the Details button opens the following window:
The Details screen has several options that are separated into two frames. The first frame deals with PC options. The top check box indicates if a Field Description File (FDF) should be used or not. The next two fields are mutually exclusive (for example, one is on or the other is on, but not both). If the Use PC file description check box is checked, the file name is enabled and the file type field is disabled (as displayed above). If the FDF check box is not checked, the displayed options are reversed. The FDF would have been created on a previous download and specifies the file format desired on upload. The last item is to translate from ANSI to EBCDIC or from ASCII to EBCDIC. This specifies whether to read the file using an ASCII Code Page translation or an ANSI Code Page translation. In most cases, the default value of ASCII will work just fine.
Under the operating system options, the first parameter instructs the transfer to do one of the following things:
|o||No, Replace Member Only
This option is used when the file and the member that contain the data on the System i™ system already exist. If there is already data in the member, the data will be cleared and the new data written to the file.
|o||Yes, Create File and Member
This option is used when the file and the member do not exist on the System i™ system. Data Transfer will create the file and member.
|o||Yes, Create Member
This option is used when the file exists, but you want to create a new member.
|o||No, append to existing member
This option is used to add or append the records to an existing operating system file member.
iSeries file type is next. There are two radio buttons, one labeled Source and the other Data. This will only be enabled if you are using an FDF and creating a new file. If you select Data, a physical data file and member will be created. This file and member will contain only the data fields that are in the PC Field Description File. If you are using an FDF, the file type should be data. If you select Source, a file and member is created with two beginning fields in addition to the field of data in the PC file. The two beginning fields consist of sequence numbers and dates. These two extra fields are six bytes long each. You must take these two fields into account when figuring the record length.
Next is the Field Reference Filename. When you opt to create a new data file, the System i™ system needs a model of what the new file should look like. This is where you will key in the file name, which in most cases will be the file name of the file used to create the FDF.
The next parameter is the Record length. This is where you specify the record length when transferring to a flat file (or a source physical file).
The last two parameters are Authority and File Text. File Text is simply a description of the file. Authority refers to the access you want to give other users. There are four options under authority:
|o||All - Gives any user *ALL authority to the file.|
|o||None - Makes the file *EXCLUDE for all other users based on their user profile.|
|o||Read Only - Allows users *USE authority.|
|o||Read/Write - Allows users not only *USE authority, but also gives them the ability to write new data into the file. Use this option if other users will be uploading data to the file.|
|o||Change Create AS/400 Object from No, Replace member only to Yes, Create file and member.|
|o||Switch the iSeries File type from Data to Source.|
|o||Uncheck the Use PC file description check box. This will cause a Source physical file to be created on the iSeries.|
|o||Ensure the Record Length is 92 (extra 12 characters added for sequence number plus the maximum length of data in the file, which is 80).|
Click OK, and click on Transfer Data to AS/400. The next screen will show the record count followed Transfer statistics screen:
Success! The data on the System i system looks similar to the following:
Display Physical File Member
File . . . . . . : SYSTEM Library . . . . : MYLIB
Member . . . . . : SYSTE Record . . . . . : 1
Control . . . . . Column . . . . . : 1
Find . . . . . . .
Transferring a Comma-Separated Variable File to the System i System
In this example, we will be transferring a CSV file to the System i system. A CSV file can be generated by almost any application. It is also known as comma delimited format. The key to transferring a CSV file to the System i system is to have a file already created on the System i system.
One way to create a table (file) on the System i system is to use Interactive SQL, but there are a number of other ways to create files besides using SQL. The files used for file transfer must be externally described.
|Caution: IBM System/36 files or IDDU linked files are not supported when using Client Access for Windows 95 Data Transfer. Refer to APAR SA57157 for details. To link to SA57157 immediately, click here .|
To create the file on the System i system, you must have some sort of table definition. A table definition is nothing more than a list of fields, the type of data each field will contain, and how long each field will be. For QCUSTCDT, a table definition looks similar to the following:
|Field Information||Field Name||Type||Length|
|Balance Due||BALDUE||Numeric||6 with 2 decimals|
|Credit Due||CDTDUE||Numeric||6 with 2 decimals|
Using this information, a table can be created on the System i system. Following is the DDS (Data Description Specification) for QCUSTCDT.
This DDS Member is in a file called QDDSSRC in MYLIB. To create the database file, on the operating system command line, type the following:
CRTPF FILE(MYLIB/QCUSTCDT) SRCFILE(MYLIB/QDDSSRC) SRCMBR(QCUSTCDT)
Press the Enter key. After this command completes, we will have a file in MYLIB called QCUSTCDT.
Note: The following step is not necessary with Client Access V3R1M3, V3R2M0, Client Access Express, and iSeries Access. These versions of Client Access and iSeries Access do not require the OS/400 file to contain records.
For pre-V3R1M3 versions of Client Access, the next step would be to put a dummy record into the table. You can put a dummy record into the file using any means you wish. One method is to use DFU. Another method is to use interactive SQL using the following statement:
INSERT INTO QIWS/QCUSTCDT VALUES(123, 'DUMMY', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)
For all Client Access and iSeries Access versions, we will transfer the file from the System i system to the PC to generate an FDF (Field Description File). Since we know the file we will be transferring up to the System i system is a CSV file, we will need to download the file from the System i system using CSV format.
If the operating system file does not contain any records, you will get a message that the file does not contain any records. When using Client Access V3R1M3, V3R2M0, Express, or iSeries Access, click OK on the message and the FDF file will be created. No data file will be created.
On pre-V3R1M3 versions or Client Access, we would end up with two files, one called C:\QCUSTCDT.CSV which contains only one dummy record and one called C:\QCUSTCDT.FDF, which is the Field Description File.
For this example, we will transfer a file called NEWCUSTS.CSV from a our PC to the AS/400e or iSeries family system. Following is what NEWCUSTS.CSV looks like on the PC:
This file could have come from anywhere; it could have been shipped in from a different branch, exported from Excel, or hand typed by someone. As long as it is a CSV file, it is fine. Note, however, that the field lengths cannot exceed what is allowed in the operating system file. For example, because LSTNAM is defined as a 10-character field, we cannot put in a value of Roeller-Nicholson.
We will now set up the transfer. We will be creating a new file called NEWCUST in MYLIB. Opening Data Transfer to iSeries Server and filling in the first screen looks similar to the following:
Next, click on Details and fill in the desired options. After filling in the necessary options, the Details screen looks similar to the following:
First, we clicked the check box to use the PC file description. This will transfer the file to the System i system using the FDF specified in the File name field. You may have to manually specify the FDF because the default behavior of Data Transfer is to use the filename listed in the PC file name field from the first screen. After specifying the FDF, we changed the Create AS/400 object to Yes, Create file and member. We did this because we are creating a new file. Because we are creating a new data file, we changed the OS/400 file type from Source (which would create a source physical file) to Data (which will create a data file). The System i system must know what fields to create in the new file and their specifications (how long and what type). This is specified with the Field reference file name. You will notice that the field reference file name is the same file we downloaded to create the FDF. Doing this assures us that the FDF and the new System i system file will match.
Click OK, and then click Transfer Data to iSeries Server. The record count is shown followed by the Transfer statistics screen:
We now have a new data file on the System i system that looks similar to the following:
When you close the data transfer session, you are given the option to save the data transfer request. If you select Yes, you will be presented with a window where you enter a File name for the .dtt file that will be created when you complete the save. You can specify the path where you would like to save the transfer request. Once the transfer request is saved, you can run the transfer by clicking on the saved transfer request. You can also either put the saved transfer request on your desktop, or you can create a shortcut to the transfer request.
Note: Data Transfer will allow you to save the request in a format compatible with earlier versions before Client Access Express V5R3. If this behavior is required, select 'Save As...' from the Data Transfer To iSeries or Data Transfer From iSeries 'File' menu and change the 'Save as type' to a Version 1.0 (.DTT) or Version 1.2 (.DTF) format and save the request. In addition, transfer requests in Version 1.x formats will continued to be saved in that format unless the new format is specified.