Chopping up a file that is too big

I know the title sound intriguing, and I thought the subject was worthy of a post. A friend approached me wanting to know how could he "chop up" files that contain hundreds of millions of records into smaller files, that he would transfer to another platform. He knew he could do it manually, but he wanted a program.

Something like this is very simple in CL. In no time I had a program for him that would take a file, copy one million records at a time to a work file, and copy the work file's contents to a CSV in the IFS.

Unfortunately I do not have file containing hundred of millions of records, so I am going to use my Person file. This file has only 41 records, and I am going to show how I can "chop" it up into files that contain a maximum of ten records. The principal is the same as copying the files with hundred of millions of records.

The source for the program is not big, just 36 lines. I am going to start with the first 17 lines:

01 PGM PARM(&LIBRARY &FILE) 02 DCL VAR(&FILE) TYPE(*CHAR) LEN(10) 03 DCL VAR(&LIBRARY) TYPE(*CHAR) LEN(10) 04 DCL VAR(&COUNT) TYPE(*UINT) LEN(2) 05 DCL VAR(&IFSNAME) TYPE(*CHAR) LEN(50) 06 DCL VAR(&RECORDS) TYPE(*DEC) LEN(10 0) 07 DCL VAR(&START) TYPE(*UINT) LEN(4) 08 DCL VAR(&END) TYPE(*UINT) LEN(4) 09 RTVMBRD FILE(&LIBRARY/&FILE) NBRCURRCD(&RECORDS) 10 DLTF FILE(QTEMP/OUTFILE) 11 MONMSG MSGID(CPF2105) 12 CPYF FROMFILE(&LIBRARY/&FILE) + 13 TOFILE(QTEMP/OUTFILE) + 14 CRTFILE(*YES) + 15 FROMRCD(1) TORCD(1) 16 CHGPF FILE(QTEMP/OUTFILE) SIZE(*NOMAX) 17 CHGVAR VAR(&START) VALUE(1)

Line 1: The library and file name must be passed to this program.

Lines 2 – 8: These are the variables that are used in the program.

Line 4: The variable &COUNT is defined as an unsigned integer, *UINT, with a length of two. This will cover values from 0 to 65,535.

Lines 7 and 8: The variables &START and &END are defined as *UINT, with the length of 4. This covers the values from 0 to 4,294,957,295.

Line 9: I am retrieving the number of records in the file into the variable &RECORDS.

Line 10: If a file called OUTFILE exists in the library QTEMP delete it.

Line 12 – 15: I am creating the output file to be a copy of the file. It does not need to contain data, but I was unable to find a way to create an empty file with the Copy File command, CPYF. Therefore, I copied just one record, line 15, to the file.

Line 16: As the output file could contain many millions of records, I am changing its initial size to be *NOMAX.

Line 17: &START is the variable that will contain the number of the record to start copying. I am initializing it with 1, to start with the first record.

18 DOWHILE COND('1') 19 IF COND(&START > &RECORDS) THEN(LEAVE) 20 CHGVAR VAR(&COUNT) VALUE(&COUNT + 1) 21 /*CHGVAR VAR(&END) VALUE(&START + 999999)*/ 22 CHGVAR VAR(&END) VALUE(&START + 9) 23 CPYF FROMFILE(&LIBRARY/&FILE) + 24 TOFILE(QTEMP/OUTFILE) + 25 MBROPT(*REPLACE) + 26 FROMRCD(&START) + 27 TORCD(&END) 28 COMPRESS(*NO) 29 CHGVAR VAR(&IFSNAME) + 30 VALUE('/home/MyDirectory/' || &FILE |< '_' || %CHAR(&COUNT) |< '.txt') 31 CPYTOIMPF FROMFILE(QTEMP/OUTFILE) + 32 TOSTMF(&IFSNAME) + 33 STMFCCSID(*PCASCII) + 34 RCDDLM(*CRLF) 35 CHGVAR VAR(&START) VALUE(&END + 1) 36 ENDDO 37 ENDPGM

The next part of the program is contained in a Do loop.

Line 18: The start of a Do while loop. The condition of "1" makes this a "never ending loop", I have to code when to exit it.

Line 19: If the value in &START exceeds &RECORDS, the number of records in the file, then leave the loop.

Line 20: &COUNT is used as a suffix for the IFS file name.

Line 21: If I was copying a million records, I would add 999,999 to the value in &START to populate &END, the last record to copy. This line is commented out in this version of the program.

Line 22: As I want to "chop" the data in the Person file to sets of ten I add nine to &START to get the value for &END. This will give me the following values when the loop is performed for the first time: &START = 1, &END = 10

Lines 23 – 28: Copy the records from the file to the output file.

Line 25: The member option is *REPLACE, therefore, the file is cleared before the data is copied.

Line 28: The from record and to record are the RRN of the records we want to copy. The Compress parameter needs to be "NO" so that the deleted records are not copied, and the records between the from and to RRN are copied. This can result in a file containing less than a million records.

Lines 26 and 27: Start copying at the record number in &START, and end after copying the record number in &END.

Lines 29 and 30: Create the path (file) name for the file that will be created in the IFS. I am creating the file in my IFS folder, "/home/MyDirectory". The IFS file name is the name of the file passed to this program, followed by an underscore ( _ ), and the value in the &COUNT variable. It is not possible to concatenate a numeric value, when I use the %CHAR built in function the value is translated to character, without the leading zeroes. Finally, I add the desired file extension ".txt>".

What do the pipes symbols mean? These are CL shortcuts I use in place of CL concatenation values. The double pipe ( || ) is the same as *CAT, and pipe followed by the less than character ( |< ) I use instead of *TCAT.

Lines 31 – 34: Using the Copy To Import File command, CPYTOIMPF to copy the data from the output file to the IFS file name. The IFS file will use the PC ASCII character set, and have carriage return and line feed character at the end of every line.

Line 35: Calculate the next starting record number.

After compiling this program, I called it:

call testcl ('MYLIB' 'PERSON')

I think the best way to show that the program worked the way I expected is to look at the job log using the JOBLOG_INFO SQL table function:

01 SELECT MESSAGE_TEXT 02 FROM TABLE(QSYS2.JOBLOG_INFO('448761/SIMON/QPADEV0001'))

Line 1: I only care about the contents of the message text.

Line 2: As I am using ACS's Run SQL Scripts, RSS, I need to pass the job name of the job where I ran the program.

The results are:

MESSAGE_TEXT --------------------------------------------------------------------------------------------------------------------- call testcl ('MYLIB' 'PERSON') 1100 - RTVMBRD FILE(MYLIB/PERSON) NBRCURRCD(&RECORDS) 1300 - DLTF FILE(QTEMP/OUTFILE) Object OUTFILE in QTEMP type *FILE deleted. 1600 - CPYF FROMFILE(MYLIB/PERSON) TOFILE(QTEMP/OUTFILE) CRTFILE(*YES) FROMRCD(1) TORCD(1) Physical file OUTFILE created in library QTEMP. Member PERSON added to file OUTFILE in QTEMP. 1 records copied from member PERSON. 2100 - CHGPF FILE(QTEMP/OUTFILE) SIZE(*NOMAX) File OUTFILE in library QTEMP changed. 3300 - CPYF FROMFILE(MYLIB/PERSON) TOFILE(QTEMP/OUTFILE) MBROPT(*REPLACE) FROMRCD(1) TORCD(10) 10 records copied from member PERSON. 4200 - CPYTOIMPF FROMFILE(QTEMP/OUTFILE) TOSTMF('/home/MyDirectory/PERSON_1.txt') STMFCCSID(*PCASCII) RCDDLM(*CRLF) All records copied from file OUTFILE in QTEMP. 3300 - CPYF FROMFILE(MYLIB/PERSON) TOFILE(QTEMP/OUTFILE) MBROPT(*REPLACE) FROMRCD(11) TORCD(20) 10 records copied from member PERSON. 4200 - CPYTOIMPF FROMFILE(QTEMP/OUTFILE) TOSTMF('/home/MyDirectory/PERSON_2.txt') STMFCCSID(*PCASCII) RCDDLM(*CRLF) All records copied from file OUTFILE in QTEMP. 3300 - CPYF FROMFILE(MYLIB/PERSON) TOFILE(QTEMP/OUTFILE) MBROPT(*REPLACE) FROMRCD(21) TORCD(30) 10 records copied from member PERSON. 4200 - CPYTOIMPF FROMFILE(QTEMP/OUTFILE) TOSTMF('/home/MyDirectory/PERSON_3.txt') STMFCCSID(*PCASCII) RCDDLM(*CRLF) All records copied from file OUTFILE in QTEMP. 3300 - CPYF FROMFILE(MYLIB/PERSON) TOFILE(QTEMP/OUTFILE) MBROPT(*REPLACE) FROMRCD(31) TORCD(40) 10 records copied from member PERSON. 4200 - CPYTOIMPF FROMFILE(QTEMP/OUTFILE) TOSTMF('/home/MyDirectory/PERSON_4.txt') STMFCCSID(*PCASCII) RCDDLM(*CRLF) All records copied from file OUTFILE in QTEMP. 3300 - CPYF FROMFILE(MYLIB/PERSON) TOFILE(QTEMP/OUTFILE) MBROPT(*REPLACE) FROMRCD(41) TORCD(50) 1 records copied from member PERSON. 4200 - CPYTOIMPF FROMFILE(QTEMP/OUTFILE) TOSTMF('/home/MyDirectory/PERSON_5.txt') STMFCCSID(*PCASCII) RCDDLM(*CRLF) All records copied from file OUTFILE in QTEMP. - RETURN /* RETURN due to end of CL program */

If I look at the 3300 records I see that the data from the Person file was copied in groups of ten. As there are 41 records in the file, the last copy was 41 – 50.

The 4200 lines show the file name of the "chopped" files in the IFS.

I can use SQL to display the number of records in each file, by using the IFS_READ table function:

01 SELECT '#1' AS "File",COUNT(*) AS "Count" 02 FROM TABLE(IFS_READ('/home/MyDirectory/PERSON_1.txt')) 03 UNION 04 SELECT '#2',COUNT(*) FROM TABLE(IFS_READ('/home/MyDirectory/PERSON_2.txt')) 05 UNION 06 SELECT '#3',COUNT(*) FROM TABLE(IFS_READ('/home/MyDirectory/PERSON_3.txt')) 07 UNION 08 SELECT '#4',COUNT(*) FROM TABLE(IFS_READ('/home/MyDirectory/PERSON_4.txt')) 09 UNION 10 SELECT '#5',COUNT(*) FROM TABLE(IFS_READ('/home/MyDirectory/PERSON_5.txt')) 11 ORDER BY 1

I wanted to have the count from each file appear in a list, with the file number first. By using the UNION clause I could combine the results from these five statements into one result:

File Count ---- ----- #1 10 #2 10 #3 10 #4 10 #5 1

The results show 41 records, which is the same as the number of records in the Person file.

My friend was very happy with the program. He texted me the following day to inform me that he had taken those files, containing many millions of records, "chopped" them into smaller files, and FTP-ed them to the other server without issue.

 

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