DB2 for z/OS NoSQL Support Update: REST and JSON
Daniel Luksetich, DanL Database consulting
danl@db2expert.com
The DB2 landscape is changing quickly, and sometimes things can quietly sneak in before you even realize it. Maybe you have heard IBMers talking about JSON support in DB2, but have you heard about the mobile DB2 access and DB2 RESTful API that is now available for DB2 for z/OS? I’m guessing that you probably haven’t since these features have recently eased their way into DB2. These features exist, in my opinion, for a couple of reasons. First of all the customers are asking for it. There is an insatiable need to build applications quickly and make applications more mobile. This speed and mobility has to extend to legacy data that is stored in DB2 for z/OS. IBM customers find themselves moving data around their enterprise in order to meet the high demands of their customers and stay competitive. Some are finding that moving this data is expensive and can reduce availability and data quality. Bringing DB2 for z/OS into the fold of mobility and fast application development eases the strain. The second reason is that DB2 needs to adapt and stay relevant to changing application development practices. There is a lot of competition out there, and the greatest features of the competition revolve around fast and easy deployment of applications. Who wants to develop and deploy a new app in one day? Everyone does! Can it be done? Yes, it can. DB2 has to be a part of this evolving development paradigm and these features are part of the effort to meet that challenge.
JSON Support in DB2 for z/OS
JSON, or Javascript Object Notation, exists as a streamlined messaging alternative to XML. It is far simpler and more lightweight than XML, and is quickly being adopted as the standard for web communication and transmission of data. JSON support for DB2 was introduced via a relationship with Mongo. IBM utilized Mongo technology to create a Java Driver for JSON API and a JSON wire listener. This was supported in both DB2 for z/OS and DB2 for LUW, and the way it was implemented was via a combination of built-in and IBM supplied user-defined functions. While this technology is functional in DB2 10 and 11 for z/OS it is not necessarily the preferred way to use JSON in DB2 for z/OS.
DB2 11 for z/OS introduced the SQL API for JSON. This allows developers to code SQL/JSON much in the same way as they have coded SQL/XML. This is the preferred way to work with JSON in DB2 for z/OS. JSON objects are stored in DB2 as a BLOB, and must be converted to a binary format prior to storage if full JSON functionality is desired. There are three primary functions that are utilized in SQL when working with JSON.
- JSON2BSON – this user-defined function converts a JSON text string into a binary JSON object (BSON). This function is used primarily when adding JSON data into a DB2 table.
- BSON2JSON – this user-defined function converts a BSON object into a JSON text string. Its obvious use is for when JSON data is retrieved from DB2 in an SQL statement.
- JSON_VAL – this built-in function can retrieve specific values from within a BSON object. This function is the key to retrieving only part of a JSON document, indexing a JSON document, or executing a predicate against a JSON document in the WHERE clause of the SQL statement.
Given DB2’s ability to store, index, filter, and extract JSON data in DB2 11 for z/OS there is no reason not to take advantage of this now in support of any requirements that mix JSON with legacy relational. As of right now, however, it is best for receiving JSON, storing it, and then processing it mixed with legacy relational data, as there is currently no simple way of extracting existing relational data from DB2 as JSON.
Here is an example of storing a JSON document in a DB2 table:
INSERT INTO JSONCUSTOMER VALUES (
101,
SYSTOOLS.JSON2BSON(
'{"Customer":{"@cid": 999,
"name": "Michael",
"age": 31,
"telephone": "234-343-2343",
"country": "USA"
}
}'));
Retrieving an entire JSON document:
SELECT SYSTOOLS.BSON2JSON(DATA)
FROM JSONCUSTOMER
WHERE ID = 101;
Extracting JSON data:
SELECT JSON_VAL(DATA, 'Customer.name', 's:40')
FROM JSONCUSTOMER
WHERE JSON_VAL(DATA, 'Customer.@cid', 'i:na')=999
Indexing JSON in support of fast SQL/JSON query access:
CREATE INDEX IX1 ON JSONCUSTOMER (
JSON_VAL(DATA, 'Customer.@cid','i:na'));
There is also functionality that allows JSON to be used as parameters in stored procedures, as transition variables in triggers, and in views. There are also IBM supplied user-defined functions that provide for the parsing and processing of JSON elements, and the SYSTOOLS.JSON_TABLE function that allows for a access to elements within JSON documents and for JSON documents to be converted into rows of a table using the SYSTOOLS.JSON_TABLE supplied user-defined function much in the way that it’s done for XML using the XMLTABLE function.
Given that JSON is quickly becoming the internet standard for data transmission I believe it is safe to imagine an expanding role for JSON in DB2 for z/OS.
DB2 Adapter for z/OS Connect
z/OS Connect is an IBM software product that enables z/OS systems to function as a service to external calls. This enables for fast application development of mobile and cloud service calls that access legacy enterprise data and functionality that is stored on z/OS servers. On October 5th, 2015 IBM quietly announced the release of the IBM DB2 Accessory Suite for z/OS v3.3. Included in this release is the IBM DB2 Adapter for z/OS connect. What this means is that developers can very quickly develop and deploy web services in DB2 using RESTful API calls. REST, or Representational State Transfer, is a software architectural style that is an extremely common method by which web applications receive and transmit data. Have you ever filled out a form on a web page to send an email, purchase a product, or add your information to a service? There is a really good chance that submitting that form invoked a RESTful service to process the request.
With the addition of the DB2 Adapter for z/OS Connect we can now make RESTful calls to DB2 for z/OS. This makes any DB2 for z/OS implementation available for internal or external cloud-like service calls. Once the infrastructure is set up (z/OS Connect, the DB2 Adapter, security, etc.) development is quite simple in that an SQL query and/or stored procedure can be developed in IBM Data Studio, that “call” can then be encapsulated within a web service developed in IBM Data Studio (can you say “drag and drop”?), and then that web service deployed to z/OS connect. At that point the SQL statement or stored procedure then exists as a service call available to any application that cares to call it. Data transmissions to and from the service are in the form of JSON objects. Only a single call (SQL statement or stored procedure) can be made within a service call and so getting the most done in a single call will be best for performance. This is no longer a dream for legacy access, but reality.
IBM MobileFirst
You are definitely in the minority if you do not own a smartphone, and if you’re a little bit of a savvy smartphone user then you’re probably taking care of a lot of your personal business using your smart phone. Banking, car rentals, airline tickets, dinner reservations, sports scores, local transportation, and communication are just the tip of the iceberg when it comes to mobile computing.
In the late 90’s it was cool to say you had a web site. Today you aren’t cool unless you have an app! Developing mobile apps is not that difficult, and you can literally go from saying “hey, I’d like to create a mobile app” to actually testing your very first mobile app in a matter of a few hours. No kidding! Now, creating a mobile app that accesses your legacy DB2 for z/OS data may present more of a challenge, but IBM does provide a solution for that. IBM MobileFirst is an end to end architecture that allows for the ultra-fast development and operation of mobile applications that access, among other resources, DB2 for z/OS data. MobileFirst consists of a development studio, device runtime, and server. The server runs under z/Linux, the development studio plugs into Eclipse, and the device runtime allows for a common set of code to run under a wide variety of mobile devices and operating systems.
Somewhat similar to the services that can be built using IBM Data Studio, SQL statements and stored procedure calls can be built into SQL adaptors stored on the server side and functionality built into the mobile app to invoke those adaptors on a variety of mobile devices. A console and mobile device emulators are built in that allow a developer quick development and testing across all device types. You can literally develop, test, and deploy in a short time using a platform (Eclipse) to which many developers are accustomed.
Creating services in DB2
Creating services in DB2 for both web and mobile can be performed quickly and efficiently, giving immediate access to legacy DB2, as well as other z/OS resources. This truly makes DB2 for z/OS available as a service.
We do then, from a performance perspective, need to view DB2 as a service in order to develop efficient service calls within DB2. This means we should take advantage of DB2 functionality to minimize the number of service calls required to satisfy an application request. Specifically speaking, DB2 features such as database enforced referential integrity, triggers, user-defined functions, and especially stored procedures should be used to get the most database tasks accomplished in a minimal number of calls. In addition, automated features such as sequences, row change timestamps, temporal tables, XML and JSON can all be used to increase the functionality within a single call. Remember that a service call can only contain a single SQL statement or call to a stored procedure, and so you want to get the biggest bang for the buck out of that one call.
The Changing Roles for Administration and Development
With the expansion of functionality and availability of DB2 as a service comes some additional complexity as related to the roles of individuals or teams within an enterprise. While DBA’s traditionally managed data within a database and developers wrote programs that accessed that data, now developers can implement and manage service objects within the database. This means that the line between the DBA and application developer is more blurred than ever. This is especially true when developers create stored procedures as part of a service call, or take advantage of automation such as user-defined functions and triggers. These are application objects that are installed into a DB2 database. Who is managing these objects, controlling security, and taking the phone call at 3AM when an object is unexpectedly unavailable? These responsibilities will have to be well defined in order for efficient deployment and management of services within DB2. “Well that’s not the way we do things around here” does not cut it anymore, and every organization will have to accommodate this development and deployment paradigm.
I generally recommend the formation of a development team that takes the responsibility of the creation and deployment of database service objects. This involves working between the application development and DBA teams to create service calls, as well as stored procedures and other automated database objects to implement efficient service calls. This team would also be responsible for the documentation and promotion of these database service calls to application teams within the enterprise.
Summary
DB2 for z/OS as the data store for cloud based computing is here today. It’s possible to communicate with DB2 using service calls and passing JSON documents, as well as working with JSON inside of DB2 via SQL. This really opens up legacy DB2 data to fast application development in web and mobile applications, and avoids expensive movement of data and massive development projects. Most, if not all, of these features are free (ask your favorite IBM rep) and once configured can enable lightning fast development. This article simple exposes the possibilities that exist with this technology. To explore further please go here for information on the DB2 Adapter for z/OS. Although the adapter is part of the IBM DB2 Accessories Suite for z/OS, V3.3, which includes IBM DB2 Accessories Suite DB2 11 it will work with DB2 10 for z/OS. The IBM DB2 Accessories Suite for z/OS, V3.3 is available on December 11, 2015. IBM MobileFirst is available now, and you can download the MobileFirst plug-in for Eclipse right now at the Eclipse Marketplace! Please go here for more information on MobileFirst.