Introduction to Db2 for z/OS JSON/SQL Support

A couple of years ago I was presented with a challenge. A customer of mine was accepting input from a web based client in the form of a JSON document. Now, the application that accepted the input simply put it into a string in a table. The actual data type was a VARCHAR. The data in the table was replicated daily to a data warehouse housed in a SQL Server database. Replication from Db2 for z/OS was handled by utilizing the IBM Log Analysis Tool, which read the Db2 Logs once per day and created LOAD utility ready files that were then loaded into a set of staging tables in Db2 for z/OS. Those staging tables were then read once per day by a batch job on the warehouse server and loaded into the SQL Server warehouse database. The challenge was that the data warehouse users wanted to see some individual data elements from the JSON documents but not the documents themselves, and they wanted to use SQL to query that data including the ability to filter on those data elements. There were no programmers available on the warehouse side of things, and none available on the mainframe side of things. So, what to do? The answer was quite simple which was was to use the Db2 for z/OS JSON/SQL support that comes as a minor add on to Db2 for z/OS versions 11 or 12. This software I am talking about is the no-charge software offering called the IBM Db2 Accessories Suite for z/OS. Once this software was enabled it took about half a day to add the desired columns to the warehouse staging table and code the JSON/SQL function calls that pulled the desired data out of the JSON documents in the VARCHAR column and put them into individual columns of the staging table. So, then after the daily LOAD of the staging tables was completed, a simple mass update statement was executed and the additional columns were populated prior to the data warehouse job pulling the data down into the SQL Server database. This was basically done in less than one day with no additional programming support needed beyond a DBA writing some SQL using the Db2 for z/OS JSON functionality. That is some powerful stuff!

Follow this link to read an introduction to JSON support in Db2 for z/OS by Dan Luksetich on the IDUG web site. The article is free, but you will need an IDUG account (which is also free).

https://www.idug.org/blogs/daniel-luksetich1/2021/06/03/introduction-to-json-capabilities-and-sql-support

Leave Comment

Your email address will not be published.