Happy Holidays

Just a brief posting today to wish everyone a very happy holiday season.

I am taking some down time thru the end of the year to visit family "up North."

So, until next year, may your databases run without a glitch and here's hoping you all have an enjoyable holiday!

Quick Thoughts on DB2 Performance

Database performance problems are not caused by magic. Indeed, all performance problems are always caused by change. That statement flies in the face of what I normally say, which is “Almost never say always or never”… but in this case, it is true.

Think about it for a moment. If everything remains stable and unchanging in your environment, then why would performance vary? That’s right, it wouldn’t.

Something tangible must change before a performance problem can be experienced. The challenge of performance tuning is to find the source of the change, gauge its impact, and formulate a solution.

Change can take many forms, including the following:

• Physical changes to the environment, such as a new CPU, new disk devices, or different tape drives.
  
• Changes to system software, such as a new release of a product (for example, WebSphere, CICS, or even z/OS), the alteration of a product (for example, the addition of more or fewer CICS regions or an IMS SYSGEN), or a new product (for example, implementation of DFHSM). Also included is the installation of a new release or version of DB2, which can result in changes in access paths as well as utilization of new features.
  
• Changes to the DB2 engine from maintenance releases and PTFs, which can change the optimizer (and sometimes introduce other new functionality).
  
• Changes in system capacity. More or fewer jobs could be executing concurrently when the performance problem occurs. Or additional users may be banging away at your transactions.
  
• Environmental changes, such as the implementation of client/server programs, the adoption of SOA, or other new technologies.
  
• Database changes. This involves changes to any DB2 object, and ranges from adding a new column or an index to dropping and re-creating an object.
  
• Changes to the application development methodology, such as usage of check constraints instead of application logic or the use of stored procedures.
  
• Changes to application code, both SQL and host language code (COBOL, C, Java, etc.).
  

Although the majority of your performance problems are likely to be application-oriented, you must be prepared to explore any and all of these other areas when application tuning has little effect.

My advice is to be sure that you institute strict change control tracking across all areas of your IT infrastructure. That way, whenever you experience a performance problem, you will be able to track what has changed recently, along with who changed it and why. This is important because every DBA knows what the answer to the question “What changed?” will be… right?

It is always “nothing!”

And that cannot be true. Oh, it does not mean that the person answering is lying. He or she may not have changed anything. And it is not necessarily reasonable to expect an application developer to know what all could have changed…especially when what can impact DB2 performance spans so many areas of the IT infrastructure.

So do yourself… and your company a favor: be sure that you meticulously track each and every change to any aspect of your systems. Then – and this is where many shops break down – make sure that you have methods of tying all of the change information together in such a way that it can be queried and examined in the face of a performance problem.

Only then can you reasonably expect your DBAs rapidly to be able to track down and remedy DB2 performance problems… because only then will they have the pertinent information at their disposal.

A Short History of DB2 for z/OS – Part 2

Today’s blog entry is a continuation of yesterday’s post in which we began a brief review of the history of DB2 for z/OS. That post covered Versions 1 through 3; so today we pick up our historical review with Version 4.

Version 4 was a very significant milestone in the history of DB2. It was highlighted by the introduction of Type 2 indexes, which removed the need to lock index pages (or subpages, now obsolete). Prior to V4, index locking was a particularly thorny performance problem that vexed many shops. And, of course, I’d be remiss if I did not discuss data sharing, which made its debut in V4. With data sharing, DB2 achieved new heights of scalability and availability unmatched within the realm of DBMS; it afforded users the ability to upgrade without an outage and to add new subsystems to a group “on the fly.” The new capabilities did not stop with there; V4 also introduced stored procedures, CP parallelism, performance improvements, and more. DB2 V4 was, indeed, a major milestone in the history of mainframe DB2.

In June 1997 DB2 Version 5 became generally available. It was the first DB2 version to be referred to as DB2 for OS/390 (previously it was DB2 for MVS). Not as significant as V4, we see the trend of even numbered releases being bigger and more significant than odd numbered releases (of course, this is just my opinion). V5 was touted by IBM as the e-business and BI version. It included Sysplex parallelism, prepared statement caching, reoptimization, online REORG, and conformance to the SQL-92 standard.

Version 6 brings us to 1999 and the introduction of the Universal Database term to the DB2 moniker. The “official” name of the product is now DB2 Universal Database for OS/390. And the Release Guide swelled to over 600 pages! Six categories of improvements were introduced with V6 spanning:

• Object-relational extensions and active data
• Network computing
• Performance and availability
• Capacity improvements
• Data sharing enhancements
• User productivity

The biggest of the new features were SQLJ, inline statistics, triggers, large objects (LOBs), user-defined functions, and distinct types.

Version 6 is also somewhat unique in that there was this “thing” typically referred to as the V6 refresh. It added functionality to DB2 without there being a new release or version. The new functionality in the refresh included SAVEPOINTs, identity columns, declared temporary tables, and performance enhancements (including star join).

March 2001 brings us to DB2 Version 7, another “smaller” version of DB2. Developed and released around the time of the Year 2000 hubbub, it offered much improved utilities and some nice new SQL functionality including scrollable cursors, limited FETCH, and row expressions. Unicode support was also introduced in Db2 V7. For a more detailed overview of V7 (and the V6 refresh) consult An Introduction to DB2 for OS/390 Version 7.

DB2 Version 8 followed, but not immediately. IBM took advantage of Y2K and the general desire of shop’s to avoid change during this period to take its time and deliver the most significant and feature-laden version of DB2 ever. V8 had more new lines of code than DB2 V1R1 had total lines of code!

I don’t want to get bogged down in recent history here outlining the features and functionality of DB2 releases that should be fresh in our memory (V8 and V9). If you really want some details on those refer to these links for them:

An Overview of DB2 for z/OS Version 8


DB2 9 for z/OS Features


Which brings us to today. Most shops should be either running Version 9 in production or planning their migration from V8 to V9. And we are all waiting with baited breath for DB2 X… which hopefully should be announced sometime next year.

A Short History of DB2 for z/OS – Part 1

Let's go back in time... almost three decades ago... back to the wild and woolly 1980s! And watch our favorite DBMS, DB2, grow up over time.

Version 1 Release 1 was announced on June 7, 1983. And it became generally available on Tuesday, April 2, 1985. I wonder if it was ready on April 1st but not released because of April Fool’s Day? Any old-time IBMer out there care to comment?

Initial DB2 development focused on the basics of making a relational DBMS work. Early releases of DB2 were viewed by many as an “information center” DBMS, not for production work like IMS.

Version 1 Release 2 was announced on February 4, 1986 and was released for general availability a month later on March 7, 1986. Wow! Can you imagine waiting only a month for a new release of DB2 these days? But that is how it happened back then. Same thing for Version 1 Release 3, which was announced on May 19, 1987 and became GA on June 26, 1987. DB2 V1R3 saw the introduction of date data types.

You might notice that IBM delivered “releases” of DB2 back in the 1980s, whereas today (and ever since V3) there have only been versions. Versions are major, whereas releases are not quite as significant as a version.

Version 2 of DB2 became a reality in 1988. Version 2 Release 1 was announced in April 1988 and delivered in September 1988. Here we start to see the gap widening again between announcement and delivery. V2R1 was a very significant release in the history of DB2. Some mark it as the bellwether for when DB2 began to be viewed as a DBMS capable of supporting mission critical, transaction processing workloads. Not only did V2R1 provide many performance enhancements but it also signaled the introduction of declarative Referential Integrity (RI) constraints. RI was important for the acceptance of DB2 because it helps to assure data integrity within the DBMS.

No sooner than V2R1 became GA than IBM announced Version 2 Release 2 on October 4, 1988. But it was not until a year later that it became generally available on September 23, 1988. DB2 V2R2 again bolstered performance in many ways. It also saw the introduction of distributed database support (private protocol) across MVS systems.

Version 2 Release 3 was announced on September 5, 1990 and became generally available on October 25, 1991. Two very significant features were added in V2R3: segmented table spaces and packages. Segmented table spaces have become the de facto standard for most DB2 data and packages made DB2 application programs easier to support. DB2 V2R3 is also the version that beefed up distributed support with Distributed Relational Database Architecture (DRDA). Remote unit of work distribution was not available in the initial GA version, but IBM came out with RUOW support for DB2 V2R3 in March 1992.

And along comes DB2 Version 3 announced in November 1993 and GA in December 1993. Now it may look like things sped up again here, but not really. This is when the QPP program for early support of DB2 started. QPP was announced in March 1993 and delivered to customers in June 1993. Still though, fairly rapid turnaround by today’s standards, right?

V3 greatly expanded the number of bufferpool options available (from 5 pools to 80). There were many advances made in DB2 V3 to take better advantage of the System 390 environment: V3 introduced support for hardware assisted compression and hiperpools. It was also V3 that introduced I/O parallelism for the first time.

We’ll stop here for today and continue our short history of DB2 in my next DB2Portal blog posting. See you soon...

Wordle of my DB2 Portal Blog

The "jumble" of words shown here is a Wordle, which is a "word cloud" of text. I fed my blog location into the Wordle generator and it created this pretty picture based on the words I most commonly use here in this blog.

The cloud gives greater prominence to words that appear more frequently in the source text. No surprise that "DB2" and "data" dominate the other words!