Access Client Solutions (ACS) is the client software IBM provides for us to access IBM i. I am shocked when I repeatedly see IBM i shops continuing to use the ancient Client Access for Windows software. You must migrate to ACS if you haven’t already done so! It’s only a matter of time before Microsoft issues a patch that breaks the software or there’s a new security vulnerability identified that affects the product. Because IBM no longer supports it, you’ll then be scrambling to migrate your users to ACS. Do you really want to be explaining to your management why you’re running software that hasn’t been supported in literally years and are now panicking when you could have had a controlled rollout of the currently supported product? And just because you’re running ACS doesn’t mean you shouldn’t pay attention to IBM Security Bulletins. Older versions of ACS have been identified as being exposed to the log4j vulnerability. In other words, you need to stay current! OK, I’ll get down off my soapbox now and explain some features of ACS.

While I use many of the ACS features, the main feature I’ll be using throughout this book is Run SQL Scripts. See Figure 1.1.

Figure 1.1: Run SQL Scripts in Access Client Solutions.

Launching Run SQL Scripts opens a window that allows you to run SQL that you write as well as take advantage of the SQL examples IBM provides and–bonus!–save your work so you don’t have to re-create the SQL every time you want to run it.

First, let’s see how you can take advantage of the shipped examples. After launching Run SQL Scripts, go to the toolbar and choose Edit > Examples > Insert from Examples. Using the dropdown list, choose IBM i Services. I’ve scrolled down to the Security section of the list. Clicking on the name of the service produces an SQL example in the right window as shown in Figure 1.2.

Figure 1.2: Use Insert to add the SQL example to your Run SQL Scripts window.

I learned how to write SQL by first using these examples. I found an example that was close to what I wanted, clicked on the Insert button to add it to my Run SQL Scripts window, and either ran it as is or, more often, modified it to meet my needs. I encourage you to check out these examples, especially if you’re an SQL novice.

The second feature I want to point out in Run SQL Scripts is the ability to save your work. I know that, as an administrator, you don’t have time to re-create your SQL every time you want to use it. To save your work, click on File in the toolbar, then Save As > PC File. You can save the file on your PC or on a common server so your teammates can also access the file. To use the saved file, click on File > Open > PC File. I use this feature ALL.THE.TIME. I have one saved for each presentation in which I’m using SQL examples, I’ve created files for clients and then sent the file to the client for their use, I have another file that I use when investigating new features but don’t want to start from scratch, and so on. In fact, I have saved the SQL examples used throughout this book and you can download and use them yourself!

Finally, many times the results of running the SQL statement need further review or analysis. When this happens, I send the results to a spreadsheet. Here are the directions to accomplish this. Depending on the version of ACS, you’ll either choose Options from the toolbar and then Enable Save Results… > For This Session. Rerun your SQL, click on a cell in the output section, right-click, and choose Save Results. Or for more recent versions of ACS, choose Edit > Preferences. Then, on the General tab, check the box labeled Enable Saving of Results and click OK. In this case, you’ll have to close down your Run SQL Scripts window, reopen it, rerun your SQL, right-click on a cell in the output section, and choose Save Results.… Or right-click on the Results tab (the tab at the bottom of Run SQL Scripts that shows the SQL that was run) and you’ll also get the Save Results option. With either method, you’ll see various formats you can save your output to (.txt, .csv, .xlsx, etc.) as well as be able to specify where the output is to be saved. Saving the results to a spreadsheet allows me to send the results of my analysis to my clients for their review, allows me to document a “before” picture before changes are made, provides documentation for submission to a change-management committee to document upcoming changes, and more. I’m sure you’ll find many ways to use this feature!