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.