Create Your Own Templates with IBM’s Run SQL Scripts
Rob Berendt explains how to create your own IBM i templates with IBM’s Run SQL Scripts.
By Rob Berendt01/01/2019
One of the things I want to talk about is the library of examples provided in Run SQL Scripts.
First, a little overview: IBM i Access Client Solutions is more than 5250 terminal emulation. Oftentimes people are only shown terminal emulation and even then the sessions are all configured for them and they are told “click this to start your session.”
If you don't have ACS, you can download it here. If you do have ACS, be sure to update your install often (at least once per year) because IBM enhances it two to four times per year.
As you can see there is much more than 5250 emulation and many of the other features are outside of the scope of this article.
In the image above, you’ll find Run SQL Scripts underneath the Database section.
Once you click on Run SQL Scripts you’ll be presented a screen such as the following:
From the Edit drop down select Insert from Examples.
There’s also a widget to do the “Insert from Examples” function.
Once you do that you will get a screen like the following:
At the top you’ll find a search bar. You can start typing in there for something like CREATE TABLE and it will display various examples of that. Below that, in the pale blue, is the category you are searching. I’ll be honest, I’ve created so many categories of my own I forget what the default one it displays is. Underneath that is the various examples within that category. Once you click on one it will display the contents of that example on the right. If it looks like what you are looking for then select Insert and it will insert that example into your code such as the following:
If you want to run that, then put your cursor on the statement, before the ending semicolon, and select the hourglass icon. Your result will be displayed as:
Let’s review the body of the inserted example. Notice the statements are color coded in this example. Green is a comment. Comments in SQL are preceded by two connecting dashes. Statements are blue and variables are black. There are other colors for constants, etc.
The first two comments you see are:
That’s dashdash spacespace the word category followed by a colon. Two spaces and then the category name.
The next line is dashdash spacespace the word description followed by a colon and then the descriptive name.
These are what show up in the following:
The IBM examples are stored elsewhere but custom examples are found on my computer in
The actual name of the file is rather meaningless. When you start up Run SQL Scripts it actually reads every file in this directory for that category and description line.
So, let’s create our own example. We can start from scratch, or insert one of IBM’s examples. Let’s say we like to create our own examples for CREATE TABLE. We’ll pick IBM’s example, modify it a little, and store it as our example. So we start out with a clean slate in Run SQL Scripts, then we do an Edit, Insert From Examples. Pick Data Definition Language (DDL) as the category. Once that opens up we scroll down to Create or Replace Table and select that. We will have the following inserted:
So we change it around to:
Now I may run it to test it. Once satisfied I can then do a File, Save As. Then I make sure I am in my C:\Users\rob\Documents\IBM\iAccessClient\RunSQLScripts\Examples directory. Once there I give it a name like StandardCreateTable.sql.
To see it show up in Insert from Examples, I have to exit Run SQL Scripts and allow it to reread that directory. Once I restart Run SQL Scripts and select Insert from Examples I will see a new category like below:
And in that category I will see my new example:
It’s really that simple to create your own examples.
You can pretty up your examples with more comments, multiple SQL statements and more. I have some to create a new schema (aka library), create several new tables in the schema, populate them with data and run some select statements.
Another template I use is when we get audited. I use Insert from Examples to insert my template, change the string M120180927 to something more date appropriate and let it run. When it’s done I download that directory to my PC, zip it up and present a collection of PDF and CSV files they can analyze further.
Perhaps you want a common location for examples for your entire department. You can do this by modifying the users AcsConfig.properties file.
# Setting the property com.ibm.iaccess.SQLCustomExamples to a specific folder will override the
# default location for Run SQL Scripts, Edit->Insert from Examples... The default location where
# this option normally looks for user provided examples is
# <Configuration Root>/RunSQLScripts/Examples. The IBM provided examples will not be
# affected by setting this property.
For additional reading on some of the many services available:
Rob Berendt is a systems analyst at Group Dekko. More →