• 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
    WorksRight Software

    Do you need area code information?
    Do you need ZIP Code information?
    Do you need ZIP+4 information?
    Do you need city name information?
    Do you need county information?
    Do you need a nearest dealer locator system?

    We can HELP! We have affordable AS/400 software and data to do all of the above. Whether you need a simple city name retrieval system or a sophisticated CASS postal coding system, we have it for you!

    The ZIP/CITY system is based on 5-digit ZIP Codes. You can retrieve city names, state names, county names, area codes, time zones, latitude, longitude, and more just by knowing the ZIP Code. We supply information on all the latest area code changes. A nearest dealer locator function is also included. ZIP/CITY includes software, data, monthly updates, and unlimited support. The cost is $495 per year.

    PER/ZIP4 is a sophisticated CASS certified postal coding system for assigning ZIP Codes, ZIP+4, carrier route, and delivery point codes. PER/ZIP4 also provides county names and FIPS codes. PER/ZIP4 can be used interactively, in batch, and with callable programs. PER/ZIP4 includes software, data, monthly updates, and unlimited support. The cost is $3,900 for the first year, and $1,950 for renewal.

    Just call us and we’ll arrange for 30 days FREE use of either ZIP/CITY or PER/ZIP4.

    WorksRight Software, Inc.
    Phone: 601-856-8337
    Fax: 601-856-9432
    Email: software@worksright.com
    Website: www.worksright.com

    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

  • Why Open Source Is Critical for Digital Transformation
  • mrc Refreshes IBM i Low-Code Dev Tool
  • Unit Testing Automation Hits Shift Left Instead of Ctrl-Alt-Delete Cash
  • Four Hundred Monitor, March 3
  • IBM i PTF Guide, Volume 23, Number 9
  • Doing The Texas Two Step From Power9 To Power10
  • PHP’s Legacy Problem
  • Guru: For IBM i Newcomers, An Access Client Solutions Primer
  • IBM i 7.1 Extended Out To 2024 And Up To The IBM Cloud
  • Some Practical Advice On That HMC-Power9 Impedance Mismatch

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 © 2021 IT Jungle

loading Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.