使用SQL将json文件发送到IBM System i和QSYS2.IFS_WRITE上的IFS

Question

我想从db2文件中提取数据，并将它们以.json格式写入IFS。下面的代码在向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;

但是，当添加json格式时，它将不再工作，并会产生错误。

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;

错误是：

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.

我在V7R3M0上

PTF SF99703为22级

感谢你的帮助，

Answer 1

似乎C代码生成的代码试图创建一个可以接收太大的a的结构

$$$***/                       
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.

如果您需要一个那么大的块，那么我认为您必须询问IBM，或者使用declare/open/fetch (它将使用一个定位器)

但是如果不是，如果15M就足够了，你可以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

Answer 2

一旦切换到json_arrayagg，就不需要for循环了。json_object或json_arrayagg的整个选择可以指定为ifs_write过程的line参数的值。

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)