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!