Bind Option Conflicts

Last week I discussed dirty reads and how isolation level (UR) is used to read uncommitted data. In the course of my research for that post, I came across a related topic: bind parameter conflict resolution for isolation and release parameters. That is, if the PLAN and the package are bound with different isolation options, which one will DB2 use during execution?

For an in-depth look at this topic, read the DB2 for z/OS 11.0 Performance Management manual:

The general rule is when a conflict exists between the bind options, DB2 will use the one that holds the locks the longest. So if the options are RELEASE(COMMIT) and RELEASE(DEALLOCATE), DB2 will always select the latter.

With isolation levels, the lock hold time increases as you go from UR to CS to RS to RR.  So if one option is set to UR and another is set to RS, DB2 will use RS. The aforementioned manual includes a table that displays all of the different combinations.

As you can imagine, if you're expecting the isolation to be CS and it suddenly becomes RR, contention problems, slowdowns and even timeouts could result. I wrote this brief SQL statement to help locate these conflicts:

            SELECT SUBSTR(P.CREATOR,1,8) AS CREATOR
                        , SUBSTR(P.NAME,1,8) AS PLAN_NAME
                        , SUBSTR(PK.NAME,1,8) AS PKG_NAME
                        , P.ISOLATION AS P_ISO
                        , PK.ISOLATION AS PKG_ISO
                        , P.RELEASE AS P_REL
                        , PK.RELEASE AS PKG_REL
              FROM SYSIBM.SYSPLAN P
                        , SYSIBM.SYSPACKLIST PKL
                        , SYSIBM.SYSPACKAGE PK
              WHERE   P.NAME = PKL.PLANNAME
                     AND PKL.LOCATION = '*'
                     AND PK.LOCATION =' '
                     AND PKL.COLLID = PK.COLLID
                     AND ( (NOT P.ISOLATION = PK.ISOLATION)
                                                 OR
                                 (NOT P.RELEASE = PK.RELEASE));

If you have your own bind option conflict and choose to use this SQL, please let me know if it helps.

Implementing 'Dirty Reads'

I was recently asked about "dirty reads." This term refers to setting an application's SELECT statement to avoid taking locks while reading and processing uncommitted data.

Of course, the benefit of doing this is to reduce CPU costs and reap potentially significant savings. As the name implies, however, dirty reads comes with a risk. It can lead to failed transactions due to discrepancies in the data. So before implementing dirty reads, you must understand how your data is being used and if discrepancies can be tolerated.

In DB2, locking behavior is controlled through the isolation level used when the statement is executed. I like this statement from the manual, "DB2 for z/OS 11.0: Managing DB2 Performance":

"Various isolation levels offer less or more concurrency at the cost of more or less protection from other application processes."

 The manual also includes a list of isolation levels with descriptions. Again, read more here:

Warning: Dirty reads -- aka the uncommitted reads (UR) isolation level -- should never be used with transactions where the data must always be accurate or up-to-date (e.g., banking transactions or inventory data). To a DBA this may seem obvious, but I've worked at places where some developers, enticed by how fast programs can run at the UR level, decided on their own to apply it to business transactions. As powerful as UR can be, it's simply an unacceptable choice outside of development. CPU savings are not worth jeopardizing customer relationships or compromising your own critical business data.

Do you use dirty reads? When do you find it beneficial? Please share your experiences in comments.

DB2 10-11 Migration: Application and SQL release incompatibilities

I previously noted that DB2 11 allows applications with incompatible SQL or XML to run in compatibility mode.

Here I'll examine a related question: What will cause an application to be incompatible?

IBM provides this information in the DB2 for z/OS Installation and Migration Guide, in the section titled, "Application and SQL release incompatibilities."

A list of potential incompatibilities, along with brief descriptions, follows. See the installation and migration guide to understand how these incompatibilities can impact your applications and how to deal with them.

* Change to determination of ASUTIME for dynamic statements -- In Version 11, new-function mode with application compatibility is set to V11R1, and the dynamic SQL ASUTIME limit for each routine is used by the resource limit facility.

* Automatic rebind of plans and packages created before DB2 Version 9 -- Plans and packages that were last bound before Version 9 are not supported in Version 11 conversion mode and later.

* Change in default for ODBC-limited block fetch initialization keyword -- The default for the LIMITEDBLOCKFETCH initialization keyword is changed in Version 11.

* DB2 objects created with period specifications in Version 10 -- In Version 11, views, materialized query tables and SQL table functions that were created with period specifications in Version 10 are not supported.

* Dropping columns -- In Version 11 new-function mode, columns named CLONE, ORGANIZATION or VERSIONING should be specified as a delimited identifier to be dropped from a table.

* XPath processing -- In DB2 Version 11 new-function mode with application compatibility set to V11R1, XPath is allowed to continue processing even when errors occur on filtered results when using predicate expressions with explicit cast or operations with an invalid value.

* XML document node implicitly added on insert and update -- In DB2 Version 11 new-function mode with application compatibility set to V11R1, if an XML document does not have a document node, then one is added during insert and update operations.

* Client information special registers length -- In DB2 Version 11 new-function mode with application compatibility set to V11R1, special registers for client information fields might return different length values. The values in the CURRENT CLIENT_USERID, CURRENT CLIENT_WRKSTNAME, CURRENT CLIENT_APPLNAME, and CURRENT CLIENT_ACCTNG special registers are determined by the application compatibility level.

* Client information results from ADMIN_COMMAND_DB2 -- In DB2 Version 11 conversion mode, the ADMIN_COMMAND_DB2 result set row returns changes in the created global temporary table SYSIBM.DB2_THREAD_STATUS when processing-type = "THD". The column data type and maximum lengths for WORKSTATION, USERID, APPLICATION and ACCOUNTING change.

* Altering limit keys blocks immediate definition changes -- In DB2 Version 11 new-function mode, if a limit key is altered for certain table space types, immediate definition changes cannot be made until the limit key changes are materialized.

* Removing the SYSPUBLIC schema from the SQL PATH routine option -- Starting in DB2 Version 11 conversion mode, SYSPUBLIC is the schema that is used for public aliases. As such, the SQL PATH routine option must not specify the SYSPUBLIC schema.

* SYSIBMADM schema added to the SQL path -- In DB2 Version 11 new-function mode with application compatibility set to V11R1, SYSIBMADM is added to the SQL path as an implicit schema.

* Change in result for CAST(string AS TIMESTAMP) -- In DB2 Version 11 new-function mode with application compatibility set to V11R1, the result of CAST(string AS TIMESTAMP) is changed in some cases.

* New maximum lengths for values that are returned for some built-in functions -- In DB2 Version 11 new-function mode with application compatibility set to V11R1, the maximum lengths for values that are returned for some built-in functions is decreased.

* Timestamp string representations -- DB2 Version 11 new-function mode with application compatibility set to V11R1 strictly enforces valid string representations of timestamp values.

* SQL reserved words -- Version 11 has several new SQL reserved words. These are listed in reserved words.

 

* Fully qualified user-defined function names -- When using a user-defined function with the same name as a built-in function that has been added to Version 11, the function name must be fully qualified. If the function name is unqualified and SYSIBM precedes the schema used for this function in the SQL path, DB2 invokes one of the built-in functions.

* SQLCODE changes -- Some SQLCODE numbers and message text may change in DB2 Version 11. Also, the conditions under which some SQLCODEs are issued may change.

With earlier DB2 releases, addressing incompatibilities would require substantial changes and testing before the migration could take place. That's the major benefit of DB2 10 compatibility mode. It enables customers to migrate quickly to DB2 11 while maintaining current applications until they can be modified and tested on the new release.

An Update on DB2 11 Manuals

 

A few weeks ago I was searching for the PDF version of the DB2 11 manuals and could only find the online versions on the IBM Information Center website. Personally, I like having the PDF manuals on my laptop. So naturally I was very happy to see the manuals are now available in PDF format at IBM's DB2 for z/OS Technical References site.

 

 

These DB2 11 manuals are available in PDF format:

 

 

 

·         Administration Guide (SC19-4050)          

 

·         Application Programming and SQL Guide (SC19-4051)            

 

·         Application Programming Guide and Reference for Java (SC19-4052)            

 

·         Codes (GC19-4053)                        

 

·         Command Reference (SC19-4054)

 

·         Data Sharing: Planning and Administration (SC19-4055)            

 

·         Installation and Migration Guide (GC19-4056)            

 

·         Internationalization Guide (SC19-4057) 

 

·         Introduction to DB2 for z/OS (GC19-4058)            

 

·         IRLM Messages and Codes for IMS and DB2 for z/OS (GC19-2666)            

 

·         Licensed Program Specifications (GC19-4059)            

 

·         Managing Performance (SC19-4060)      

 

·         Managing Security (SC19-4061)              

 

·         Messages (GC19-4062)                              

 

·         ODBC Guide and Reference (SC19-4063)            

 

·         pureXML Guide (SC19-4064)                    

 

·         RACF Access Control Module Guide (SC19-4065)            

 

·         SQL Reference (SC19-4066)                    

 

·         Utility Guide and Reference (SC19-4067)            

 

·         What's New? (GC19-2985)                        

 

 

In addition, while I was downloading the DB2 11 manuals I decided to check on the DB2 10 manuals. Here are some updates as of October 2013.

 

 

 

Avoiding Lock Contention

Having spent many years tuning batch applications and designing systems that could simultaneously run batch and online applications, I've learned quite a bit about locking and how to avoid it. Here's a presentation I delivered to the Midwest Database User Group (MWDUG) back in 2008. On slide 12 I discuss the lock size needed to improve availability. Then on slide 13 I discuss commit frequencies as another way to improve availability. However, even if you take these steps, if you have a very high transaction rate, you still may experience lock contention.

With this in mind, I'm excited about a DB2 10 feature that's designed to improve throughput and reduce timeouts due to locks being held on rows you're trying to read. The best way to take advantage of this capability is to use the BIND/REBIND command and select the CURRENTACCESSRESOLUTION option (USECURRENTLYCOMMITTED).

Alternatively, this capability can be enabled through a new system parameter, SKIPUNCI (skip uncommitted inserts), which allows committed changes to be read while uncommitted inserts occur. This feature only works with statements that are running with row-level locking and read stability or cursor stability isolation levels.

As you probably know, IBM defaults to existing functionality for all new features, so the default value of SKIPUNCI is NO. The NO setting tells DB2 to wait for a commit or rollback before reading the data. By setting SKIPUNCI to YES, DB2 allows data to be read and ignores insertion of rows that haven't been committed.

This table spells it out. With the NO default, you will wait until a commit. With the YES reset, you will skip uncommitted inserts.

 

SKIPUNCI value              CONCURRENTACCESSRESOLUTION value             Skip or Wait  

YES                  USECURRENTLYCOMMITTED                    Skip uncommitted INSERTs

YES                  WAITFOROUTCOME                           Wait COMMIT or ROLLBACK

YES                  Not specified                            Skip uncommitted INSERTs

NO                   USECURRENTLYCOMMITTED                    Skip uncommitted INSERTs

NO                   WAITFOROUTCOME                           Wait COMMIT or ROLLBACK

NO                   Not specified                            Wait COMMIT or ROLLBACK

 

As is generally the case, it's advisable to test this feature before changing the system default in your production environment.

Craig Mullins has been covering this and other locking-related topics of late. Check out “DB2 Locking Part 7: Lock Avoidance, Related Issues, and Stuff.”

So have you taken advantage of SKIPUNCI? What is your system setting for SKIPUNCI and why is it set to this value? Please share your experiences in comments.

Using EXPLAININPUTSCHEMA

At the beginning of this year, IBM introduced a new bind query parameter, EXPLAININPUTSCHEMA.

Available for DB2 for z/OS 10.0, this parameter is designed to simplify the task of populating EXPLAIN tables that are required for command input. The parameter allows DBAs to create these tables under a separate schema.

Prior to this enhancement, customers had to delete most existing rows in the plan_table while maintaining only those rows needed to be used as input into the BIND QUERY command. Plan_tables normally contain the access path for many different programs, so having to delete this history was a real problem for many who rely on this information.

To take advantage of this enhancement, you need to create a new plan_table using a new schema name. For example, to create a plan_table under schema BINDQ and then copy rows from your existing plan_table to be used as input into BIND QUERY, you'd issue this statement:

BIND QUERY EXPLAININPUTSCHEMA(’BINDQ’)

With this change, the BIND QUERY LOOKUP(YES) must change to recognize the new table. The format to use BINDQ schema is:

BIND QUERY LOOKUP(YES) EXPLAININPUTSCHEMA(‘BINDQ’)

Read more about this parameter in the DB2 10 for z/OS Command Reference.

More on DB2 10 Security

Last week I noted a key new security feature found in DB2 10: increased granularity in DB2 administrative interfaces. As I said though, DB2 10 features numerous security-related enhancements that are designed to control who manages the security, who manages the system and who can access the data. So let's look at some other new features and capabilities.

I would say the most important security-related change with DB2 10 is the separation of DB2 administrative authorities. Prior to DB2 10, the system administrator privilege (SYSADM or INSTALL SYSADM) allowed you to have complete control over installation, configuration and security as well as DB2 object creation and data access. However, with DB2 10, the new system privilege SECADM can be made the only privilege that's authorized to manage all security-related objects and data access -- even though SECADM itself can't access data. This is accomplished by simply setting the SEPARATE_SECURITY system parameter to YES.

As noted last week, another new system privilege -- EXPLAIN -- provides even greater granularity over the use of SQLADM. Users with the EXPLAIN privilege can only use EXPLAIN PLAN or ALL; they're restricted from using STMTCACHE ALL, STMTID, STMTTOKEN, MONITORED STMTS. For a list of further restrictions, see the DB2 10 for z/OS Managing Security manual.

DB2 10 introduced row and column access controls that use SQL to manage access to the table. The row permission uses a database object that describes a specific row access control rule for a table. In the form of an SQL search condition, the rule specifies the conditions under which a user, group or role can access the rows of data in the table. The column access is controlled through a mask, which is a database object that describes a specific column access control rule for a column. In the form of an SQL CASE expression, the rule specifies the conditions under which a user, group or role can receive the masked values returned for a column.

DB2 10 introduces an audit policy that defines a set of criteria to determine the categories to be audited. These categories are:

• CHECKING allows you to determine if a user was denied access through an attempt due to inadequate authority. 

• VALIDATE records when authorities are assigned or switched to another user or a trusted connection.

• OBJMAINT records information related to a table being altered or dropped.

• EXECUTE records SQL statements executed against an audited DB2 table.  (See the manual for limitations.)

• CONTEXT: utility start, objects or phase change or utility end.

• SECMAINT records GRANT/REVOKE security activity.

• SYSADMIN records administrative privilege activity.

• DBADMIN records database administrative privilege activity.

To learn more about managing DB2  security, read these chapters in the manual:

•    Managing access through authorization IDs and roles
•    Managing access through RACF
•    Managing access through row permissions and column masks
•    Managing access through trusted contexts
•    Managing access through data definition control
•    Managing access through exit routines
•    Protecting data through encryption and RACF
•    Auditing access to DB2

If you're on DB2 10 and have taken advantage of some of the new features, please take a moment to share your experience in comments.

Managing Security in DB2 10

DB2 10 is a very significant release in terms of the new security-related features and enhancements it provides. For instance, when I was recently updating my collection of DB2 10 manuals, I scanned the latest version of the DB2 10 for z/OS Managing Security manual.

Here are the subheads for the What’s New in DB2 10 for z/OS chapter:

• Increased granularity in DB2 administrative authorities
• Separation of DB2 administrative authorities
• EXPLAIN system privilege
• Row and column access control
• Audit policy

That's quite a list. Let's dig into the first topic: increased granularity in DB2 administrative authorities. With prior DB2 versions, many enterprises felt they had no choice but to give system privilege (SYSADM) to numerous system users so they could perform various system operations for which they were responsible. SYSADM, of course, is a very powerful authority. It allows complete control over DB2, including system configuration, security management, DB2 objects management, application execution and data access control.

However, with DB2 10, a new set of privileges have been defined to split up the authority by the type of work needing to be done. Here's a quick list. The manual offers extensive details.

SECADM: With this authority, you can manage security-related objects in DB2 and control access to all database resources. However, SECADM doesn't have any inherent privilege to access any user data in those databases.

ACCESSCTRL: With this authority, you can grant explicit privileges to authorization IDs or roles by issuing SQL GRANT/REVOKE statements.

DATAACCESS: With this authority, you can access data in tables, views and materialized query tables in a DB2 subsystem. DATAACCESS also allows you to execute plans, packages, functions, and procedures.

System DBADM: With this authority, an administrator can manage all databases in a DB2 subsystem. By default, the system DBADM has all the privileges of the DATAACCESS and ACCESSCTRL authorities.

SQLADM: With this authority, you can issue the SQL EXPLAIN statements, execute the PROFILE commands and run the RUNSTATS and MODIFY STATISTICS utilities on all user databases. In addition, you can execute system-defined routines such as stored procedures or functions, and any packages that are executed within the routines.

This new granularity solves many management problems. For example, an SQL performance analyst can now be granted the authority to EXPLAIN an application program or SQL statement without having the data access control authority that's included with SYSADM.

Next week I’ll discuss the controls put around data access and the capabilities to audit these controls.

EXPLAIN Tables Need to be Converted Prior to DB2 10 Migration

Traditionally with DB2 upgrades, DBAs haven't had to worry about converting the EXPLAIN tables (including, for example, the user PLAN_TABLE). Of course new columns are added to these tables with each new DB2 release, and DBAs could choose to modify their current tables to accommodate these new columns, but it hasn't been necessary. The EXPLAIN tables could function through the upgrade without any changes. And even without the columns and other enhancements delivered with the latest DB2 release, the tables could still be populated with valid data post-upgrade.

Convenient as this has been, it's changed with DB2 10. It's now necessary to convert EXPLAIN tables prior to migration. While this doesn't require a significant amount of extra work, customers do need to plan for it.

The DB2 install provides instructions on converting existing EXPLAIN tables to the current release format.  If you're migrating to DB2 10 from DB2 version 8, you should convert all the existing EXPLAIN tables to version 8.0 format before beginning the migration. Likewise, if you're migrating from DB2 9, convert the EXPLAIN tables to DB2 9 format before starting the migration. If the EXPLAIN tables aren't converted before migrating to DB2 10, issuing an EXPLAIN will result in an error. For instance, DB2 will return SQLCODE -20008 reason code 2 when performing an EXPLAIN and the EXPLAIN tables are in a pre-Version 8 format.

Once you migrate to DB2 10, you must also ensure the tables are in DB2 10 format and encoded in UNICODE. If your tables are still encoded in EBCDIC, an EXPLAIN will generate an SQLCODE -878 error.

This process is covered in the “Converting EXPLAIN Tables (before migration)" section of the DB2 for z/OS Installation and Migration Guide. Note that there are different sets of instructions when migrating from V8 versus V9.1

The install guide lists some jobs that you can run to help with the conversion. One thing I noticed when reviewing this section: Check any applications you have that may join with the EXPLAIN tables. You may receive different results because the EXPLAIN tables being unicode and your user tables are in EBCDIC.

Have you converted EXPLAIN tables as part of a migration to DB2 10? If so, please share your words of wisdom about this process.

Boosting Big Data Performance

Dave Beulke’s blog post on improving big data performance got me wondering how IBM documents data warehousing performance DSNZPARMs.

I discovered that the "Managing Performance" guide has some good information about data warehousing queries, but nothing about system parameters set in DSNZPARM. The "Installation and Migration" guide has a list of parameters with descriptions, but I couldn't find a group of parameters specific to data warehousing. You just have to go through the list and read each parameter's description to find the ones that influence data warehousing queries.

Fortunately, Dave keeps his own list of data warehousing parameters and suggested settings.

Does your shop have a dedicated set of zparms for tuning DB2 workloads? Please respond in Comments.