• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • 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

    The Data-Centric Depiction Of IBM i

    IBM i Tech Refresh Reiterates Database Emphasis

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags: Tags: IBM i, SQL

    Sponsored by
    DRV Tech

    Get More Out of Your IBM i

    With soaring costs, operational data is more critical than ever. IBM shops need faster, easier ways to distribute IBM applications-based data to users more efficiently, no matter where they are.

    The Problem:

    For Users, IBM Data Can Be Difficult to Get To

    IBM Applications generate reports as spooled files, originally designed to be printed. Often those reports are packed together with so much data it makes them difficult to read. Add to that hardcopy is a pain to distribute. User-friendly formats like Excel and PDF are better, offering sorting, searching, and easy portability but getting IBM reports into these formats can be tricky without the right tools.

    The Solution:

    IBM i Reports can easily be converted to easy to read and share formats like Excel and PDF and Delivered by Email

    Converting IBM i, iSeries, and AS400 reports into Excel and PDF is now a lot easier with SpoolFlex software by DRV Tech.  If you or your users are still doing this manually, think how much time is wasted dragging and reformatting to make a report readable. How much time would be saved if they were automatically formatted correctly and delivered to one or multiple recipients.

    SpoolFlex converts spooled files to Excel and PDF, automatically emailing them, and saving copies to network shared folders. SpoolFlex converts complex reports to Excel, removing unwanted headers, splitting large reports out for individual recipients, and delivering to users whether they are at the office or working from home.

    Watch our 2-minute video and see DRV’s powerful SpoolFlex software can solve your file conversion challenges.

    Watch Video

    DRV Tech

    www.drvtech.com

    866.378.3366

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Guru: Parameter Passing Fundamentals Of Programs Versus Procedures IBM Gives The Midrange A Valentine’s Day (Processor) Card

    Leave a Reply Cancel reply

TFH Volume: 27 Issue: 10

This Issue Sponsored By

  • Profound Logic Software
  • Maxava
  • WorksRight Software
  • Linoma Software
  • Northeast User Groups Conference

Table of Contents

  • IBM Gives The Midrange A Valentine’s Day (Processor) Card
  • SQL And Database Shine As Next Tech Refresh Approaches
  • Guru: Parameter Passing Fundamentals Of Programs Versus Procedures
  • What’s New In Access Client Solutions
  • Meet Watson, Rommety, And The New IBM

Content archive

  • The Four Hundred
  • Four Hundred Stuff
  • Four Hundred Guru

Recent Posts

  • Meet The Next Gen Of IBMers Helping To Build IBM i
  • Looks Like IBM Is Building A Linux-Like PASE For IBM i After All
  • Will Independent IBM i Clouds Survive PowerVS?
  • Now, IBM Is Jacking Up Hardware Maintenance Prices
  • IBM i PTF Guide, Volume 27, Number 24
  • Big Blue Raises IBM i License Transfer Fees, Other Prices
  • Keep The IBM i Youth Movement Going With More Training, Better Tools
  • Remain Begins Migrating DevOps Tools To VS Code
  • IBM Readies LTO-10 Tape Drives And Libraries
  • IBM i PTF Guide, Volume 27, Number 23

Subscribe

To get news from IT Jungle sent to your inbox every week, subscribe to our newsletter.

Pages

  • About Us
  • Contact
  • Contributors
  • Four Hundred Monitor
  • IBM i PTF Guide
  • Media Kit
  • Subscribe

Search

Copyright © 2025 IT Jungle