Some of the new tablespace partitioning choices and features in DB2 Version 8 and now DB2 Version 9 for z/OS that I talked about last week are getting extra enhancements.  During the recent IBM System Z Summit road show that was in Washington DC last week, a number of other tablespace enhancements were discussed that are making their way back into Version 8.

Within the IBM Z Summit, there are not only great presentations on DB2 but also Rational Application Developer, CICS, WebSphere and other great System Z products that continue to make them the lowest cost of ownership products across the computing spectrum.  If you have not seen the agenda yet, go here (http://www-01.ibm.com/software/os/systemz/summit/index.html) to see all the great presentations.

During the DB2 presentations, the talk about current technology, its enhancements and the new DB2 Version X for z/OS was very interesting.  More great features are making it into DB2 and some are making all the way back into DB2 Version 8 through the APAR maintenance stream.

One great enhancement is the exposure of the ability to possibly compress SPT01.  This is coming into DB2 by exposing a DB2 Version 8 and DB2 Version 9 for z/OS zParm.  This zParm is exposed through APAR PK80375 and has a number of considerations especially for data sharing environments that are storage constrained.  This might help your system perform better and relieve some of the memory challenges on your system.

Other Z Summit presentation discussions talked about the reordered row format (RRF) feature and recommended applying APARs PK78958, PK78959, PK79127, PK87348 and PK85881.  The RRF feature is great feature to help minimizing the logging within the tables with VARCHAR columns.  This can be especially beneficial for some of the large software packages that have many VARCHAR(1) column definitions.

Other presentations within the Z Summit talked about a number of great features coming and also other APARs that might help performance for other situations.  They were:

• APAR PK700600 to separate the DGTTs from competing with DB2 Sort processes within the Workfiles for secondary extents within the TEMP database.
• APAR PK83992 is great for speeding up unloads that use File Reference Variables for unloading LOB data.
• APARs PK81471, PK81470 and UK41212 for the APPEND process.  This can be a great option for quickly adding data or LOBS into a table and then reclaiming the space through a REORG.
• APAR PK49265 helps the optimizer prune “Always False” predicates.  Within SQL sometimes developers code deliberate false statements within their SQL WHERE clauses.  This APAR helps the optimizer understand the SQL better and sometimes allows index access.  Check this one out if your developers commonly rely on this bad habit of coding false WHERE predicates too much.

• APAR PK59731, PK51734 and PK76100 help also with SQL optimization.  These APARs help with picking the best SQL access path such as favoring index usage, index-only access and Pair Wise Star Join methods over less efficient index and data access methods.  These APARs and their impact can help DB2 choose the best access path and the best indexes that dramatically speed up your processing.

Of course, some of these are older APARs and there may be a number of considerations with all of them, so research if your system needs them before putting them on your system.

It is always great to see that IBM continues to make DB2 the best performing database any way they can.  Even though it is somewhat difficult through the APAR process, we all appreciate every performance item IBM can help us get into our systems.

Some of the new tablespace partitioning choices and features in DB2 Version 8 and Version 9 for z/OS are making everyone realize there are a number of table design choices to fit any application access type.

In DB2 V8, the first of the tablespace definition partitioning options were enhanced with partitioning and clustering being separate options.  This enhancement brought out the options for the table clustering to be one way and partitioned in another direction.  For example, the separation of partitioning and clustering lets you quickly partition by time and have the clustering be by customer id within each partitioning.  This separation of clustering and partitioning provides a variety of choices and solutions to tough application access designs.

Now with the DB2 V9 for z/OS, the Universal tablespace and the partition by growth (PBG) tablespace provide even more design enhancements, flexibility and manageability.  The PBG and Universal tablespaces have become the defacto standards for software packages where the table population is unknown and the number of tables is too big for detailed analysis until the application usage trends are fully realized.  The PBG allows safety of operation since it can expand to match the application needs.  These options let the DBA define the tablespace for the variety of software packages, let them grow and provide good performance by separating the I/O into many partitions.

The partition-by-growth PBG tablespace provides even more flexibility by, as the name implies, growing a new partition as the number of rows increase in the table.  This is great for robust applications that are continuing to grow their rows.  The PBG tablespace is also great for segmenting these new rows away from the old rows within the tablespace.  This can be a tremendous benefit for focusing the  I/Os within the system into a portion of the tablespace and avoiding the scan of an entire tablespace.  The focusing of the I/OS also enhances the benefits from the DB2 buffer pool and allows more focused data and index entries to be cached within this vital memory resource.

Each tablespace type has different number of data sets and different size parts where DB2 I/O and extent management need analysis to be properly optimized for the applications.  With all these tablespace design choices, make sure to leave analysis time to pick the best configuration and optimize your tablespace choice for the best application performance.

Some of the new performance features in DB2 Version 9 for z/OS are built into the software.  To leverage some of these functions and features, system maintenance DBAs and application DBAs need to coordinate their efforts to realize the benefits.

A couple of these enhancements that are related to new DB2 9 for z/OS zParms are the “support for optimistic locking” and “skipping locked rows” options.  These two features highlight the new enhancements that IBM is implementing in DB2 to address application locking issues.  

Locking always becomes a problem for databases that are extended for new applications other than their original purpose.  Also the latest .NET and Java application sometimes are challenging when they try to cache too much data.

The use of these two new performance enhancements built into DB2 9 for z/OS have some restrictions and considerations, but can be of some relief when applications locking issues are plaguing your system.  These new enhancements are detailed in the DB2 Version 9 manuals along with additional write-ups in section 5.6 of the new IBM Redbook, DB2 9 for z/OS Serialization and Concurrency Control (sg244725—available at the http://www.redbooks.ibm.com/abstracts/sg244725.html) This new Redbook discusses all the many parameter options for addressing locking issues like LOCKSIZE, LOCKMAX, MAXROWS, along with design guidelines for getting the best concurrency and serialization within your applications.

So if you want to understand your locking options better, research and leverage these new “support for optimistic locking” and “skipping locked rows” options within your DB2 9 for z/OS system and applications.

Another great year of DB2 education and conferences are ahead of us.  To stay up with the latest news please feel free to connect with me through my Linked in account (LinkedIn-Dave_Beulke).

Also join the Linked in main IDUG group and the others associated with the various IDUG conferences.
The main group is  

IDUG: The World Wide DB2 User Community

Conference subgroups are :

IDUG Europe Conference
IDUG North America Conference
IDUG Speakers
IDUG India
IDUG Australasia

By getting linked in with these groups you can find out about a number of special education and conference items this year.  So get connected today.

As I have talked about in the previous weeks, DB2 Version 9 for z/OS has over 50+ great new performance features. Many of these features can really benefit your standard application performance; improve application response time and overall availability of your systems and data.

Another one of the great features for availability and flexibility with database changes is the new CLONE tables.  When you create CLONE tables, only available in DB2 Version 9 New Function Mode (NFM), they have the exact same structure, indexes, triggers and LOB objects. Everything is the same.  The data can then be put into the CLONE using the ALTER EXCHANGE statement that quickly manages application access while DB2 switches and renames the underlying DB2 datasets.

What is also great about CLONE tables is that your applications can immediately access and use them.  Your applications will use the same access paths as the base table’s real-time statistics are invalidated and then used for the new CLONE tables.

Even though there are a number of restrictions CLONE tables can be created on a range-partitioned or a partition-by-growth table space that is managed by DB2. CLONE tables can make your life easier for taking care of your database and the various application availability issues so check them out.  Also check out the usage of CLONE tables in a test environment so all the considerations can be experienced and you can see how the CLONE tables give your systems more availability and flexibility.

With all those year-end jobs running and the system already at 100% utilization there are not too many options for getting things to run faster.

In DB2 Version 9, one of the new features that can be leveraged to make things run faster is increasing your buffer pool with the Workload Manager (WLM) facility. Even if the system is already at 100% utilized, the important jobs need to get done and giving DB2 more resources is usually the best way to improve performance and overall throughput.

By letting WLM increase your buffer pool allocations 25%, based on presentations at the 2009 IBM Information on Demand conference, you give more memory to active or vital data. Unfortunately, IBM’s standard increase of 25% may not be effective or correct for your situation so you will need to test this recommendation. Increasing the buffer pool size usually leads to improved I/O rates. These improved I/O rates can dramatically drop CPU and improve overall I/O processing.

Improving buffer pool sizes for incrased I/O rates has been around since DB2 Version 1. Over the years, I have personally worked with clients and enlarged, reduced and optimized many systems’ buffer pools for better throughput. It is a good idea to enhance DB2 buffer pools and even though this enhancement is very basic, the concept is a good idea.

Given the snowstorm and the extra time in airports, I thought it was perfect time to do a blog entry.  In the early releases of DB2, we had to work the holidays to do the Reorgs because that was the only time during the year when we weren’t taking orders.  Now with online Reorgs and the elimination of DB2 BUILD2 index rebuilding phase everyone can have a Happy Holiday.

In DB2 Version 9, the reorganization utility BUILD2 phase that rebuilds your non-partitioning indexes (NPIS) was eliminated through improvements within DB2.  This elimination of the BUILD2 phase is very important since this phase took the associated tablespace and its other indexes offline causing downtime from database access.

Be warned that the online Reorg does not come free of charge. Eliminating the BUILD2 phase provides a dramatic improvement in availability, but can increase the utility CPU time and the elapsed time. Behind the scenes, DB2 is using a lot of active and archive logs, CPU and I/O resources to provide access while the database rebuilds the indexes.  Make sure the activity is at the complete minimum possible before starting an online Reorg.  Eliminating the Reorg utility BUILD2 phase allow online Reorgs during non-peak processing and provides the DBAs with more opportunity to maintain and improve their systems, database and application performance.

So have a Happy Holidays everyone and leave the Reorgs for some other time of the year.

While talking with clients and friends at the recent conferences, I noticed that everyone seemed to like the blog entries I’ve done about the performance features of DB2 v9 for z/OS. Some have even implemented some of the features previously mentioned and have already reduced their costs and overall processing elapsed times. So, with DB2 Version 9 for z/OS having over 50+ great new performance features, we will continue highlighting these many great new features in the coming weeks.

One of the biggest improvements has come for application and SQL processing in the improvements in index lookaside. This application and SQL index lookaside improvement is built into DB2 Version 9 and leverages the access of the previous work within the current SQL process or other SQL processes.

Index lookaside is the improved process where DB2 keeps track of the index and data pages referenced and their associated index value ranges using system cache. This provides DB2 with the ability to bypass or ignore any subsequent duplicate requests for I/Os against these desired pages. Since these duplicate page requests happen quite frequently during insert activities, index lookaside provides tremendous I/O relief for insert activities. Index lookaside is a great index I/O performance improvement feature that came with DB2 Version 8 for insert activities only.

In DB2 Version 9, the index lookaside process has been leveraged for delete processing also. Since most insert and delete activities process via some order, the index key values and their associated index and data pages could be requesting duplicate I/Os. According to IBM, analysis has found that the new index lookaside feature can save tremendous amounts of I/O — sometime up to a 100 times reduction in I/O requests.

Given these index lookaside features that are built into DB2 Version 9 for z/OS it should be immediately available to all your SQL and applications. This should help improve the performance of existing applications that do a large portion of insert and delete processing with or index lookaside reducing overall elapsed time for your processing.

It is also interesting to hear all the hype about cloud computing at this particular time. The timing is perfect because IT management has finally realized the number of personnel, maintenance, hardware and software costs of distributed Windows and UNIX systems is larger than centralized mainframe costs. This cost realization is being leveraged by salesmen for the cloud computing agenda and the replacing of the IT department with a cloud computing environment is already being heard.

It is also interesting to hear the virtues of cloud computing just as development, security, performance and reliability standards are truly being implemented and enforced across all the Windows and UNIX systems just like their mainframe partners. Change control, security, naming standards and performance have a way of exposing the platform’s true reliability features. Studies continue to show that the mainframe is still the ultimate gold standard. Yes, the mainframe still has the lowest costs because it serves so many systems and so many people so reliably. So much so that some of the old client server systems now run within the mainframe environment that they were supposed to replace. Emphasize and talk about the total cost per transaction for every application and examine the numbers to see the true IT platform costs.

Back in the 1990s while learning the new DB2 for OS/2, which matured into DB2 LUW, implementing these client server systems, my company and this once young and naïve IT professional learned many lessons.

Cloud Computing Lessons

The first lesson was to not completely believe the salesperson or magazine article. Test their claims and products—first prove that it works before the critical project fails because of its problems.

Second, evaluate the technology with your standard project criteria. Any shortcuts for the new cloud will lead to problems. Involve governance, security and capacity planning and use a previous standard project plan because it forces the discussion of all the project aspects in relation to the new cloud platform. Also, involve the budget and accounting people so the true costs are captured for the platform. Track the total personnel, hardware and software costs per transaction. Cloud will work nicely for the right situation, but not for all situations.

Third, get involved with the new cloud technology. While the scars of these client server projects were deep and nasty, they taught me about a new database (DB2 LUW) and new skills that complimented my mainframe background and experience. Like my Java programming hobby for the last nine years, learning Java complements all the architecture, systems and DBA skills and helps you understand the newest OO languages and the Java performance problems and considerations. Learning something new always gives you a new perspective and adds to your overall skills.

So next time you hear someone say they are going to replace the mainframe or all these distributed systems with cloud computing smile, find the salesperson, and let them take you out to lunch. Learn the promises and terms because you’re the one that will have to make these new solutions work. There are no new problems only old ones with new names. Welcome to cloud computing.

First, getting a database connection is expensive because of all those great things that a database provides such as security and integrity. Since the database is important, every connection request must have its security checked and authorized. This security authorization against the database system and the data desired is quick, but takes time. Next, when database processing guarantees integrity it is through its transaction logging of the unit of work. Starting a new database unit of work again is fast but must be managed within the database so that it can be backed out should the transaction processing fail.

Next within the database connection, the SQL processing selects, inserts, updates, or deletes data. These actions holds locks against the data referenced and prevent applications from trying to update the same data or reference the same deleted data. The DB2 applications have several mechanisms to control and handle this locking within the application and system. The best way is to Bind the application against the database is using the Bind parameters ISOLATION(CS) and CURRENTDATA(NO). This minimizes the immediate locks held and allows other transaction more concurrency to the data. If the application is read only and is not concerned with other transaction manipulating the data then use ISOLATION(UR). Using the ISOLATION(UR) setting is preferred for application referencing data that doesn’t change.

Next, the application unit of work must maintain the connection. Large application workloads that perform too many updates, inserts or deletes within a unit of work hold on to too many locks and can cause extended back out times when an application fails. It is very important to have the proper transaction commit scope, issue appropriate commits to minimize the amount of locks and amount of work that the database may have to back out. It is also critical for the applications to reference the database tables and perform their updates in the same sequence. Referencing the data in same order acquires and releases locks synchronously, allowing more application concurrency. Since your application wants to minimize the number of locks and the time those locks are held, it is always best to do your data updates and inserts right before your application performs a commit or ends your transaction. This minimizes the time the locks are held and again provides more concurrency.

While all of this information is standard practice for most applications, within the new SOA architectures the services may not know much about the unit of work or connection situation. Within one client SOA architecture, recent research showed that a particular module had seven different connections active within its service. The service had several connections; DB2 for z/OS, DB2 for LUW, Oracle, MQ series inbound and outbound Queues and connections to application and web servers for AJAX activities. It is a bit much to have all of these connections within a single service and when some minor changes caused this module to fail many processes could not function. Also debugging was very difficult because one connection failure caused all the connection participants to back out their transactions, causing more locking and data integrity issues.

So make sure your application handles connections properly because they are expensive to acquire. Minimize the number of database activities within a transaction to minimize the locks and understand the number of connections that are involved within a particular unit-of-work so that you can get the best performance from your applications.