DB2 10 Technical Overview at IOD Conference

Today I attended the IOD conference and had the opportunity to listen to Jeff Josten present an technical overview of DB2 10 for z/OS. Even though information and specifications have been trickling out on DB2 10 for z/OS over the course of the past year or so, this is the first DB2 10 presentation I have attended subsequent to the GA announcement IBM made last week (announced October 19th, General Availability on October 22nd, 2010). So I’m fairly certain that everything Jeff will talk about will be officially part of DB2 10, instead of just the rumors, hints and allegations proffered prior to the GA announcement. I don’t think anything to be covered will surprise me, but we’ll see.

Jeff started off saying that the technical strategy for DB2 10 was four-pronged:

Continuous availability

Performance and scalability

Ease of management

Advance application features

One of the key value propositions is the deep synergy with the System z hardware. Because the code does not need to be ported all over the place, it can take advantage of the hardware capabilities that bring improved performance and efficiency.

DB2 10 is being promoted as delivering 5 to 10 percent CPU batch and transaction performance improvement out-of-the-box; 20 percent for new workloads. And then an additional 10 percent when you start using new features. This seems to be the high-level talking point for DB2 10 – but that is okay, it is a very good one!

DB2 V8 will go out of service April 2012. But with the ability to go staright from V8 to 10, I’m betting a lot of V8 shops will skip 9 altogether and go right to 10. But it looks like you have just under 2 years to get off of V8, though.

DB2 10 takes advantage of many zEnterprise (the new mainframe announced a couple of months ago) features to deliver scalability. Examples include improved compression, cache optimization, blades for running the Smart Analytics Optimizer, etc.

Jeff mentioned that we’ll be able to support 10 times more users by avoiding memory constraints in DB2 10. That is a big scalability improvement!

DB2 10 went through the largest beta ever: 23 customers and more than 80 vendors. The focus was on testing production level workloads to ensure that the release is stable. That is different than past betas where the focus was on testing new features. And 22 of the beta customers are planning to go into production with DB2 10 next year. Impressive!

Before diving into the technical details, Jeff mentioned that not much has changed versus what IBM has been talking about over the past months. A couple late add features include hash performance, BIND performance, REBIND not required for packages flagged as private protocol (but they will fail if they actually use private protocol), and a new ZPARM for default SEGSIZE for DDL compatibility.

Post GA delivery items include APREUSE and APCOMPARE (for reusing access paths instead of using “hints”) because beta testing exposed some quality items, the ability to delete a data sharing member, inline LOBs for SPT01, online REORG concurrency for materializing deferred ALTERs, and some temporal enhancements (e.g. TIMESTAMP WITH TIMEZONE support).

High performance DBATs (DDF threads) were forced to be RELEASE COMMIT. This gave good storage usage but at the expense of CPU for releasing resources at COMMIT and putting the thread back on the queue. For DB2 10, customers can use RELEASE DEALLOCATE which will keep the thread assigned to the distributed requestor so the next time he comes in he can reuse the thread. This is a nice feature for shops with heavy distributed usage.

Another nice thing right out of the box is parallel index updating at INSERT. This used to be synchronous, each index modified one after the other. Now, the indexes can be modified in parallel, which should be a nice performance improvement for many shops!

There is also a new buffer pool option for a “fully in memory” object for reading the data and pinning it in buffers. Don’t know about you, but I’ve been wanting that for years.

Things that require a REBIND will include most access path enhancements, query parallelism improvements, IN list performance improvements, and Stage 2 predicates being pushed to Stage 1.

Things that require NFM include DB2 Catalog concurrency, compress on insert capability, most utility enhancements, LOB streaming between DDF and the rest of DB2, INSERT improvements for universal table spaces, faster FETCH and INSERT with lower virtual storage consumption, SQL Procedure Language performance improvements, efficient caching of dynamic SQL with literals, as well as a few other “things.”

And then there are things that require NFM and DBA work, such as hashing, index include columns, inline LOBs, DEFINNE NO for LOB and XML columns, MEMBER CLUSTER for universal table spaces, and online REORG for all DB2 Catalog and Directory table spaces.

So it looks like our favorite DBMS is continuing to grow and expand offering high performance functionality and features that are unparalleled in the industry. Indeed, there is a lot of great and exciting new “stuff” on the way in DB2 10.

Null Follow-up: IS [NOT] DISTINCT FROM

After publishing the last blog post here on the topic of pesky problems that crop up when dealing with nulls, I received a comment lamenting that I did not address the IS [NOT] DISTINCT FROM clause. So today’s blog post will redress that failure.

First of all, IS [NOT] DISTINCT FROM is a relatively new predicate operator, introduced to DB2 for z/OS in Version 8. It is quite convenient to use in situations where you are looking to compare to columns that could contain NULL.

Before diving into the operator, let’s first discuss the problem it helps to solve. Two columns are not equal if both are NULL, that is because NULL is unknown and a NULL never equals anything else, not even another NULL. But sometimes you might want to treat NULLs as equivalent. In order to do that, you would have to code something like this in your WHERE clause:

WHERE COL1 = COL2
OR (COL1 IS NULL AND COL2 IS NULL)

This coding would cause DB2 to return all the rows where COL1 and COL2 are the same value, as well as all the rows where both COL1 and COL2 are NULL, effectively treating NULLs as equivalent. But this coding although relatively simply, can be unwieldy and perhaps, at least not at first blush, unintuitive.

Here comes the IS NOT DISTINCT FROM clause to the rescue. As of DB2 V8, the following clause is logically equivalent to the one above, but perhaps simpler to code and understand:

WHERE COL1 IS NOT DISTINCT FROM COL2

The same goes for checking a column against a host variable. You might try to code a clause specifying WHERE COL = :HV :hvind (host variable and indicator variable). But such a search condition would never be true when the value in that host variable is null, even if the host variable contains a null indicator. This is because one null does not equal another null - ever. Instead we’d have to code additional predicates: one to handle the non-null values and two others to ensure both COL1 and the :HV are both null. With the introduction of the IS NOT DISTINCT FROM predicate, the search condition could be simplified to just:

WHERE COL1 IS NOT DISTINCT FROM :HV :hvind

Null Troubles

A null represents missing or unknown information at the column level. A null is not the same as 0 (zero) or blank. Null means no entry has been made for the column and it implies that the value is either unknown or not applicable.

DB2 supports null, and as such you can use null to can distinguish between a deliberate entry of 0 (for numerical columns) or a blank (for character columns) and an unknown or inapplicable entry (NULL for both numerical and character columns).

Nulls sometimes are inappropriately referred to as “null values.” Using the term value to describe a null is inaccurate because a null implies the lack of a value. Therefore, simply use the term null or nulls (without appending the term “value” or “values” to it).

DB2 represents null in a special “hidden” column known as an indicator. An indicator is defined to DB2 for each column that can accept nulls. The indicator variable is transparent to the end user, but must be provided for when programming in a host language (such as COBOL or PL/I).

Every column defined to a DB2 table must be designated as either allowing or disallowing nulls. A column is defined as nullable – meaning it can be set to NULL – in the table creation DDL. Null is the default if nothing is specified after the column name. To prohibit the column from being set to NULL you must explicitly specify NOT NULL after the column name. In the following sample table, COL1 and COL3 can be set to null, but not COL2, COL4, or COL5:

CREATE TABLE SAMPLE1
  (COL1   INTEGER,
   COL2   CHAR(10) NOT NULL,
   COL3   CHAR(5),
   COL4   DATE     NOT NULL WITH DEFAULT,
   COL5   TIME     NOT NULL);

What Are The Issues with Null?

The way in which nulls are processed usually is not intuitive to folks used to yes/no, on/off, thinking. With null data, answers are not true/false, but true/false/unknown. Remember, a null is not known. So when a null participates in a mathematical expression, the result is always null. That means that the answer to each of the following is NULL:

• 5 + NULL
• NULL / 501324
• 102 – NULL
• 51235 * NULL
• NULL**3
• NULL + NULL
• NULL/0

Yes, even that last one is null, even though the mathematician in us wants to say “error” because of division by zero. So nulls can be tricky to deal with.

Another interesting aspect of nulls is that the AVG, COUNT DISTINCT, SUM, MAX, and MIN functions omit column occurrences set to null. The COUNT(*) function, however, does not omit columns set to null because it operates on rows. Thus, AVG is not equal to SUM/COUNT(*) when the average is being computed for a column that can contain nulls. To clarify with an example, if the COMM column is nullable, the result of the following query:

    SELECT  AVG(COMM)
    FROM    DSN8810.EMP;

is not the same as for this query:

    SELECT  SUM(COMM)/COUNT(*)
    FROM    DSN8810.EMP;

But perhaps the more troubling aspect of this treatment of nulls is “What exactly do the results mean?” Shouldn’t a function that processes any NULLs at all return an answer of NULL, or unknown? Does skipping all columns that are NULL return a useful result? I think what is really needed is an option for these functions when they operate on nullable columns. Perhaps a switch that would allow three different modes of operation:

1. Return a NULL if any columns were null, which would be the default
2. Operate as it currently does, ignoring NULLs
3. Treat all NULLs as zeroes

At least that way users would have an option as to how NULLs are treated by functions. But this is not the case, so to avoid confusion, try to avoid allowing nulls in columns that must be processed using these functions whenever possible.

Here are some additional considerations regarding the rules of operation for nulls:

• When a nullable column participates in an ORDER BY or GROUP BY clause, the returned nulls are grouped at the high end of the sort order.
• Nulls are considered to be equal when duplicates are eliminated by SELECT DISTINCT or COUNT (DISTINCT column).
• A unique index considers nulls to be equivalent and disallows duplicate entries because of the existence of nulls, unless the WHERE NOT NULL clause is specified in the index.
• For comparison in a SELECT statement, two null columns are not considered equal. When a nullable column participates in a predicate in the WHERE or HAVING clause, the nulls that are encountered cause the comparison to evaluate to UNKNOWN.
• When a nullable column participates in a calculation, the result is null.
• Columns that participate in a primary key cannot be null.
• To test for the existence of nulls, use the special predicate IS NULL in the WHERE clause of the SELECT statement. You cannot simply state WHERE column = NULL. You must state WHERE column IS NULL.
• It is invalid to test if a column is <> NULL, or >= NULL. These are all meaningless because null is the absence of a value.

Examine these rules closely. ORDER BY, GROUP BY, DISTINCT, and unique indexes consider nulls to be equal and handle them accordingly. The SELECT statement, however, deems that the comparison of null columns is not equivalence, but unknown. This inconsistent handling of nulls is an anomaly that you must remember when using nulls.

Here are a couple of other issues to consider when nulls are involved.

Did you know it is possible to write SQL that returns a NULL even if you have no nullable columns in your database? Assume that there are no nullable columns in the EMP table (including SALARY) and then consider the following SQL:

SELECT SUM(SALARY)
FROM   EMP
WHERE  DEPTNO > 999;

The result of this query will be NULL if no DEPTNO exists that is greater than 999. So it is not feasible to try to design your way out of having to understand nulls!

Another troubling issue with NULLs is that some developers have incorrect expectations when using the NOT IN predicate with NULLs. Consider the following SQL:

SELECT C.color
FROM   Colors AS C 
WHERE  C.color NOT IN (SELECT P.color 
                       FROM   Products AS P);

If one of the products has its color set to NULL, then the result of the SELECT is the empty set, even if there are colors to which no other product is set.

Summary

Nulls are clearly one of the most misunderstood features of DB2 – indeed, of most SQL database systems. Although nulls can be confusing, you cannot bury your head in the sand and ignore nulls if you choose to use DB2 as your DBMS. Understanding what nulls are, and how best to use them, can help you to create usable DB2 databases and design useful and correct queries in your DB2 applications.

A Recommended New DB2 Book

Judy Nall has performed a much-needed service for the DB2 for z/OS community by writing her new book, DB2 9 System Administration for z/OS: Certification Study Guide. There are many DB2 for z/OS books (heck, I wrote one myself) that cover programming, performance, and database administration details. But never before has there been one that focused on system administration and system programming.

Of course, the book is targeted at those looking to become an IBM Certified System Administrator for DB2 for z/OS. I have never taken the exams required for that certification, but the material in this book will go a long way toward making you a better system programmer for a mainframe DB2 environment.

Whereas some of the material can be found in greater detail in other books on the market, we must keep in mind that target market for the book. And the coverage of DB2 fundamentals and performance is well-written and hits the mark for systems folks. And the chapters on installation and migration, system backup and recovery, and systems operation and troubleshooting offer great systems-level knowledge not found in other DB2 for z/OS books.

So while DB2 9 System Administration for z/OS: Certification Study Guide is not for everyone, the people that it is for (systems programmers and systems DBAs) should enjoy it and benefit from the nice job Judy has done organizing and explaining the details of system administration for DB2 for z/OS.

Free DB2 Education Webinar Series

Want to learn more about DB2 for z/OS but there is no money in the education budget? Can you spare an hour a week over the course of a month? Well then, you are in luck because SoftwareOnZ is sponsoring a series of DB2 webinars presented by yours truly, Craig S. Mullins…

Each webinar will be focused on a specific DB2 topic so you can pick and choose the ones that are most interesting to you – or attend them all and receive a certificate signed by me indicating that you have completed The DB2 Education Webinar Series.

The schedule and topics for these sessions follows:

September 28, 2010 – DB2 Access Paths: Surviving and Thriving

Binding your DB2 programs creates access paths that dictate how your applications will access DB2 data. But it can be tricky to understand exactly what is going on. There are many options and it can be difficult to select the proper ones… and to control when changes need to be made.

This presentation will clarify the BIND process, enabling you to manage DB2 application performance by controlling your DB2 access paths. And it will introduce a new, GUI-based product for managing when your programs need to be rebound.

October 5, 2010 – Optimizing DB2 Database Administration

DB2 DBAs are tasked with working in a complex technological environment, and as such, the DBA has to know many things about many things. This makes for busy days. How often have you asked yourself, “Where does the time go?”

Well, the more operational duties that can be automated and streamlined, the more effective a DBA can be. This presentation will address issues that every DB2 Database Administrator and/or DB2 Systems Programmer faces on a daily basis. And it will introduce a new tool, DB-Genie, that will reduce the amount of time, effort, and human error involved in maintaining DB2 databases.

October 12, 2010 – DB2 Storage: Don’t Ignore the Details!

For many DB2 professionals, storage management can be an afterthought. What with designing, building, and maintaining databases, assuring recoverability, monitoring performance, and so on, keeping track of where and how your databases are stored is not top of mind. But a storage problem can bring your databases and applications to a grinding halt, so it is not wise to ignore your storage needs.

This presentation will discuss the important storage-related details regarding DB2 for z/OS, including some of the newer storage options at your disposal. And we will also introduce a new web-based tool for monitoring all of your mainframe DB2 storage.

October 19, 2010 – The DB2 Application Developer’s Aid de Camp

Building DB2 application programs is a thankless job. And it can be difficult to ensure that you have a effective and efficient development environment for coding DB2 applications. Can you easily identify which tables are related to which… and what indexes are available so you code queries the right way the first time? Do you have the right data to test your programs? Can you make quick and dirty changes to just a few tables or rows without having to write yet another program?

This presentation will discuss the issues and difficulties that developers encounter on a daily basis as they build DB2 applications… and it will present a useful programmer-focused toolset for overcoming these difficulties.

Summary

Certainly there will be something of interest for every DB2 professional in at least one, if not all, of these complimentary web-based seminars.

So what’s stopping you? Sign up today!