Troubleshooting
Problem
This document explains how to transfer a file from a client to an existing file in a library on IBM i.
Resolving The Problem
When transferring files to IBM i there are several things that should be known:
- What is the file type (data format) of the PC file?
- When transferring data to an existing IBM i file, does that file have multiple fields or only one?
- Are you transferring data to a data file or a source file? (Source files have 3 columns: SRCSEQ, SRCDAT, SRCDTA)
The following examples will use the following files:
Uploading to any type of file other than a source physical file requires a client file description file. This is a file which describes the PC data file, the columns of the physical file and the options for date and time formats and separators and decimal separator values. This file is required by the upload function to correctly convert the PC data to the correct format and to insert it into the correct column (which doesn't mean much for a flat file...but it will later). The client file description file is created as a by-product of a data transfer download. It may seem counter-intuitive, but we must start the upload process by performing a download. For this example, I am going to upload data into MYLIB/MYFLATFILE which in this case was created with the DDS source listed previously (it works the same way regardless of how the file was created) so I start with a download from MYLIB/MYFLATFILE:
| o |
PC Files:
C:\WINDOWS\SYSTEM.INI - Flat Text file. There is nothing in the file to delimit any columns. C:\TMP\QCUSTCDT.CSV - Comma-Separated Values file. Columns of data within a row are delimited by commas. C:\TMP\DTXFER.CLP - A flat text file that contains the source code for a CL program.
|
| o |
IBM i Files:
MYLIB/MYFLATFILE - A flat file; that is a file with only one character column. We will upload the SYSTEM.INI file to this file. MYLIB/NEWCUSTS - A copy of QIWS/QCUSTCDT. I has 11 columns that are of type character and numeric. We will the QCUSTCDT.CSV file to this file. QGPL/QCLSRC - A source physical file with a SRCDTA column that is 100 characters wide. (Note that SRCSEQ and SRCDAT are numeric and the data for them is system-generated)
|
Flat Data File Upload to IBM i
This is the most basic transfer. This transfer takes any ASCII, ANSI, or Unicode PC file and transfers the contents to a file on IBM i, converting the characters to the EBCDIC CCSID (Coded Character Set IDentifier) defined by the column in the IBM i file (or the job CCSID if the field is defined with CCSID 65535). If the file does not exist on IBM i, there are several ways to create it. All of the following techniques can be used to create a 'flat file' with 100 characters per row.
This is the most basic transfer. This transfer takes any ASCII, ANSI, or Unicode PC file and transfers the contents to a file on IBM i, converting the characters to the EBCDIC CCSID (Coded Character Set IDentifier) defined by the column in the IBM i file (or the job CCSID if the field is defined with CCSID 65535). If the file does not exist on IBM i, there are several ways to create it. All of the following techniques can be used to create a 'flat file' with 100 characters per row.
| SQL |
CREATE TABLE MYLIB.MYFLATFILE (DATA CHAR(100))
This method creates a table rather than a file. The distinction that matters here is that a table can only have one member, a file might have many.
|
| CRTPF command |
CRTPF FILE(NEWFILE) RCDLEN(100)
This mechanism uses no DDS source to define the fields and it creates a file with a member named NEWFILE with a single character column also named NEWFILE. The CCSID of the column is always 65535 which is not a valid CCSID, it is the CCSID that represents binary data that is not converted. Steps must be taken on the client to force conversion to happen.
|
| DDS | A DDS source member with the following contents can be compiled to create a file with one character column defined with CCSID 37:
The details of creating files with DDS is beyond the scope of this technote. For more information about using DDS, see: DDS for physical and logical files
|
Uploading to any type of file other than a source physical file requires a client file description file. This is a file which describes the PC data file, the columns of the physical file and the options for date and time formats and separators and decimal separator values. This file is required by the upload function to correctly convert the PC data to the correct format and to insert it into the correct column (which doesn't mean much for a flat file...but it will later). The client file description file is created as a by-product of a data transfer download. It may seem counter-intuitive, but we must start the upload process by performing a download. For this example, I am going to upload data into MYLIB/MYFLATFILE which in this case was created with the DDS source listed previously (it works the same way regardless of how the file was created) so I start with a download from MYLIB/MYFLATFILE:
I don't want to create the file C:\TMP\IRRELEVANT.TXT and I don't want to actually download any data, I just want the .FDFX file to be created so there are two more steps. The first is to click the 'Details' button and set the 'File type' to the type that matches my PC data file. Since it isn't tab delimited and I don't want any tabs within the file to cause data truncation, I set the type to 'Text (.txt)'. My file comes from US English copy of Windows so I set the 'Translate system data to:' to Windows-1252, the default ANSI code page for my PC. Finally, the whole reason for this is to create the .FDFX file so I check the box labelled 'Save client file description' and give it a path and name, then click 'OK'.
Next, click 'Data Options' and type "1=0" in the 'Where' clause. This is what prevents the transfer from downloading any data and simply creating my .FDFX file.
Click 'OK' to close the 'Change Data Options' window and click the 'Start Transfer' button. You get an informational message window that states 'No data was matched to the specified options.' (because 1 does not ever equal 0). The PC data file is not created but the .FDFX file is. Now we proceed to the actual upload. I select the upload tab in the data transfer tool (the tab with the green arrow pointing upward). I provide the name of my PC data file and the physical file that I am uploading the data to as shown in the following image:
Next, click the 'Details' button and provide the name of the .FDFX file that was just created as shown in the following:
There are options here to override the client file code page. If you find that the conversion isn't correct, it is possible that it wasn't set correctly when the .FDFX file was created, you can change the value from this screen so you don't have to perform the download again. There is also an option to select whether you are creating a new member with this data transfer, creating a file and member, overwriting the existing member or appending to the existing member. My example is using the default option to overwrite the existing member. Click 'OK' to save and exit these settings then press the 'Start Transfer' button to complete the upload.
There is another setting that must be made if the physical file was created in such a way that the column CCSID is set to 65535 (for example, CRTPF without DDS source). Before clicking 'Start Transfer', click 'Properties' and check the option 'Convert CCSID 65535'. This option causes data transfer to convert the data to CCSID that the data transfer server job is using rather than to try to convert to 65535. Recall that CCSID 65535 is not a 'real' CCSID, there are no conversion tables to convert from or to it.
If this data transfer might be performed more than once, it is a good idea to save it so that it can be reused.
Upload To An Externally-Described File With Multiple Columns
This transfer type is essentially the same as transferring to a flat file. The same steps are performed to create the .FDFX file and transfer the file. The difference is that the PC file has data for multiple columns. In the simplest case, the PC data file has the same number of columns in the same order as the physical file. In that case the creation of the .FDFX is exactly the same as in the file file case. The query that is prepared to retrieve the metadata is a simple 'Select * from...' query. If the PC file has the columns in a different order than the IBM i file or only a subset of the physical file columns, then the query used to create the .FDFX file must specify the columns in the order that they appear in the PC file. As an example, I have a CSV file that has data for some, but not all of the columns in the QIWS/QCUSTCDT file. It has the data for the last name, initials, customer number and zip code in that order:
So I need to create client field description file that matches that. I start the data transfer upload process, select my IBM i file, QIWS/QCUSTCDT, my output type is a file, again C:\TMP\IRRELEVANT.TXT and I click on the details button to set the PC file type to .CSV to match my actual PC file type. One of the frequent reasons for data transfer uploads to fail is that the PC file type specified in the .FDFX file is incorrect...this is where the 'File type' setting comes from. I select the option 'Save client file description' and provide and path and name for the file.
Click 'OK' to close this window and click the 'Data Options' button, then the 'Details' button from that window to open the tool to create your request using the graphical interface shown in the following image. In the 'Download Request Details' I select the columns that I have data for in the order that they appear in the PC data file. As I click on each column, the interface adds the columns to the select clause.
Then click 'OK' to return the the 'Change Data Options' screen. Here I add the 1=0 where criteria to prevent the download from creating a data file. Click 'OK' and 'Start Transfer'. I get an inquiry message because I used a PC filename with a .TXT extension and that doesn't match the CSV file type. Press 'OK' to continue, I want the .FDFX to have the CSV file type specified in it. The informational message is displayed telling me that no data was found and the .FDFX file is created. Now I use that .FDFX file with my CSV data file. In the data transfer upload, I have specified my PC data file, the IBM i file and a new member name, SOMECOLS:
Click the 'Details' button next to provide the .FDFX file path and IBM i object creation options. In this case I am going to create a new member with this data. I provided the member name as part of the IBM i file (see previous image) and on the details, I set the transfer to create this member and provide it with some member text:
Click 'OK' to close the details pane and click 'Start Transfer' to complete the transfer. Whether the transfer works or not depends on how the file is defined. In this case the QCUSTCDT file allows null values in most of the fields and it provides default values for others. You can set those options in your DDS source or your SQL DDL specification.
Upload Source Code Into A Source Physical File
A source physical file is a special type of physical file on IBM i. They always contain 3 columns. An example is shown in the following image of the QCLSRC file.
The three columns are SRCSEQ which is a sequence number (was very important back in the days of punched cards), SRCDAT a numeric representation of the date that a line was last modified, and SRCDTA the actual source code itself. The SRCSEQ and SRCDAT columns are always defined the same as show in the previous image as 6 digit numeric columns. The SRCDTA column size varies depending on the length specified when it was created (by using the CRTSRCPF command). When uploading into a source physical file, the SRCSEQ and SRCDAT columns are not required. A data transfer download from a source file will only download the contents of the SRCDTA column by default. Because of the only column that is transferred is always a character column, SRCDTA, uploads to source physical files do not require client field description (.FDFX) files.
In the following example, I upload the following CL source code to a new member in the QGPL/QCLSRC source file. My PC file, C:\TMP\DTXFER.CLP contains the following:
PGM
QSH CMD('java -jar /QIBM/ProdData/Access/ACS/Base/acsbundle.jar +
/Plugin=Download /MikSwens/cust.dtfx; +
java -jar /QIBM/ProdData/Access/ACS/Base/acsbundle.jar +
/Plugin=Maint /KillDaemon')
ENDPGM
QSH CMD('java -jar /QIBM/ProdData/Access/ACS/Base/acsbundle.jar +
/Plugin=Download /MikSwens/cust.dtfx; +
java -jar /QIBM/ProdData/Access/ACS/Base/acsbundle.jar +
/Plugin=Maint /KillDaemon')
ENDPGM
Note that the longest line in this source code is 74 characters long, that will fit into the 80 column SRCDTA field in QCLSRC. I set up the data transfer with the PC file path and name and the name of the source physical file, complete with the member name that I am adding:
Click on 'Details' and set the transfer so that it does not use a client file description and it creates a new member:
Click 'OK' to close the details and 'Start Transfer' to run the data transfer. Once it is complete your data is transferred. There is another step to take though if this is really program source code. Data transfer does not set the member type attribute so that must be done either in PDM or whatever IDE you prefer. Here is an example showing the members of QCLSRC just after the data transfer completed:
If I want to take full advantage of my programming IDE, I have to set the source type column to CLP or CLLE so that the IDE associates the correct compile and build commands.
[{"Type":"MASTER","Line of Business":{"code":"LOB68","label":"Power HW"},"Business Unit":{"code":"BU070","label":"IBM Infrastructure"},"Product":{"code":"SWG60","label":"IBM i"},"ARM Category":[{"code":"a8m0z000000cwMLAAY","label":"Data Access-\u003EData Transfer"}],"ARM Case Number":"","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"All Versions"}]
Historical Number
13022001
Was this topic helpful?
Document Information
Modified date:
31 December 2024
UID
nas8N1019674