JSON for Relational Data in DB2 for z/OS?!
Last year I wrote an article on JSON and the impact for DB2 DBAs for the IDUG Content Library (see http://www.idug.org/p/do/sd/sid=6779&type=0). In this article I advocated the advent of JSON views on relational data.
In my article I referred to options with
JSONx, but the technology appears to be outdated and new functions have
risen since its publication. In a developerWorks article of Jane Man
and Jae Lee (http://www.ibm.com/developerworks/data/library/techarticle/dm-1501sql-json-db2/index.html), the JSON SQL interfaces for DB2 11 are described.
These
new functions will allow you to to create a poor man's JSON replication
of your relational data. I name it poor man's, as it will obviously not
permit you to do realtime projections of your relational data, but will
allow incremental refreshes of JSON documents using SQL INSERT, UPDATE,
DELETE statements.
As JSON is a simple hierarchical structure, it can easily be mapped with the more complex relational structures on the one hand and the object structure of an object-oriented language on the other hand. I saw in this an opportunity to get rid of Object Relational Mapping software that often result in heavily consuming SQL.
Instead of leaving the data mapping to the application programmer, it could be the data architect that could identify the data structure in the database and provide the mapping. With data architect, I refer to a person with knowledge of the business, the functional processes and the data structure. This role could build a relational model and map it adequately to an hierarchical model.
As a developer is not supposed to know relational theory in depth, the data architect could make use of his specialization and apply it to the database he is specialized in. For us this would be DB2 for z/OS.
- First of all, the notion of JSON schemas should appear. MongoDB has a methodology to enforce mandatory structures upon JSON data (you cannot control the non-mandatory in JSON as you can in XML), this is what is known as JSON schemas. In DB2 this has not been available insofar.
These schemas could allow one to create JSON projections of relational data in DB2 for z/OS as you can with XML projections in SQL Server (https://technet.microsoft.com/en-us/library/aa258637%28v=sql.80%29.aspx). JSON schemas should only be applied to those JSON documents that relate to relational data in order to guarantee performance. - Next, these JSON projections should be accompanied with concurrency checks, they shouldn't provide unlimited update/insert/delete options, as the underlying data remains relational. As such, the JSON projections would only hold versions of a record if they relate to temporal tables or tables with a history table involved.
- Finally, the JSON schemas should be immediately discoverable by any JDBC driver compatible with JSON, thus that the build of an object model mapping is no longer required, as a usable hierarchical structure is already available from within the database.
The translation of relational data to
JSON would also allow imports of this data into Big Data systems like
Hadoop or other JSON stores without the need of direct connectivity. From this perspective DB2, and especially DB2 for z/OS, could play the role as a central data repository that could push enterprise data
to applications running in the outer domains of the secure network,
without the need to open the mainframe for internet access and risking
hacks on your most valuable information.
If IBM would consider these changes, the DBA community could continue to leverage its knowledge and expand it to the application development, even in the cloud. This vision would suit within the spirit of DevOps where the DBA would become one of the project team members closely working with the business and functional analysts.
Next to DB2, there are other interfaces that you could use to address relational data and JSON. Have a look at BigSQL, stinger.next, ... There is a lot of interesting material, but I'll keep this for another blog :-)
Best regards,
Ludovic
