• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • 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.

    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.

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    New Generation Software

    FREE Webinar:

    Creating Great Data for Enterprise AI

    Enterprise AI relies on many data sources and types, but every AI project needs a data quality, governance, and security plan.

    Wherever and however you want to analyze your data, adopting modern ETL and BI software like NGS-IQ is a great way to support your effort.

    Webinar: June 26, 2025

    RSVP today.

    www.ngsi.com – 800-824-1220

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    NGS:  FREE Webinar: Preparing, Presenting, & Using Data with NGS-IQ and Microsoft Power BI. Nov 10
    Fresche:  IBM i staffing for all of your IT needs. Request a FREE estimate. 1-800-361-6782
    Chrono-Logic:  Simplify deployment with LANSA-based Change Management and Deployment solutions.

    Questions Arise Over IBM’s New Cloud Backup for IBM i Sundry Fall Power Systems Peripheral Enhancements

    One thought on “IBM i Access Client Solutions For Database”

    • Ken Killian says:
      June 1, 2023 at 10:16 am

      Why are the pictures show a BLANKS? Can that be fixed?

      Reply

    Leave a Reply Cancel reply

Volume 16, Number 24 -- November 1, 2016
THIS ISSUE SPONSORED BY:

WorksRight Software
Focal Point Solutions Group
UCG Technologies

Table of Contents

  • IBM i Access Client Solutions For Database
  • SQL PL–The FOR Loop
  • Beyond Hello With Node.js

Content archive

  • The Four Hundred
  • Four Hundred Stuff
  • Four Hundred Guru

Recent Posts

  • Public Preview For Watson Code Assistant for i Available Soon
  • COMMON Youth Movement Continues at POWERUp 2025
  • IBM Preserves Memory Investments Across Power10 And Power11
  • Eradani Uses AI For New EDI And API Service
  • Picking Apart IBM’s $150 Billion In US Manufacturing And R&D
  • FAX/400 And CICS For i Are Dead. What Will IBM Kill Next?
  • Fresche Overhauls X-Analysis With Web UI, AI Smarts
  • Is It Time To Add The Rust Programming Language To IBM i?
  • Is IBM Going To Raise Prices On Power10 Expert Care?
  • IBM i PTF Guide, Volume 27, Number 20

Subscribe

To get news from IT Jungle sent to your inbox every week, subscribe to our newsletter.

Pages

  • About Us
  • Contact
  • Contributors
  • Four Hundred Monitor
  • IBM i PTF Guide
  • Media Kit
  • Subscribe

Search

Copyright © 2025 IT Jungle