Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
SQL write CLOB to IFS file
-- This example are using the IFS_WRITE and IFS_APPEND
-- to produce a IFS files based on CLOB data in UTF-8
-----------------------------------------------------
create or replace procedure qusrsys.ifs_write_clob(name varchar(256), buf clob ccsid 1208 )
external action
modifies sql data
specific IFS_WRTCLB
set option output=*print, commit=*none, dbgview = *source
begin
declare remlen int;
declare startpos int;
declare writelen int;
if buf is not null then
set remlen = length(buf);
set startpos = 1;
while remlen > 0 do
set writelen = min(32000 ,remlen);
if startpos = 1 then
call qusrsys.ifs_write (name, substring ( buf , startpos , writelen));
else
call qusrsys.ifs_append (name, substring ( buf , startpos , writelen));
end if;
set startpos = startpos + writelen;
set remlen = remlen - writelen;
end while;
end if;
end;
-- Usecase
-- Write huge clob:
call qusrsys.ifs_write_clob('/tmp/test.txt' , repeat (clob('Hello ') , 10000));
-- Write JSON
call qusrsys.ifs_write_clob('/tmp/test.json' ,
json_array((
select
json_object (
'customerNumber' : trim(CUSNUM),
'name' : trim(LSTNAM)
)
from qiws.qcustcdt
))
);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment