Wednesday, August 21, 2019

Executing CL commands using RUNSQLSTM

adding cl commands to member used by 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.

4 comments:

  1. Hi Simon, can also use CL: in iSeries Navigator RUNSQL. Think this has been around since 6.1

    ReplyDelete
  2. How will I read the spool file created by the runsqlstm?

    ReplyDelete
    Replies
    1. I am not sure I understand you question... like any other spool file.

      Do you mean how can you read it and check for errors?
      If so there is the SPOOLED_FILE_DATA table function.

      Delete

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.