IBM i Access Client Solutions For Database
November 1, 2016 Paul Tuohy
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.
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:
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.
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.
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!
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 Message/Global Variables and Special Registers panes in a separate window.
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:
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:
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).
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.
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.
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.
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.