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.
The North American IDUG Conference and the IOD EMEA conference highlighted all the functionality and features within DB2 10. The more you hear about these functions and features, the more you realize they are really going to save your company money right away.
Justifying a DB2 upgrade is always necessary for management and for the new DB2 10 it is going to be a lot easier with its many CPU-saving features. With all the new features saving from 5-7% right out of the box in conversion mode, DB2 10 offers existing systems and applications immediate savings. Add in all the new options such as Hash Access, Index Include Columns and other features and developer and DBA activities become a lot easier, reducing your overall DB2 CPU capacity requirements possibly up to 20%. Think of getting a 20% decrease in your costs and the costs of the associated third party software and you can see why management is going to push for getting DB2 10 installed as soon as possible.
One of first ways DB2 10 reduces your CPU within your systems and applications, is through a new access path called Range-List Index Scan. This new access path should immediately assist many of your applications that have multiple WHERE predicates that reference the same index. In DB2 9 the access paths scanned the index for each of the WHERE predicate references. Within DB2 10 the Range-List Index Scan scans the index only once to retrieve all the RIDs, saving the extra CPU and I/Os of multiple separate scans, RID consolidation and elimination of duplicate RID entries. This can be a huge improvement for applications that have paging or other types of searching SQL WHERE predicates
When your applications are being prepared for migration to DB2, the plan stability features started in Version 9 and improved with DB2 10 will assist you. Within DB2 10 this enhancement lets the application manager lock down any package for a particular access path. If the access path is changed through an attempt to rebind the package, the process can supply an error or provide a warning within the Bind process. This protects your access paths against access path problems and alerts your developers to a possible problem.
So if you want a 20% decrease in your CPU demand or just want to start thinking about staying up with your competition, start your plans for DB2 10 now
Dave Beulke is an internationally recognized DB2 consultant, DB2 training and DB2 education instructor. Dave helps his clients improve their strategic direction, dramatically improve DB2 performance and reduce their CPU demand, saving millions in their systems, databases and application areas within their mainframe, UNIX and Windows environments.
DB2 upgrades are not the same in any two companies and may not even be the same within the same company. As multiple DB2 systems sometimes support different applications with varying requirements, the Return on Investment (ROI) is completely different. Also, comparing DB2 z/OS or DB2 LUW to Oracle system upgrades also does not even begin to be similar or comparable as all these databases have completely different contract clauses, ROI requirements and upgrade factors. Apples and oranges are both edible, but that is where the comparison and similarities stop.
DB2 upgrades depend on the technology. First-hand experience has shown that bad research, poor database design, poor application development techniques, bad choices in third party vendor tools and an assortment of other factors make ROI equations for upgrades complex or almost impossible. Having helped fix bad technology decisions, redesigned bad database designs and fixed SQL application performance problems, I know that reducing costs is always on everyone’s mind. Upgrading any software especially raises cost consciousness.
To minimize upgrade costs, strive for better understanding and knowledge of the DB2 technology. This will help everyone avoid unnecessary costs and bad decisions. Work on minimizing the DB2 10 upgrade costs through better management of your DB2 systems today. I’ve mentioned the considerations of SMS environments and improving your security definitions to get ready to separate security data access.
This week the suggestion for preparing for DB2 10 is to begin analyzing your DB2 application techniques. The first thing to analyze is how your plans and packages are put together. IBM’s DB2 engineers have been telling everyone to get to DB2 packages within their environment for many releases. In DB2 10 the old plan management technique of DBRMs going directly into plans needs to be replaced by collecting them within COLLIDs and then binding into your DB2 plans. It is a small difference but it has big implications. If your shop still uses the technique of putting DBRMs directly into plans, it is time to start a process to migrate to the new method.
Next, the compliers that your applications utilize also need to be current. DB2 10 changes within the COBOL pre-compiler generate new SQLCA and SQLDA structures along with generating COMP-5 binary data attributes instead of COMP-4 or COMP. Even though there are still some ways to handle old compilers it is best to review your COBOL compiler versions to make sure they will be ready for your eventual DB2 migration.
Dave Beulke is an internationally recognized DB2 consultant, DB2 trainer and education instructor. Dave helps his clients improve their strategic direction, dramatically improve DB2 performance and reduce their CPU demand saving millions in their systems, databases and application areas within their mainframe, UNIX and Windows environments.
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.
DB2 10.5 “Cancun Release” was released as DB2 LUW Version 10.5 Fix Pak 4. IBM has put a number of impressive features in this new update. Within DB2 10.5 “Cancun Release” Fix Pak 4 there are several new and interesting monitoring elements. These new monitoring elements provide capabilities to dig deeper into performance tuning efforts to get more information and feedback. These new monitoring elements come in relation to time spent, sort heap, and more information about the SQL within the package cache.
Know where you spent your time. Within DB2 10.5 “Cancun Release” there are new monitoring elements that track online backups and index creation processes. The first two time-spent elements, total_backup_time and total_backup_proc_time, capture the total elapsed time and the amount of non-wait processing time of the backups. Also, a total_backups monitor element counts the total number of backups completed. These backup monitoring elements give administrators additional information on their backup processing efficiency and show how much time is necessary for their backup procedures.
In addition to these backup monitoring elements, there are similar monitoring elements that track index creation, re-creation, or rebuild. For the index monitoring there are total_index_build_time and total_index_build_proc_time elements which capture the total elapsed time and the amount of non-wait processing time of the index creation or rebuild.
The monitoring element of total_index_build_time is the parent of element stmt_exec_time. The element total_index_build_proc_time is parent to the total_rqst_time element along with being parent to the stmt_exec_time element. There is also the element total_indexes_built which tracks the number of indexes built. This index monitoring elements provide especially useful information when DB2 automatically rebuilds indexes during an online reorg or when DB2 automatically rebuilds indexes after running out of space or another index error situation.
Understand your sort heap requirements. There are several new sort heap monitoring elements in the DB2 10.5 “Cancun Release.” These elements provide a huge amount of additional information about the sorting processes within your DB2 system. All of these elements help you understand more attributes of the sort consumers, how much of the sort heap is being dominated by a particular process, and how much memory these concurrent sort processes are utilizing. All of these sort heap monitors will also help automatic memory management understand your workload and manage the memory to maximize overall performance better.
With all of these new sort heap monitor elements and automatic memory management, you have a number of elements to tell what is happening within your system.
More Cache SQL Information. Next within DB2 10.5 “Cancun Release” there is more information provided by monitoring elements shown through the MON_GET_PKG_CACHE_STMT_DETAILS table function. These new monitoring elements help out in two main ways. The first, and probably the most important is monitoring SQL for suboptimal compilation. Through the prep_warning and prep_warning_reason monitoring elements, the table function can warn about SQL that might adversely impact your performance.
Get Cache SQL Object information. Through the more general MON_GET_PKG_CACHE_STMT table function, even more information is available about SQL in the package cache. The wide variety of data available now expands information about the statement id, plan id, and semantic_env_id which indicate whether or not the default schema was being used for the SQL statement.
All of this information along with the MON_GET_PKG_CACHE_STMT_DETAILS table function information can help monitor, warn, and help you better understand the attributes of SQL going against your most important performance sensitive objects.
Save information for later analysis. DB2 10.5 “Cancun Release” also adds new MON_GET_SECTION and MON_GET_SECTION_OBJECT table functions. These functions let you gather information and save it for later analysis. With the MON_GET_SECTION function you can save SQL information and pass it along to the EXPLAIN_FROM_DATA stored procedure to get more details on the SQL access path.
With MON_GET_SECTION_OBJECT table function DB2 translates the internal identifiers used in the package cache to the object type, object schema, object name, and object module information.
All of these new monitoring elements are only available in DB2 10.5 “Cancun Release” which should give everyone more motivation to adopt the new Fix Pak as soon as 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.
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…
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.
Previously, I’ve talked about the new DB2 10 temporal tables and how they are great for data warehousing applications. To leverage the temporal tables and data warehousing applications within the DB2 for z/OS environment the system needs the proper DSNZPARM settings. Since everyone has too many systems, some organizations only review system DSNZPARMs during migrations, meaning that many of the new settings might not be set up or enabled. 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.
First, 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 created an alphabetic list of DB2 data warehouse DSNZPARMs. Making sure these data warehouse DSNZPARMs are enabled and adjusted for your system will help your application fully exploit DB2 and get the best performance possible for your data warehouse.
Another situation when a DB2 Java transaction runs into problems is when it must check something outside of the critical transaction path or its normal activity. For example, when a DB2 Java transaction uses seven different discrete web services to accomplish a complete transaction unit-of-work and after the third web service runs into a situation where something else needs to be checked. The processing then tries to resolve the situation by accessing another service and the new service experiences an error exception. In most DB2 Java database transaction environments the previous three services’ work would be rolled back and the entire transaction would need to be restarted.
Within good DB2 Java processing designs the extra situation checking would be moved out of the standard flow and services transaction processing. Starting another unit of work is understandable for these double checking situations only because we want to retain the integrity of the first group of services activity already completed. Analysis needs to be completed to determine the number of times the exception processing is needed and how many times it errors out with an exception.
One of the new features within DB2 Version 9.7, Cobra, is called autonomous transactions. This allows a transaction to commit a block of statements independent of an invoking transaction. This invoked autonomous transaction, implies that the work done is committed even if the invoking transaction itself is rolled back. This feature is perfect for this type of exception processing within DB2 Java applications and can be easily implemented within a web service structure.
Within the zEnterprise announcement in July 2010 there were several exciting
comments about the new query performance within the IBM Smart Analytics
Optimizer. This smart system leverages the integrated platform, providing
industry leading scalability for data warehouse and business intelligence
Queries that scanned entire terabytes of data that were previously avoided
can now be executed in seconds through the IBM Smart Analytics Optimizer
processing. This capability provides data mining features for quickly getting
answers to the clustering, associations, classification and predictions within
your data warehousing environment.
Within some of the documentation, IBM testing of the Smart Analytics
Optimizer shows huge performance boosts. Queries that scanned and then
subsequently optimized sometimes showed an improvement of 54 times, the cost of
the query 711 times. This type of improvement is very exciting and especially
what new data warehousing and business intelligence workloads need on the
Check out all the
zEnterprise documentation—especially the information on the IBM Smart Analytics
Optimizer. This was just the beginning of a resurgence of the new mainframe
that supports any mainframe, UNIX or windows workload. Known as a company’s
“private cloud,” it allows IT departments to leverage tremendous performance
improvements while reducing query cost substantially.