Skip to main content

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.

Multicolored boxes in a wave pattern against a white background

IBM i Access Client Solutions is the replacement product for the deprecated IBM i Client Access for Windows and has been out for a number of years. It has many improvements—among them is an improved “Run SQL Scripts.” Additional features have also been added, including colorization.

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.


IBM-i-1.png

 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:

IBM-i-2.png

From the Edit drop down select Insert from Examples.

IBM-i-3.png

There’s also a widget to do the “Insert from Examples” function.
 
Once you do that you will get a screen like the following:

IBM-i-4.png

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:
IBM-i-5.png

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:

IBM-i-6.png

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:

IBM-i-7.png

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:

IBM-i-8.png

The IBM examples are stored elsewhere but custom examples are found on my computer in 
C:\Users\rob\Documents\IBM\iAccessClient\RunSQLScripts\Examples
 
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:

IBM-i-9-(1).png

So we change it around to:
IBM-i-10.png

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:


IBM-i-12.png
And in that category I will see my new example:

IBM-i-13.png
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.

IBM-i-14.png

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. 
# com.ibm.iaccess.SQLCustomExamples=V:/Shared_Network_drive/OurSQLExamples
 
For additional reading on some of the many services available:
http://ibm.biz/DB2foriServices
https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_73/rzajq/rzajqservicessys.htm
https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_73/db2/rbafzcatalog.htm
https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_73/rzajq/rzajqservicesdb2.htm
 
IBM Systems Webinar Icon

View upcoming and on-demand (IBM Z, IBM i, AIX, Power Systems) webinars.
Register now →