New QSHONI Commands for SQL and IFS on IBM i

QShell on i utility (QSHONI) makes it easy for traditional CL, RPG, and COBOL programs to call Python utilities and other QShell/PASE utility programs (PHP, Node, Java, etc.) and directly use their output. Last month we updated you on three new QSHONI features: QSHPHPRUN, QSHCALL and QSHPYCALL.

This month we will cover several other new and updated QSHONI features added in the past 12 months. Use the following quick links to read about a specific command.

• Query Data to Selected Temp Table
• Query Data via SQL Source Member to Temp Table
• Run SQL Action via SQL Statement with Parm Replacement
• Run SQL Action from SQL Source with Parm Replacement
• Scan IFS File for Values
• Save Library to IFS Save File / Save IFS Objects to IFS Save File

QSHQRYTMP — Query Data to Selected Temp Table with RUNSQL (Updated)

Sometimes your CL program may need to extract data via SQL and write to a temporary table. The QSHQRYTMP command is a convenient wrapper over RUNSQL for running SQL data select queries, with several special features.
The command can write query results to a temporary output table or an existing table in QTEMP or another library.

The command creates three data areas, all in library QTEMP:

(1) SQLQRYCNT: Count of records returned from the query. It can be used to determine if your query returned any results. Then your program can process or exit accordingly when it has no data. (2) SQLQRYFIL and (3) SQLQRYLIB: Name of the output file and library where results were written from the query, in case another process needs to know the output file name.

A good example use case for QSHQRYTMP might be to call one of the Db2 services to store the results for further processing. Or possibly you want to extract a subset of records from another table for processing by another process that needs to utilize a temporary table. In the 80’s and 90’s we used #GSORT, the FMTDTA (Format Data) command, or the OPNQRYF (Open Query File) command to query data for programmatic usage via a CL program. QSHQRYTMP brings us out of the dark ages for temporary data extraction by using SQL.

NEW: The ability to pass soft-coded substitution parameters to a predefined SQL statement, effectively simulating parameter markers in the RUNSQL command. This makes queries much more dynamic.

Also NEW: The CRTIDCOL parameter, which can add a unique identity column to the output file, to make it easier for other processes to use the output.

This QSHQRYTMP example selects records from table QIWS/QCUSTCDT using soft-coded library and file name parameters @@LIB and @@FILE. The example also adds a unique ID column to the output table after it’s created.

QSHQRYSRC — Query Data via SQL Source Member to Temp Table with RUNSQLSTM (Updated)

The QSHQRYSRC command uses the same process as QSHQRYTMP to run dynamic SQL query commands with or without substitution parameters, except that it uses the RUNSQLSTM command underneath (instead of RUNSQL). In addition, the QSHQRYSRC SQL queries are pulled from a source member, to enable template-based SQL action query scripts, with reusable soft-coded queries.

The following example uses QSHQRYSRC to select records from table QIWS/QCUSTCDT using soft-coded file name parameters of @@LIB and @@FILE stored as part of the SQL statement in source member SQLTEST3. It also adds a unique ID column to the output table after it’s created.

Sample query source member SQLTEST3 in file QSHONI/SOURCE:

Sample call to the QSHQRYSRC command using source member SQLTEST3, and creating a new ID column using substitution variables and values.

RUNSQLPRM — Run SQL Action via SQL Stmt with Parm Replacement

This CL command is a nice way to run dynamic SQL action (non-query) commands via RUNSQL. The command allows us to pass soft-coded parameters to SQL statements, effectively giving the RUNSQL command superpowers by allowing substitution parameters to be used much like parameter markers. This makes action queries much more dynamic when embedding SQL in a CL program.

Here’s an example of RUNSQLPRM inserting a record to QIWS/QCUSTCDT by passing a SQL statement template and parms for @@LIB, @@FILE, @@CUSNUM, @@LSTNAM and @@QT to pass single quotes to a query. All keyword instances get replaced with selected parameter values.

RUNSQLSRC — Run SQL Action from SQL Src with Parm Replacement

This CL command uses the same process as RUNSQLPRM to run dynamic SQL action (non-query) commands, except that it uses the RUNSQLSTM command (instead of RUNSQL). In addition, the RUNSQLSRC SQL queries are sourced from a source member so template SQL action query scripts can be created with reusable soft-coded queries.

Here’s an example of RUNSQLSRC inserting a record to QIWS/QCUSTCDT using a SQL source member that contains template parms for @@LIB, @@FILE, @@CUSNUM and @@LSTNAM.

Sample query source member SQLTEST4 in file QSHONI/SOURCE.

Sample call to the RUNSQLSRC command using source member SQLTEST4 with substitution variables and values.

QSHIFSSCAN — Scan IFS File for Values 

This CL command is a companion to the QSHLOGSCAN command, which can scan STDOUT results for a value. But QSHIFSSCAN can be used to scan the data lines in any selected IFS file for a selected value.

My use case for creating this was a customer who wanted to pipe STDERR to an IFS file via their PASE command call and then look at the results. In their scenario, their PASE command call to curl was never failing with an actual error (this seems to be curl behavior), but was piping data to STDERR rather than STDOUT. They were trapping both STDOUT and STDERR to IFS files. And they needed to be able to scan the contents of a specific IFS file to check the STDERR results to determine success or error of their curl script call. Putting in a call to QSHIFSSCAN did the trick to scan the selected STDERR file and return the results of scanning for the selected value and whether it was found or not. In this example case they were looking for 404 anywhere in the file to indicate a curl call failure.

QSHSAVLIB — Save Library to IFS Save File

QSHSAVIFS — Save IFS Objects to IFS Save File 

These CL commands are convenience wrappers over the SAVLIB and SAV commands for doing library and IFS backups. The results of the commands are exported to an IFS file so that save file can be archived or distributed to another system, network drive, SAN, NAS, OneDrive, Amazon S3 or other location if desired.

Stay in touch

Make sure to visit the QShell on i GitHub site to stay up to date on QSHONI enhancements. Also feel free to suggest new commands or enhancements in the Github site issue pages, and to contribute!

For reliable answers to all of your questions about using open source on IBM i, check out Seiden Group’s SmartSupport program.