How To
Summary
This technote provides an example of how to make use of the database file creation tool built in to the IBM i Access Client Solutions data transfer program.
Objective
You have a PC data file that you want to upload to IBM i as a physical file (or table) but the table does not exist. You could create the table manually with DDS or SQL but the data transfer program has a tool that can scan your PC data file and give you a significant head start on the file creation.
Environment
For this example, I have a text file in comma separated values (csv) format that contain Winsock error codes, name and descriptions. While the error codes themselves are a consistent length the error code names, descriptions and details are varying lengths. Some of the rows do not have a detailed description value at all. Here is an example of the first few lines:
WSAEACCES,10013,"Permission denied.","An attempt was made to access a socket in a way forbidden by its access permissions. An example is using a broadcast address for sendto without broadcast permission being set using setsockopt(SO_BROADCAST)."
WSAEADDRINUSE,10048,"Address already in use.","Only one usage of each socket address (protocol/IP address/port) is normally permitted. This error occurs if an application attempts to bind a socket to an IP address/port that has already been used for an existing socket, or a socket that wasn't closed properly, or one that is still in the process of closing. For server applications that need to bind multiple sockets to the same port number, consider using setsockopt(SO_REUSEADDR). Client applications usually need not call bind at all - connect will choose an unused port automatically."
WSAECONNABORTED,10053,"Software caused connection abort.","An established connection was aborted by the software in your host machine, possibly due to a data transmission timeout or protocol error."
WSAECONNREFUSED,10061,"Connection refused.","No connection could be made because the target machine actively refused it. This usually results from trying to connect to a service that is inactive on the foreign host - i.e. one with no server application running."
WSAELOOP,10062,"Cannot translate name."
WSAENAMETOOLONG,10063,"Name component or name was too long."
WSAEHOSTDOWN,10064,"Host is down.","A socket operation failed because the destination host was down. A socket operation encountered a dead host. Networking activity on the local host has not been initiated. These conditions are more likely to be indicated by the error WSAETIMEDOUT."
WSAENETUNREACH,10051,"Network is unreachable.","A socket operation was attempted to an unreachable network. This usually means the local software knows no route to reach the remote host."
WSAECONNRESET,10054,"Connection reset by peer.","A existing connection was forcibly closed by the remote host. This normally results if the peer application on the remote host is suddenly stopped, the host is rebooted, or the remote host used a ""hard close"" (see setsockopt for more information on the SO_LINGER option on the remote socket.)"
WSAEADDRINUSE,10048,"Address already in use.","Only one usage of each socket address (protocol/IP address/port) is normally permitted. This error occurs if an application attempts to bind a socket to an IP address/port that has already been used for an existing socket, or a socket that wasn't closed properly, or one that is still in the process of closing. For server applications that need to bind multiple sockets to the same port number, consider using setsockopt(SO_REUSEADDR). Client applications usually need not call bind at all - connect will choose an unused port automatically."
WSAECONNABORTED,10053,"Software caused connection abort.","An established connection was aborted by the software in your host machine, possibly due to a data transmission timeout or protocol error."
WSAECONNREFUSED,10061,"Connection refused.","No connection could be made because the target machine actively refused it. This usually results from trying to connect to a service that is inactive on the foreign host - i.e. one with no server application running."
WSAELOOP,10062,"Cannot translate name."
WSAENAMETOOLONG,10063,"Name component or name was too long."
WSAEHOSTDOWN,10064,"Host is down.","A socket operation failed because the destination host was down. A socket operation encountered a dead host. Networking activity on the local host has not been initiated. These conditions are more likely to be indicated by the error WSAETIMEDOUT."
WSAENETUNREACH,10051,"Network is unreachable.","A socket operation was attempted to an unreachable network. This usually means the local software knows no route to reach the remote host."
WSAECONNRESET,10054,"Connection reset by peer.","A existing connection was forcibly closed by the remote host. This normally results if the peer application on the remote host is suddenly stopped, the host is rebooted, or the remote host used a ""hard close"" (see setsockopt for more information on the SO_LINGER option on the remote socket.)"
Steps
The file creation wizard will scan the data file, describe that data with a suggested file layout which I can modify, create the file and also create the .FDFX file used by data transfer uploads. To give the wizard a head start, I start with the data transfer upload screen and fill in the PC file name and IBM i library and table name to be created:
To launch the file creation wizard, you can press the button in the middle of the toolbar, click 'Create IBM i Database File...' from the Actions menu, or press Ctrl+D.
The wizard follows the familiar pattern of taking you through the steps with a series of dialogs that start with the following:
The first step is to provide the name of the input data file. Note that in the following image, the wizard filled that value in from the data transfer upload request that I started.
The next step allows you to specify the PC file type and the encoding of the file. In the following image you see that the wizard selected CSV for me based on the file extension of the input file. It selected the default ANSI code page for my US-English installation of Windows. Note that the detection of character encoding is an all-but-impossible task to automate so you might need to change the encoding if your data file is encoded with a different character set, UTF-8 for example.
The next step asks you to supply the name for the .FDFX file that the data transfer upload requires. The wizard will create this file add that detail to the upload request that we started. The .FDFX file is a 'metadata' file, it describes the layout and details of the PC data file and is required for an upload to an externally described file.
The next step allow you to define the default attributes that will be used in the creation of the table on IBM i. Click the 'Data Options' button to view and change these settings.
There are three tabs in the Data Options: Date/Time, Decimals, and Scan Data Options
Note at the top of the Date/Time there is a check box that is disabled. If my input file had been a spreadsheet, that option would allow me to transfer date and time stored in the spreadsheet to standard date and type column types. The 'Scan Data Options' tab allows me to pre-set what the default character type columns will be, CHAR, VARCHAR, GRAPHIC, and so forth. I'm going to make a change to the default CCSID since I don't want my character data in EBCDIC Thai columns today.
The next step in the wizard is the start of the input file scan process. If your data file has column names (valid SQL column names that is) as the first row of data, check the corresponding box. My data has no such headings, I'll add them after the scan.
After clicking the 'Start Scan' button, the progress bar turns green indicating that the scan is complete. Press the Next button to see the table layout that the scanner presents.
I could accept this and continue but I want to make some changes. I'm going to make the character columns larger to accommodate more data. And there's nothing like good descriptive column names...and these are certainly nothing like good descriptive column names, so those get changed too. Click on a row and press 'Details' to make changes. Here are the changes I'm making:
I provided better column names, some column text, increased the size where necessary and in the last column I changed the data type to VARCHAR and made the column null capable. After all the changes are made the layout looks like the following:
Just a few more easy steps. Clicking 'Next', the wizard prompts you for the system that the table is to be created on.
Then the library and table name to be created:
The next step prompts you for a text description for the new table:
Then the last step, a confirmation dialog with the details of what is about to be created:
After clicking 'Next' I get a confirmation that the creation of the objects has succeeded. Clicking the 'Finish' button closes the wizard and returns me to the data transfer request that I had started.
If you click the 'Details' button, you can confirm that the wizard filled in the details of the name of the .FDFX file, that the transfer is to use that file and that the transfer should replace the existing member.
Clicking the 'Start Transfer' button gets me the following confirmation that my data now resides in the table MIKSWENS/WINSOCK
Document Location
Worldwide
[{"Type":"MASTER","Line of Business":{"code":"LOB66","label":"Technology Lifecycle Services"},"Business Unit":{"code":"BU070","label":"IBM Infrastructure"},"Product":{"code":"SSRQKY","label":"IBM i Access Client Solutions"},"ARM Category":[{"code":"a8m0z000000cwMLAAY","label":"Data Access-\u003EData Transfer"}],"ARM Case Number":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"All Versions"}]
Was this topic helpful?
Document Information
Modified date:
05 May 2025
UID
ibm17232028