Showing posts with label DB2 10. Show all posts
Showing posts with label DB2 10. Show all posts

Friday, May 01, 2020

Db2 for z/OS and Managing Database Changes - The Recap

During the month of April 2020 I wrote a series of blog posts on the different types of Db2 for z/OS database change management and the things to remember and consider... 

Today, the first day of May, I just wanted to publish a quick recap and links to all of these posts.

So without further ado...

The first post in this series introduced the types of changes and briefly explained the differences at a very high level. It serves as the introduction to the next three parts.

Part 2 examined simple changes, the easiest of the three types of change to implement. These usually just require issuing a simple ALTER to effect database changes.

In the next installment, Part 3 details medium changes, known in the Db2 world as pending changes. Introduced in Db2 10 for z/OS, these require a little bit more work and can only be performed on database objects in Universal table spaces.

And then in the final post, Part 4 takes a look at complex changes. These are the types of changes to database structures that are only supported by dropping and then re-creating the database structure with your required changes. 

If this quick recap whetted your appetite for more details, please take a moment or two to click through each of the links and read the more detailed posts.

And good luck managing your Db2 for z/OS changes!

Thursday, April 02, 2020

A Condensed 35-Year History of DB2 for z/OS (...and Db2 for z/OS)


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

DB2 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? 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 workloads, like IMS was.

Version 1 Release 2 was announced on February 4, 1986 and was released for general availability a month later on March 7, 1986. 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 changes, whereas releases are not quite as significant as a version.

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 significant release in the history of DB2, a bellwether of sorts for when DB2 began to be viewed as capable of supporting mission-critical, transaction processing workloads. Not only did V2R1 provide significant performance enhancements but it also signaled the introduction of declarative Referential Integrity (RI) constraints.

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 quickly became a de facto standard 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).

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 early support program for DB2 started. Early support was announced in March 1993 and delivered to customers in June 1993. V3 greatly expanded the number of buffer pool options available (from 5 pools to 80), and many advances were made for DB2 to take better advantage of the System 390 environment, including support for hardware-assisted compression and hiperpools. It was also V3 that introduced I/O parallelism for the first time.

Version 4 signaled another 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. Data Sharing made its debut in V4, too, and with it, DB2 achieved new heights of scalability and availability allowing users to upgrade without an outage and to add new subsystems to a group “on the fly.” DB2 V4 also introduced stored procedures, as well as CP parallelism.

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, that 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 became 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, network computing, performance and availability, capacity improvements, data sharing enhancements, and 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.

DB2 Version 8 followed, but not immediately. IBM took advantage of Y2K and the general desire of shops 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!

With DB2 9 for z/OS, we drop the “V” from the name. Is that in response to Oracle’s naming conventions? Well, we do add a space between the DB2 and the version number because we don’t want to talk about DB-twenty-nine! A lot of great new functionality comes with DB2 9 including additional database definition on demand capabilities, binary data types, and a lot of new SQL capabilities including OLAP functions and EXCEPT/INTERSECT. But probably the biggest new feature is pureXML, which allows you to store DB2 data as native XML. The XML is stored natively as a new data type that can be searched and analyzed without the need to reformat it. The approach was novel in that it  supports native XML, basically enabling dual storage engines.

And that brings us to DB2 10 for z/OS. This version of DB2 was built to take advantage of many zEnterprise (the latest new mainframe at the time) features to deliver scalability. Examples include improved compression, cache optimization, blades for running the Smart Analytics Optimizer, etc. 

Additional capabilities included many performance improvements (BIND, IN-list, utilities, etc.), hash organized table spaces, high-performance DBATs (DDF threads) forced to use RELEASE COMMIT, parallel index updating, efficient caching of dynamic SQL with literals, temporal data support, safe query optimization, improved access path hints, access to currently committed data, new TIMESTAMP precision and time zones, and buffer pool options for pinning objects in memory.

In October 2013 we got another new version, DB2 11 for z/OS. Click on that link if you want all the details, but some highlights included transparent archiving, global variables, improved SQL PL, APREUSE(WARN), significant utility improvements, DROP COLUMN support, and JSON support with IBM BigInsights.

And that brings us to the present day, with DB2 12 for z/OS as the current (and soon to be only) supported version of Db2. Released for general availability in October 2016, DB2 12 for z/OS abandons the traditional new release cycle that IBM has followed for decades, adopting a new continuous delivery model. New functionality is now delivered in Function Levels (FLs) that are easily applied and delivered much more rapidly than in the past. Indeed, the current Db2 function level is FL506, which means there have been 6 new function levels added since 2016.

Version 12 brought with it a plethora of new capabilities including virtual storage enhancements, optimization improvements, and improved control over the introduction of new SQL capabilities. DB2 12 for z/OS delivered many improvements for both application development and database administration. Examples of new application capabilities include:
  • Additional support for triggers, arrays, global variables, pureXML, and JSON
  • MERGE statement enhancements
  • SQL pagination support
  • Support for Unicode columns in an EBCDIC table
  • Piece-wise deletion of data
  • Support for temporal referential constraint
  • More flexibility in defining application periods for temporal tables
  • PERCENTILE function support
  • Resource limits for static SQL statements
  • Db2 REST services improve efficiency and security
  • DevOps with Db2: Automated deployment of applications with IBM UrbanCode Deploy
Examples of new DBA and SYSADM capabilities include:

  • Installation or migration without requiring SYSADM
  • Improved availability when altering index compression
  • Online schema enhancements
  • Improved catalog availability
  • Object ownership transfer
  • Improved data validation after running DSN1COPY
  • Automatic start of profiles at Db2 start
  • Increased partition sizes and simplified partition management for partition-by-range table spaces with relative page numbering
  • Ability to add partitions between existing logical partitions
  • UNLOAD privilege for the UNLOAD utility
  • Temporal versioning for Db2 catalog tables
  • Statistics collection enhancements for SQL performance    
Of course, these are just some of the V12 improvements; there are many more (as well as all of the Function Level improvements)!

Then sometime in the middle of 2017, IBM decided to change the name of DB2 by making the uppercase B a lowercase b. So now the name of our beloved DBMS is Db2. Nobody has been able to explain to me what the benefit of this was, so don’t ask me!

The Bottom Line

I worked with DB2 way back in its Version 1 days, and I’ve enjoyed watching DB2 grow over its first 35 years. Of course, we did not cover every new feature and capability of each version and release, only the highlights. Perhaps this journey back through time will help you to remember when you jumped on board with Db2 and relational database technology. I am happy to have been associated with Db2 (and DB2) for its first 35 years and I look forward to many more years of working with Db2… 

Monday, July 15, 2013

DB2 Locking, Part 16: Skipping Uncommitted Inserts

DB2 10 for z/OS introduces a new technique for concurrency. As we have discussed previously in this series on DB2 locking, one of the most troublesome problems for DB2 performance analysts is reducing timeouts and lock suspensions.

The CONCURRENTACCESSRESOLUTION parameter can be used to specify a concurrent access resolution option to use for statements in a package when binding your application program.
There are two options. The first, USECURRENTLYCOMMITTED, indicates that DB2 can use the currently committed version of the data for applicable scans when data is in the process of being updated or deleted. Rows that are in the process of being inserted can be skipped. This clause applies only when the isolation level in effect is Cursor Stability (CS) or Read Stability (RS) making skip uncommitted inserts apply. It is ignored for other isolation levels.

The second option is WAITFOROUTCOME, which indicates that applicable scans must wait for a COMMIT or ROLLBACK operation to complete when data is in the process of being updated or deleted. Rows that are in the process of being inserted are not skipped.

The default behavior is WAITFOROUTCOME. Instead, if you choose to specify USECURRENTLYCOMMITTED DB2 will ignore rows that are in the process of being inserted and use only currently committed rows. This might be desirable for highly concurrent web transactions or to mimic the application concurrency settings or behavior of another DBMS.

Another option at your disposal is to specify that uncommitted inserts are to be skipped at the subsystem level (using option 19 on panel DSNTIPB during DB2 installation or by assembling a new DSNZPARM). The SKIPUNCI subsystem parameter specifies whether statements ignore a row that was inserted by another transaction if the row has not if the row has not yet been detected as committed. A newly inserted row can be detected as committed only after the lock held on the row has been released.

There are two options for this parameter:
  • Specifying YES for SKIPUNCI will cause DB2 to behave as though the newly inserted row has not yet arrived and the row is skipped, until the lock held on a newly inserted row is released.
  • SKIPUNCI(NO) indicates that DB2 will wait for the inserted row to be committed or rolled back. It then processes the row if the insert commits, or it moves on to find another row if the insert is rolled back. If a transaction performs one or more inserts, and then spawns a second transaction, specify NO for SKIP UNCOMM INSERTS if the first transaction needs the second transaction to wait for the outcome of the inserts. This is the default value.
By using either of these two methods to skip uncommitted inserts you can improve concurrent access to data as you tune your application’s locking and concurrency requirements.

Monday, October 24, 2011

IBM Information on Demand 2011: Day Two (#IODGC)

As promised, here is the second of my daily blogs from the IOD conference in Las Vegas. Today it was reported that the attendance at the event was the highest ever for an Information On Demand conference; there are more than 11,500 registered attendees.

The second day of the conference is when things really start humming with news and in-depth presentations. The day kicked off with the general session delivered by a host of IBM executives and customers. Big data, business analytics, and gaining insight into data was the theme of the session. The opening session was peppered with lots of interesteing facts and figures. For example, did you know that 90 percent of the world's data was created in just the last two years? Me neither... but there was no attribution to that nugget of information, so...

Other highlights of the day included the announcement of Cognos Mobile for the iPhone and iPad (a free trial is available on the iTune store)… and the other big product focus of the day was IBM InfoSphere BigInsights, a Hadoop-driven big data solution that can process huge amounts of data very quickly and accurately. For more details on that offering check out my Data Technology Today blog where I cover a customer implementation of this solution.

I also had the opportunity to chat with IBM's Bernie Spang, Director of Marketing, Database Software and Systems. We chatted about various things, starting with the uptake of DB2 10 for z/OS. Earlier in the day it was stated that the uptake of V10 has been faster than for V9 and I asked Bernie why that was. His answer made a lot of sense: skip-level migration support coupled with a clear performance boost out-of-the-box without having to change the database or the apps. I asked if he had metrics on how many customers had migrated, but he didn't have access to that. He said he would get back to me and when he does I will share that information with you all.

We also chatted quite a bit about the recently announ ced DB2 Analytics Accelator. Bernie thinks this is probably the announcement he is most excited about. For those of you who haven't heard about this great piece of technology, it is the second iteration of the Smart Analytics Optimizer (but that name is now dead). The DB2 Analytics Accelerator is built on Netezza technology and can be used to greatly improve the performance of DB2 for z/OS analytical queries without changing the SQL or any application code. There are multiple value points but Bernie pointed out the application transparency and the ability to keep the data on the z platform (no movement required) while accelerating the performance of analytical queries.

IBM views the competition as Oracle Exadata and Teradata, which makes sense. I asked Bernie if there were plans to incorporate the Oracle compatibility features of DB2 LUW in a future iteration of DB2 for z/OS, and he said that made sense. Of course, no one from IBM will commit to future functionality of an as yet to be announced  version, but perhaps Vnext??? (that was me speaking there, no Bernie!)

Then I think I blew his mind when I passed a thought of mine past him. With Netezza being used as a component of an accelerator to improve DB2 analytical processing, has IBM given any thought to using IMS as a component of an accelerator to improve DB2's traditional OLTP processing. Not sure if that is even possible, but it should be worth a research project, right? Especially with IBM announcing IMS 12 at the conference today and the IBM boast that IMS 12 can achieve 61,000 transactions per second. That is impressive! But can the mismatch between relational and hierarchical be overcome in a useful manner to do it?

Finally, we chatted about Informix. As a DB2 bigot I am always at a loss for when to direct people to Informix instead of DB2. It just doesn't sound like something I would do! But Bernie offered a brief overview of Informix time series as something unique that certain customers should look into. An Informix customer uses time series for smart meter management of over 100 million smart meters. A month's worht of data - 4 terabytes - can be loaded and processed in less than 8 hours. And some queries perform from 30x to 60x faster.

OK, even to this DB2 bigot that sounds like an impressive capability. Kudos to Informix.

Finally, I'd like to direct my readers over to the video blog that I am hosting in conjunction with SoftBase Systems. I'll be interviewing DB2 luminaries daily, so tune in there at http://www.softbase.com/blog to view each daily submission!

Until tomorrow...

Tuesday, October 18, 2011

DB2 Developer's Guide, 6th edition

I know a lot of my readers are waiting on the updated edition of my book, DB2 Developer's Guide. This short blog post is to let you know that the wait is almost over. The book will be published early next year and is available to be pre-ordered on amazon com.



The book has been completely updated and is now up-to-date with DB2 10 for z/OS. Just think of the things that have been added to DB2 since the last time the book was updated: Universal table spaces, pureXML, SECADM, hashes, new data types, INSTEAD OF triggers, temporal support, and much, much more.

Consider pre-ordering a copy today so you'll get it as soon as it comes off the presses!

Friday, August 12, 2011

Do You Still Use That DB2 Program?

A recurring problem for programmers and IT folks in general is determining whether or not a particular program is still required. As your organization grows and the number of programs increases, keeping track of them all can become quite difficult.

As administration and management burdens increase, a common desire is to get rid of programs that are no longer being used. But it can be difficult to determine which programs are no longer used.

You can always “ask around,” but few IT professionals would be willing to delete anything based on such a general tactic. Another common method is to review performance reports or extracts from a performance warehouse. But perhaps your performance traces are not turned on all the time.

The question is probably more common in DB2 environments because of the plans and packages that consume storage and "sit around" taking up space if their associated program is no longer being used.

Well, for DB2 professionals this type of question becomes easier to answer once you migrate to DB2 10 for z/OS. DB2 maintains a new column, LASTUSED, in the DB2 Catalog. The column exists in both SYSIBM.SYSPACKAGE and SYSIBM.SYSPLAN and is defined as a DATE data type. The date is changed when the package header is requested from EDM. The column is also maintained for triggers and stored procedures. After BIND (REPLACE) this column is reset to the default value (CURRENT DATE).

This is similar to the LASTUSED column added to SYSIBM.SYSINDEXSPACESTATS in DB2 9, which is used to show the last time an index was used.

Of course, you will have to give it some time –- because you might have a program that is used only rarely, yet still used. Most shops have queries and programs that run quarterly, or even annually, but nevertheless are very important. So don’t just start freeing packages a month after you’ve migrated to DB2 10!

But it is good to know that we now have additional usage information at our fingertips in the DB2 Catalog, isn’t it?

Tuesday, July 19, 2011

Updating DB2 Developer's Guide (6th edition)

Just a brief blog post today to inform regular readers who might be concerned that I have not been blogging as frequently as in the past.

I am currently working on updating my book, DB2 Developer's Guide, for DB2 versions 9 and 10. This will be the sixth edition of the book and if all goes as planned, it should be available on IBM Press before the end of the year (2011).

Be sure to keep an eye on this blog and my web site (http://www.craigsmullins.com) for further details about the next edition of the book.

Monday, December 13, 2010

More Indicator Variables Available in DB2 10 for z/OS

As you all should know by now, version 10 of DB2 doe z/OS is generally available and has been for a month or so now. As such, it is probably time that I start to blog about some of the features of the new release. But instead of starting with one of the bigger features, that you already may have heard about, I decided to start with a feature that has flown somewhat under the radar: extended indicator variables.


Those of you who write programs that deal with possibly null results should know what an indicator variable is. Basically, DB2 represents null in a special variable 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). If the indicator variable is less than zero, then the column to which it applies has returned NULL.


DB2 10 for z/OS enhances and extends the concept of an indicator variable so they can be used outside the scope of nullability. Consider the scenario where a program is being written to modify data. There are multiple combinations of columns that may need to be modified based on conditional programmatic processing. Maybe the program is for editing customer data. The customer has multiple columns that could be modified: name, address, telephone number, credit rating, etc. So the programmer codes up an online screen (e.g. CICS) with all of the data that can then be changed by the end user.


But what happens when the end user cracks the enter key to submit the changes? What actually changed and what stayed the same? Does the program check every value on the screen (perhaps hundreds) and build every UPDATE statement iteration for data that might have been changed? Unlikely, since that would require x! statements (where x is the total number of columns). For non-mathematicians a discussion of factorial can be found here (http://en.wikipedia.org/wiki/Factorial).


Yes, there are CICS options to help the programmer determine which values have changed (or simply save and compare). But until now, dealing with all the potential SQL statements could be problematic. Well, DB2 10 indicator variables come to the rescue. As of DB2 10 NFM you can use indicator variables to inform DB2 whether the value for an associated host variable has been supplied or not… and to specify how DB2 should handle the missing value.


This is an extended indicator variable. And it can be applied to host variables and parameter markers. Whether you will use extended indicator variables can be enabled at the package level, by using the EXTENDEDINDICATOR option of the BIND PACKAGE command. You can also enable extended indicator variables on a statement level for dynamic SQL by using the WITH EXTENDED INDICATORS attribute on the PREPARE statement.


How would this work? Well, extended indicator variables can be specified only for host variables that appear in the following situations:

  • The set assignment list of an UPDATE operation in UPDATE or MERGE statements
  • The values list of an INSERT operation in INSERT or MERGE statements
  • The select list of an INSERT statement in the FROM clause
OK, then, how would we use an extended indicator variable? By setting its value to tell DB2 how to proceeed. The following values are available:

  • 0 (zero) or a positive integer: This indicates the first host identifier provides the value of this host variable reference and it is not null.
  • -1, -2, -3, -4, or -6: This indicates a null.
  • -5: If extended indicator variables are not enabled, this indicates a null; otherwise, a value of -5 indicates that the DEFAULT value is to be used for the target column for this host variable.
  • -7: If extended indicator variables are not enabled, this indicates a null; otherwise, a value of -7 indicates that the UNASSIGNED value is to be used for the target column for this host variable (in other words, treat it as if it were not specified in this statement).


For an INSERT, -5 and -7 settings for an extended indicator variable will end up with the same result. This is so because the INSERT statement works by inserting a default value for any column that is missing. On the other hand, for UPDATE and the UPDATE portion of a MERGE, setting the extended indicator variable to -5 leads to the column being update to the default value, but -7 leads to the update of the column not being applied.


With extended indicator variables then, there is no need for the application to re-send a column’s current value, or to know a column’s DEFAULT value. Which should make things easier for developers.

Monday, October 25, 2010

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.

    Wednesday, May 12, 2010

    IDUG Tampa 2010, Day One

    As usual, the North American IDUG conference is proving to be a hectic, yet enjoyable and informative time. The days are packed from morning til evening with technical sessions, networking, and running from here to there and back again.

    Tuesday was the first day for normal IDUG sessions (the day-long seminars were moved to Monday this year), and the day was dominated (for me at least) by DB2 10 sessions. The spotlight session by Jeff Josten was an information-packed 90 minutes overview of DB2 10 that can only be described as drinking from a firehose. Myself and about 200 other curious attendees sat in attention as Jeff discussed the features that back up the themes of Versionn 10, which are efficiency, resilience, and growing new workloads on DB2 for z/OS.

    Jeff didn’t share a GA date for the new version, nor would anyone else from IBM this week, but it has been strongly hinted that it could be before the end of the year (2010).

    The biggest “thing” being touted by IBM about DB2 10 is the performance gains it delivers right out-of-the-box. Jeff discussed IBM’s performance objectives as historically being to deliver less than a 5% performance regression from release to release. But things have perked up recently. For DB2 9, most customers reported no regression or gain out of box. And the new goal is no longer containing regression, but delivering gain. For DB2 10, the expectation is that many customers will reduce CPU time 10% to 20% right out-of-the-box.

    In IBM’s labs, Jeff indicated that the out-of-the-box CPU reduction numbers for traditional workloads are ranging from 5-10% and for newer workloads (e.g. TCP/IP, stored procedures) the improvement is as much as 20% in lab measurements. And when you start using new functionality, you can reasonably expect to see up to 10% CPU reduction. Of course, Jeff was careful to note that these are pre-GA numbers so things could change, even though there is no expectation that they will change.

    Additionally, there is a lot of focus on scalability in DB2 10. Shops can expect to support 5x to 10x more concurrent users, up to 20,000 per subsystem. This is possible due to virtual storage relief: threads have been moved above the bar.

    Jeff went on to cover a lot of additional new functionality to be delivered with DB2 10 including parellel index update during INSERT (which should speed up inserts against tables with multiple indexes), DB2’s usage of 1MB page size (z/OS) in buffer pools, multiple SQL access path and performance improvements, efficient caching of dynamic SQL with literals, LOB streaming between DDF and rest of DB2, Workfile spanned records (PBG), INSERT improvements for UTS, solid state disk monitoring and exploitation, temporal data support, timestamp data type improvements, and more.

    Hash support is particularly interesting. With hashing you can get direct access to data with a single getpage instead of the multi-getpage approach of b-tree indexing. The targeted use case for hashes is for lookup of a row based upon primary key. The hashing algorithm is stored in the DB2 engine. Never fear, though, because you can still define additional indexes on hashed tables and the optimizer will understand and prefer hashed access when it is possible. (I hear the IMS DBAs out there laughing. DB2 DBAs are now going to need to understand space calculations for hash space and what collisions and overflow means.)

    Next up was Roger Miller who covered DB2 10 from a database administration perspective. He began his session by referencing the extra detail that is available in the DB2 10 webcast presentation that Roger did about last month, which is available on the web.

    Roger states that a lot of what is at the heart of DB2 10 is about making things easier for DBAs. And then to prove his point he talked for an hour about all of those things. Highlights included the reduced need for REORG, monitoring enhancements, hashing, and pureXML enhancements for usability, scalability, and performance.

    A particularly interesting point made by Roger is that query parallelism these days is less about decreasing elapsed time and more about the ability to shuttle workload to a zIIP.

    Roger also discussed the ability to skip V9 and go directly from V8 to V10. He also expressed concern that folks who choose to do this not ignore learning all about V9 when they do this. For example, RUNSTATS in V9 had key changes, so shops need to be careful to run RUNSTATS when moving to V10.

    Roger also spoke about the significant changes to the DB2 Catalog and DB2 Directory in DB2 10. There are about 60 new table spaces, the links have been removed, inline LOBs are used in many places, and row level locking is used. These changes mean that online REORG works for everything in the catalog & the directory.

    He also spoke about the various improvements to security administration in DB2 10. There is a new SECADM authority with no access to data and there is also a new option for DBADM without data access. Another nice new option is DBADM authority for every database in the subsystem. And then there is the ability to REVOKE without cascading, something that DB2 security administrators have been looking for for years!

    Changing pace, I attended Billy Sundarrajan’s presentation on “De-mystifying JDBC Universal Drivers – for the z/OS DBA.” The reality is that more and more dynamic SQL applications are being implemented, so knowing about JDBC drivers is a necessity, not a luxury for the mainframe DBA.

    Billy discussed the types of JDBC drivers and the installation issues involved. You can connect using a type 2 or type 4 driver. The Type 2 driver connects directly without DB2 Connect gateway; Type 4 driver connects thru DB2 Connect gateway.

    He also discussed the benefits of setting end user variables for monitoring and the different properties that can be used for configuration.

    Of course, I attended a few other sessions and spent some time at the exhibit hall and caught up with some old friends and… well, this is long enough of a post for the first day… check back tomorrow for a shorter (I promise) synopsis of day two.

    Tuesday, February 09, 2010

    IBM Announces DB2 10 for z/OS Beta Program

    IBM announced the beta program for the next version of DB2 today, now "officially" known as DB2 10 (no more DB2 X). It is a closed beta program that will begin on March 12, 2010. That means you have to be selected by IBM to participate.

    The announcement highlighted some of the areas of improvement to be delivered by DB2 10 for z/OS, and at the top of that list, to no one's surprise, is performance. DB2 10 promises to deliver out-of-the-box savings by improving operational efficiencies ranging from 5% to 10% out-of-the-box CPU savings for traditional workloads and up to 20% out-of-the-box CPU savings for nontraditional workloads.

    Other areas called out by IBM in the announcement include
    • Improved business resiliency through scalability improvements and fewer outages (planned or unplanned).
    • Schema evolution or data definition on demand as well as query performance manageability enhancements support improved availability.
    • New features such as hash access, index include columns, inline large objects, parallel index updates, faster single row retrievals, work file in-memory, index list prefetch, 64-bit memory enhancements, use of the 1 MB page size of the System z10, buffer pools in memory, access path enhancements, member clustering for universal table spaces, efficient caching of dynamic SQL statements with literals, improved large object streaming, and SQL procedure language performance.
    • Rapid application and warehouse deployment for business growth including improved concurrency for data access, data management, and data definition.
    • The ability avoid an outage by adding active log data to a subsystem.
    • Improved application and data warehousing support including temporal data, a 64 bit ODBC driver, currerntly committed locking, implicit casting or loose typing, timestamp with time zone, variable timestamp precision, moving sum, and moving average.
    • Improvements to DB2's XML support including expanded pureXML, customer-driven performance and usability requirements, schema validation in the engine, binary XML exchange format, multiversioning, easy update of subparts of XML document, stored procedures, user-defined functions and triggers, XML index matching with date/timestamp, and a CHECK XML utility.
    • Enhanced query and reporting facilities, including QMF V10 with over 140 new analytical functions, support for HTML, PDF, and Flash reports, and more.
    So it would seem that there is a lot of new functionality for us to begin to become acquainted with. As IBM rolls out more details, and customers begin to use the new version of DB2, we will examine some of these new features in more depth here on the DB2 Portal blog.

    If you are interested in the beta program, the pre-requisite for DB2 10 is z/OS V1.10 (5694-A01) or later running in 64 bit mode. More information about the DB2 10 beta program is available on IBM's web site.

    No GA date for DB2 10 has been announced.