Executing CL commands using RUNSQLSTM

The Run SQL Statements command, RUNSQLSTM, runs all of the SQL statements that are in a particular source member. I use the command a lot as I put all of my SQL statements to create Tables, Views, Indexes, and the statements to Alter them into source members, and I update the member whenever I make a change to the object. I also put miscellaneous groups of SQL statements into source members, that I can then execute whenever I want.

I use RUNSQLSTM so much I have a PDM option defined so I don't have to type in the command and all the parameters I care about.

Opt Command RS RUNSQLSTM SRCFILE(&L/&F) SRCMBR(&N) COMMIT(*NONE) ERRLVL(20)

In the past if I ever wanted to run a CL command in one of these member I would use SQL's QCMDEXC procedure.

When I attended OCEAN user group's TechCon19 I learn a different way to execute CL commands when using RUNSQLSTM (which is why I encourage all of you to attend user group meetings as you are bound to learn something new). Apparently this has been around since IBM i 7.1, but I had not been aware of it:

CL: CL-command-goes-here

Yes, just CL: (CL followed by a colon), and what follows needs to be valid CL command. Searching IBM's KnowledgeCenter was of no help, as when I search for "CL:" the colon is ignored and all the results containing "CL" are returned. I am sure you can imagine how many results that is.

In my "playing" with the CL: this is what I have found:

• Must be followed a valid CL command
• No variables are allowed in the following CL command
• There is no MONMSG to stop the previous CL command from failing

On to my first very simple example:

01 CL: CRTDTAARA DTAARA(QTEMP/TEST) TYPE(*CHAR) LEN(10) TEXT(TEST) ; 02 BEGIN 03 DECLARE VAR1 CHAR(10) ; 04 DECLARE VAR2 VARCHAR(100) ; 05 SET VAR1 = 'TEST' ; 06 SET VAR2 = 'CHGDTAARA DTAARA(QTEMP/TEST *ALL) VALUE(' || VAR1 || ')' ; 07 CALL QSYS2.QCMDEXC(VAR2) ; 08 END ;

Line 1: I create a data area in the library QTEMP. If there is already a data area with the same name already in QTEMP then the command will fail, and no further statements will be executed.

Line 2: As I want to use variables, see lines 3 and 4, I need to place then within a, I am not sure what to call this, an "un-named" SQL procedure. Notice that there is no semicolon ( ; ) following the BEGIN.

Lines 3 and 4: Declaring the two variables I will be using.

Line 5: Setting the value of VAR1 to be "TEST".

Line 6: Here I am making a CL command string to change the value of the data area created on line 1. The double pipe symbols ( || ) are used to concatenate the various parts of the string together.

Line 7: Here I am using SQL's QCMDEXC procedure as I cannot execute a CL command from in string using CL:.

Line 8: This marks the end of my "un-named" procedure, and this does end with a semicolon.

The result is as expected:

Data area . . . . . . . : TEST Library . . . . . . . : QTEMP Type . . . . . . . . . : *CHAR Length . . . . . . . . : 10 Text . . . . . . . . . : TEST Value Offset *...+....1....+....2... 0 'TEST '

My next example is a bit more complex:

01 CL: DLTDTAARA QTEMP/TEST ; 02 CL: CRTDTAARA DTAARA(QTEMP/TEST) TYPE(*CHAR) LEN(50) TEXT(TEST) ; 03 BEGIN 04 DECLARE LNAME CHAR(15) ; 05 DECLARE FNAME CHAR(15) ; 06 DECLARE VAR1 VARCHAR(100) ; 07 SELECT LASTNAME,FIRSTNAME INTO LNAME,FNAME FROM PRODLIB.EMPMAS WHERE EMPNO = 10023 ; 08 SET VAR1 = 'CHGDTAARA DTAARA(QTEMP/TEST *ALL) VALUE(''' || RTRIM(LNAME) || ', ' || FNAME || ''')' ; 09 CALL QSYS2.QCMDEXC(VAR1) ; 10 END ;

Line 1: This time I am going to try to delete the data area in QTEMP. If it is not there this will error, and no more of the statements will be executed.

Line 2: Creating a data area, TEST, in QTEMP.

Line 3: Beginning of my "un-named" procedure.

Lines 4 – 6: Three variables will be needed here.

Line 7: To make this example more interesting I am getting the first and last name of employee number 1023, and placing them into the variables LNAME and FNAME.

Line 8: My CL command string is a bit more complicated than in the previous example. I need to use the three apostrophes ( ''' ) as it will be mapped to a single apostrophe in the string. The RTRIM function removes the trailing spaces when the last name is concatenated in the string.

Line 9: The CL command string is executed by SQL's QCMDEXC procedure.

Line 10: End of this "un-named" procedure.

The data area contains:

Data area . . . . . . . : TEST Library . . . . . . . : QTEMP Type . . . . . . . . . : *CHAR Length . . . . . . . . : 50 Text . . . . . . . . . : TEST Value Offset *...+....1....+....2... 0 'SMITH, JOHN

When the RUNSQLSTM command finishes it generates a spool file, with the same name of the source member you used with RUNSQLSTM. Within the spool file any errors are listed:

MSG ID SEV RECORD TEXT CPF2105 40 1 Position 1 Object TEST in QTEMP type *DTAARA not found. Message Summary Total Info Warning Error Severe Terminal 1 0 0 0 0 1 40 level severity errors found in source

In the case of successful completion of the second example the last part of the spool files looks like:

MSG ID SEV RECORD TEXT CPC2191 0 1 Position 1 Object TEST in QTEMP type *DTAARA deleted. CPC0904 0 3 Position 1 Data area TEST created in library QTEMP. SQL7985 0 6 Position 1 CALL statement complete. Message Summary Total Info Warning Error Severe Terminal 3 3 0 0 0 0 00 level severity errors found in source

How do I prevent the error causing the RUNSQLSTM command to end when it cannot find the data area?

The simplest way is to change the Severity Level, ERRLVL, to 40 when using the command.

RUNSQLSTM SRCFILE(MYLIB/DEVSRC) SRCMBR(EXAMPLE2) ERRLVL(40)

There are lots of CL commands I could use that will not cause me these kinds of problems, for example:

01 INSERT INTO TESTFILE VALUES('ANIMAL','AARDVARK') ; 02 CL: DLYJOB 120 ; 03 UPDATE TESTFILE SET VALUE = 'BUFFALO' WHERE THING = 'ANIMAL' ; 04 CL: DLYJOB 120 ; 05 UPDATE TESTFILE SET VALUE = 'COUGAR' WHERE THING = 'ANIMAL'

Having discovered CL: I will be using it in the future, rather than the SQL procedure QCMEXC, in source members I will be using RUNSQLSTM on.

 

You can learn more about the RUNSQLCMD command from the IBM website here.

 

This article was written for IBM i 7.3, and should work for some earlier releases too.