System i SQL stream file from IFS

Question

IBM System i V7R3M0 I would like to pull a .json file with SQL. The SQL cannot resolve QSYS2.IFS_READ.

select x.id, x.username, x.firstName, x.lastName, x.email 
from json_table(
 QSYS2.IFS_READ(PATH_NAME => '/FileTransferData/userList.json'), 
 'lax $' columns (
  id INTEGER PATH 'lax $.id',
  username VARCHAR(30) FORMAT JSON PATH 'lax $.username',
  firstName VARCHAR(30) FORMAT JSON PATH 'lax $.firstName',
  lastName VARCHAR(30) FORMAT JSON PATH 'lax $.lastName',
  email VARCHAR(75) FORMAT JSON PATH 'lax $.email'
 )
)
as x;

The error is:

SQL State: 42704
Vendor Code: -204
Message: [SQL0204] IFS_READ in QSYS2 type *N not found. Cause . . . . . :   IFS_READ in QSYS2 type *N was not found. 

The job log in QZDASOINIT tells me the same thing, that I do not have IFS_READ in QSYS2

Job 111566/QUSER/QZDASOINIT started on 04/12/21 at 12:23:06 in subsystem QUSRWRK in QSYS. Job entered system on 04/12/21 at 12:23:06.
User MOLNARJ from client 10.111.0.24 connected to server.
The following special registers have been set: CLIENT_ACCTNG: ,
  CLIENT_APPLNAME: System i Navigator - Run SQL Scripts, CLIENT_PROGRAMID:
  cwbunnav.exe, CLIENT_USERID: MOLNARJ, CLIENT_WRKSTNNAME:
  FS2ISYMOB943.umassmemorial.org
Trigger Q__OSYS_QAQQINI_BEFORE_INSERT_______ in library QTEMP was added to file QAQQINI in library QTEMP.
Trigger Q__OSYS_QAQQINI_AFTER_INSERT________ in library QTEMP was added to file QAQQINI in library QTEMP.
Trigger Q__OSYS_QAQQINI_BEFORE_UPDATE_______ in library QTEMP was added to file QAQQINI in library QTEMP.
Trigger Q__OSYS_QAQQINI_AFTER_UPDATE________ in library QTEMP was added to  file QAQQINI in library QTEMP.
Trigger Q__OSYS_QAQQINI_BEFORE_DELETE_______ in library QTEMP was added to
  file QAQQINI in library QTEMP.
Trigger Q__OSYS_QAQQINI_AFTER_DELETE________ in library QTEMP was added to file QAQQINI in library QTEMP.
Object QAQQINI in QTEMP type *FILE created.
1 objects duplicated.
IFS_READ in QSYS2 type *N not found.

I'm between a rock and a hard place. My outsourced support company claims that I need to create a program to utilize the API. However, I believe the API is not installed.

I have based my work on IBM technical documents such as this: https://www.ibm.com/docs/en/i/7.4?topic=is-ifs-read-ifs-read-binary-ifs-read-utf8-table-functions

Running the example in this document (with file path and name changed to mine) give the same error.

SELECT * FROM TABLE(QSYS2.IFS_READ(PATH_NAME => '/FileTransferData/userList.json', END_OF_LINE => 'CRLF'));

Answer 1

This link indicates PTF SF99703 level 22 is required. You can check what's installed or available with :

with iLevel(iVersion, iRelease) as (
select
    OS_VERSION, OS_RELEASE
from
    sysibmadm.env_sys_info )
select
    case PTF_GROUP_CURRENCY when 'INSTALLED LEVEL IS CURRENT' then '' else PTF_GROUP_CURRENCY end,
    PTF_GROUP_ID "ID",
    PTF_GROUP_TITLE "Title",
    PTF_GROUP_LEVEL_INSTALLED "Installed",
    PTF_GROUP_LEVEL_AVAILABLE "Available",
    ptf_group_level_available - ptf_group_level_installed "Diff",
    date(to_date(PTF_GROUP_LAST_UPDATED_BY_IBM, 'MM/DD/YYYY')) "Available since",
    current date - date(to_date(PTF_GROUP_LAST_UPDATED_BY_IBM, 'MM/DD/YYYY')) "Days since available",
    PTF_GROUP_RELEASE "Release",
    PTF_GROUP_STATUS_ON_SYSTEM "Status"
from
    iLevel,
    systools.group_ptf_currency P
where
    ptf_group_id = 'SF99703'
order by
    ptf_group_level_available - ptf_group_level_installed desc;