Wednesday, January 27, 2021

For groups in CL

dofor allows for logic in cl program

This comes under the category of: I thought I had already written about this. The DOFOR command in CL allows me to construct a For group in CL, like I can in RPG when I use its FOR operation code. The same information has to be given to both:

RPG
===
for Count = 1 to 10 by 1 ;


CLLE
====
DOFOR VAR(&COUNT) FROM(1) TO(10) BY(1)
  1. What I call the count variable
  2. Starting value
  3. End value
  4. Increment value, this is optional as it is in RPG. If it is not given 1 is assumed

The similarities between the two end when I start replacing the values in the command. With the RPG operation code the variables just have to numeric. In the command the variables need to either be integer or unsigned integer variables.

01  PGM

02  DCL VAR(&COUNT) TYPE(*INT)
03  DCL VAR(&TO_VALUE) TYPE(*UINT) VALUE(10)

04  DOFOR VAR(&COUNT) FROM(1) TO(&TO_VALUE)

05  ENDDO

06  ENDPGM

Lines 2 and 3: I am sure it did not take you long to determine that &COUNT is an integer and &TO_VALUE is an unsigned integer. I deliberately not given the size of the two variables to show that the CL compiler will give them default sizes.

Declared Variables
Name      Defined     Type    Length
&COUNT        300     *INT         4
&TO_VALUE     400     *UINT        4

Of course I can give those variables lengths when I define them:

01  PGM

02  DCL VAR(&COUNT) TYPE(*INT) LEN(4)
03  DCL VAR(&TO_VALUE) TYPE(*UINT) LEN(4) VALUE(10)

04  DOFOR VAR(&COUNT) FROM(1) TO(&TO_VALUE)

What happens if you define the To parameter as a decimal (*DEC) variable?

01  PGM

02  DCL VAR(&COUNT) TYPE(*INT) LEN(4)
03  DCL VAR(&TO_VALUE) TYPE(*DEC) LEN(4 0) VALUE(10)

04  DOFOR VAR(&COUNT) FROM(1) TO(&TO_VALUE)

When I compile the program I get an error:

400- DOFOR VAR(&COUNT) FROM(1) TO(&TO_VALUE)
* CPD089C 30  Variable &TO_VALUE for parameter TO must be
              declared TYPE(*INT) or TYPE(*UINT).

Fortunately I can overcome this using the Integer built in function, %INT:

01  PGM

02  DCL VAR(&COUNT) TYPE(*INT) LEN(4)
03  DCL VAR(&TO_VALUE) TYPE(*DEC) LEN(4 0) VALUE(10)

04  DOFOR VAR(&COUNT) FROM(1) TO(%INT(&TO_VALUE))

Line 4: In the To parameter I am using the %INT to convert the value in decimal &TO_VALUE to an integer value.

Let me give an example of the DOFOR in action. This example is based upon a question I was asked by a colleague. He needed to break up a large file, I am calling TESTFILE, into smaller files as the program he had to call can only handle files of up to 9,999 records. Rather than using the Copy File command, CPYF, he asked if it was possible to do the same in SQL. The answer is "Yes".

01  PGM

02  DCL VAR(&DIVISOR) TYPE(*DEC) LEN(5 0) VALUE(9990)
03  DCL VAR(&COUNT) TYPE(*INT)
04  DCL VAR(&OFFSET) TYPE(*DEC) LEN(10 0) VALUE(0)
05  DCL VAR(&RECORDS) TYPE(*DEC) LEN(10 0) VALUE(0)
06  DCL VAR(&TIMES) TYPE(*INT)

07  RTVMBRD FILE(TESTFILE) NBRCURRCD(&RECORDS)
08  CHGVAR VAR(&TIMES) VALUE(&RECORDS / &DIVISOR)
09  IF COND((&TIMES * &DIVISOR) < &RECORDS) +
         THEN(CHGVAR VAR(&TIMES) VALUE(&TIMES + 1))

10  DLTF FILE(QTEMP/TABLE*)
11  MONMSG MSGID(CPF0000)

12  DOFOR VAR(&COUNT) FROM(1) TO(&TIMES)
13    RUNSQL SQL('CREATE TABLE QTEMP.TABLE' || %CHAR(&COUNT) +
14                |< ' AS (SELECT * FROM TESTFILE +
15                OFFSET ' || %CHAR(&OFFSET) |< ' ROWS +
16                FETCH FIRST ' || %CHAR(&DIVISOR) |< +
17                ' ROWS ONLY) WITH DATA') +
18             COMMIT(*NC)

19    CHGVAR VAR(&OFFSET) VALUE(&OFFSET + &DIVISOR)
20  ENDDO

21  ENDPGM

Line 2: This variable contains the maximum number of records to be copied into the "child" files.

Lines 3 and 6: My integer variables I will be using in the DOFOR command.

Line 7: Retrieve the number of records in TESTFILE.

Line 8: Divide the number of records retrieved from TESTFILE by the maximum number of records wanted in each "child" file.
    100,000 / 9,990 = 10.01
As &TIMES is an integer the result will be just 10.

Line 9: This is where I determine if I need to perform the For group an 11th time. If the value in &TIMES is multiplied by the maximum number of records wanted, from &DIVISOR, is less than the number of records in the file I need to perform the For group one more time.
    10 * 9,990 = 99,900
As this is less than the number of records in the file I increment the value in &TIMES.

Line 10: Delete all the "child" files, if they already exist.

Lines 12 – 20: The For group will be performed 11 times as &TIMES contains 11.

Lines 13 – 18: This may look confusing but it is not. All that is happening here is the building of the SQL statement to be executed by the RUNSQL command.

The first time the For group is performed the SQL statement looks like:

CREATE TABLE QTEMP.TABLE1
AS (SELECT * FROM TESTFILE
OFFSET 0 ROWS
FETCH FIRST 9990 ROWS ONLY)
WITH DATA

By using the %CHAR built in function on line 13 I convert the number 1 to the character value "1", which can be made part of the file name: TABLE1

This statement creates a new table, TABLE1, based on the SQL statement that follows after the AS.

The offset value looks strange as how can zero be valid? When the data is offset it offset that number of rows, therefore, if I offset zero rows I am at the start of the file.

The FETCH FIRST instructs the statement how many records to copy starting at the offset record.

The WITH DATA is needed as I want a table that contains data.

When the For group loops and this is performed a second time the SQL statement will be:

CREATE TABLE QTEMP.TABLE2
AS (SELECT * FROM TESTFILE
OFFSET 9990 ROWS
FETCH FIRST 9990 ROWS ONLY)
WITH DATA

The table name has been incremented. The offset of 9,990 will mean that the data will be copied starting with the 9,991st record.

In the 11th loop of the For group there are just a hundred records that need to be copied. Even though this is less than 9,900 rows I it does not error. It just copies those last records into TABLE11.

I hope you will find this as useful in CL as it is using the FOR operation code in RPG.

 

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

 

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

5 comments:

  1. Interesting, I didn't know about DOFOR. I rarely write CL anymore, but occasionally I need to modify it. This is good to know.

    ReplyDelete
  2. Very helpful.

    ReplyDelete
  3. Interesting indeed . . . thanks for sharing, Simon!

    ReplyDelete
  4. Enjoyed reading SQL part and your explanation. Great work Simon. Much Appreciated!

    ReplyDelete

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.