Seasons Greetings 2017

It is that time of year again... A time to reflect on the year gone by and to enjoy time with our friends, family and loved ones.  A time to put down the work that consumes us most of the year and to celebrate and enjoy... to remember past years and to consider the upcoming year.  

No matter what holiday tradition you celebrate, I wish you an enjoyable holiday season. Seasons greeting to one and all... and I'll see you next year here on the Db2 Portal blog!

The Db2 12 for z/OS Blog Series - Part 20: Fast Insert: An Alternative INSERT Algorithm

Db2 12 offers many performance improvements that can help you to speed up your applications. The Fast Insert algorithm, also called Insert Algorithm 2, is one such improvement, but you have to understand what it is and when it can be helpful.

The general idea behind this alternative Insert algorithm is to improve performance of Insert-intensive workloads for unclustered data. This is a common requirement for applications that require an audit table or a journal of activities. New data that is inserted is appended to the end of the table with no concern for clustering. A frequent issue with such applications is when the workload is so high that rows cannot be inserted rapidly enough thereby increasing the elapsed time.

Most of these types of applications design the journal/audit table using partitioned table spaces with the MEMBER CLUSTER and APPEND attributes. This design will direct Db2 to insert all new rows at the end of the partition. Insert performance should improve because the space search algorithm can be bypassed for the most part. Nevertheless, some Db2 applications still experienced performance issues even when using this approach.

This bring us to the Db2 12 Fast Insert algorithm. This new algorithm uses an in-memory structure called an insert pipe to speed things up. The insert pipe maintains a list of pages that are available for this member to use for Insert processing for the page set partition. Each member that opens a page set gets an Insert pipe for that member to use. A system agent fills up the pipe asynchronously, making sure that pages are always available for the threads to use for inserting rows.

There is more to the process, but that is the high-level intro to how it work. Now the question is: when will the new algorithm be used?

The Fast Insert algorithm only works with Universal table spaces with the MEMBER CLUSTER option; APPEND is not required. The new algorithm is the default algorithm for this type of table space.

Settings and options are available to control use of the new algorithm. To set usage of the algorithm at a system-wide level, use the DEFAULT_INSERT_ALGORITHM subsystem parameter (DSNZPARM). There are three options:

• 0 indicates that the basic Insert algorithm is to be used no matter what (Insert algorithm 2 is disabled for the subsystem); 
• 1 indicates that the basic insert algorithm is used unless insert algorithm 2 was specified at the table space level; 
• 2 indicates that Insert algorithm 2 is used unless insert algorithm 1 was specified at the table space level.

Which brings us to the DDL options for controlling the insert algorithm at the individual table space level. To do so, use the INSERT ALGORITHM option on the CREATE TABLESPACE or ALTER TABLESPACE statement. Again, there are three options: 

• 0 indicates that the Insert algorithm to be used is as specified by the DEFAULT_INSERT_ALGORITHM subsystem parameter at the time a row is inserted; 
• 1 indicates that the basic Insert algorithm is to be used; and 
• 2 indicates that the Fast Insert algorithm is to be used.

Summing Up

The impact of using the new Fast Insert algorithm will depend on various factors, including whether the table has indexes or not and the specific makeup of your workload. If the workload has lock/latch contentions (on the space map pages and data pages) then the new Insert algorithm will probably be beneficial.

Happy Thanksgiving 2017

Today, November 23rd, in the United States of America, we celebrate Thanksgiving by gathering together with our loved ones and giving thanks for what we have. 

Typically, this involves celebrations with food, traditionally a big turkey dinner with stuffing, mashed potatoes and gravy, as we watch a parade and football games. I plan to follow this tradition to the letter this year and I wish you the same!

And with the general notion of giving thanks on this fine day, I want to also pause and thank each and every one of you for reading my blog, whether just today, or all year long. Hopefully you'll keep coming back and we can keep discussing all things Db2 here.

But for today, wherever you may be, I am thankful for you and I wish you a very happy day!

Oh... and try not to get trampled on Friday if you're going out shopping for bargains!

The Db2 12 for z/OS Blog Series - Part 19: Profile Monitoring Improvements

The ability to monitor Db2 using profile tables is a newer, though by no means brand new capability for Db2 DBAs. You can use profile tables to monitor and control various aspects of Db2 performance such as remote connections and certain DSNZPARMs.

But this blog post is not intended to describe what profile monitoring is, but to discuss the new capabilities added in Db2 12 to enhance profile monitoring.

There are four new enhancements offered by Db2 12 for the use of system profiles.

The first enhancement is the ability to automatically start profiles when you start up a Db2 subsystem. This can be accomplished using a new subsystem parameter called PROFILE_AUTOSTART. Setting the parameter to YES causes Db2 to automatically execute START PROFILE command processing. The default is NO, which means that Db2 will not initiate START PROFILE when the subsystem starts up.

The second improvement is the addition of support for global variables. As of Db2 12 you can specify the following global variables as a KEYWORDS column value in the SYSIBM.DSN_PROFILE_ATTRIBUTES table:

• GET_ARCHIVE
• MOVE_TO_ARCHIVE
• TEMPORAL_LOGICAL_TRANSACTION_TIME
• TEMPORAL_LOGICAL_TRANSACTIONS

If a profile filter matches a connection, Db2 will automatically apply the built-in global variable value to the Db2 process of that connection when the connection is initially established, and when a connection is reused.

Wildcarding support is the third enhancement for profiles in Db2 12. One row for each profile is contained in the SYSIBM.DSN_PROFILE_TABLE. Each column in the table informs Db2 which connection to monitor. Without wildcarding, handling various connections required multiple rows to be defined in the table. But with Db2 12, you can have one row representing more than one connection. Wildcarding is available for AUTHID (authorization IDs), LOCATION (IP addresses of monitored connections), and PRDID (product specific identifier, for example DSN for Db2).

The fourth and final enhancement is for managing idle threads. The MONITOR IDLE THREADS column in the SYSIBM.DSN_PROFILE_ATTRIBUTES table directs DB2 to monitor (for an approximate amount of time) an active server thread’s idle time. The ATTRIBUTE1 column, which is used to specify the type of messages and level of detail of messages issued for monitored threads, has been enhanced to allow the following values: 

• EXCEPTION_ROLLBACK
• EXCEPTION_ROLLBACK_DIAGLEVEL1
• EXCEPTION_ROLLBACK_DIAGLEVEL2 

Note: This particular change to idle threads
for EXCEPTION_ROLLBACK was made available
in Db2 11 after general availability, and will be
available on a Db2 12 system after new function
is activated.

For more details on any of these capabilities, or indeed, on profile monitoring in general, refer to the IBM Db2 12 for z/OS Managing Performance manual, SC27-8857.

Db2 Social Advocate of the Year

A great big thank you to IBM and IDUG for naming me Db2 Social Advocate of the Year for 2017 at the Db2 Awards ceremony at IDUG in Lisbon this year.

I received a nice crystal award and recognition on stage at the event with Surekha, Daniel and Namik.

It is always great to be recognized for my contributions to the Db2 community, but it is something that I do because I love Db2... and have ever since I first used Version 1 back in the 1980's! 

So a big thank you to everyone involved and hopefully I can continue to warrant such accolades throughout the remainder of this year and for many years to come.

Long live Db2!

The Db2 12 for z/OS Blog Series - Part 18: Adaptive Indexes

Have you ever had one of those tough queries that was always a challenge to keep performing well? This type of query usually experiences fluctuating filtering. By that I mean that the filtering can change, sometimes dramatically, between executions of the query.

Some of the things that can cause fluctuating filtering are predicates with ranges that vary, sometimes returning a small subset of rows and sometimes returning everything. You know the type, perhaps there is a BETWEEN clause that can be set and sometimes it is set as BETWEEN 3 AND 5, whereas other times it is set as BETWEEN 0 and 999999. And maybe even sometimes it is set to BETWEEN 3 AND 3 to just search for equality... Or perhaps it is a LIKE clause that sometimes starts with a wildcard ('%').

Well, Db2 12 offers execution time adaptive indexes that allows list-prefetch plans to quickly determine filtering and adjust at execution time as needed. Db2 can do this for static SQL queries even if REOPT(ALWAYS) is not specified. 

Execution time adaptive indexes are not limited to search screening, as described in the previous paragraph. Indeed, any query with a high uncertainty in the optimizer’s estimate can benefit. This includes range predicates, JSON, Spatial, and index on expression queries.

A quick evaluation is performed by looking done at the literals used in the query. Further costlier evaluation of filtering is deferred until after one RID block is retrieved from all participating indexes. This offers a better optimization opportunity while at the same time minimizing overhead for short running queries.

How about some examples of how execution time adaptive indexes work? For an access path that uses list prefetching or a multi-index OR the query can fall back to a table space scan if a large percentage of the data is going to be read. For an access path that uses multi-index AND Db2 can reorder index legs from most to least filtering, as well as providing an early-out for non-filtering legs and fallback to a table space scan if there is no filtering.

If you are interested in tracking when adaptive index processing is utilized, IFCID 125 has been enhanced to track this feature.

The Db2 12 for z/OS Blog Series - Part 17: A New Privilege for UNLOAD

Db2 12 for z/OS introduces a new privilege that, when granted, enables a user to be able to unload data using the DB2 IBM UNLOAD utility. In past releases, the SELECT privilege (or other higher level admin privileges) was required to unload data using the UNLOAD utility. But this was less than desirable.

Why? Well, one reason is that it created a potential security gap. Consider the situation where a table has column masks or row permissions. In such as case, a user with SELECT privilege against the table still might not be able to access all of the rows and columns because of the masks/permissions that are defined. However, the same user with the same privilege set could execute the UNLOAD utility and be able to read all of the data in the table. Such as situation is not ideal and would not pass an audit.

To remove this gap IBM has introduced a new privilege, the UNLOAD privilege. After you move to Db2 12 for z/OS, SELECT authority is no longer enough to be able to unload data. In order to unload data the user must be granted the UNLOAD privilege on that table. The UNLOAD privilege can only be granted on a table; it cannot be granted on an auxiliary table or a view. The UNLOAD privilege is required after you have moved to function level V12R1M500 or higher.

Of course, there is a workaround if you still want to allow users with the SELECT privilege to be able to unload using the UNLOAD utility. This requires setting a DSNZPARM named AUTH_COMPATIBILITY to "SELECT_FOR_UNLOAD". The default for this DSNZPARM is NULL, which means that the UNLOAD privilege is required. 

Regardless of the privilege, keep in mind that tables with multilevel security impose restrictions on the output of your UNLOAD jobs. A row will be unloaded only if the security label of the user dominates the security label of the row. So it is possible that an unload may not actually unload every row in the table. If security label of the user does not dominate the security label of the row, the row is not unloaded and DB2 does not issue an error message.

See You in Lisbon for the EMEA IDUG DB2 Tech Conference!

This year the IDUG EMEA DB2 Tech Conference is being held in Lisbon, Portugal from October 1st through October 5th. Lisbon is a beautiful city with a lot to see and do, so you can plan for a great week of DB2 and analytics education... and then follow it up with a visit to truly remarkable city.

Whether or not you care about vacationing is irrelevant, though, because a week at an IDUG event is always an educational and enjoyable time. If you’ve ever attended an IDUG conference before then you know all about the fantastic educational and entertainment opportunities that IDUG offers. Of course there will be a ton of informative technical sessions on all of the latest and greatest DB2 technologies and features. 

There are a couple of great keynote sessions scheduled this year. Daniel Hernandez, offering leader with IBM Analytics, will discuss Data Without Limits. He’ll talk about how IBM is changing Db2 like never before including the latest news on Db2, Db2 for z/OS and the Db2 Analytics Accelerator, as well as providing demonstrations of new solutions. And the closing keynote is from Jonathan Adam, vice president and general manager of ZSolutions at BMC Software. Jonathan will present Continuing Digital Business Challenges — Finding Value in your data..., an overview of how to achieve business advantage in the digital age.

And then there are those technical presentations delivered by IBMers, consultants, and your colleagues. These are the heart and soul of an IDUG conference. Where else can you find four days and six tracks of data and DB2-related education? Check out the agenda to see what is on tap!

I will be busy this year at IDUG spending time with the DB2 gold consultants, many of whom will be at the conference to present, mingle, and learn.  I will also be delivering a vendor-sponsored presentation for LogOn Software with intriguing title of How to Accelerate DB2 SQL Workloads... Without DB2. Whatever could that mean? You'll have to join me on Monday at 2:00 PM in Room VITIS!

And don't forget to download the mobile app for the conference to help you navigate all the opportunities available to you!

The IDUG DB2 Tech Conference is the place to be to learn all about DB2 from IBMers, gold consultants, IBM champions, end users, and more. With all of this great stuff going on why wouldn't you want to be there!?!?

DB2 for z/OS Coupling Facility Sizing

Just a very brief blog post today to inform you about something that I ran across doing some web research today... and that is a free, web-based Coupling Facility sizing tool from IBM named CFSIZER. 

The CFSIZER tool will connect to your live server to get information. You will need to input some values and some estimation of the type of workload for your system and CFSIZER will output its recommendations.

If you are struggling with your Data Sharing environment this might be a good place to start if you don't have any tools to help you. Here is the link to CFSIZER.

Thanks IBM!

The Db2 12 for z/OS Blog Series - Part 16: Db2 Catalog Availability Improvements

IBM has improved the availability of accessing Db2 Catalog objects when maintenance is being run in Db2 12 for z/OS. This impacts access during CATMAINT and online REORG.

This change is largely being driven by dynamic SQL, which is more prevalent but can cause problems. When dynamic SQL statement is executed, Db2 must dynamically prepares the SQL to determine access paths in order to run it. During this dynamic SQL preparation process, Db2 acquires read claims on a handful of Db2 Catalog table spaces and their related indexes. Additionally, a DBD lock is acquired on the Db2 Catalog database. The DBD lock is needed to serialize catalog operations with CATMAINT and other DDL that may execute against the catalog, because CATMAINT might be making structural changes to the catalog.

Prior to Version 12, the DBD lock and the read claims were released at COMMIT points. All well and good, but for transactions issuing dynamic SQL but not committing frequently, CATMAINT and online REORG on the Db2 Catalog were blocked during that period period of time.

As of Db2 12, DBD locks on the Db2 Catalog and read claims against catalog objects are released as soon as PREPARE statement execution is complete. This will improve availability for CATMAINT and online REORG of Db2 Catalog objects.

The Db2 12 for z/OS Blog Series - Part 15: DSN1COPY and Data Validation Improvements

If you’ve worked with Db2 for z/OS for awhile (note to IBM: I still have a problem with that lower case "b" but I'm trying), particularly as a DBA, you’ve almost certainly had the opportunity to use the DSN1COPY offline utility, sometimes called the Offline Copy utility.

DSN1COPY can be used in many helpful ways. For example, it can be used to copy data sets or check the validity of table space and index pages. Another use is to translate Db2 object identifiers for the migration of objects between Db2 subsystems or to recover data from accidentally dropped objects. DSN1COPY also can print hexadecimal dumps of Db2 table space and index data sets.

Its primary function, however, is to copy data sets. DSN1COPY can be used to copy VSAM data sets to sequential data sets, and vice versa. It also can copy VSAM data sets to other VSAM data sets and can copy sequential data sets to other sequential data sets. As such, DSN1COPY can be used to

• Create a sequential data set copy of a Db2 table space or index data set.
• Create a sequential data set copy of another sequential data set copy produced by DSN1COPY.
• Create a sequential data set copy of an image copy data set produced using the Db2 COPY utility, except for segmented table spaces. (The DB2 COPY utility skips empty pages, thereby rendering the image copy data set incompatible with DSN1COPY.)
• Restore a Db2 table space or index using a sequential data set produced by DSN1COPY.
• Restore a Db2 table space using a full image copy data set produced using the Db2 COPY utility.
• Move Db2 data sets from one disk to another.
• Move a Db2 table space or index space from a smaller data set to a larger data set to eliminate extents. Or move a Db2 table space or index space from a larger data set to a smaller data set to eliminate wasted space.

Given such a wide array of useful purposes you can see how DSN1COPY is an important arrow in a DBA’s quiver… But remember, it is an offline utility, so Db2 is not aware of – or in control of the data that is moving. So if you use it to change data in a production page set data integrity issues can arise. For example, you may get mismatches between the data page format and the description of the format in the Db2 Catalog.

Other types of errors that can ensue when using DSN1COPY include:

• incorrect DBID/PSID/OBID values,
• improper table space layout (for example, using DSN1COPY to copy data from a segmented table space to a partition-by-growth universal table 
• version number and table definition errors

In scenarios where DSN1COPY was not used properly you can encounter invalid data, abends, and storage overlays. Not good!

Thankfully, we get some help in Db2 12 for z/OS though. Improvements to the REPAIR utility make it easier to detect and correct data mismatches. You can use the REPAIR CATALOG utility to fix situations where the column data type or length in the table space differs from the catalog definition for the column. If Db2 can convert from the data type and length in the table space to the data type and length in the column then the REPAIR CATALOG utility enables conversion. The data type or length of the data in the table space will be changed to match the definition in the Db2 Catalog the next time that the data is accessed.

Additionally, we can use the REPAIR CATALOG TEST utility to detect multiple types of data mismatches. All of the following can be detected:

• If a range-partitioned table space indicates absolute page numbering, but the catalog indicates relative page numbering; and vice versa.
• The number of columns in the table space is greater than the number of columns in the catalog definition of the table.
• The column data type or length in the table space differs from the catalog definition for the column.

So Db2 12 makes life a bit easier for those of us who use DSN1COPY and sometimes do not specify the parameters or the data sets exactly perfectly.

The DB2 12 for z/OS Blog Series - Part 14: Improved MERGE SQL Statement

A very common requirement for application developers is to be able to read through new data – from a table, a file or as entered by an end user – and either INSERT the data if it does not already exist or UPDATE data that does exist with new values.

The ANSI SQL standard defines the MERGE statement for this purpose. The purpose of the MERGE statement is to take two “tables” and merge the data into one table. DB2 for z/OS has supported the MERGE statement since Version 9, but it is more functional now as of Version 12.

Prior to DB2 12, the MERGE statement could not accept a table reference as a way of supplying source data. Input to the MERGE can only be a host variable array or a list of values. This limitation caused MERGE to be somewhat lightly implemented.

Well, Version 12 does away with this limitation – and adds even more features. So you can now write a MERGE statement where data from one table is merged with data from another table. Remember, merge takes the data and compares it and when the comparison is matched does one thing… and when the comparison is not matched it does another. So you can UPDATE when matched and INSERT when not matched.

Consider the following SQL:

MERGE INTO EMP Tgt

USING (SELECT EMPNO, FNAME, LNAME, ADDRESS, SALARY FROM NEW_EMP) Src

ON (Tgt.EMPNO = Src.EMPNO)

WHEN MATCHED THEN

  UPDATE SET (Tgt.FNAME, Tgt.LNAME, Tgt.ADDRESS, Tgt.SALARY) =

  (Src.FNAME, Src.LNAME, Src.ADDRESS, Src.SALARY)

WHEN NOT MATCHED THEN

  INSERT (EMPNO, FNAME, LNAME, ADDRESS, SALARY)

  VALUES (Src.EMPNO, Src.FNAME, Src.LNAME, Src.ADDRESS, Src.SALARY)

ELSE IGNORE;

This MERGE statement takes a table containing new/revised employee data and inserts the data when a match is not found and updates the data if it is found. Note that this is a simple MERGE that assumes that all the columns (in this case) are provided if the data is to be updated.

More complex MERGE statements are possible as of DB2 12 because you can now provide additional matching condition options and additional predicates on the matching conditions (instead of just matched/not matched). It is also possible to issue a SIGNAL statement to return an error when a matching condition evaluates to True.

When you use the new functionality of the MERGE statement in DB2 12+, the operations is atomic; this means that the source rows are processed as a set of rows by each WHEN clause. If an error occurs for any source row, processing stops and no target rows are modified.

But the bottom line here is that the MERGE statement has been significantly improved and is a powerful way of processing data using only SQL as of DB2 12 for z/OS. 

The IBM z14: There's a New Mainframe Coming!

Today, July 17, 2017, IBM announced the next iteration of its stalwart mainframe hardware, the IBM z14. The news comes a little more than two years since the previous version, the IBM z13. The hardware will be generally available later this year.

The big news is that IBM delivering pervasive encryption as part of the IBM z14. With pervasive encryption you can encrypt and protect all of your data all the time, at any scale, without having to change any application code or do any development work. That means you can protect all of your data without requiring an interruption to your business.

The IBM z14 features the industry's fastest microprocessor, running at 5.2GHz, and a new scalable system structure that delivers up to a 35 percent capacity increase compared to the previous generation z13. The system can support over 12 billion encrypted transactions per day on a single system.

Other new capabilities of the IBM z14 include up to 32 TB of memory (3x the memory of the z13), three times faster I/O, and a 10x latency reduction with SAN response time using zHyperLink.

IBM also has introduced three new pricing models to help reduce the cost of mainframe computing, specifically for development and testing, new application workloads, and payment processing.

All in all, with the announcement of the IBM z14, IBM is doing what it takes to make the best computing platform even better. I look forward to using the platform and seeing what it can do for high availability, high performance, secure computing!

The DB2 12 for z/OS Blog Series - Part 13: DRDA Fast Load

Have you ever had a situation where you needed to load data into a DB2 table, but the file with the data was not on the mainframe? So you had to PTF that data to the mainframe and then load it.

Well, with DB2 12 for z/OS you get a new capability to load the data to the mainframe without moving the file. The DRDA fast load feature provides you with an efficient way to load data to DB2 for z/OS tables from files that are stored on distributed clients.

The DNSUTILU stored procedure can be invoked by a DB2 application

program to run DB2 online utilities. This means that you can run an online LOAD utility using DSNUTILU. Before loading remote data, you must bind the DSNUT121 package at each location where you will be loading data. A local package for DSNUT121 is bound by installation job DSNTIJSG when you install or migrate to a new version of DB2 for z/OS.

The DB2 Call Level Interface APIs and Command Line Processor have been enhanced to support remote loading of data to DB2 for z/OS. They have been modified to stream data in continuous blocks for loading. This feature is supported in all DB2 client packages. The extraction task for data blocks that passes them to the LOAD utility is 100 percent offloadable to the zIIP, so the process can result in reduced elapsed time.

This capability is available before activating new function.

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:

SET ARRAYNAME = (SELECT LASTNAME FROM DSN8C10.EMP WHERE SEX = 'F'); 

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:

SELECT WORKDEPT,
       LISTAGG(LASTNAME, ’, ’) WITHIN GROUP(ORDER BY LASTNAME)
       AS EMPLOYEES
FROM   DSN8C10.EMP

GROUP BY WORKDEPT;

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:

• PERCENTILE_CONT
• PERCENTILE_DISC

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:

SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY COMM),
FROM   DSN8C10.EMP
WHERE  WORKDEPT = 'E21';

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.

SELECT PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY COMM),
FROM EMPLOYEE
WHERE WORKDEPT = 'E21';

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:

• GENERATE_UNIQUE
• GENERATE_UNIQUE_BINARY

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. 

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_SHA1
• 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
HASH_CRC32		CRC32		BINARY(4)
HASH_MD5		MD5		BINARY(16)
HASH_SHA1		SHA1		BINARY(20)
HASH_SHA156		SHA256		BINARY(32)

Summary

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!

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? 

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 TYPE IntgrArray AS INTEGER ARRAY[5]
  ...
  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.

The DB2 12 for z/OS Blog Series - Part 10: Improved EDM Memory Management

EDM pool management is improved in DB2 12 for z/OS, which is especially beneficial to organizations running many concurrent threads with with large EDM pools specified.

Prior to DB2 12, the EDMDBDC, EDMSTMTC and EDM_SKELETON_POOL DSNZPARM values were used to allocate DBD cache, dynamic statement cache and EDM skeleton pool memory and the values could not be lowered past the specified installation vales.

With DB2 12 this bahvior is modified. No storage is initially allocated for these 3 EDM structures. Instead, storage is allocated by DB2 as it is needed while being used. If more stroage is required When the specified DSNZPARM values are reached then structures that are not currently in use are removed to tkeep the storatge that is in use below the specified values.

This should result in more effective and efficient EDM storage usage and allocation. Some larger shops may see higher real storage usage by DB2.

Hope to See You in Anaheim for the IDUG DB2 Tech Conference!

This year the IDUG North American DB2 Tech Conference is being held in Anaheim, CA from April 30th through May 4th. That means we'll be near Disneyland, so you can plan for a great week of DB2 and analytics education... and then follow it up with a visit to Mickey. 

Whether or not you care about Disneyland is irrelevant, though, because a week at an IDUG event is always an educational and enjoyable time. If you’ve ever attended an IDUG conference before then you know all about the fantastic educational and entertainment opportunities that IDUG offers. Of course there will be a ton of informative technical sessions on all of the latest and greatest DB2 technologies and features. 

There are a couple of great keynote sessions scheduled this year. Mike Gualtieri, an analyst with Forrester Research, will deliver Forrester's view on Big Data, Analytics, and Open Source. And then IBM's Daniel Hernandez will talk about machine learning in another keynote session. I'm looking forward to both of them!

And this year the Data Tech Summit is being held in conjunction with the typical, informative DB2 sessions. The Data Tech Summit offers up content for data architects and data scientists on topics like big data, cognitive computing, Spark, R and analytics. So if you are looking for a little bit more than just DB2 stuff, you can check out the agenda for the Data Tech Summit at this link.

As usual, I will be busy this year at IDUG. I have a session on Thursday (Session A14, 8:00 AM) titled Prest-O Change-O where I will discuss the ins and outs of DB2 for z/OS database change management. Change is a constant and once you create your DB2 environment it is guaranteed that at some point, something will need to be modified. This session will go over the different types of changes, the complexity of some types of changes, and trends and issues impacting change management including DevOps, regulatory compliance and data growth/Big Data. Hope you can join me!

I will also be delivering a vendor-sponsored presentation for LogOn Software with intriguing title of How to Accelerate DB2 SQL Workloads... Without DB2. Whatever could that mean? You'll have to join me on Wednesday at 10:30 AM to find out!

Let's not forget the exhibit hall (aka Solutions Center) where vendors present and demo their products that can help you manage DB2 better. It is a good place to learn about new technology solutions for DB2, but also to hang out and meet with IBMers, consultants, and peers. 

This year I'll be in the CorreLog booth (#107) in the Solutions Center on Tuesday. Be sure to stop by and say hello, take a look at CorreLog's great solution for auditing DB2 for z/OS, and register to win one of my books!

Already that is a lot for one week, but there is more. You can go to full-day education sessions on Sunday (at an additional cost), attend Special Interest Groups (where you can discuss the latest industry trends and topics with other interested technicians), attend Hands-On Labs (delivering working training led by IBM), and even take complementary IBM certification exams. 

And don't forget to download the mobile app for the conference to help you navigate all the opportunities available to you!

The IDUG DB2 Tech Conference is the place to be to learn all about DB2 from IBMers, gold consultants, IBM champions, end users, and more. With all of this great stuff going on why wouldn't you want to be there!?!?