SQL And Database Shine As Next Tech Refresh Approaches
February 20, 2017 Dan Burger
Twelve inches of fresh snow has piled up on your driveway and all you have is a shovel. That’s minimal functionality and, depending on your fitness level, your snow removal performance may be less than desirable. At best, you’re on the low end of the functionality and performance continuum and you’re wishing for better.
So it is with the DB2 for i database and the programmers who shovel data there. Any help moving data from where it is to where it’s needed will likely be appreciated. Ditto for accelerating application behavior and gaining application functionality.
This brings us to the mid-March availability of technology refreshes for those of you running IBM i 7.2 and 7.3. If you are already tuned in to SQL and database efficiency, there’s more functionality and performance enhancements on the horizon. If your database is pre-SQL, you’re stuck with your data shovel.
The benefits of SQL programming and a modern DB2 for i database are always on display in the Technology Refresh program, which makes it next appearance March 17.
This time around it’s additional support for JSON in the SQL language that leads the list. JSON support is not new. It’s a continuation of several phases of JSON support that began almost two years ago.
The first step in JSON support (for i 7.1, 7.2 and 7.3) began with DB2 JSON Store.
“When we added DB2 JSON Store in 7.1, the primary business value was that JSON documents could be treated as business-critical entities to the extent that they could be stored and retrieved in a DB2 for i table for disaster recovery and high availability purposes,” explained Scott Forstie, IBM’s DB2 for i business architect.
In 2016, JSON TABLE was added with support for 7.2 and 7.3 only. It allowed JSON documents to be deconstructed.
In the March 2017 TRs, two JSON QUERY Predicates will be added. Query Predicates allow programmers to add a new level of business rules that can assure the JSON docs are well formed.
“When JSON TABLE and the JSON Query Predicates were added, it allowed JSON applications to be deployed to the database, which meant the application layer could treat JSON like relational data,” Forstie said during an interview with IT Jungle last week.
“The important thing to think about is: Where are JSON documents in your IT strategy? Are you producing or consuming feeds?
“I like to point out that you already have the ability with DB2 for i to query the internet using HTTP functions. (Not a widely recognized fact.) That gives organizations new possibilities to include things like JSON feeds. Which ones have unique value is determined by each shop,” Forstie said.
For all the JSON functions built into DB2 for i, there remains a missing element. Publishing JSON documents containing relational data is still on the development to-do list.
Forstie points out that the SQL standards body is embracing JSON, but has yet to embrace a publishing standard. Look for this to be accomplished soon, however, and watch for the capability to be added to DB2 for i, maybe as soon as this fall.
The technology added through the twice-a-year Technology Refreshes, continues to lower the cost of filling gaps that SQL users encounter. Those gaps are either avoided or developers find workarounds that are labor intensive, costly, or slow. In some cases, shops don’t recognize they have the tools (already purchased as part of DB2 for i, but unknown) to solve bottlenecks.
For example, LISTAGG is a new built-in database function that becomes available next month.
Developers can point it to a column within a database table and it will create an aggregated list with the developer’s choice of separators and ordering. The obvious use case is names, but that’s not where the use begins and ends.
“People had found ways to do this in the past, but when they see LISTAGG they are going to run to it,” Forstie said. “We’ve taken what was essentially a subprocedure and made it part of the SQL statement.”
Upgrades to the IBM i services from DB2 on i have also been added in the upcoming TR. The list of services includes:
- MESSAGE_QUEUE_INFO
- OBJECT_PRIVILEGES
- AUTHORIZATION_LIST_INFO
- AUTHORIZATION_LIST_USER_INFO
- SET_PASE_SHELL_INFO()
- USER_INFO
- LICENSE_INFO
- LICENSE_EXPIRATION_CHECK()
- RESET_TABLE_INDEX_STATISTICS()
Advancements in the capability to manage security using SQL stand out in this list of services. We’ve seen security concerns rise in surveys of IBM i shops, where the state of security is much worse than most shops would predict.
Look for Forstie’s new educational session that he’ll be presenting at tech conferences and user group meetings in 2017. It’s called “SQL for the Security Administrator,” and it includes examples of how to leverage services to gain insights into security configurations.
The IBM developerWorks site provides additional details on the DB2 for i enhancements included in the IBM i 7.2 TR6 and 7.3 TR2 technology updates.
RELATED STORIES
Why You Should Hire An IBM i Database Engineer
Knocking On The Old Database Door
No Seats At Cruikshank’s SQL Database Sessions
Analytical Expectations And Misconceptions Of IBM i
IBM i Execs Put Database On The Map
