IBM Adds Support For Publishing JSON In DB2
October 4, 2017 Alex Woodie
At long last, IBM i shops will be able to publish JavaScript Object Notation (JSON) documents from their Db2 for i database. That much-desired capability will finally arrive as part of the latest Technology Refreshes (TRs) for IBM i versions 7.2 and 7.3, which were announced yesterday and ship this month. New SQL commands and security log enhancements round out the release.
The capability to publish JSON documents has been a long time coming for IBM i shops, who until now have had to content themselves with the capability to consume and store JSON within their database, but who lacked the formal IBM-sanctioned capability to generate or publish the popular data type directly from their workhorse database.
IBM got the JSON ball rolling in June 2015, when it launched the JSON store for DB2 for i, which IBM recently, for some reason, decided to rename Db2 for i (note the lowercase “b”.) It fleshed out the framework a year ago when it added support for JSON tables, which enabled JSON documents to be deconstructed.
The JSON story improved substantially earlier this year, when IBM added support for JSON predicates. That essentially created a layer that enabled applications to treat JSON like relational data.
But the keystone in the JSON support in Db2 for i – what many shops have been waiting for — is support for publishing JSON directly from the database. IBM finally filled that hole with the forthcoming releases of IBM i 7.2 TR7 and IBM i 7.3. TR3, and the database updates that accompany them.
Specifically, IBM added four database commands related to JSON publishing, including JSON_ARRAYAGG, JSON_OBJECTAGG, JSON_ARRAY, and JSON_OBJECT. IBM also added two scalar functions to its JSON kit, including JSON_VALUE and JSON_QUERY. You’ll be able to read the gory details about these commands on the IBM i Technology Updates pages on the developerWorks site when they finally go live.
Scott Forstie, the Db2 for i business architect and SQL development leader at the IBM lab in Rochester, Minnesota, says the new features completes Db2 for i’s SQL support for JSON.
“We are adding the last elements of JSON support into the SQL language,” he tells IT Jungle. “You will see JSON scalar functions, but the most interesting additions are the JSON publishing functions. With these enhancements, we have completed nearly all of the support being added to the SQL Standard.”
IBM has added JSON capabilities to the Db2 for i database gradually over the years.
While many Db2 for i clients are already heavily using JSON, they’ll be able to take their use of the language further with the latest TRs, he says. “With these enhancements, they can use standards-based technologies where it’s possible to get to end-of-job in a single query,” Forstie says. “The new enhancements will use portable skills to produce web services data more efficiently.”
Other new arrivals in the database include new SQL commands for accessing IBM i operating system details. For example, the new ASP_INFO view returns information about auxiliary storage pools (ASPs). The ASP_VARY_INFO view, meanwhile, gives administrators the capability to use the use DB2 for i’s SQL Query Engine (SQE) to compare and contrast performance over time. Lastly, the JOB_QUEUE_INFO view returns one row for each job message queue.
The SQE also gets new features to make it easier to enable stateless pagination, which is an important capability for developers who are aiming to deliver stateless sessions in their IBM i-based Web and mobile applications. The capabilities are delivered via OFFSET and LIMIT commands.
The new OFFSET feature “lets the database manager know that the application does not want to start retrieving rows until offset-row-count rows have been skipped,” according to the IBM developerWorks story on the feature. Similarly, the new LIMIT feature “lets the database manager know that the application does not want to retrieve more than fetch-first-row-count rows, regardless of how many rows there are in the intermediate result table,” it says in developerWorks. You can access more detailed information in the developerWorks page for Db2 for i enhancements.
Lastly, the security story gets better in the database thanks to new logs aimed at giving auditors a steady stream of security access data delivered in a standards-based manner. That includes the new HISTORY_LOG_INFO table function that generates IBM i history log details in Syslog format. It also includes a new feature in the DISPLAY_JOURNAL table function that gives the auditor the option of returning a subset of IBM i audit journal entry types in Syslog.
The database features will be included in Db2 PTF Group SF99703, which IBM says is scheduled to be released on October 27.
You can view a PDF copy of the IBM announcement letter for IBM i 7.2 TR7 here and the announcement letter for IBM i 7.3 TR3 here.
RELATED STORIES
IBM i Tech Refresh Arrives; JSON And Perl In Spotlight
This is good, that IBM has added Support for JSON. JSON is everywhere in Web, Mobile, Desktop apps. JSON is shadowing XML, I would suggest a tool for JSON which help to format and validate JSON data , https://jsonformatter.org