Running Remote SSH Commands from IBM I CL and RPG over SSH

If you've ever wanted to run a process on another system from an IBM i CL or RPG program and wait until it completes, then I have the perfect CL command for you. QSHSSH is a lightweight wrapper over the open source PASE based ssh command. When running remote SSH commands you can call remote QShell and PASE scripts and commands, CL commands and program calls. And even SQL utilities like sql and db2util.

QSHSSH lets you log into a remote IBM i or any system (Windows, Linux, AIX, etc) running SSH using a private SSH key or user ID and password credentials if desired. Once connected to the remote system your selected command line sequence will execute and return once the entire sequence has completed. Information is returned to the calling RPG or CL program by utilizing the STDOUTQSH outfile or writing STDOUT messages to the job log, a spool file or IFS based log file to log the call and see what just ran. 

And if your commands happen to fail on the remote system, QSHSSH will let you know via an escape message that you can monitor for in your CL and RPG programs.

QSHSSH is part of the popular QShell on i open source utilities for IBM i.

Bring secure, scriptable, and dependable SSH remote command automation to IBM i with QSHSSH.

https://www.github.com/richardschoen/qshoni

Dynamic SQL with Soft Coded Parameters from IBM i CL Programs

Dynamic SQL from IBM i CL commands has never been easier. RUNSQL and RUNSQLSTM have been available for years to run SQL statements or stored SQL scripts. However they have always lacked the ability to create queries that contain dynamic parameter values. Think about how powerful these two commands become with soft coded parameter markers.

Imagine running an embedded dynamic SQL statement from a CL program and also passing parameters to the query on-the-fly. Or think about being able to store an SQL query template in a source member or IFS file with parameter markers ready to be run dynamically.

The following SQL helper commands were recently added to my IBM i QShell on i utilities to make life easier when you need dynamic SQL in a CL program or process. QShell on i is available on the following GitHub site. https://github.com/richardschoen/qshoni 

Read on for an example of how to use the commands:

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.

RUNSQLPRM SQL('INSERT INTO @@LIB.@@FILE (CUSNUM,LSTNAM) 

         VALUES(@@CUSNUM,@@QT@@LSTNAM@@QT)')                 

         PARMS(@@LIB @@FILE @@CUSNUM @@LSTNAM @@QT)         

         PARMVALS(QIWS QCUSTCDT 123456 Test '''')           

         NAMING(*SQL)                                       

         DSPOUTPUT(*YES)

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. You can use whatever values you want but I have found @@ to be an adequate parameter marker header. 

Sample query source member SQLTEST4 in file QSHONI/SOURCE with parameter markers. 

INSERT INTO @@LIB.@@FILE 

(CUSNUM,LSTNAM) VALUES(@@CUSNUM,'@@LSTNAM')

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

RUNSQLSRC SRCFILE(QSHONI/SOURCE)                 

          SRCMBR(SQLTEST4)                       

          PARMS(@@LIB @@FILE @@CUSNUM @@LSTNAM)  

          PARMVALS(QIWS QCUSTCDT 123456 Test)    

          NAMING(*SQL)                           

          DSPOUTPUT(*YES)

Caution when naming your parameter marker values

When using parameter markers make sure each parameter name is fully unique. For example don't have parameters named @@NAME and @@NAME1. If @@NAME were to get processed first in line, it would also replace anything with @@NAME1 as well but leave the 1 at the end of the value. One way around this could be to use a trailing parameter marker as well.  Ex: @@NAME@@ and @@NAME1@@. Or something like: @NAME@ and @NAME1@. I just try to always keep my named parameter markers unique.  

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!