Tuesday, October 11, 2016

Are You Falling Behind?

Increasingly, we are finding that IBM i installations are falling behind. In many cases, perilously so...

Recently, we engaged a client who only applies PTFs when there is an actual problem encountered. That means, almost never. In other words, no proactive or preventive maintenance. No periodic updates or upgrades. And yes, they do have several planned maintenance windows available per year.

Another set of clients have just migrated to IBM i 7.1. That, after waiting several years to finally get "current". Unfortunately, they are miles away from current.

More and more we see IBM i shops who are in some kind of analysis paralysis as they attempt to make a decision to stay with IBM i or move off to some "other" platform. All the while their business is changing, expanding and branching out in ways that require a non-linear approach to information management and data processing. My experience shows that virtually all of these shops have business requirements that can be met very nicely using current technology available via the combination of Power+IBM i+DB2. Technology that is readily available to them, I might add.

Presently, the IBM i user community enjoys significant stability, as well as tremendous flexibility when it comes to OS and RDBMS features and functions. Not only are there three OS releases available and supported (7.1, 7.2 and 7.3), there are also periodic enhancements available in the form of a technology refresh or update. While a given update is typically delivered as a robust set of PTFs, you certainly should not view a Technology Refresh (TR) as a simple set of "fixes".

Hey Look, Another Technology Refresh!

Today, IBM is announcing yet another technology refresh, specifically 7.3 TR1 and 7.2 TR5.

Alas, if you are using 7.1, you are falling behind. There is no TR available for the n-2 release.

The general availability for the 7.3 and 7.2 updates should be in about a month, so you have time to begin your testing and installation planning process.

From the database point of view, what are some of the items we can expect from this TR?

As usual, the focus is on increasing capabilities around data centric processing and enhancing database engineer productively...

A new table function which works very much like XML_TABLE, allowing for JavaScript Object Notation data to be returned as rows and columns.

Provides a way to include (pull source code into) another SQL procedure/function.

PERCENT_RANK as an ordered OLAP specification (7.3 only)
A distribution function that returns a relative percentile rank of a row within an OLAP window.

Additional Services (via SQL)

Retrieve data from QHST

Retrieve a list of jobs by user(s)

Analyze an SQL statement and extract information about the DB object(s),  column(s) and UDF(s)

Misc. Performance Improvements

LPAD/RPAD functions
UDF in-lining (more complex UDFs can now be inline)

IBM Access Client Solutions - Database GUI
Enhancements to SQL Performance Center, Visual Explain and Run SQL Scripts


For more details on the new DB2 for i Technology Refresh, the illustrious Mr. Scott Forstie has what you need here.

For more information on all various TRs, go here.


What Should You Do Now?

Regardless of any future and far off "migration plans", get current with PTFs and TRs. That includes HIPER, Group and the latest Technology Refresh for your particular release, even 7.1.

While the various IT "leaders" are off trying to identify your future platform, you can get on with solving current business problems and providing immediate value with IBM i.

To facilitate keeping current (and relevant), stand up a DB2 for i sandbox. This can be a small but balanced LPAR or stand alone system running 7.3 plus TR1.

Take some dedicated time every week to discover, experiment, and try new database features and functions.  Show off your work to the higher ups.  Be a hero.

Please secure the personal identifiable information (PII) stored in your files and tables.

Start by asking, and answering the following question:  

Who has *ALLOBJ authority on any system where PII is stored?

After you get over the shock and awe of finding out just how exposed you are, embark on the serious journey of securing the at risk data. Begin with the correct application of IBM i object based security, then continue with separating roles and responsibilities (the security officer and the database engineer should have different levels of authority based on their respective responsibilities).

Take a good look at using DB2 Row and Column Access Control. It will allow you to apply a more granular level of security on the sensitive data. DB2 RCAC is available in 7.2 and 7.3, so yet another reason to get current.

Identify, train, and support an IBM i database engineer. Ensure this person has not only the responsibility, but also the authority to do the job.

The database engineer is the font of knowledge and skill around DB2 for i features, functions and benefits. The database engineer will not only keep your organization current, but also be the trusted advisor for when and how DB2 technology can be applied to solving data centric business problems!

Start using modern tools and modern techniques immediately.

Go get IBM Data Studio and learn to use it.

Go get Access Client Solutions and try it out.

And finally, do more with your data, right now.

Go get DB2 Web Query, create some compelling visualizations and demonstrate the value to your business leaders.  You can use that IBM i 7.3 sandbox to check out the advanced aggregation functions and OLAP specifications. Again, be a hero.

If you need more information or assistance with DB2 for i features and/or data centric programming technique, please do not hesitate to reach out.


  1. As usual, great insight and advice, Mike. Thanks. I especially applaud the idea of the 7.3 sandbox and taking time out to discover what i (and DB2 for i) can do. And then bragging about it. We're often better at doing cool things than we are showing off the cool things we did.

  2. Wow. I think my prayers have been answered with the SQL PARSE_STATEMENT feature coming. With that I can build a database that tells me virtually anything I need to know about all my SQL, making code maintenance much more efficient and thorough.

    I'm a HUGE consumer of the SQL interfaces that the DB2 for i team is cranking out. Your team is awesome!

    Thank you very much!

  3. I believe that much of the problem with people not getting current is two fold. First, there is not the same incentive that there is in other environments that have a continual string of problems that require upgrades. Second, a lot of IBM i shops are being run by Server People, people whose whole experience is in that realm. They don't know what to do with the i, they don't feel they need to bother to learn, and it atrophies.

  4. You're very welcome.
    Thank you for the feedback Mike.
    Scott Forstie

  5. Great post! Many thanks to you, Scott and the entire DB2 for i team - keep up the innovation and communication.

    On the topic of sandbox systems, there are several publicly-available options (some fee, some for-fee). The #IBMiOSS community held a CrowdCast back in August - https://www.crowdcast.io/e/IBMiOSS-16Aug2016. Here's the agenda (with links): https://paper.dropbox.com/doc/Agenda-4eHbCdr6IkFwEdX95CG0C

    I've used many of these sandbox systems to quickly and easily experiment with the new DB2 for i enhancements over the years. I'm eagerly awaiting the upcoming TRs for 7.3 and 7.2 - especially the JSON_TABLE() function and a deeper dive into the native noSQL options available to us (conveniently integrated into our platform).