DB2 Performance Tuning

Make sure that you are using the correct database design to improve DB2 performance:

• Use partitioning for robust tables that play central parts in the processing to help ease locking and encourage parallelism. Also leverage database partitioning for large tables (over 500,000 rows) which might benefit from spreading out the I/O to different partitions and different devices.
• Use database compression for those databases that have a large row sizes and a majority of read access profiles.
• Do you have any simple tablespaces that should be changed to segmented tablespaces for better DB2 performance? Are your segmented tablespaces properly sized?
• Do you have frequently updated tables with a large number of indexes? Double check to see if any of these indexes can be eliminated to improve DB2 performance. Having extra indexes over busy tables can be a huge DB2 performance overhead. Deleting two or three extra indexes can sometimes cut one-quarter or one-half of a second off your transaction response time. So make sure you only have the indexes that are being used defined on your tables.

Use application programming and SQL techniques correctly

• It is important to drive processing to the SQL engine as much as possible. Review applications in your company for:
• Lack of SQL Joins
• SQL Sub queries
• Table expressions
• CASE expressions
• Limited fetch
• Review applications in your company for:
• Excessive use of tablespace scans
• Excessive use of list prefetch and index scans
• Use the new DB2 pureQuery for DB2 Java applications to improve DB2 performance, security and statically bind the application access path within your environment.
• Do you have too many locks against your tables? How many lock escalations happened today? The answer to these questions lies in looking at the order of your application processing and your programming methodologies so that concurrent transactions can occur easily.
• Data performance reviews need to be exhaustive in order to achieve all the possible savings possible. Data access reviews are an important part of these reviews. Also:
• Analyze the types of access paths that are active in your environment.
• Review the number of invalid plans and packages
• Check the use of table compression for large tables with frequent Select statements run against them.

_______________________________________________________
Dave Beulke is an internationally recognized DB2 consultant, DB2 training and DB2 education instructor.Dave helps his clients improve their strategic direction, dramatically improve DB2 performance and reduce their CPU demand, saving millions in their systems, databases and application areas within their mainframe, UNIX and Windows environments. Search for more of Dave Beulke’s DB2 Performance blogs on DeveloperWorks and at www.davebeulke.com.

In the past, we have examined in-depth the DB2 performance problems that might be found in a typical DB2 installation. My purpose in doing this was to show you how a methodical review can help you discover ways to reduce system costs. Good application review and positive changes can even save substantial investment in new hardware.

Be sure to check through the various blogs to hone in on the type of analysis essential for your site. Below is a review of some of the topics we've covered over the past four months.

Analyze Your System

• Use RUNSTATS to determine what is really going on in your system. Redo and review them for analysis as the system is implemented and grows.
• Using performance reports and drilling down into the DB2 catalog can give you valuable information about specific performance problems. Use these tools to discover:
• Processes that use excessive resources
• Underlying data objects that might be causing some performance problems.
• Reasons why data object definition, particularly index definition, might be causing performance problems.
• Determine the number of abends, deadlocks and dumps. These can take a tremendous amount of CPU resources and should be researched and eliminated as soon as possible.
• Check secondary extents for all data sets, including SortWorks
• Check to see if there are lots of times that a write engine is not available. Look at the size of your buffer pools, look at the deferred write threshold and look at the timing of your jobs and possibly eliminate  the times write engines are not available.
• Verify the checkpoint frequency of your systems. Having the correct checkpoint frequency can provide the proper checkpoints for disaster and regular recoveries of your databases and system. The recommended checkpoint frequency is about every 10 to 15 minutes. So depending on how busy your system is adjust the number of log records accordingly.
• Make sure that your Work Load Manger (WLM) is set up properly to distribute the CPU resources adequately and properly to the various database systems and applications. Having the database at the same or below the applications can cause huge performance and overall throughput problems.
• Insure that your DB2 System Maintenance is at the appropriate level. Do you have maintenance plans that include checking the Service Pack levels?

To be continued...

_______________________________________________________

Dave Beulke is an internationally recognized DB2 consultant, DB2 training and DB2 education instructor.Dave helps his clients improve their strategic direction, dramatically improve DB2 performance and reduce their CPU demand, saving millions in their systems, databases and application areas within their mainframe, UNIX and Windows environments. Search for more of Dave Beulke’s DB2 Performance blogs on DeveloperWorks and at www.davebeulke.com.

Another great feature that is in the new Version 9.7 of DB2 is its new compatibility with Oracle. This compatibility takes many forms within the database such as understanding operational syntax, stored procedures keyword syntax and improved operational concurrency that I covered in this DB2 Performance blog.

The DB2 compatibility with Oracle was developed because many clients are migrating from Oracle to DB2 for its improved DB2 performance, lower licensing costs, ease of use and lower total cost of ownership (TCO). All of these factors are very compelling given the current economic times for cost savings, consolidation of servers and lower CPU and energy costs of processing within DB2 as compared to other DBMSs.

DB2 Handles Oracle’s Non-Standard Names

Many of the operational aspects of standard databases such as the data definition language (DDL), data manipulation language (DML) and structured query language (SQL) and data control language (DCL) are standard across databases such as SQL Server Oracle and DB2 Family. Over the years Oracle has called parts of these DDL, DML and DCL, non-ISO standard names, to perform operations. DB2 has added these unique Oracle non-standard operational keywords to its lexicon of syntax to perform these operational activities. The most popular is the Oracle TRUNCATE statement that is used everywhere to clear the contents of a table.

DB2 and Oracle Stored Procedures Compatibility

Another major item is stored procedures compatibility because they are utilized everywhere. Again with all the standard programming languages such as Java C++ C# and others, over the years the syntax was controlled by the ISO and other standards committees. The various DBMS vendors took liberties with their stored procedure languages do perform certain processes. Again DB2 has added these Oracle syntax items so that any developer that is familiar with Oracle will be able to quickly and effectively code for any of these DBMSs. This one feature is critical for many companies because all companies have multiple different DBMS vendors and have a programming staff that needs to write stored procedures on all of them.

Oracle to DB2 Migration Success

I recently worked with a client during the DB2 Version 9.7 Cobra program and found that the syntax for both the operational aspects and the stored procedures is about 95% there for general conversions from Oracle to DB2. Working with the dedicated client staff, some of my consultant associates and the IBM Cobra people, we were able to migrate a vendor product from Oracle to DB2 Cobra and then take the DB2 code from the Linux, UNIX and Windows platform and run the application on DB2 for z/OS environment. Given these Oracle to DB2 compatibilities, the client is positioning to leverage the lowest TCO in the industry of DB2 on z/OS with a vendor and their solution. This is remarkable because they didn’t even understand the mainframe concepts only three months ago.

Another Oracle to DB2 migration project done by a friend of mine was completed in only three days, so the conversion work can be done very quickly and you can become the next hero lowering your company’s total costs within IT. Migrate to DB2 today and save your company money, it might even save your job or your coworker’s job.

_______________________________________________________

Dave Beulke is an internationally recognized DB2 consultant, DB2 training and DB2 education instructor.Dave helps his clients improve their strategic direction, dramatically improve DB2 performance and reduce their CPU demand, saving millions in their systems, databases and application areas within their mainframe, UNIX and Windows environments. Search for more of Dave Beulke’s DB2 Performance blogs on DeveloperWorks and at www.davebeulke.com.

I received some great comments and questions based on the blog entry/video about the many new features in DB2 Cobra. One of the more interesting questions was asked “How much work or how compatible is the new DB2 with Oracle?”The answer DB2 is very compatible, so compatible in fact, that many people have migrated from Oracle to DB2 in only days.

So what are the reasons or justification for management to consider changing their database from Oracle to DB2? The top four reasons I have heard recently are:

·         Oracle contract pricing continues to go up. Every time we look Oracle wants more money because we had to add CPU cores to get application performance.

·         Application performance for our BI OLAP Oracle work does not respond. We always have to rewrite the SQL or create new indexes to make it work. When we tried it on DB2, their optimizer automatically rewrites the SQL avoiding the manual SQL rewrite, improving application performance and overall response time.

·         DB2 disaster recovery and HADR feature is much better than any Oracle’s stand-by or their fail-over solution. Now that the application is compatible with DB2 there is no reason to risk the business because of an Oracle failure.

·         Saving IT budget money is paramount these days and with our Oracle database, we continue to spend money on expanding disk requirements. DB2 data compression and the new index compression provide a way to reduce disk costs, improve database performance and minimize backup and recovery while reducing disk requirements and saving money.

Converting your database from Oracle to DB2 is very easy. You can save money for your company, your IT department, and maybe your job by migrating today.

_______________________________________________________

Dave Beulke is an internationally recognized DB2 consultant, DB2 training and DB2 education instructor. Dave helps his clients improve their strategic direction, dramatically improve DB2 performance and reduce their CPU demand, saving millions in their systems, databases and application areas within their mainframe, UNIX and Windows environments. Search for more of Dave Beulke’s DB2 Performance blogs on DeveloperWorks and at www.davebeulke.com.

In 2009 IBM announced DB2’s newest feature, DB2 pureScale.This new feature within the DB2 Family of products represents the next step in overall database performance because of its unlimited capacity, application transparency and continuous availability.The DB2 pureScale feature provides the ability to balance the workload across a dynamic number of DB2 servers that share a common set of disks while managing all the database concurrency issues.

Currently, DB2 pureScale is implemented across DB2 cluster service nodes running on the IBM Power Servers leveraging InfiniBand network connectivity using the Tivoli System Automation.DB2 pureScale will be available first on the IBM Power Servers and then rolled out to other configurations in the future.Through these facilities, the DB2 pureScale feature is able to dynamically add new server capacity transparently for any application.This provides continuous availability and instantaneous scalability capabilities for all applications.

By being able to grow or shrink your DB2 cluster servers on demand, your business can provide resources where they are needed and only when they are needed.DB2 pureScale provides the ability and flexibility to deploy DB2 resources for days or weeks and only pay for the software stack when those resources are in use.This technology flexibility limits the business cost by only paying for the resources for this short period of time when they are in use.This can dramatically reduce overall hardware and software costs for any business and especially for businesses that have seasonal or fluctuating workload demands.

IBM’s testing of the DB2 pureScale feature shows tremendous scalability as the number of DB2 servers are increased for the workload.By balancing the workload across the DB2 database servers the configuration retains 95% of its scalability through 32 server nodes and 81% scalability up to 112 DB2 server nodes.All of this capacity with application transparency and automatic fail over provides a tremendous computing power for any application requirements.

This DB2 pureScale feature leverages unique optimization of the global locking and caching coherency interfaces of the DB2 cluster nodes through DB2 Cluster Services.These Cluster Services provide centralized and duplexed mechanisms, like a DB2 for z/OS data sharing coupling facility, for managing the global locking and caching needs of the entire cluster application workload. The centralization of the Cluster Services provides many advantages over other clustering technologies, like Oracle RAC, by minimizing the lock and cache communication to multiple servers.This is another reason DB2 continues to lead the industry in innovation, performance, scalability and continuous availability.

For more information go to the IBM DB2 pureScale web site at
http://www-01.ibm.com/software/data/db2/9/editions-features-purescale.html
or the DB2 pureScale press release can be found at
http://www-03.ibm.com/press/us/en/pressrelease/28593.wss

_______________________________________________________

Dave Beulke is an internationally recognized DB2 consultant, DB2 training and DB2 education instructor.Dave helps his clients improve their strategic direction, dramatically improve DB2 performance and reduce their CPU demand, saving millions in their systems, databases and application areas within their mainframe, UNIX and Windows environments. Search for more of Dave Beulke’s DB2 Performance blogs on DeveloperWorks and at www.davebeulke.com.

Many years ago, IBM System Z announced the introduction of additional new specialty processors. The first System Z specialty engine introduced for data sharing was the internal coupling facility (ICF), next the integrated facility for Linux (IFL), next the application assist processor (zAAP) and the latest is the integrate information processor (zIIP). All of these specialty engines take the burden off the general processors, reducing the software licensing costs within System Z environments.

The savings from the zIIP and zAAP specialty engines are especially dramatic when processing different types of DB2 workloads. Within the System Z environment, the zAAP engines are available for Java workloads and XML System services. This Java processing is especially important for the System Z application server workloads running on WebSphere, Tomcat and other Java web and application processing. Next, as the unstructured data world integrates into database systems, the XML processing workload offloaded to zAAP engines continues to assist the growth within everyone’s environment.

The zIIP engines are especially great for handling some very popular and intense processing within DB2. The first is for the processing of remote DB2 access processing, parallel SQL query work, IPSec network encryption, additional XML services, and assisting with the DB2 utility sort processing. It is critical to understand and analyze the potentially substantial quantity of these types of processing used by these specialty processors in your environment.

At a recent client, the DBAs saw large requirements of these types of workloads and prompted the capacity planners to dig into the zIIP and zAAP utilization analysis. Analysis discovered that the environment workload was maxing out the zIIP engines and the workload was taking capacity from the general processors. The client ordered and installed more zIIP processors and reduced their general processor utilization by providing more zIIP capacity. This also reduced their overall applications response time and provided more overall capacity at a minimum of cost.

The zIIP and zAAP processors utilization is not always on the radar but needs attention periodically. The capacity planners’ and DBAs’ analysis should dig deeper into the overall utilization of these specialty processors and analyze whether additional specialty engines might relieve the burden on your general processors. Not only will these zIIP and zAAP processors relieve the stress on your general processors, but they can also reduce your overall peak CPU utilization and your MSU software licensing costs. This will save your company money while zIIPing and ZAAPing your DB2 performance and your overall application environment.

_______________________________________________________

Dave Beulke is an internationally recognized DB2 consultant, DB2 training and DB2 education instructor.Dave helps his clients improve their strategic direction, dramatically improve DB2 performance and reduce their CPU demand, saving millions in their systems, databases and application areas within their mainframe, UNIX and Windows environments. Search for more of Dave Beulke’s DB2 Performance blogs on DeveloperWorks and at www.davebeulke.com.

Recently working with a client’s SOA environment showed several interesting DB2 performance issues. One DB2 performance issue that was quite stunning was the large number of connections that the .Net and Java applications were making to DB2 and other systems. Researching the system and application further uncovered a wide disparity in the handling and the amount of connections each of the many application modules were using. Proper connection handling is very important to DB2 performance because of three main reasons: acquiring new connections is expensive, application connections maintain database locks and connections are unit of work transactions.

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 be 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 for DB2 performance is to Bind the application against the database is using the Bind parameters for cursor stability ISOLATION(CS) and CURRENTDATA(NO). This minimizes the immediate locks held and allows other transactions more concurrency to the data. If the application is read only and is not concerned with other transaction manipulating the data then use uncommitted read 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 and impact DB2 performance. 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 workload 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 and impact DB2 performance. 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 DB2 performance from your applications.

_______________________________________________________

Dave Beulke is an internationally recognized DB2 consultant, DB2 training and DB2 education instructor.Dave helps his clients improve their strategic direction, dramatically improve DB2 performance and reduce their CPU demand, saving millions in their systems, databases and application areas within their mainframe, UNIX and Windows environments. Search for more of Dave Beulke’s DB2 Performance blogs on DeveloperWorks and at www.davebeulke.com.

As each version of DB2 for z/OS comes out there are always the projects that need justification for going to the next DB2 version. The huge number of DB2 performance features in DB2 Version 9 for z/OS should be an imperative to get to the DB2 9 or DB2 10 release. This large number of DB2 performance features in DB2 V9 for z/OS can make a huge difference in your system, database and application performance.

These large number of performance enhancements in DB2 Version 9 for z/OS also are accompanied by a large number of operational and availability enhancements that provide new functionality. The combination of all these new Version 9 features can make your life as a DBA or an application developer dramatically better.

Some of these features are discussed in my new presentation, “Improving Performance with DB2 V9 for zOS,” that has been posted for download. Also in this presentation there is a full list of all the Version 9 enhancements that is great to include as reference for a justification that management wants for your migration to the latest DB2 Version 9 for z/OS.

In July 2010 IBM announced the zEnterprise with its new integrated IBMPOWER7 and IBM System x blades environments. This provides users with the ability to run traditional mainframe applications alongside any UNIX and Intel based applications. These new capabilities provide a one-stop cloud environment for businesses to deploy and scale any application from any platform.

In August 2010 IBM announced the new deployment options for the DB2 pureScale system to the IBM System x environments. The ability to leverage the DB2 pureScale environment provides the same multi-member DB2 data sharing mainframe capabilities within a new SUSE Linux based solution. Testing shows that the DB2 pureScale environment performs and scales almost linearly, like the z/OS DB2 Data Sharing mainframe environment.

Providing these DB2 Data Sharing type capabilities within the System x architecture provides another great performance and scalability option within the DB2 LUW family. The DB2 pureScale option with its tremendous scalability provides a great expansion option for existing DB2 LUW systems that are running out of capacity within their single computing environment footprint. And now with the ability to deploy them to the zEnterprise, companies that have DB2 LUW systems can get the reliability and performance management of the mainframe environment.

This DB2 pureScale on System x also provides another DB2 LUW open solution for companies that need to consolidate UNIX or Intel based environments and applications. So regardless of the application or system requirements, the DB2 family provides the most open cost efficient options. Check out the full IBM DB2 pureScale announcement at IBM.

Within the zEnterprise announcement in July 2010 there were several exciting comments about the new query performance within the IBM Smart Analytics Optimizer. This smart system leverages the integrated platform, providing industry leading scalability for data warehouse and business intelligence capabilities.

Queries that scanned entire terabytes of data that were previously avoided can now be executed in seconds through the IBM Smart Analytics Optimizer processing. This capability provides data mining features for quickly getting answers to the clustering, associations, classification and predictions within your data warehousing environment.

Within some of the documentation, IBM testing of the Smart Analytics Optimizer shows huge performance boosts. Queries that scanned and then subsequently optimized sometimes showed an improvement of 54 times, the cost of the query 711 times. This type of improvement is very exciting and especially what new data warehousing and business intelligence workloads need on the zEnterprise platform.

Check out all the zEnterprise documentation—especially the information on the IBM Smart Analytics Optimizer. This was just the beginning of a resurgence of the new mainframe that supports any mainframe, UNIX or windows workload. Known as a company’s “private cloud,” it allows IT departments to leverage tremendous performance improvements while reducing query cost substantially.

 The zEnterprise IBM computer was announced in July 2010 and it can run any workload. Yes any workload! Your company can now run mainframe, UNIX and some Windows applications within the new environment.  The new zEnterprise is designed with performance and capacity for large-scale consolidation of any workload.  As has been done recently with consolidating UNIX systems into the mainframe environment, it’s now possible to consolidate the myriad of Windows based systems with the integration of z/VM 6.1.  This new computing architecture helps consolidate performance, integrate data better and improve overall management of the computing environment with unified standard performance security and optimization facilities for better overall availability.

The zEnterprise takes consolidation one step further as any workload can be implemented within its standard environment with dramatic energy savings using workload optimizers through zEnterprise’s integration of the IBMPOWER7 and IBM System x blades, allowing the consolidation of diverse application workloads.

This helps deliver the mainframe’s reliability, availability and security to these other platforms while helping your company lower risk, overall computing and energy costs.  The new architecture also helps centralize all the data from these diverse platforms to a central hub, eliminating the many copies and remote islands of data.  This improves integration, latency and performance as heterogeneous architectures perform more end-to-end enterprise transactions.

Consolidated computing through the new zEnterprise system is just beginning. Now all architectures, systems and applications can truly be evaluated for their performance.  Regardless of the operating system, programming language or network protocol, your company can get it all done with the lowest total cost of ownership with 96 of the potentially newest and fastest CPUs (5.2GHz) available on any computer in the world through the new zEnterprise system.

Check out all the zEnterprise information at the links below and the interesting comments about z/VM from PCWorld. Then imagine all your enterprise data integrated into a high performance, energy efficient and optimized system.

ibm.com/systems/zenterprise/
http://www.vm.ibm.com/linux/
http://www.vm.ibm.com/zvm610/
http://www.pcworld.com/businesscenter/article/160888/run_windows_on_a_mainframe.html

 

Within all database systems there are tables that hold transactions or some type of data based on time.  These transactions unfortunately build up over time to handle regulatory compliance, infrequent access or support development of historical and trending reports.

Within your database design, this time-based transaction data builds up quickly and managing it so the volume does not affect performance is critical.  Sometimes the frequency or grain of transactions such as web clicks, banking transactions, stock transactions or other transactions build up to terabytes of data per day, quickly overwhelming robust storage arrays.

Separating out the real time transaction data versus the old data within your database requires planning and design steps.  These planning and design steps help the separation of the old data versus new data and guarantee application and SQL performance does not suffer when your database is fully populated A separation of the old and new data also helps performance management so more resources can be delegated to maintain transaction performance for business operational success.

DB2 10 Temporal Tables with their built-in functionality automatically understand the business time or system time that the data was entered into the system.  This functionality is great for finding out the condition of the business as of a certain time.  There are two new BUSINESS_TIME and SYSTEM_TIME table period definitions columns available.  These new time period column definitions are used for the new DB2 temporal table definitions to provide system-maintained, period-maintained or bi-temporal (when both system and period maintained) database tables.

Many systems today have manual applications or utilities which migrate their real time data to history tables.  These new temporal tables with their built-in system and business time columns can be used in conjunction with a user-defined trigger to automatically migrate transactional temporal table data to another user defined HISTORY table.  Having these facilities built into the database greatly improves regulatory compliance, operations and overall performance.

More information can be found on DB2 10 Temporal Tables and other DB2 10 features in the IBM White Paper: IBM DB2 10 for z/OS Beta -- Reduce Costs with Improved Performance.

The North American IDUG Conference and the IOD EMEA conference highlighted all the functionality and features within DB2 10.  The more you hear about these functions and features, the more you realize they are really going to save your company money right away.

Justifying a DB2 upgrade is always necessary for management and for the new DB2 10 it is going to be a lot easier with its many CPU-saving features.  With all the new features saving from 5-7% right out of the box in conversion mode, DB2 10 offers existing systems and applications immediate savings.  Add in all the new options such as Hash Access, Index Include Columns and other features and developer and DBA activities become a lot easier, reducing your overall DB2 CPU capacity requirements possibly up to 20%.  Think of getting a 20% decrease in your costs and the costs of the associated third party software and you can see why management is going to push for getting DB2 10 installed as soon as possible.

One of first ways DB2 10 reduces your CPU within your systems and applications, is through a new access path called Range-List Index Scan.  This new access path should immediately assist many of your applications that have multiple WHERE predicates that reference the same index.  In DB2 9 the access paths scanned the index for each of the WHERE predicate references.  Within DB2 10 the Range-List Index Scan scans the index only once to retrieve all the RIDs, saving the extra CPU and I/Os of multiple separate scans, RID consolidation and elimination of duplicate RID entries.  This can be a huge improvement for applications that have paging or other types of searching SQL WHERE predicates

When your applications are being prepared for migration to DB2, the plan stability features started in Version 9 and improved with DB2 10 will assist you.  Within DB2 10 this enhancement lets the application manager lock down any package for a particular access path.  If the access path is changed through an attempt to rebind the package, the process can supply an error or provide a warning within the Bind process.  This protects your access paths against access path problems and alerts your developers to a possible problem.

So if you want a 20% decrease in your CPU demand or just want to start thinking about staying up with your competition, start your plans for DB2 10 now

__________________________
Dave Beulke is an internationally recognized DB2 consultant, DB2 training and DB2 education instructor.  Dave helps his clients improve their strategic direction, dramatically improve DB2 performance and reduce their CPU demand, saving millions in their systems, databases and application areas within their mainframe, UNIX and Windows environments.

DB2 upgrades are not the same in any two companies and may not even be the same within the same company. As multiple DB2 systems sometimes support different applications with varying requirements, the Return on Investment (ROI) is completely different. Also, comparing DB2 z/OS or DB2 LUW to Oracle system upgrades also does not even begin to be similar or comparable as all these databases have completely different contract clauses, ROI requirements and upgrade factors. Apples and oranges are both edible, but that is where the comparison and similarities stop.

DB2 upgrades depend on the technology. First-hand experience has shown that bad research, poor database design, poor application development techniques, bad choices in third party vendor tools and an assortment of other factors make ROI equations for upgrades complex or almost impossible. Having helped fix bad technology decisions, redesigned bad database designs and fixed SQL application performance problems, I know that reducing costs is always on everyone’s mind. Upgrading any software especially raises cost consciousness.

To minimize upgrade costs, strive for better understanding and knowledge of the DB2 technology. This will help everyone avoid unnecessary costs and bad decisions. Work on minimizing the DB2 10 upgrade costs through better management of your DB2 systems today. I’ve mentioned the considerations of SMS environments and improving your security definitions to get ready to separate security data access.

This week the suggestion for preparing for DB2 10 is to begin analyzing your DB2 application techniques. The first thing to analyze is how your plans and packages are put together. IBM’s DB2 engineers have been telling everyone to get to DB2 packages within their environment for many releases. In DB2 10 the old plan management technique of DBRMs going directly into plans needs to be replaced by collecting them within COLLIDs and then binding into your DB2 plans. It is a small difference but it has big implications. If your shop still uses the technique of putting DBRMs directly into plans, it is time to start a process to migrate to the new method.

Next, the compliers that your applications utilize also need to be current. DB2 10 changes within the COBOL pre-compiler generate new SQLCA and SQLDA structures along with generating COMP-5 binary data attributes instead of COMP-4 or COMP. Even though there are still some ways to handle old compilers it is best to review your COBOL compiler versions to make sure they will be ready for your eventual DB2 migration.

___________________________________________________________________ 

Dave Beulke is an internationally recognized DB2 consultant, DB2 trainer and education instructor.  Dave helps his clients improve their strategic direction, dramatically improve DB2 performance and reduce their CPU demand saving millions in their systems, databases and application areas within their mainframe, UNIX and Windows environments.

 

Whether you are currently using DB2 Version 8 or Version 9, getting ready for all the CPU efficiencies in DB2 10 is easy. Your company may not be thinking about migrating to DB2 10 yet, but DB2 10 is getting huge industry wide support as the cost savings began to be realized by management. Everyone wants to save money and with everyone trying to squeeze their budgets, a faster adoption of DB2 10 is in your future. It is best to be prepared, so here are a few things you can do  in DB2 Version 8 that will make it easier to get to DB2 10.

Embrace System Managed Storage SMS

No, I am not Darth Vader telling you to embrace the dark side, but to let you know that DB2 10 catalog and directory use SMS. Using SMS is a hard thing to do for many companies and personally, I am not a real big fan of SMS. DB2 10 and additional improvements in storage have made me think twice and SMS may be more help than a problem. Being a performance geek, I recommend limiting or not sharing storage volumes within the DB2 SMS definitions. The SMS performance key, if possible, is to segregate the DB2 SMS definitions completely, yes completely, away from other storage definitions. SMS horror stories about the I/O troubles are well known and through my consulting clients I see SMS at the center of many I/O problems. Embrace SMS but be diligent guarding your DB2 system’s I/O performance and especially your DB2 catalog and directory data sets.

Sharpen Your Security

I am a big supporter of compliance, protecting data assets, security audits and all the security team’s efforts for protecting the company and all the business information. DB2 10 offers more privileges and authority granularity in your DB2 systems and security experts will need to embrace these features to separate out your data access, utility operations and day-to-day work. Start auditing your security usage of your systems area, various DBA support groups, different local and distributed applications, and analyze their needs. Do the activities require data access? Could the DB2 DBA work maybe been done using a different security level such as DB2 SYSOPR or another security authority? More DB2 10 security privileges and authority granularity improvements are going to affect your security profiles. Get ready and start the security analysis now.

Learn more about DB2 10 through the replay of Roger Miller’s and my 2010 webcast: DB2 10 for z/OS – Helping you improve operational efficiencies and gain competitive advantage. The webcast replay is available here…

http://www-01.ibm.com/software/os/systemz/webcast/13apr/index.html?S_TACT=100GU00M&S_CMP=SP

 

After the original post of “Reminding Management of the Advantages of System Z,” many people commented on how their management is increasingly out of touch with the mainframe. Also, comments also stated that the System Z environment is really processing almost all of the transactions in their company and how all the Windows platform systems continue to have scalability issues.

Although the mainframe revenue for IBM suffered in 2009 because of its upgrade cycle, the introduction of the z10 System platform demonstrates it is the best open system. Yes, that is correct, the mainframe is the most open system available because it runs all types of workloads: the legacy standards of Assembler, COBOL, PL1 etc. but also C++, C#, Java, PHP and the rest of the languages that run on UNIX and Windows boxes.

Also, some people are starting to run “virtualized” windows on the mainframe environments. PCWORLD highlighted this capability in early 2009With the System Z speed, scalability and network, the mainframe continues to be the best solution for all types of workloads. A nice short demo of z/Vos is on YouTube along with many other videos that you can show to your iPhone-obsessed boss, demonstrating that consolidating those hundreds of MS SQL Server instances is also possible.

The story of virtualization continues to drive UNIX consolidation to the mainframe. In 2009 Allianz consolidated 60 servers into a single mainframe, saving substantial operating, licensing and energy costs while improving scalability. This story detailed in this ComputerWorld article is being repeated at many companies as the mainframe IFL, zIIP and zAAP specialty engines continue to bring processing power at PC or minimized prices. This consolidation activity has a very short term return on investment as these efforts pay for themselves usually in the first year and reduce power consumption dramatically making it a “green” project.

The next time the hundreds of windows or UNIX server configurations need an OS, database or other software upgrade make sure to mention how System Z is saving other companies time and operating costs and overall costs through consolidating these environments to the best cost alternative -- the mainframe System Z.

During the 2010 IBM Z Summit road show, there were several presentations detailing the mainframe platform advantages over UNIX and Windows platforms such as the lowest total cost of ownership, the best availability and unparalleled scalability. These presentations cut through the rumors with detailed facts and figures of different platform configurations. Download these presentations and distribute them to your management for a little reminder why the mainframe continues to be the best platform for your enterprise applications.

The Windows and UNIX platforms proponents always discount and minimize the total cost of ownership, availability and scalability topics. It is our duty to periodically remind management of the extra costs of these UNIX and Windows systems with their huge power consumption costs, software license fees, and software maintenance costs of working with several hundred or thousands of disparate systems. The mainframe quietly continues to process the majority of the transactions at the Fortune 500 companies and everyone, especially younger management types who think the world can run on an iPhone, needs to understand that the System Z infrastructure is the best backbone for any company.

The System Z mainframe is also evolving since it now has specialized processors such as the IFL, zIIP and zAAP to reduce overall operational and licensing costs. These specialty processors, along with the smaller configuration of the System Z offer a single small platform that can consolidate any number of UNIX workloads into a single footprint with a smaller greener energy footprint and better licensing configuration.

The presentations detail benchmarks, licensing fees and labor costs of various mainframe versus UNIX platform configurations. The figures show it sometimes takes double the number of processor cores on a UNIX configuration to start to scale out a configuration. Even more UNIX processors are required to achieve transaction rates that are still only performing one-fourth of what the mainframe System Z executes. These UNIX systems are also dedicated to the production transaction environment with no thought of supporting testing, QA or failover facilities that have yet to be priced or considered, features that come standard within the System Z environment.

System Z also continues to grow because of its faster chips. Ask any PC or UNIX platform personnel “what platform has the fastest clock speed processors” and you will quickly find out who keeps up with the industry information. The chip clock speeds of the System Z and other IBM platforms have improved like the rest of the PC industry. In fact, the System Z z10 chip operates at 4.4 GHz and comes in a 64-way quad core configuration that can speed up any application performance problem. This is almost twice as fast as the HP Superdome processors and a third faster than the Intel Nehalem chips.

So the mainframe continues to lead the industry. Does your management know the cost savings and performance figures of System Z? Tell them and show them the presentations before someone tries to “replace the mainframe” again with a more troublesome, power hunger, bad performing clustered iPhone configuration.

 

The previous Part 1 and Part 2 discussions highlighted and discussed the various DB2 Data Warehouse DSNZPARMS for z/OS within Versions 8 and 9.  With the new DB2 Temporal Tables the system needs all the DB2 Data Warehouse DSNZPARMS to be enabled and all the other new and improved DB2 10 DSNZPARMS for maximizing performance.  To ensure the new DB2 DSNZPARMS are enabled, below is a listing of the Depreciated, Improved or new DB2 DSNZPARMS to make sure your data warehouse and other applications get the best performance available within your DB2 10 environment.
Since the storage model of the new DB2 10 system has moved much of the processing components above the 2GB memory line, most of these new and improved DB2 DSNZPARMS deal with new maximum memory settings.  If you are monitoring system paging, running on hardware that has enough memory or running on one of the new 196 hardware platforms, leverage the new DB2 memory capabilities and the new hardware as soon as possible.

Remember even with a number of the DB2 10 components moving above the 2GB memory bar, there are still many components (sometimes as much as 25%) below the bar.  Monitor your system paging and the size of DB2 memory footprint and adjust your settings incrementally and carefully. 

Remember that monitoring, analyzing, improving and repeating in small increments is the best way to provide the best DB2 Data Warehouse DSNZPARMS and a stable high performance environment.

To access the DB2 10 DSNZPARM data warehouse chart in PDF format, please go to DB2 Information on www.davebeulke.com. 

Previously we talked about the first alphabetic group of DB2 data warehouse DSNZPARMS that can improve your access paths and overall application performance.  This week the second set of DSNZPARMS are discussed.  Many of the data warehouse DSNZPARMS discussed are somewhat hidden within the regular DSNZPARM install panels.  All of these DSNZPARMS discussed are available in DB2 for z/OS DB2 Version 9. Some are available in DB2 Version 8 or DB2 Version 10. 

Caution needs to be taken with all system settings and especially these data warehouse DSNZPARMS.  These DSNZPARMS are meant to change access paths and improve them, but each data warehouse design is unique along with each application access path, so results will vary.  If the data warehouse DB2 subsystem is shared with other OLTP or operational applications, I highly recommend fully documenting and setting up a full PLAN STABLITIY plan and package management structure for your current access paths before changing any DSNZPARMS.  This documentation along with a good PLAN STABILITY DB2 plan and package management implementation and back out practices helps your environment quickly react and back out any detrimental access paths encountered through unexpected rebind of any program.

Some of the comments I’ve received regarding this issue highlighted the resurgent of data warehousing on the z/OS platform and why running a data warehouse on z/OS provides many advantages over other platforms.  One that was noted from several people is when your data warehouse runs on z/OS, the huge ETL processes usually don’t have to transmit the data over a network.  Even though the network bandwidth is robust, avoiding this extra bottleneck can sometimes save hours of extra overhead, guaranteeing that your refresh data jobs have enough time every day to provide critical refreshes of you data within your data warehouse.

 Additionally most of your source data warehouse data comes from the z/OS operational systems and can quickly be put into operational business intelligence data warehouses.  This fresh data increases sales, provides real time inventory or product availability updates and, the most important factor, removes latency for all your critical single point master data source of record for the enterprise.

Improve your system and application performance by adjusting these data warehouse DSNZPARMS to improve your access paths and by using the superior DB2 optimizer technology and most efficient performance available.