IBM i Access Client Solutions Feature Roundup

IBM Access Client Solutions

⎯Visual Explain of SQL statements allows you to see the work that DB2 had to perform to get you the results.

Coloring and formatting your SQL for readability could make a big difference in productivity over un-formatted SQL. It does for me. If you cut and paste SQL from other documents, the formatter can clean it up for you. In a before and after example, SQL initially entered in lower-case with a run-on-sentence approach, after formatting, becomes clear and is split over several lines. Results of using the “Format Selected” option, is illustrated in Figure 1.

There are line numbers in this version of Run SQL Scripts. For large SQL source members, the ability to go to a specific line is activated by clicking in the line number area.

To configure how your SQL will be formatted, use the SQL Formatter -> Configure pane, as shown in Figure 2. Notice various options for naming, case and line length or splitting. Further, an SQL CASE statement can optionally be configured to split lines. See the ways that readability of SQL for developers has improved?

From the Edit -> Insert from Examples menu, you can select one of numerous categories, each of which has more options beneath it. Figure 3 shows the various categories available.

Selecting a category yields a list of available items, each containing SQL example code you can insert into your execution window to run as-is or to modify. Among the DML template examples are the standard statements like SELECT, INSERT, DELETE. The DDL examples include ALTER, CREATE, DROP, LABEL and so forth. Also, included are SQL statements related to creation of Temporal Tables, for those on a release where that option is available.

The addition of IBM i, and DB2 for i services within Run SQL Scripts, gives access to a growing list of services that IBM has provided, plus examples of SQL formatting tricks to adopt in your own applications. The services include system information that you previously needed an API, commands or output files to obtain, for Storage, Security PTF and Work management. Now these capabilities are easily accessed with an SQL Select statement.

Running the PTF Currency Service for IBM i let’s you compare your system to what IBM has released for PTFs. The result information in Figure 4 shows several updates are available for download.

Selecting the User_Info service, for example, gives me an SQL statement with which to view users who have failed sign-on attempts. The service examples also often include links to documentation or different versions for IBM i 7.3, 7.2 or 7.1. See Figure 5.

IBM i Services

The Work Management - ACTIVE_JOB_INFO service can be run a number of ways to provide information similar to that seen with the WRKACTJOB command. Monitoring system activity with services will become easier than prior methods. In fact, with this information readily available in SQL format, you can generate DB2 Web Query reports, charts or graphs, creating a customized system performance dashboard like Figure 6.

Whether you are developing new SQL or looking at SQL statements in existing applications, you may be familiar with the Visual Explain, which was added to ACS in a recent release. Visual Explain is the part of DB2 for i that shows graphically how the query optimizer implements an SQL statement and what happens when it runs. For instance it lets you know whether you are using the improved SQE Query Engine and advises you about process steps, database performance or need for indexes. See Figure 7.

Visual explain, while not a new IBM i feature, has been implemented nicely in ACS. Maybe the most exciting aspect of this product for many people is that Access Client Solutions can be run on multiple platforms. Thanks to being built on Java-based technology, Windows, Linux and MacOS are all supported. The installation is quite easy: download a zip file and expand a folder containing a sub folder for each platform, and run the platform specific installation script in the sub folder.

Integrated File System

The Integrated File System browser, is differently laid out than its Access for Windows counterpart, but gives the same File System support and performs well. Each entry has less information and the ability to customize views appears to be lacking, but right-clicking on any item shows the remainder of the information as seen in Figure 8.

The Mac has the ability to page up and down by holding the function (fn) key and pressing up or down arrows. I was pleasantly surprised to see that with ACS, when I use the Mac scroll gesture on my touchpad, subfiles roll up or down. That’s a great bonus feature!

With regard to speed of execution, I can’t see many differences in performance with various aspects of the ACS client as compared to Access for Windows. The product appears well thought out and it all works. My SQL development tasks are more enjoyable and, I suspect, more precise. Is it possible that ACS on the Mac might out-perform the Windows version of ACS? This isn’t an observation backed up by a benchmark and could be something in my specific configuration or influenced by happiness that I can use ACS natively on a Mac. As a Mac user, I've previously used a Virtual Machine product to run the Windows clients. With this software, both Windows and MacOS users can enjoy doing database development using ACS. I encourage you to download ACS and give it a spin around the block. You’ll probably like it and be on your way to adopting it as your preferred client.

IBM is making development easier for IBM i with their focus on next generation client tools, languages and high performance hardware.

Rick Flagler is an information technology consultant, teacher and mentor.

Like what you just read? To receive technical tips and articles directly in your inbox twice per month, sign up for the EXTRA e-newsletter here.

comments powered by Disqus



2019 Solutions Edition

A Comprehensive Online Buyer's Guide to Solutions, Services and Education.

Untangling Web Query

How metadata can reduce query and report complexity

Protection From Slow Queries

Introducing DB2 for i Adaptive Query Processing

Intelligent Queries

DB2 for i Learning Statistics Engine works smarter

IBM Systems Magazine Subscribe Box Read Now Link Subscribe Now Link iPad App Google Play Store
IBMi News Sign Up Today! Past News Letters