04
Mon, Nov
5 New Articles

Configuring 32-Bit Client Access/400 ODBC, Part 1

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

ODBC, or Open Database Connectivity, is an API that uses a set of drivers and a driver manager to allow applications to access data using SQL. It is commonly used by most of the visual languages, e.g., Visual Basic and Delphi, as well as by Microsoft Access and MS Query to retrieve data from local or remote systems. Since it uses SQL as its search engine, it is heterogeneous and can retrieve data from such varied sources as PCs, the AS/400 and other midrange systems, and mainframes. Newer technologies such as ActiveX Data Objects (ADO) from Microsoft and OLE DB, which is the power behind IBM’s Project Lightning, have begun to look like the wave of the future, but ODBC is a proven workhorse used in thousands of applications. Because of that fact alone, it’s extremely likely that ODBC will be around for many years to come. This article is the first part of a two-part series on configuring 32-bit Client Access/400 ODBC. Part one will take a brief look at what ODBC is, and continue on with a more detailed explanation that takes you from locating the ODBC Administrator Configuration panel through ODBC packages and how to configure them. The second article in the series will pick up with ODBC performance and walk you through the rest of the ODBC configuration process. Finally, I’ll close out the series by taking a look at a few of the things you will need to configure on your AS/400 to take advantage of ODBC.

 

What It Is

 

As I said, ODBC uses drivers and a driver manager to access remote data. In order for this to work, every database management system (DBMS) must have its own unique driver to retrieve data using the rules defined for that DBMS. This keeps the data-retrieval process generic at the application level, allowing software developers to concentrate on writing their applications without having to worry about maintaining the various unique links to all the DBMSs available. These drivers are really just Dynamic Link Libraries (DLLs) that a particular application, such as Client Access/400, uses to access a remote data source. The driver manager is merely another DLL that provides the link to the ODBC


driver’s DLL. When an application needs to retrieve data from a remote data source, it will make calls to the ODBC manager. The drivers defined for that DBMS will then perform the SQL commands required to access the remote data.

In most cases, your DBMS will come with the ODBC DLLs that applications will need to exchange data with the database. Generally, these drivers are provided free of charge, as is the case with the Client Access/400 ODBC drivers. You can also find many ODBC drivers on the Web. For example, the Microsoft Windows 32-bit ODBC drivers can be found on Microsoft’s ODBC home page at http://www.microsoft.com/data/.

That’s a fairly high-level look at what ODBC is. If you would like a more in-depth look at the theory behind ODBC, check out “ODBC Concepts and the Future,” Midrange Computing, October 1996.

 

Getting There

 

Now let’s take a look at how to configure ODBC on your PC. Unless otherwise indicated, this article will use the Client Access/400 32-bit ODBC driver and V3R1M3 of Client Access/400.

In order to configure ODBC, you need to open the ODBC Data Source Administrator. You can find the Data Source Administrator icon in a couple of places. From Control Panel, double-click on the icon labeled 32bit ODBC (see Figure 1) and you’ll be presented with the ODBC Data Source Administrator panel (see Figure 2). You can also select ODBC Administration from the IBM AS/400 Client Access submenu on your Windows 95/NT Programs menu. From here, you select the data source to be used by your application and configure it. What’s a data source? A data source is made up of both the data the user wants to get to and the instructions or rules required for accessing that data. An example of a data source would be your AS/400’s DB2/400 database, the AS/400 itself, and the network used to get to the AS/400.

For my example, I’m going to show you how to configure the Client Access ODBC 32-bit driver. To create a new data source, click on the Add button. You’ll see the Add Data Source panel (Figure 3). From here, click on Client Access ODBC Driver (32-bit) and then click Finish. You’ll be presented with the Client Access ODBC Setup (32-bit) panel (see Figure 4).

On this panel, you will enter a name for your data source; I’ll call the example data source AS/400 Data Source. This is the data source name you will refer to in the applications that need to access the AS/400’s DBMS through ODBC. You’ll see that the Client Access ODBC driver’s description has already been filled in here. You can change this if you want to, but there’s probably not much point in it. You’re not going to directly refer to this description anywhere else. You also need to enter the name of the AS/400 you want to access, as well as a valid user ID on that AS/400.

 

Libraries

 

After you’ve identified the data source and the system you want to connect to, click on the Server tab in the Client Access ODBC Setup window. From here (see Figure 5), you enter the default libraries you want the ODBC-enabled application to access, as well as the Commit mode. The library information you enter here is one of the most important pieces of information in the entire setup process, so let’s explore this a little deeper.

The libraries you enter here are the ones used by the ODBC application during the connection and query process. This means that the files in the libraries you specify here are the only ones that will appear during an SQL transaction. If you forget to include one of your data file libraries in this list, the files in it will not be immediately available to your application. That doesn’t mean you can’t get to those files at all. It just means that to do so will require you to code an explicit SQL declaration that qualifies the file and library you want to access.

When you define the libraries for this data source, you have a couple of options. You can add to the user’s existing library list or you can replace the library list entirely. To add to the existing library list, enter the special value *USRLIBL, followed by the libraries


you want to add. This will add the additional libraries after the user’s library list. If you enter the additional libraries before the *USRLIBL special value, those libraries will be added in front of the user’s library list. You can even put some before the user library list and some after it if you prefer. Note that the library QIWS will always be added to the end of the library list, even if you forget to include it. This library is added so that the ODBC server code on the AS/400 will be in the library path. Without it, you wouldn’t be able to run the SQL command on the AS/400.

What should you enter then on the library list parameter? Consider one thing before you answer: Entering a large list of libraries will decrease the performance of the ODBC driver, because each SQL request will have to search the entire list of libraries you entered. On the other hand, if you don’t enter the library where the data is stored in this parameter, you will have to explicitly name it on each SQL request that needs to get to it. You’re going to have to think about how your application will use the data source and before you make your decision.

The commit parameter is used to specify the commitment control level available to your application when using ODBC. If you don’t specify anything here, COMMIT (*NONE) will be used. This means that ROLLBACK and COMMIT statements will not be allowed in the defined SQL code. There are other commitment levels you can specify here, but they are outside the scope of this article.

Figure 6 shows you the Package(s) tab. Packages are a type of object on the AS/400 that are used to store the control structures and access plans necessary to process an SQL request on the AS/400. With a properly configured data source, the first time an ODBC SQL request is run from your application, a package will be created on the AS/400, and the access plan and control structure information will be stored in it. The next time the same SQL request runs, the package will be used. This will improve performance, because a significant portion of the request will already be in place and can be reused, saving the time of recreating it. In order for your ODBC application to store this information on the AS/400, you must ensure that the Enable Extended Dynamic (package) Support box is checked.

The Default package library para-meter specifies the library on the AS/400 where the package will be created. This value can be overridden by entering an application name in the Application name parameter and a library name in the Package Library parameter.

If you enter a value in the Application name parameter, the Package Library and Package name parameters will be enabled. Enter a specific name in the Package name parameter to have the first SQL request create the package under this name. The name you choose here can be a maximum of seven characters long. This limitation is in effect because ODBC will append a three-character suffix for internal identification.

As additional SQL requests are made, their control information and access plans will be added to the named package. When the package becomes full, it becomes read-only and your application will not be able to add new requests. To avoid this, check the Clear package if full box on this panel. Check the Cache package locally radio button if you want to keep a copy of the package on the PC. This can boost performance of the SQL request with some applications.

Select the desired radio button under Package usage if you want to enable this feature. This option is enabled if you enter an application name. Disable tells ODBC not to use the SQL package. Use says to use the package in a read-only mode, i.e., don’t add new information to the package. Use/add tells ODBC to use this package if it exists, create a new one if it doesn’t, and to add statements to the package as required.

The Unusable package section is used to control what happens when the package becomes unusable. This option is enabled when you put a value in the Application name field. You have three options here, so check the desired radio button for the function you


 

Commit

 

 

Packages

 

want. Selecting Error will return an error to the user if the ODBC package becomes unusable. Warning will return a warning only if the package becomes unusable, and Ignore will ignore any errors. You should at least return a warning to the user, otherwise an unusable package can go undetected. Left unattended, an unusable or damaged package can cause your SQL performance to be seriously degraded.

 

There’s More to Come

 

So far I’ve taken a look at what ODBC is, how to locate the ODBC Administration Configuration panel, and how to partially configure a data source. In the November/ December issue, I’ll look at how to complete the configuration of your ODBC data source and what you’ll need to do on your AS/400 in order to use ODBC. While you’re waiting for the next issue of Client Access/400 Expert, I encourage you to explore the ODBC configuration yourself. Although expert advice such as that found in Client Access/400 Expert is always welcome, nothing can beat the knowledge that can be gained from hands- on experience.

Figure 1: In the Control Panel, double-click on the icon labeled 32bit ODBC to start the ODBC Administrator.


 

Configuring_32-_Bit_Client_Access-_400_ODBC__Part_104-00.jpg 400x343

 

 

Configuring_32-_Bit_Client_Access-_400_ODBC__Part_105-00.jpg 400x325

 

Figure 2: You use the ODBC Data Source Administrator panel to select the data source to be used by your application and configure it Figure 3: From this panel, select the data source driver you want to add.


 

Configuring_32-_Bit_Client_Access-_400_ODBC__Part_105-01.jpg 400x215

 

 

Configuring_32-_Bit_Client_Access-_400_ODBC__Part_106-00.jpg 400x340

 

Figure 4: You will use the ODBC Setup panel to finish your data source creation.

Figure 5: From the Server tab, you can configure library lists and commitment control levels.


 

Configuring_32-_Bit_Client_Access-_400_ODBC__Part_106-01.jpg 400x336

 

 

Configuring_32-_Bit_Client_Access-_400_ODBC__Part_107-00.jpg 400x336

 

Figure 6: The Package(s) tab allows you to control whether and how packages are stored on your AS/400.


SHANNON ODONNELL
Shannon O'Donnell has held a variety of positions, most of them as a consultant, in dozens of industries. This breadth of experience gives him insight into multiple aspects of how the AS/400 is used in the real world. Shannon continues to work as a consultant. He is an IBM Certified Professional--AS/400 RPG Programmer and the author of an industry-leading certification test for RPG IV programmers available from ReviewNet.net.
 
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: