Awesome Run SQL Scripts Enhancements

Get familiar with enhancements made to Access Client Solutions from the past few updates.

There have been some fabulous enhancements to Access Client Solutions in the past few updates. The most recent update became available in April (1.1.8.4 April 13, 2020). This version has some very nice enhancements to Run SQL Scripts. IBM documents the enhancements in the readmespacs.txt (Readme Service Pack ACS) file, but the descriptions are generally high level. This blog provides more details about each of them.
 
Start using this latest version if you haven’t already!

SQL language and performance improvements to Content Assist.
Content Assist preferences support, including description text and data type.

Content Assist was introduced with the December 2019 update. It provides an easy way to get a list of available columns to add to your statement. Content Assist is a prompter for SQL. The article Guru: ACS 1.1.8.3 Content Assist Includes Prompt For SQL! gives a nice overview of what Content Assist provides.

With the April 2020 update, preferences have been added for Content Assist, found under the Edit menu:

This brings up a simple box where you can select or deselect whether you want text / labels and data types of display.  

For a simple illustration on the Content Assist improvements, I created a query on one of my favorite Collection Services files (QAPMJOBMI). I can use Content Assist to help me identify the columns I want in my SELECT statement. Content Assist presents me with a list of available columns as well as the labels associated with those columns, which helps me determine the columns I want returned. The preferences allow me to specify whether I want the data type and text labels displayed. 

For comparison, the December 2019 version of Run SQL Scripts, Content Assist only showed me the cryptic field names. Clearly the April enhancement is a great improvement.

Display timestamp of query in results window (RFE 134636)

This is simply a date and time displayed in the bottom right-hand corner of the results window.

 If you don’t see the blue icon in your results window, you need to update to at least the December 2019 version of ACS; this icon allows you to download all the rows returned without needing to scroll down to get them all. I love this feature when I have many rows I need returned.

Details… action added on query result (RFE 134635) (RFE 141298)

This feature makes it easy to select the columns you want returned on your query. It’s easiest to explain with an example. I use the QSYS2.Active_job_Info service quite a bit; it returns many columns of information. Of course, I have created some of my own examples to start with, but there are many times when I need to access different columns of information. Rather than having to look up the column name and type it into the SELECT statement, Run SQL Scripts can help me out. 

I can start with a simple query which returns all the columns:

SELECT * FROM TABLE
(qsys2.active_job_info(detailed_info => ‘ALL’) ) x;

In the query results window, I can review the information that is returned to help me determine which columns I need. I can now right click and find an option called Details…

This opens a window where you get information about each column that is returned. It has the column name, type, precision, etc., as you can see from the screen capture below. I can select the columns I want (control-click to select multiple):

 Right-click after you have selected the columns of choice and find the action to Copy -> Column Name:

Close the window and you can simply paste the column names into your SQL statement. What’s really cool is the commas are included, even to the detail of no comma after the last column name!

SELECT JOB_NAME,
SUBSYSTEM,
SUBSYSTEM_LIBRARY_NAME,
AUTHORIZATION_NAME,
JOB_TYPE,
FUNCTION_TYPE
    FROM TABLE (
            qsys2.active_job_info(detailed_info => ‘ALL’)
        ) x;
 

Option to Suppress Inquiry Messages (RFE 140075)

A new option is available to optionally suppress inquiry messages.

New entries in Insert from Examples for all new IBM i Services

This one should be obvious to almost everyone. IBM has been providing examples for IBM i Services for quite some time. Each release of ACS/Run SQL Scripts comes with more examples. This is a great starting point for many examples. t’s a time-saver as well as a great way to get started if you are new to SQL.

In May of this year, the Db2 Group PTF had many new services, and with this ACS update, there are now examples for all of these new services.
 

---