SQL iQuery

SQL Scripting Tool and Preprocessor

The SQL iQuery Scripting Tool license is included with SQL iQuery. The ability to process SQL statements from within a source file member or an IFS text file is an important feature for today's Modern SQL. This feature is commonly know as an SQL Scripting Tool.

For more information on iQuery Scripting, download our Powerpoint document here.

Return to SQL iQuery home page.

Classic SQL scripting tools allow users to run batches of SQL or other statements. One scripting tool included with IBM i SQL is the the RUNSQLSTM (Run SQL Statements) command. For example, to create a file in a library and add 3 records to that file, the user would normally be required to start SQL (STRSQL) and type in the following 4 SQL statements:

create table QTEMP.EMPFILE (empNo integer not null default 0,
                              empName char(50),                
                              empDept dec(3,0))
insert into QTEMP.EMPFILE values (12, 'Bob Cozzi', 38)
insert into QTEMP.EMPFILE values (21, 'Wayne Evans', 38)
insert into QTEMP.EMPFILE values (32, 'John Sears', 38) 

That's easy enough, but what if you need to run these 4 statements again? You can re-enter STRSQL and page backwards through the history. But an easier way is place these statements in a source file member. An IFS text file works just as well, but for illustration purposes I'm showing only source member usage.

Creating your First SQL Script

Create a source member using whatever method you enjoy. Be sure to specify the source type as SQL; although not strictly required, it is good practice to always have a correct source type. Then launch your editor of choice, such as SEU (via PDM or ATM) or RDi, and open that empty source member.

Next, enter the four SQL statements from above into your source member. Be sure to include a semicolon at the end of every statement. It should end up looking similar to this:

        *************** Beginning of data ********************************
001.00 create table QTEMP.EMPFILE (empNo integer not null default 0,
002.00                       empName char(50),                
003.00                       empDept dec(3,0));
004.00 insert into QTEMP.EMPFILE values (12, 'Bob Cozzi', 38);
005.00 insert into QTEMP.EMPFILE values (21, 'Wayne Evans', 38);
006.00 insert into QTEMP.EMPFILE values (32, 'John Sears', 38);
       ***************** End of data ************************************ 

Now go to a Command Entry display or menu with a Command Line. On the command line, run the SQL script by entering the following:

RUNSQLSTM SRCMBR(MYSCRIPT) SRCFILE(mylib/QSQLSRC) COMMIT(*NONE)

SQL iQuery Scripting tool can also run these statements. To use the iQuery Script tool, the following command would be used:

RUNiQRY SRCMBR(MYSCRIPT) SRCFILE(mylib/QSQLSRC) COMMIT(*NONE)

This assumes the source member is named MYSCRIPT and is stored in the library MYLIB in QSQLSRC source file. Change the above command parameters to match those for your environment.

That's it, you've created and run your SQL script!

There are some good things in RUNSQLSTM, however you may NOT run SQL SELECT statements, and there isn't a good method of dynamic statement generation or script controlling. It does allow you to create SQL Functions and Store Procedures however.

 For example, if the EMPFILE file already exists, do you really want to run the CREATE statement? Or what if you want to run an SQL SELECT statement? For example, let's attempt to query the sample QCUSTCDT database table that is shipped in the QIWS library.

4 > RUNSQL SQL('select * from qiws.qcustcdt')
    SQL (select) statement not allowed.
    RUNSQLSTM or RUNSQL command failed.      

I've used RUNSQL here, as it is just a wrapper for RUNSQLSTM. RUNSQL reads the ad hoc SQL statement, creates a source file in QTEMP, and then passes that file and member name to the RUNSQLSTM infrastructure to process it. Neither support conditional statements outside of UDF/UDTF/SP.

If you need better scripting tool, you best choice is to use SQL iQuery for IBM i.

iQuery Scripting Tool

The SQL Scripting Tool included with iQuery is different from RUNSQLSTM. Since SQL iQuery includes a preprocessor, you have much better control over the statements that are run. There are two significant syntax differences in the scripting syntax in iQuery vs older methods:

  1. iQuery supports the SQL SELECT and related statements. Output is sent to the user-specified device (display, print, IFS, web, etc.)
  2. iQuery scripts supports preprocessor directives that allow you to run secondary SQL statements and control what source code is included in the final, primary SQL statement that is run.
     *************** Beginning of data *************************************
001.00 create table QTEMP.EMPFILE (empNo integer not null default 0,
002.00                       empName char(50),   
003.00                       empDept dec(3,0));
004.00 insert into QTEMP.EMPFILE values (12, 'Bob Cozzi', 38);
005.00 insert into QTEMP.EMPFILE values (21, 'Wayne Evans', 38);
006.00 insert into QTEMP.EMPFILE values (32, 'John Sears', 38);
       ***************** End of data ***************************************** 

To run this using SQL iQuery, you would go to command entry and run the following command.

RUNiQRY SRCMBR(MYSCRIPT) SRCFILE(mylib/QSQLSRC)

What if you want to display the contents of the EMPFILE table? With RUNSQLSTM you can't, but with iQuery, you just run it like any other SQL statement.

RUNIQRY 'SELECT * FROM QTEMP.EMPFILE'

The rows (records) are displayed as:

   EMPNO  EMPNAME                         EMPDEPT     
      12  Bob Cozzi                           38     
      20  Wayne Evans                         38     
      30  John Sears                          38     

If we add a primary SQL statement to our SQL script source member, it would look like the following:

        *************** Beginning of data *************************************
001.00 create table QTEMP.EMPFILE (empNo integer not null default 0,
002.00                       empName char(50),     
003.00                       empDept dec(3,0));
004.00 insert into QTEMP.EMPFILE values (12, 'Bob Cozzi', 38);
005.00 insert into QTEMP.EMPFILE values (21, 'Wayne Evans', 38);
006.00 insert into QTEMP.EMPFILE values (32, 'John Sears', 38);
007.00
008.00 SELECT * FROM QTEMP.EMPFILE;
       ***************** End of data ***************************************** 

Now when this script is run, the content of EMPFILE is created and then listed.

Certainly we don't want to rerun this script because doing so would cause the 3 employee records to be added repeatedly to the file. So let's place some conditional logic around that first set of statements and run them only when the file does not exist. Here's the code:

        *************** Beginning of data *************************************
000.01 #ifNotExists QTEMP/EMPFILE
001.00 create table QTEMP.EMPFILE (empNo integer not null default 0,
002.00                       empName char(50),   
003.00                       empDept dec(3,0));
004.00 insert into QTEMP.EMPFILE values (12, 'Bob Cozzi', 38);
005.00 insert into QTEMP.EMPFILE values (21, 'Wayne Evans', 38);
006.00 insert into QTEMP.EMPFILE values (32, 'John Sears', 38);
006.01 #endif
007.00 SELECT * FROM QTEMP.EMPFILE
       ***************** End of data ***************************************** 

I've used the #ifNotExists and #EndIf preprocessor directives to control the flow of the statements. If the file EMPFILE in library QTEMP does not exist, then the original 4 SQL statements are run, otherwise it skips them and jumps to the SELECT statement.

Suppose you only wanted employees from department 38 to be displayed. Normally you would simply include a WHERE clause on the SELECT statement like this:

SELECT * FROM QTEMP.EMPFILE WHERE empDept = 38

That works great, but if we want to do that in our script, it may also want to dynamically set the department number at runtime. To do that iQuery supports runtime variables. Similar to the old Query/400 SETVAR parameter, iQuery's RUNIQRY command includes the SETVAR parameter to allow runtime substitutions to be specified similar to Query/400. To establish the variable, let's modify the SELECT statement:

 SELECT * FROM QTEMP.EMPFILE WHERE empDept = &DEPT

Assuming the script source member has been change as indicated, we would run the RUNIQRY command and include the SETVAR parameter:

RUNIQRY SRCMBR(MYSCRIPT) SRCFILE(mylib/qsqlsrc)  SETVAR((DEPT 38))

The SETVAR((DEPT 38)) causes the &DEPT symbol in the script source member to be replaced with the number 38.

This is nice, but what if you'd like the department to be specified most of the time, but also allow a default value to be passed in. That's certainly a good option for users.

To do that, the preprocessor supports the #default control. This control establishes a value for a runtime substitution variable if that variable is not specified on the SETVAR parameter. Here's what the full script might look like:

        *************** Beginning of data *************************************
000.01 #default &dept = 38
000.02 #ifNotExists QTEMP.EMPFILE
001.00 create table QTEMP.EMPFILE (empNo integer not null default 0,
002.00                       empName char(50),          
003.00                       empDept dec(3,0));
004.00 insert into QTEMP.EMPFILE values (12, 'Bob Cozzi', 38);
005.00 insert into QTEMP.EMPFILE values (21, 'Wayne Evans', 38);
006.00 insert into QTEMP.EMPFILE values (32, 'John Sears', 38);
006.01 #endif
007.00 SELECT * FROM QTEMP.EMPFILE WHERE empDept = &dept
       ***************** End of data ***************************************** 

The #default defines a variable and assigns it a value if the variable has not previously be defined and assigned a value. This is extremely useful when running scripts from the Command Entry where you may prefer to omit the SETVAR parameter.

Now when RUNIQRY is run, if the SETVAR parameter is omitted, the &DEPT variable defaults to 38. But any value specified on the SETVAR parameter will override the #default control.

In addition to #default there is also #define which, when used, is similar to specifying a value on the SETVAR parameter. The subtle difference is, #define will set the variable to the specified value, replacing any previously assigned value. Whereas the #default control first checks if the variable exists, if it exists, nothing is done to the variable. If it does exist, it creates it and assigns it the value.

More on directives in the next page.

iQuery Directives >>