This reads the JSON from the IFS – decodes it using JSON_TABLE and reports on time taken to perform decode.
In this case the JSON data is a simple layout that looks like this:
{"users":[{"userID":"@123","firstName":"Billy","lastName":"Bob","initials":"123","company":110,"division":30,"department":325,"secProfile":""},{"userID":"AAATEMP","firstName":"Albert","lastName":"Smith","initials":"AAA","company":110,"division":1,"department":1,"secProfile":""},{"userID":"AAATEST","firstName":"Andy","lastName":"Tester","initials":"AAA","company":110,"division":1,"department":530,"secProfile":"AAT"},{"userID":"AAA3","firstName":"Fred","lastName":"Blogs","initials":"AA3","company":110,"division":1,"department":1,"secProfile":"AAA"},{"userID":"AATEST","firstName":"AA","lastName":"TEST","initials":"AAT","company":110,"division":1,"department":1,"secProfile":""},{"userID":"ABB","firstName":"Abba","lastName":"Dancing Queen","initials":"","company":110,"division":1,"department":1,"secProfile":""},{"userID":"BINGBONG","firstName":"Bing","lastName":"Bong","initials":"BB","company":110,"division":2,"department":2,"secProfile":""},{"userID":"LITTENN","firstName":"Nick","lastName":"Litten","initials":"NJL","company":001,"division":1,"department":123,"secProfile":"Secret Squirrel"}]}
This JSON example contains the values:
USERID varchar(100);
FIRSTNAME varchar(100);
LASTNAME varchar(100);
INITIALS varchar(100);
COMPANY int(10);
DIVISION int(10);
DEPARTMENT int(10);
FIRSTNAME varchar(100);
LASTNAME varchar(100);
INITIALS varchar(100);
COMPANY int(10);
DIVISION int(10);
DEPARTMENT int(10);
Now lets read it and break it out into fields, which I am just storing in an array. You could write them to a file just as easily…
**FREE
/TITLE JSON_TABLE Decode JSON using SQL - Proof of Concept
// PROGRAM CREATION OVERRIDES:
// Create as a Module and bind into ILE program
//
// JSNIFSSQL.sqlrpgle (fully /free)
//
// This demonstrates reading JSON data from an IFS file and then
// parsing that JSON using SQL JSON_TABLE functions.
//
// The JSON data is loaded into a data structure suitable
// for display from a program debugger.
//
// MODIFICATION HISTORY:
// 07/17/2017 nick.litten@projex.com V1.00 LM740287
ctl-opt dftactgrp(no) actgrp('NICKLITTEN') option(nodebugio:srcstmt:nounref)
datfmt(*ISO) decedit('0.')
copyright('| JSNIFSSQL V1.0 06/27/2017 Use JSON TABLE to read JSON from IFS and parse it into array.');
// IFS file with JSON code that we will be reading and decoding
dcl-c ifsFilename
const('/littenn/getwebjsn.json');
dcl-s lastElem int(10);
dcl-s errMsg varchar(500) inz('');
dcl-s json sqltype(clob:16000000);
dcl-ds jsonFields qualified;
USERID varchar(100);
FIRSTNAME varchar(100);
LASTNAME varchar(100);
INITIALS varchar(100);
COMPANY int(10);
DIVISION int(10);
DEPARTMENT int(10);
end-ds;
dcl-ds result qualified;
success ind;
errmsg varchar(500);
jsonArray likeds(jsonFields) dim(9999);
end-ds;
dcl-s strTimeStamp timestamp inz; // start of pgm
dcl-s strJsonTimeStamp timestamp inz; // start of JSON decode logic
dcl-s endTimeStamp timestamp inz; // used to calculate duration
// use IBM i *APIS to loads IFS into variable
dcl-s Count int(10);
dcl-s Handle int(10);
dcl-s ifsData char(16000000);
dcl-s ifsDataLen int(10);
dcl-s rc int(10);
dcl-s O_RDONLY int(10) inz(1);
dcl-s O_TEXTDATA int(10) inz(16777216);
dcl-pr open int(10) extproc('open');
n pointer value options(string); // filename
*n int(10) value; // openflags
*n uns(10) value options(*nopass); // mode
*n uns(10) value options(*nopass); // codepage
end-pr;
dcl-pr read int(10) extproc('read');
*n int(10) value; // filehandle
*n pointer value; // datareceived
*n uns(10) value; // nbytes
end-pr;
dcl-pr close int(10) extproc('close');
*n int(10) value; // filehandle
end-pr;
// Parms for logging-text being sent back into program message queue
dcl-pr QMHSNDPM extpgm('QMHSNDPM');
*n char(7) const; // MsgID
*n char(20) const; // MsgFile
n char(32767) const options(varsize); // MsgData
*n int(10) const; // MsgDtaLen
*n char(10) const; // MsgType
*n char(10) const; // StackEntry
*n int(10) const; // StackCount
*n char(4); // MsgKey
*n char(32767) options(*varsize); // ErrorCode
end-pr;
dcl-ds ErrorCode;
BytesProv int(10) inz(0);
BytesAvail int(10) inz(0);
end-ds;
dcl-s joblogMsg char(200);
dcl-s MsgKey char(4);
/Title [---------- MAINLINE ---------- ]
strTimeStamp=%timestamp();
// Open the stream file
Handle = open(%trim(ifsFilename):O_RDONLY + O_TEXTDATA);
// Loop to read the stream file into variable "ifsData"
dou ifsDataLen<1;
Count += 1;
ifsDataLen=read(Handle:
%addr(ifsData):
%size(ifsData));
enddo;
// Close the stream file
rc= close(Handle);
if ifsData <> *blanks;
// we have the IFS data loaded into variable(ifsData) so lets load
// that variable into the SQL(clob) and process it using the JSON_TABLE
// to break the JSON out into fields for processing. NOTE: we are not
// doing anything with those fields in this program because this is just
// a proof of concept
exec SQL set option Naming = *Sys,
Commit = *None,
UsrPrf = *User,
DynUsrPrf = *User,
Datfmt = *iso,
CloSqlCsr = *EndMod ;
strJsonTimeStamp=%timestamp();
JSON_LEN = %len(%trim(ifsdata));
JSON_DATA = %trim(ifsData);
EXEC SQL DECLARE C1 CURSOR FOR
select *
from JSON_TABLE(:json, '$' // read the JSON data from a string
COLUMNS( NESTED '$.users[*]'
COLUMNS (USERID VARCHAR(100) PATH '$.userID',
FIRSTNAME VARCHAR(100) PATH '$.firstName',
LASTNAME VARCHAR(100) PATH '$.lastName',
INITIALS VARCHAR(100) PATH '$.initials',
COMPANY INT PATH '$.company',
DIVISION INT PATH '$.division',
DEPARTMENT INT PATH '$.department')
)) AS X;
EXEC SQL OPEN C1;
exec SQL fetch next from C1 into :jsonFields ;
dow sqlstt='00000' or %subst(sqlstt:1:2)='01';
lastelem += 1;
// Store Datastructure values in next element of 'return array'
result.jsonArray(lastelem) = jsonFields;
// here we could do something with each row of data ie: write to file
// or some other business logic.
exec SQL fetch next from C1 into :jsonFields ;
enddo;
exec SQL close C1;
result.success = *on;
else;
// If unable to load JSON data from the IFS then tell the world
result.success = *off;
result.errmsg = 'Bugger! I couldnt read the IFS file';
DSPLY %trim(result.errmsg);
endif;
// Set Ending timestamps and calculate runtime
endTimeStamp=%timestamp();
joblogMsg = 'JSNIFSSQL JSON_TABLE Completed with ' +
%char(lastelem) +
' elements. Total runtime:' +
%char(%diff(endTimeStamp:strTimeStamp:mseconds)) + '
SQL JSON_TABLE DECODE only:' +
%char(%diff(endTimeStamp:strJsonTimeStamp:mseconds));
// stick that message into the joblog so we can clearly see the runtime
QMHSNDPM( 'CPF9897'
: 'QCPFMSG LIBL'
: joblogMsg
: %len(%trimr(joblogMsg))
: 'DIAG'
: '*'
: 0
: MsgKey
: ErrorCode );
*inlr = *on;
/TITLE JSON_TABLE Decode JSON using SQL - Proof of Concept
// PROGRAM CREATION OVERRIDES:
// Create as a Module and bind into ILE program
//
// JSNIFSSQL.sqlrpgle (fully /free)
//
// This demonstrates reading JSON data from an IFS file and then
// parsing that JSON using SQL JSON_TABLE functions.
//
// The JSON data is loaded into a data structure suitable
// for display from a program debugger.
//
// MODIFICATION HISTORY:
// 07/17/2017 nick.litten@projex.com V1.00 LM740287
ctl-opt dftactgrp(no) actgrp('NICKLITTEN') option(nodebugio:srcstmt:nounref)
datfmt(*ISO) decedit('0.')
copyright('| JSNIFSSQL V1.0 06/27/2017 Use JSON TABLE to read JSON from IFS and parse it into array.');
// IFS file with JSON code that we will be reading and decoding
dcl-c ifsFilename
const('/littenn/getwebjsn.json');
dcl-s lastElem int(10);
dcl-s errMsg varchar(500) inz('');
dcl-s json sqltype(clob:16000000);
dcl-ds jsonFields qualified;
USERID varchar(100);
FIRSTNAME varchar(100);
LASTNAME varchar(100);
INITIALS varchar(100);
COMPANY int(10);
DIVISION int(10);
DEPARTMENT int(10);
end-ds;
dcl-ds result qualified;
success ind;
errmsg varchar(500);
jsonArray likeds(jsonFields) dim(9999);
end-ds;
dcl-s strTimeStamp timestamp inz; // start of pgm
dcl-s strJsonTimeStamp timestamp inz; // start of JSON decode logic
dcl-s endTimeStamp timestamp inz; // used to calculate duration
// use IBM i *APIS to loads IFS into variable
dcl-s Count int(10);
dcl-s Handle int(10);
dcl-s ifsData char(16000000);
dcl-s ifsDataLen int(10);
dcl-s rc int(10);
dcl-s O_RDONLY int(10) inz(1);
dcl-s O_TEXTDATA int(10) inz(16777216);
dcl-pr open int(10) extproc('open');
n pointer value options(string); // filename
*n int(10) value; // openflags
*n uns(10) value options(*nopass); // mode
*n uns(10) value options(*nopass); // codepage
end-pr;
dcl-pr read int(10) extproc('read');
*n int(10) value; // filehandle
*n pointer value; // datareceived
*n uns(10) value; // nbytes
end-pr;
dcl-pr close int(10) extproc('close');
*n int(10) value; // filehandle
end-pr;
// Parms for logging-text being sent back into program message queue
dcl-pr QMHSNDPM extpgm('QMHSNDPM');
*n char(7) const; // MsgID
*n char(20) const; // MsgFile
n char(32767) const options(varsize); // MsgData
*n int(10) const; // MsgDtaLen
*n char(10) const; // MsgType
*n char(10) const; // StackEntry
*n int(10) const; // StackCount
*n char(4); // MsgKey
*n char(32767) options(*varsize); // ErrorCode
end-pr;
dcl-ds ErrorCode;
BytesProv int(10) inz(0);
BytesAvail int(10) inz(0);
end-ds;
dcl-s joblogMsg char(200);
dcl-s MsgKey char(4);
/Title [---------- MAINLINE ---------- ]
strTimeStamp=%timestamp();
// Open the stream file
Handle = open(%trim(ifsFilename):O_RDONLY + O_TEXTDATA);
// Loop to read the stream file into variable "ifsData"
dou ifsDataLen<1;
Count += 1;
ifsDataLen=read(Handle:
%addr(ifsData):
%size(ifsData));
enddo;
// Close the stream file
rc= close(Handle);
if ifsData <> *blanks;
// we have the IFS data loaded into variable(ifsData) so lets load
// that variable into the SQL(clob) and process it using the JSON_TABLE
// to break the JSON out into fields for processing. NOTE: we are not
// doing anything with those fields in this program because this is just
// a proof of concept
exec SQL set option Naming = *Sys,
Commit = *None,
UsrPrf = *User,
DynUsrPrf = *User,
Datfmt = *iso,
CloSqlCsr = *EndMod ;
strJsonTimeStamp=%timestamp();
JSON_LEN = %len(%trim(ifsdata));
JSON_DATA = %trim(ifsData);
EXEC SQL DECLARE C1 CURSOR FOR
select *
from JSON_TABLE(:json, '$' // read the JSON data from a string
COLUMNS( NESTED '$.users[*]'
COLUMNS (USERID VARCHAR(100) PATH '$.userID',
FIRSTNAME VARCHAR(100) PATH '$.firstName',
LASTNAME VARCHAR(100) PATH '$.lastName',
INITIALS VARCHAR(100) PATH '$.initials',
COMPANY INT PATH '$.company',
DIVISION INT PATH '$.division',
DEPARTMENT INT PATH '$.department')
)) AS X;
EXEC SQL OPEN C1;
exec SQL fetch next from C1 into :jsonFields ;
dow sqlstt='00000' or %subst(sqlstt:1:2)='01';
lastelem += 1;
// Store Datastructure values in next element of 'return array'
result.jsonArray(lastelem) = jsonFields;
// here we could do something with each row of data ie: write to file
// or some other business logic.
exec SQL fetch next from C1 into :jsonFields ;
enddo;
exec SQL close C1;
result.success = *on;
else;
// If unable to load JSON data from the IFS then tell the world
result.success = *off;
result.errmsg = 'Bugger! I couldnt read the IFS file';
DSPLY %trim(result.errmsg);
endif;
// Set Ending timestamps and calculate runtime
endTimeStamp=%timestamp();
joblogMsg = 'JSNIFSSQL JSON_TABLE Completed with ' +
%char(lastelem) +
' elements. Total runtime:' +
%char(%diff(endTimeStamp:strTimeStamp:mseconds)) + '
SQL JSON_TABLE DECODE only:' +
%char(%diff(endTimeStamp:strJsonTimeStamp:mseconds));
// stick that message into the joblog so we can clearly see the runtime
QMHSNDPM( 'CPF9897'
: 'QCPFMSG LIBL'
: joblogMsg
: %len(%trimr(joblogMsg))
: 'DIAG'
: '*'
: 0
: MsgKey
: ErrorCode );
*inlr = *on;
So what does this do?
In this case :
call JSNIFSSQL
JSNIFSSQL JSON_TABLE Completed with 91 elements.
Total runtime:513000 SQL JSON_TABLE DECODE only:512000
This was fun to program but #prettydarnslow