02 - DB2 for i (EN)02a - SQL (EN)02c - Miscellanea DB2 for i (EN)

Utility XSQL :”Compile” SQL objects into desired library – Part.2

Last Updated on 3 November 2019 by Roberto De Pedrini

The RUNSQLSTM command allows you to execute SQL scripts stored in IFS files or in traditional source file members. A limitation of this command is that you can only execute “static” scripts, and there is no way to pass variable parameters to drive the script’s execution (for example, library names where to generate objects or for referencing tables in a VIEW, or filter values for WHERE clauses, sorting fields for ORDER BY, etc…).
Here we present a little free utility that overcomes this limitation: the XSQL command allows you to specify a parameter in which you can pass variable values, which will replace the special “placeholders” appropriately set in the script.
Let’s see in detail the operations; this is the XSQL command prompt:

                       XSQL - Execute SQL script (XSQL)                      
                                                                                
Type choices, press Enter.
                                                                                
 Source library . . . . . . . . . SRCLIB    __________                           
 Source file  . . . . . . . . . . SRCFILE   __________                           
 Member . . . . . . . . . . . . . SRCMBR    __________                           
 Stream file  . . . . . . . . . . STMF      ________________________________     
____________________________________________________________________________     
____________________                                                             
 Parameters . . . . . . . . . . . PARMS     ________________________________     
____________________________________________________________________________     
____________________________________________________________________________     
____________________________________________________________________________     
____________________________________________________________________                

The first four parameters are pretty straightforward, here you specify where the script source is stored (source file member or IFS file). It is the PARMS parameter that does the trick … here you can specify “names” of variables (or better, placeholders) and their value, which will be then replaced in the script where the variable placeholder appears. The replacement is performed on a temporary source member, so that the original script will remain untouched.
The variable names must be enclosed within two delimiter characters; the tool uses the square brackets. In the script source the variables must also be enclosed within delimiters, later we will see examples of how to specify them. The format of each variable to be passed is:

  • open square bracket “[“
  • variable name, as it appears in the script. I use the “CL style”, with an initial ampersand and one or more letters to identify the variables (eg &L, &LIB, etc …)
  • closed square bracket “]”
  • equal sign “=”
  • value to be replaced – WITHOUT QUOTES. The quotes are to be indicated in the script, in order to facilitate building the PARMS parameter string.

Examples of variables: [&L] = LIB001, [ &F] = FILE001, [ &DATE ] = 20191101 etc.
As you can see from the examples, the tool ignores all the spaces within the separators, those between the final separator and the equal sign, and makes a TRIM of the string between the equal sign and the next initial separator. The spaces inside the string are thus preserved.
Let’s see a sample script with placeholders and the corresponding XSQL command to execute it. The script is stored in the QSQLSRC file of the MDUCA1 library:

CREATE TABLE [&L].Tab0101 AS (
SELECT articolo, YEAR(datamovimento) anno,
  SUM(CASE 
  WHEN segno='+' THEN quantita 
  ELSE -1 * quantita 
  END) saldo
FROM   mduca1.movmag00f
WHERE  YEAR(datamovimento) = [&Y]
GROUP BY articolo
ORDER BY articolo, YEAR(datamovimento)
) WITH DATA
;

This script creates in a library (specified in a variable) the table TAB0101 with the items balance obtained from the warehouse transactions of a given year, also passed as variable.
Here is the XSQL command that executes the script, with its variables:

XSQL SRCLIB(MDUCA1) SRCFILE(QSQLSRC) SRCMBR(TAB0101) PARMS('[&L]=MDUCA2   [&Y]=2019 ')

Here is the result:

Dropping the table and running the command with another year, we get the appropriate results:

DLTF MDUCA2/TAB0101
XSQL SRCLIB(MDUCA1) SRCFILE(QSQLSRC) SRCMBR(TAB0101) PARMS('[&L]=MDUCA2   [&Y]=2018 ')

The beauty of this tool is that in the PARMS parameter variables you can set not only the classic “single” field values, but also whole portions of the script to run: sort fields, WHERE clauses, file names, etc. The only limitation is the size of the PARMS parameter (200 characters in this “standard” version).
Let’s see an example where we pass as a variable part of the table name, the destination library and the sorting fields. This is the script:

CREATE TABLE [&L].Saldi[&Y] AS (                
WITH a AS (                                     
SELECT articolo, YEAR(datamovimento) anno,      
  SUM(CASE                                      
  WHEN segno='+' THEN quantita                  
  ELSE -1 * quantita                            
  END) saldo                                    
FROM   mduca1.movmag00f                         
WHERE  YEAR(datamovimento) = [&Y]               
GROUP BY articolo, YEAR(datamovimento)          
)                                               
SELECT * FROM a                                 
ORDER BY [&O]                                   
) WITH DATA  

We run the command passing the year 2018 and the sort order by item code:

XSQL SRCLIB(MDUCA1) SRCFILE(QSQLSRC) SRCMBR(TAB0102) PARMS('[&L]=MDUCA2 [&Y]=2018  [&O]=articolo  ')  

Let’s check the results:

The table SALDI2018 has been created with the warehouse balances of that year, ordered by item code.
Now let’s pass the year 2019 and a different ordering criteria:

XSQL SRCLIB(MDUCA1) SRCFILE(QSQLSRC) SRCMBR(TAB0102) PARMS('[&L]=MDUCA2 [&Y]=2019 [&O]=saldo DESC')  

Et voilà! Now we have a new table (SALDI2019) with the rows ordered by decreasing balance.

Thanks to this little tool we can add considerable flexibility to our SQL scripts.

The full sources of the XSQL tool can be found on GitHub at this link:

https://github.com/MD2706GIT/XSQL

Related Posts
DB2 for i SQL – String Manipulation – POSSTR-LOCATE-LOCATE_IN_STRING (EN)

Introduction Often, in our applications, we need to work with text strings, and DB2 SQL can come in very useful Read more

DB2 for i – FAQ & Howtos (EN)

DB2 Database and SQL ... maybe the most important things on IBM i platform: here's a collection of FAQs, tips Read more

IBM i 7.4 Announcement (En)

Comes directly with the Easter egg this IBM announcement for the news of the IBM i 7.4 version, iNext version Read more

Generated Always Columns (EN)

Introduction "Generated Always Column": are columns, table fields, filled by DB2 engine: something like columns with a default value but Read more

About author

Senior IBM i Analyst/Developer and technical writer. Former collaborator of the Italian edition of "System i News" magazine and author of several publications about tools and development practices for the IBM i platform.

Leave a Reply

Your email address will not be published. Required fields are marked *