Guru: How To Use Global Variables In SQL Scripts

January 23, 2017 Michael Sansoterra

Hey, Mike:

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);

–Guru Reader

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.

–Mike

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.