Managing the data lifecycle
Anson_Forum 120000KVU9 7,472 Views
In today's economy doing more with less is imperative. You want to get the most out of your DB2 investment because your enterprise depends on it. IBM Data Server Manager is a simple, scalable and smart solution to administer, monitor, manage and optimize performance for DB2 for Linux, UNIX and Windows databases across the enterprise. Let's take a look at the different facets of where IBM Data Server Manager can empower you to do more.
Let's start with performance. Think about all those many times where you were responsible for diagnosing and troubleshooting a performance problem. There are many factors that you need to consider in order to find out the root cause of performance problems like CPU time, constrained network, data skew, poor statistics and many more.. Solving database performance problems in a multi-tiered environment can be very daunting to say the least. It's good to know that IBM Data Server Manager is there to help by providing access to real time and historical performance data, capability to identify potential data access bottlenecks, comparing SQL performance over time, and even get alerted of emergent issues using proactive alerts.
Let's now look at query tuning. Use the query tuning capabilities and let all the built in advisors and statistics make recommendations on how to fix poorly performing queries. Don't know where you can take advantage of BLU Acceleraiton yet? With IBM Data Server Manager you can get recommendations on what tables will get the most benefit if you convert to BLU...This means you can spend less time wondering where BLU would be of value and more time implementing the next generation of in memory computing for DB2.
Having troubles identifying all the different clients that are connecting to your database server? Let IBM Data Server Manager help you control what those clients are doing and even take an inventory of different connection properties from one central location so that you can control how they should be set. You can also control memory management and let the tool advise you on where to take advantage of adaptive compression in DB2 10.5.
From a database administration capability IBM Data Server Manager is there to help the novice user run various utilities against DB2 like setting up BLU tables, but it also helps the non-DBA user to do things like edit and run SQL statements with help using content assist. Other areas of interest for the DBA are catalog navigation, object management, privilege management, maintenance and availability management.
Think about all of these capabilities contained in one product. IBM Data Server Manager provides a simple quick and easy install and simplified user experience with an integrated web console. Look at real time and historical metrics that can scale to 100's of databases in your enterprise. Use the smart alerts in IBM Data Server Manager to optimize query performance and give guided problem diagnosis and workflow analysis from investigation to action.
In order to be the most efficient out there you want to stay ahead of your competition by being more lean and fit. DB2 database software already leads the way in terms of performance, scale and reliability. Let IBM Data Server Manager help you make the most out of your database investment allowing you to increase SLA attainment, reduce IT costs and align with business priorities.
Anson_Forum 120000KVU9 6,923 Views
This week with the release of DB2 Cancun, we also announced many new releases of IBM Database Management Tools for Linux, UNIX and Windows. Here is a summary of the new releases of the tools:
High Performance Unload 184.108.40.206
DB2 Recovery Expert 220.127.116.11
DB2 Merge Backup 18.104.22.168
Optim Performance Manager 5.3.1
Data Studio 4.1.1
Optim Query Workload Tuner 4.1.1
InfoSphere Data Architect 9.1.2
DB2 Connect 10.5.0.4
Optim Workload Replay 22.214.171.124
From Day 1 we are building on top of DB2 Cancun capabilities to provide support for key new features such as DB2 BLU, pureScale and the newly announced shadow tables.
I would also like to introduce the beta for IBM Data Server Manager. This is the next generation of tooling that will integrate key capabilities from the database tools platform for DB2 for Linux, UNIX and Windows. We strive to take a design first approach to this tooling so that users can enjoy day to day database administration and performance management tasks. Early feedback has been very positive and I encourage you to take a test drive so you can help us shape this new tool. See here to participate in the IBM Data Server Manager Early Access Program.
Whether you are an IOD newbie or expert it never hurts to take some time before the conference to explore and learn about the different session offerings. Information On Demand (IOD) 2013, IBM’s biggest conference for information and analytics, is happening November 3rd – 7th at Mandalay Bay in fabulous Las Vegas Nevada. With just a couple days left to finish planning your IOD schedule, time is valuable. This year there are plenty of opportunities to learn more about database management, and more specifically learn more about Optim Database Tools. The following link https://www-950.ibm.com/events/global/iod/agendabuilder/preview.html?agendaid=165 provides you with a list of all the Optim Database Tools sessions at IOD in a saved agenda from the official IOD site. There are 39 different Optim Database Tools sessions to choose from, that include elective session, hands-on labs, and usability sandbox sessions. Happy IOD planning!
Hot off the press is a new Redbook on InfoSphere Optim Performance Manager and InfoSphere Optim Query Workload Tuner here: http://www.redbooks.ibm.com/abstracts/sg248111.html?Open
You'll find architectural overviews, planning and installation guidance, feature overviews, as well as usage scenarios. This is a great reference whether monitoring transaction processing or business intelligence workloads.
Let us know if you find this helpful!
vpetrill 0700000NDM 4,993 Views
BLU Acceleration has been available for a few weeks now. Have you had a chance to try it? One of the design themes for BLU was "simplicity". No SQL changes. Just enable BLU on your database, convert some tables and presto, your queries run WAY faster. Is it that simple? Well pretty much, but what do you need to do to enable BLU on a database? How do you know what tables to convert. I have just learned that Danny Zilio, a Senior Software Developer in the Optim Query Workload Tuner team, and Marcia Miskimen, QA lead on product integration will be doing a presentation on "Adopt BLU Acceleration in DB2 10.5 the Faster Easier Way". In this presentation, Danny and Marcia will step you through the whole process from start to finish. They will show how to measure your performance before and after to see how much BLU Acceleration has helped you. The presentation is July 25, 2013. Join the webcast here: bit.ly/tt2013jul.and bring a friend!
Hope you can attend or watch a recording of it so you too can BLU.
Data management solutions for productivity, database performance, and availability are now available
vpetrill 0700000NDM 4,844 Views
We have been blogging about some of the new features that were announced here and in my "BLU Acceleration and IBM Infosphere Optim Database Management Solutions" blog. I am pleased to announce that, along with DB2 10.5 for Linux, UNIX and Windows, all of the following are now available:
IBM Data Studio V4.1
IBM DB2 Merge Backup for Linux, UNIX and Windows V2.1
IBM DB2 Recovery Expert V4.1
IBM InfoSphere Configuration Manager for DB2 for Linux, UNIX and Windows V3.1
IBM InfoSphere Data Architect V9.1
IBM InfoSphere Optim High Performance Unload for DB2 for Linux, UNIX and Windows V5.1
IBM InfoSphere Optim Performance Manager Extended Edition for DB2 for Linux, UNIX, and Windows V5.3
IBM InfoSphere Optim Performance Manager for DB2 for Linux, UNIX, and Windows V5.3
IBM InfoSphere Optim pureQuery Runtime for Linux, Unix, and Windows V3.3
IBM InfoSphere Optim Query Workload Tuner for DB2 Linux, UNIX, Windows V4.1
Look for these on passport advantage and download Data Studio directly from developerWorks
Have a great weekend - Vince
vpetrill 0700000NDM 5,110 Views
To set a checkpoint, right click at the space right before the desired line and select "Add Checkpoint".
When Data Studio 4.1 is available, check this feature out. I am sure you will find it valuable.
One of the issues that users have raised to me is the impedance mismatch between our web-based tools and our eclipse-based tools, in particular, between InfoSphere Optim Performance Manager (Performance Manager) and InfoSphere Optim Query Workload Tuner (Query Tuner). Query Tuner availability on the Eclipse platform grew out of our rich investment in Eclipse and our strategy to drive improved query performance early in the development cycle. Thus, key integration with Data Studio and Rational development features were, and continue to be, highly valued.
Key use cases for performance management involve using Performance Manager to identify high cost queries and transfer them to Query Tuner for analysis and advice. Performance Manager makes it easy to identify high cost queries (either long running queries or short queries that are costly in aggregate) or to identify query workloads that are not meeting their response time targets. The handshake between Performance Manager and Query Tuner provides the full problem identification through problem resolution support. However, Performance Manager provides a web console and Query Tuner is an Eclipse-based client, so there is a transition that can be unexpected, and for some, unwelcome. With the new releases coming in June, we are embarking on delivering query tuning features directly in the web console! This release is a first step, so all features are not available, but it is indicative of our direction to make the cycle of problem identification through problem resolution a seamless experience. When the Eclipse client is installed and open, we will still transfer the queries to the Eclipse client since it has the full query tuning function.
Features available from the web will now include the ability to:
The screen capture below previews the query tuning tab right inside the Performance Manager web console. The embedded job manager in Performance Manager is used to execute the query tuning jobs. All the submitted query tuning jobs can be viewed and selected in the grid including their status and progress.
Features still requiring the Eclipse client are:
Also, you still need the Query Tuner license activated on the target database to be able to use the function from the Performance Manager web console. The license activation capability is still in the Query Tuner Eclipse client.
Hope you will enjoy this new function. Give us some feedback on our Query Tuner forum or post a question here.
Ravi_M 270001R0TA 6,139 Views
IBM® DB2® pureScale® (also known as DB2 application cluster transparency) feature introduced clustering technology to DB2 on distributed platforms, so you can deploy continuously available and scalable database clusters. The technology is based on the IBM DB2 for z/OS® architecture that businesses worldwide trust for their most critical systems.
IBM InfoSphere® Optim™ Configuration Manager for DB2 for LUW V3.1 (OCM) includes the following capabilities for IBM DB2 pureScale® feature:
Although there are many capabilities in OCM V3.1 for IBM DB2 pureScale® feature, this blog entry focuses on the isolate application transactions capability (#6 in the above list).
Isolate Application using Optim Configuration Manager:
Isolate applications on DB2 pureScale capability allows DBAs to isolate application workloads to certain members of a DB2 pureScale group. This capability is invaluable for enterprises when they want to:
I’ll use an example to describe this capability, the figure below shows the application “A” and “B” running on against a DB2 pureScale group. DB2 pureScale member “2” has been identified as the member on which isolated applications will execute (penalty box).
A user can identify misbehaving applications (applications that may have an unusually high CPU utilization) via IBM InfoSphere Optim Performance Manager for DB2 for LUW (OPM). OPM can also identify impacted applications. In the example, application “B” is the misbehaving application and as a result the resources available to application “A” are impacted. The mission critical application “A” therefore has poor response times and the effect cascades to any other applications running on the DB2 pureScale group.
OCM can be invoked from the OPM dashboard.
You can define and use client rules in OCM to control applications connecting to DB2 for LUW servers. Additionally, rules can be used to control drivers, data sources, and transactions of user applications and the connections to DB2 for LUW servers. You can create client rules for managed clients only. Managed clients are those clients on which IBM InfoSphere Optim Data Tools Runtime Client is installed. Before you create a rule, you first need to create a rule set, which is a named group of rules.
A rule with “Isolate application transactions” type can be defined which isolates a resource-consuming application on DB2 pureScale to a restricted environment so that other applications can obtain the resources they need to perform optimally. This rule moves an application, a group of applications, or a workload within an application to a pre-configured location alias (penalty box) of a specific member in a DB2 pureScale.
Steps to isolate an application to specific DB2 pureScale member(s) in Optim Configuration Manager:
The following steps are not comprehensive; I’ve attempted to highlight the steps involved in isolating an application.
Step 1: Setup up a location alias for members in a DB2 pureScale group
For a DB2 pureScale group, you can define or update DB2 pureScale aliases using a DB2 call to the SYSPROC.WLM_CREATE_MEMBER_SUBSET stored procedure.
For example to create a location alias called MY_PBOX for DB2 pureScale member ID 2:
A location alias represents one, a subset, or all members of a data sharing group.
The following screen shot shows location aliases ALIAS1 for DB2 pureScale member ID 1, ALIAS01 for DB2 pureScale member ID 0 and 1, and PBOX for DB2 pureScale member ID 2.
Step 2: Define a rule to isolate an application
To create a rule for a managed client, choose Open > Managed Clients, select the application client and click on “Add Rule…”.
The “Rule Set Manager” tab is opened and the Rule Set name, which was automatically created when the connection to the DB2 pureScale server was defined, is automatically filled in. Specify any Name for the rule (“PBOX” in the following screen shot) and Isolate Application Transactions as the Action to be performed by this rule.
The conditions for identifying the client application are automatically filled in as shown in the screen shot below. Edit the conditions if necessary.
In the Action tab specify the Alias Name to isolate the application to certain member(s) of the DB2 pureScale group.
Save the Rule Set.
Step 3: Activate the rule set
Activate the rule set. Once the rule set has been activated, OCM directs new connections to the DB2 pureScale members identified by the location alias “PBOX”.
The following screen shot shows the activated Rule Set.
Anson_Forum 120000KVU9 6,941 Views
I think that you have all seen the various announcements and press around the new DB2 10.5 and all the various features and functions. I wanted to dive a little bit deeper on the DB2 Advanced Recovery Feature. First of all now there is one package that contains all 3 of the backup and recovery tools for DB2 for LUW – DB2 Recovery Expert V4.1, DB2 Merge Backup V2.1 and Optim High Performance Unload V5.1.
With DB2 Recovery Expert V4.1 for Linux, UNIX and Windows one of the most significant features added in this release is the capability now to do granular recovery and log analysis in DB2 pureScale environments. This means that you can monitor changes to the database and quickly restore or correct erroneous data when time is of the essence. Also new in this release is the support for Adaptive Compression and Multi-Temperature Features in DB2 for LUW. The remote log analysis feature means that you can move your logs off your production machine and run log analysis without impacting the database server. Quiet Time Reporting allows you to look for times where there is least activity on the database so you can plan for database intensive activities.
In DB2 Merge Backup V2.1 for Linux, UNIX and Windows we have built on the added capability of allowing you to merge delta and incremental backups with full backups by now letting you work with different types of backups, eg. even if one is on disk and the other on some storage manager like TSM.
Optim High Performance V5.1 for DB2 LUW, which allows you to unload at lightning fast speed either from the database itself or from a database backup – now is integrated with the Optim Masking on Demand product line. This means you can unload and mask all in one step. You can use the libraries that are part of products like Optim Test Data Management, Optim Data Privacy, etc. and call them directly from within the HPU product. Also new in this release are enhancements to install and configure more easily and more performance enhancements to make this tool even faster than it already is.
These releases are optimized for DB2 10.5 Linux, UNIX and Windows but work equally as well with older versions of DB2. Have a look at these tools if you are looking for capabilities to help improve data availability, mitigate risk and accelerate crucial administrative tasks.
vpetrill 0700000NDM 5,412 Views
Yesterday was an exciting day with some very important announcements. IBM announced DB2 for Linux, UNIX and Windows Version 10.5 with the new column-organized tables that utilize BLU Acceleration; a unique and advanced columnar data store with advanced compression and hardware exploitation. IBM also announced updates to a number of database management solutions to help develop, monitor, manage and tune your DB2 LUW database. All of these solutions support the new column-organized tables.
hollyann 100000SR9G 4,836 Views
If you're heading to IDUG, then don't miss Sue Sterling's session "C05 - Highlight Zone-MetLife DB2 LUW-Tooling Time". This is a great opportunity to hear how this enterprise customer manages hundreds of DB2 databases.
Abstract: At MetLife we “create time” for our DBAs with performance, availability, cost reduction, and expense avoidance tools. The presentation shares our DB2 LUW footprint with easy-to-follow examples illustrating OPM 5, STMM, WLM, ROS and V10 Orac
vpetrill 0700000NDM Tags:  tuning opm tuner releases ods data-studio petrillo ds performance optim purequery oda query data_studio fixpacks 6,923 Views
Today IBM announced updates to several performance management products, all of which are available individually or as part of the DB2 Advanced Enterprise Server Edition. They include:
We all know that poorly performing applications can lead to lost revenue and customer dissatisfaction. We have made numerous improvements in all these products to make it easier to identify and resolve issues before they can impact your business. There are far too many improvements to list but some notable improvements include:
IBM InfoSphere Optim Performance Manager V5.2
We have added enhancements to better analyze stored procedure performance. Rank stored procedures and drill down to find poorly performing SQL. We have extended reporting now with scheduling, emailing, and retention management. To help you automate actions, we have provided the ability to trigger user defined jobs in response to alerts.
IBM InfoSphere Optim Query Workload Tuner for DB2 Linux, UNIX, Windows V3.2
Once you have identified SQL that could use some help, use Query Workload Tuner to provide suggestions and analyze possible improvements or use Query Workload Tuner to analyze SQL before it ever goes into production. V3.2 has many enhancements to help with "what-if" analysis. Use Query Workload Tuner and virtual indexes to analyze the impact of adding, dropping or changing an index. Use the plan comparator to see the changes to access plans.
IBM InfoSphere Optim pureQuery Runtime for Linux, Unix Windows V3.2
Suppose you have poorly performing SQL from a third party application. pureQuery Runtime can help with that. Use pureQuery to swap out poorly performing SQL. With V3.2, new configuration scripts makes it easier to deploy on WebSphere Application Server and configuration changes can go into effect immediately.
IBM Data Studio V3.2
IBM Data Studio has also been updated. Many enhancements were made to alerts and scheduling in the web console. Users can now define their own alert view preferences. The client was updated to make debugging even simpler and more robust. Debug triggers and PL/SQL anonymous blocks. You can now view global and package variables, associative arrays and array of rows in the debugger. It is also much easier to recompile a routine for debugging. It is almost automatic. Starting with V3.2 the Data Studio administration client will be available via DB2 Express-C and refreshed as needed. The full client is available via the normal Data Studio download page. Remember, for the full client, when using the Installation Manager option, only the options selected will be downloaded and installed on your system. If you are using the administration client, try the full client.
Finally, connection management is now easier for many of these products. IBM InfoSphere Optim Performance Manager, Query Workload Tuner, and Data Studio can now obtain and stay synchronized with connection information from the Optim Configuration Manager's database repository.
For more information, check the announcement here.
At the end of August we released a new fixpack for InfoSphere Optim Performance Manager (OPM) 126.96.36.199. In addition to defect rollup, there were a few functional enhancements as well:
For Tivoli OMEGAMON XE for DB2 Performance Expert on z/OS users, Optim Performance Manager added stored procedure monitoring enabling a view of the aggregate expense of a stored procedure, analysis of calling paths, and correlation of SQL calls with the stored procedure that issued them.
Sorry for the late notice. Cliff Leung, chief architect of Optim Query Workload Tuner, and I will be presenting a webcast on Optim Performance Management this Thursday via the DB2 Tech Talk series. This setting provides an opportunity to drill down a little given that we have 90 minutes. We are hoping that it will be informative with lots of time for questions. You can register here. I hope you will join us.
If you haven't heard much about IBM PureApplication Systems, now is the time! With built-in expertise to deploy transactional and datamart patterns in private clouds, PureApplication is really revolutionizing time to value and infrastructure simplification. And for Optim followers, DB2 and Optim Performance Manager come built-in :-) There's going to be a Twitter Chat Monday from 1 to 2 p.m. EDT (5 to 6 p.m. GMT) entitled "IT Infrastructure Improvements for Midsize Businesses" moderated by Paul Gillin, a veteran technology journalist and a thought leader in new media. I particularly enjoyed the PureSystems video intro by Jason McGee. Hope you will too!
cleung@svl 060000HEVU 5,280 Views
In October 2011, we released a new product: InfoSphere Optim Query Workload Tuner for DB2 for Linux, UNIX, and Windows. As with InfoSphere Optim Query Workload Tuner for DB2 for z/OS, you can capture and save a set of queries into a workload, and then launch advisors to get expert advice for tuning the workload as a whole. The advisors are the Workload Statistics Advisor, which can recommend RUNSTATS control statements for collecting statistics on database objects that are used by the workload, as well as scripts for creating statistical views and modifying existing ones; and the Workload Index Advisor, which can recommend new indexes and changes to existing ones.
We released this product as version 3.1, to coincide with the 3.1 release of the DB2 for z/OS product. Earlier this year, we delivered InfoSphere Optim Query Workload Tuner for DB2 for Linux, UNIX, and Windows, Version 3.1.1, which contains a number of enhancements that make use of new features in DB2 V10 for Linux, UNIX, and Windows.
Apart from exploiting new features in DB2 V10 for Linux, UNIX, and Windows, we further enhanced InfoSphere Optim Query Workload Tuner by
RichieEscarez 270000WWFW Tags:  3.1.1 center roadmap data whats new information help studio 8,201 Views
Now that IBM Data Studio Version 3.1.1 is available for download, we thought it would be helpful to share this list of documentation links to help you with everything from finding what's new in this release to getting you up and running in IBM Data Studio:
IBM Data Studio Version 3.1.1 release information:
Data Studio V3.1.x features: by data server and component
Roadmap, overview, and help information:
IBM Data Studio 3.1.x Information Roadmap
Quick Start Guide for IBM Data Studio Version 3.1.1
Shell-share IBM Data Studio:
Review the list of compatible products to determine if you can shell share IBM Data Studio.
Still can't find what you're looking for? Send me an email @ email@example.com
Anson_Forum 120000KVU9 5,269 Views
As mentioned in Holly's blog dated April 3, 2012 - Optim Releases Support DB2 10.
I wanted to drill down a little further into what enhancements are available for the DB2 Advanced Recovery Solutions with respect to support for DB2 V10.
With the new release of DB2 Recovery Expert V3.1.1 you can now restore altered, incorrect or missing DB2 V10 for Linux, UNIX and Windows database assets including tables, tablespaces, indexes and data to a specified point in time often without taking the database or business operations offline. You can also use log analysis to generate reports on DB2 V10 for Linux, UNIX and WIndows databases and monitor changes indicating what was changed and by whom. This in turn allows the DBA to correct or restore erroneous data using less resources and reducing data outages.
DB2 Merge Backup for Linux, UNIX and Windows V1.1.1 allows you take full backups from DB2 V10 for Linux, UNIX and Windows databases and merge them with incremental and delta backups to provide the most consistent and up to date backups needed in case of recovery.
InfoSphere Optim High Performance Unload for DB2 for Linux, UNIX and Windows V4.2.1 is a data extraction tool that aids in recovery and migration purposes. Use this tool to extract individual data from DB2 V10 for Linux, UNIX and Windows backup files even when the database is not available for the fastest possible recovery time. You can also extract directly from the live database many times faster than what is available via regular database utilities.
All of these tools have eGA'ed on April 30, 2012 and have trial versions available for those that want to try it out.
DB2 Recovery Expert V3.1.1 Trial Version:
DB2 Merge Backup V1.1.1 Trial Version:
InfoSphere Optim High Performance Unload V4.2.1 Trial Version:
Look at the product specific main websites for more details....
Here is my promised update on InfoSphere Optim Performance Manager (OPM) 5.1.1 that will be available April 30th. As I mentioned in my earlier posting Optim Releases Support DB2 10, the majority of the enhancements are in support of DB2 10 features, so let me go over those first:
In addition, there are a few enhancements apart from DB2 10 support I want to mention that I think you’ll be excited about.
More information, including the Information Center updates, will be available with the general availability.
vpetrill 0700000NDM Tags:  fixpacks optim ods ds data-studio releases data_studio oda petrillo 2 Comments 9,775 Views
We have been busy working on an update to Data Studio just in time for DB2 10.1 for Linux, Unix and Windows. This release includes enhancements throughout the product as well as the added support for DB2® V10.1 for Linux, UNIX, and Windows databases which include the following features:
These DB2® V10.1 features are fully supported by Data Studio making it easier for you to take advantage of them.
Other product enhancements include updated syntax checking, additional support for pureScale, improvements to SQL routine support, improved performance information gathering, as well as enhancements to query tuning, shell sharing, and the Data Stuido web console, here are the details:
The query tuning features in Data Studio have been enhanced to include the following:
In addition to the Data Studio query tuning enhancements, the client for InfoSphere Optim Query Workload Tuner is now part of the Data Studio v3.1.1 product. After you install Data Studio v3.1.1, all that you need is a license to enable InfoSphere Optim Query Workload Tuner, simplifying the install process for those of you who use both Data Studio and InfoSphere Optim Query Workload Tuner.
On top of all the new functionality, the Data Studio v3.1.1 clients are now built off of Eclipse 3.6, which means the Data Studio full client is now compatible (for shell-sharing) with newer Eclipse based offerings such as the latest IBM Rational Application Developer and Rational Developer for System z.
For the Data Studio web console, we have included the following enhancements:
Visit the Data Studio product page and download from developerWorks. Data Studio is bundled with a number of IBM data servers such as DB2 for Linux, Unix and Windows, DB2 Connect and IBM Informix. For a good overview, check out the "Getting started with IBM Data Studio for DB2" ebook.
Greetings Optim Community,
I’m hoping that you saw the big DB2 10 for Linux, UNIX, and Windows announcement or will be attending the virtual conference The Future of Database and Data Warehousing Software today through Thursday. Concurrent with the upcoming delivery of DB2 10, scheduled for April 30th, our InfoSphere Optim tools team is planning a set of releases designed to provide support for the new DB2 10 features. We hope these enhancements will help you get going with new features including time travel query, row and column access control, multi-temperature storage, adaptive compression, and query performance improvements. The new releases are:
The new capabilities help developers, DBAs, or security administrators
Furthermore, InfoSphere Optim tools continue to extend support for production deployments of DB2 pureScale environments with added configuration, administration, and monitoring capabilities. Developers will benefit from new tool support that facilitates application migration and co-existence, with added support for PL/SQL procedure profiling and Oracle database federation definition.
And those are just the enhancements focused on DB2 10. Over the next few weeks, we’ll be blogging on the releases in detail including enhancements not mentioned here.
And more BIG news: DB2 Advanced Enterprise Server Edition now includes more of the InfoSphere Optim portfolio: specifically InfoSphere Data Architect, InfoSphere Optim Query Workload Tuner, InfoSphere Optim pureQuery Runtime, InfoSphere Optim Performance Manager Extended Edition, and InfoSphere Optim Configuration Manager. The Fillmore Group is doing a webcast April 5th titled DB2 Advanced Enterprise Server Edition -Why You Should Consider it. Check it out.
And stay tuned!
International Technology Group (ITG) has recently published research on the Cost/Benefit Case for IBM DB2 Advanced Enterprise Server Edition and InfoSphere Optim Operational DBA Tools Comparing Costs and Capabilities with Oracle Database 11g. For the many companies considering what platform they will choose for a new application or for a re-engineering effort or for customers that are considering a platform migration, ITG lays out the case for considering DB2. The paper features key DB2 differentiators like compression, automation, workload management, XML support, and pureScale.
While there have been many articles that feature the benefits of DB2 vis-à-vis Oracle, I am particularly excited about this paper because it includes considerations for the value of the surrounding tooling. ITG includes an assessment of the capability, value, and differentiation of the InfoSphere Optim development, administration, and performance management tools. ITG favors the InfoSphere Optim portfolio in the areas of business alignment, cross-disciplinary integration, and predictive analysis. For example, InfoSphere Optim Performance Manager gives DBAs the ability to monitor service level objectives such as database transaction response time by workload so the impact of emerging bottlenecks is apparent. InfoSphere Optim Query Workoad Tuner can then provide actionable expert advice to improve the performance of workloads performing below service level objectives which ITG characterizes as having superior breadth and sophistication compared with Oracle offerings.
I hope you’ll download and read the paper at http://ibm.co/AESE_WP.
Each of the tuning advisors in IBM Data Studio, IBM InfoSphere Optim Query Tuner, and IBM InfoSphere Optim Query Workload Tuner has at least one option that you can set to influence the recommendations that they generate. Some of the advisors have exactly one option, but the advisors that help with statistics and indexes have many more than that. In this blog, I'd like to explain some of these options from time to time because a number of them look complicated, are not documented well, or both.
The options appear in two places. In one place, you can set values globally, in which case the values apply to all statements and workloads that you tune while working in the current workspace. In the other place, you can set values for individual statements and workloads, overriding the global values.
Before you start tuning a statement or a query workload, you can set values for these options in the global preferences by selecting Window > Preferences. When the Preferences window opens, in the tree view on the left expand the Data Management entry, and the expand the Query Tuner entry. Expand the Tune Single Query entry and the Tune Workload entry to see the entries for the advisors. Select one of the advisors and the right side of the Preferences window shows the options for it. After you set values for the options for an advisor, click the Apply button if you want to set preferences for another advisor before closing the Preferences window. After you've finished setting values and have closed the window, you can start tuning.
You can also set values for the advisor options after you have captured a statement or a workload and are either on the Run Single-Query Advisors and Tools page or the Run Workload Advisors page of the Query Tuner Workflow Assistant. On the left side of the page, select Set Advisor Options. The options that appear are the same ones that appear in the Preference window. The difference is that the values that you set here apply only to the current statement or workload that you are running.
Take some time to look through the options and let me know which ones you would like me to explain. In my next post, I'll start with the first option for the Workload Statistics Advisor for DB2 for z/OS, unless someone asks me to cover a different one first.
Robert Heath, firstname.lastname@example.org (Start the subject line with "MDL blog: ")
Information Development for InfoSphere Optim Query Tuner and InfoSphere Optim Query Workload Tuner
Here's a handy tip for a Monday. You might already know that, in InfoSphere Optim Query Tuner and InfoSphere Optim Query Workload Tuner, you can use the client to develop SQL stored procedures. When you are working in a routine editor, you can right-click anywhere in an SQL statement and select Start Tuning to open the Query Tuner workflow assistant. The statement appears in the Query Tuner workflow assistant. From this location, you can run advisors and tools to get analyses and recommendations.
Until version 3.1, if the SQL statement contained variables, the variables were present in the statement after it was imported into the workflow assistant, and the statement couldn't be tuned. However, in version 3.1, the variables are replaced with parameter markers.
For example, the SQL statement in this routine uses the variables ORDER_DETAIL_CODE_IN and SALES_STAFF_CODE_IN. The first is a VARCHAR, the second is an INTEGER.
After you right-click in the routine editor and select Start Tuning, the Query Tuner workflow assistant opens to the Run Single-Query Advisors and Analysis Tools page. The variables are replaced with parameter markers, using the appropriate casts to indicate data types.
From this point, you can click the Select What to Run button and choose the tools and advisors that you want to run on the statement, and proceed tuning as you would with any other statement.
Robert Heath, email@example.com
Have you ever noticed that you can't do anything that someone else hasn't declared to be an art? To use a now commonplace method of generating examples, go to Google and search on the words "art of". To list only those on the first page of results that I get:
Here and there on the internet are some of the opinion that tuning SQL statements is an art. That might be. However, you don't have to rely on artistic inspiration to solve performance problems quickly and resolutely if you are using IBM Data Studio, InfoSphere Optim Query Tuner, or InfoSphere Optim Query Workload Tuner. In fact, tuning generally follows a sequence of steps that can cover many cases and that you can modify when you encounter idiosyncratic problems. The general sequences for tuning single statements and for tuning query workloads are in the information centers for these products, but I want to recap them here, because they might not be widely known.
General steps for tuning query workloads that run on DB2 for Linux, UNIX, and Windows
(The original steps are here.)
General steps for tuning query workloads that run on DB2 for z/OS
(The original steps are here.)
General steps for tuning single SQL statements with the free features in IBM Data Studio full client or administration client
(The original steps are here.)
General steps for tuning single SQL statements
(The original steps are here.)
Do these general steps match those that you take to resolve 80% of performance problems when you are tuning with Data Studio, InfoSphere Optim Query Tuner, or InfoSphere Optim Query Workload Tuner? If not, how do your steps differ?
What sorts of uncommon situations do you find yourself in, and how do you modify these steps then?
Robert Heath, firstname.lastname@example.org (Start the subject line with "MDL blog: ")
rdheath 120000A31Q Tags:  sql tuning roadmap performance documentation 1 Comment 7,519 Views
Using software can be like trying to get to a freeway onramp that you know well by driving through an unfamiliar part of a city. You know what the outcome should look like (cruising on the open highway), but to get there you have to learn new landmarks ("Ah, so I turn left at the donut shop!"), new shortcuts ("So, I saved 5 minutes by taking Jefferson. I'll have to remember that."), the streets that go only one way (*pant, pant* "No, I guess I can't drive that way down Sycamore after all."), and more.
The information roadmap for InfoSphere Optim Query Tuner and InfoSphere Optim Query Workload Tuner will help you get to know the streets and landmarks of those two products, so that you can tune single SQL statements or workloads and get to your open highways faster. You'll find it at http://www.ibm.com/developerworks/data/roadmaps/roadmap_ioqt_ioqwt.html.
If you have any suggestions for links to include in it or have any other comments about it, please let me know.
Information Development for InfoSphere Optim Query Tuner and InfoSphere Optim Query Workload Tuner
Updated on December 15th to correct announcement letter URLs.
More news from Optim land to help you manage DB2 performance. We just announced and today released a new version of IBM InfoSphere Optim Performance Manager 5.1 (for DB2 for Linux, UNIX and Windows servers) and a new product offering IBM InfoSphere Optim Configuration Manager 2.1, which is available for both DB2 for z/OS and DB2 for Linux, UNIX and Windows servers.
InfoSphere Optim Performance Manager 5.1 is a major release in which we re-architected the server to take advantage of the DB2 9.7 state-of-the-art in-memory metrics monitoring capabilities. We significantly reduced monitoring overhead and increased scalability for monitoring partitioned systems, reduced disk footprint for performance data, and improved retrieval times. I encourage you to read about all the new features in the announcement letter, but here are a few of my favorites:
For more detailed information, see the information center.
InfoSphere Optim Configuration Manager 2.1 is available for both DB2 for Linux, UNIX and Windows and DB2 for z/OS environments. It provides centralized management of database and client configurations to get insight into IT environments, reduce database outages, and improve compliance to enterprise standards. Top features enable DBAs and other staff to:
Check out our Configuration Manager demo that highlights several of the features above. You can read more about InfoSphere Optim Configuration Manager in our announce letters for offerings on Linux, UNIX, and Windows or on z/OS
or in the information center.
If you have any feedback, please post here or to our Performance Management forum.
Take Charge of your Data Environment: IBM Performance Management Solutions
Performance issues are often at the source of declining productivity, increasing capital expenditures, and lost revenue. This Thursday, November 10, we’re hosting a webinar, IBM Performance Management Solutions, Featuring OPM and OQWT, to walk you through IBM Performance Management offerings. I'm joined by Bob Harbus, DB2 Technical Specialist and Danny Zilio, Software Analyst – Optim Query Workload Tuner. We'll be discussing how you can use InfoSphere Optim Performance Manager and InfoSphere Optim Query Workload Tuner to manager performance, both during development and in production. Join us to share your questions, challenges, and insights.
Hope to hear you on the call!
Holly Hayes, Optim Tools Product Manager
We heard from many users that they sometimes have trouble finding the information they need for specific products in the multi-product Integrated Data Management Information center. To address this problem, we needed to create individual information centers for the products, and yet still provide a way of tying the information together for the people who need to find information for multiple products. So this is what we did…..
We separated the documentation for most of the solution into individual information centers; the separation is generally according to product. For those users who do need information across products, it's all easier to get to and keep track of because we added a header to our information centers that has tabs that you can use to navigate from one set of information to another.
Much like the Data Studio Task launcher has tabs to organize the tasks, the information centers now have tabs to organize the information by data lifecycle phase. The tabs are: Design, Develop, Test, Administer, Monitor, Tune, Backup & Recover, Archive & Retire, and Community.
If you are in the Data Studio information center, and you want to see the InfoSphere Data Architect information center, just click on the Design tab and choose the product or version that you are interested in. To get back, click on the Develop tab or the Administer tab and choose Data Studio. (Hint: In either case, you will get to the same information center because the Develop and Administer tasks are both part of the Data Studio set of information.)
With this new tabbed header, you can get to information for previous product releases, too. This new design makes it easy to find the version of the information that you want, the product that you want, and the key task that you want.
If you want to bookmark the information center for your favorite product now, here is a list of the URLs. And you only need to bookmark one because with this new design, you'll easily be able to find your way to the rest!
IBM InfoSphere Data Architect v7.6
Data Studio/InfoSphere Optim Information Architect