The request... Is it possible to extract data from IBM i into JSON format with a Db2 service?

db2_to_json.sql

--
-- Subject: The request... return SQL services detail using JSON.
-- Author: Scott Forstie  
-- Date  : April, 2023
-- Features Used : This Gist uses qsys2.syscolumns2, listagg(), rtrim(), dynamic SQL, SQL PL, PIPE
--
-- Note: 
--       When someone asks you to return Db2 for i data "as JSON", they probably want you
--       to publish a JSON document, which contains good key names and of course, the data.
--
--       SQL is used to build the Query we need.
--       Then, SQL is used again to execute the Query and return the results.
stop;

--
-- System status info
--
select *
  from qsys2.system_status_info;
stop;

--
-- What are the column names
--
select column_name, SYSTEM_COLUMN_NAME
  from qsys2.syscolumns2
  where TABLE_SCHEMA = 'QSYS2' and
        table_name = 'SYSTEM_STATUS_INFO'
  order by ORDINAL_POSITION;
stop;

--
-- System status info, returned in JSON form (
--
select json_object(
    key 'TOTAL_JOBS' value TOTAL_JOBS
  )
  from qsys2.system_status_info;
stop;

--
-- Build a JSON publishing query (partial)
--
select 'json_object( ' concat
    listagg(
      ' key ''' concat rtrim(SYSTEM_COLUMN_NAME) concat ''' value ' concat
        rtrim(SYSTEM_COLUMN_NAME), ', ') concat ')'
  from qsys2.syscolumns2
  where TABLE_SCHEMA = 'QSYS2' and
        table_name = 'SYSTEM_STATUS_INFO';
stop;

--
-- Build a JSON publishing query (full)
--
select 'select json_object( ' concat
    listagg(
      ' key ''' concat rtrim(SYSTEM_COLUMN_NAME) concat ''' value ' concat
        rtrim(SYSTEM_COLUMN_NAME), ', ') concat ') from qsys2.system_status_info'
  from qsys2.syscolumns2
  where TABLE_SCHEMA = 'QSYS2' and
        table_name = 'SYSTEM_STATUS_INFO';
stop;

--
-- Nice... but we can do a smidge better
--
select json_object(
    key 'TOTAL_JOBS' value TOTAL_JOBS, key 'MAX_JOBS' value MAX_JOBS, key 'ACT_JOBS' value ACT_JOBS,
    key 'INTER_JOBS' value INTER_JOBS, key 'JOBS_ON' value JOBS_ON, key 'JOBS_DSC' value JOBS_DSC,
    key 'JOBS_SYSRQ' value JOBS_SYSRQ, key 'JOBS_GRP' value JOBS_GRP, key 'JOBS_PRT' value JOBS_PRT,
    key 'BATCH_RUN' value BATCH_RUN, key 'BATCH_WAIT' value BATCH_WAIT,
    key 'BATCH_END' value BATCH_END, key 'BATCH_MSGW' value BATCH_MSGW,
    key 'BATCH_RHLD' value BATCH_RHLD, key 'BATCH_SHLD' value BATCH_SHLD,
    key 'BATCH_JHLD' value BATCH_JHLD, key 'BATCH_NOS' value BATCH_NOS,
    key 'BATCH_PRT' value BATCH_PRT, key 'ELAP_TIME' value ELAP_TIME,
    key 'ELAP_USED' value ELAP_USED, key 'ELAP_SHARE' value ELAP_SHARE,
    key 'ELAP_UNCAP' value ELAP_UNCAP, key 'CONFIGCPUS' value CONFIGCPUS,
    key 'CPU_SHARE' value CPU_SHARE, key 'CPU_CAP' value CPU_CAP, key 'CPU_RATE' value CPU_RATE,
    key 'CPU_AVG' value CPU_AVG, key 'CPU_MIN' value CPU_MIN, key 'CPU_MAX' value CPU_MAX,
    key 'CPU_SQL' value CPU_SQL, key 'MAIN_STG' value MAIN_STG, key 'SYS_STG' value SYS_STG,
    key 'AUX_STG' value AUX_STG, key 'SYS_RATE' value SYS_RATE, key 'TEMP_CUR' value TEMP_CUR,
    key 'TEMP_MAX' value TEMP_MAX, key 'PERM_RATE' value PERM_RATE, key 'TEMP_RATE' value TEMP_RATE,
    key 'TEMP_256MB' value TEMP_256MB, key 'TEMP_4GB' value TEMP_4GB,
    key 'PERM_256MB' value PERM_256MB, key 'PERM_4GB' value PERM_4GB, key 'TEMP_JS' value TEMP_JS,
    key 'PERM_JS' value PERM_JS, key 'TOTAL_JOBT' value TOTAL_JOBT,
    key 'AVAIL_JOBT' value AVAIL_JOBT, key 'INUSE_JOBT' value INUSE_JOBT,
    key 'ACT_JOBT' value ACT_JOBT, key 'JOBQ_JOBT' value JOBQ_JOBT, key 'OUTQ_JOBT' value OUTQ_JOBT,
    key 'PEND_JOBT' value PEND_JOBT, key 'HOST_NAME' value HOST_NAME, key 'PART_ID' value PART_ID,
    key 'NUM_PART' value NUM_PART, key 'ACT_THREAD' value ACT_THREAD,
    key 'REST_STATE' value REST_STATE, key 'PART_NAME' value PART_NAME,
    key 'PART_GROUP' value PART_GROUP, key 'POOL_ID' value POOL_ID, key 'DEF_MEM' value DEF_MEM,
    key 'MIN_MEM' value MIN_MEM, key 'MAX_MEM' value MAX_MEM, key 'MEM_INCR' value MEM_INCR,
    key 'DED_PROC' value DED_PROC, key 'PHY_PROC' value PHY_PROC, key 'PHY_SHARE' value PHY_SHARE,
    key 'MAX_PHY' value MAX_PHY, key 'DEF_VIRT' value DEF_VIRT, key 'VIRT_PROC' value VIRT_PROC,
    key 'MIN_VIRT' value MIN_VIRT, key 'MAX_VIRT' value MAX_VIRT, key 'DEF_CAP' value DEF_CAP,
    key 'CAPACITY' value CAPACITY, key 'AVAIL_CAP' value AVAIL_CAP,
    key 'MIN_REQCAP' value MIN_REQCAP, key 'MAX_LICCAP' value MAX_LICCAP,
    key 'MIN_CAP' value MIN_CAP, key 'MAX_CAP' value MAX_CAP, key 'CAP_INCR' value CAP_INCR,
    key 'DEF_INTCAP' value DEF_INTCAP, key 'INT_CAP' value INT_CAP,
    key 'INT_THRESH' value INT_THRESH, key 'AVL_INTCAP' value AVL_INTCAP,
    key 'MIN_INTCAP' value MIN_INTCAP, key 'MAX_INTCAP' value MAX_INTCAP,
    key 'DEF_CAPW' value DEF_CAPW, key 'VAR_CAPW' value VAR_CAPW, key 'AVAIL_CAPW' value AVAIL_CAPW,
    key 'HW_MLT_THR' value HW_MLT_THR, key 'HW_BND_THR' value HW_BND_THR,
    key 'THREADS_PP' value THREADS_PP, key 'LATENCY' value LATENCY,
    key 'DISPATCH_T' value DISPATCH_T, key 'CPU_TOTAL' value CPU_TOTAL,
    key 'CPU_INTER' value CPU_INTER, key 'CPU_THRESH' value CPU_THRESH,
    key 'CPU_UNUSED' value CPU_UNUSED, key 'MACH_TYPE' value MACH_TYPE,
    key 'MACH_MOD' value MACH_MOD, key 'SERIAL' value SERIAL, key 'ATTN_LIGHT' value ATTN_LIGHT,
    key 'IPL_MODE' value IPL_MODE, key 'IPL_TYPE' value IPL_TYPE, key 'JRNRCYCNT' value JRNRCYCNT,
    key 'CACHEWAIT' value CACHEWAIT
  ) concat '}'
  from qsys2.system_status_info;
stop;

--
-- Build a JSON publishing query  
--
select 'select ''{"SSI": '' concat json_object(' concat
    listagg(
      CAST(' key ''' concat rtrim(SYSTEM_COLUMN_NAME) concat ''' value ' concat
        rtrim(SYSTEM_COLUMN_NAME) AS CLOB(1M)), ', ') concat ') concat ''}'' from qsys2.system_status_info'
  from qsys2.syscolumns2
  where TABLE_SCHEMA = 'QSYS2' and
        table_name = 'SYSTEM_STATUS_INFO';
stop;

--
-- Final form?
--
select '{"SSI": ' concat
    json_object(
      key 'TOTAL_JOBS' value TOTAL_JOBS, key 'MAX_JOBS' value MAX_JOBS,
      key 'ACT_JOBS' value ACT_JOBS, key 'INTER_JOBS' value INTER_JOBS, key 'JOBS_ON' value JOBS_ON,
      key 'JOBS_DSC' value JOBS_DSC, key 'JOBS_SYSRQ' value JOBS_SYSRQ,
      key 'JOBS_GRP' value JOBS_GRP, key 'JOBS_PRT' value JOBS_PRT, key 'BATCH_RUN' value BATCH_RUN,
      key 'BATCH_WAIT' value BATCH_WAIT, key 'BATCH_END' value BATCH_END,
      key 'BATCH_MSGW' value BATCH_MSGW, key 'BATCH_RHLD' value BATCH_RHLD,
      key 'BATCH_SHLD' value BATCH_SHLD, key 'BATCH_JHLD' value BATCH_JHLD,
      key 'BATCH_NOS' value BATCH_NOS, key 'BATCH_PRT' value BATCH_PRT,
      key 'ELAP_TIME' value ELAP_TIME, key 'ELAP_USED' value ELAP_USED,
      key 'ELAP_SHARE' value ELAP_SHARE, key 'ELAP_UNCAP' value ELAP_UNCAP,
      key 'CONFIGCPUS' value CONFIGCPUS, key 'CPU_SHARE' value CPU_SHARE,
      key 'CPU_CAP' value CPU_CAP, key 'CPU_RATE' value CPU_RATE, key 'CPU_AVG' value CPU_AVG,
      key 'CPU_MIN' value CPU_MIN, key 'CPU_MAX' value CPU_MAX, key 'CPU_SQL' value CPU_SQL,
      key 'MAIN_STG' value MAIN_STG, key 'SYS_STG' value SYS_STG, key 'AUX_STG' value AUX_STG,
      key 'SYS_RATE' value SYS_RATE, key 'TEMP_CUR' value TEMP_CUR, key 'TEMP_MAX' value TEMP_MAX,
      key 'PERM_RATE' value PERM_RATE, key 'TEMP_RATE' value TEMP_RATE,
      key 'TEMP_256MB' value TEMP_256MB, key 'TEMP_4GB' value TEMP_4GB,
      key 'PERM_256MB' value PERM_256MB, key 'PERM_4GB' value PERM_4GB, key 'TEMP_JS' value TEMP_JS,
      key 'PERM_JS' value PERM_JS, key 'TOTAL_JOBT' value TOTAL_JOBT,
      key 'AVAIL_JOBT' value AVAIL_JOBT, key 'INUSE_JOBT' value INUSE_JOBT,
      key 'ACT_JOBT' value ACT_JOBT, key 'JOBQ_JOBT' value JOBQ_JOBT,
      key 'OUTQ_JOBT' value OUTQ_JOBT, key 'PEND_JOBT' value PEND_JOBT,
      key 'HOST_NAME' value HOST_NAME, key 'PART_ID' value PART_ID, key 'NUM_PART' value NUM_PART,
      key 'ACT_THREAD' value ACT_THREAD, key 'REST_STATE' value REST_STATE,
      key 'PART_NAME' value PART_NAME, key 'PART_GROUP' value PART_GROUP,
      key 'POOL_ID' value POOL_ID, key 'DEF_MEM' value DEF_MEM, key 'MIN_MEM' value MIN_MEM,
      key 'MAX_MEM' value MAX_MEM, key 'MEM_INCR' value MEM_INCR, key 'DED_PROC' value DED_PROC,
      key 'PHY_PROC' value PHY_PROC, key 'PHY_SHARE' value PHY_SHARE, key 'MAX_PHY' value MAX_PHY,
      key 'DEF_VIRT' value DEF_VIRT, key 'VIRT_PROC' value VIRT_PROC, key 'MIN_VIRT' value MIN_VIRT,
      key 'MAX_VIRT' value MAX_VIRT, key 'DEF_CAP' value DEF_CAP, key 'CAPACITY' value CAPACITY,
      key 'AVAIL_CAP' value AVAIL_CAP, key 'MIN_REQCAP' value MIN_REQCAP,
      key 'MAX_LICCAP' value MAX_LICCAP, key 'MIN_CAP' value MIN_CAP, key 'MAX_CAP' value MAX_CAP,
      key 'CAP_INCR' value CAP_INCR, key 'DEF_INTCAP' value DEF_INTCAP, key 'INT_CAP' value INT_CAP,
      key 'INT_THRESH' value INT_THRESH, key 'AVL_INTCAP' value AVL_INTCAP,
      key 'MIN_INTCAP' value MIN_INTCAP, key 'MAX_INTCAP' value MAX_INTCAP,
      key 'DEF_CAPW' value DEF_CAPW, key 'VAR_CAPW' value VAR_CAPW,
      key 'AVAIL_CAPW' value AVAIL_CAPW, key 'HW_MLT_THR' value HW_MLT_THR,
      key 'HW_BND_THR' value HW_BND_THR, key 'THREADS_PP' value THREADS_PP,
      key 'LATENCY' value LATENCY, key 'DISPATCH_T' value DISPATCH_T,
      key 'CPU_TOTAL' value CPU_TOTAL, key 'CPU_INTER' value CPU_INTER,
      key 'CPU_THRESH' value CPU_THRESH, key 'CPU_UNUSED' value CPU_UNUSED,
      key 'MACH_TYPE' value MACH_TYPE, key 'MACH_MOD' value MACH_MOD, key 'SERIAL' value SERIAL,
      key 'ATTN_LIGHT' value ATTN_LIGHT, key 'IPL_MODE' value IPL_MODE,
      key 'IPL_TYPE' value IPL_TYPE, key 'JRNRCYCNT' value JRNRCYCNT,
      key 'CACHEWAIT' value CACHEWAIT, key 'JOB_SCHED' value JOB_SCHED, key 'SMAPP' value SMAPP,
      key 'SMAPP_AP' value SMAPP_AP, key 'AP_RECOV' value AP_RECOV, key 'AP_INELIG' value AP_INELIG,
      key 'SMAPP_STG' value SMAPP_STG
    ) concat '}'
  from qsys2.system_status_info;
stop;

--
-- Add a standard header?
--
select host_name from sysibmadm.env_sys_info;
select host_name from qsys2.system_status_info;
stop;

--
-- Final form?
--
select '{"SSI": ' concat
    json_object(
      key 'IBMi' value e.Host_name, key 'TOTAL_JOBS' value TOTAL_JOBS,
      key 'MAX_JOBS' value MAX_JOBS, key 'ACT_JOBS' value ACT_JOBS,
      key 'INTER_JOBS' value INTER_JOBS, key 'JOBS_ON' value JOBS_ON, key 'JOBS_DSC' value JOBS_DSC,
      key 'JOBS_SYSRQ' value JOBS_SYSRQ, key 'JOBS_GRP' value JOBS_GRP,
      key 'JOBS_PRT' value JOBS_PRT, key 'BATCH_RUN' value BATCH_RUN,
      key 'BATCH_WAIT' value BATCH_WAIT, key 'BATCH_END' value BATCH_END,
      key 'BATCH_MSGW' value BATCH_MSGW, key 'BATCH_RHLD' value BATCH_RHLD,
      key 'BATCH_SHLD' value BATCH_SHLD, key 'BATCH_JHLD' value BATCH_JHLD,
      key 'BATCH_NOS' value BATCH_NOS, key 'BATCH_PRT' value BATCH_PRT,
      key 'ELAP_TIME' value ELAP_TIME, key 'ELAP_USED' value ELAP_USED,
      key 'ELAP_SHARE' value ELAP_SHARE, key 'ELAP_UNCAP' value ELAP_UNCAP,
      key 'CONFIGCPUS' value s.CONFIGCPUS, key 'CPU_SHARE' value CPU_SHARE,
      key 'CPU_CAP' value CPU_CAP, key 'CPU_RATE' value CPU_RATE, key 'CPU_AVG' value CPU_AVG,
      key 'CPU_MIN' value CPU_MIN, key 'CPU_MAX' value CPU_MAX, key 'CPU_SQL' value CPU_SQL,
      key 'MAIN_STG' value MAIN_STG, key 'SYS_STG' value SYS_STG, key 'AUX_STG' value AUX_STG,
      key 'SYS_RATE' value SYS_RATE, key 'TEMP_CUR' value TEMP_CUR, key 'TEMP_MAX' value TEMP_MAX,
      key 'PERM_RATE' value PERM_RATE, key 'TEMP_RATE' value TEMP_RATE,
      key 'TEMP_256MB' value TEMP_256MB, key 'TEMP_4GB' value TEMP_4GB,
      key 'PERM_256MB' value PERM_256MB, key 'PERM_4GB' value PERM_4GB, key 'TEMP_JS' value TEMP_JS,
      key 'PERM_JS' value PERM_JS, key 'TOTAL_JOBT' value TOTAL_JOBT,
      key 'AVAIL_JOBT' value AVAIL_JOBT, key 'INUSE_JOBT' value INUSE_JOBT,
      key 'ACT_JOBT' value ACT_JOBT, key 'JOBQ_JOBT' value JOBQ_JOBT,
      key 'OUTQ_JOBT' value OUTQ_JOBT, key 'PEND_JOBT' value PEND_JOBT,
      key 'HOST_NAME' value s.HOST_NAME, key 'PART_ID' value PART_ID, key 'NUM_PART' value NUM_PART,
      key 'ACT_THREAD' value ACT_THREAD, key 'REST_STATE' value REST_STATE,
      key 'PART_NAME' value PART_NAME, key 'PART_GROUP' value PART_GROUP,
      key 'POOL_ID' value POOL_ID, key 'DEF_MEM' value DEF_MEM, key 'MIN_MEM' value MIN_MEM,
      key 'MAX_MEM' value MAX_MEM, key 'MEM_INCR' value MEM_INCR, key 'DED_PROC' value DED_PROC,
      key 'PHY_PROC' value PHY_PROC, key 'PHY_SHARE' value PHY_SHARE, key 'MAX_PHY' value MAX_PHY,
      key 'DEF_VIRT' value DEF_VIRT, key 'VIRT_PROC' value VIRT_PROC, key 'MIN_VIRT' value MIN_VIRT,
      key 'MAX_VIRT' value MAX_VIRT, key 'DEF_CAP' value DEF_CAP, key 'CAPACITY' value CAPACITY,
      key 'AVAIL_CAP' value AVAIL_CAP, key 'MIN_REQCAP' value MIN_REQCAP,
      key 'MAX_LICCAP' value MAX_LICCAP, key 'MIN_CAP' value MIN_CAP, key 'MAX_CAP' value MAX_CAP,
      key 'CAP_INCR' value CAP_INCR, key 'DEF_INTCAP' value DEF_INTCAP, key 'INT_CAP' value INT_CAP,
      key 'INT_THRESH' value INT_THRESH, key 'AVL_INTCAP' value AVL_INTCAP,
      key 'MIN_INTCAP' value MIN_INTCAP, key 'MAX_INTCAP' value MAX_INTCAP,
      key 'DEF_CAPW' value DEF_CAPW, key 'VAR_CAPW' value VAR_CAPW,
      key 'AVAIL_CAPW' value AVAIL_CAPW, key 'HW_MLT_THR' value HW_MLT_THR,
      key 'HW_BND_THR' value HW_BND_THR, key 'THREADS_PP' value THREADS_PP,
      key 'LATENCY' value LATENCY, key 'DISPATCH_T' value DISPATCH_T,
      key 'CPU_TOTAL' value CPU_TOTAL, key 'CPU_INTER' value CPU_INTER,
      key 'CPU_THRESH' value CPU_THRESH, key 'CPU_UNUSED' value CPU_UNUSED,
      key 'MACH_TYPE' value MACH_TYPE, key 'MACH_MOD' value MACH_MOD, key 'SERIAL' value SERIAL,
      key 'ATTN_LIGHT' value ATTN_LIGHT, key 'IPL_MODE' value IPL_MODE,
      key 'IPL_TYPE' value IPL_TYPE, key 'JRNRCYCNT' value JRNRCYCNT,
      key 'CACHEWAIT' value CACHEWAIT
    )
  from sysibmadm.env_sys_info e, qsys2.system_status_info s;
stop;

--
-- Lets move the logic into a UDTF
--
create or replace function systools.db2_to_json (
      p_schema_name varchar(128) for sbcs data, 
      p_view_name varchar(128) for sbcs data,
      p_key varchar(10) for sbcs data
  )
  returns table (
    json_doc clob(2g) ccsid 1208
  )
  not deterministic
  external action
  modifies sql data
  fenced
  set option COMMIT = *NONE
  begin
    declare local_sqlcode integer;
    declare local_sqlstate char(5) for sbcs data;
    declare v_message_text varchar(70) for sbcs data;
    declare not_found condition for '02000';
    declare at_end integer default 0;
    declare v_json clob(2g) ccsid 1208; 
    declare json_doit_cursor_stmttext varchar(10000) for sbcs data;
    declare json_it_cursor_stmttext varchar(10000) for sbcs data;
    declare json_it_cursor cursor for json_it_cursor_statement;
    declare json_doit_cursor cursor for json_doit_cursor_statement;
    declare exit handler for sqlexception
    begin
      declare local_sqlcode integer;
      declare local_sqlstate char(5) for sbcs data;
      declare v_message_text varchar(200) for sbcs data;
      get diagnostics condition 1
          local_sqlcode = db2_returned_sqlcode, local_sqlstate = returned_sqlstate,
          v_message_text = message_text;
      call systools.lprintf('systools.db2_to_json() FAILED WITH SQLCODE=' concat local_sqlcode
          concat ' SQLSTATE=' concat local_sqlstate concat ' MESSAGE= ' concat v_message_text);
    end;
    declare continue handler for not_found set at_end = 1;
      
    set json_it_cursor_stmttext = 
      'select ''select ''''{"' concat p_key concat '":'''' concat json_object('' concat
         listagg(CAST('' key '''''' concat rtrim(SYSTEM_COLUMN_NAME) concat '''''' value '' concat
         rtrim(SYSTEM_COLUMN_NAME) AS CLOB(1M)), '', '') concat '') concat ''''}'''' from ' concat p_schema_name concat '.' concat p_view_name concat '''
       from qsys2.syscolumns2 where TABLE_SCHEMA = ? and table_name = ?'; 
              
    prepare json_it_cursor_statement from json_it_cursor_stmttext;
    open json_it_cursor using p_schema_name, p_view_name;
    fetch from json_it_cursor into json_doit_cursor_stmttext;
    call systools.lprintf('Doit statement: ' concat varchar(json_doit_cursor_stmttext,900));
    close json_it_cursor;
    
    prepare json_doit_cursor_statement from json_doit_cursor_stmttext;
    open json_doit_cursor using json_doit_cursor_stmttext;
    set at_end = 0;
    fetch from json_doit_cursor into v_json;
    w1: while (at_end = 0) do
      call systools.lprintf('Pipe: ' concat varchar(v_json,900));
      pipe(v_json);
      set at_end = 0;
      fetch from json_doit_cursor into v_json;
    end while;
    close json_doit_cursor;
    return;
  end;
stop;

--
-- And finally... the end game
--
select * 
  from table(systools.db2_to_json('QSYS2', 'SYSTEM_STATUS_INFO', 'SSI')); 
stop;
 
select * 
  from table(systools.db2_to_json('QSYS2', 'ASP_INFO', 'ASP')); 
stop;

When I copied this, created the function in my library, and ran it, the JSON object was missing the final curly brace }. Did anyone else see this?

Hi.
I had the wrong version of the SQL posted. The gist has been updated.
Please give it a fresh go.
Thanks, Scott

Wow. Thanks for the quick response. Yes, the new version creates a correctly formatted JSON object.