IBM Improves Run SQL Scripts with ACS Update
September 22, 2021 Alex Woodie
In recent years, Access Client Solutions (ACS) has become an indispensable utility for a range of tasks involving the IBM i. A good reason for that success is the adoption of Run SQL Scripts, which has been enhanced with the latest Technology Refresh for IBM i 7.3 and 7.4.
ACS is a Java-based product that IBM first unveiled in 2012 and that has replaced other versions of Client Access, including the dedicated Windows utility. No matter what type of computer or device you’re using to work with the IBM i server, be it Windows, Linux, or Mac, you can access it with ACS.
The utility includes several must-have tools for working in an IBM i environment, including a 5250 emulator, 5250 printer emulation, data transfer capabilities, IFS file viewing, spool file management, and a virtual console for LAN and HMC management. Run SQL Scripts is also a popular utility, and ACS is also used for updating open source software on IBM i through YUM.
With the September 8 announcements of IBM i 7.3 TR11 and IBM i 7.4 TR5, IBM unveiled ACS version 1.1.8.8. This release brings several enhancements to ACS, primarily to Run SQL Scripts, which allows users to execute pre-written snippets of SQL code, i.e. SQL Scripts.
One of the biggest enhancements with Run SQL Scripts in ACS 1.1.8.8 is the introduction of commit and rollback buttons to the toolbar, which we alluded to in our September 13 story. According to IBM i Chief Architect Steve Will, the new commit and rollback buttons will help prevent users from mistakenly closing out their ACS sessions before they have made changes.
“Within run SQL scripts, within the ACS framework now you will be able to find out — if you notice at the bottom right part of the page — that there is a set of uncommitted changes associated with what you’re currently doing in ACS,” Will said in a COMMON webinar on the new release. “And you’ll be able to rollback what you’ve done so far, or commit it. And then again ACS will tell you that that has been done.”
This feature will be particularly useful in light of the expanded role that ACS is playing in how developers interact with the system through Run SQL Scripts and the bevy of SQL services that have become the preferred way of doing things on the IBM i server. According to Will, the commit and rollback function will alleviate the burden of having to remember to commit a change.
“Most people are who are doing this sort of work all the time have to mentally keep track of, okay am I still in the middle of something?” he said. “We’re providing some help so that you can visually see and then accomplish the commit rollback that you want to do.”
If a user makes a fat-finger mistake and inadvertently leaves a session before committing a change, the Run SQL Scripts component will catch them before they fall. “We will now bring up and say ‘The session has uncommitted changes. Do you want to commit them?’” Will said. “Of course, we leave that up to you. We’re not forcing you to commit something. We’re just giving you the opportunity recognize that you are in the middle of something.”
These Run SQL Script functions were added at the request of users, who submitted multiple requests for enhancement (RFEs). (If you had doubts that IBM was listening, then those doubts have been answered. IBM does listen.)
ACS 1.1.8.8 brings a number of other enhancements, including improved memory management, which will help users who have multiple Run SQL Scripts windows open. There is also now support for searching the job log, and the capability to synchronize recently used files when there are multiple RSS windows. The upload results panel in Run SQL Scripts now has separate fields for schema and table, as well as a replace table option, according to IBM.
Several other changes have been made to other components of ACS not named Run SQL Scripts. And again, many of these are driven by RFEs. For instance, it has added “previous” and “next” buttons to the Journal Viewer in Schemas component of ACS, which was added back in a 2017 update. IBM also added “deterministic” and “inline” columns to the functions list in Schemas, it says.
In the SQL Performance Center component of ACS, IBM has added a search function to the main window as well as to its “analyze reports” function. New buttons have also been added to give users more view options on the main window, amid other changes.
The Data Transfer component of ACS has also been bolstered. With this release, when connecting to an active Excel spreadsheet, IBM has added support for selecting a specific Excel spreadsheet as either an output or an input device.
A variety of fixes were also shipped with this release, according to the IBM Wiki for ACS, which also included a couple of special notes. One of those notes are targeted at users who want to launch the new IBM Navigator for IBM i from ACS (a couple of PTFs are required). The other is for Mac users who want to apply this update atop ACS version 1.1.8.4 or earlier.
ACS 1.1.8.8 release requires Java 11 or higher. It’s available for download here, or it can be obtained by installing the latest HTTP PTF Group. For more information, see www.ibm.com/support/pages/ibm-i-access-acs-updates.
RELATED STORIES
Surprise! It’s IBM i Technology Refresh Time
IBM Bolsters ACS with Database and Administrative Goodies (ACS 1.1.8.2)
While recommended, ACS v1.1.8.8 does not REQUIRE Java 11 for Windows.
From the ACS Getting started document:
3.0 Prerequisites
3.1 Prerequisites (workstation)
IBM i Access Client Solutions runs on most operating systems that support Java 8.0 or higher including various versions of Linux, Mac and Windows.
Note: ACS may not be able to locate Java 8 on macOS 11.0 Big Sur. We recommend installing Java 11.