Charlie Despres posed a good question in a comment to my original post Speeding up CPYF, "Does this still work if I add conditions to the CPYF?" I assumed he would see the same benefit, but there is only one way to really know, test it!
I used the same file, BIGFILE, and only copy records into the output file, QTEMP/@BIGFILE, where the value of the field FLD1 is in the range of 1,000 to 2,000. There are 12 records in the 1 million records in BIGFILE that fit the criteria.
The only difference from the original programs shown in my last post was the Copy File command, CPYF, statements. This statement, with the default of FROMRCD(*START), was placed in one program:
CPYF FROMFILE(MYLIB/BIGFILE) +
TOFILE(QTEMP/@BIGFILE) MBROPT(*ADD) +
INCREL((*IF FLD1 *GE 1000) (*AND FLD1 *LE 2000))
|
This statement was placed, with the FROMRCD(1), was placed in a second program:
CPYF FROMFILE(MYLIB/BIGFILE) +
TOFILE(QTEMP/@BIGFILE) MBROPT(*ADD) +
FROMRCD(1) +
INCREL((*IF FLD1 *GE 1000) (*AND FLD1 *LE 2000))
|
The third program contained the same CPYF as the second but had all of the overrides for changing the control block size. If you want to learn more about changing the control block size see Using control blocking to improve database performance.
OVRDBF FILE(BIGFILE) TOFILE(MYLIB/BIGFILE) +
OVRSCOPE(*JOB) SEQONLY(*YES *BUF256KB)
ALCOBJ OBJ((MYLIB/BIGFILE *FILE *EXCL))
CPYF FROMFILE(MYLIB/BIGFILE) +
TOFILE(QTEMP/@BIGFILE) MBROPT(*ADD) +
FROMRCD(1) +
INCREL((*IF FLD1 *GE 1000) (*AND FLD1 *LE 2000))
|
As before I submitted the three programs to a single threaded job queue in the order of program 1, 2, 3, 1, 2, 3, etc, ten times. The time taken for each CPYF was calculated and the average was determined:
| Type of copy | Average seconds |
| FROMRCD(*START) | 20.8 |
| FROMRCD(1) | 6.4 |
| FROMRCD(1) with control blocking | 6.0 |
The results show that the CPYF with the FROMRCD(1) ran in only 30% of the time of the CPYF with the FROMRCD(*START), and the use of control blocking reduced the time to 28% of the original.
By all means if I used the same commands for a different range of numbers the times would be different, but this clearly shows that it is still faster to use the FROMRCD(1) than leaving the CPYF with its default, FROMRCD(*START). And by using control blocking it can be even faster.
This article was written for IBM i 7.2, and should work for earlier releases too.
I will change how I use CPYF command to create test data, this is big time saver. Good job Simon.
ReplyDeleteDoes this apply on other CPY command like CPYFRMIMPF too?
ReplyDeleteThanks! It save a lot of time!
ReplyDelete