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,
CodePudding user response:
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
CodePudding user response:
once you switched to json_arrayarg
there is no need for the for
loop. The entire select of the json_object
or json_arrayarg
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)