No matter what version of DB2 you are running, it’s a good idea to review the DSNZPARMs on a regular basis and get them set up to maximize the performance of your data warehousing application SQL by leveraging all the DB2 optimizer capabilities.

To begin, get a listing of the current DSNZPARMs for your data warehouse system’s settings.  This can be done a number of different ways: through your performance monitor, through the JCL that creates the DSNZPARMs, or through the IBM REXX exec of the DB2 stored procedure DSNWZP.  This DSNWZP stored procedure routine is one of the many DB2 administration procedures and is set up during the system install.

The data warehouse DSNZPARMs affect the performance of the DB2 system and its applications in many different ways, from the DB2 optimizer’s access path choices to the number of parallel processes DB2 will perform during data retrieval.  To make sure that all DB2’s performance features and functions are available to improve performance, I’ve made an alphabetic list of DB2 data warehouse DSNZPARMs. (To get Part 1, click here.) Making sure these data warehouse DSNZPARMs are enabled and adjusted for your system will help your application fully exploit DB2 V9 and get the best performance possible for your data warehouse.

____________________

Dave Beulke is a system strategist, application architect, and performance expert specializing in Big Data, data warehouses, and high performance internet business solutions. He is an IBM Gold Consultant, Information Champion, and President of DAMA-NCR, former President of International DB2 User Group, and frequent speaker at national and international conferences. His architectures, designs, and performance tuning techniques help organization better leverage their information assets, saving millions in processing costs.

 

As I have talked about previously, DB2 Version 9 for z/OS introduced over 50+ great new performance features. Many of these features can really benefit your standard DBA operations, improve application performance and increase overall availability of your systems and data.

One of the important new changes in DB2 Version 9 for z/OS is Utilities options and performance improvements which helps your overall maintenance and health of your systems.

Several performance improvements were made within these standard DB2 utilities leveraging improved access and the new big block I/O features within z/OS.

These improvements result in the potential to save a tremendous amount of CPU on many of your daily, weekly and monthly utility jobs.
Analysis and performancefigures published by IBM shows that the following CPU performance improvement potential.

• 0 to 15% Copy Tablespace
• 5 to 20% in Recover index, Rebuild Index, and Reorg Tablespace/Partition
• 5 to 30% in Load
• 20 to 60% in Check Index
• 35% in Load Partition
• 30 to 50% in Runstats Index
• 40 to 50% in Reorg Index
• Up to 70% in Load Replace Partition with NPIs and dummy input

Elimination of BUILD2 Phase

One of the most important items related to the DB2 Version 9 for z/OS Utilities Improvements was the elimination of the BUILD2 phase processing within the standard utilities when rebuilding indexes from the data entries.  The BUILD2 phase was the time when DB2 data was unavailable. Eliminating this phase helps availability for all your applications and shorter utility related downtime.

Eliminating the BUILD2 phase provides a dramatic improvement in availability, but could increase the CPU time and the elapsed time because DB2 manages the concurrent applications when one or more partitions are reorganized. Eliminating the BUILD2 phase allowed online reorgs during non-peak processing and provides the DBAs with more opportunity to maintain and improve their systems, database and application performance.

With each new release of DB2, IBM makes important improvements in performance and CPU reduction, like those noted in this recent blog post.
 

DB2 Version 9 for z/OS introduced over 50+ great new performance features. Many of these really stand out, and I have written about some that can really help you reduce the CPU demand on your system and application environments. (Go to www.davebeulke.com and click on the tag, DB2 9 to get a list of these articles.)
Some of the new features in DB2 9 for z/OS are really great data warehousing and reporting features migrated from DB2 LUW: the RANK, DENSE_RANK, and ROW_NUMBER SQL scalar functions. These OLAP type functions are great for a wide variety of data warehousing applications, moving sums, moving averages, complex calculations, or processing. These OLAP functions improve performance because they drive this complex processing into the DB2 database engine.

SQL RANK Function

The first OLAP function, RANK is used to order and prioritize your data according to your specific criteria when you do not want ties or the same number on duplicate rows. RANK orders your data assigning successive sequential numbers to the rows returned from the SQL query. The ranked rows can be individual data rows or groups of data rows.

SQL RANK Example:

SELECT WORKDEPT,AVG(SALARY+BONUS)AS AVG_TOTAL_SALARY,

RANK() OVER (ORDER BY AVG(SALARY+BONUS) DESC) AS RANK_AVG_SAL

FROM EMPLOYEE

GROUP BY WORKDEPT

ORDER BY RANK_AVG_SAL

 

WORKDEPT     AVG_TOTAL_SALARY                  RANK_AVG_SAL

--------     ----------------------------- --------------------

B01          95050.00000000000000000000000                    1

E01          80975.00000000000000000000000                    2

C01          77847.50000000000000000000000                    3

A00          71670.00000000000000000000000                    4

D11          59283.63636363636363636363636                    5

D21          51740.00000000000000000000000                    6

E21          47570.00000000000000000000000                    7

E11          45720.00000000000000000000000                    8

 

  8 record(s) selected

In the example above, the RANK function provides a way to order the data across two different criteria, providing the user the data in an easy to understand report broken out by each work department and average salary within that particular department.

SQL DENSE_RANK Function

The next OLAP function, DENSE_RANK, provides a way to manage ties or duplicate values within your ranking of the result set rows. DENSE_RANK orders your data and assigns successive sequential numbers based on the OVER PARTITION data values found. DENSE_RANK differs from RANK because common values or ties are assigned the same number.

SQL DENSE RANK Example:

SELECT WORKDEPT, EMPNO, LASTNAME, FIRSTNME, DENSE_RANK() OVER (PARTITION BY WORKDEPT ORDER BY EDLEVEL DESC) AS RANK_EDLEVEL

FROM EMPLOYEE ORDER BY WORKDE

PT, RANK_EDLEVEL, LASTNAME

 

WORKDEPT EMPNO  LASTNAME        FIRSTNME     RANK_EDLEVEL

-------- ------ --------------- ------------ --------------------

A00      000110 LUCCHESSI       VINCENZO                        1

A00      000010 HAAS            CHRISTINE                       2

A00      200010 HEMMINGER       DIAN                            2

A00      000120 O'CONNELL       SEAN                            3

A00      200120 ORLANDO         GREG                            3

B01      000020 THOMPSON        MICHAEL                         1

C01      000030 KWAN            SALLY                           1

C01      200140 NATZ            KIM                             2

C01      000140 NICHOLLS        HEATHER                         2

C01      000130 QUINTANA        DELORES                         3

D11      200220 JOHN            REBA                            1

D11      000220 LUTZ            JENNIFER                        1

D11      000210 JONES           WILLIAM                         2

D11      000160 PIANKA          ELIZABETH                       2

D11      000180 SCOUTTEN        MARILYN                         2

D11      000150 ADAMSON         BRUCE                           3

D11      000200 BROWN           DAVID                           3

D11      000060 STERN           IRVING                          3

D11      000190 WALKER          JAMES                           3

D11      200170 YAMAMOTO        KIYOSHI                         3

D11      000170 YOSHIMURA       MASATOSHI                       3

D21      000240 MARINO          SALVATORE                       1

D21      200240 MONTEVERDE      ROBERT                          1

D21      000260 JOHNSON         SYBIL                           2

D21      000070 PULASKI         EVA                             2

D21      000270 PEREZ           MARIA                           3

D21      000250 SMITH           DANIEL                          3

D21      000230 JEFFERSON       JAMES                           4

E01      000050 GEYER           JOHN                            1

E11      000280 SCHNEIDER       ETHEL                           1

E11      200280 SCHWARTZ        EILEEN                          1

E11      000090 HENDERSON       EILEEN                          2

E11      000300 SMITH           PHILIP                          3

E11      000290 PARKER          JOHN                            4

E11      000310 SETRIGHT        MAUDE                           4

E11      200310 SPRINGER        MICHELLE                        4

E21      200340 ALONZO          ROY                             1

E21      000340 GOUNOT          JASON                           1

E21      000320 MEHTA           RAMLAL                          1

E21      000330 LEE             WING                            2

E21      000100 SPENSER         THEODORE                        2

E21      200330 WONG            HELENA                          2

 

  42 record(s) selected.

In the example above, the DENSE_RANK function provides a way to order the data even though there are a number of people with the same level of education within the same work department.

SQL ROW_NUMBER Function

The ROW_NUMBER function provides a way to add row numbers to any result set. These additional ROW_NUMBERs can be a great assistance for evaluation of applications, their processing logic, and end-user web applications or service architectures.

 

SQL ROW_NUMBER Example:

SELECT ROW_NUMBER() OVER (ORDER BY WORKDEPT, LASTNAME) AS NUMBER,

LASTNAME, SALARY

FROM BEULKE.EMPLOYEE

ORDER BY

WORKDEPT, LASTNAME

 

db2 => SELECT ROW_NUMBER() OVER (ORDER BY WORKDEPT, LASTNAME) AS NUMBER, LASTNAME, SALARY FROM EMPLOYEE ORDER BY WORKDEPT, LASTNAME

 

NUMBER               LASTNAME        SALARY

-------------------- --------------- -----------

                   1 HAAS              152750.00

                   2 HEMMINGER          46500.00

                   3 LUCCHESSI          66500.00

                   4 O'CONNELL          49250.00

                   5 ORLANDO            39250.00

                   6 THOMPSON           94250.00

                   7 KWAN               98250.00

                   8 NATZ               68420.00

                   9 NICHOLLS           68420.00

                  10 QUINTANA           73800.00

                  11 ADAMSON            55280.00

                  12 BROWN              57740.00

                  13 JOHN               69840.00

                  14 JONES              68270.00

                  15 LUTZ               49840.00

                  16 PIANKA             62250.00

                  17 SCOUTTEN           51340.00

                  18 STERN              72250.00

                  19 WALKER             50450.00

                  20 YAMAMOTO           64680.00

                  21 YOSHIMURA          44680.00

                  22 JEFFERSON          42180.00

                  23 JOHNSON            47250.00

                  24 MARINO             48760.00

                  25 MONTEVERDE         37760.00

                  26 PEREZ              37380.00

                  27 PULASKI            96170.00

                  28 SMITH              49180.00

                  29 GEYER              80175.00

                  30 HENDERSON          89750.00

                  31 PARKER             35340.00

                  32 SCHNEIDER          36250.00

                  33 SCHWARTZ           46250.00

                  34 SETRIGHT           35900.00

                  35 SMITH              37750.00

                  36 SPRINGER           35900.00

                  37 ALONZO             31840.00

                  38 GOUNOT             43840.00

                  39 LEE                45370.00

                  40 MEHTA              39950.00

                  41 SPENSER            86150.00

                  42 WONG               35370.00

 

  42 record(s) selected.

It is always good to review your current applications and see where these OLAP functions might help your performance.  These OLAP functions usually improve performance where any histograms, moving sums, moving averages, or complex calculations take place within your various reports or other applications.  Since the OLAP DB2 engine features are faster than any application program process, these new powerful SQL features can easily improve your application response time while reducing your overall CPU demand.

 

 

As I referenced in an earlier DW post, DB2 Version 9 for z/OS introduced over 50+ great new performance features. Many of these enabled companies to reduce CPU demand on system and application environments.

One of the features introduced in Version 9, selecting modified data, helps your applications minimize the number of SQL calls and the number of trips to the DB2 system.  This feature provides the ability to get data values with SQL SELECTs clauses wrapped around your standard UPDATE, INSERT, MERGE and DELETE SQL statements.

By providing for the ability to perform an SQL SELECT from an UPDATE, INSERT, MERGE or DELETE SQL statement, the application is able to retrieve many important values previously retrieved in additional SQL statements.  By retrieving these data values when the UPDATE, INSERT, MERGE or DELETE SQL statement is executed, the application can get data values for ROWIDs, identity, sequence, timestamp columns and other values or defaults in the database.
What is also great about using a SQL statement to select the modified data is that the application can get the data values of columns modified by BEFORE INSERT triggers. By getting these column data values, your application can use them in other parts of the application immediately.  This is especially valuable for maintaining referential integrity relationships that need the values of ROWIDs, identity or sequence column values.

The new feature of selecting modified data or SQL SELECT from an UPDATE, INSERT, MERGE or DELETE SQL statements, is very powerful also because it can eliminate a large amount of network traffic and additional SQL calls.  Processes and their related SQL that were done in multiple calls to DB2 can now be done in a single call.  Additionally before selecting modified data SQL enhancement, these old multiple SQL calls sometimes had to transmit large chunks of data back and forth across the network. Now all that extra data transmission traffic is eliminated.

By implementing this powerful feature of selecting modified data or SQL SELECT from an UPDATE, INSERT, MERGE or DELETE SQL statement, you can dramatically reduce the number of SQL Calls, reduce your network traffic, and improve your application response time while reducing your overall CPU demand
 

This is a continuation of last week's post.

Avoid Inappropriate Tablespace Scans

There are many DB2 access paths available. The most expensive of these is a tablespace scan because it causes DB2 to scan all the entries of a table to retrieve the rows desired by the application. Within most of the system and application performance reviews done at one recent client's review, there were 1,869 distinct existing tablespace scans in their production environment. Almost one tenth of the applications were doing tablespace scans. This was significantly worse than that found at other clients or industry standards.

In addition to accessing every row within a table, these tablespace scans hold additional database locks, consume additional CPU, and hinder application throughput performance. Each of these tablespace 1,869 scans needed to be evaluated to see if the access path could be improved. Improving all of your production tablespaces scans will reduce overall CPU and locking consumption.

List Prefetch and Index Scans

Other expensive DB2 access paths are List Prefetch and Index scans. These access paths can very often be improved and optimized to direct index access resulting in CPU savings and improve elapsed runtimes. Analyzing the production environment at a recent financial bank showed a large number (737) of the available programs doing these types of SQL access types.

When these List Prefetch and Index Scans are being used, they are pushing a number of Record Identifiers (RIDs) to the RID pool within the DB2 system. When a large number of applications push RIDs into the RID Pool sometimes the pool requirements become too big causing RID pool failures. When RID Pool failures happen, the access path is degraded, causing more I/O and CPU to be used to retrieve the database information. To avoid this situation each of the index scans should be evaluated to determine if it is overflowing or causing major utilization of the RID pool.

These types of List Prefetches and Index Scans access types can sometimes be improved through using more SQL WHERE statement parameters or verifying the index definitions are appropriate. By improving the SQL, monitoring the number of rows referenced and monitoring RID Pool failures, the efficiency of the programs will be improved and the CPU reduced for each application execution.

Summary

Review applications in your company for:

• Excessive use of tablespace scans
• Excessive use of list prefetch and index scans
• Lack of use of more recent SQL techniques such as limited fetch or table expressions.

________________________________________
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.
 

Let's delve into the SQL used in the applications of a major DB2 system.

Taking Advantage of Relational Technology

During reviews of various client systems, analyzing the SQL used is very helpful for understanding how sophisticated the shop and system are at leveraging relational technology. When the shop and system are well designed, the tables, indexes and application properly utilize object designs through effective object reuse and consistent design patterns. All of these application components are exposed when the application access methods are designed and formulated into SQL. Usually if a system is designed properly the SQL contains good access paths. SQL with good access paths use the following:

• SQL Joins
• SQL Sub queries
• Table expressions
• CASE expressions
• Limited fetch

The more work done within in the DB2 relational technology engine, usually the faster the data can be turned into information for application reports, web pages, etc. At a recent client there were zero table expressions, limited fetches and very few of any of the other SQL options to leverage DB2 and relational technology. Rewriting one of the applications with an SQL Join and a SQL Table expression dramatically reduced processing elapsed time and CPU requirements taking the application processing from hours to only a few minutes.

Use SQL Joins

Analysis of another client's system showed that the application programmers were not using SQL Joins in any of their applications. Most of the application data processing focused on retrieving one row at a time and often from only one table.

The basis of relational technology is set processing or processing a large number of rows through a single SQL statement. Application programmers need to understand and leverage relational technology so that the application programs process more data with each interaction with the database. Often when SQL is used to process single database rows, the application program can be rewritten with SQL Joins of tables using indexes and proper WHERE clauses and dramatically cut the CPU and elapsed processing time. Using any of the several of SQL Join methods also optimizes I/O and CPU usage of the application by pushing the work from the program into the DB2 engine.

Experience with several past clients has shown significant elapsed time process improvement and tremendous CPU savings by rewriting application with SQL Joins. For example, an application designed with poor SQL was rewritten with SQL Joins and went from 12 hours of execution time to only 5 minutes.

Experience has shown that applications that yield the biggest improvements are those that are executed often and open cursors to access a large number of rows. Once these applications are identified, an applications team can redesign these programs to use more efficient relational technology and SQL join set processing.

Summary

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

________________________________________

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.
 

Last time I wrote about some of the application performance aspects. This week we’ll delve deeper into DB2 Checkpoint aspects for recoveries and index definitions over your busiest tables and their potential performance impact.

DB2 Checkpoint System Frequency

The DB2 system runs transactions and takes system wide recovery checkpoints for system consistency and integrity. These system wide checkpoints synchronize the system across all applications and can be disruptive because they hold up transactions to get a synchronization point. It is a common industry rule-of-thumb to take these checkpoints every ten to fifteen minutes to have good recovery points and not hold up processing.

Within the examined system these checkpoints were being taken very infrequently because the active log data sets were very large. This frequency, while good for processing through put, exposes the DB2 system to having an elongated recovery window should a disaster happen. The system checkpoint frequency needed to be evaluated and adjusted to provide better recoverability.

This situation also can cause issues during the batch cycle as programs write over 250,000 or 500,000 log records without the system taking a checkpoint.

Too Many Indexes Defined on Busy Tables

Looking at the workload for the overall system found that one of the main insert, update and delete processing tables also had a large number of indexes defined on it. Querying the DB2 catalog, found 11 indexes defined on this very busy table. Analysis also showed that the majority of the processing was inserts, updates and deletes as opposed to select activity.

Other very busy tables also had 7 indexes. These tables and each of their indexes needed to be analyzed for further usage analysis versus their overhead.

Too Many Indexes Not Being Used

Looking further into the indexes that were defined on the application database also showed many indexes that were not being used. Querying the DB2 catalog I often find a large number of indexes defined that are not being used. Sometimes these indexes that aren’t being used are even on the largest or the busiest tables.

Further research showed that some of these indexes were defined for one time processes and weren’t deleted after the processing was complete. One other index was being used and was critical for the dynamic SQL workload. So just because you queried the DB2 Catalog with the query below to find the index plan and package dependencies does not mean that you can delete the index.

SELECT DISTINCT TBNAME, NAME AS INDEXNAME

FROM SYSIBM.SYSINDEXES

WHERE DBNAME LIKE '<database-name>%'

ANDNAME NOT IN

(SELECT BNAME

FROM SYSIBM.SYSPACKDEP

UNION

SELECT BNAME

FROM SYSIBM.SYSPLANDEP)

 

Run the query and use the indexes listed as your starting point for further analysis. Each index listed could be critical for dynamic SQL performance so be very careful before deleted any indexes from your system.

In Summary

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.

Having extra indexes over busy tables can be a huge 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.

 

________________________________________

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.

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.

_______________________________________
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.
 

Tablespaces Need to Be Reorganized
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.

• 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?

________________________________________
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.

DB2 System Maintenance
The 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.
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.
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?

________________________________________
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.

DB2 Sort Work Pool
The 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.
Buffer Pool Sizing and Settings
One 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.
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.
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.

Summary
It 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.

________________________________________
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.

DB2 Performance Discoveries and Recommendations

• 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.

________________________________________
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.

In this post, I’ll delve a little deeper into the system architecture and CPU reduction opportunities found in a major DB2 system at a large financial institution. (To see first part of case study, click here.)

Analysis
In 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 Architecture
The 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.
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.
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.
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.
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.

________________________________________
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.

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.

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.