DB2 for Linux, UNIX, and Windows Best Practices
fitzgarr 27000288SG Tags:  industry models best database practice db2 datawarehouse design 5,068 Views
The table below, an excerpt from the best practices paper “Transforming IBM Industry Models into a production data warehouse” describes guidelines for implementing an intelligent table space design strategy that gives you the flexibility you need to meet your service level objectives for all workloads; not just query, but backup, archive, maintenance, recovery, and ETL.
sboivin 060000F8YY Tags:  data_growth best_practices db2 distribution_key database_design 2 Comments 4,857 Views
The best practice paper Managing data growth provides a wealth of recommendations to help you design and manage a database environment for efficient data growth, including tips on how to choose the right distribution key for a partitioned database.:
Database partitioning helps you to adapt to data growth by providing a way to expand the capacity of the system and scale for performance. A distribution key is a column (or group of columns) that is used to determine the database partition in which a particular row of data is stored. The following guidelines will help you to choose a distribution key.
If you have any comments or questions for the authors of this best practice paper, feel free to log a comment on the paper's summary page and we will respond. You need to login with your IBM ID to be able to enter comments. Registering your Id is free and easy at developerWorks.
The popular DB2 best practices paper DB2 databases and the IBM General Parallel File System is now updated and includes DB2 V10.1 support.
The examples in this paper are based on DB2 V10 fix pack 2 and GPFS 22.214.171.124 efix13 installed on AIX 6.1 TL6 SP5 but can be extended to more recent versions and other supported platforms. All versions of GPFS are supported with DB2 for Linux, UNIX, and Windows, however, the latest supported fix packs are recommended to ensure the best quality experience.
Technical paper summary:
In today’s highly competitive marketplace, it is important to deploy a data processing architecture that not only meets your immediate tactical needs, but that also provides the flexibility to grow and change to adapt to your future strategic requirements. To help reduce management costs, add flexibility, and simplify the storage management of your DB2® for Linux®, UNIX®, and Windows® installation, you need to choose a file system that is designed to provide a dynamic and scalable platform. The IBM® General Parallel File System™ (GPFS™) is a powerful platform on which to build this type of relational database architecture. This paper describes why GPFS is the right file system to use with DB2 databases by outlining the benefits and providing best practices for deploying GPFS with DB2 software. In addition, a section has been added to this paper to describe the DB2 pureScale feature, and how it configures and uses GPFS.
Enda.McCallig 2700028UUP Tags:  database_design table_partitioning db2 range_partitioning best_practices 4,242 Views
Table or Range partitioning is a powerful feature of DB2 that facilitates good database design principles that can help lead to easier maintenance operations, increased data availability and more optimized queries.
But why is table partitioning so good in a warehousing environment? Here are some reasons:
1. Range-specific maintenance operations
Where data partitions (ranges of data within a table) are placed in individual table spaces, maintenance operations can be targeted at active data only.
Many DB2 commands, for example REORG and BACKUP, can be specified to execute against specific table spaces or data partitions. This can significantly reduce maintenance times.
2. Data lifecycle management
Table partitioning large fact tables by date means that older data in a table can be detached as an online operation. This can help eliminate the need to perform costly delete statements.
In addition, as data ages, it can be moved as an online operation to less costly storage. In db2 v10 this multi-temperature data management is facilitated by the new storage groups feature.
3. Partition elimination
Range Partitioning benefits queries where the query spans one or a subset of the range partitioning key.
The DB2 optimizer can then eliminate entire data partitions from the query, and this reduction in rows/read (I/O) can help increase query performance.
4. Local indexes
Local indexes can help to significantly reduce index maintenance and increase query performance where significant sorting is not required.
In addition, local indexes can be placed in separate table spaces which provides more flexibility in building a backup schedule and a recovery strategy.
For example, in a restore scenario you have the choice between restore or rebuild of indexes.
5. Backup performance
Backup performance can be improved by backing up just those table spaces (table ranges) that are active.
By balancing the average size of your table spaces, parallelism within the BACKUP operations can also increase, helping to reduce the elapsed time of your backup operations.
These and other warehouse design issues are discussed in our many papers on warehousing. If you have any comments or experiences you would like to share with the authors, please leave a comment.
sboivin 060000F8YY Tags:  db2 middleware fields workload wlm info management client 3,869 Views
A new supplement to the popular DB2 best practices paper "Implementing DB2 Workload Management" has just been published. The supplement will help you set the DB2 client information fields for a variety of common middleware applications.
You can find it, along with other useful supplements, on the paper's information web page:https://ibm.biz/Bdx2n6
The DB2 client information fields are available on each connection to a database. These fields enable an external application that is using a connection to provide additional information to the DB2 database server that can be used to discriminate among connections based on end-user identification. The values in the client information fields are reported by DB2 for Linux®, UNIX®, and Windows® and other members of the DB2 family through various database monitoring and auditing interfaces. They are also leveraged by the DB2 workload definition in DB2 for Linux, UNIX, and Windows Version 9.5 and later as another way to aggregate connections to the database for purposes of monitoring and control.
Share your impressions and questions about the paper and supplements by adding comments to the web page (you need to join developerWorks and login first).
The new video from the DB2 team, "Getting up and running with HADR", provides a demonstration of how straightforward it is to set up HADR.
As we set up HADR in the video, we provide insight into some of the more important configuration decisions we are making, hopefully heading off some of the more common issues users face when setting up HADR.
Watch the full video on YouTube: http://youtu.be/P4JCd2a4uWk
sboivin 060000F8YY Tags:  installation paper db2 technical silent best_practices new 3,485 Views
We are pleased to announce the publication of a new DB2 for Linux, UNIX, and Windows best practices paper: DB2 V10 silent installation and uninstallation.
You can use DB2 silent installation and uninstallation to install or uninstall DB2 products and components without user interaction. Silent installation is useful for large-scale deployments of DB2 product editions. It is also useful when you need to embed the DB2 installation and uninstallation processes within the installation process of solutions that include DB2 products.
This paper covers the following tasks:
Share your impressions and questions about this paper by adding a comment on the paper's web page: https://ibm.biz/Bdx8Hr You will need to login to developerWorks with your IBM ID first.
sboivin 060000F8YY Tags:  db2 practices best webcast purescale monitoring performance 3,209 Views
You are a busy professional and you don't always have the time and resources to travel to a technical conference, or call into a live web presentation, to listen to technical experts give great presentations about the products and technology you care about. Recorded webcasts offer you the benefits of listening to the same experts, at your own convenience, at the office or at home.
The tips and techniques presented in this webcast reflect information validated through the DB2 team's internal performance testing, as well as performance benchmark tests and customer engagements in real life DB2 pureScale environments.
We are happy to announce the publication of a new best practices paper to help you understand and get the most from the new BLU Acceleration technology introduced in DB2 for Linux, UNIX, and Windows V10.5: Optimizing analytic workoads using DB2 10.5 with BLU Acceleration (https://ibm.biz/BdDrnq)
BLU Acceleration is a new collection of technologies for analytic queries that are introduced in DB2 for Linux, UNIX, and Windows Version 10.5. At its heart, BLU Acceleration is about providing faster answers to more questions and analyzing more data at a lower cost. DB2 with BLU Acceleration is about providing order-of-magnitude benefits in performance, storage savings, and time to value.
New best practices paper: "Building a data migration strategy with IBM InfoSphere Optim High Performance Unload"
sboivin 060000F8YY Tags:  "data_migration_strategy" "ibm_smart_analytics_syst... "infosphere_warehouse" "hpu" "data_migration" "data_warehouse" "high_performance_unload" "db2" "puredata_for_operational... 2 Comments 2,920 Views
Announcing a new best practice paper: "Building a data migration strategy with IBM InfoSphere Optim High Performance Unload"
This paper addresses the topic of data migration and how you can use HPU to build a data migration strategy that can be scheduled to be migrated, automatically, from source to target database with no manual steps.
No longer do you have to grapple with reserving large amounts of storage capacity on the source or target database to stage data; no longer do you have to worry about preserving identity (surrogate) keys; no longer do you have to worry about generating subsets (ranges) of data to be migrated; and no longer do you have to worry about different DB2 software levels or distribution maps.
This newly published and second paper on HPU, the first paper looked at using HPU as part of a recovery strategy, looks at how you can build and implement a data migration strategy using HPU. In testing the recommendations in this paper, we used both an IBM Smart Analytics System and an IBM PureData for Operational Analytics System.
IBM Knowledge Center Open Beta is available!
We have just published a new best practices paper for IBM Smart Analytics System and IBM PureData System for Operational Analytics customers: Performance monitoring in a data wartehouse.
This best practices paper covers real-time monitoring of the IBM Smart Analytics System and IBM PureData System for Operational Analytics. You can apply most of the content to other types of clusters of servers running a data warehouse system with DB2 software and database partitioning under AIX and Linux operating systems. The focus of this paper is finding the reasons for performance problems. These can be bottlenecks that are in the operating system, are in the DB2 database software, or are related to a single query. The focus is on data warehouse systems with long-running queries rather than transactional systems with mostly short queries.
A main goal of this paper is to provide a set of key performance indicators (KPIs) or metrics for the operating system and DB2 software, along with a methodology for analyzing performance problems in a distributed DB2 environment. This paper describes scenarios to help you gather the right information depending on the symptoms of the performance problem.
This paper first provides an overview of the approach and what to consider in general when monitoring the performance of a data warehouse. It then describes the most important operating system and DB2 metrics for multiserver data warehouse systems. The last section describes in detail several performance problem scenarios that are related to data warehouse or BI workloads and explains how to use the metrics for analyzing the problems.
Most of the information about KPIs that are described in the paper has sample commands that extract actual values. However, these examples are not intended to provide comprehensive tooling. You can use this best practices paper as a guideline for selecting the metrics to focus on when using monitoring tools such as IBM InfoSphere® Optim™ Performance Manager.
You can use this paper to complement the best practices paper Managing data warehouse performance with IBM InfoSphere Optim Performance Manager, which covers historical and end-to-end monitoring.
sboivin 060000F8YY 1,769 Views
sboivin 060000F8YY 1,571 Views
One of our most popular best practices paper is now completely revised and updated to provide recommendations for the latest DB2 environments, including DB2 V10.1, DB2 V10.5, and PureData System for Operational Analytics.
Tuning and monitoring database system performance (https://ibm.biz/Bdx2nt) is available for download from our DB2 best practices community.
Most DB2 systems go through something of a “performance evolution”. The system must first be configured, both from hardware and software perspectives. In many ways, this sets the stage for how the system behaves when it is in operation. Then, after the system is deployed, a diligent DBA monitors system performance, in order to detect any problems that might develop. If such problems develop, we come to the next phase – troubleshooting. Each phase depends on the previous ones, in that without proper preparation in the previous phase, we are much more likely to have difficult problems to solve in the current phase.
This paper presents DB2 system performance best practices following this same progression. We begin by touching on a number of important principles of hardware and software configuration that can help ensure good system performance. Then we discuss various monitoring techniques that help you understand system performance under both operational and troubleshooting conditions. Lastly, because performance problems can occur despite our best preparations, we talk about how to deal with them in a step-wise,methodical fashion.
sboivin 060000F8YY 1,476 Views
We are pleased to announce a new video to help you capture and format DB2 and CLI traces: DB2 & CLI Tracing
This video explains the various DB2 trace facilities and how traces can be gathered by IBM business partners and customers when they request IBM software support to investigate problems involving DB2 applications. The DB2 trace facilities are available on all DB2 products.
sboivin 060000F8YY 1,415 Views
Do you want to know more about what DB2 High Availability Disaster Recovery (HADR) is, and how it works?
Read this useful overview in the DB2 Knowledge Center and let us know what you think. It includes a great interactive diagram to show you the "big picture": https://ibm.biz/BdEeYj
New best practices paper: Deploying the IBM Banking Data Warehouse to IBM DB2 10.5 with BLU Acceleration
sboivin 060000F8YY 1,363 Views
We are pleased to announce the release of a new DB2 Warehouse best practices paper: Deploying the IBM Banking Data Warehouse to IBM DB2 10.5 with BLU Acceleration
Implementing industry models can help accelerate projects and reduce risk in a wide variety of industry sectors when enterprises develop and implement business intelligence applications.
sboivin 060000F8YY 1,284 Views
Help us provide you with the best practices you need the most.
What business scenarios do you need to implement with DB2 products that you need practical recommendations to help you?
What issues are you facing for which you have not found practical recommendations to help solve them?
Let us know what topics would be the most helpful for us to write and publish best practices in this community. Simply add a comment to this blog post with your ideas. The more detailed the better!
We will review and evaluate them with our product experts, so that we can prioritize our projects to help as many of you as possible.
We cannot promise to publish best practices that will answer all of your questions, but we will definitely review all of them so that we can prioritize our efforts.
P.S. You will need to login with your IBM developerWorks userid in order to add comments.
NEW Best practices webcast: Deep-dive performance analysis with InfoSphere Optim Performance Manager V5.3
sboivin 060000F8YY 1,260 Views
We are pleased to announce the release of a new DB2 and Optim best practice webcast: Deep-dive performance analysis ysing InfoSphere Optim Performance Manager V5.3
This new webcast by IBM DB2 preformance expert Steve Rees, provides an in-depth tutorial to help you use IBM InfoSphere Optim Performance Manager V5.3 (OPM) to diagnose, analyse, and correct potential performance problems with your DB2 system. Some of the topics covered in the webcast include:
... and more.
Let us know what you think by leaving feedback on the webcast's description page.