How Many Temporal Tables Does Your Site Have?

Sometimes it can be difficult to remember where information is stored in the Db2 Catalog. Usually, with a little rumination and a little review of Appendix A of the IBM Db2 SQL Reference manual (SC27-8859), you can come up with a solution.

For example, I was talking to some DBAs who were trying to remember if they had ever created any business-time temporal tables. A comment was made that we could surely find that in the Db2 Catalog and the conversation moved along... but then I thought, hmmm, let me see what I can do about coming up with a catalog query.

The first step was to think about where this information might be found, which took me to SYSTABLES. A good first thought, but no, it isn't there. So I thought, how about SYSCOLUMNS? And lo' and behold, there was the answer.

The columns identified as the start and end date/time for the temporal range are documented in SYSCOLUMNS in the PERIOD column. PERIOD is defined as a CHAR(1) column and it contains one of the following values for every column defined for each table:

Value	Meaning
B	Column is the start of period BUSINESS_TIME
C	Column is the end of period BUSINESS_TIME with     an exclusive endpoint
I	Column is the end of period BUSINESS_TIME with     an inclusive endpoint
S	Column is the start of period SYSTEM_TIME
T	Column is the end of period SYSTEM_TIME
blank	Column is not used as either the start or the end of     a period

So using this information, here is a query that will show information about all of the business-time temporal tables you have created:

SELECT SUBSTR(TBCREATOR,1,8) || '.' || SUBSTR(TBNAME,1,30) 
       AS TABLENAME,
       SUBSTR(NAME,1,40) AS COLUMNNAME, 
       COLNO, 
       PERIOD
FROM   SYSIBM.SYSCOLUMNS
WHERE  PERIOD IN ('B', 'C', 'I')
ORDER BY TABLENAME, PERIOD, COLUMNNAME;

If you want to find the system-time temporal tables, just swap out the WHERE clause with this one:

  WHERE PERIOD IN ('S', 'T')  

By becoming adept at querying the Db2 Catalog tables you can find out just about everything you want to know about the databases and objects defined in your Db2 subsystems!

Db2 12 for z/OS Function Level 510

I'm a little late with this Db2 function level update, but better late than never, right?

In April 2021, IBM introduced a new function level, FL510, for Db2 12 for z/OS. If you want to take a look at the announcement for it, you can read it here, but there really isn't a lot to it.

Unlike all the other function levels, FL510 does not add any new features or capabilities, nor does it introduce any new changes to the Db2 Catalog. So what does it do?

This function level is basically there to prepare for the next new release of Db2, which will obviously be coming soon, or IBM would not have created this function level for it!  So it is time to start thinking about Db2 Next and getting ready for a new release/version of our favorite DBMS!

But we really haven't answered what FL510 does, have we? It is a housekeeping type of function level. When you activate FL510 it verifies and enforces several pre-migration conditions that have to be met before you can migrate to the next Db2 release. It will make sure that all Db2 12 function levels are activated and that all catalog updates for Db2 12 have been applied. This means that the Db2 catalog level is at the last catalog level for Version 12 and any future migration can therefore proceed.

Additionally, FL510 will check to make sure that your application packages were rebound recently enough to ensure that they are supported by the next Db2 release.

If any of the previous conditions are not met, then the activation of FL510 will fail. You will have to remediate your system and try to activate FL510 again before you can move forward to the new release.

Also, please be aware that FL510 has nothing to do with the fallback SPE that will have to be applied before moving forward with the eventual, new Db2 release. IBM will deliver the fallback SPE in a subsequent APAR at a point in time.

So I guess that this is a boring function level in that it delivers no new functionality... but it is exciting as it is a pre-req for a new  Db2 release that is on the horizon!

Thinking About the Mainframe, the Cloud, and IBM Think 2021

A Bit about Think

I am looking forward to attending the IBM Think 2021 conference, IBM's annual flagship technology event. I have attended several in-person Think events, as well as last year’s virtual conference, and I always come away with new knowledge and additional insight into technology and IBM’s vast portfolio of hardware, software, and solutions. The Think conference is always one of the tech highlights of the year for me!

This year’s event, IBM Think 2021, is again being held as a virtual conference, May 11 and 12, 2021. And it is free of charge, which means that you can experience all the great education, presentations, and networking opportunities without having to leave your desk.

My favorite aspect of the Think conference is the breadth and scope of pertinent technical content that it covers. Whether you are a developer, a DBA, a data scientist, a manager, an executive, or any flavor of IT or business specialist, there will be a wealth of useful information presented to educate you and make you “think.”  Be sure to register here.

My Think 2021 Agenda

There are multiple sessions to be delivered at this year’s IBM Think conference that intrigue me because they focus on areas where I specialize.  For example, Dr. Dario Gil, SVP and Director of IBM Research will be delivering a keynote session on IT infrastructure which is sure to be educational. This session, 2081, offers a deep dive into the IBM innovations powering the next generation of hardware, including IBM Z.

Another session I am looking forward to is session 2303 focusing on security “everywhere.” It features IBM luminaries like Tom Rosamilia, Senior Vice President, IBM Systems, and Mary O’Brien, General Manager IBM Security. And Forrester Research Director, Lauren Nelson, will also be lending her industry expertise to the session.

But I think the Think 2021 session I am most looking forward to is The IBM Z roadmap for hybrid cloud and AI (session 1605) featuring Ross Mauri General Manager for IBM Z. Mauri promises to offer a timely discussion on the business value of integrating the IBM Z platform as a full participant into your hybrid cloud. And he’ll speak with Russell Plew, Technology Senior Manager at M&T Bank who will discuss their real-life experiences in doing so!

Why is this session so interesting to me? Well, I’ve worked with the mainframe my entire career, and as anybody who works on the mainframe knows, the IBM Z platform is used to drive mission-critical workloads across all major industry sectors, worldwide. If your organization needs to perform large-scale transaction processing (thousands of transactions per second), support thousands of users and programs concurrently, manage terabytes of information, and handle large-bandwidth communication, chances are you rely on the mainframe to do that because the platform excels at all of those things.

If you’ve ever deposited a check into your bank account, booked a flight on an airline, or used a credit card to purchase something, it is probable that a mainframe was involved in completing that activity!

Ever since Stewart Alsop of InfoWorld predicted the last mainframe would be unplugged on March 15, 1996 there has been a lingering perception that the mainframe would go away at some point. But here we are, 25 years later, and the mainframe is still going strong! At last year’s IBM Think conference IBM presented the following statistics on the mainframe’s ubiquity and power:

●      70% of the Fortune 500 use mainframes and 72% of customer-facing applications are dependent on the mainframe for some or all data processing.

●      Mainframes are designed to be able to process a trillion web transactions a day with the capability to process 1.1 million transactions per second.

●      95% of transactions in the banking, insurance, airline and retail industries are handled by mainframes.

Indeed, the mainframe continues to offer a strong, unparalleled platform for performance, security, and reliability. Of course, the mainframe has changed and grown over its 50+ year lifespan. Today’s IBM z15 is light-years beyond the original IBM System/360 introduced in 1964. Some of the great newer capabilities of the IBM Z include encryptions everywhere with pervasive encryption and Data Privacy Passports, rack-mountable mainframes, Instant Recovery, and cloud-native development. I’m looking forward to hear how IBM’s customers have taken advantage of these, and other capabilities, to integrate the IBM Z into their hybrid cloud architecture.

It only makes sense that businesses relying on the mainframe will continue to do so, even as they embrace cloud computing. This is what the “hybrid” in the term hybrid cloud implies, an IT infrastructure that uses a mix of on-premises and private / public cloud from multiple providers. And this approach makes the most sense because everything can’t shift to the cloud immediately (perhaps ever) because most existing applications were not built with an understanding of the public cloud and it would take a lot of investment to re-engineer them to properly take advantage of a public cloud architecture. And even if you wanted to move everything, cloud service providers (CSPs) can’t build out their infrastructure fast enough to support all the existing data center capacity “out there” to immediately support everything.

So, it will be exciting to watch the IBM continue to innovate on the IBM Z platform as enterprise customers work to integrate Z as a vital component of their hybrid cloud infrastructure. With the large investment enterprises have in their working mainframe applications, large data sets and databases containing crucial data, and high-volume processing requirements they will continue to rely on the mainframe well into the future… and that makes it important to understand how IBM is enabling the IBM Z to participate in your hybrid cloud architecture.

So, join me at Think 2021 for session 1605 to learn how to use your investments in IBM Z and build and modernize applications into container-based workloads using a common DevOps experience. And stick around for other sessions to gain insights on harnessing the full value of IBM hardware, software and services in your organization as you continue to support, manage, and transform traditional business and IT operations.

Happy Birthday to the IBM Mainframe

I am older than the mainframe... I turned 58 on April 3rd, and the IBM mainframe officially celebrates its 57th birthday today, April 7th.

The IBM 360 was launched on April 7, 1964 and the world of enterprise computing has never been the same.

Here are a few links and articles to check out as we celebrate the ongoing vitality of mainframe computing:

• IBM Archives: System/360 Announcement
• Building the System/360 Mainframe Nearly Destroyed IBM
• How Mainframes Have Changed Over the Years
• Wikipedia: IBM System/360 mainframe line
• 9 Mainframe Statistics that may Surprise You

So, all of you mainframe users out there, today is indeed a day to celebrate... another year has gone by, and mainframes are still here... running the world!

Db2 12 for z/OS Function Level 509

Late last month, February 2021, IBM introduced a new function level, FL509, for Db2 12 for z/OS. You can find in-depth details here.

But if you are looking for a high-level synopsis, read on! 

There are several interesting new capabilities introduced with this function level, but perhaps the most important thing that organizations want to know is that there are no new incompatible changes or deprecations introduced with FL509.

Okay, so what’s new here. The first thing to report is an improvement to data security with tamper-proof audit policies. This means that an audit policy cannot be changed, or even stopped, unless requested by an authorized user. And the authorization must be via a z/OS security product (such as IBM’s RACF), not Db2.

This capability provides another step in the separation of duties required for proper auditing. In other words, the audited must not be the controller of the audit policy or auditing capabilities. It also protects administrative users from mistakenly modifying audit policies.

The next new capability delivered by FL509 is high-availability accelerator-only tables. Accelerator-only tables (AOTs) are those defined to the IBM Db2 Analytics Accelerator only, and not in the base Db2 for z/OS. Queries and DML statements issued against AOTs are always routed to an accelerator (because the data does not exist anywhere else).

So, what are high availability AOTs? Well, FL509 delivers the capability to define an accelerator-only table in more than one accelerator. This can improve availability and with workload balancing a query can be rerouted to another available accelerator if the target accelerator is not available.

Also as of FL509, you can specify a compression algorithm at the table, table space, or partition level. This means you can explicitly use either the fixed-length or Huffman compression algorithm at the table, table space, or partition level using CREATE TABLE and ALTER statements. The Db2 catalog is updated to indicate the compression algorithm used for each object.

Finally, FL509 delivers enhanced temporal RI. What this means is that restrictions on UPDATE and DELETE statements are removed relating to the temporal RI introduced originally in Db2 12.

To elaborate, one FL509 is active, when an UPDATE statement with a FOR PORTION OF clause attempts to update the parent table in a temporal RI relationship, the update is allowed as long as the rules of temporal RI are not violated. Likewise, when a DELETE statement with a FOR PORTION OF clause attempts to delete from the parent table in a temporal RI relationship, the deletion is allowed, as long as the rules of temporal RI are not violated.

At any lower application compatibility level, such UPDATE or DELETE statements for a parent table in an RI relationship will fail (with SQLCODE -4736).

Summary

Now that IBM is using function levels to deliver significant new capabilities for Db2 12 for z/OS, it is imperative that your organization keeps up-to-date on this new functionality and determines where and when it makes sense to introduce it into your Db2 databases and applications.

Also, be aware that if you are not currently running at FL508, moving to FL509 activates all earlier function levels. You can find a list of all the current function levels here.