02
Sat, Nov
2 New Articles

IBM Answers the Top Five Client Access Questions

Typography
  • Smaller Small Medium Big Bigger
  • Default Helvetica Segoe Georgia Times

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.


IBM_Answers_the_Top_Five_Client_Access_Questions05-00.jpg 444x281

IBM_Answers_the_Top_Five_Client_Access_Questions06-00.jpg 444x420

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.


IBM_Answers_the_Top_Five_Client_Access_Questions06-01.jpg 444x348

BLOG COMMENTS POWERED BY DISQUS

LATEST COMMENTS

Support MC Press Online

$

Book Reviews

Resource Center

  • SB Profound WC 5536 Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application. You can find Part 1 here. In Part 2 of our free Node.js Webinar Series, Brian May teaches you the different tooling options available for writing code, debugging, and using Git for version control. Brian will briefly discuss the different tools available, and demonstrate his preferred setup for Node development on IBM i or any platform. Attend this webinar to learn:

  • SB Profound WP 5539More than ever, there is a demand for IT to deliver innovation. Your IBM i has been an essential part of your business operations for years. However, your organization may struggle to maintain the current system and implement new projects. The thousands of customers we've worked with and surveyed state that expectations regarding the digital footprint and vision of the company are not aligned with the current IT environment.

  • SB HelpSystems ROBOT Generic IBM announced the E1080 servers using the latest Power10 processor in September 2021. The most powerful processor from IBM to date, Power10 is designed to handle the demands of doing business in today’s high-tech atmosphere, including running cloud applications, supporting big data, and managing AI workloads. But what does Power10 mean for your data center? In this recorded webinar, IBMers Dan Sundt and Dylan Boday join IBM Power Champion Tom Huntington for a discussion on why Power10 technology is the right strategic investment if you run IBM i, AIX, or Linux. In this action-packed hour, Tom will share trends from the IBM i and AIX user communities while Dan and Dylan dive into the tech specs for key hardware, including:

  • Magic MarkTRY the one package that solves all your document design and printing challenges on all your platforms. Produce bar code labels, electronic forms, ad hoc reports, and RFID tags – without programming! MarkMagic is the only document design and print solution that combines report writing, WYSIWYG label and forms design, and conditional printing in one integrated product. Make sure your data survives when catastrophe hits. Request your trial now!  Request Now.

  • SB HelpSystems ROBOT GenericForms of ransomware has been around for over 30 years, and with more and more organizations suffering attacks each year, it continues to endure. What has made ransomware such a durable threat and what is the best way to combat it? In order to prevent ransomware, organizations must first understand how it works.

  • SB HelpSystems ROBOT GenericIT security is a top priority for businesses around the world, but most IBM i pros don’t know where to begin—and most cybersecurity experts don’t know IBM i. In this session, Robin Tatam explores the business impact of lax IBM i security, the top vulnerabilities putting IBM i at risk, and the steps you can take to protect your organization. If you’re looking to avoid unexpected downtime or corrupted data, you don’t want to miss this session.

  • SB HelpSystems ROBOT GenericCan you trust all of your users all of the time? A typical end user receives 16 malicious emails each month, but only 17 percent of these phishing campaigns are reported to IT. Once an attack is underway, most organizations won’t discover the breach until six months later. A staggering amount of damage can occur in that time. Despite these risks, 93 percent of organizations are leaving their IBM i systems vulnerable to cybercrime. In this on-demand webinar, IBM i security experts Robin Tatam and Sandi Moore will reveal:

  • FORTRA Disaster protection is vital to every business. Yet, it often consists of patched together procedures that are prone to error. From automatic backups to data encryption to media management, Robot automates the routine (yet often complex) tasks of iSeries backup and recovery, saving you time and money and making the process safer and more reliable. Automate your backups with the Robot Backup and Recovery Solution. Key features include:

  • FORTRAManaging messages on your IBM i can be more than a full-time job if you have to do it manually. Messages need a response and resources must be monitored—often over multiple systems and across platforms. How can you be sure you won’t miss important system events? Automate your message center with the Robot Message Management Solution. Key features include:

  • FORTRAThe thought of printing, distributing, and storing iSeries reports manually may reduce you to tears. Paper and labor costs associated with report generation can spiral out of control. Mountains of paper threaten to swamp your files. Robot automates report bursting, distribution, bundling, and archiving, and offers secure, selective online report viewing. Manage your reports with the Robot Report Management Solution. Key features include:

  • FORTRAFor over 30 years, Robot has been a leader in systems management for IBM i. With batch job creation and scheduling at its core, the Robot Job Scheduling Solution reduces the opportunity for human error and helps you maintain service levels, automating even the biggest, most complex runbooks. Manage your job schedule with the Robot Job Scheduling Solution. Key features include:

  • LANSA Business users want new applications now. Market and regulatory pressures require faster application updates and delivery into production. Your IBM i developers may be approaching retirement, and you see no sure way to fill their positions with experienced developers. In addition, you may be caught between maintaining your existing applications and the uncertainty of moving to something new.

  • LANSAWhen it comes to creating your business applications, there are hundreds of coding platforms and programming languages to choose from. These options range from very complex traditional programming languages to Low-Code platforms where sometimes no traditional coding experience is needed. Download our whitepaper, The Power of Writing Code in a Low-Code Solution, and:

  • LANSASupply Chain is becoming increasingly complex and unpredictable. From raw materials for manufacturing to food supply chains, the journey from source to production to delivery to consumers is marred with inefficiencies, manual processes, shortages, recalls, counterfeits, and scandals. In this webinar, we discuss how:

  • The MC Resource Centers bring you the widest selection of white papers, trial software, and on-demand webcasts for you to choose from. >> Review the list of White Papers, Trial Software or On-Demand Webcast at the MC Press Resource Center. >> Add the items to yru Cart and complet he checkout process and submit

  • Profound Logic Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application.

  • SB Profound WC 5536Join us for this hour-long webcast that will explore:

  • Fortra IT managers hoping to find new IBM i talent are discovering that the pool of experienced RPG programmers and operators or administrators with intimate knowledge of the operating system and the applications that run on it is small. This begs the question: How will you manage the platform that supports such a big part of your business? This guide offers strategies and software suggestions to help you plan IT staffing and resources and smooth the transition after your AS/400 talent retires. Read on to learn: