Editor’s Note: This month, Midange Network Expert asked Karen Orbeck, the primary contact for AS/400 Client Access Express for Windows (Express client) feedback and development liason to the IBM Service organization, to share the top questions that people ask about Express client and the answers to those questions. Here’s what Karen came up with and how it can help your shop.

Question: Why Do All the Character Fields In the File That I Have Selected for Download Show Up As Type HEX Instead of CHAR?

Data transferred from the AS/400e and iSeries 400 system may sometimes appear as hexadecimal numbers rather than ASCII characters. This occurs anytime you label the data on the system as binary data by using a coded character set ID (CCSID) of 65535. This can happen when you use any of the following Express client functions:

• Data Transfer
• ODBC
• Operations Navigator (OpsNav) to view database files
• OLE DB

What Are Database Files with CCSID 65535?

CCSID is a part of the Native Language System (NLS) inside OS/400. A CCSID assigns a value that uniquely identifies the coded graphic character representation used for character data. In other words, it defines how the binary data stored in an OS/400 file should appear to the user. For example, a character field with data x4F marked CCSID 00500 (Multilingual) will appear as an exclamation point (!). The same data in a field with CCSID 00037 (United States and Canada) will appear as a solid vertical bar (|). In a field with CCSID 65535, the Data Transfer function will show the data as 4F, the hex number that is stored in the field.

You assign a CCSID to fields and files during creation. You can create database files on the AS/400e or iSeries 400 system through a number of different methods, but all files fall into two categories: externally described files and flat files.

You can create externally described files by compiling data description specifications (DDS) source code or by using an SQL CREATE TABLE command. You usually create flat files by entering the Create Physical File (CRTPF) command and specifying a fixed record length rather than a DDS source file. When you create either of these file types, a CCSID is assigned to the file or fields in a file as an attribute, which

determines how the data is stored. Which CCSID is assigned depends on how you created the file.

When you compile a DDS source to create an externally described file, the default CCSID of the file (and any character fields in the file) is the same as the Default coded character set identifier parameter of the current job: “If the job coded character set identifier (CCSID) is not 65535, the default CCSID will equal the job CCSID. If the job CCSID is 65535 (the OS/400 default), an appropriate value is set for the default CCSID based on the job’s language identifier (LANGID).” The job’s language identifier comes from the user profile information, which in turn defaults to the appropriate system value.

When you use the CRTPF command to create a fixed record-length file (flat file), the AS/400e or iSeries 400 system will always use the job’s coded character set identifier (as opposed to the job’s default coded character set identifier) to set the file CCSID. Because the OS/400 default for this parameter is 65535, the file is created with CCSID 65535—binary data. Additionally, some tools on the AS/400e or iSeries 400 system such as Query/400 will output database files with character fields defined as CCSID 65535. What Impact Does This Have When You’re Transferring Data To the PC Using Client Access? The CCSID determines how the data is handled when you’re transferring it across platforms. U.S. English uses CCSID 037 and Multilingual support uses CCSID 500, but 65535 by definition implies that there is no character set associated with binary data. In the case of Client Access, if the CCSID of the file or of the individual fields is 65535, no conversion takes place and the data retains its respective EBCDIC hexadecimal value.

Client Access has several settings that you can use to control how the CCSID 65535 data is handled during the transfer:

• For the Express client Data Transfer from an AS/400 program, choose the setting that’s available from the File > Properties > Conversion menu bar selection, and then select the check box for Convert CCSID 65535.

• For ODBC, a setting is available in the ODBC Administrator. On the User DSN tab, highlight the appropriate DSN, click on Configure, select the Translation tab, and then select the Translate CCSID 65535 radio button.

• The Database component of Operations Navigator uses ODBC under the covers, so this component is controlled by the ODBC settings.

• Unlike Client Access ODBC and Data Transfer, the Client Access OLE DB provider does not have a connection property to let a user force conversion of 65535 fields. To force conversion, use the Client Access Express data conversion automation objects to translate the data.

Changing the CCSID Of a Database File

Technically, when you force translation, you are corrupting the data. This corruption is why Client Access continues to ship with force translation disabled by default. Some CCSIDs do not support round-trip conversion. If you use force translation to download a file that really does contain binary data (CCSID 65535) and then upload it back to the AS/400e or iSeries 400 system, the data may be altered and corrupt. Before enabling forced translation, be sure that the data you are accessing really is intended to be displayable character data.

You’re not allowed to change the CCSID of a flat (program-described) file. If you attempt to set the CCSID parameter on either the CRTPF command or Change Physical File (CHGPF) command, an error will occur. To correct the problem, you need to create a file with the correct CCSID using SQL or DDS and then use the Copy File (CPYF) command to copy the data. For example, to create a file to use with the Copy Spool File (CPYSPLF) command (a one-character field that is 132 bytes long), use the following DDS:

R REC1

A F1 132A

Then you would use the CPY command to copy the data to the designated file before downloading it to your PC.

Question: How Do I Download a Spool File To the PC with Client Access Express?

Client Access Express greatly simplifies downloading spool files. Before this client was available, you had to go through quite a few steps to download spool files. First, you had to create a physical file of the appropriate length. You then had to copy the spool file to the database file. After that, you had to use the Copy to PC Document (CPYTOPCD) command to copy it to a PC file in an AS/400 folder or use the Data Transfer function in Client Access to download the file to the PC.

With Client Access Express, Operations Navigator lets you easily drag and drop spool files to the PC and it automatically converts the data from EBCDIC to ASCII. The resulting file is saved as a text (.txt) file. This capability is available via the Printer Output option under the Basic Operations node (Figure 1). You can easily access a list of your spool files and then drag and drop them to the desired location on your PC.

Question: Can I Drag and Drop a File from the AS/400 To the PC Using Operations Navigator?

There has been some confusion in this area as to which files you can legitimately drag and drop. A file can be defined as either a database file in QSYS.LIB (your DB2/400 files) with the .FILE extension or any object within the AS/400 Integrated File System (AS/400 IFS). Operations Navigator does not support dragging and dropping database (QSYS.LIB) file objects to the desktop. However, OpsNav does support dragging and dropping byte-stream files, which basically are all AS/400 IFS files outside of QSYS.LIB.

If you want to transfer files between AS/400s, you can use the Send menu option if you are using Management Central (available within OpsNav). The Send menu option is available on all AS/400 IFS folders, including .FILE objects in QSYS.LIB. Otherwise, you’ll have to perform a save/restore. To use Send, right click on the file to be sent and select Send from the pop-up menu that appears. This will open the Send Files From dialog (Figure 2). You need to configure Management Central with your target AS/400 endpoint systems to use this feature. Just select the Endpoint system to which you want to send the file and OpsNav will do the rest.

Question: Is There Any Need for Me to Reinstall Client Access Express After Upgrading My PC to a New Operating System?

It is very important that you reinstall Client Access Express after you upgrade or migrate a PC to Windows 2000 from Windows 95/98, Windows Millennium Edition (Windows Me), or Windows NT. You have always had to reinstall Client Access Express after you upgrade or migrate a PC from Windows 95/98 or Windows Me to Windows NT because the NT architecture is so different from Windows 9x in the the areas of security, multiple user support, and printer drivers. Note that the Client Access Express installation behaves differently based on whether the client is Windows 9x or Windows NT.

Windows 2000 is similar to the Windows NT architecture, but there are key differences now (and potentially more in the future). Because of these differences, it is a good idea to reinstall Client Access Express even when you’re moving from Windows NT to Windows 2000. The Client Access Express installation performs special Windows 2000-enablement in the areas of AFP printer drivers (different driver files and installation

methods) and SNA Character Stream (SCS) printer drivers (different driver files and installation methods). Support for non-administrative users includes the following:

• Certain registry keys are explicitly made public-writeable to allow functions running under a non-Administrator profile to operate properly.

• You must store certain modifiable files (i.e., PC5250 workstation profiles, Operations Navigator configuration files) on a per-user basis in file system areas accessible by a non- Administrator user. The installation will set up certain files under the My Documents directory—or under the Profile directory for a particular user—instead of storing them under the main Client Access installation directory structure (which could be a restricted access file system area such as c:Program Files).

Question: How Can I Upload an Excel Spreadsheet To the AS/400?

Express client makes it a lot easier to upload an Excel spreadsheet. In Express Data Transfer, you can use a wizard to create a DB2/400 table based on the structure of an Excel file. This feature, coupled with the Data Transfer capabilities in Client Access, makes it possible to do the entire file transfer process from the client side.

In order to transfer PC data to an OS/400 database file (e.g., a physical file where FILETYPE is *DATA) with Client Access Data Transfer, you have to check on two things. For one, a DB2/400 database table (physical file) must be present to receive the data. Second, a file description file (FDF) that describes how the PC file is to be parsed and mapped must be present on the PC. Before this client was available, users fulfilled these two criteria by performing the following tasks, some of which they had to do manually from OS/400:

• Create an externally described file or SQL table
• Download the file using Data Transfer specifying the option to create a PC FDF
• Upload the PC file using that FDF file

With Client Access Express, users can fulfill the criteria by using the wizard, which scans a PC file to determine its file type, creates a table in DB2/400, creates the PC FDF required for uploading the data, and sends the data to the DB2/400 file. Unquestionably, the wizard simplifies the file transfer process, as users no longer have to create the DB2/400 file manually.

To start the wizard function, access the Data Transfer to AS/400 application from Client Access Express. Select Tools, and then select Create AS/400 Database File. This launches the wizard that prompts you for the correct parameters, and the file type detected is displayed in the File Type window (Figure 3). If the file type is incorrect, you must select the correct type from the drop-down box before continuing. Valid file types are as follows:
• ASCII text
• BASIC Sequential
• BIFF3 (MS Excel 3)
• BIFF4 (MS Excel 4)
• BIFF5 (MS Excel 5)
• Comma-separated value (CSV)
• DIF (Lotus)
• Tab-delimited text
• WK4 (Lotus) There are some things to keep in mind when using the wizard. For one, you should close any applications using the PC file that the wizard is going to scan. Also, data in

individual columns should be of the same type (numeric or character), except for field names. Note that the wizard does not support quoted field names and field names that contain spaces, and it does not support Microsoft Excel 95, 97, and 2000 formats. Be sure to save your Excel files to be transferred as Excel 3, 4, or 5 worksheets (not workbooks) or as CSV. However, help is on the way for Excel 95, 97, and 2000 formats. The new Excel types are supported in the new Client Access Express for Windows V5R1, which is being released with OS/400 V5R1 this summer.

Figure 1: Operations Navigator lets you easily drag and drop spool files to the PC and it automatically converts the data from EBCDIC to ASCII.

Figure 2: The Send Files From dialog allows you to easily send an AS/400 IFS file from one AS/400 to another.

Figure 3: The Express client Create AS/400 Database File wizard allows you to easily create and transfer data to an AS/400 file from your PC.

Also Read

RPG Academy - Database Modernization: Entity Relationship Diagrams

RPG Academy - Database Modernization: A Bit of Database Theory

Talking About Modernization Isn’t Good Enough