Mass insert into IFS file using IFS_WRITE

There are times when someone asks me a question I think is interesting enough to become a post here. This was the question that was the germ for this post:

How can I read a physical file and for each records, use the IFS_WRITE Function to write the [ IFS ] file?

The IFS_WRITE are really three SQL procedures that writes data to a file in the IFS:

1. IFS_WRITE:  Write plain text to IFS file
2. IFS_WRITE_UTF8:  Write UTF8 text
3. IFS_WRITE_BINARY:  Write binary text

In the following examples I am going to use IFS_WRITE_UTF8 as I want the contents of the IFS file to be UTF8 compatible.

As all of the variations of IFS_WRITE are SQL Procedures I need to way to perform a loop to fetch data from the IBM i based file, TESTFILE, and call the procedure. I can perform loops but they need to be within SQL a BEGIN group, which is used in procedures.

I created two solutions:

1. If this is a "one-off" deal, it will only be performed once or maybe just a few times
2. If this is to be performed many times

In the first scenario, one-off, I am going to place the SQL statement in a source member, and then execute it using the Run SQL Statement command, RUNSQLSTM. The source member, source file, and library I will be using are:

• Source member:  MYSRCMBR
• Source file:  DEVSRC
• Library:  MYLIB

The contents of the source member is:

01 BEGIN 02 CALL QSYS2.IFS_WRITE_UTF8( 03 PATH_NAME => '/home/MyFolder/testfile.txt', 04 LINE => '', 05 OVERWRITE => 'REPLACE', 06 END_OF_LINE => 'CRLF') ; 07 FOR SELECT FLD001 FROM TESTFILE DO 08 CALL QSYS2.IFS_WRITE_UTF8( 09 PATH_NAME => '/home/MyFolder/testfile.txt', 10 LINE => FLD001) ; 11 END FOR ; 12 END ;

Lines 1 and 12: The beginning and end of the BEGIN group.

Lines 2 – 6: When I call the IFS_WRITE_UTF8 procedure I am creating or replacing the file in the IFS. This does write one blank record to the file.

Lines 7 – 11: This is the loop I was talking about.

Line 7: This is a SQL For group. The statement must start with FOR followed by the Select statement, in this case, to call the procedure to insert a row into the IFS file.

Line 10: The value contained in the field FLD001, in the file TESTFILE, is what is inserted.

Line 11: I need an END FOR and the end of the For group.

I could execute these SQL statements using the RUNSQLSTM on a command line. I want the RUNSQLSTM in a CL program, that way I can ensure that the command's parameters are the same every time I execute the command. My CL program looks like:

01 PGM 02 RUNSQLSTM SRCFILE(DEVSRC) SRCMBR(MYSRCMBR) + COMMIT(*NONE) ERRLVL(20) MARGINS(*SRCFILE) 03 ENDPGM

Line 2: The MARGINS(*SRCFILE) was a good enhancement in IBM i 7.5 and 7.4 TR6. It means the SQL can extend beyond the 80th position in the source file, to fill the entire source member's line.

If I am going to do this more than once then using the RUNSQLSTM is still usable, I would prefer something that is faster. This is where I would create my own SQL procedure. It would look like:

01 CREATE OR REPLACE PROCEDURE MYLIB.MYPROC 02 LANGUAGE SQL READS SQL DATA 03 BEGIN 04 CALL QSYS2.IFS_WRITE_UTF8( 05 PATH_NAME => '/home/MyFolder/testfile.txt', 06 LINE => '', 07 OVERWRITE => 'REPLACE', 08 END_OF_LINE => 'CRLF') ; 09 FOR SELECT FLD001 FROM TESTFILE DO 10 CALL QSYS2.IFS_WRITE_UTF8( 11 PATH_NAME => '/home/MyFolder/testfile.txt', 12 LINE => FLD001) ; 13 END FOR ; 14 END ;

I always use the CREATE OR REPLACE here as if I need to make changes I don't have to delete the existing object before I attempt to create a new version.

Line 2: I need to "tell" that the procedure is written in SQL and performs reads. There are no parameters that need to be passed or are returned from this procedure.

Lines 3 – 14: These are the same as was in the source member used by the RUNSQLSTM command.

After I have created the procedure I need to call it. I could call it from "Run SQL Scripts", but it is more practical to call it from a program.

It is very simple to call my new procedure from a CL program using the RUNSQL command.

01 PGM 02 RUNSQL SQL('CALL MYPROC') COMMIT(*NONE) 03 ENDPGM

I have not bothered to show any of the results of the IFS file testfile.txt, as its content is not important.

 

This article was written for IBM i 7.5 TR1 and 7.4 TR7.