We are pleased to announce that the best practices Writing and tuning queries for optimal performance has been updated.
The old PDF paper has been replaced by a roadmap to the most up-to-date information in the DB2 V10.5 Information Center. The best practices information covers the most current DB2 V10.5 products.
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.
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.
Have you downloaded and read any of the DB2 LUW best practices papers and videos in the past year? Has any of them helped you solve a DB2 problem, implement a new scenario, or helped you learn something new? If so, which ones?
Let us know what you think! Post your comments here to help us continue to write and publish best practices that are useful to you.
Welcome to the new DB2 Best Practices developerWorks community group.
You will find here technical papers, videos, and webcasts that offer expert advice on a variety of topics, to help you get the most out of your DB2 solutions, including DB2 for Linux, UNIX, and Windows, DB2 pureScale, and InfoSphere Warehouse products.
The best practices are written and tested by teams of technical experts who work in the IBM DB2 development and quality assurance teams, as well as with customers just like you, to determine and document the practical recommendations that can help you save time and resources on your information management projects.
If you are already familiar with our DB2 best practices on developerWorks, this new group is the next step and replaces the previous site. It includes all the great technical papers that we have published in the past, with the addition of new papers and videos on an ongoing basis.
Although you can already browse and download all the best practices in this group, we encourage you to join the group for added interaction with our teams. As a member, you are able to rate individual papers and leave comments about them, to help us make the best practices better. You can also subscribe to individual pages so that you will get notified when they get updated or when new comments are added.
If you like a particular best practice, you can also use developerWorks' sharing feature to easily share pages with friends and colleagues on a variety of social networks such as Facebook, Twitter, and LinkedIn.
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.
A updated version of the best practices paper entitled "Expanding an IBM Smart Analytics System database and redistributing data" is now available: https://ibm.biz/Bdx2eF
The paper incorporates updated information based on user testing and additional information for IBM PureData System for Operational Analytics.
Access to IBM DB2 information centers will be redirected to IBM Knowledge Center at ibm.biz/IBMKCgo very soon. We encourage you to explore IBM Knowledge Center now, as we are nearing the end of its open beta period.
If you haven't already heard, IBM Knowledge Center offers many benefits that clients are excited about. One of the biggest causes for excitement is that the product documentation for all IBM products is now on one website rather than on 800 or more separate information centers. The interface lets you search, filter, and browse through this information efficiently so that you aren't slowed down by the volume of information that is there.
So, if you haven't had time to check out IBM Knowledge Center, take some time to try it this week. You can find useful information about how to get the most out of IBM Knowledge Center by following this Technical Content blog @ ibm.biz/IBMKCTCBlog.
After you have spent a little time exploring your favorite IBM product documentation in IBM Knowledge Center, you are invited to take a short survey about your first impressions: https://www-950.ibm.com/survey/oid/wsb.dll/s/ag554. We will use feedback that we get from you and other respondents as we consider possible future enhancements to the IBM Knowledge Center user experience.
The trend in programming today is towards greater diversity in datastores that can be applied to a broad set of applications. Developers and Data Architects require the ability to not only work with traditional relational databases but also with document based databases
A Meetup is scheduled for November 6 at the Mandalay Bay Convention Center in Las Vegas to highlight the significance of open interfaces and open source in the vibrant and rapidly evolving world of NoSQL, MongoDB, Big Data in the Cloud. Come meet with us to learn how open technologies are changing the face of computing and how they participate in the evolving open architecture
This is a three hour event with a panel, demos, lightning talks, stimulating discussions, networking and refreshments. Register now for the Big Data Developers Meetup and, after registering, you will see the meetup location at the Mandalay Bay Convention Center in Las Vegas.
More information on the meetup can be found at: http://bit.ly/MeetupIOD
Interact with industry experts. Challenge your knowledge of open technologies. Join the discussion.
We published a new video on our Information Management Best practices Youtube channel: Using IBM dashDB with Esri ArcGIS
This new video tutorial shows you how to use IBM dashDB with Esri ArcGIS software to analyze geospatial data.
You've heard about dashDB and how you can harness the power of Cloud Analytics. See it in action now!
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.
Learn how to convert row-organized tables to column-organized tables with DB2 10.5 with BLU Acceleration: http://www.ibm.com/developerworks/library/dm-1406convert-table-db2105/index.html
This new developerWorks technical paper written by IBM experts shows you several methods to easily convert row-organized tables to column-organized tables in DB2 10.5 with BLU Acceleration. You will then be able to experience all the performance benefits of BLU Acceleration.
This new technical article on developerWorks replaces the previous "data Protection in the Cloud" best practices paper. It is new and demonstrate a use case with IBM BLU Acceleration.
IBM BLU Acceleration for Cloud: A use case
This tutorial demystifies cloud security and arms you with the know-how to adopt the cloud with confidence. Learn how cloud security is a shared responsibility between the cloud service provider and the client. The responsibilities of each party are explored. Also walk through a data security use case involving IBM BLU Acceleration for the Cloud. When certain criteria are met, clients can achieve data security equal to or better than what they can achieve onsite.
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).
Modified by sboivin
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.