The CL Corner: True and False Conditions

Typography

Smaller Small Medium Big Bigger
Default Helvetica Segoe Georgia Times
Reading Mode

Let's take a look at using logical variables.

Recently, on TechTarget's IT Knowledge Exchange site, SundarNVS entered the following request:

"Hi! I have an iSeries [sic] database table that has a column name JOBQ. I want to update all records with JOBQ value QBATCH into 2 different jobq names for alternate records. I'm a CL programmer and looking for an example of updating a column via SQL that I can call from my CL in a loop."

This month, we'll look at the CL program I offered as an example of how to accomplish the desired database update. As the sample program can run on releases 6.1, 7.1, or 7.2, it does not demonstrate anything really new to CL, but you may find a few current CL capabilities that you were not aware of (and, with 7.2, I will later point out a way to eliminate two of the statements in the program).

Below is the source for the CHGJOBS CL program.

Pgm

DclF File(BVining/Jobs)

Dcl Var(&EveryOther) Type(*Lgl)

Dcl Var(&KeyFldChr) Type(*Char) Len(9)

Dcl Var(&SQLStmt) Type(*Char) Len(512)

Loop: RcvF

MonMsg MsgID(CPF0864) Exec(Goto CmdLbl(Exit))

If Cond(&JobQ *EQ 'QBATCH') Then(Do)

If Cond(*Not &EveryOther) Then( +

ChgVar Var(&JobQ) Value('THISONE'))

Else Cmd( +

ChgVar Var(&JobQ) Value('THATONE'))

ChgVar Var(&KeyFldChr) Value(&KeyFld)

ChgVar Var(&SQLStmt) Value(+

'Update BVining/Jobs Set JobQ = ''' *TCat +

&JobQ *TCat +

''' where KeyFld =' *BCat +

&KeyFldChr)

RunSQL SQL(&SQLStmt) Commit(*None)

ChgVar Var(&EveryOther) Value(*Not &EveryOther)

EndDo

Goto CmdLbl(Loop)

Exit: EndPgm

The assumptions that I made in CHGJOBS are:

The qualified name of the database table is BVINING/JOBS.
In addition to BVINING/JOBS containing the column (or field) JOBQ, there is also a field named KeyFld, which uniquely identifies the record to be changed. There could of course be many additional fields (for instance, JOBQ library, Job Description, Program to call, etc.) and rather than one field (KeyFld) to uniquely identify the record some combination of fields, but these types of attributes would not significantly impact the approach demonstrated by the program.
KeyFld is defined as a packed numeric field with a precision of 9,0.
The two different job queue names to be used, when replacing 'QBATCH', are 'THISONE' and 'THATONE'.

The program first declares the file BVINING/JOBS and the three variables &EveryOther, &KeyFldChr, and &SQLStmt.

For testing purposes, the database JOBS is defined using the following DDS.

A UNIQUE

A R RECORD

A KEYFLD 9P 0

A JOBQ 10A

A K KEYFLD

To create JOBS in library BVINING, you can use this command:

CRTPF FILE(BVINING/JOBS) SRCFILE(QDDSSRC)

Variable &EveryOther is declared as a logical variable. A logical variable can be set to one of two values: '0' or '1', where the value '0' is typically associated with FALSE, NO, and/or OFF and the value '1' with TRUE, YES, and/or ON. CHGJOBS will use this variable when a &JobQ value of 'QBATCH' is encountered in the JOBS database. When &EveryOther is '0' (which will be the case for the first, third, fifth, etc. instances), then &JobQ will be changed to the value 'THISONE'. When &EveryOther is '1' (the second, fourth, sixth, etc. occurrences), then &JobQ will be changed to the value 'THATONE'.

Variable &KeyFldChr is declared as a character variable with a length of 9 bytes. CHGJOBS will use this variable to hold a character representation of the numeric &KeyFld value. This character variable will be used in constructing the WHERE clause of a SQL UPDATE statement.

Variable &SQLStmt is declared as a character variable with a length of 512 bytes. CHGJOBS will, as you might guess from the name, use this variable to hold the SQL statement used to update records/rows within the JOBS database.

With the various declares out of the way, CHGJOBS uses Receive File (RCVF) to read the first record of JOBS. CHGJOBS monitors for end of file (CPF0864) and, when encountered, goes to command label EXIT, which ends the program.

For each record read, CHGJOBS checks to see if field &JobQ of the record has the value 'QBATCH'. If the logical condition (&JobQ *EQ 'QBATCH') is TRUE, then a DO group is entered. If the condition is FALSE, then the DO group is bypassed and the next command run is the GOTO CMDLBL(LOOP), which reruns the RCVF command, reading the next record of the JOBS database.

Within the DO group, a test is performed to determine if logical variable &EveryOther is FALSE/OFF/'0' or TRUE/ON/'1'. In the logical expression of the IF command's COND parameter, many of you have undoubtedly used the operators of *AND and *OR as in COND((&A *EQ 'ABC') *AND (&B *NE 'XYZ')). Not so many of you may have used the *NOT operator. The *NOT operator is used to negate logical variables. Using a COND parameter of (*Not &EveryOther), as done by CHGJOBS, is a test for variable &EveryOther being set to FALSE/OFF/'0'. If &EveryOther is FALSE/OFF, then the logical expression (*Not &EveryOther) is TRUE and the Change Variable (CHGVAR) is run to assign &JobQ a value of 'THISONE'. If &EveryOther is currently TRUE/ON, then the logical expression (*Not &EveryOther) is FALSE and the ELSE command is run, changing the value of &JobQ to 'THATONE'.

As an aside from this discussion of CHGJOBS, this ability to use logical expressions and/or logical variables in COND parameters can enable some brevity in COND parameters. Many times I've seen an IF command coded as in IF COND(&IN10 *EQ '1') THEN(…), where variable &IN10 is declared as a logical variable. This testing for &IN10 being TRUE/ON/'1' is redundant as the expression (&IN10) will only return TRUE if &IN10 is indeed set to '1', so IF COND(&IN10) gets the same job done. In the same manner, an IF COND(&IN10 *EQ '0') can be replaced with IF COND(*Not &IN10).

Returning to our review of CHGJOBS, after &JobQ is set to the appropriate value of 'THISONE' or 'THATONE', CHGJOBS then:

Converts the numeric value of &KeyFld is to a character format using CHGVAR and variable &KeyFldChr
Constructs an SQL UPDATE statement using CHGVAR and variable &SQLStmt
Runs the SQL statement found in variable &SQLStmt using the RUNSQL command. Note that if you're not familiar with the RUNSQL command, you may want to review a few earlier CL Corner columns: Introducing the New Run SQL command and Using the SQL Select Statement with RUNSQL.
Uses CHGVAR to reverse the current value of variable &EveryOther
Exits the DO group and runs the GOTO CMDLBL(LOOP) command, which reruns the RCVF command, reading the next record of the JOBS database

I have to admit it was the quantity of email I received about step 4 (reversing the value of &EveryOther) that prompted me to write this article. But before I go into how step 4 works, a pop quiz. There are not all that many statements in the CHGJOBS program to begin with, but if you're familiar with the new CL functions available with 7.2 you can, with a minor change to one statement, remove two other statements. Can you identify the changes I have in mind? If you need a refresher on the CL 7.2 enhancements, you may want to review the articles More Tools for the CL Developer and Still More Tools for the CL Developer. The answer can be found at the end of this column.

Returning to the CHGVAR command used in step 4, virtually every CL developer is familiar with the CHGVAR command, but some of you may not be aware of just how flexible the command is. CHGVAR changes the value of a CL variable based on the expression found in the VALUE parameter, and the type of expression that can be used varies by the type of variable identified by the VAR parameter as seen in Table 1 of a Knowledge Center article. In the case of the VAR parameter identifying a logical variable, then the VALUE parameter can be any logical expression.

Many CL developers in the past, in order to reverse the setting of a logical variable/indicator, might have coded something like this:

If Cond(&EveryOther *EQ '1') Then( +

ChgVar Var(&EveryOther) Value('0'))

Else Cmd(ChgVar Var(&EveryOther) Value('1'))

Or, based on our earlier discussion in this article, you might have coded this:

If Cond(&EveryOther) Then( +

ChgVar Var(&EveryOther) Value('0'))

Else Cmd(ChgVar Var(&EveryOther) Value('1'))

But due to the flexibility of the CHGVAR command, you could have simply coded this:

ChgVar Var(&EveryOther) Value(*Not &EveryOther)

As discussed earlier in the context of If Cond(*Not &EveryOther) the If is TRUE if the current value of &EveryOther is OFF (and FALSE if currently ON). All we're doing here is using CHGVAR to set the returned TRUE/FALSE value to &EveryOther, rather than having the returned value consumed by the If command.

Using the same technique, we could also replace the following code

ChgVar Var(&IN10) Value('0')

If Cond(&A *EQ 'ABC') Then( +

ChgVar Var(&IN10) Value('1'))

If Cond(&A *EQ 'XYZ') Then( +

ChgVar Var(&IN10) Value('1'))

with the following CHGVAR

ChgVar Var(&IN10) Value( +

((&A *EQ 'ABC') *or (&A *EQ 'XYZ')))

Both sequences will result with &IN10 being ON if variable &A is currently set to either 'ABC' or 'XYZ', otherwise OFF.

Having reviewed CHGJOBS, and earlier created table BVINING/JOBS, we can now compile the program using a command such as this:

CRTBNDCL PGM(CHGJOBS) SRCFILE(QCLSRC)

To test CHGJOBS, we need to load some test data. The following CL program, LOADJOBS, can be used to write nine records to BVINING/JOBS. If our CHGJOBS program works correctly, it will change only those records with a JobQ value of 'QBATCH', which are those identified by KeyFld values of 21, 22, 121, 222, and 123456789.

Pgm