Know Your Db2 Universal Table Spaces

 One of the biggest changes in the last decade or so has been the introduction of new types of table spaces – known as Universal table spaces, or UTS. Not only are UTS new to Db2, they are quickly becoming the de facto standard type of table space for Db2 applications, new and old.

At some point, Universal table spaces will displace your existing segmented and classic partitioned table spaces. We’ll examine why this is so later in the post, but first let’s briefly describe what Universal table spaces are.

Two Types of Universal Table Spaces

Introduced in Db2 9 for z/OS, Universal table spaces combine the best attributes of partitioned and segmented table spaces. If you do not know what partitioned and segmented table spaces are, I refer you to this older article I wrote on DB2 Table Space Options to bring you up to speed (note that this article is almost 20 years old at this point).

Universal table spaces offer improved space management for variable length rows because they use space map pages (like segmented table spaces). Also, like segmented table spaces, UTS deliver improved mass delete performance, and you can immediately reuse the table segments after the mass delete. And like partitioned table spaces, Universal table spaces can grow large (up to 128TB of data) and consist of multiple partitions.

At a high-level, there are two types of Universal table spaces:

1.     Partition-by-growth (PBG): The PBG UTS creates new partitions as the amount of data grows without the need to specify key ranges. This type of UTS is beneficial for tables that grow over time and need the additional limits afforded by partitioning but can benefit from the performance of segmented.

2.     Partition-by-range (PBR): The range-partitioned, or PBR UTS requires a key range for partitioning like classic partitioned table spaces. A PBR UTS basically adds segmentation to the existing partitioned table space.

 

Both types of UTS can contain only a single table, but IBM presentations have indicated that this is likely to change at some point in the future (although nothing has been announced or confirmed for certain).

A partition-by-range UTS is basically a segmented, partitioned table space. The limit key ranges must be specified in the table DDL. Index partitioning, which was supported for the earliest classic partitioned table spaces, is not supported for a PBR UTS. So before converting your classic partitioned table spaces to PBR UTS, you must first convert from index-controlled partitioning to table-controlled partitioning. Check out this blog post for a trick to quickly convert to table-controlled partitioning.

The second type of UTS is the partition-by-growth Universal table space. As its name implies, a PBG UTS can automatically add a new partition as the data in the table space grows. Over time, as the UTS is used by applications, data gets added to the table. When the PBG UTS reaches its maximum size, a new partition is automatically added to the table space. The new partition uses the same characteristics as the existing partitions, including compression details, free space, and so on.

You control the type of UTS using the DDL keywords: NUMPARTS, MAXPARTITIONS, and SEGSIZE. To create a PBR UTS you specify both NUMPARTS and SEGSIZE. To get a PBG UTS you must code the MAXPARTITIONS and SEGSIZE parameters. MAXPARTITIONS indicates the limit on the total number of partitions that a PBG UTS can grow to. Be careful, because if you only code the NUMPARTS parameter without SEGSIZE, then you will create a traditional partitioned table space. If you only code the SEGSIZE parameter (without either NUMPARTS or MAXPARTITIONS) you will create a traditional segmented table space.

Db2 12 for z/OS

A significant new feature for supporting big data was introduced in Db2 12, relative page numbering (or RPN) for range-partitioned table spaces. An RPN range-partitioned table space can be created, or an existing range-partitioned table space can be changed to RPN via an ALTER TABLESPACE with PAGENUM RELATIVE, followed by an online REORG of the entire table space.

An RPN table space delivers many benefits for availability and storing large amounts of data. This requires an expanded RID, which increases from 5 bytes to 7 bytes.

From an availability perspective, you can specify DSSIZE at the partition level for RPN table spaces. Furthermore, the allowable DSSIZE value is no longer dependent on the page size and number of table space partitions. The DSSIZE change can be an immediate change (no online REORG required to take effect) as long as the change does not decrease the DSSIZE value. You still can decrease DSSIZE, but only at the table space level.

From a big data perspective, the DSSIZE can grow up to 1 TB for a partition. And the maximum table size increases to 4 PB with approximately 280 trillion rows per table. That is a lot of data that can be stored. Think about it this way: if you were to insert 1000 rows per second it would take more than 8000 years to fill the table to capacity!

Why Are Universal Table Spaces the Future of Db2?

As of today (September 2020, Db2 12 for z/OS), there are basically five types of table spaces from which to choose:

1.     Segmented table spaces

2.     Universal Partition-By-Growth (PBG) table spaces

3.     Universal Partition-By-Range (PBR) table spaces

4.     Universal Partition-By-Range Relative Page Number table spaces

5.     Classic partitioned table space

Of course, for new databases, it is best to remove the classic partitioned table space from consideration because the PBR UTS is more efficient (and classic partitioning will likely be deprecated at some point). Technically speaking, there are actually two other types of table spaces (LOB and XML table spaces), but they are not general-purpose table spaces and can be used only in specific situations (with LOB and XML data).

So why do I advise that you favor Universal table spaces over segmented whenever you can? Well, for a number of reasons. First of all, because Universal table spaces are newer and all you really need for most every Db2 implementation. Secondly, because many new features of Db2 can only be used with Universal table spaces. Newer features that only work with UTS include:

·     Clone tables

·     Hash-organized tables

·     Currently committed locking

·     Pending DDL

·     Inline LOBs

·     XML multi-versioning

·     ALTER TABLE with DROP COLUMN

And this trend is likely to continue. As IBM introduces new function levels and versions of Db2 with new features that only work with UTS, it will become increasingly difficult for DBAs to keep track of which table spaces are not UTS so that they can make sure they are not using any new features that will not work with their old types of table spaces.

What this means is that other than Universal table spaces, the only other type you should be using are segmented table spaces and then only when you absolutely must have a multi-table table space. Therefore, the best practice I recommend is to make all new table spaces Universal (except for multi-table table spaces which can be segmented).

So, what is the future of the segmented table space? For the immediate future, segmented table spaces will continue to be supported. My guess is that, at some point, IBM will deliver a multi-table UTS capability, and then at some point deprecate segmented table spaces. But this is only my guess. As of the date I am writing this, IBM has not committed to a multi-table UTS and the segmented table space is still the recommended (and only) method for assigning multiple tables into a single table space.

My general recommendation though is that you avoid multi-table table spaces unless you have many very small tables and are close to reaching the open data set limit (200,000). Of course, your limit may be lower depending on the setting of the DSMAX subsystem parameter, which specifies the maximum number data sets that can be open at one time. Acceptable values range from 1 to 200,000; a lower setting may be specified due to operating system contraints or storage/memory limitations.

My general recommendation for table spaces is to slowly work on a conversion project to migrate your classic partitioned table spaces to PBR UTS and your segmented table spaces to PBG UTS. Doing so will bring you to the latest and greatest Db2 table space technology and position you to be able to use all new functionality in current and future versions of Db2 whenever – and wherever – you see fit.

Summary

To make sure that your systems are up-to-date and ready for new functionality it makes sense to adopt Universal table spaces for all of your Db2 tables. The only exception is for multi-table segmented table spaces, and you shouldn’t have too many of them.

Good luck Universalizing your Db2 databases!

The Benefits of In-Memory Processing

One area that most organizations can benefit from is by better using system memory more effectively. This is so because accessing and manipulating data in memory is more efficient than doing so from disk.

Think about it… There are three aspects of computing that impact the performance and cost of applications: CPU usage, I/O, and concurrency. When the same amount of work is performed by the computer using fewer I/O operations, CPU savings occur and less hardware is needed to do the same work. A typical I/O operation (read/write) involves accessing or modifying data on disk systems; disks are mechanical and have latency – that is, it takes time to first locate the data and then read or write it.

There are many other factors involved in I/O processing that involve overhead and can increase costs, all depending upon the system and type of storage you are using. For example, each I/O consists of a multitude of background system processes, all of which contribute to the cost of an I/O operation (as highlighted in Figure 1 below). It is not my intent to define each of these processes but to highlight the in-depth nature of the processing that goes on behind-the-scenes that contributes to the costly nature of an I/O operation.

Figure 1. The Cost of an I/O

So, you can reduce the time it takes to process your mainframe workload by more effectively using memory. You can take advantage of things like increased parallelism for sorts and improve single-threaded performance of complex queries when you have more memory available to use. And for OLTP workloads, large memory provides substantial latency reduction, which leads to significant response time reductions and increased transaction rates.

The most efficient way to access data is, of course, in-memory access. Disk access is orders-of-magnitude less efficient than access data from memory. Memory access is usually measured in microseconds, whereas disk access is measured in milliseconds. (Note that 1 millisecond equals 1000 microseconds.)

The IBM z15 has layers of on-chip and on-board cache that can improve the performance of your application workloads. We can view memory usage on the mainframe as a pyramid, from the slowest to the fastest, as shown in Figure 2. As we go up the pyramid, performance improves; from the slowest techniques (like tape) to the fastest (core cache). But this diagram drives home our core point even further: that system memory is faster than disk and buffering techniques.

Figure 2. The Mainframe Memory Pyramid

So how can we make better use of memory to avoid disk processing and improve performance? Although there are several different ways to adopt in-memory processing for your applications, one of the best methods can be to utilize a product. One such product is the IBM Z Table Accelerator.

 IBM Z Table Accelerator is an in-memory table accelerator that can improve application performance and reduces operational cost by utilizing system memory. Using it can help your organization to focus development efforts more on revenue-generating business activity, and less on other less efficient methods of optimizing applications. It is ideal for organizations that need to squeeze every ounce of power from their mainframe systems to maximize performance and transaction throughput while minimizing system resource usage at the application level. You can use it to optimize the performance of all types of data, whether from flat files, VSAM, Db2, or even IMS.

 So how does it work? Well, typically a small percentage of your data is accessed and used a large percentage of the time. Think about it in terms of the 80/20 Rule (or the Pareto Principle).  About 80% of your data is accessed only 20% of the time, and 20% of your data is accessed 80% of the time.

The data that you are accessing most frequently is usually reference data that is used by multiple business transactions. By focusing on this data and optimizing it you can gain significant benefits. This is where the IBM Z Table Accelerator comes into play. By copying some of the most often accessed data into the accelerator, which uses high-performance in-memory tables, significant performance gains can be achieved. That said, it is only a small portion of the data that gets copied from the system of record (e.g. Db2, VSAM, etc.) into the accelerator.

High-performance in-memory technology products -- such as IBM Z Table Accelerator -- use system memory. Sometimes, if the data is small enough, it can make it into the L3-L4 cache. This can be hard to predict, but when it occurs things get even faster.

Every customer deployment is different, but using IBM Z Table Accelerator to optimize in-memory data access can provide a tremendous performance boost.

A Use Case: Tailor-Fit Pricing

Let’s pause for a moment and consider a possible use case for IBM Z Table Accelerator.

In 2019, IBM announced Tailored Fit Pricing (TFP), with the goal of simplifying mainframe software pricing and billing. IBM designed TFP as a more predictable, cloud-like pricing model than its traditional pricing based on a rolling-four-hour-average of usage. Without getting into all of the details, TFP eliminates tracking and charging based on monthly usage and instead charges a consistent monthly bill based on the previous year’s usage (plus growth).

It is important to note that last point: TFP is based on last year’s usage. So you can reduce your bill next year by reducing your usage this year, before you convert to TFP. Therefore, it makes a lot of sense to reduce your software bills to the lowest point possible the year before the move to TFP.

 

So what does this have to do with IBM Z Table Accelerator? Well, adopting techniques to access data in-memory can lower MSU usage – and therefore your monthly software bill. Using IBM Z Table Accelerator to optimize your reference data in-memory before moving to TFP can help you to lower your software bills and better prepare you for the transition to Tailored Fit Pricing.

 

 --------------------

 If you’d like to learn more about IBM Z Table Accelerator there is an upcoming SHARE webinar on September 15, 2020, that goes into some more details about the offering. It is titled Digital Transformation IncludesGetting The Most Out of Your Mainframe: click the link for details and to register to attend.

 

 

 

 

 

IBM Db2 on Cloud News

If you are thinking about, or have already adopted Db2 in the cloud, there is some recent news you should know about. But before we explore that news, let’s take a look at the quick highlights of using Db2 in the cloud.

IBM’s Db2 on Cloud offering is a fully-managed operational data store running the IBM Db2 11.5 engine offering 24x7x365 availability. So if you know Db2 on Linux, Unix, and Windows platforms, you know Db2 on Cloud… but there’s more.

Db2 on Cloud runs containerized Db2 on with a dedicated DevOps team managing the maintenance and updates required to run your mission-critical workloads. This includes features like seamless data federation, point-in-time recovery, HADR with multizone region support and independent scaling. So many of the administrative burdens of managing Db2 on-premises are handled by IBM in the cloud.

Now if you know me, and have been reading my “stuff” on cloud and DBA, you know that this does not mean that you can entirely offload you DBA. But it is cool and it does help, especially with DBA teams being stressed to their limits these days.

So yes, you can run Db2 on Cloud! And there are many good reasons to consider doing so, such as scalability, pay-as-you-use pricing, and to take advantage of managed services.

OK, So What is New?

I promised some news in the title of this blog post and so far we have just set the stage by examining IBM’s cloud offering of Db2 (albeit at a high level). So, what’s new?

Well, IBM is revamping its pricing plans. Before digging into the news, you need to know that IBM offers two high-level pricing plan options.

• The Lite plan uses a shared multi-tenant system designed for application development and evaluation of IBM Db2 on Cloud. It is offered free-of-charge, without any time limitations.
• Enterprise plans are for usage and deployment of business applications and systems. It includes one database per service instance with 4 vCPU x 16 GB RAM x 20 GB storage on dedicated compute slices with the option to use a three-HA-node configuration with multizone region support. Pricing starts at $989/month.

What is new is that on August 19 IBM introduced two new plans, the Enterprise non-HA plan, and the Standard non-HA plan.  This means that there are now four options, other than the free Lite plan: Enterprise HA, Enterprise non-HA, Standard HA, and Standard non-HA.

As is typical with IBM pricing, it is not really all that simple and it is getting more complex.  But options are always good (I think).

So what is this Standard plan that does not appear on the IBM Db2 on Cloud: Pricing page? Well, we can find this on the IBM Db2 on Cloud catalog page Here we see that (as one might expect) it is a lower-cost option between Lite and Enterprise starting at 8 GB RAM with 20 GB storage.

IBM also noted that IBM Db2 on Cloud is now available in the following six data centers: Dallas, Frankfurt, Tokyo, London, Sydney, and Washington. And your instances can be provisioned either with or without the Oracle compatibility feature.

It is important to note that IBM also notes that customers on older, legacy plans (how about that, cloud legacy already) will be required to upgrade their to one of the newer plans.

 Summary

So, there are more options to choose from with your Db2 on Cloud implementations. And if you have an older plan take some time to familiarize yourself with the new pricing plan options and be ready to choose accordingly for your workload requirements.

Navigating the IBM COBOL 4.2 End of Service Waters: Chart a course to benefit your business

 

Surprisingly, COBOL has been in the news a lot recently, due to its significant usage in many federal govern-ment and state systems, most recently with unemployment systems, being in the news. With the global COVID-19 pandemic, those unemployment systems were stressed like never before with a 1600% increase in traffic (Government Computer News, May 12, 2020) as those impacted by the pandemic filed claims.

Nevertheless, there is another impending event that will likely pull COBOL back into the news as IBM withdraws older versions of the COBOL compiler from service. All IBM product versions go through a lifecycle that starts with GA (general availability), after some time moves to EOM (end of marketing) where IBM no longer sells that version, and ends with EOS (end of support) where IBM no longer supports that product or version. It is at this point that most customers will need to decide to stop using that product or upgrade to a newer version because IBM will no longer fix or support EOS products or versions.

Of course, code that was compiled using an unsupported COBOL compiler will continue to run, but it is not wise to use unsupported software for important, mission-critical software, such as is usually written using COBOL. And you need to be aware of interoperability issues if you rely on more than one version of the COBOL compiler.

So what is going on in the world of COBOL that will require your attention? First of all, earlier this year on April 30, 2020, IBM withdrew support for Enterprise COBOL 5.1 and 5.2. And Enterprise COBOL 4.2 will be withdrawn from service on April 30, 2022 – just about two years from now.

So now is the time for your organization to think about its migration strategy.

Why is COBOL still being used?

Sometimes people who do not work in a mainframe environment are surprised that COBOL is still being used. But it is, and it is not just a fringe language. COBOL is a language that was designed for business data processing, and it is extremely well-suited for that purpose. It provides features for manipulating data and printing reports that are common business requirements. COBOL was purposely designed for applications that perform transaction processing like payroll, banking, airline booking, etc. You put data in, process that data, and send results out.

COBOL was invented in 1959, so its history stretches back over 60 years; a lot of time for organizations to build complex applications to support their business. And IBM has delivered new capabilities and features over the years that enable organizations to keep up to date as they maintain their application portfolio.

So, COBOL is in wide use across many industries.

A majority of global financial transactions are processed using COBOL, including processing 85 percent of the world’s ATM swipes. According to Reuters, almost 3 trillion dollars in DAILY commerce flows through COBOL systems!

The reality is that more than 30 billion COBOL transactions run every day. And there are more than 220 billion lines of COBOL in use today. COBOL is not dead…

 What’s new in COBOL 6

With COBOL 5.1 and 5.2 already out of support, and COBOL 4.2 soon to follow, one migration path is to Enterprise COBOL 6, and IBM has already delivered three releases of it: 6.1, 6.2, and 6.3. There are some nice new features that are in the latest version(s) of IBM Enterprise COBOL, including:

• Compile and runtime support delivering performance improvements for z15 hardware and z/OS 2.4 operating system
• Increased compiler capacity making it possible to compile and optimize larger programs (6.1)
• 64-bit (AMODE 64) support in this compiler enables users to process large data tables that require greater than 2 GB of addressing space (6.3)
• JSON support (6.1) including JSON PARSE statement (6.2)
• Support for many new features from the COBOL 2002/2014 programming standards including new statements like ALLOCATE, FREE and INITIALIZE; addition of Dynamic Length elementary items; conditional compilation using the DEFINE compiler option, and more
• Many new compiler options
• Improved usability with USS

At the same time, there are concerns that need to be considered if and when you migrate to version 6. One example is that the new compiler will take longer to compile programs than earlier versions – from 5 to 12 times longer depending on the optimization level. There are also additional work data sets required and additional memory considerations that need to be addressed to ensure the compiler works properly. As much as 20 times more memory may be needed to compile than with earlier versions of the compiler.

Some additional compatibility issues to keep in mind are that your executables are required to be stored in PDSE data sets and that COBOL 6 programs cannot call or be called by OS/VS COBOL programs.

And of course, one of the biggest issues when migrating from COBOL 4.2 to a new version of COBOL is the possibility of invalid data – even if you have not changed your data or your program (other than re-compiling in COBOL 6). This happens because the new code generator may optimize the code differently. That is to say, you can get different generated code sequences for the same COBOL source with COBOL 6 than with 4.2 and earlier versions of COBOL. While this can help minimize CPU usage (a good thing) it can cause invalid data to be processed differently, causing different behavior at runtime (a bad thing).

Whether you will experience invalid data processing issues depends on your specific data and how your programmers coded to access it. Some examples of processing that may cause invalid data issues include invalid data in numeric USAGE DISPLAY data items; parameter/argument size mismatches; using TRUNC() with binary data values having more digits than they are defined for in working storage; and data items that are used before they have been assigned a value.

Migration considerations

Keep in mind that migration will be a lengthy process for any medium-to-large organization, mostly due to testing application behavior after compilation, and comparing it to pre-compilation behavior. You need to develop a plan that best suits your organization’s requirements and work to implement it in the roughly 2-year timeframe before IBM Enterprise COBOL 4.2 goes out of support.

Things to consider:

• Gartner research shows that “huge ‘all-or-nothing’ modernization programs often fail to meet expectations”
• What is your current state? Which COBOL compilers are you using and what is your end goal (6.1, 6.2, 6.3)?
• Remember that compiled programs will continue to run, so it may not be imperative to re-compile everything prior to the end of support date. Of course, it can be difficult to keep track of what has been converted and what has not if you do not have a plan moving forward other than “convert when the program has to be changed at some point.” And it can become difficult to keep track of all the requirements and incompatibilities for multiple versions of COBOL if you do not plan for, and eventually convert to a newer compiler version.
• Do you have the COBOL talent and knowledge not only to convert but to continue supporting your existing portfolio of COBOL applications?
• Enterprise application portfolios can be quite large, making it difficult to effectively discover and map all of the dependencies. Consider using tools to help. 

Migration challenges and Options to Consider

As you put your plan together, you might consider converting some of your COBOL applications to Java. An impending event such as the end of support for a compiler is a prime opportunity for doing so. But why might you want to convert your COBOL programs to Java?

Well, it can be difficult to obtain and keep skilled COBOL programmers. As COBOL coders age and retire, there are fewer and fewer programmers with the needed skills to manage and maintain all of the COBOL programs out there. At the same time, there are many skilled Java programmers available on the market, and universities are churning out more every year.

Additionally, Java code is portable, so if you ever want to move it to another platform it is much easier to do that with Java than with COBOL. Furthermore, it is easier to adopt cloud technologies and gain the benefits of elastic compute with Java programs.

Cost reduction can be another valid reason to consider converting from COBOL to Java. Java programs can be run on zIIP processors, which can reduce the cost of running your applications. A workload that runs on zIIPs is not subject to IBM (and most ISV) licensing charges... and, as every mainframe shop knows, the cost of software rises as capacity on the mainframe rises. But if capacity can be redirected to a zIIP processor, then software license charges do not accrue - at least for that workload.

Additional benefits of zIIPs include:

• They are significantly cheaper to acquire than standard CPs
• When workload is redirected to a zIIP it frees up capacity on the standard CP

So, there are many reasons to consider converting at least some of your COBOL programs to Java. Some may be worried about Java performance, but Java performance is similar to COBOL these days; in other words, most of the performance issues of the past have been resolved. Furthermore, there are many tools to help you develop, manage, and test your Java code, both on the mainframe and other platforms.

Keeping in mind the concerns about “all-or-nothing” conversions, most organizations will be working toward a mix of COBOL migrations and Java conversions, with a mix of COBOL and Java being the end results. As you plan for this be sure to analyze and select appropriate candidate programs and applications for conversion to Java. There are tools that can analyze program functionality to assist you in choosing which the best candidates. For example, you may want to avoid converting programs that frequently call other COBOL programs and programs that use pre-relational DBMS technologies (such as IDMS and IMS).

How to convert COBOL to Java

At this point, you may be thinking, “Sure, I can see the merit in converting some of my programs to Java, but how can I do that? I don’t have the time for my developers to re-create COBOL programs in Java going line-by-line!” Of course, you don’t!

This is where an automated tool comes in handy. The CloudFrame Migration Suite provides code conversion tools, automation, and DevOps integration to deliver very maintainable, object-oriented Java that can integrate with modern technology available within your open architecture.  It can be used to refactor COBOL source code to Java without changing data, schedulers, and other infrastructure components. It is fully automated and seamlessly integrates with the change management systems you already use on the mainframe.

The Java code generated by CloudFrame will operate the same as your COBOL and produce the same output. There are even options you can use to maintain the COBOL 4.2 treatment of data, thereby avoiding the invalid data issues that can occur when you migrate to COBOL 6. This can help to reduce project testing and remediation time.

It is also possible to use CloudFrame to refactor your COBOL programs to Java but keep maintaining the code in COBOL. Such an approach, as described in this blog post (Consider Cross-Compiling COBOL to Java to Reduce Costs), can allow you to keep using your COBOL programmers for maintenance but to gain the zIIP eligibility of Java when you run the code.

Upcoming Webinar

To learn more about COBOL migration, modernization considerations, and how CloudFrame can help you to achieve your modernization goals, be sure to attend CloudFrame’s upcoming webinar where I will be participating on a panel along with Venkat Pillay (CEO and founder of CloudFrame) and Dale Vecchio (industry analyst and former Gartner research VP). The webinar, titled Navigating the COBOL 4.2 End of Support(EOS) Waters: An expert panel discusses the best course of action to benefit your business will be held on September 23, 2020 at 11:00 AM Eastern time. Be sure to register and attend!

Summary

Users of IBM Enterprise COBOL 4.2 need to be aware of the imminent end of service date in April 2022 and make appropriate plans for migrating off of the older compiler.

This can be a great opportunity to consider what should remain COBOL and where the opportunities to modernize to Java are.  Learn how CloudFrame can help you navigate that journey.

The Virtual North American IDUG Db2 Tech Conference 2020

The IDUG North America Virtual Conference is happening now... and it runs through the end of next week (August 14, 2020), so there is still time to register, attend, and hear about some great Db2 "stuff!"

Originally, the event was planned as a weeklong conference to be held early in June in Dallas, Texas. But with the pandemic, IDUG changed its plans and turned this year's North American IDUG into a virtual event. The conference has actually been running since July 20, 2020, when the event kicked off, with new content (labs, workshops, and sessions) being released every week since.

What this means to you is that there is still time to take advantage of all the great, online Db2 content that IDUG has made available virtually! The IDUG Virtual Db2 Tech Conference is not free; there is a nominal cost of $199 to participate and attend. But this is a bargain considering the regular cost of attending an IDUG event (not just the event cost, but also travel and lodging).

I've been participating in the event for the past couple weeks and I have to say, there is great content available. It may be a bit more difficult to stay focused on the event as you participate from your home office, though. At least, I found it to be. There are interruptions and distractions that are not there when you participate live, on-site. Furthermore, the camaraderie of an in-person event is lost when it is just you and your computer. 

But these are minor quibbles. Overall, there is a lot of great stuff on offer from this virtual IDUG event that make it well worth the nominal fee being charged. The event includes 60+ sessions, live Q&A with industry experts and leaders, opportunities for engagement with your favorite vendors and each other, and most importantly, cutting-edge technical education streaming straight to your home or office. Your registration also includes a complimentary premium membership so you can access exclusive IDUG content all year long.  

One final thing to share with you is that my pre-recorded session, The Plight of the Modern DBA, will be available starting Monday, August 9, 2020 at 8:00 AM Easter time. I hope you'll take the time to give it a listen and share your thoughts with me. I’ll be participating in a Q&A session on Friday, August 14, 2020, at 2:15 PM Eastern time, so you can stop by and ask anything you'd like!

More information on the 2020 IDUG virtual Db2 Tech Conference can be found here.

Mainframe Earnings Up Big for IBM

This week IBM announced earnings results for 2020Q2 and the company reported Systems revenues of $1.9 billion, up 6 percent, led by IBM Z, which was up 69 percent… the mainframe is a shining jewel in IBM’s earnings.

Note that the Systems category comprises IBM systems hardware and operating system software, including mainframe, Power systems, storage, etc.

Other bright spots for IBM include total cloud revenue of $6.3 billion, up 30 percent and Red Hat revenue up 17 percent.

After reporting the earnings, IBM shares rose by as much as 6 percent in extended trading on Monday due to the overall better-than-expected results.

Overall, earnings were $2.18 per share, adjusted, vs. $2.07 per share as expected by analysts.

Take Advantage of the Wealth of Information in Your Db2 Logs

The Db2 for z/OS log sometimes referred to as the transaction log, is a fundamental component of Db2 that is central to all data activity in the database management system. Every change to application data (with a few exceptions) is recorded serially in the log as the change is made. The log is a key resource for ensuring data integrity and recoverability of data. Using the logged information, Db2 can track which transaction made which changes to the database.

The log contains units of recovery, checkpoint data, control records, and other pieces of information needed to ensure that your data is successfully managed and changed appropriately. Log data is crucial for rolling back unwanted changes, recovering database objects, and resetting the database back to a particular point-in-time. 

During normal database application processing SQL inserts, updates, and deletes are executed to modify data in the database. As these database modifications are made, they are recorded in the log. The Db2 transaction log is a write-ahead log. This means that changes are made to the transaction log before they are actually made to the data in the database itself. When the database modification has been fully recorded on the log, recovery of the transaction is guaranteed. 

Periodically, a system checkpoint is taken by Db2 to guarantee that all log records and all modified database pages are written safely to disk. The frequency of database system checkpoints can be set up by the DBA using database configuration parameters – usually, checkpoint frequency is set either as a predetermined time interval or as a preset number of log records written. 

Generally, the following type of information is recorded on the database log: 

• the beginning and ending time of each transaction
• the actual changes made to the data and enough information to undo the modifications made during each transaction (accomplished using before and after images of the data)
• the allocation and deallocation of database pages
• the actual commit or rollback of each transaction

Using this data the DBMS can accomplish data integrity operations to ensure consistent data is maintained in the database. Of course, there are other “things” stored in the log, but I don’t want to get into an in-depth discussion of that. At this point, it is time to start thinking about all of the great information stored on the log, and how we can take advantage of that information to accomplish many different tasks.

Using the Db2 Log

There are many worthwhile uses for the data on the Db2 log other than the operational necessities as required by Db2 for z/OS itself. Because the log records data changes, it can aid in the delivery of data propagation, database auditing, surgically repairing changed data using undo and redo SQL, and undropping database objects. You can also use the Db2 log to report on all changes and identify erroneous changes as well as when they were made.

Of course, to use the log you need to understand the structure of the data (schema) and how it is configured. Log records are not simply laid out and it can take a long time to digest and understand the data. For this reason, many organizations look to acquire a product to deliver visibility and usage of log data.

And that brings us to UBS-Hainer’s ULT4Db2^TM.  

ULT4Db2 is a powerful log analysis product that delivers multiple capabilities for DBAs to manage, control, and analyze Db2 logs. ULT4Db2 simplifies most tasks associated with the Db2 log with a user-friendly ISPF interface and comprehensive automation features. No need to understand the Byzantine layout and structure of the Db2 log because ULT4Db2 does most of the heavy lifting for you.

You can keep Db2 tables synchronized using the ULT4Db2 data propagation feature. It can be used to directly execute the same INSERT, UPDATE, and DELETE statements against different target tables. Or you can direct ULT4Db2 to write those statements into external data sets. If your target tables are in a different database system or a platform like Oracle, Microsoft SQL Server, or other DBMS, then you can change the syntax of the generated statements to suit your needs.

You can keep track of changes to sensitive information using the ULT4Db2 audit capability. Use it to track who made what change to Db2 tables, when the change was made, and what exactly was changed. You can analyze all the changes over a given period of time and filter by user name, plan name, column contents, or any other criteria.

The repair capability of ULT4Db2 makes it simple to undo a single change -- or a single transaction -- that affected one or more tables. Instead of backing out or recovering an entire database object, ULT4Db2 can create SQL statements that revert a specific change that happened at a given point-in-time. This is sometimes referred to as undo SQL. To generate undo SQL, the database log is read to find the data modifications that were applied during a given timeframe and

• INSERTs are turned into DELETEs.
• DELETEs are turned into INSERTs.
• UPDATEs are turned around to UPDATE to the prior value.

This technique is also called transaction recovery. A traditional recovery specifies a database object and then lays down a backup copy of the object and reapplies log entries to recover the object to a specific, wanted point-in-time. Transaction recovery enables a user to recover a specific portion of data based on user-defined criteria. So only a portion of the data is affected. With ULT4Db2, these undo/redo statements can be written to an external dataset so that DBAs can review them first before running them as you would any other SQL statements.

And the ULT4Db2 undrop feature makes it easy to restore an object that was accidentally dropped. Restoring an object to the state before the drop operation is usually tedious and error-prone, requiring a lot of resources and extra work for DBAs. But ULT4Db2 can bring back objects that have been accidentally dropped using information from the Db2 log and existing image copy data sets. The entire process is automated and does not require manual intervention. ULT4Db2 is able to undrop databases, table spaces, tables, and indexes. All foreign keys, check constraints, and table privileges are automatically recreated as well.

ULT4Db2 can generate a variety of reports that help you keep an overview on how your tables are used. It can summarize the INSERT, UPDATE, and DELETE activity for your tables by different criteria like unit-of-recovery, user name, or plan name. You can also produce a detailed report that contains each row as it was before and after each update. These are just a few of the many reports that are available using ULT4Db2.

Finally, you can automate your ULT4Db2 log analysis processes using the ISPF interface.

The Bottom Line

The Db2 for z/OS logs contain a plethora of useful data that can be exploited to better manage your Db2 environment and expose useful information for business purposes. Consider looking into a log analysis tool like ULT4Db2 to help you better control and access the large variety of useful business information embedded in your Db2 logs.

Consider Cross-Compiling COBOL to Java to Reduce Costs

Most organizations that rely on the mainframe for their mission-critical workload have a considerable amount of COBOL programs. COBOL was one of the first business-oriented programming languages having been first introduced in 1959. Designed for business and available when the IBM 360 became popular, COBOL is ubiquitous in most mainframe shops.

Organizations that rely on COBOL need to make sure that they continue to support and manage these applications or risk interruptions to their business, such as those experienced by the COBOL applications that run the state unemployment systems when the COVID-19 pandemic caused a spike in unemployment applications.

Although COBOL continues to work -- and work well -- for many application needs, there are on-going challenges that will arise for organizations using COBOL. One issue is the lack of skilled COBOL programmers. The average age of a COBOL programmer is in the mid-50’s, and that means many are close to retirement. What happens when all these old programmers retire? 

Another issue is cost containment. As business improves and workloads increase, your monthly mainframe software bill is likely increasing. IBM continues to release new pricing models that can help, such as tailored-fit pricing, but it is not easy to understand all of the different pricing models, nor is it quick or simple to switch, at least if you want to understand what you are switching to.

And you can’t really consider reducing cost without also managing to maintain your existing performance requirements. Sure, we all want to pay less, but we need to maintain our existing service level agreements and meet our daily batch window deadline.

Cross-Compiling COBOL to Java

Which brings me to the main point of today’s blog post. Have you considered cross-compiling your COBOL applications to Java? Doing so can help to address some of the issues we just discussed, as well as being a starting point toward your application modernization efforts.

What do I mean by cross-compiling COBOL to Java? Well, the general idea is to refactor the COBOL into high-quality Java using Cloudframe^TM. CloudFrame is the company and the product, which is used to migrate business logic in COBOL into modular Java. This refactoring of the code changes the program structure from COBOL to object-oriented Java without changing its external behavior.

After refactoring, there are no platform dependencies, which allows the converted Java workloads to run on any platform while not requiring changes to legacy data, batch schedulers, CICS triggers or Db2 stored procedures.

I can already hear some of you out there saying “wait-a-minute… do you really want me to convert all of my COBOL to Java?” You can, but I’m not really suggesting that you convert it all and leave COBOL behind… at least not immediately.

But first, let’s think about the benefits you can get when you refactor your COBOL into Java. Code that runs on a Java Virtual Machine (JVM) can run on zIIP processors. When programs run on the zIIP, the workload is not charged against the rolling four-hour average or the monthly capacity for your mainframe software bill. So, refactoring some of your COBOL to Java can help to lower your software bill.

Additionally, moving workload to zIIPs frees up your general-purpose processors to accommodate additional capacity. Many mainframe organizations are growing their workloads year after year, requiring them to upgrade their capacity. But if you can offload some of that work to the zIIP, not only can you use the general purpose capacity that is freed, but if you need to expand capacity you may be able to do it on zIIPs, which are less expensive to acquire than general purpose processors.

It's like Cloudframe is brining cloud economics to the mainframe.

COBOL and Java

CloudFrame refactors Batch COBOL workloads to Java without changing data, schedulers, and other infrastructure (e.g. MQ). CloudFrame is fully automated and seamlessly integrated with the change management systems you use on the mainframe. This means that your existing COBOL programmers can maintain the programs in COBOL while running the actual workloads in Java.

Yes, it is possible to use Cloudframe to refactor the COBOL to Java and then maintain and run Java only. But it is also possible to continue using your existing programmers to maintain the code in COBOL, and then use Cloudframe to refactor to Java and run the Java. This enables you to keep your existing developers while you embrace modernization in a manageable, progressive way that increases the frequency of tangible business deliverables at a lower risk.

An important consideration for such an approach is the backward compatibility that you can maintain. Cloudframe provides binary compatible integration with your existing data sources (QSAM, flat files, VSAM, Db2), subsystems, and job schedulers. By maintaining COBOL and cross-compiling to Java, you keep your COBOL until you are ready to shift to Java. At any time, you can quickly fall back to your COBOL load module with no data changes. The Java data is identical to the COBOL data except for date and timestamp.

With this progressive transformation approach, your migration team is in complete control of the granularity and velocity of the migration. It reduces the business risk of an all-or-nothing, shift-and-lift approach because you convert at your pace without completely eliminating the COBOL code.

Performance is always a consideration with conversions like this, but you can achieve similar performance, and sometimes even better performance as long as you understand your code and refactor wisely. Of course, you are not going to convert all of your COBOL code to Java, but only those applications that make sense. By considering the cost savings that can be achieved and the type of programs involved, cross-compiling to Java using Cloudframe can be an effective, reasonable, and cost-saving approach to application modernization.

Check out their website at www.cloudframe.com or request more information.

Db2 12 for z/OS Function Level 507

This month, June 2020, IBM introduced a new function level, FL507, for Db2 12 for z/OS. This is the first new function level this year, and the first since October 2019. The Function Level process was designed to release Db2 functionality using Continuous Delivery (CD) in short, quick bursts. However, it seems that the global COVID-19 pandemic slowed things a bit… and that, of course, is understandable. But now we have some new Db2 for z/OS capabilities to talk about for this first time in a little bit! 

There are four significant impacts of this new function level:

• Application granularity for locking limits
• Deletion of old statistics from the Db2 Catalog when using profiles
• CREATE OR REPLACE capability for stored procedures
• Passthrough-only expressions with IBM Db2 Analytics Accelerator (IDAA)

Let’s take a quick look at each of these new things.

The first new capability is the addition of application granularity for locking limits. Up until now, the only way to control locking limits was with NUMLKUS and NUMLKTS subsystem parameters, and they applied to the entire subsystem. 

NUMLKTS defines the threshold for the number of page locks that can be concurrently held for any single table space by any single DB2 application (thread). When the threshold is reached, DB2 escalates all page locks for objects defined as LOCKSIZE ANY according to the following rules:

• All page locks held for data in segmented table spaces are escalated to table locks.
• All page locks held for data in partitioned table spaces are escalated to table space locks.

NUMLKUS defines the threshold for the total number of page locks across all table spaces that can be concurrently held by a single DB2 application. When any given application attempts to acquire a lock that would cause the application to surpass the NUMLKUS threshold, the application receives a resource unavailable message (SQLCODE of -904).

Well, now we have two new built-in global variables to support application granularity for locking limits. 

The first is SYSIBMADM.MAX_LOCKS_PER_TABLESPACE and it is similar to the NUMLKTS parameter. It can be set to an integer value for the maximum number of page, row, or LOB locks that the application can hold simultaneously in a table space. If the application exceeds the maximum number of locks in a single table space, lock escalation occurs.

The second is SYSIBMADM.MAX_LOCKS_PER_USER and it is similar to the NUMLKUS parameter. You can set it to an integer value that specifies the maximum number of page, row, or LOB locks that a single application can concurrently hold for all table spaces. The limit applies to all table spaces that are defined with the LOCKSIZE PAGE, LOCKSIZE ROW, or LOCKSIZE ANY options. 

The next new capability is the deletion of old statistics when using profiles. When you specify the USE PROFILE option with RUNSTATS, Db2 collects only those statistics that are included in the specified profile. Once function level 507 is activated, Db2 will delete any existing statistics for the object(s) that are not part of the profile. This means that all frequency, key cardinality, and histogram statistics that are not included in the profile are deleted from the Db2 Catalog for the target object. 

This is a welcome new behavior because it makes it easier to remove old and stale distribution statistics. Keep in mind that this new behavior also applies when you use profiles to gather inline statistics with the REORG TABLESPACE and LOAD utilities.

Another great new capability that stored procedure users have been waiting for for some time now is the ability to specify CREATE OR REPLACE for procedures. This means that you do not have to first DROP a procedure if you want to modify it. You can simply specify CREATE OR REPLACE PROCEDURE and if it already exists, the procedure will be replaced, and if not, it will be created. This capability has been available in other DBMS products that support stored procedures for a while and it is good to see it come to Db2 for z/OS!

Additionally, for native SQL procedures, you can use the OR REPLACE clause on a CREATE PROCEDURE statement in combination with a VERSION clause to replace an existing version of the procedure, or to add a new version of the procedure. When you reuse a CREATE statement with the OR REPLACE clause to replace an existing version or to add a new version of a native SQL procedure, the result is similar to using an ALTER PROCEDURE statement with the REPLACE VERSION or ADD VERSION clause. If the OR REPLACE clause is specified on a CREATE statement and a procedure with the specified name does not yet exist, the clause is ignored and a new procedure is still created.

And finally, we have support for passthrough-only expressions to IDAA. This is needed because you may want to use an expression that exists on IDAA, but not on Db2 12 for z/OS. With a passthrough-only expression, Db2 for z/OS simply verifies that the data types of the parameters are valid for the functions. The expressions get passed over to IDAA, and the accelerator engine does all other function resolution processing and validation. 

What new expressions does FL507 support you may ask? Well all of the following built-in functions are now supported as passthrough-only expressions to IDAA:

• ADD_DAYS
• BTRIM
• DAYS_BETWEEN
• NEXT_MONTH
• Regression functions 
• REGR_AVGX
• REGR_AVGY
• REGR_COUNT
• REGR_INTERCEPT
• REGR_ICPT
• REGR_R2
• REGR_SLOPE
• REGR_SXX
• REGR_SXY
• REGR_SYY
• ROUND_TIMESTAMP (when invoked with a DATE expression)

You can find more details on the regression functions from IBM here. 

Summary

These new capabilities are all nice, new features that you should take a look at, especially if you have applications and use cases where they can help. 

The enabling APAR for FL507 is PH24371. There are no incompatible changes with FL 507. But be sure to read the instructions for activation details and Db2 Catalog impacts for DL 507.