Using SQL to send json file to IFS on IBM System i with QSYS2.IFS_WRITE

Question

I would like to extract data from db2 files and write them in .json format to the IFS. The below code does work insofar as it sends a flat file to the IFS.

BEGIN
CALL QSYS2.IFS_WRITE(
     PATH_NAME =>'/myIFSdir/testout.txt',
                       LINE => '',
                       OVERWRITE => 'REPLACE',
                       END_OF_LINE => 'NONE',
     FILE_CCSID => 1208);

FOR select btbnbr as a
     from gldbfa/glpbt
     where btbnbr in (241858, 241934, 241983, 242534, 242538, 242657, 242708, 242717, 242723)

DO
    CALL QSYS2.IFS_WRITE(
     PATH_NAME => '/myIFSdir/testout.txt',
     LINE => a,
     FILE_CCSID => 1208);
  END FOR;
END;

However, when adding json formatting it no longer works and produces error.

BEGIN
CALL QSYS2.IFS_WRITE(
     PATH_NAME =>'/myIFSdir/testout.json',
                       LINE => '',
                       OVERWRITE => 'REPLACE',
                       END_OF_LINE => 'NONE',
     FILE_CCSID => 1208);

FOR select
     json_object('top' value (json_arrayagg(
             json_object ('number' value btbnbr)
             ))) as a
     from gldbfa/glpbt
     where btbnbr in (241858, 241934, 241983, 242534, 242538, 242657, 242708, 242717, 242723)

DO
    CALL QSYS2.IFS_WRITE(
     PATH_NAME => '/myIFSdir/testout.json',
     LINE => a,
     FILE_CCSID => 1208);
  END FOR;
END;

The error is:

SQL State: 42904
Vendor Code: -7032
Message: [SQL7032] SQL procedure, function, trigger, or variable *N in *N not created. Cause . . . . . :   SQL procedure, function, trigger, or variable *N in *N, or program for compound (dynamic) statement was not created. The compile was not successful. SQL creates an SQL procedure, function, trigger, variable, or a compound (dynamic) statement as a C program that contains embedded SQL.  Errors not found during the initial parsing of the CREATE PROCEDURE, ALTER PROCEDURE, CREATE FUNCTION, ALTER FUNCTION, CREATE TRIGGER, CREATE VARIABLE, or compound (dynamic) statement can be found during the precompile. Recovery  . . . :   If a compile error occurred, see the appropriate listing in QSYSPRT. If the SQL precompile failed, there is always a listing with the error. If the C compile failed, the listing is only created if requested. Specify SET OPTION OUTPUT=*PRINT prior to the routine body in the CREATE PROCEDURE, CREATE FUNCTION, or CREATE TRIGGER statement if listings are required. To see these errors for a compound (dynamic) statement, temporarily change it to a CREATE PROCEDURE statement.

I am on V7R3M0

PTF SF99703 is level 22

Grateful for any help,

user_12111821 · Answer

It seems the C code generated tries to make a struct that can receive a that's too big

$$$***/                       
typedef struct {              
/***$$$                       
SQL TYPE IS CLOB(2147483647) A
$$$***/                       
_Packed struct A_t {          
   unsigned long length;      
   char  data[2147483647];    
 } A                          
                          ;          
short SQLP_I2;                           
short AT_END;                            
unsigned SQLCursorOpen_0 :1;             
 } SQLP_L4_T;      
SQLP_L4_T SQLP_L4;
*=SEVERE==========> a - CZM0049  The size of object SQLP_L4 exceeds the compiler limit.

if you need a clob that big then I think you have to ask IBM, or use declare/open/fetch cursor (it will use a locator)

but if not, and if 15M are enough, you can CAST(... AS CLOB(15M))

BEGIN
CALL QSYS2.IFS_WRITE(
     PATH_NAME =>'/myIFSdir/testout.json',
                       LINE => '',
                       OVERWRITE => 'REPLACE',
                       END_OF_LINE => 'NONE',
     FILE_CCSID => 1208);
FOR select
     cast(json_object('top' value (json_arrayagg(
             json_object ('number' value btbnbr)
             ))) as clob(15m)) as a
     from (values 241858, 241934, 241983, 242534, 242538, 242657, 242708, 242717, 242723) e (btbnbr)
DO
    CALL QSYS2.IFS_WRITE(
     PATH_NAME => '/myIFSdir/testout.json',
     LINE => a,
     FILE_CCSID => 1208);
  END FOR;
end

user_5484737 · Answer

once you switched to json_arrayagg there is no need for the for loop. The entire select of the json_object or json_arrayagg can be specified as the value of the line parameter of the ifs_write procedure.

CALL QSYS2.IFS_WRITE(                                    
     PATH_NAME => '/home/steve/srcmbr.json',          
  overwrite => 'REPLACE',                                
     LINE => (                                           
select  json_arrayagg(                                   
             json_object('srcseq' value a.srcseq,        
                         'srcdat' value a.srcdat,        
                         'srcdta' value rtrim(a.srcdta)) 
         ) json                                          
from qrpglesrc a ),                                      
     FILE_CCSID => 1208)

Using SQL to send json file to IFS on IBM System i with QSYS2.IFS_WRITE

2 Answers