IBM i Access Client Solutions For Database

If you are an SQL user, you need to get your hands on IBM i Access Client Solutions. After some false starts, it looks as if IBM is heading in the right direction again when it comes to providing tools to help us develop and use SQL. I want to take you through some of the highlights of the database functions of IBM i Access Client Solutions.

Some Background

Over the last few years, trying to find the right tools for the SQL developer has been quite a challenge. There were Run SQL Scripts in System i Navigator, the DDL wizards in System i Navigator and Navigator for i, the Database Maintenance and Plan Cache functionality in System i Navigator and Navigator for i, the Database Perspective in RDi, Data Studio, Start SQL (STRSQL) on green screen, and any open source database interface such as SQuirrelSQL. The chances are you have used a permutation and combination of a few of these tools.

Without a doubt, the most useful of these tools was Run SQL Scripts in System i Navigator, if for no other reason than these were the only tools to provide access to Visual Explain. Unfortunately, IBM did not announce support for System i Navigator in Windows 10.

Then along came IBM i Access Client Solutions (ACS). Although ACS does not (yet) offer the full range of what was available in System i Navigator, it does offer a few benefits over what was there.

The Main Benefits

The main benefits of ACS, from the database developer’s point of view, are:

• ACS is not platform specific. It is Java-based. I cannot tell you how happy that makes this Mac user!
• Run SQL Scripts has been enhanced.
• Run SQL Scripts has Visual Explain.
• Run SQL Scripts can be activated from within RDi.

Getting Started

When you start ACS, you are presented with a window as shown in Figure 1. You define connections to systems using System Configurations in the Management section. Once you have defined one or more connections, you can select the system you are connecting to from the drop-down box above the menu.

Figure 1: Starting IBM i Access Client Solutions.

The items we are interested in are in the Database section: Run SQL Scripts and SQL Performance Center.

Run SQL Scripts

At first glance, Run SQL Scripts bears a striking resemblance to what we are used to, as shown in Figure 2.

Figure 2: Starting Run SQL Scripts.

But when we start to use Run SQL Scripts, as shown in Figure 3, we see our first major difference: the code is color-coded!

Figure 3: Using Run SQL Scripts.

As well as the color coding, there are a few other enhancements, some of which we will look at in more detail in a moment:

• Show line numbers. This option can be disabled/enabled from the view menu.
• Format your SQL statements.
• Toggle comments. A quick way of commenting/un-commenting a number of lines. Available from the edit menu, context menu or shortcut key.
• Insert from Examples is greatly enhanced.
• Global Variables pane now shows Global Variables and Special Registers.

Show Message/Global Variables and Special Registers panes in a separate window.

• Configure multiple JDBC connection configurations and select the one you want to use.

At the time of writing, there are also a few items that are no longer available (but they might sneak back in at a later stage), including:

• Insert Generated SQL.
• SQL Assist/Prompt CL.
• The options to Defer Run History and Change Query Attributes have been removed from the Options menu.
• The menu option for Monitor, which allowed you to start and manage an SQL Performance Monitor.

At least some of these items are scheduled for a future release. You can get details at http://www.ibm.com/support/docview.wss?uid=nas8N1019797.

Some of the menu items have moved around as well, such as:

• Clear Message and Close All Results have moved from the Edit Menu to the View Menu.
• The items in the Options menu are in a different sequence.

And then there are the shortcut keys. The shortcut keys for Run All, Run from Selected, and Run Selected have changed from Ctrl+R, Ctrl+T, and Ctrl+Y to Ctrl+Shift+A, Ctrl+Shift+R and Ctrl R (Command+Shift+A, Command+Shift+R and Command R on Mac).

Formatting SQL

You can format your SQL statements by placing the cursor on a statement and selecting Edit>SQL Formatter>Format Selected or the equivalent shortcut key (Shift Command F for Mac).

Before you format any statements, you might want to configure how you want your statements formatted. Select Edit>SQL Formatter>Configure to be presented with the configuration options shown in Figure 4.

Figure 4: Configuring SQL Formatting.

Insert From Examples

In the past, the best I could say about Insert from Examples was that it was well intentioned but the examples were hard to find and of little practical use. All that has changed.

Select Edit>Insert from Examples to see the window shown in Figure 5. The examples are now grouped in easy-to-find categories in a dropdown list. I found the examples for the “new” IBM i Services extremely useful.

Figure 5: Insert from Examples.

Run SQL Scripts and RDi

In RDi, the option to Run SQL Scripts is available from the Source menu. If you are editing a program with embedded SQL, selecting the SQL statement and choosing Run SQL Scripts will open Run SQL Scripts with the statement copied in.

Initially, you had to be edit an embedded SQL source before the option to Run SQL Scripts showed in the menu but that (hopefully) has been or is about to be corrected.

SQL Performance Center

The SQL Performance Center gives you the options to work with Performance Monitors and the Plan Cache, as shown in Figure 6. This is the same functionality that was available in System i Navigator with a different interface to arrive at the point.

Figure 6: SQL Performance Center.

What Does the Future Hold?

Only IBM can answer that question. I hope that they continue to enhance ACS and RDi in unison. In the long term, I would like to see all of the database functionality removed from the System i Navigator web interface and incorporated into ACS and RDi.

Of immediate benefit would be the addition of Insert Generated SQL and SQL Assist/Prompt CL to Run SQL Scripts. Then there is Index Advisor followed by Database Maintenance Functions. And then there are all the DDL wizards for defining and maintaining a database.

There is still a way to go but we sure are off to a good start. I will keep watching http://www.ibm.com/support/docview.wss?uid=nas8N1019797 for details!

Paul Tuohy is CEO of ComCon, an iSeries consulting company, and is one of the co-founders of System i Developer, which hosts the RPG & DB2 Summit conferences. He is an award-winning speaker who also speaks regularly at COMMON conferences, and is the author of “Re-engineering RPG Legacy Applications,” “The Programmers Guide to iSeries Navigator,” and the self-study course called “iSeries Navigator for Programmers.” Send your questions or comments for Paul to Ted Holt via the IT Jungle Contact page.