Guru: How To Use Global Variables In SQL Scripts
January 23, 2017 Michael Sansoterra
I’m writing SQL scripts to do some administrative work. These scripts are run in iNavigator’s RunSQL scripts utility and use the special CL: directive to execute an IBM i command. I stumbled across the CREATE VARIABLE statement and wondered if I could use an SQL variable to substitute a library name within the OS commands.
For example, if I define something like the following in iNavigator:
CREATE OR REPLACE VARIABLE QGPL/LIB CHAR(10) DEFAULT 'JOE';
Can I then use the LIB variable in a CL: command as follows?
CL: DSPLIB LIB(LIB) OUTPUT(*PRINT);
Unfortunately, GLOBAL VARIABLES cannot be used in this manner because the IBM i command interpreter knows nothing of them. However, if you use the QSYS2.QCMDEXC procedure to execute the IBM i commands (instead of using CL:), then it’s easy to place the desired library from a global variable in an IBM i command as follows:
BEGIN DECLARE @CMD VARCHAR(1024); SET @CMD='DSPLIB LIB(' || RTRIM(QGPL.LIB) ||') OUTPUT(*PRINT)'; CALL QSYS2.QCMDEXC (@CMD); END
In this case, the value in variable QGPL.LIB is inserted into the IBM i DSPLIB command string, which is then passed to the QCMDEXC stored procedure for execution. Using QCMDEXC is often preferable to CL: because it should work in any DB2 for i compatible SQL processor (including STRSQL) whereas CL: is a special “trick” supported by IBM GUI SQL tools in iAccess and Access Client Solutions.
Michael Sansoterra is a DBA for Broadway Systems in Grand Rapids, Michigan. Send your questions or comments for Mike via the IT Jungle Contact page.