By now you must be getting used to seeing a new version of IBM i Access Client Solutions (ACS) every time a new Technology Refresh (TR) is released – you are keeping your ptf’s up to date aren’t you?! In the official announcement letter we can read what is new for ACS, but most people do not go there. Most people also do not go to the “IBM i Access – ACS Updates” page either. So my guess is that most ACS users miss a lot of fun with the new enhancements.
When visiting the ACS update page you will learn that most of the enhancements of ACS are triggered by IBM ideas:
Run SQL scripts(RSS):
Prompt for statement variables https://ibm-power-systems.ideas.ibm.com/ideas/IBMI-I-1883
Rich-text copy support https://ibm-power-systems.ideas.ibm.com/ideas/IBMI-I-2202
Active hyperlinks https://ibm-power-systems.ideas.ibm.com/ideas/IBMI-I-323
Support insert/overwrite toggle https://ibm-power-systems.ideas.ibm.com/ideas/IBMI-I-2364
Allow user to set row retrieval block size https://ibm-power-systems.ideas.ibm.com/ideas/IBMI-I-3269
5250 Emulator:
Support certificates with multiple Subject Alternative Name (SAN)
https://ibm-power-systems.ideas.ibm.com/ideas/IBMI-I-3505
Eliminate extra terminal window that appears after applying Windows 11 update
https://ibm-power-systems.ideas.ibm.com/ideas/IBMI-I-3481
If you have an idea for enhancing ACS, the above list is the living proof that IBM is listening. All you need is an IBM ID to enter an idea yourself here.
The idea I want to put in the spotlight in this article is the “Prompt for statement variables”. The request is to add prompt variables and, if possible, global variables in Run SQL Scripts (RSS).
As you can see from the image above we have the option to use a question mark. Resulting in:
select * from qiws.qcustcdt where STATE = ?;
When we run this statement we are prompted to enter a value as shown below:
When using the question mark in a more complex statement (taken from last months article), as the number of question marks increases it is more likely we will get lost in the forest of values:
In order to get around this IBM i is helping us when using IBM i Services:
Please be aware that this second SQL statement will run faster than the first one because the filtering is done one level higher, but the result will be the same.
In order to get some meaningful text instead of “value N” we need to make a small modification:
You may even decide to use this:
This helps to remind yourself to enter the “%” on both sides of you search string.
With this functionality in mind, I would like to take it one step further. Please have a look at this SQL script:
Being able to prompt for entering a value into a Global Variable allows you to use a specific value in a CL command. The example given is just to show what is possible, enabling a user through 5250 emulation or even better Navigator for i might be faster, but please allow me to show you another example of a CL command, which I hope you will never need to use:
The command used here is the command FTP2IBMCMD part of the Must Gather Tool provided by IBM to upload diagnostic data to IBM to allow them to give support in an IBM support case.
As mentioned before using the new RSS prompting for CL commands may sound far fetched, but as my live as a system administrator has evolved, moving away from 5250 emulation to using IBM i Services, it is more likely I already have an RSS session open. Apart from that the number of IBM i LPARs has which need to be managed has increased, so using this technique saves me time, especially if the commands are overlapping many lines. Cut & and Paste has it’s limititations. I always say, if you know something is possible, it is up to you to decide to use it, Yes or No, but why not give it a try?
Leave a Reply