DB2 Performance Tuning

We’ve checked out numerous areas to check for performance. Now it’s time to see how the application performance is doing within the DB2 system. During this post, I’ll talk about some of the other standard places to check for performance improvements.

Locking Tables and Lock Escalations

Looking into the DB2 system and application monitor showed huge spikes of CPU during the morning hours. These CPU spikes were also happening when there were a number of applications that are experiencing lock escalation in the system. Lock escalation can be caused by numerous situations and needs to be minimized for best processing throughput possible. For example, in a production performance report there were LOCK TABLES = 14,667 and LOCK ESCALATIONS = 25 during a given day.

The high number of LOCK TABLES is a major consideration because it prevents concurrent work. These situations need further research to determine whether they could be modified and eliminated.

The lock escalations were also troubling in terms of system performance. Twenty-five lock escalations during the daytime online window could cause throughput and CPU performance problems. These lock escalations needed to be further researched to determine their origin and the resources that were being locked.

Write Engine Not Available

Looking into the DB2 system and application monitor showed extended read and write times during peak processing. The disk configuration showed normal disk I/O rates and no particular data sets had particular bad read or write times or performance issues.

Looking further into the DB2 monitor statistic showed that in some of the buffer pools the amount of write activity was very robust during certain peak periods. During these extreme peak periods there were some instances where the write activity was so robust that all of DB2’s write engines assigned to that buffer pool were busy.

This situation is denoted in various performance monitor reports under each individual buffer pool report as Write Engine not available. The condition is caused by updated or dirty pages remaining in the buffer longer than necessary and reducing the number of buffer pages available for other processes.

Once the number of buffer pool pages update becomes too big the write engine try to write all the updated pages to the disk. Since the activity was so robust, DB2 could not keep up and all the write engines were busy trying to keep up. This situation can be fixed in a variety of ways depending on your existing configuration.
The first method can be to expand buffer pool allowing more write cushion or changed the deferred write threshold setting to write more frequently or change the workload to spread out the peak timeframe. All methods will potentially improve the situation and allow DB2 to keep up with the write activity before it runs out of DB2 write engines.

In Summary

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.

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 limit the times write engines are not available.

Last time I wrote about DB2 Checkpoint and index definitions over your busiest tables. This week I will discuss the old style of tablespace definitions and their potential performance impact.

Tablespaces Need to Be Reorganized

The first thing to look at is the current state of the database tables and indexes. Usually I find that there are many database objects in need of reorganization and normal on-going maintenance. This is indicated by the number of extents found on the each of the various tablespace and indexspaces which can usually be obtained through a standard data set extent report. This is also discovered by querying the DB2 catalog information and highlighting the number of FAROFFPOS rows. FAROFFPOS indicates that retrieving these data rows requires extra I/Os when referenced in the system because they are not stored on their optimum position within the database tablespace or indexespace. These FAROFFPOS rows are usually found within several large tablespaces and their indexes of database that have not had normal maintenance done on them during the normal business cycle.

All database tables and indexes need to be analyzed to understand their daily activity (insert, update and delete frequency) so that their free space can be adjusted through the FREEPAGE and PCTFREE DB2 allocation parameters. These parameters and space allocations needed to be adjusted to handle the workload for a period of six months without database reorganization. The analysis and adjustments will pay off in better system and application performance, while freeing staff from database object reorganizations for an extended period of time.

Simple Tablespaces Should be Changed to Segmented Tablespaces

Simple tablespaces are not allowed in DB2 Version 10. Usually when I am doing a performance review on an old DB2 Version 9 system I find a small number of tablespaces that were still defined as a simple tablespace type. These tablespaces need to be redefined as segmented or Universal Table Space tablespaces to leverage the better space management and processing features of these tablespaces.

Given that the simple tablespace definition is going away, it is a good idea to change these database tablespaces before the migration requirement creeps up on you and messes up or constrains your schedule.

Tablespace Segment Size is Too Small

Sometimes when I am looking into the segment tablespace definitions I find bad definitions or tablespace definitions that have taken the defaults. When this happens I usually find database tablespace segment size of 4 pages which is too small for normal databases. This segment size is used in secondary allocation efforts and pre-fetch operations.

Having a small segment size limits the efficiency of pre-fetch operations and could require additional I/O during normal operations. It is best to have a large segment size of 32, if possible, depending on the number of partitions and whether or not you are using the new Universal tablespace type. When defining a regular segmented tablespace use a large segment size whenever possible to improve I/O and prefetch operations.

Minimal Partitioning

In some shops partitioning is not really used for the databases. In another performance review I found that a majority of the current database tablespaces used a segmented tablespace definition for handling data. There were a number of tables that had a large number of rows. Those tablespaces needed be analyzed to determine whether they should be redefined as partitioned tablespaces.

Partitioning these tablespaces would split up the data into a number of smaller data sets and spread out the I/O and locking within the database. Partitioning allows better data set and space management for isolating processing or data set secondary extents. Partitioning also enables the processing to possibly use parallel access to reduce the elapsed time of any long running batch processes.

It is best to partition tables that have over one million rows. The DB2 Catalog can easily be queried to determine tables with over one million rows that aren't partitioned.

Table Reorganization Schedule

Maintenance procedures needed be put in place to regularly review and schedule database utilities and database tools against the production database environment. These schedules and tools are very important to the ongoing efficiency of the database system. Performance problems can be very disruptive to the business environment.

Analysis of the environment uncovered tablespaces and indexes in multiple extents in need of reorganization. Workflow analysis and database change statistics need to be gathered to develop an efficient maintenance schedule. Also standard jobs, downtime maintenance windows and operational procedures are needed to minimize the business disruption impact.

Summary

Honing in on your tablespace and table structures can give you lots of places to improve DB2 performance. Take a look at these areas in your own shop:

Are there tablespaces and tables that need to be reorganized? Check for a large amount of data set extents and a large number in rows that are in a FAROFFPOS position.

Do you have any simple tablespaces that should be changed to segmented tablespaces for better 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 performance.

Are there large segmented tablespaces that really should be redefined as partitioned? Check for tables with over a million rows.

Do you have a reorganization schedule an appropriate FREEPAGE and PCTFREE freespace settings that keeps tables cleaned up with a minimum of outage?

See you when we continue on with our exploration of this fascinating case study.

We’ve checked into the DB2 Sort Work, EDM, RID and Buffer Pools. During this post, I’ll talk about some of the other standard places to check for performance improvements.

DB2 System MaintenanceThe DB2 system software maintenance from IBM contains many fixes and performance adjustments in its software maintenance stream. When investigating this company’s maintenance levels, I discovered that their DB2 system is behind on its maintenance level, which does not allow the latest performance improvements to be leveraged. Maintenance also needs to be coordinated with the implementation of pre-tested Service Packs related to other IBM software products.These Service Packs test the compatibility between z/OS, IMS, MQ Series, CICS and DB2 and can help eliminate maintenance compatibility issues. By evaluating the latest release compatible with operating system, MQ Series, CICS and other software connecting to DB2, the company can apply the correct maintenance level for their DB2 Version. Yearly maintenance plans need to be developed to help all departments understand the dependencies and the need to apply maintenance on a regular schedule.

Dynamic Statement Cache Pool Sizing and Settings

Additional analysis showed that the Dynamic Statement Cache (DSC) was being leveraged for application efficiency. This recently implemented feature was working well and only needed to be fine-tuned. (The DSC holds SQL statements executed frequently and does not have to re-determine the access path, verify object existence or re-check security if various settings are the same in subsequent executions.) A good portion of the SQL statements at the company were being cached letting, DB2 use the previously optimized SQL executing in the system. Leveraging the DSC area has usually shown a 2 to 3% CPU savings per SQL transaction and should be monitored closely to make sure to maintain its efficiency.If your environment executes a large percent of dynamic SQL applications, the savings from leveraging the DSC area deserves on-going attention.

Summary

Checking the various aspects of your DB2 system can have a great impact on the performance of your system. Take a look at these areas to improve system performance:

Is your DB2 System Maintenance at the appropriate level? Do you have maintenance plans that include checking the Service Pack levels to ease the integration with IMS, CICS, MQ Series and other software within your environment?

Is your Dynamic Statement Cache Pool set to the appropriate size for your system? Are your settings encouraging SQL caching?

DB2 Sort Work PoolThe DB2 system work area consists of a number of DB2 DSNDB07 work tablespace data sets to handle the DB2 SQL sort requirements. The DB2 sort work pool data sets were in many extents in the system I examined. They needed to be consolidated to minimize the extra I/Os using these extended data sets. Additionally, these sort work data sets needed to have larger primary allocations and a zero secondary allocations quantity to avoid secondary extents.

The 32k sort pool needed its extents consolidated also. The usage of the pool should also be monitored to discover poor program performance or programs that are executing poor SQL that is referencing result sets greater than 4k. Those sorts are forced into the 32k sort pool.

Buffer Pool Sizing and SettingsOne of the company’s systems used a number of buffer pools to cache the various database data and index information and improve the overall efficiency of the database activity. The number, size, and mixture of different database objects allocated to the number of buffer pools can have a dramatic positive performance impact on the processing.

By starting to cache similar database objects, objects with similar read or write access processing patterns, the data can better cached and improve performance immediately. By caching the correct data in the buffer pools and combining or isolating different database objects, more data can be cached and CPU and I/O requirements reduced.

Further evidence that the buffer pools should be analyzed was that some buffer pools were very busy while some were not being used at all. For example, one buffer pool supported a number of database objects, making it very busy throughout the day, while two other buffer pools with high allocations were not being used at all. Additionally, the buffer pools needed to be sized according to their usage, taking buffers from the less busy pools and adding to the busiest buffer pools.

Finally, the company needed to consider having different buffer pool configurations at different points of time. Some clients benefit tremendously by having different buffer pool configurations for on-line daytime workloads and then modifying their buffer pool for a night time batch workload. Since the system had very distinct database tables that were active during each of these periods, having different buffer pool allocations for daytime and nighttime processing cut 1.5 hours of elapsed time and CPU demand off their DB2 processing.

EDM Pool Sizing and Settings

The DB2 environment has many caching mechanisms to improve system and application performance. One of these pools is the EDM pool which helps handle transactions in the system. It consistently had 5-10% free memory space. It is vital that free space be maintained in the EDM pool but only having this much free space consistently indicates some issues.

Because the company was behind in maintenance, I recommended that they bring their DB2 maintenance up to date. This was particularly important since there were a number of EDM pool fixes (PTFs) in the DB2 maintenance.

Once the maintenance was applied, the size of the EDM pool size needed to be monitored and analyzed. The EDM pool memory within this DB2 Version 8 system was rather large and the maintenance and freeing of some old DBRMS within some big DB2 Plans helped reduce EDM workload and add some needed free space.

RID Pool Sizing and Settings

The row id (RID) pool is used for the RID sorts that accompany optimizer access path techniques such as list pre-fetch, hybrid join, and multi-index access. These access paths were very common within the company’s environment and the RID pool was overflowing with work. When these overflow conditions occurred, the SQL access method changed to a tablespace scan or in DB2 10 the RID pool entries overflow to work files within the DSNDB07 sort area causing a huge increase in the number of pages accessed and the required resources to retrieve the information. This bad situation happened 613 times in one day, causing many RID limit failures, leading to thousands of additional I/Os and high CPU usage.

I recommended that the RID pool size should be increased as soon as possible. The RID Pool can be defined up to 1 GB for Version 7 systems and much larger now since it is above the line as of DB2 Version 8. Once that was done, further analysis would detail the transaction frequency and the number of rows referenced through the pre-fetch, hybrid join and multi-index access.

SummaryIt is important to monitor and analyze the various pools in your systems. Check for the following:

Sort work pools aren’t going into secondary extents.

Buffer pools are sized correctly for their I/O activity rate.

Data objects and index objects are in separate buffer pools.

Data objects with similar access characteristics are defined to the same buffer pools.

EDM pools should be defined big enough to maintain 10 to 15% free space if possible

RID pool is adequately sized and RID pool overflows are not happening within the environment.

With this post we are going to delve deeper into problems and recommendations to improve DB2 performance tuning and DB2 security.

DB2 Performance Discoveries and Recommendations

The operating system, CICS and DB2 systems were operating efficiently and managed well within the overall environment. As the number of transactions and systems continue to grow, the systems will require more CPU and performance tuning attention. As the number of different diverse workloads, the software packages and number of transaction increases the amount of time and resources to manage and tune the efficiency of the systems will continue to increase regardless of the platform, database or application.

DB2 CICS RDO Thread Reuse

The connection between DB2 and CICS is very important. These connections settings are handled through the RDO interface that has many DB2 thread reuse and security settings. Based on my experience, these settings can have a dramatic impact on performance and CPU consumption.

The settings were reviewed for the DB2 workload transactions running through the systems CICS environments. These environments averaged approximately over a million transactions per day and had extensive DB2 workloads.

The current CICS and DB2 connection settings did not fully leverage the performance settings that were available to improve connection security, thread and DB2 package or plan reuse. My recommendation was that the settings should be monitored and updated monthly to reflect the transaction workload and dedicated threads defined for many of the larger volume CICS DB2 transactions.

Given that the company has just begun implementing these RDO settings and improvements, there were still opportunities for RDO tuning through the priority, security and plan settings. The dedicated threads were recommended to be run at the normal priority setting, with a security group profile that allows users to reuse their DB2 plan execution authorizations. These settings along with the number of threads allocated, protected and use in the CICS DB2 Pool needed to be fined tuned for each of the environments.

Reviewing and improving these settings through the CICS Statistics DB2 Connection reports that are buried inside the overall CICS Statistics report provides great details on the transactions and thread usage. Changing these RDO settings had an immediate positive effect by reducing CPU consumption. The company could save more by fine tuning all their RDO settings.

DB2 Security Improvements

The DB2 environment had security settings that were used in conjunction with the security system, Top Secret. The company was using both DB2 and Top Secret security extensively, causing additional system-checking overhead. DB2 and Top Secret should be configured to leverage the use of secondary authorization security verification. The use of secondary authorization security checking is common at many large government and banking clients and should be implemented as soon as possible. By using secondary security, the system checking security is done once and reused because of the grouping that allows CICS transaction threads to be reused. This will save the extra system overhead of double-checking security for every transaction execution and save CPU.

Summary

If your system is heavily dependent on CICS transactions:

Make sure that you are reviewing the CICS Statistics DB2 Connections reports on a regular basis.

Update the RDO settings so that the DB2 workload is using mainly dedicated and reused threads.

Insure that your RDO settings use a minimum of pool threads.

Make sure that your workload never has to wait for a thread to execute work in your DB2 CICS environment.

Make sure that you aren’t duplicating security checking with both DB2 and another security product.

Use group ids for your DB2 authorizations as much as possible so that plan, package and CICS thread authorizations can be reused.

AnalysisIn order to do a complete performance analysis of the system, the system and application statistics were reviewed using the standard DB2 performance reports. This data provided a basis of the various system, database, application and SQL observations and improvement recommendations. These statistics along with system, process, and application documentation, interviews with application programmers and observations of the workload guided the investigation of the CPU consumption and CPU reduction effort.

Current Enterprise ArchitectureThe enterprise architecture had evolved over the years to support many diverse database systems. This caused several databases to be cloned and their transactions workloads to be intermixed. This combination of CICS transactions provided a diverse workload of different data requirements, run time durations and application types.

This combination of workloads runs on a single CPU mainframe environment that supports both the test and production environments. Workloads come into the system through a variety of interfaces: CICS, Visual Basic and Web applications using MQ Series Connect and batch jobs throughout the day. These applications access a variety of database tables that support the corporation’s nation-wide business needs.

The enterprise applications environment with a mix of applications operates efficiently experiencing occasional dramatic CPU application requirement spikes. These application CPU requirement spikes manifest themselves throughout the day when CICS program errors occur and dumps are created. These dumps cause the system to pause and dump the transaction situation. This occurs too frequently; almost once every 15 minutes in the production CICS region. Busy business periods of multiple concurrent transactions with a large memory footprint also show stress within the systems.

Work Load Manager

The architecture of the system and its performance are controlled through a variety of software with Work Load Manager (WLM) playing a central role in overall system performance.WLM controls CPU and provides priorities of the different subsystems, online workload and batch processes.

Analysis of the WLM settings needed to be done to determine the optimum and most efficient workload software settings and determine whether the DB2, CICS, and batch transaction have the compatible settings to maximize throughput.

Observing the system processing discovered that the workflow accomplished is fluctuating when the systems has errors or dumps occurring in the various CICS regions. These dumps against the system workflow showed that the system CPU peaked and workflow was severely impacted.

When an on-line program error or dump occurs its core dump documentation and resolution are the highest priority within the system stopping or pausing all other work. An example of the problem occurred by 10:30 a.m. on a summer day. Five regions had 27 errors/dumps occur by that time, which is one every four minutes (27/150 minutes) during the production work day. Industry standards typically have a very small number of these errors or dumps occur in their production regions. This problem directly related to the application quality assurance testing and this situation will only continue to degrade the overall workflow and overall performance of the systems.

CICS Region Configuration and Allocations

The architecture of the CICS systems and the on-line programs reflects how additional data and capabilities have been added. New CICS regions and databases have been added to the workload as additional systems were added to the workload and additional features added to the applications.

These workloads were each separated into their own regions. To improve the overall workflow and provide further room to efficiently grow the CICS transaction workload a Sysplex architecture could be considered. The CICS Sysplex architecture separates the workload out to terminal owning regions (TOR), application owning regions (AOR) and data owning regions (DOR) that can be finely tuned to service each specific type of workload. These regions work together to spread and balance the peak transaction workloads.

Summary

All of these architecture, system, database, application and SQL considerations provide the opportunity for CPU cost reductions. These cost reductions could be achieved through system tuning, database design analysis, application SQL documentation and application coding standards and reviews. Implementing these has the potential of saving tremendous CPU capacity and delaying a CPU upgrade.

Analyze the number of abends, deadlocks and the number of dumps within different parts of your applications. These deadlocks and dumps take a tremendous amount of CPU resources at critical times within your system.

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 performance and overall throughput problems.

Validate the settings between your CICS transaction configurations. Make sure the maximum workload from one TOR, AOR or DOR is not overwhelming another CICS processing partner.

Make sure that the interface with CICS works well to improve performance:

Validate the settings between your DB2 CICS transaction configurations. Make sure the maximum workload from one TOR, AOR or DOR is not overwhelming another CICS processing partner.

If your system is heavily dependent on CICS transactions:

Make sure that you are reviewing the CICS Statistics DB2 Connections reports on a regular basis.

Update the RDO/RCT settings so that the DB2 workload is using mainly dedicated and reused threads.

Insure that your RDO/RCT settings use a minimum of pool threads.

Make sure that your workload never has to wait for a thread to execute work in your DB2 CICS environment.

Handle DB2 security correctly to improve DB2 performance:

Make sure that you aren't duplicating security checking with both DB2 and another security product.

Use group ids for your DB2 authorizations as much as possible so that plan, package and CICS thread authorizations can be reused.

Review DB2 bufferpool activity to improve DB2 performance:

A DB2 bufferpool should be sized correctly for its activity. Make sure to put data objects and index objects in different bufferpools. Data objects with similar access characteristics should be defined to the same bufferpools.

EDM pools maintain enough free space.

RID pool is adequately sized and RID pool overflows are not happening in your environment.

Is your Dynamic Statement Cache Pool set to the appropriate size for your system? Are your settings encouraging SQL caching?

Check the health of your database objects to improve DB2 performance:

Are there DB2 tablespaces and tables that need to be reorganized? Check for a large amount of data set extents and a large number in FAROFFPOS.

In order to use indexes optimally,

Check to insure that indexes leverage uniqueness

Make sure that clustering indexes make sense and that the table is clustered in the most popular application processing sequence order and maintains a CLUSTERRATIO of 95% or higher

Make sure that index columns are in optimum order for your most frequently executed application WHERE clauses.

Make sure that all defined indexes are being used

Do you have a reorganization schedule an appropriate freespace that keeps DB2 tables cleaned up with a minimum of outage?

If you do the intensive review suggested here, you'll find that you will become the "go to" person for DB2 performance. In these economic times, saving the corporation money will make a positive impression within the company.

_______________________________________________________

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.

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.

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.

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.

_______________________________________________________

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.