20
Sat, Apr
5 New Articles

Configuring the Windows 95 Client Access ODBC Driver

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

The IBM Client Access for Windows 95 client includes an ODBC driver that you can use to connect your favorite ODBC-aware applications to your AS/400 data. There are many configuration options that you can choose to optimize your connection capabilities and performance. This article explains how to create an ODBC connection for your ODBC applications to use. It also shows you what some of the options are on the Client Access driver configuration screen. Armed with this information, you can connect your PC applications to your AS/400 in the fastest manner possible.

 

Basic ODBC

 

As you may know, ODBC is an acronym that stands for Open Data Base Connectivity. It is a data access standard created by Microsoft and other vendors to allow application programs to access data from any type of database that supports the standard. It is also IBM’s preferred means of client/server connectivity for the AS/400. Combined support from industry giants such as IBM and Microsoft ensures that ODBC will be a perennial standard for database access, so it’s worth your while to spend a little time gaining an understanding of how it works. Figure 1 shows a diagram of the ODBC architecture. ODBC consists of three layers of programs that function together to bring the data to your application: the driver manager, the ODBC driver, and the data source.

The driver manager is a program that manages the individual ODBC drivers installed on your system. It is also referred to as the ODBC administrator program. It provides the interface between the applications and the drivers, allows for the management of data sources, and handles some of the application programming functionality.


ODBC drivers are the programs that perform the translation between the external data source and the ODBC programming interface. There are specific drivers for many different data sources, including local PC database files and remote databases such as the AS/400. You can tell which ODBC drivers are installed on your system by looking at the list of available drivers in the ODBC administrator program.

The final layer of the ODBC architecture is the data source. This is the layer that you’ll deal with the most often when retrieving data from the AS/400. Data sources are basically named connections to a database. They’re created from ODBC drivers to refer to a specific database or library on the AS/400. ODBC drivers can have many different settings, including default libraries and connection methods. When you create a data source with the ODBC administrator program, these settings are stored with that data source so that you don’t have to enter the configuration information into the driver every time you want to access your data.

The ultimate goal of configuring ODBC is to create a data source that you can use when accessing data from your AS/400. Let’s do that now.

 

Configuring a Win 95 Client Access Data Source

 

After you install the Client Access ODBC driver using the Client Access setup program, you need to create a data source using the 32-bit ODBC driver manager. This applet is found in the Client Access folder or in the Windows control panel. Start this program and click the Add button to create a new data source. A list of the ODBC drivers installed on your system will be displayed. Select Client Access ODBC Driver (32-bit) from the list and press OK. This brings up the Client Access ODBC driver configuration screen.

 

Keeping a Tab on Things

 

Staying consistent with the Windows 95 user interface, IBM organized the various parameters of the ODBC driver into sections in a tabbed dialog box. These sections are displayed by clicking on the tabs at the top of the configuration dialog box. Let’s take a look at each of these tabs. Under the General tab (Figure 2), there are some parameters that are not optional. One is a name for your data source. The name is used to identify the data source when connecting to your AS/400, so make it something that you can remember. It can be up to 32 characters. You won’t usually have to type the name, so be descriptive. In the System field, you are required to enter the name of the AS/400 to which you want to connect. This must be an AS/400 connection that is defined to Client Access. You can also enter a description for the data source. This description is optional. In the User ID field, you can enter a user profile name which will be used to connect to the AS/400. This parameter is optional. If it is not entered, you will be prompted for it every time you access the AS/400 using this data source.

Figure 3 shows the options under the Server tab. Under this tab, you can set two parameters—the default libraries and the commit mode. The Default libraries parameter controls which libraries are used by the data source. This parameter is very flexible—and very important. When ODBC connections are made, lists of available tables (files) are often created by the client programs. The Default libraries parameter controls what is displayed in those lists. You can specify multiple libraries in this field by separating each entry by comma. The first library named in this list is the default library. This library is important because programs like Microsoft Access and others that aren’t aware of AS/400 libraries do all their work in the default library. For example, if you export a table from Access, it will be placed in the default library. Normally, the libraries in this field replace


any libraries in the user library list. You can tell Client Access ODBC to use the user library list by specifying *USRLIBL as an entry. For example, to have QGPL as the default library and add the users library list, you would enter QGPL, *USRLIBL. You should specify as few libraries as possible here, as searching multiple libraries can adversely affect performance.

The Commit mode parameter determines whether on not commitment control is used during database updates. Commitment control determines when database updates are made, and whether or not they can be undone. Using commitment control can adversely affect performance, so if you don’t need it, you should select the default value of *NONE.

The Format tab (Figure 4) is where you control the format of the data sent back and forth to the AS/400. On this screen, one of the things you can set is the naming convention used by the driver. This will affect how SQL statements are formatted. For most PC applications, you should choose the default of *SQL. If you choose *SYS, the format of your SQL statements will have to be changed to the standard AS/400 naming conventions. For example, with *SQL naming, a particular file in a particular library would be identified with LIBRARY.FILENAME. With the *SYS naming convention, the file would be identified with LIBRARY/FILENAME. Most PC applications won’t generate SQL with this syntax, so you should use the *SQL setting. The other parameters on this screen tell how the ODBC driver should communicate certain types of information to your AS/400. The Decimal separator parameter tells the ODBC driver which character your AS/400 uses as a decimal point. For the United States, a period is the default. For other countries, you may need to select the comma as the decimal separator. The Time and Date Format areas are used to control the date and time formats used. The defaults should work in most cases, but if necessary, use the list boxes to choose an alternate format.

Under the Performance tab (Figure 5), the unique innovations of the IBM driver become apparent. Once again, the default values for the parameters under this tab should suffice for most applications, but understanding the parameters and how they work can help you tune the Client Access driver for your environment.

One of the interesting abilities of the IBM ODBC driver and the AS/400 is the ability to cache SQL statements for reuse. Checking the Enable Dynamic Extended Support checkbox allows the usage of cached dynamic SQL statements that you have already created and use them again. This translates into a speed increase, because the SQL statements can be used without having to be recompiled. When you use dynamic SQL, as is the case with almost all ODBC connections, it is a two step process on the AS/400. First the SQL statements are compiled—that is, the AS/400 looks at the database and determines the best way to retrieve the data. This is sometimes called building an access plan. Then the AS/400 uses the plan to retrieve the data. Creating the plan can take time. Selecting this option allows the AS/400 to reuse plans that have already been created, providing performance benefits.

The Enable Lazy Close support checkbox provides another method for speeding up the ODBC session. SQL statements in an ODBC environment are opened when records are accessed and closed when operations are completed. If the Enable Lazy Close option is selected, the Client Access ODBC driver will not explicitly close a statement until the next database operation is performed. This reduces the amount of data that needs to be transmitted between the client and the AS/400, thereby increasing speed. If this option is turned off, a close will be sent immediately instead of with the next database operation.

The record blocking section controls how the Client Access ODBC driver brings database records back from the AS/400. There are three options—two that enable blocking and one that disables it. Blocking allows multiple records to be transferred in a single


request, thereby reducing the number of back-and-forth data transmissions between the client and the AS/400. If blocking is disabled, records are transmitted to the client one at a time. Two selections control how blocking is performed. FOR FETCH ONLY blocks records only if the records are retrieved by an SQL SELECT statement explicitly containing the clause “FOR FETCH ONLY.” Most client programs that generate SQL statements for ODBC probably will not use that clause, so there is another option. The For update of parameter will block all record retrievals except for records returned by an SQL SELECT statement containing “FOR UPDATE OF.” This means that normal SQL requests, such as those generated by Microsoft Access and other programs, will be blocked. This is the default and provides the fastest operation.

The last parameter under the Performance tab is the OS/400 Library view parameter. It controls which tables (the ODBC term for AS/400 data files) are displayed when a request to view all tables on the system. If this parameter is set to Default Library List, only tables from your library list will be retrieved. If it is set to All Libraries on the System, all tables from all libraries will be retrieved. This will take a long time, so you should leave this setting at the default of Default Library List. IBM placed options that didn’t fit in any of the other tabs under the Other tab (Figure 6). There are three main setting under this tab. The Translation option allows you to choose whether or not binary data will be translated from EBCDIC to ASCII. Binary data is identified on the AS/400 with a CCSID (coded character set identifier) of 65535. The default value of this parameter is to not translate binary data, which will leave it in the same format as it is stored on the AS/400. If you elect to translate the data, it will go through an EBCDIC-to-ASCII translation. This could alter the format of the binary data, leaving it unusable. It won’t change the actual data on the AS/400, however. It will just change the way it is delivered to the ODBC data source. Under most circumstances, you’ll want to leave the translation set to the default.

The Object Description Type setting controls which object description is used to describe the objects. Setting it to OS/400 Object Description will use the object description that most AS/400 people should be used to. If you set it to SQL Object Comment, the OS/400 SQL comment will be used. In my experience, most PC programs don’t display the object descriptions when connecting to an ODBC data source, so setting this parameter may not have any effect.

The Scrollable Cursor option could have possibly been placed under the Performance tab. The default setting, Scrollable Unless Rowset Size is 1, provides a significant performance boost over the other setting of Always Scrollable. Making a cursor, which is basically a placeholder in a set of records returned from the AS/400, scrollable adds some overhead, because the system must keep track of where the cursor is. Nonscrollable cursors are usually quicker, so using them whenever possible has performance advantages. If the rowset size is one, meaning there is only one record in the rowset, it wouldn’t make sense to scroll, so it’s not necessary to create a scrollable cursor.

 

Bits: 16 vs. 32

 

Windows 95 has the capability to run both 16- and 32-bit applications. There are also 16- and 32-bit ODBC drivers, as well as 16- and 32-bit ODBC driver managers. This can cause confusion. At first, it was not possible to use a 32-bit ODBC driver with a 16-bit application. Fortunately, with the ODBC driver manager that ships with Client Access for Windows 95, it is now possible. With the shipping versions, the Client Access setup program should automatically update your system’s path statement to point to this ODBC driver manager. With the beta versions available on the Web, the path had to be changed


manually. Either way, you should have an entry in your path that points to the shared directory in the Client Access directory. For example, on my system, I use the following path entry:

C:PROGRA~1IBMCLIENT~1SHARED

This entry points to the 32-bit ODBC driver manager shipped with Client Access, which supports using 32-bit ODBC drivers in 16-bit applications. Unfortunately, using 16- bit ODBC drivers with 32-bit applications is not supported.

 

Create A DSN And You’re Done...

 

That’s it—for the ODBC configuration that is. Press the OK button and you’ve just created a data source for your AS/400. Now you can use this new data source with your favorite ODBC-enabled applications to retrieve data from your AS/400. There are many parameters for the IBM ODBC driver. Luckily for us, IBM created default settings that provide the best performance in most environments.

Figure 1: ODBC Architecture


 

Configuring_the_Windows_95_Client_Access_ODBC_Driver05-00.jpg 444x412

 

 

Configuring_the_Windows_95_Client_Access_ODBC_Driver06-00.jpg 450x354

 

Figure 2: The General Tab Figure 3: The Server Tab


 

Configuring_the_Windows_95_Client_Access_ODBC_Driver06-01.jpg 450x354

 

 

Configuring_the_Windows_95_Client_Access_ODBC_Driver07-00.jpg 450x354

 

Figure 4: The Format Tab Figure 5: The Performance Tab


 

Configuring_the_Windows_95_Client_Access_ODBC_Driver07-01.jpg 450x354

 

 

Configuring_the_Windows_95_Client_Access_ODBC_Driver08-00.jpg 450x354

 

Figure 6: The Other Tab


Brian Singleton
Brian Singleton is former editor of Midrange Computing. He has worked in the IBM midrange arena for many years, performing every job from backup operator to programmer to systems analyst to technology analyst for major corporations and IBM Business Partners. He also has an extensive background in the PC world. Brian also developed a line of bestselling Midrange Computing training videos, authored the bestselling i5/OS and Microsoft Office Integration Handbook, and has spoken at many popular seminars and conferences.

MC Press books written by Brian Singleton available now on the MC Press Bookstore.

i5/OS and Microsoft Office Integration Handbook i5/OS and Microsoft Office Integration Handbook
Harness the power of Microsoft Office while exploiting the iSeries database.
List Price $79.95

Now On Sale

BLOG COMMENTS POWERED BY DISQUS

LATEST COMMENTS

Support MC Press Online

$0.00 Raised:
$

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: