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:

CWBZZTRC -V

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 www.as400.ibm.com/clientaccess/caiixd1.htm.

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 www.as400.ibm.com/clientaccess/caiixd1.htm.

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

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.