Copying JSON in CLOB to an IFS file

The germ for this post comes from a comment made on the Better examples for creating and consuming JSON post. The question was:

How can I create an IFS File of the JSON Object from the MYLIB.OUTPUT object?

In that post I used the JSON_OBJECT scalar function to create a JSON array in a DDL table with a CLOB column:

01 CREATE TABLE MYLIB.OUTPUT (OUTPUT CLOB)

I filled the CLOB with a SQL Insert statement with the JSCON_OBJECT scalar function, creating a JSON array from the data in the Person file:

01 INSERT INTO OUTPUT 02 SELECT JSON_OBJECT('ARRAY_1' 03 VALUE JSON_ARRAYAGG( 04 JSON_OBJECT('IDENTIFIER' : PERSON_ID, 05 'NAME' : LAST_NAME || ', ' || FIRST_NAME, 06 'DATE_OF_BIRTH' : DATE_OF_BIRTH))) 07 FROM PERSON

I can show the generated data with this statement:

01 SELECT * FROM OUTPUT

Which returns the JSON array's data.

OUTPUT ------------------------------------------------------------------------------ {"ARRAY_1":[{"IDENTIFIER":1,"NAME":"ALLEN, REG","DATE_OF_BIRTH":"1919-05-03"}, {"IDENTIFIER":2,"NAME":"CROMPTON, JACK","DATE_OF_BIRTH":"1921-12-18"}, {"IDENTIFIER":3,"NAME":"BYRNE, ROGER","DATE_OF_BIRTH":"1929-02-08"}, {"IDENTIFIER":4,"NAME":"CAREY, JOHNNY","DATE_OF_BIRTH":"1919-02-23"}, {"IDENTIFIER":5,"NAME":"MCNULTY, THOMAS","DATE_OF_BIRTH":"1929-12-30"},

The result is actually one long string of data. I have separated the first five elements onto their own lines to make this easier to understand.

Now I have the data in the Table OBJECT I want to have the data in UTF8 character set, therefore, I am going to use IFS_WRITE_UTF8 SQL procedure to copy the data to a file in the IFS:

01 CALL QSYS2.IFS_WRITE_UTF8( 02 PATH_NAME => '/home/MyDirectory/json.txt', 03 OVERWRITE => 'REPLACE', 04 LINE => (SELECT * FROM OUTPUT))

Line 1: As I am using ACS’s Run SQL Scripts I can just call the procedure directly.

Line 2: The name and location of the file I want create to contain the data.

Line 3: If the file already exists overwrite it.

Line 4: I can use a SQL statement to select all the rows from the OUTPUT file.

IMHO the only way to test if the data was correctly copied to the IFS file is to read it, using the IFS_READ scalar function:

01 SELECT * 02 FROM TABLE(QSYS2.IFS_READ( 03 PATH_NAME => '/home/MyDirectory/json.txt', 04 END_OF_LINE => 'NONE'))

Line 4: I need to "say" that there is no end of line character as I did not use one when creating the file.

The results show that there is just one row of results that contains all of the JSON array.

LINE_NUMBER LINE ----------- ---------------------------------------------------- 1 {"ARRAY_1":[{"IDENTIFIER":1,"NAME":"ALLEN, REG","...

It is possible to create a JSON array and write it to a file directly, without using an interim file.

I use IFS_WRITE_UTF8 again:

01 CALL QSYS2.IFS_WRITE_UTF8( 02 PATH_NAME => '/home/MyDirectory/json2.txt', 03 OVERWRITE => 'REPLACE', 04 LINE => (SELECT JSON_OBJECT('ARRAY_1' 05 VALUE JSON_ARRAYAGG( 06 JSON_OBJECT('IDENTIFIER' : PERSON_ID, 07 'NAME' : LAST_NAME || ', ' || FIRST_NAME, 08 'DATE_OF_BIRTH' : DATE_OF_BIRTH))) 09 FROM PERSON))

Lines 4 – 9: These lines are the same as the statement when I wrote the data to the OUTPUT file.

The data is copied straight from the PERSON file into a file in the IFS.

I can use the following statement to check that the data was correctly copied:

01 SELECT * 02 FROM TABLE(QSYS2.IFS_READ( 03 PATH_NAME => '/home/MyDirectory/json2.txt', 04 END_OF_LINE => 'NONE'))

By all means I am only showing part of the JSON array, I did check it all that it was successfully copied.

LINE_NUMBER LINE ----------- ---------------------------------------------------- 1 {"ARRAY_1":[{"IDENTIFIER":1,"NAME":"ALLEN, REG","...

These scenarios answer the original request, and go beyond.

 

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