The North American IDUG Conference and the IOD EMEA conference highlighted all the functionality and features within DB2 10. The more you hear about these functions and features, the more you realize they are really going to save your company money right away.
Justifying a DB2 upgrade is always necessary for management and for the new DB2 10 it is going to be a lot easier with its many CPU-saving features. With all the new features saving from 5-7% right out of the box in conversion mode, DB2 10 offers existing systems and applications immediate savings. Add in all the new options such as Hash Access, Index Include Columns and other features and developer and DBA activities become a lot easier, reducing your overall DB2 CPU capacity requirements possibly up to 20%. Think of getting a 20% decrease in your costs and the costs of the associated third party software and you can see why management is going to push for getting DB2 10 installed as soon as possible.
One of first ways DB2 10 reduces your CPU within your systems and applications, is through a new access path called Range-List Index Scan. This new access path should immediately assist many of your applications that have multiple WHERE predicates that reference the same index. In DB2 9 the access paths scanned the index for each of the WHERE predicate references. Within DB2 10 the Range-List Index Scan scans the index only once to retrieve all the RIDs, saving the extra CPU and I/Os of multiple separate scans, RID consolidation and elimination of duplicate RID entries. This can be a huge improvement for applications that have paging or other types of searching SQL WHERE predicates
When your applications are being prepared for migration to DB2, the plan stability features started in Version 9 and improved with DB2 10 will assist you. Within DB2 10 this enhancement lets the application manager lock down any package for a particular access path. If the access path is changed through an attempt to rebind the package, the process can supply an error or provide a warning within the Bind process. This protects your access paths against access path problems and alerts your developers to a possible problem.
So if you want a 20% decrease in your CPU demand or just want to start thinking about staying up with your competition, start your plans for DB2 10 now
Dave Beulke is an internationally recognized DB2 consultant, DB2 training and DB2 education instructor. Dave helps his clients improve their strategic direction, dramatically improve DB2 performance and reduce their CPU demand, saving millions in their systems, databases and application areas within their mainframe, UNIX and Windows environments.
Modified by DaveBeulke
Previously, I talked about the first alphabetic group of DB2 data warehouse DSNZPARMS that can improve your access paths and overall application performance. This week the second set of DSNZPARMS are discussed. Many of the data warehouse DSNZPARMS discussed are somewhat hidden within the regular DSNZPARM install panels. All of these DSNZPARMS discussed are available in DB2 for z/OS DB2 Version 9. Some are available in DB2 Version 8.
Caution needs to be taken with all system settings and especially these data warehouse DSNZPARMS. These DSNZPARMS are meant to change access paths and improve them, but each data warehouse design is unique, along with each application access path, so results will vary. If the data warehouse DB2 subsystem is shared with other OLTP or operational applications, I highly recommend fully documenting and setting up a full PLAN STABLITIY plan and package management structure for your current access paths before changing any DSNZPARMS. This documentation along with a good PLAN STABILITY DB2 plan and package management implementation and back out practices helps you quickly react to your environment and back out any detrimental access paths encountered through unexpected rebind of any program.
Some of the comments from previous blogs on data warehouse applications highlighted the resurgent of data warehousing on the z/OS platform and why running a data warehouse on z/OS provides many advantages over other platforms. One that was noted from several people is when your data warehouse runs on z/OS, the huge ETL processes don’t usually have to transmit the data over a network. Even though the network bandwidth is robust, avoiding this extra bottleneck can sometimes save hours of overhead, guaranteeing that your refresh data jobs have enough time every day to provide critical refreshes of you data within your data warehouse. Additionally, most of your source data warehouse data comes from the z/OS operational systems and can quickly be put into operational business intelligence data warehouses. This fresh data increases sales, provides real time inventory or product availability updates and, most importantly, removes latency for all your critical single point master data source of record for the enterprise.
Improve your system and application performance by adjusting these data warehouse DSNZPARMS to improve your access paths and by using the superior DB2 optimizer technology and most efficient performance available.
To get Part 2 of the DB2 V9 DSNZPARM settings, click here.
DB2 upgrades are not the same in any two companies and may not even be the same within the same company. As multiple DB2 systems sometimes support different applications with varying requirements, the Return on Investment (ROI) is completely different. Also, comparing DB2 z/OS or DB2 LUW to Oracle system upgrades also does not even begin to be similar or comparable as all these databases have completely different contract clauses, ROI requirements and upgrade factors. Apples and oranges are both edible, but that is where the comparison and similarities stop.
DB2 upgrades depend on the technology. First-hand experience has shown that bad research, poor database design, poor application development techniques, bad choices in third party vendor tools and an assortment of other factors make ROI equations for upgrades complex or almost impossible. Having helped fix bad technology decisions, redesigned bad database designs and fixed SQL application performance problems, I know that reducing costs is always on everyone’s mind. Upgrading any software especially raises cost consciousness.
To minimize upgrade costs, strive for better understanding and knowledge of the DB2 technology. This will help everyone avoid unnecessary costs and bad decisions. Work on minimizing the DB2 10 upgrade costs through better management of your DB2 systems today. I’ve mentioned the considerations of SMS environments and improving your security definitions to get ready to separate security data access.
This week the suggestion for preparing for DB2 10 is to begin analyzing your DB2 application techniques. The first thing to analyze is how your plans and packages are put together. IBM’s DB2 engineers have been telling everyone to get to DB2 packages within their environment for many releases. In DB2 10 the old plan management technique of DBRMs going directly into plans needs to be replaced by collecting them within COLLIDs and then binding into your DB2 plans. It is a small difference but it has big implications. If your shop still uses the technique of putting DBRMs directly into plans, it is time to start a process to migrate to the new method.
Next, the compliers that your applications utilize also need to be current. DB2 10 changes within the COBOL pre-compiler generate new SQLCA and SQLDA structures along with generating COMP-5 binary data attributes instead of COMP-4 or COMP. Even though there are still some ways to handle old compilers it is best to review your COBOL compiler versions to make sure they will be ready for your eventual DB2 migration.
Dave Beulke is an internationally recognized DB2 consultant, DB2 trainer and education instructor. Dave helps his clients improve their strategic direction, dramatically improve DB2 performance and reduce their CPU demand saving millions in their systems, databases and application areas within their mainframe, UNIX and Windows environments.
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.
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.
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.
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.
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.
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
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 the 2011 European IDUG approaches, it’s good to remember the annual North American IDUG conference in May where the latest and greatest information about DB2 was unveiled. The IDUG Board of Directors and especially the Conference Planning Committee did a great job providing qualified users, consultants and IBM developers as speakers to discuss their technical experiences with DB2 for z/OS and LUW, including the new release of DB2 10. DB2 performance tuning, application development and DBA topics filled the agenda and everyone learned the latest techniques to help their companies gain the competitive advantage
DB2 10 Beta program was the largest with testing by 24 companies and the most successful testing period ever for a new DB2 version.
The DB2 10 for z/OS software is only about a year old and is definitely being tested by an ever growing number of companies. The very nature of a new release requires the regular introduction of the maintenance for both z/OS and DB2 for the best stable environment and to fully leverage its performance tuning improvements. It is recommended that companies planning to go to DB2 10 should plan on at least quarterly maintenance cycles to fully incorporate all APARs, Hipers, and other fixes or enhancements.
DB2 10 continues to be enhanced due to some of the issues discovered during the beta program and other items that needed additional work. The DB2 for z/OS Version 9 Plan Stability feature is getting a lot of attention with its ability to help with production package access path preservation. The DB2 10 additional features of APCOMPARE and APREUSE are still being worked on. Even if these new enhancements don’t make it in the release, Plan stability continues to be a great feature introduced in DB2 for z/OS Version 9 for guaranteeing that access paths stay the same or improve with every new developer version.
For those of you that are looking for the DB2 10 White Paper the sign up link for the free download of the PDF from the IBM website is below. You have to sign up because they want to know how many people download the DB2 10 White Paper, but it’s free and you can opt out of any email spam. So download it today at:https://www14.software.ibm.com/webapp/iwm/web/signup.do?source=sw-infomgt&S_PKG=db2_zos_reduce_costs
Whether you are currently using DB2 Version 8 or Version 9, getting ready for all the CPU efficiencies in DB2 10 is easy. Your company may not be thinking about migrating to DB2 10 yet, but DB2 10 is getting huge industry wide support as the cost savings began to be realized by management. Everyone wants to save money and with everyone trying to squeeze their budgets, a faster adoption of DB2 10 is in your future. It is best to be prepared, so here are a few things you can do
in DB2 Version 8 that will make it easier to get to DB2 10.
Embrace System Managed Storage SMS
No, I am not Darth Vader telling you to embrace the dark side, but to let you know that DB2 10 catalog and directory use SMS. Using SMS is a hard thing to do for many companies and personally, I am not a real big fan of SMS. DB2 10 and additional improvements in storage have made me think twice and SMS may be more help than a problem. Being a performance geek, I recommend limiting or not sharing storage volumes within the DB2 SMS definitions. The SMS performance key, if possible, is to segregate the DB2 SMS definitions completely, yes completely, away from other storage definitions. SMS horror stories about the I/O troubles are well known and through my consulting clients I see SMS at the center of many I/O problems. Embrace SMS but be diligent guarding your DB2 system’s I/O performance and especially your DB2 catalog and directory data sets.
Sharpen Your Security
I am a big supporter of compliance, protecting data assets, security audits and all the security team’s efforts for protecting the company and all the business information. DB2 10 offers more privileges and authority granularity in your DB2 systems and security experts will need to embrace these features to separate out your data access, utility operations and day-to-day work. Start auditing your security usage of your systems area, various DBA support groups, different local and distributed applications, and analyze their needs. Do the activities require data access? Could the DB2 DBA work maybe been done using a different security level such as DB2 SYSOPR or another security authority? More DB2 10 security privileges and authority granularity improvements are going to affect your security profiles. Get ready and start the security analysis now.
Learn more about DB2 10 through the replay of Roger Miller’s and my 2010 webcast: DB2 10 for z/OS – Helping you improve operational efficiencies and gain competitive advantage. The webcast replay is available here…
he DB2 10.5 “Cancun Release” was previously available as DB2 LUW Version 10.5 Fix Pak 4.
The short list of prerequisites and summary DB2 10.5 “Cancun Release” features list is here at the IBM DB2 10.5 “Cancun Release” Fix Pak Knowledge Center website.
Within the release there are many important features, and some of the most interesting ones are related to the new DB2 Explain information. New Explain columns provide more information on the DB2 SQL access path attributes used within our applications.
There are 14 new columns which have many values to support a variety of DB2 optimizer access path information. These are highlighted below, along with a description of what the new Explain provides for our debugging and performance research.
Provides an indicator related to whether the SQL Explain output was built from SQL that was reused. This can be important to identify SQL that could be reused from your system statement cache, be resident for a long time, and not the new refreshed version that was just pushed into the test environment.
Indicates whether the sort process is used to buffer the result set information as it is retrieved from the database.
• BYDPART and RANDACC
Help identify and describe the Zig Zag Join access path. These columns indicate if the DB2 access part is partition dependent which is especially important for pureScale systems. The RANDACC Explain parameter highlights whether the Zig Zag Join uses a TEMP table to facilitate and improve random access.
Indicates that XML data may be moved between DPF partitions during the execution of the SQL.
Indicates that the access path references the index block entries within a Multi-Dimensional Clustered (MDC) index to FETCH the result set data. This is similar to index-only access on a standard table.
Indicates DB2 SQL optimization error(s) occurred during the parsing or applying of SQL Profiles, a new feature. By defining specialized SQL optimization profiles, access paths can be improved or standardized for access-path-challenged tables. Sometimes by applying this profile information, the parsing or optimization can encounter an error. This column, along with the diagnostic messages, can help debug the error situation.
Tells whether the bind option for skipping locked data during the SQL execution is enabled. Skipping locked data can be troublesome especially in robust systems with detailed result set requirements. Be careful and monitor this for accounting and other critical result set data applications.
• PLANID, STMTID and EXECUTID
Help uniquely identify the SQL statements for the DB2 plan, statement, and the execution id used for the Explain. These unique identifiers are very important due to the new ability to set up execution profiles which the optimizer can use to favor critical DB2 access paths for better performance.
• BUSTSENS and SYSTSENS
Indicate whether the SQL could be impacted by the value of the CURRENT TEMPORAL SYSTEM_TIME special register. This is very important since SQL can have specific DB2 temporal table business time or system time requirements. The temporal special register and the process’s SQL temporal requirements now provide more information about the flexibility for using the business or system temporal time.
Indicates the DB2 access path isolation level used for the SQL against a specific table. Since the isolation level can be adjusted through a number of mechanisms, this Explain column provides the information indicating whether it is Uncommitted Read, Read Stability, Cursor Stability, or Repeatable Read.
All of these new Explain columns in the new DB2 10.5 “Cancun Release” provide more critical information about the development of DB2 SQL access paths by the DB2 optimizer. Knowing how the DB2 optimizer is treating your SQL is always better for getting the best DB2 SQL performance possible.
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.
It is usually pretty easy to quickly implement new DB2 features. DB2 makes it easy to improve your database performance with a new zParm or table space definition. Unlike most of these new features, however, DB2 temporal tables need research before you implement them. DB2 temporal tables offer great flexibility and many data warehouse design options that can be leveraged very effectively--or be abused--with the wrong application design.
To prepare you need to evaluate whether the application is appropriate for temporal tables. First with DB2 temporal tables it is even more important to determine the frequency of the inserts, updates and deletes that are going to happen. Frequencies are always a good design point for any application but it is especially important for DB2 temporal tables because of the way BUSINESS_TIME or SYSTEM_TIME is maintained and how all the data changes are captured within the associated history temporal table. Every data change could really be two processes because rows need to be replicated into your history table. That could be a major performance consideration.
The next research points are the restrictions with DB2 temporal tables and history tables. The temporal table must be a regular table with the added BUSINESS_TIME or SYSTEM_TIME. No clone table capabilities, column masks, row permissions or security label columns are allowed. The same restrictions are in place for the history table.
The temporal table and its associated history table must be kept in sync. Restrictions also exist regarding the altering, adding or removing columns into the temporal table and the history table to guarantee integrity. Also backup and recovery for the temporal table and its history table must be kept in sync and there are restrictions around DB2 Utilities that could delete data from these tables. In addition once the history table is defined, its table space or table cannot be dropped. So make sure the columns desired in your DB2 temporal tables are stable and well defined for the application.
There are several resources that should be reviewed before designing your first application using DB2 temporal tables. The first is the IBM DB2 10 manuals. By reading these friendly manuals you get a good understanding of the syntax, various examples and details about all the restrictions. Next there have been some presentations about DB2 temporal tables at past IDUG conferences and IOD conferences. Track down you colleagues that went to the conferences and get the CD or access to the website for the presentation downloads.
Corporations build business intelligence and data warehouse systems to gain competitive advantage. DB2 helps you create business intelligence and data warehouse systems that give the performance corporations need by providing many industry leading features.
Combine DB2 MQTs for DB2 Business Intelligence Databases
In other posts I talked about DB2 business intelligence and data warehouse designs that combine multiple DB2 materialized query tables (DB2 MQTs). Combining these DB2 MQTS with different Weekly, Monthly, Quarterly and Yearly data provides a great way to quickly get reporting information.
But business intelligence data warehouse systems are more than just providing a platform to total sales figures quickly. As an IBM commercial pointed out, business intelligence is about getting a deeper understanding of your business. Your data warehouse design needs to be able to provide the extra data or information that provides context, comparisons and a deeper meaning to data.
Drill Down for Requirements for Your DB2 Business Intelligence and Data Warehousing Systems
Understanding the flow of the sales totals, noticing whether sales trends are improving or declining is only the beginning. The ability to drill into your data warehouse information quickly and meaningfully is the start of a business intelligence system. Being able to drill through big department categories and then also analyze to the tiniest pixel color levels within unstructured data items is becoming part of data warehouse requirements for new systems.
When you are designing your new DB2 data warehouse business intelligence system, don’t stop with the major questions that are going to be asked. Follow up to uncover additional questions that will be asked after the first question gets answered. After you have uncovered four or five levels of questions, you’re ready to start the DB2 business intelligence data warehouse design. This way your system can provide better value and deep insights into improving the business.