Full-tilt Client Access Application Performance

IT Infrastructure - Other
  • Smaller Small Medium Big Bigger
  • Default Helvetica Segoe Georgia Times

In "Tweaking Client Access Performance" in the May/June 1999 issue of AS/400 Network Expert, I discussed some tips and techniques for improving overall Client Access performance. As a member of the Client Access development group, I also know that there are some steps you can take to improve the speed of individual Client Access applications. In this article, I will focus on four areas where optimal performance is needed for the typical user—ODBC, OLE DB, Client Access Data Transfer, and AS/400 Operations Navigator—and share some tricks and techniques you can use to get that performance boost. Most of the information I will discuss here applies to both AS/400 Client Access for Windows 95/NT (the Windows 95/NT client) and AS/400 Client Access Express for Windows (the Express client) so you can take advantage of these techniques no matter which client you're using.

ODBC's DSN Performance Parameters

ODBC is the most popular method for accessing DB2/400 databases with the Client Access family. With the Express client, there are five key parameters you can use to control the speed at which your applications access a DB2/400 database. The good news is that four of these parameters are also available with the Windows 95/NT client. To improve your ODBC access speed, you generally want to look at the following parameters in your Data Source Name (DSN) configurations:

• Enable lazy close support
• Enable pre-fetch during execute
• Enable data compression (Express client only)
• Record blocking
• OS/400 library view These options can be accessed by going into the ODBC Administration program (available in your Client Access or Express client folder), selecting the Client Access ODBC DSN you want to use for your AS/400 access, clicking the Configure button, and selecting the Performance tab. These steps bring you to the ODBC setup panel for that DSN (Figure 1).

I'll look at each option in depth and how it can rev up ODBC performance. Enable lazy close support is defaulted to on (checked) and it should be kept on for best performance. When this option is on and Client Access receives a SQL command to

free and close a file, the file will not actually be closed until the next request is sent. When this option is set to off, it causes an extra flow to the AS/400 to close the file, adding time to the total operation.

The default for the Enable pre-fetch during execute parameter is off (unchecked). But, in most cases, performance can be improved by turning this option on. Turning it on causes the open and fetch commands to be combined when an SQL Select is executed. As a result, the first block of data is retrieved in advance (before the application asks for it), so communication flows are reduced, leading to quicker completion of the operation. Note, however, that pre-fetch does not work for extended fetch operations, so if your applications works with extended fetches, this parameter should remain turned off.

The default for Enable data compression is off (unchecked), but performance can be improved by turning it on. Turning this option on causes repeated duplicate characters in your downloaded data to be compressed. For example, if the data retrieved from your AS/400 contains lots of sequential embedded zeros, you can reduce the size of the communications flow by turning Enable data compression on. Since this option is only available in Express client, migrating to Express client may help you improve your ODBC application performance. Please note, however, that this option is only used with data coming from the AS/400, not data going to the AS/400.

The Record Blocking area under the Performance setup tab contains two parameters you can use for performance enhancement: type of record blocking to be used and the size of your record-blocking buffer. Although you can disable record blocking altogether (by clicking on the Type drop-down box and selecting Disable record blocking), one of the two blocking options should always be specified for best performance. Block except if FOR UPDATE OF specified is the default, but Block FOR FETCH Only will result in better performance. When record blocking is disabled, multiple rows cannot be received with a single fetch, so more communications flows are needed to complete an operation. Also, you should increase the record block size beyond the 32k default if you are doing large data transfers. Increasing the record block size will result in less overhead and fewer flows. However, if you are doing record display operations, smaller blocking size settings will provide faster response times.

The OS/400 Library View option should be left at the default setting of Default Library List. If you change the option to view all libraries on the system, catalog operations will take much longer.

For ODBC performance, you should also look at the Server panel (accessed by clicking on the Server Tab on the ODBC setup panel). There is a field on this panel called Maximum Field Data Returned that defaults to a value of 32k. This field is used to set the threshold value for large objects transferred from an AS/400 to a PC. If you frequently transfer a large object, you should ensure that this field is set to a size slightly larger than the size of the largest field in that object. Otherwise, the data will be broken into multiple pieces, causing additional flows and degraded performance.

Although all these tips are valuable, there is no single tip that will have the largest impact on ODBC performance because each method is dependent on the data being transferred.

OLE DB—SQL vs. Record-level Access

OLE DB is gaining popularity as a data access method. While there are a number of different ways to access data from the AS/400, OLE DB is the best method for accessing different types of data and it has an easy-to-use interface that provides good performance. Other methods may be faster, but they could cost you in added development time and loss of application portability. The Client Access OLE DB provider is written to the Client Access APIs, so you could see slightly improved performance using those APIs directly, but it would likely take more time to implement that method. If you are just using SQL

statements and SQL stored procedures, the OLE DB provider will have performance comparable to the Express client ODBC driver.

Whether you choose to use OLE DB record-level access or OLE DB SQL support may depend on what your application does. Either method could be faster, depending on the application requirements. SQL can usually be written to process multiple records faster and with fewer lines of code. However, if only simple random reads are required, the record-level access method will be faster. If your application needs to do both, using the right combination of these methods will give you optimum performance.

When using the SQL method, and the SQL statements vary in values from one call to another, you should consider using parameter markers. If you use parameter markers and prepare the SQL statements before executing them, your performance will be improved.

When using record-level access, be aware that the Express client OLE DB provider does not support blocked reads of opened tables. Multiple record access will not work with this technique, so you will need to read the records from the AS/400 one at a time. What you may want to consider doing instead is using SQL SELECT * or a call to an SQL stored procedure to get large recordset objects back to the PC. Then, after you have all of your data back, you can use record-level access support to perform the individual record insert, update, and delete operations. If you choose to use record-level access, I recommend that your AS/400 be at V4R2 or later. There were significant TCP/IP Distributed Data Management (DDM) server improvements made in that release and that improves performance for this method.

Finally, if you are experiencing OLE DB performance problems, one thing you can check is to make sure that error logging and tracing are turned off. IBM provides The CWBZZTRC.EXE tool to show you your current error logging and tracing status and to change those settings. To view your current OLE DB settings, run CWBZZTRC with the View (-V) option as follows:


You can also run the tool with no parameters from a DOS prompt, and you will be shown the options and syntax for changing these settings. When the Express client (or V3R2M0 of the Windows 95/NT client) is installed, the default is to have error logging on

and tracing off. You only need to change the error logging and tracing parameters once by using CWBZZTRC. All subsequent OLE DB applications will use the new parameters until you decide to change them again by using CWBZZTRC.

For OLE DB, the tips having the most impact are those where you choose between SQL and record-level access methods successfully.

Data Transfer

Data transfer performance can be improved by making changes on your AS/400 in conjunction with changes on your PCs. To make your data transfer run faster, you can do the following:

• Make sure you are running the right OS/400 and Client Access version of each product

• Run multiple data transfer requests under a single server job
• Properly manage your prestart jobs and pools on the AS/400 When OS/400 V4R2 and Client Access V3R1M3 were both released in February 1998, there was a significant performance improvement due to overall design changes in how the AS/400 database server interacted with Client Access data transfer. As a result, it's important that both your OS/400 release and your Client Access release are at that level or later.

It's also important to properly manage your AS/400 prestart jobs, as described in "Tweaking Client Access Performance."

In addition, you should also make sure the prestart jobs are not using a job description that is printing the job log on termination.

If you are performing many transfers to the same AS/400 every day, you can improve performance by ensuring that all requests run through a single invocation of the data transfer application. Simply start up the Client Access Data Transfer application (either the Data Transfer From or Data Transfer To program) one time, and then for each transfer, click on the Open option under the File menu and run that transfer (Figure 2). If you do each transfer under a single open of the Data Transfer application, the same database server job is used for each transfer, which reduces the overhead associated with each transfer and saves resources on your AS/400.

If your users perform batch transfers using the DOS RTOPCB.EXE and RFROMPCB.EXE programs, they can also run multiple jobs under the same database server job because each of these programs now allows multiple server requests within a single invocation. So, if you define your transfer requests in .TTO files, you can type a batch transfer command similar to the following to run several transfer requests under one server job:

RTOPCB.EXE xfer1.TTO xfer2.TTO xfer3.TTO

This function is available with V3R1M3 or later of the Windows 95/NT client and with the Express client.

In general, any steps you can take to use a single database server job for multiple data transfers will give you the biggest performance benefit. For additional information on Data Transfer performance, reference Informational APAR II10202, "Windows 95/NT Data Transfer Information—Part 1," at

Operations Navigator Performance

With all the new functions available in Operations Navigator, especially when using Express Client on a V4R4 AS/400, users are spending much more time with this function.

As a result, there are some things you should know that can reduce the amount of time you spend doing tasks in Operations Navigator.

One time-saving technique is to use the Include menu choice, when available, in the Options pull-down menu. (Include will be grayed out for parts of Operations Navigator that don't support this option.) The Include dialog allows you to subset the list of items to be displayed according to your needs. If you are retrieving lists that are much larger than you need, you'll experience a longer wait for the request to complete and there will be more data flowing in your network than is necessary.

An example can be found under the Job Management tree. You can choose to filter what jobs or server jobs you want to view. One thing to be careful of with Operations Navigator is your use of shortcuts. From the File menu on the main Operations Navigator screen (Select File/Create Shortcut), you can create a shortcut on your desktop to whatever is shown in the right OpsNav window at that moment. Using this feature, you might create several shortcuts that will quickly get you to the views that you most commonly work with. Be aware that every time that you activate a shortcut, however, a separate instance of Operations Navigator is opened. Each instance uses a significant amount of memory on your PC, so if your PC has limited memory, only keep open the views that you really need at that time. Otherwise, you will probably experience slow response times.

If you have lots of available memory on your PC, one thing you should be aware of is that if you are waiting for one request to complete, you can always open up a new instance of Operations Navigator and start working on your next request. Doing so can

keep you more productive by doing multiple tasks in parallel. However if you are memory- constrained, opening a new instance of Operations Navigator may be counter-productive.

You should also be aware of a change made in the Express client when connecting to OS/400 V4R4 machines. Work was done to compress the data flowing from the AS/400 to the PC. In some OpsNav situations, it's possible that 32k bytes of data could be compressed down to 1k, which can reduce the amount of traffic in your network, helping everyone's overall performance. However, you must be using OS/400 V4R4 with Express client to use this feature.

When You Feel the Need for Speed

The four applications that I have discussed are the ones most users need to be fast. Using the techniques described here, you should be able to improve response times, reduce network traffic, and more efficiently use your PC memory. The Client Access development group is always striving to make the user's experience as enjoyable as possible, so we want you to be aware of these tips. I hope that you find them useful.

Related Materials

"Tweaking Client Access Performance," AS/400 Network Expert, Jeff Van Heuklon, May/June 1999

IBM Informational APAR II10202, "Windows 95/NT Data Transfer Information—Part 1," at

Full-_tilt_Client_Access_Application_Performance06-00.jpg 600x512

Figure 1: The Performance panel of your DSN ODBC setup contains a number of parameters for speeding up ODBC application performance.

Full-_tilt_Client_Access_Application_Performance07-00.jpg 600x480

Figure 2: You can reduce the overhead associated with Client Access Data Transfer by running each transfer under the same Open of your Client Access Data Transfer application.



Support MC Press Online





  • White Paper: Node.js for Enterprise IBM i Modernization

    SB Profound WP 5539

    If your business is thinking about modernizing your legacy IBM i (also known as AS/400 or iSeries) applications, you will want to read this white paper first!

    Download this paper and learn how Node.js can ensure that you:
    - Modernize on-time and budget - no more lengthy, costly, disruptive app rewrites!
    - Retain your IBM i systems of record
    - Find and hire new development talent
    - Integrate new Node.js applications with your existing RPG, Java, .Net, and PHP apps
    - Extend your IBM i capabilties to include Watson API, Cloud, and Internet of Things

    Read Node.js for Enterprise IBM i Modernization Now!


  • Profound Logic Solution Guide

    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 companyare not aligned with the current IT environment.

    Get your copy of this important guide today!


  • 2022 IBM i Marketplace Survey Results

    Fortra2022 marks the eighth edition of the IBM i Marketplace Survey Results. Each year, Fortra captures data on how businesses use the IBM i platform and the IT and cybersecurity initiatives it supports.

    Over the years, this survey has become a true industry benchmark, revealing to readers the trends that are shaping and driving the market and providing insight into what the future may bring for this technology.

  • Brunswick bowls a perfect 300 with LANSA!

    FortraBrunswick is the leader in bowling products, services, and industry expertise for the development and renovation of new and existing bowling centers and mixed-use recreation facilities across the entertainment industry. However, the lifeblood of Brunswick’s capital equipment business was running on a 15-year-old software application written in Visual Basic 6 (VB6) with a SQL Server back-end. The application was at the end of its life and needed to be replaced.
    With the help of Visual LANSA, they found an easy-to-use, long-term platform that enabled their team to collaborate, innovate, and integrate with existing systems and databases within a single platform.
    Read the case study to learn how they achieved success and increased the speed of development by 30% with Visual LANSA.


  • Progressive Web Apps: Create a Universal Experience Across All Devices

    LANSAProgressive Web Apps allow you to reach anyone, anywhere, and on any device with a single unified codebase. This means that your applications—regardless of browser, device, or platform—instantly become more reliable and consistent. They are the present and future of application development, and more and more businesses are catching on.
    Download this whitepaper and learn:

    • How PWAs support fast application development and streamline DevOps
    • How to give your business a competitive edge using PWAs
    • What makes progressive web apps so versatile, both online and offline



  • The Power of Coding in a Low-Code Solution

    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:

    • Discover the benefits of Low-code's quick application creation
    • Understand the differences in model-based and language-based Low-Code platforms
    • Explore the strengths of LANSA's Low-Code Solution to Low-Code’s biggest drawbacks



  • Why Migrate When You Can Modernize?

    LANSABusiness 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.
    In this white paper, you’ll learn how to think of these issues as opportunities rather than problems. We’ll explore motivations to migrate or modernize, their risks and considerations you should be aware of before embarking on a (migration or modernization) project.
    Lastly, we’ll discuss how modernizing IBM i applications with optimized business workflows, integration with other technologies and new mobile and web user interfaces will enable IT – and the business – to experience time-added value and much more.


  • UPDATED: Developer Kit: Making a Business Case for Modernization and Beyond

    Profound Logic Software, Inc.Having trouble getting management approval for modernization projects? The problem may be you're not speaking enough "business" to them.

    This Developer Kit provides you study-backed data and a ready-to-use business case template to help get your very next development project approved!

  • What to Do When Your AS/400 Talent Retires

    FortraIT managers hoping to find new IBM i talent are discovering that the pool of experienced RPG programmers and operators or administrators is small.

    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:

    • Why IBM i skills depletion is a top concern
    • How leading organizations are coping
    • Where automation will make the biggest impact


  • Node.js on IBM i Webinar Series Pt. 2: Setting Up Your Development Tools

    Profound Logic Software, Inc.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. In Part 2, Brian May teaches you the different tooling options available for writing code, debugging, and using Git for version control. Attend this webinar to learn:

    • Different tools to develop Node.js applications on IBM i
    • Debugging Node.js
    • The basics of Git and tools to help those new to it
    • Using as a pre-built development environment



  • Expert Tips for IBM i Security: Beyond the Basics

    SB PowerTech WC GenericIn this session, IBM i security expert Robin Tatam provides a quick recap of IBM i security basics and guides you through some advanced cybersecurity techniques that can help you take data protection to the next level. Robin will cover:

    • Reducing the risk posed by special authorities
    • Establishing object-level security
    • Overseeing user actions and data access

    Don't miss this chance to take your knowledge of IBM i security beyond the basics.



  • 5 IBM i Security Quick Wins

    SB PowerTech WC GenericIn today’s threat landscape, upper management is laser-focused on cybersecurity. You need to make progress in securing your systems—and make it fast.
    There’s no shortage of actions you could take, but what tactics will actually deliver the results you need? And how can you find a security strategy that fits your budget and time constraints?
    Join top IBM i security expert Robin Tatam as he outlines the five fastest and most impactful changes you can make to strengthen IBM i security this year.
    Your system didn’t become unsecure overnight and you won’t be able to turn it around overnight either. But quick wins are possible with IBM i security, and Robin Tatam will show you how to achieve them.

  • Security Bulletin: Malware Infection Discovered on IBM i Server!

    SB PowerTech WC GenericMalicious programs can bring entire businesses to their knees—and IBM i shops are not immune. It’s critical to grasp the true impact malware can have on IBM i and the network that connects to it. Attend this webinar to gain a thorough understanding of the relationships between:

    • Viruses, native objects, and the integrated file system (IFS)
    • Power Systems and Windows-based viruses and malware
    • PC-based anti-virus scanning versus native IBM i scanning

    There are a number of ways you can minimize your exposure to viruses. IBM i security expert Sandi Moore explains the facts, including how to ensure you're fully protected and compliant with regulations such as PCI.



  • Encryption on IBM i Simplified

    SB PowerTech WC GenericDB2 Field Procedures (FieldProcs) were introduced in IBM i 7.1 and have greatly simplified encryption, often without requiring any application changes. Now you can quickly encrypt sensitive data on the IBM i including PII, PCI, PHI data in your physical files and tables.
    Watch this webinar to learn how you can quickly implement encryption on the IBM i. During the webinar, security expert Robin Tatam will show you how to:

    • Use Field Procedures to automate encryption and decryption
    • Restrict and mask field level access by user or group
    • Meet compliance requirements with effective key management and audit trails


  • Lessons Learned from IBM i Cyber Attacks

    SB PowerTech WC GenericDespite the many options IBM has provided to protect your systems and data, many organizations still struggle to apply appropriate security controls.
    In this webinar, you'll get insight into how the criminals accessed these systems, the fallout from these attacks, and how the incidents could have been avoided by following security best practices.

    • Learn which security gaps cyber criminals love most
    • Find out how other IBM i organizations have fallen victim
    • Get the details on policies and processes you can implement to protect your organization, even when staff works from home

    You will learn the steps you can take to avoid the mistakes made in these examples, as well as other inadequate and misconfigured settings that put businesses at risk.



  • The Power of Coding in a Low-Code Solution

    SB PowerTech WC GenericWhen 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:

    • Discover the benefits of Low-code's quick application creation
    • Understand the differences in model-based and language-based Low-Code platforms
    • Explore the strengths of LANSA's Low-Code Solution to Low-Code’s biggest drawbacks



  • Node Webinar Series Pt. 1: The World of Node.js on IBM i

    SB Profound WC GenericHave 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.
    Part 1 will teach you what Node.js is, why it's a great option for IBM i shops, and how to take advantage of the ecosystem surrounding Node.
    In addition to background information, our Director of Product Development Scott Klement will demonstrate applications that take advantage of the Node Package Manager (npm).
    Watch Now.

  • The Biggest Mistakes in IBM i Security

    SB Profound WC Generic The Biggest Mistakes in IBM i Security
    Here’s the harsh reality: cybersecurity pros have to get their jobs right every single day, while an attacker only has to succeed once to do incredible damage.
    Whether that’s thousands of exposed records, millions of dollars in fines and legal fees, or diminished share value, it’s easy to judge organizations that fall victim. IBM i enjoys an enviable reputation for security, but no system is impervious to mistakes.
    Join this webinar to learn about the biggest errors made when securing a Power Systems server.
    This knowledge is critical for ensuring integrity of your application data and preventing you from becoming the next Equifax. It’s also essential for complying with all formal regulations, including SOX, PCI, GDPR, and HIPAA
    Watch Now.

  • Comply in 5! Well, actually UNDER 5 minutes!!

    SB CYBRA PPL 5382

    TRY 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.

    Request your trial now!

  • Backup and Recovery on IBM i: Your Strategy for the Unexpected

    FortraRobot automates the routine 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:
    - Simplified backup procedures
    - Easy data encryption
    - Save media management
    - Guided restoration
    - Seamless product integration
    Make sure your data survives when catastrophe hits. Try the Robot Backup and Recovery Solution FREE for 30 days.

  • Manage IBM i Messages by Exception with Robot

    SB HelpSystems SC 5413Managing messages on your IBM i can be more than a full-time job if you have to do it manually. 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:
    - Automated message management
    - Tailored notifications and automatic escalation
    - System-wide control of your IBM i partitions
    - Two-way system notifications from your mobile device
    - Seamless product integration
    Try the Robot Message Management Solution FREE for 30 days.

  • Easiest Way to Save Money? Stop Printing IBM i Reports

    FortraRobot 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:

    - Automated report distribution
    - View online without delay
    - Browser interface to make notes
    - Custom retention capabilities
    - Seamless product integration
    Rerun another report? Never again. Try the Robot Report Management Solution FREE for 30 days.

  • Hassle-Free IBM i Operations around the Clock

    SB HelpSystems SC 5413For over 30 years, Robot has been a leader in systems management for IBM i.
    Manage your job schedule with the Robot Job Scheduling Solution. Key features include:
    - Automated batch, interactive, and cross-platform scheduling
    - Event-driven dependency processing
    - Centralized monitoring and reporting
    - Audit log and ready-to-use reports
    - Seamless product integration
    Scale your software, not your staff. Try the Robot Job Scheduling Solution FREE for 30 days.