Friday, November 13, 2020

Db2 for i Comings & Goings

 As you might have guessed from the quietness of this blog, Mike Cain, my good friend and long-time Db2 partner in crime retired from IBM last year and is happily enjoying retirement.  

And if you haven’t heard yet, I returned to the IBM i world this Fall after a 5-year stint working on healthcare solutions powered by IBM Watson technologies. I’m excited to be back working work with the Db2 team in the IBM Lab Services Power Systems Delivery Practice where I get to help clients get the most out of Db2 and SQL on IBM i.  With my return, I’ll be using this blog to raise awareness about all things related to Db2 for i. 

Speaking of new things in Db2 land, today there are new IBM i technology refreshes available for the 7.3 and 7.4 releases that as always include numerous Db2 enhancements.  My favorite is the new IF EXISTS clause for DROP statements which can be used to greatly simplify your SQL database creation scripts – you can learn about how to use this feature in my article.  A complete list of all the Db2 and IBM i enhancements in the latest technology refresh can be found here.   

It's good to be back!

Friday, April 13, 2018

Data: The New Currency

In this month's issue of the IBM Systems Magazine - POWER edition, author Neil Tardy shares some observations and insights on the importance of realizing maximum value from data through collaboration.

If you have not already read the piece, you can check it out here.

I highly recommend you share it with your business leaders and executives, taking advantage of the opportunity to discuss and reevaluate your collection and use of data.

Please reach out to me if you need assistance with the conversation, and as you persevere to do more, and do better.

Monday, December 4, 2017

Making the Case for a Database Engineer - Again

Recently, I collaborated with IBM Systems Magazine author Neil Tardy to reiterate the importance, and the value of standing up an IBM i database engineer. The end result of this collaboration is a simple but profound article that once again, makes the case for the Db2 for i DBE.

Take a few minutes to read Neil's article, and then share it.  Use it at as a good excuse to call on your managers and executives.  Deliver a message.

If you need help in communicating the message, or convincing them of the urgency, please reach out, I am more than happy to help illuminate this important topic.

Mr. Tardy's article can be found here, in the December 2017 issue of IBM Systems Magazine.

Tuesday, October 3, 2017

Interesting Db2 items in the latest TR!

Today, IBM announces the latest IBM i 7.2 and IBM i 7.3 Technology Refresh (TR). 

The availability of the respective TRs via group PTF is scheduled for October 27, 2017.

More information on the IBM i TRs can be found here.

More detailed information on the Db2 for i TRs can be found here.

Looking through the database enhancements, we find some very interesting items to consider...

Db2 Web Query extends the community of users to the true data analyst and/or data scientist. New powerful data discovery capabilities make it even easier to navigate through data in pursuit of  understanding and actionable information.  More information can be found here.

Db2 for i JSON support extends database capability via new publishing functions:


Joining XML, JSON is fast becoming the go to mechanism for storing and sharing unstructured data. Composing a JSON document from the structured, relational (row and column) data via the integrated JSON functions represents expanded "data centric" development capabilities in Db2 for i.

SQL DML LIMIT and OFFSET enhancements (Hi Jon Paris!) including the ability to use them in both DELETE and UPDATE statements.

IBM i Access Client Solutions enhancements to database support. ACS is fast becoming the main tool for the IBM i database engineer.  Make it part of your toolkit today!

Alerts when approaching database limits.  IBM i will produce alerts for a subset of System Limits tracking. Once per day, the operating systems will look for new occurrences where consumption of some limits exceeded 90% of the maximum allowable size. For these instances of extremely high consumption, messages will be sent to the QSYSOPR system operator message queue. Limit alerting is available for:

    Maximum number of all rows in a table (partition)
    Maximum index size
    Maximum encoded vector index size
    Maximum number of variable-length segments

Trust me, when one hits a database limit unexpectedly, the fun stops. Allowing IBM i to provide an alert to the database engineer is the first step in avoiding a painful issue.  By the way, all of the Db2 for i limits can be found in the SQL Reference, appendix A.


If you need assistance with getting more value out of your data residing in IBM i, or determining how best to make use of these, or any Db2 features and functions, please reach out - we are here to help.

Note: if you are currently running IBM i 7.1, these new features and functions are not available to you.  Please upgrade your operating system as soon as possible to take advantage of all that Db2 for i can offer!

Monday, August 14, 2017

Are You Open to Open Source Databases?

During the last 5 years, the awareness, acceptance and embrace of open source database systems has exploded. Everyone, including IBM is firmly seated on the bandwagon. For the last 12 months, I have been illuminating the concepts of open source (non relational) database systems during the Db2 for i Technical Forums. We also continue to review the fundamentals of relational database, lest we forget.

So, as a Db2 for i user, are you open to open source database?

If not, should you be?

Obviously the answer is: it depends!

Before we get into exactly what it might depend on, I highly recommend you take a few minutes to go off and read an introduction to open source databases by my friend and colleague, Rick Murphy.

Rick's article, "All you really need to know about open source databases" can be found here.

Welcome back!

If you're like me, you found Rick's overview interesting and insightful. We can also use it as a starting point to answer the question posed above.

When considering the adoption of any component or solution, we must first understand our business requirements and the technical requirements. These requirements must be reconciled and prioritized. Then and only then can we effectively consider components and/or solutions that might meet our requirements, thus avoiding buyer's remorse.

Now the question has transformed into: do you have business and technical requirements that can be met by an open source database?

And more specifically, what flavor of open source database:

Non relational?
GPU accelerated?

A related and possibly more profound question is:

Can your current Db2 relational database management system, the one you already own, meet your requirements?  

hmm... if there is head scratching and shrugging of shoulders happening about now, it's time to learn more about what the current version of Db2 for i can do for you.

Revitalization vs. Modernization

More than a decade ago, we were introducing the idea of "modernizing" your database. Our goal was simply to get you to do more with Db2 by embracing true relational database constructs through proper modeling and design, move from application-centric programming to data-centric programming, make use of SQL as your language for interacting with Db2, migrate from record-at-a-time processing to set-at-a-time processing.

What was once called database modernization, we now refer to as database "revitalization". This term more accurately reflects what needs to be accomplished. Db2 for i is, and always has been, a "modern" relational database management system. That fact that you are not using it that way should in no way diminish the capability, nor tarnish the value, of the Db2 relational database management system you have in hand today.

Extending and expanding your database and data-centric programming capabilities is called database modernization.  Meaning, a modern database environment is comprised of many different data storage and data processing technologies that are fit for purpose and meet requirements. This can be referred to as a Polyglot Persistence Environment. This is a formal way of saying: store, process and access your data using the database system that best meets your needs, and using more than one is expected. In more practical terms, why try to store and process vast quantities of unstructured and unrelated data in a structured, relational database. It's better to use an unstructured, non relational database system instead.

What does your future look like?

It will definitely include the continued use of Db2 for relational data!

My prediction is that it will also include handling unstructured data using non relational, open source database system(s), on premise and in the clouds.  And that means, making use of an operating system other than IBM i. 

Let's be clear, if you are going to acquire, implement and make productive use of an open source database, you will use an open source operating system. The good news is, IBM Power systems run Linux, AND Power runs open source databases very well indeed.

Another important aspect... you are going to step up your database engineering. Embracing various and sundry forms of data storage, data processing and data access will require a lot more database science and art. If nothing else, you will need to sort out the database designs and architectures that will best meet your requirements, and guide the users toward those sources.

A Summary

And remember, modernization and revitalization is a process, not a product, and not a tool.

Modernization and revitalization involve not only applications and databases, but also:

  • Organization Structure and Alignment
  • Organization Leadership
  • Adopting and embracing new practices and new disciplines
  • Multi-dimensional communication

If you want to learn more about revitalizing and/or modernizing your database environment (including the people part of the equation), please let me know.  We are here to guide you, and assist with the journey!

Thursday, March 16, 2017

Using SAP on IBM i?

Are you running SAP on IBM i?

If so, seriously consider attending the upcoming IBM - SAP on Power Summit event to be held in Rochester, Minnesota USA on April 26 - 27.

More details about the event can be found here.

Late April is a great time to visit SE Minnesota.  The snow should be melted by then!

Friday, February 17, 2017

A Few More Tools

In case you missed it, you now have a few more tools for your "hero bag".

Earlier this week, IBM announced the IBM i 7.3 TR2 and IBM i 7.2 TR6 with a planned availability date of March 17, 2017.  If you are still running on IBM i 7.1, nothing new for your kit.  Please get on with upgrading to 7.3. Even Superman needed a cape to be a hero...  just saying

As mentioned in the previous post, one of the very nice things you can do with DB2 Web Query is take advantage of the SQL based IBM i services available.  It is a quick and easy way to demonstrate more value. With that in mind, there are even more services available with the new technology refreshes.

A couple other interesting additions are some JSON functionality and IBM i Access Client Solutions (ACS) enhancements.  If you are not exploring the use of ACS to interact with DB2 for i, now is a good time start. I suggest making it part of your DBE toolkit.

With regards to JSON, document stores and unstructured, non-relational databases, look for more information and insight to come. The notion of employing "other" databases that have advantageous attributes and specific characteristics tailored to today's ever expanding data storage and processing requirements is something we need to illuminate and discuss. Stay tuned.

As usual, Mr. Forstie has done a very nice job detailing the technology refreshes for 7.2 here, and 7.3 here.  Check it out.

And if you have any questions or concerns as to how to take advantage of all the tools and techniques, please let me know.