Thursday, June 29, 2017

The DB2 12 for z/OS Blog Series - Part 12: New Built-in Functions

As with most new releases of DB2 for z/OS, at least lately, there are several new built-in functions (or BIFs) that have been added. DB2's BIFs are used to translate data from one form or state to another. They can be used to overcome data format, integrity and transformation issues when you are reading data from DB2 tables. 

So what new things can we do with functions in DB2 12 for z/OS?

The ARRAY_AGG function can be used to build an array from table data. It returns an array in which each value of the input set is assigned to an element of the array. So basically speaking, you can use ARRAY_AGG to read values from rows of a table and convert those values into an array. For example, if I wanted to create an array of name from the EMP table for all females employees I could write it like this:


The new part is the ability to use an associative array aggregation. That means that the ARRAY_AGG function is invoked where there is a target user-defined array data type in the same statement, or the result of the ARRAY_AGG function is explicitly cast to a user-defined array data type.

More details can be found here.

Another new capability comes with the LISTAGG function, which is only available as of function level 501. The LISTAGG function aggregates a set of string values for a group into

one string by appending the string-expression values based on the order that is specified in the WITHIN GROUP clause.

So if I needed to create a list of comma-separated names, in alphabetical order grouped by department I could write:



You can find additional details here.

DB2 12 for z/OS also adds functions for calculating the percentile of a set of values. There are two options:

The PERCENTILE_CONT function returns a percentile of a set of values treated as a continuous distribution. The calculated percentile is an interpolated value that might not have appeared in the input set.

On the other hand, the PERCENTILE_DISC function returns a percentile of a set of values treated as discrete values. The calculated percentile is always a value that appeared in the input set.

Consider the following two statements:


The result here, using the sample data, would be 1968.50. There are an even number of rows, so the percentile using the PERCENTILE_CONT function would be determined by interpolation. The average of the value of the two middle rows (1907.00 and 2030.00) is used.


The same SQL statement but substituting PERCENTILE_DISC for PERCENTILE_CONT would return 1907.00. Again, the example would return 6 rows (an even number) but instead of an average a discrete value is returned; the value of the first of the two middle rows, which is 1907.00.

Another set of new functions give the ability to generate unique values that can be used for keys:
In both cases, the function will return a unique value that includes the internal form of the Universal Time, Coordinated (UTC), and the Sysplex member (for Data Sharing environments). 

For GENERATE_UNIQUE a bit data character string 13 bytes long is returned. That means CHAR(13) FOR BIT DATA.

For GENERATE_UNIQUE_BINARY a BINARY(16) value is returned. Both functions require parentheses without any arguments.

You can use the new WRAP function to obfuscate your database code objects. The function works only on procedural objects (stored procedures, triggers and user-defined functions).

The general idea behind wrapping procedural database objects is to encode a readable data definition statement such that its contents are not easily identified. The procedural logic and embedded SQL statements in an obfuscated data definition statement are scrambled in such a way that any intellectual property in the logic cannot be easily extracted.

A related system stored procedure, CREATE_WRAPPED, is also provided that can be used to obfuscate a readable data definition statement and deploy it in the database. 
Read syntax diagram

More details can be found here and here and here.

Finally, there are a series of new functions for returning hashes. Given an expression, a hash algorithm is applied and the hash value is returned. There are four options:
  • HASH_CRC32
  • HASH_MD5
  • HASH_SHA256
The name of the function determines the hashing algorithm that is used and the data type of the result, as shown in the table below:

BIF Algorithm Data Type


The general advice for every release of DB2 holds for DB2 12: always read through the manuals to find the new functions that can be used to minimize the amount of programming and work that needs to be done. It is important for both DBAs (in order to give good advice and be able to review SQL) and programmers (in order to write efficient and effective SQL) to know what functions are available. Be sure to review the new BIFs in DB2 12 and test them out to see how they work and where they can best be used at your shop!

Monday, June 26, 2017

BMC and CA: Impending Nuptials?

Have you heard the one about BMC Software acquiring CA, Inc.? 

At first glance, to those of us who have been in the business for awhile, that sentence may look odd? Those two have been slugging it out in the mainframe software world for decades now. And to be fair, not just the mainframe world, but also in the distributed world.

But the chatter is out there that this could happen. Read this Reuters article or this Bloomberg article or this one from the Register

The general idea is that BMC and its financial backers are working on a deal to take CA private and combine it with BMC. This would indeed be interesting.

The two companies have competing solutions across the board in many areas, which would make the combination challenging... at least in terms of customer satisfaction. First, the new combined entity (BMCA?) would have to identify all of the competing software products (both companies probably already know this, so no big deal). The next steps are the troubling ones. For each case where there are competing offerings they would have to choose to support both (costly in the long run and not sustainable), choose one to sunset (probably making existing customers not very happy), or combine the best of both products (technologically difficult and I know of no concrete examples where this happened successfully post-acquisition). So there is that to deal with.

Nevertheless, a combined BMC and CA would be a very powerful systems software vendor. But it would come into existence when a lot of software offerings are moving into the cloud. This will be on most analyst's lips if this "merger" moves forward. But it is not a big concern to me as there are still a lot of organizations that rely on systems software (from both BMC and CA) that are not going to move it all to the cloud any time soon.

From a DB2 perspective, the two companies have competing products (and solutions) across all the major categories (fast DB2 utilities, performance management, change management, backup/recovery, and catalog visibility). So if this acquisition happens, it is likely that a whole suite of DB2 for z/OS tooling (that has been "out there" for decades) gets completely eliminated. I'd bet on most of the BMC stuff surviving… not just because BMC would be the "acquirer" but because BMC has been paying more attention to their DB2 product line (lately) than CA. Nevertheless, it’d be sad to see some of the old Platinum stuff retired (e.g. Detector).

The DB2 tools are one component, but not the biggest. Think job scheduling and workload automation, for example. CA has the CA7 and AutoSys product lines for mainframe and distributed; BMC has Control-M. What happens to consolidate these products is anybody's guess?

Two areas without a lot of cross over in the two companies portfolios are help desk and release management. CA probably covets BMC’s help desk (Remedy) and BMC probably covets CA’s software release management (Endevor). But the whole DevOps revolution is impacting the ongoing viability of products like Endevor. Now that is not to say that the market for such tools will disappear overnight, but...

At any rate, I think the hassle would be tremendous for customers as the combined company tries to rationalize its product portfolio. If it goes the traditional BMC route it keeps both sets of products at least for some time; the CA route it lets all products sort of die by attrition over time. The best case scenario would that that a ruthless product-customer-focused view be deployed so that winners in each category are determined with a reasonable conversion plan for customers to switch to whatever wins. I’d be surprised if that happened because in my experience “reason” rarely prevails with an acquisition.

Also, and this is not a minor concern, I’m not sure that this would pass the regulatory anti-trust requirements, but who knows?

I would think that discounting would not be as great in a post-acquisition market because prospects will no longer be able to play one vendor against another. IBM could become more of a viable choice for competing system management software.

What do you think? Should BMC and CA combine together? How would it impact your company if they did? 

Thursday, June 08, 2017

The DB2 12 for z/OS Blog Series - Part 11: Enhanced Support for Arrays

The ARRAY data type was added to DB2 in the last release (Version 11) with the ability to define both ordinary arrays or associative arrays. An ordinary array has a user-defined number of elements that are referenced by their ordinal position in the array. An associative array has no user-defined number of elements that are referenced by the array index value. An associative array’s index values do not have to be contiguous but they are unique. SQL PL variables and parameters for SQL PL routines could be defined as arrays. 

Support for global variables was also added to DB2 11 for z/OS, but they could not be defined as an ARRAY. With DB2 12 for z/OS you can create global variables with an array data type. So the following is now legal as long as you are on V12 or higher:

  CREATE VARIABLE IntgrArrayGV IntgrArray

A data type is defined as an integer array and a global variable is created using that data type.

Additional enhancements for array handling added to DB2 12 include the ability to use the ARRAY_AGG aggregate function to create an associative array... and  you can specify the ORDER BY clause on the ARRAY_AGG aggregate function (as an option). The ARRAY_AGG function enables your programs to utilize arrays without having to code SQL PL in stored procedures or triggers.