Publishing file contents using JSON and SQL

Publishing file contents using JSON and SQL.sql

stop;

-- Publish the data within a table using SQL
with json_rows (j) as (
    select json_object(
        key 'EMPNO' value empno, key 'FIRSTNME' value firstnme,
        key 'MIDINIT' value midinit, key 'LASTNAME' value lastname,
        key 'WORKDEPT' value workdept, key 'PHONENO' value phoneno,
        key 'HIREDATE' value hiredate, key 'JOB' value job,
        key 'EDLEVEL' value edlevel, key 'SEX' value sex,
        key 'BIRTHDATE' value birthdate, key 'SALARY' value salary,
        key 'BONUS' value bonus, key 'COMM' value comm
        absent on null returning clob(2g) ccsid 1208 format json
      )
      from shoestore.employee
      order by lastname
      limit 3 offset 0)
  select json_object(
      key 'SHOESTORE.EMPLOYEE' value json_arrayagg(
        j format json
      ) absent on null returning clob(2g) ccsid 1208 format json
    )
    from json_rows;
stop;
-- Use dynamic SQL to build JSON publishing function queries!
set path   coolstuff;
set schema coolstuff;
CREATE TYPE coolstuff.COLUMN_NAME_ARRAY_TYPE AS VARCHAR(128) CCSID 37 ARRAY [8000]; 
create or replace variable coolstuff.myJSONvar clob(2G) CCSID 1208;
CREATE OR REPLACE FUNCTION coolstuff.generate_json_for_a_table(p_schema_name VARCHAR(128),
                                                               p_table_name  VARCHAR(128),
                                                               p_pagination  VARCHAR(50) default ' LIMIT 10 OFFSET 0 ')
   RETURNS CLOB(2G) CCSID 1208 SPECIFIC coolstuff.generate_json_for_a_table
   NOT DETERMINISTIC NO EXTERNAL ACTION NOT FENCED READS SQL DATA CALLED ON NULL INPUT
   SET OPTION COMMIT = *none, DBGVIEW = *SOURCE, DYNUSRPRF = *USER
   BEGIN
      DECLARE v_no_data SMALLINT DEFAULT 0;
      DECLARE V_COLUMN_NAME_ARRAY COLUMN_NAME_ARRAY_TYPE;
      DECLARE V_COLUMN_NAME_ARRAY_CARDINALITY BIGINT;
      DECLARE V_LOOPVAR BIGINT default 1;
      DECLARE V_JSON_STATEMENT_TEXT CLOB(2M) default '';
      DECLARE v_json_object CLOB(2g) CCSID 1208 ;
         
      SELECT ARRAY_AGG( qsys2.delimit_name(column_name) ) INTO V_COLUMN_NAME_ARRAY 
         FROM qsys2.syscolumns 
         WHERE table_schema = p_schema_name AND table_name = p_table_name and CCSID<>65535;
         -- Contains 65535 if the column is a BINARY,VARBIN, BLOB, or ROWID.        
      SET V_COLUMN_NAME_ARRAY_CARDINALITY = CARDINALITY(V_COLUMN_NAME_ARRAY);
      SET V_JSON_STATEMENT_TEXT = 'with json_rows(j) AS ( SELECT JSON_OBJECT('; 
      -- Add the key-value pairs
      WHILE (V_LOOPVAR <= V_COLUMN_NAME_ARRAY_CARDINALITY) DO
        SET V_JSON_STATEMENT_TEXT = V_JSON_STATEMENT_TEXT CONCAT
            ' KEY ''' CONCAT V_COLUMN_NAME_ARRAY[V_LOOPVAR] CONCAT ''' VALUE ' CONCAT V_COLUMN_NAME_ARRAY[V_LOOPVAR];
        SET V_LOOPVAR = V_LOOPVAR + 1;
        IF  (V_LOOPVAR < V_COLUMN_NAME_ARRAY_CARDINALITY + 1) THEN 
          SET V_JSON_STATEMENT_TEXT = V_JSON_STATEMENT_TEXT CONCAT ',';
        END IF;        
      END WHILE;
      SET V_JSON_STATEMENT_TEXT = V_JSON_STATEMENT_TEXT CONCAT ' ABSENT ON NULL RETURNING CLOB (2G) CCSID 1208 FORMAT JSON) 
            FROM ' concat qsys2.delimit_name(p_schema_name) concat '.' concat qsys2.delimit_name(p_table_name);
      -- Add pagination                                                                                    
      SET V_JSON_STATEMENT_TEXT = V_JSON_STATEMENT_TEXT CONCAT p_pagination concat ')';
      -- Add CTE projection
      SET V_JSON_STATEMENT_TEXT = V_JSON_STATEMENT_TEXT CONCAT
          'SELECT JSON_OBJECT(KEY ''' concat 
                                upper(p_schema_name) concat '.' concat 
                                upper(p_table_name)  concat ''' VALUE 
                                JSON_ARRAYAGG(j format json) absent ON NULL
                                returning CLOB(2g) CCSID 1208 format json) from json_rows';
      -- I'm only using a global variable to make it easier to develop & debug
      EXECUTE IMMEDIATE 'SET coolstuff.myJSONvar = (' concat V_JSON_STATEMENT_TEXT concat ')';
RETURN coolstuff.myJSONvar;
END;
    
stop;

values coolstuff.generate_json_for_a_table(
   p_schema_name => 'TOYSTORE',
   p_table_name  => 'SALES',
   p_pagination  => ' limit 100000 ');
   

As I looked at this, I realized I already had the data files needed. I used the same files when I wrote this article, https://lnkd.in/gzbh8fz6. If I had seen this first, I would have been half way to the solution.