SQL write CLOB to IFS file

ifs_write_clob.sql

--
-- Writes UTF-8 CLOB to IFS 
-- Simple way to write a stream file to the IFS, by using C runtime as inline code
--
-- This also showcase how to integrate the C code directly into your UDTF
--
-- Simply paste this gist into ACS SQL and step through the example.
--
-- Note: I am using library QUSRSYS. I suggest you put it into your own tool library

-- It is a cool example how far you can go with SQL: Have fun :)  
-- (C) Niels Liisberg 2022-2024
--
-- This gist is distributed on an "as is" basis, without warranties 
-- or conditions of any kind, either express or implied.
----------------------------------------------------------------------------------------------
call qcmdexc ('crtsrcpf FILE(QTEMP/C) MBR(C) RCDLEN(200)');
insert into qtemp.c (srcdta) values 
    ('{'),
    ('#include <sys/types.h>'),
    ('#include <sys/stat.h>'),
    ('#include <QSYSINC/H/fcntl>'), 
    ('#include <QSYSINC/H/SQLUDF>'),
    ('long chunk, offset, inputLen = 0, len, rc,outfile,l;'),
    ('unsigned char buf [32760];'),
    ('mode_t mode = S_IRUSR | S_IWUSR | S_IXUSR;'),
    ('long option = O_WRONLY | O_CREAT | O_TRUNC | O_CCSID ;'),
    ('IFS_WRITE_CLOB.NAME.DAT[IFS_WRITE_CLOB.NAME.LEN] =0;'),
    ('outfile = open(IFS_WRITE_CLOB.NAME.DAT, option, mode, 1208);'),
    ('rc = sqludf_length(&IFS_WRITE_CLOB.BUFFER,&inputLen);'),
    ('for (offset = 1; offset <=  inputLen; offset += sizeof(buf) ) { '),
    ('    chunk = inputLen - offset + 1;'),
    ('    if (chunk > sizeof(buf)) chunk = sizeof(buf); '),
    ('    rc = sqludf_substr ('),
    ('         &IFS_WRITE_CLOB.BUFFER, '),
    ('         offset,'),
    ('         chunk,'),
    ('         buf,'),
    ('         &len'),
    ('    );'),
    ('    l = write(outfile, buf , len);'),
    ('}'),
    ('close (outfile);'),
    ('}')
;

create or replace procedure qusrsys.ifs_write_clob(name varchar(256), buffer clob (2g) ccsid 1208 )
external action 
modifies sql data
specific IFS_WRTCLB
set option output=*print, commit=*ur, dbgview = *source
begin 
    include qtemp/c(c);
end;    

call qcmdexc ('dltf FILE(QTEMP/C) ');

-----------------------
-- Test cases:
call qusrsys.ifs_write_clob ('/tmp/test.txt'  , 'Hello');  

-- National chars:
call qusrsys.ifs_write_clob ('/tmp/test.txt'  , 'Smørrebrødspålæg');  

-- Write huge clob this will be exactly 60000 bytes:
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
    ))
); 

-- edgecase - null? Wil produce an empty outfile
call qusrsys.ifs_write_clob('/tmp/test.txt'  , null);