Pages

Wednesday, June 7, 2023

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 fileDEVSRC
  • 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.

5 comments:

  1. While MARGINS(*SRCFILE) is new for 7.4 and later, MARGINS exists on 7.3 by specifying the end position up to 32754. We use MARGINS(120) here.

    ReplyDelete
  2. To avoid creating a blank record, you could use END_OF_LINE => 'NONE' on the first statement when you create the file and END_OF_LINE => 'CRLF' in the loop.

    ReplyDelete
  3. Hi Simon, other possible way is to create ani UDF that wraps IFS_WRITExxx procedure and then use it directly in SELECT statement. Values returned from UDF can be stored in a temp table, which can be deleted after processing.

    ReplyDelete
  4. Dear Simon
    Thanks your for an example.
    Unfortunately when I specified more than one field for SELECT ... and LINE=> ... parameters inside of FOR ... DO ... END FOR loop the script failed with MSGID SQ20483 . IBM documentation also does not provide any examples or explanation about. May be you have any hint(s), ideas ?

    ReplyDelete
    Replies
    1. I tried the same thing, but after i GOT the SAME error I used CONCAT to make one string out of all columns. In my case comma separated, because I wanted a .csv file. The IBM documentation says that LINE => should be followed by a string.
      E.g. SELECT COL1 CONCAT ',' CONCAT COL2 CONCAT ',' CONCAT......

      Delete

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.