I run into people often that are still using STRSQL to query their system. It works but it is clunky and time consuming. Some excuses I hear; it’s all I know, it’s what I am comfortable with, it has all of my history, it’s easy. I would argue that once you try Run SQL Scripts you will never want to use STRSQL again. Run SQL Scripts in ACS is feature rich and actually helps you write SQL. Wait, what!?! How does it help me write SQL? Well let me tell/show you in this blog post. I’m calling this Making the Move…
If you have not installed ACS or don’t know what I am talking about, check out this link (Thanks Wayne Bowers!):
Obtain and Install IBM i Access Client Solutions
I recommend running the latest version of ACS which at the time of this article is 1.1.8.5. Stay current with the releases to get all of the features and benefits. It’s easy to install upgrades.
Below I would like to speak to all of the excuses and show examples of why Run SQL Scripts is better:
It’s all I know:
I understand the challenge and I acknowledge the time it takes to learn something new. However, we all know that technology is advancing every day and with advancement comes easier and better ways of doing things. If you could save 15 to 30 minutes or 1 hour every day would you? That adds up and those are conservative numbers depending on the type of work you do. If you are an analyst those numbers might be low. In the following examples I am going to show you how to use the Run SQL Scripts feature.
It’s what I am comfortable with:
If writing SQL in STRSQL is comfortable for you I guarantee that working in Run SQL Scripts will be even easier and more intuitive for you. IBM has heavily invested in the ACS product to make it easy to use, help you write SQL, format your SQL, download the results, etc. Let’s look at a few examples.
- Insert from Examples – ACS comes packaged with 100’s of examples that you can search through and choose from to insert and run. Just locate the example you would like to use, select Insert, and hit Crtl+R to execute or execute from the menu. You can even add your our examples to this list for commonly referenced statements that you use.
- Content Assist – Start typing and let content assist help you finish it. You can get content assist to help you in any number of ways; creating the statement, selecting columns in the select from, selecting columns for the where, group by, or order by portion of the statement. Depending on where you are in the statement when you hit Ctrl+Spacebar (or Cmd+Spacebar for Mac users) will decide on how Content Assist makes suggestions. In the first screen shot below Content Assist is helping me with options for my where clause. In the second screen shot below Content Assist is helping me finish the statement. Content Assist even explains things to be with the description in the yellow box. It provides field information and explains various portions of the SQL statement (you can see this below as well). Just click or Crtl+Click to select multiple items in the list and hit enter to insert into the SQL statement.
Let’s say I wanted to check the History Log for everything that occurred in the last 30 minutes. I could write something like this. I used content assist to pull the MESSAGE_TIMESTAMP field into my where portion of the statement.
select * from table(qsys2.history_log_info()) where MESSAGE_TIMESTAMP >= current timestamp – 30 minutes;
I execute this and I end up with these results:
But let’s say that I only wanted to see certain columns. I could right-click on the results and select Columns… to only see the columns I want to see. You could also have Content Assist help you in the SELECT FROM portion of the statement to select the desired fields. That would be helpful if you always wanted to see just specific columns. In this case I will get all of the columns and can just deselect the ones I don’t want to see. It’s all about what you are trying to do and what saves you the most time.
Now here is my results set. It doesn’t have to query the system again to show me less columns here, it just filters the results already displayed. You can do this as many times as you want with the results and move the columns around if desired as well.
- Saving Results – now let’s say I want to save the results to review later. If you have Save Results enabled you can right-click on the results windows and select Save Results… and output the results to a variety of different file types (.txt, .csv, .xls, .xlsx, .ods, .dsr). If you don’t have save results enabled you can enabled it through the settings/preferences in Run SQL Scripts.
NOTE: How to Enable Save Results in 1.1.8.5: Go to Options -> Enable Save Results -> select For This Session or Always. If you select Always saving results will persist across Run SQL Scripts sessions and if you close out and reopen.
- Saving SQL for later – if I want to save the SQL I have been working on, to review or use later, I can save it off to a .sql file. I can save this on my local machine, to the IFS on my IBM i, or to a physical file. Click the Save icon and it will give you the 3 options. In this case I am going to save it to my local machine. I can name it something meaningful and save it somewhere for easy retrieval.
Later I can reopen it and take the one statement I had in the file, build on it, add comments to help me remember what I was trying to accomplish, or I could run a series of statements in succession. You can see in the below example that I am using Content Assist and Crtl+Click to get just the columns I want to have inserted into my statement.
Wow, let’s see STRSQL do that… And this is just scraping the surface of what Run SQL Scripts is capable of.
It has all of my history:
This is my favorite one. Wow, I have seen pages and pages of history in STRSQL. By raise of hands, how many times have you had to F9 thirty times to get to the statement you want to use? What happens when you miss the statement you are looking for? Or let’s say you have to scroll to find it…Yikes, sounds painful. Believe me, I know. I started out using STRSQL but when I found out about Run SQL Scripts years ago I made the switch and have not regretted it for a moment. I am going to help you move from STRSQL in 5 easy steps.
- Execute STRSQL from a command line
- Hit F13 and select 2. Print Current Session
- Open Printer Output… from the Actions menu in ACS
- Download and Open the QSYSPRT file that was just created
- Copy and paste the results into a Run SQL Scripts window and edit it
This took me all of about 3 minutes. Granted if your history is extensive it will take longer but you can put a semicolon wherever you want to start and after each statement and just work with what you need now and clean it up over time. Or you can just reference it and write new statements from scratch.
It’s easy:
Viewing Results:
It does not get any easier than reviewing the results. In STRSQL you have to use Shift+F8 and Shift+F7 and sometimes type in the column number to be able to see the whole column to review the columns of results. In Run SQL Scripts you can see more at one time and can scroll or hide columns not needed (as I showed above) to help seeing your results set even easier. They also setup the results in ACS to have color coding with alternating white and grey to separate each row.
Execute CL from Run SQL Scripts:
You can even execute CL commands and prompt for help writing them in Run SQL Scripts.
View Source Code:
Did you know that you can open members in Run SQL Scripts to review them? Go to Open -> Source Physical File -> select the file. Here is an example that we use to get PTFs from IBM. Now I can review my physical files more than 10 lines at a time to see how things look. I would not use this as my daily ILE editor but it is an easy way to review it, print it, or copy and paste it to send to someone else for review.
I hope by seeing just a few of the ways Run SQL Scripts in ACS is better than STRSQL you will give it a try. I am open to comments and feedback on other things you would like to see or ways that you think STRSQL is better. Using the tools that ACS has provided to interact with my IBM i has saved me time, energy, headaches, and has kept me from making mistakes.
Thanks for reading and hopefully you will be convinced to try Run SQL Scripts.
