We released several fix packs on June 30th and a modification release on July 1. There are a few new features and platform support worth taking time to highlight.
Optim Performance Manager – Optim Performance Manager 4.1.1 is now available. We put a lot of focus on up-and-running improvements in this release. This includes embedding a lightweight web container to simplify installation, configuration, and security, simplifying our port infrastructure for Extended Insight support, improving the out-of-the-box monitoring templates based on customer experience and more. We have added pureScale-specific monitoring enhancements including pureScale monitoring templates, group buffer pool hit ratios, cluster facility lock wait time, escalations, timeouts, etc. We provided more flexibility for monitoring large DPF environments by separating collection intervals for bufferpool versus tablespace and table statistics, which provides more comprehensive information at a lower cost. We have also included DB2 Enterprise Server Edition 9.7 for use with Optim Performance Manager. For the complete list of features and more information, see the IBM Optim Performance Manager 4.1.1 release notes.
Optim Development Studio – Optim Development Studio 220.127.116.11 now also officially supports deployment on Citrix (I know several organizations that went ahead and deployed before our formal support was announced). It also includes two very cool items:
- Integration with Optim Performance Manager – Now you can pull performance metrics into Optim Development Studio to assist in determining which SQL statements need a little more tuning and whether your tuning efforts are paying off. This integration requires Optim Performance Manager Extended Edition with Extended Insight monitoring configured.
- Support for Sybase - You can connect to a Sybase database and browse catalogs, manage objects, create and run SQL scripts, and create, deploy, run, and debug stored procedures. Plus you can use the ANTs Software product IBM® DB2 SSacSA to connect to a DB2 for Linux, UNIX, and Windows database as a Sybase database.
Remember, Optim Development Studio is available on Passport Advantage to most DB2 and Informix customers as part of their DB2 for Linux, UNIX, and Windows, DB2 Connect, or Informix investment. Find it in the Linux or Windows eAssemblies. See IBM Optim Development Studio Version 18.104.22.168 Release Notes
for a more information and download updates from Fix Central
Optim Database Administrator - Optim Database Administrator 22.214.171.124 also adds official support for Citrix deployment. It also sports a new table data editor for DB2 for LUW and z/OS databases and closed a couple gaps in the dependency analysis space for packages and procedures. It also includes catalog filtering improvements tailored to very large databases. Optim Database Administrator is available to DB2 for Linux, UNIX, and Windows customers as part of their DB2 9.7 Linux or Windows eAssembles on Passport Advantage. See IBM Optim Database Administrator Version 126.96.36.199 Release Notes for more information and download updates from Fix Central.
Data Studio – Data Studio 188.8.131.52, both stand-alone and IDE, inherit the Optim Database Administrator Citrix, table data editor, dependency analysis, and catalog filtering enhancements. In addition, this release includes some enhancements specifically for DB2 for z/OS, notably use of TABLESAMPLE keyword on statistics recommendations and catalog navigation performance improvements. For more information, see the IBM Data Studio Version 184.108.40.206 Release Notes. Download updates from Fix Central or a fresh install at developerWorks.
Check them out and post questions, comments, or requests here or on our forums
…and at the same time maintain or improve query performance.
Previously I have blogged about how DB2 Optimization Expert
can help developers produce better performing queries early on during development. So with good query tuning it should be possible to drive down CPU costs for database applications.
However, there’s another way that DB2 Optimization Expert can help drive down CPU costs and it has to do with the general maintenance that DBAs are tasked to perform.
A common practice in many DB2 for z/OS shops is to execute RUNSTATS with the TABLESPACE <database-name.table-space-name> TABLE ALL
option. If you have ever attended one of Bryan Smith’s presentations on utilities, you have heard him say using this option is expensive and wastes CPU resources.
Why is this option expensive? The TABLE ALL option gathers statistics on all
columns of the table(s) in the named table space. The CPU resources required increases as the table size increases and is also dependent on the number of columns defined. Are all of the column statistics really needed? Probably not, because if the column is not referenced in the WHERE clause of a query, then unneeded statistics have been gathered and unnecessary CPU resources have been consumed. Also the TABLE ALL option does not gather COLGROUP or histogram statistics, which might improve filter factor estimates and improve query performance.
The DB2 for z/OS V9.1 Utilities Guide
has a paragraph in the Improving RUNSTATS performance section:
“Run RUNSTATS on only the columns or column groups that might be used as search conditions in a WHERE clause of queries. Use the COLGROUP option to identify the column groups. Collecting additional statistics on groups or columns that are used as predicates improves the accuracy of the filter factor estimate and leads to improved query performance. Collecting statistics on all columns of a table is costly and might not be necessary.”
Easier said than done. How does one perform the analysis required to only gather the statistics that are truly required to maintain query performance and reduce the CPU requirements of RUNSTATS? Without a tool, it’s a very manually intensive process and can be even more of a challenge when working with dynamic SQL. Just to give you a feel for this effort, here are the steps you might need to do:
- Capture the SQL for a given object or set of related objects
- Identify all of the columns which are used in the WHERE clause
- Identify which of the above columns are used in an index
- Capture usage metrics for the predicates
As you can see, this could be a time-consuming and arduous task.
Fortunately, the Statistics Advisor in DB2 Optimization Expert for z/OS can make this analysis less daunting – a lot less daunting. Statistics Advisor will analyze a single statement or group of statements (aka workload) and provide a set of recommended RUNSTATS statements.
To illustrate how easy it is to gather the necessary statistics in OE, I captured a workload from the DB2 catalog for a specific collection. I was presented with 852 queries and then invoked Workload Statistics Advisor on this set of queries to receive the recommended RUNSTATS statements for objects in the workload. The process was completed in less than thirty minutes. For dynamic SQL, I would follow basically the same process, but the workload would have been captured from the dynamic statement cache, or from the profile monitor for DB2 for z/OS V9.1 subsystems.
To validate the CPU usage assertions, I actually executed RUNSTATS twice on one of the table spaces used in the above workload. The first execution was with the commonly used TABLE ALL option: RUNSTATS TABLESPACE DBASE1.TSPACE1 TABLE(ALL) INDEX(ALL)
SHRLEVEL CHANGE REPORT YES
The second execution used the statement recommended by OE’s Statistics Advisor: RUNSTATS TABLESPACE DBASE1.TSPACE1 TABLE(QUAL1.TB1) COLUMN(EMP_NO,PROJ_NO) INDEX(QUAL1.TB1X3 HISTOGRAM NUMCOLS 1 NUMQUANTILES 20, QUAL1.TB1X2, QUAL1.TBIX1 KEYCARD) SHRLEVEL CHANGE REPORT YES
There was a 28% reduction in CPU time between the two runs
. Not only has the CPU consumption been reduced, but additional filter factor statistics have been gathered that should improve query performance.
One topic I have not covered is SAMPLING. It is a technique that can be used to further reduce RUNSTATS CPU consumption by limiting the number of rows evaluated. Statistics Advisor supports SAMPLING and its use is controlled via user-managed preferences.
So in summary, DB2 Optimization Expert’s Statistics Advisors can help DBAs gather the right statistics resulting in less CPU consumption and improved maintenance window throughput. And the additional stats gathered may improve query performance, which is the ultimate goal of running RUNSTATS after all.
Modified on by Anson_Forum
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 220.127.116.11
DB2 Recovery Expert 18.104.22.168
DB2 Merge Backup 22.214.171.124
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 126.96.36.199
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.
I'm looking forward to next Wednesday, July 22nd when I get to participate in one of the virtual technical briefings that Kathy Z blogged about recently
. The topic is InfoSphere Data Architect 101
, and I'm planning to do something with one of the technical architects that is a combination of presentation and demonstration, so hopefully we'll keep it interesting for you.
If you want to get a little background before coming, you can check out this great introductory video
. Also, Holly covered some of the new privacy capabilities in the first virtual tech briefing, Data Studio becomes Optim: What does it mean for you
, which will be available for replay for a limited time. Logistics:
Just sign in
with your computer and email address! Date:
10 AM Pacific, 1 PM Eastern (but sign in 30 minutes early if you can)
The whole thing is done via the computer, so you may want to go to the web site ahead of time and click on the system check
Talk with you soon.
-- Anson Kokkat
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.
It's been less than 5 months since we announced our 1.2 releases of Data Studio, which I blogged about
back in July.
Since then, we have talked to thousands of people, provided demonstrations to hundreds, and visited dozens of customers. People are starting to understand Data Studio and the value of Integrated Data Management better.
With this latest release, announced today, we are really targeting the DBA with enhancements across the portfolio to help DBAs improve application performance, security, manageability, and TCO. In this release, the enhancements are particularly targeting Java applications that access DB2 data, but you'll see we're starting to branch into .NET as well.
The announcements today are for:Data Studio Administrator
2.1, in which we've really focused on both usabilty and functionality. We've done lots of usability testing with DBAs and have provided a more natural approach for doing many tasks, including copy and paste of database changes, flatter traversal of the data source explorer, better sorting and filtering of objects, and new task assistants for utilities, commands and configuration parameters, so you won't have to leave your environment to go out to the command line or control center to perform those tasks.Data Studio Developer
and Data Studio pureQuery Runtime
2.1, which extends the power of pureQuery for developers and DBAs to collaborate together to:
- Eliminate SQL injection risk for Java database applications by giving you the ability to indicate that only SQL that has been captured and approved my be executed.
- Optimize SQL performance by providing developers with the ability to profile the SQL to see immediately how many times a SQL statement is executed, and how long it takes to run (elapsed time), giving developers an easy way to start identifying potential hot spots in the application before coming to the DBA.
- Improve quality of service for OpenJPA and .NET applications. Steve Brodsky blogged about the integration of pureQuery with OpenJPA, which actually was avalable with the 1.2 pureQuery release with WebSphere Application Server v7. For the many many people who ask when can we see the benefits of static SQL with .Net applications, we have taken an initial step in this release by allowing client optimization .NET applications; in other words, the ability to capture dynamically executing SQL and bind them into packages.
Last but not least, DB2 Performance Expert for Linux, UNIX, and Windows 3.2
and the new DB2 Performance Expert Extended Insight Feature
3.2. This is an announcement particularly close to my heart as many of you who have sat in on my talks probably know. Whenever I sit down with DBAs and talk about the problems with diagnosing performance problems in a Java application environment, they always nod their heads in agreement. There is a real pain point here by not having the same diagnostic capabilities for Java as many DBAs are familiar with for COBOL/CICS applications.
If you extend DB2 Performance Expert with the Extended Insight feature (separate PID and separately priced but prereqs DB2 PE), you can enable new end-to-end database monitoring for Java applications for DB2 servers on Linux, UNIX, and Windows. This monitoring capability will really help improve availability of mission-critical database applications by making it much easier to detect performance issues and figure out whether the problem is one in the database or somewhere else in the software stack.
Also, you can set thresholds (your SLAs, so to speak) so you can easily see how the application is performing against those targets. If you haven't read it yet, I encourage you to see the article
that the Germany team who develops this feature wrote. It's a great introduction to this new capability, and it's really just our first step. This whole concept of providing greater insight to DBAs and developers is planned to be rolled out across more databases and more data access environments.
Just a head up. We're not done. We have more announcements coming soon!
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
Happy IOD Season! Kimberly Madia here from the Optim product marketing team, and I am excited to be blogging for the first time. I wanted to share some details about key activities for the Optim tools at the IOD Global Conference in Las Vegas, Nevada, October 24-October 28, 2010. It’s NOT too late to register, check out the conference website for details and registration information. While you are at the conference, come by and visit the IBM team at booth numbers 806-21 and 806-19. We want to hear from you.
You will also not want to miss our big tent events. This is a fantastic opportunity to hear from the executive team and other thought leaders including Arvind Krishna and Steven Adler.
• Expo Grand Opening Reception, Sunday, 6:00 – 8:00 PM
• InfoSphere Community Reception, Sunday, 8:00 – 10:00 PM
• Data Management Keynote Session, Monday 2:45 – 3:45 PM
• InfoSphere Keynote, Tuesday, 3:00 – 4:00 PM
• Information Governance Keynote, Wednesday, 10:00–11:00 AM
• IOD 2010 Networking Party, Tuesday, 7:00 – 11:00 PM
Next, you will have the opportunity to meet Optim tools product experts including several distinguished engineers and of course Curt Cotner, IBM Fellow, VP & CTO for Database Servers. Here are a few of the experts, session numbers and times when these folks will be available to you.
• MTE-3683, Curt Cotner, Tue, 26/Oct 4:30-5:30 PM
• MTE-3684, Stephen Brodsky, Mon 25/Oct 4:00-5:00PM
• MTE-3490, Bryan Smith, Tue, 26/Oct 1:00-2:00 PM
As for technical presentations, we have lots. Certainly too many to mention here, so I will just point out a few highlights. Look for the Optim tools sessions in both DB2 and Information Governance tracks.
Kevin Beck, IBM
Best Practices for Workload Management Using IBM Optim Performance Manager Best Practices Using Optim Performance Manager
Greg Marino, IBM
Real-Life Best Practices with IBM Optim Integrated Data Management from the GBS DBA Practitioners Perspective
Kaushik Bhaskar, IBM
Empowering DBAs with IBM Optim Administration and Tuning Tools
Kimberly Madia, IBM
Client success with an IBM Optim Performance Solution
Daniel Galvin, Galvin Consulting, Inc., Stephen Brodsky, IBM
What's New for IBM Optim pureQuery in Enterprise Java Data Access
Curt Cotner, IBM
IBM Optim and IBM Data Studio Portfolio Strategy: Optimize Performance and Availability while Reducing Costs
Vijay R. Bommireddipalli, IBM, Lief Pedersen, IBM
Managing the Performance of Your IBM DB2 zOS Applications by Using IBM Optim Solutions
Kathy Komer, Aetna, Hui Liao, IBM
Modeling and Designing Databases with IBM InfoSphere Data Architect: A User Experience
Madhu Kochar, IBM
Can You Benefit from IBM Optim Solutions? Yes! Come Hear Our Client Success Stories
Holger Karn, IBM
IBM Optim Performance Manager V4.1 for IBM DB2 on Linux, UNIX, and Windows
Managing Performance of your DB2 zOS Applications using Optim Solutions
Troubleshooting IBM WebSphere Application Transactions on IBM DB2 with IBM Tivoli Composite Application Manager and IBM Optim Performance Manager
Andrew Mackey, Baldor
Baldor reduces costs and improves performance of critical SAP application
Kenneth Larsen, PBS
Optim pureQuery Runtime for z/OS
Anson Kokkat, IBM
Delta Database Experiences Using the New Backup and Recovery Tooling for DB2 Linux, UNIX and Windows
Extract the most value from .NET and ODBC Applications
Optimizing You Business System Using IBM Optim Performance Manager
We have hands on labs covering the following products:
- Optim Performance Manager / Extended Edition
- Optim Development Studio and Optim pureQuery Runtime
- Optim Query Tuner/Optim Query Workload Tuner
We also have usability labs for the following products:
- Optim Database Server and Client Configuration Management
- Getting Started with Optim / Data Studio Tooling
- Optim Query Workload Tuner
- IBM Optim Database Management: Making Database Administration Easy
- IBM Optim Performance Manager
Finally, at IOD you will have numerous opportunities to see live product demos. Optim tools will be available in the InfoSphere demo area as well as all Data Management demo suites.
Well that wraps up the highlights. We in product marketing are certainly excited about this conference. Looking forward to seeing you there! Have a great weekend.
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!
I had two recent visits with customers where I was explaining pureQuery. When I finished what I thought was a nice polished presentation on the subject, both times someone said, "So, I have to use those pureQuery APIs in order to turn my dynamic SQL into static SQL." Ugh. You know that feeling where it seems like you must be speaking in a foreign language because the words just aren't being understood? I felt some relief when Rafael Coss told me that he gets this every time he explains pureQuery, and he has a great knack for making the complex seems simple.
Just in case you are also of the impression that the pureQuery APIs must replace existing JDBC, Spring, Hibernate, etc. calls to the database, the answer is no. The conversion from dynamic to static SQL using client optimization, does not require any changes to your application. Plain old JDBC calls can remain in your programs and with pureQuery Runtime, we can capture the SQL and it can be statically bound to DB2 (z/OS or LUW). This explanation usually creates the "Ah ha" moment.
So, while pondering this, I have come up with a new way to explain pureQuery. I now plan to hold off introducing the APIs until after I finish talking about client optimization and the great capabilities you get when you use client optimization:
- How Optim Development Studio tooling provides the pureQuery Outline to visualize the relationships among Java code, SQL statements and database objects
- How SQL injection can be reduced/eliminated
- How framework-generated SQL can be reviewed and possibly tuned
- How SQL can be revised
- How non-captured SQL can be blocked from reaching the data sever
(By the way, Patrick Titzler's tutorial
, although a little dated, still is the best source I know of to understand the process of client optimization.)
Then, after a deep breath and a new title slide, I plan to talk more about ORM frameworks and our pureQuery APIs. (By the way, if you're curious about how pureQuery relates to ORM frameworks, check out Rafael's ChannelDB2 video
Hopefully the "Ah ha" moments won't get delayed any more. :-)
The fix packs for products in the Data Studio family 2.1 have arrived!
The fix packs includes enhancements and fixes to the Version 2.1 release of IBM Data Studio Developer, IBM Data Studio Administrator, IBM Data Studio pureQuery Runtime and of InfoSphere Data Architect 7.5.1. These fix packs are intended to fix problems you may have experienced in the 2.1 release. For additional information or detail on included fixes please check out these links.Data Studio Administrator Fix Pack 1 InfoSphere Data Architect Fix Pack 1Data Studio Developer Fix Pack 1
For those who already have these products installed on Windows, you will use IBM Installation Manager to apply the fix pack:
- Go to the link to download the compress file for Fix Pack 1 and then extract it to a temporary directory. For example, C:\temp
- Start the IBM Installation Manager by going to Start > All Programs > IBM Installation Manager > IBM installation Manager
- In the IBM Installation Manager, click File > Preferences... this will launch Preferences wizard. What you want to do here is click "Add Repository..." and then you can put the path for fix pack 1. For example, C:\temp. Click OK after you have entered the path and click Apply on the Preferences page and OK.
- Back in the IBM Installation Manager start page, click Update Packages.
- Select IBM Data Studio (for Data Studio Administrator or Developer) or IBM Software Delivery Platform (for InfoSphere Data Architect) as the package you want to update and click Next.
- On the licensing page, read the license agreement and select "I accept the terms in the license agreement" and click Next.
- On the summary page, verify the installation information and click Update. This will begin the installation of the fix pack on your system.
- When the installation is complete you can click Finish, and close IBM Installation Manager.
For those who don't have the products installed, go ahead and download the trial versions of the products from the following links:Data Studio Developer Data Studio Administrator InfoSphere Data Architect
(By they way, you can find all these links together on the Data Studio Community Space
- Unzip the Data Studio package you just downloaded and click setup.exe. This will launch/install IBM Installation Manager.
- When prompted to select packages to install, click Check for Other Versions and Extensions. This will show you both the newest IBM Installation Manager, plus the fix pack!
Now you are ready to go!
We also have fix packs for DB2 Performance Expert and DB2 Performance Expert Insight Feature:DB2 Performance Expert Version 3.2 Fix Pack 1DB2 Performance Expert Extended Insight Feature Version 3.2 Fix Pack 1
-- Tina Chen
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:
- art of manliness
- art of trolling
- art of participation
- art of questioning
- art of living
- art of shaving
- art of flight
- art of dying
- art of seduction
- art of the steal
- art of marriage
- art of getting by
These are dubious arts, at best. If shaving is an art, then it's most likely a Dadaist one, and the masterwork would be to reverse what Marcel Duchamp did to the Mona Lisa
. I wouldn't care to hear from anyone smug enough to think he'd mastered the art of living, and I suspect that I never will hear the wisdom of any masters in the art of dying. And is it just me or do you also get the idea that the inventors of the "art of getting by" were perhaps just tired of failing to be masters of anything else and thought they might have a chance with that?
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
- Create a workload of the SQL statements that you want to tune.
- Run the Workload Statistics Advisor to generate RUNSTATS commands for ensuring that the DB2 optimizer has the most current and the most needed statistics. Then, run the commands.
- Run the Workload Statistics Advisor again to generate recommendations for creating statistical views and modifying existing statistical views. Then, run the DDL scripts and RUNSTATS commands for collecting statistics for the views.
- Run the Workload Index Advisor and follow its recommendations to ensure that the right indexes exist to help avoid unnecessary table scans. Then, run the DDL scripts to create the recommended indexes.
The idea is to make sure that the relevant statistics are current before you move on to getting recommendations from the Workload Index Advisor.General steps for tuning query workloads that run on DB2 for z/OS
(The original steps are here
- Create a workload of the SQL statements that you want to tune.
- Collect EXPLAIN information about the SQL statements that are in the query workload. The workflow assistant must run the EXPLAIN statement for each of the SQL statements that are in the query workload. The Workload Statistics Advisor requires up-to-date information about the SQL statements.
- Run the Workload Statistics Advisor to generate RUNSTATS commands for ensuring that the DB2 optimizer has the most current and the most needed statistics.
- Run the Workload Index Advisor and follow its recommendations to ensure that the right indexes exist to help avoid unnecessary table scans.
- Run the Workload Query Advisor and follow its recommendations to find and revise structures in SQL statements that are likely to cause the DB2 optimizer to choose a suboptimal access path.
- Run the Workload Access Path Advisor to discover whether the DB2 optimizer is using a suboptimal access path to process the statement. Then, follow the advisor's recommendations.
Again, the idea is to make sure that the relevant statistics are current before you move on to getting recommendations from the other advisors. 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
- Format the SQL statement to make it easier to read and comprehend. Seeing a formatted version of the SQL statement can lead to insights that are not as apparent with the unformatted version.
- Analyze the access plan graph for the SQL statement to find out how DB2 accesses data in the objects that the statement references.
- Run the Statistics Advisor and follow its recommendations to ensure that the DB2 optimizer has the most current and most important statistics.
The idea here is to examine the SQL statement first to try to understand where the current bottlenecks might be, then to update the relevant statistics. You can then regenerate an access plan graph and find out whether the bottlenecks are improved.General steps for tuning single SQL statements
(The original steps are here
- Capture the SQL statement that you want to tune.
- Format and annotate the problem statement to make it easier to read and comprehend. The annotations show relevant statistics that can help you understand what information the DB2 optimizer is using when generating the access plan.
- Follow either or both of these steps:
Generate a visual representation of the access plan to see the choices that the optimizer makes in accessing the data.
Browse the access plan for problems by using the Access Plan Explorer.
- Run the Statistics Advisor and follow its recommendations to ensure that the DB2 optimizer always has the most current and the most needed statistics.
- Run the Index Advisor and follow its recommendations to ensure that the right indexes exist to help avoid unnecessary table scans.
- Run the Query Advisor and follow its recommendations to find and revise structures in the statement that are likely to cause the DB2 optimizer to choose a suboptimal access path.
- Run the Access Path Advisor to discover whether the DB2 optimizer is using a suboptimal access path to process the statement. Then, follow the advisor's recommendations.
- If the DB2 optimizer continues to use a suboptimal access path, create, validate, and deploy a plan hint (for DB2 for z/OS) or optimization profile (DB2 for Linux, UNIX, and Windows) that gives the optimizer explicit instructions for choosing an access path.
The idea here is to examine the SQL statement first to try to understand where the current bottlenecks might be, then to update the relevant statistics. You can then regenerate an access plan graph and find out whether the bottlenecks are improved. You could also move on to the other advisors, regenerating an access plan graph at times to compare it against your benchmark. Plan hints and optimization profiles in this sequence are tools of last resort.
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: ")
Information Development for InfoSphere Optim Query Tuner and InfoSphere Optim Query Workload Tuner
It’s only been a few months since our last release, and my team and I have been busy taking our products closer to realizing the Integrated Data Management vision for integrated lifecycle development and heterogeneous database support.
So what's different in this release? With the announcement and release of Optim Development Studio 2.2
(formerly Data Studio Developer), we added:
- Features that help developers and DBAs work together to create high performing Java data access code with high productivity, on Oracle, DB2, and Informix Dynamic Server databases.
- Speedy iterative testing
- Enhanced impact analysis
- Support for building and debugging SQL/PL procedures to run against either DB2 9.7 or Oracle databases.
As the architect for pureQuery tools, I wrote a new developerWorks article
that goes into some detail on the new capabilities, mostly from a pureQuery perspective. (You can see more about the other Oracle support capabilities in Venkatesh's blog
.) I’ll give you some highlights of this release from a pureQuery perspective in hopes that it will convince you to go read the article
and download the trial code!
Some of the important features –Oracle pureQuery support
The big news of course is support for pureQuery capabilities for Oracle databases – pureQuery code generation, SQL content assist, validation and all the editing capabilities ( now also available for JDBC, and native SQL in JPA and Hibernate applications), client optimization, dependency analysis, hot spot analysis. If you aren’t familiar with these capabilities, my article does review them.
The screenshot below shows a sample pureQuery application that was run against Oracle. Using the SQL outline (formerly called pureQuery outline), you can see the performance metrics from the Oracle queries. You can also see predicted cost using the EXPLAIN Data
option. (That EXPLAIN Data
option is new for DB2 and IDS as well.) Visibility of data privacy attributes to developers
The other interesting integration work that was done is the ability to maintain data privacy attributes from modeling through development and test. Anson Kokkat touched on this in his recent blog
. Production databases often contain sensitive information such as credit card numbers or social security numbers. When data architects create data models for such databases using InfoSphere Data Architect, they can identify which attributes or columns contain sensitive information and specify appropriate privacy policies to be used with them.
By associating this model with their database, developers can easily see which columns are identified as containing sensitive information and this can help them maintain compliance in how they handle that data in their applications. This protection also extends further to their applications - they can see how those private columns are being used in context to ensure they are not doing something inappropriate, such as printing out data in those columns.
The screenshot below shows the private columns in the SQL outline (a little padlock icon is used to indicate a private column), their privacy properties, and how you can navigate to the model to get more information. Other key capabilities include:
- Ability to copy and paste objects or data subsets as an aid to developers. If you need something more heavy duty, the copy/paste wizard can generate a script that can be used with Optim Test Data Management solutions. (Note that this capability is currently available only with DB2 LUW 9.7 and Oracle.)
- For DB2, you can now specify that literals be replaced with parameter markers as part of the SQL capture process. Because this makes the statement less ‘unique’ it is now eligible to be bound statically. This was a requirement we had from several customers. Also, we’ve made a lot of enhancements in package management to give you more granularity in identifying and rebinding only packages that have been impacted by a change. In addition, you can bind in the background.
There are really a lot more enhancements, but you’ll need to read the article – I don’t have room to list everything here. We’re also working on some videos that will show these features in action.
Hi, all. I am a developer on our Optim Query Tuner products. A new feature called ‘Compare Access Plan’ was shipped in Optim Query Tuner (for DB2 for z/OS) and Query Workload Tuner V2.2 fix pack 1
that allow DB2 for z/OS DBAs to compare the access path cost of SQL statements in packages between the latest two EXPLAIN versions. The tool generates a HTML report that provides high level package cost and individual package details.
In this blog, I’ll describe how to launch the feature, and then I’ll describe some common scenarios. Launching the Access Plan Comparison
First you must be connected to a DB2 for z/OS server. You can then invoke this feature by going to Data Source Explorer->Query Tuner->Compare Access Plans
The following input screen is displayed:
Each of the options circled above is useful for different scenarios, which I’ll describe here.Option: Compare two collections that contain the same packages
Use this option in scenarios such as:
- Identifying packages whose performance can be impacted following DB2 migration from one release to another. It can help you identify packages that can benefit from REBIND after migration and packages that should not be rebound
- After running RUNSTATS to identify packages whose performance can be impacted if a REBIND is performed.
Let’s look at the first scenario in more detail - version to version migration. In DB2 for z/OS environment, after migration you have two options for running application programs that contains embedded SQL statements:
- REBIND – Rebind the existing packages to make use of new features that are introduced in the newer version. However, the rebind may alter (positively or negatively) the access plan of SQL statements in the underlying packages.
- Do nothing – Continue to operate with the packages that were bound in the earlier DB2 version. Thus there are no changes in the access plan of the SQL statements in the underlying packages. The flip side is that some of the SQL statements that could benefit from changes in the newer DB2 version would still operate in the old way.
Assume that you want to determine if you need to rebind the selected collections (group of packages) to exploit new features available in the new version of DB2 for better performance. You would need to do the following:
- Before the migration, bind any packages that are going to be analyzed with EXPLAIN(YES).
- After the migration, bind the packages to a new collection with EXPLAIN(YES) to insure that the current packages are not overlaid and are available for execution. The packages must be exactly the same in the before and after migration collections. The underlying application programs must not be changed in any way or rebuilt. Also be sure that the EXPLAIN output for the bind points to the same set of EXPLAIN tables. In either case, the access plan comparison report will show an error if you don’t do this correctly.
- From Optim Query Tuner, in the input screen shown above, you specify the sets of collections to be compared by entering the name of the first collection in the Source Collection Name field. For example, in a migration scenario, the source collection might be the name of the collection prior to the migration and the target collection might be the name of the collection following the migration. You can also use filtering to narrow down to a subset of packages.
- Click Add. See the screen-shot below for an example (you can repeat the above steps to add additional collections):
- Click Generate Report to get the comparison report. Query Tuner will look into the catalog tables and EXPLAIN output to compare the access plan of all the operative and valid SQL statements in the underlying packages pre and post migration of the selected collection(s) and then generate an HTML comparison report.
The HTML report is self-descriptive. It displays result in two sections:Summary Table shown below:
Lists out all the compared packages in decreasing order of the performance degradation. Thus the package whose performance has degraded the most is shown at the top. The package cost is determined by aggregating the cost of individual SQL statements within the package. TOTAL_COST is used as a parameter for computing performance degradation. However TOTAL_COST information was added in the explain table only in DB2 z/OS v8 new function mode. For releases that don’t have this information, the PROCMS value is used to determine the performance degradation. For easy navigation, your can click on the package name to navigate to the section where the package details are listed. Package Table shown below:
Lists out all the SQL statements within a package in the decreasing order of the performance degradation. The same parameter that was used for determining package degradation is used for computing individual SQL degradation also.
Use this report to determine whether or not the performance of the SQL statements in the target collection has degraded or whether any packages have errors. Packages that have errors are highlighted in red, indicating one of the following reasons:
- The collection name is invalid or no qualifying packages were found.
- The package content was different between the two collections.
- A package appears in only one of the collections.
- The explain entries are missing for a package in one or both of the collections.
You can save the report by clicking Save Report
. Firefox users can use the save and print feature to save the report.
What to do next
You would either rebind the package/s to the original collection to make them live, or begin analysis of those statements that have been identified as having degradation in performance and not rebind them until you have resolved any performance issues. You can use Optim Query Tuner to analyze the SQL statements that have problems and to receive specific advice about the problem queries.
Option: Compare two different versions of the same collection
Use this option for those cases when you rebind packages regularly and want to gain an understanding of the changes in the access plan before and after the rebind. As with the previous scenario, bind any packages that are going to be analyzed with EXPLAIN(YES), before the rebind. Then rebind packages to the original collection with EXPLAIN(YES) to insure that the current packages are overlaid and are available for execution. The packages pre- and post-rebinds must be exactly the same. The underlying application programs must not be rebuilt or changed in any way.
Now, in the input screen you will specify the name for the collection that contains the packages to be analyzed in the Collection Name
field as shown here:
Click Generate Report
to get the comparison report. Query Tuner will look into the catalog tables and EXPLAIN output to compare the access plan of all the operative and valid SQL statements in the underlying packages pre and post rebind of the selected collection(s) and then generate a comparison report. From the generated HTML report you can determine whether or not the later version of the collection has degraded performance.
I hope you find this new feature useful. Let us know what you think. You can also ask questions and discuss with other users on the Optim Query Tuning Solutions discussion forum
Modified on by squinter
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!
Greetings from Berlin! I’m very pleased today to tell you about several new announcements we are making in the portfolio previously known as Data Studio. With these new releases, we are taking great strides toward the vision of Integrated Data Management -- An integrated, modular environment to manage enterprise application data and optimize data-driven applications, from requirements to retirement across heterogeneous environments.
While delivering on an Integrated Data Management vision is a very broad value proposition and one that will involve all aspects of IBM Software Group, you’ll see with this release that we are adopting the Optim name as a rallying point for this technology emphasizing our focus on optimizing the value of your data assets by managing them across their lifecycle. This announcement represents another major step in delivering on our vision focusing both on extending heterogeneity and portfolio integration that provides the basis for cross-role, cross-lifecycle collaboration, efficiency, and alignment.
In future posts, we’ll take you through a couple of scenarios enabled by the new releases, but to get you started, here is a summary of the announcement and links to announcement letters and web pages:
- Enhancements to and renaming of Optim Development Studio, Optim pureQuery Runtime, and Optim Database Administrator (formerly Data Studio Developer, Data Studio pureQuery Runtime, and Data StudioAdministrator). You’ll find lots of new functionality for both developers and DBAs in these releases. Most notably:
- pureQuery and development support for Oracle databases (including PL/SQL) for a common integrated development environment across DB2, Informix, and Oracle
- A host of fantastic new pureQuery capabilities that customers have been asking for, including translation of literals to host variables to improve application performance
- More complete DB2 for Linux, UNIX, and Windows administration capabilities in Optim Database Administrator including support for large warehousing environments
- Better governance of privacy attributes across design, development, and test environments Better support for DB2 package management so that DBAs can restrict changes and rebinds to just those packages affected by a change.
Many of you may be wondering about the no-charge capabilities. We heard loud and clear that our customers want a stand-alone download package for the no-charge capability. We are reverting to that packaging model with this release. More on that as it becomes available in the near future.
- New product: Optim Query Tuner for DB2 for Linux, UNIX, and Windows brings single-query tuning advice and formatting to theDB2 developer. I think Ray Willoughby will be blogging more on this, but this product provides a great first step toward enabling developers to more effectively tune queries during development. Its integration with Optim Development Studio provides a seamless environment for crafting queries, identifying SQL hot spots, and optimizing queries all pre-production to help produce enterprise-ready code and facilitate collaboration between the developers and DBAs. See the announcement letter and Web page for more detail.
- New release of InfoSphere Data Architect. This product has always been a shining star in the portfolio for its heterogeneous database capabilities. This release includes improvements to the data governance value proposition around data privacy and the ability to maintain volumetric data for capacity planning. Most notably, users can choose from pre-defined privacy templates and share privacy definitions with developers and testers via Optim Development Studio. See the announcement letter or Web page for more detail
I’m asking some of my technical leaders and product managers to go into more detail on these announcements in future blog posts. In the meantime, I must go back to the conference. Lots to talk about…
Today's entry is inspired by a recent Dilbert cartoon
where the pointy-haired boss tells Dilbert that he needs to get better at anticipating problems. While we'd all like to see problems before they happen, we need a little help here, and inspiring words from the pointy-haired boss just doesn't cut it.
Today's DBAs have a lot of responsibility; arguably more than they have had in the past in terms of number of systems and the complexity of these systems. Most DBAs have implemented early detection mechanisms for production systems, but what about non-production or less-critical systems like development or test systems? These are often called "non-critical systems" until a severe issue occurs with them, and then they suddenly become critical because they are preventing new work from being implemented on schedule. Sometimes it may be difficult to justify the cost of robust monitoring software like DB2 Performance Expert
, Tivoli OMEGAMON for DB2
, or IBM Tivoli Monitoring
for these labeled "less-critical" systems, so what's a pro-active DBA to do?
One solution is the Data Studio Administration Console (DSAC)
. It is a no-charge offering with your data server license that supports DB2 for z/OS and DB2 for Linux, UNIX, and Windows with an "at-a-glance" view to see the health and availability of these systems. It is not a full-blown performance monitor, but it does show several key indicators like whether the system is up/down, locking rates, resource utilization, etc.In new news, although DSAC used to be the delivery vehicle for the Q Replication Dashboard, we have just made available a new and improved Q Replication Dashboard One of our Gold Consultants, Frank Fillmore, will be discussing this dashboard in a webcast (two sessions to accommodate different timezones) with IBM on September 15. Get the details from his blog.
With this change, you might be asking what other changes are in store for DSAC? You may have heard us talking about our next generation performance manager. It has a new architecture along with a web browser interface that will support DB2 and eventually other DBMSs. Once we roll out this performance manager (be sure to attend IOD
to find out more), we plan to use this new architecture for the next release of DSAC. It will still provide the same high-level health and availability capabilities that DSAC 1.2 provides today, but the Web user interface will be refreshed and have consistency with our other Web UI offerings.
So, don't let the pointy-haired boss get you down the next time they ask you to anticipate problems better -- just smile, thank them for their leadership, and go take a look at DSAC to prevent those critical situations.
I've been talking to a lot of customers about Data Studio over the past few months. Some of the more "seasoned" customers ask an interesting question -- "We've seen IBM go after this lifecycle management problem before with the AD/Cycle product. Why should we believe this attempt with Data Studio is going to be more successful?" In my view, there are several key reasons why we believe Data Studio will have a different outcome:
- AD/Cycle was conceived almost 20 years ago. At that time, hardware was the primary cost issue customers faced when planning for their IT budget, so the cost of running the application and data life cycle was not a pivotal financial issue. Today, the majority of our customers' IT budgets are associated with the people that build and manage the applications, run the data center, etc. Customers are very focused on solutions that can reduce human labor cost, allow them to run their existing servers more efficiently, and in general get more out of their current IT infrastructure. Data Studio is intended to help on all of those fronts.
- In the IBM company that exists today (unlike 20 years ago), we have a large number of software offerings that already address significant portions of the life cycle issue: the Rational family of products for application design and development activities, the Tivoli product line for system management and monitoring, and a wealth of data management tools such as the offerings from our Optim product family (formerly known as Princeton SoftTech). The missing piece in this puzzle is the solution that incorporates all the data management activities into a cohesive data and application life cycle management solution. This of course is the role Data Studio intends to play.
- There is also a major difference in the financial investment IBM is making in Data Studio. IBM believes there is a very strong need in the industry for an integrated data and application life cycle management solution. We've created a product development organization that dwarfs any past investment by IBM in this space. We've assembled a team of developers with deep expertise in all the technology areas that need to be addressed by Data Studio. We fully expect that this team will provide some very innovative solutions that allow customers to significantly reduce the cost of system management, monitoring, problem determination, etc.
- Lastly, we're delivering the technology in a way that will allow customers to adopt the solution incrementally, rather than deliver a monolithic system that requires years of planning to deploy. Customers will be able to deploy individual solutions (data archiving, test data generation, end-to-end elapsed time monitoring for WebSphere application, etc.). This will allow customers to consume the Data Studio solution at whatever pace they like, without major disruption to their existing system management process.
I hope I've addressed this common question. If you have any comments or other things you'd like to see me or someone else blog about, either add a comment to this blog or send an email to email@example.com.
-- Curt Cotner[Read More
As mentioned in my earlier blog, I work on heterogeneous data accessand I am one of the technical leads responsible for Oracle integration.Until we get more detail out on developerWorks, I want to provide youwith an overview of the support we will have for Oracle
Curt hinted in his announcement blog
about Oracle support in the upcoming release of Optim products that were formerly named Data Studio. Support for Oracle has been been expanded in Optim Development Studio (formerly Data Studio Developer) to extend to the development environment the capabilities already available in InfoSphere Data Architect
(for modeling and design) and the Optim solutions for data privacy
, data growth
, and test data management
With Optim Development Studio 2.2, support for Oracle has been added in the areas of:
- Object Management - You can explore into your Oracle database using the Data Source Explorer and view/create/alter/drop tables, views, materialized views, sequences, synonyms, indexes and user-defined types. And as usual, for any of the edits that you make it generates appropriate DDL to be deployed to the Oracle backend. Furthermore, using the Data Source Explorer, you can view contents of your existing stored procedures, functions and PL/SQL packages.
- PL/SQL life cycle management - Start by creating a new PL/SQL package in a Data Development project (specification and body), add contents to it and then deploy the package to the server (with debug enabled), debug a PL/SQL package stored procedure or function. The debug works like the standard Eclipse Java debugger: you can set breakpoints where you want, and step through and look at changes to variables defined in your stored procedure or function. You can also copy a package from the Data Source Explorer to a Data Development project and edit the package / deploy it back or debug the package entities from there. The same use case scenario extends to PL/SQL based stored procedures and functions as well.
- Data Management - Look at/Export/Import/Edit the contents for a table or view. Copy schema objects from one Oracle schema to the other (enforce data privacy rules on the copy over if required - this applies to copying over a table with data from one schema to another). You can also use the new Copy functionality to keep your DB2 implementation current with your Oracle implementation by copying the impacted database objects between the two database servers. This can only be done with the new DB2 Linux, UNIX, Windows 9.7 release with the DB2 database in the Oracle compatibility mode.
- Visual Explain - View the Visual Explain of a particular SQL statement by right clicking on the SQL statement and then clicking on Open Visual Explain. This action can be performed from the SQL Editor, from the pureQuery-enabled Java editor and the SQL outline view. This shows an explain graph for the statement with details on the different nodes/operators (type of operator, cost / cardinality etc). Also, right clicking on a particular node in the graph will show additional details (for a table operator, you get to see the columns, indexes with related data).
pureQuery support has been added for Oracle with this release as well. You should be able to use the pureQuery scenarios as listed in this article
except for the dynamic-to-static and the JPA ones. There are several enhancements to pureQuery-based development that have been introduced with this release. Sonali Surange will be publishing an article on this soon.
As Curt mentions in his blog, Oracle support in the product(s) adds to the heterogeneity of the product set and provides people who need to develop cross-database applications a single platform for developing applications with a consistent look and feel. And this development environment is part of a larger integrated data management suite that provides heterogeneous data lifecycle capabilities from design through application retirement.
Keep an eye on the Integrated Data Management Community Space
, which should have a link to the updated trial download before long. The announcement letter
also indicates the Oracle versions and drivers supported. Please try the product out and give us your valued feedback.
-- Venkatesh Gopal
Is "agile data" just another buzzphrase? Does it even make sense to try to apply agile development principles to the database?
An expert in agile development, Scott Ambler
, sees agile data as an essential component for application development that goes against a database. You can learn more about agile data here: http://www.agiledata.org/
I think one of the classic challenges that agile data faces is about dealing with a "brittle" database. What do I mean by brittle? Basically, I am talking about how difficult and time consuming it can be to refactor the database schema to improve software. Check out the results of this survey question: "How long does it take to safely rename a column in a production database?"
Source of this survey:
Source: Data Quality Techniques
survey by Ambysoft, September 2006.
The database and/or your software development techniques around the database are "brittle" if it takes longer than one week to make a simple rename change. Almost half of these respondents fell into that category. I would venture to say that more interesting refactoring would therefore take most shops much longer than a week.
Another part of the agile data challenge is about being able to quickly tell what the impact of a change is going to be. If we want to rename a column, what are all the database objects (tables spaces, views, stored procedures, etc ...) that will be impacted, and is there a tool to help me automate a script to make these changes?
If this sounds interesting to you and you want to learn more about agile data and how Data Studio can help, come listen to a replay (until May 09) of a webcast
I did last week on how Data Studio can help make data more agile.
If you listen to the replay or are exploring agile data I am very curious to get your feedback. Just call me an agile guy. What do you think of applying agile techniques to the database? Are you doing it? If so, what is your experience? What tools are you using? What tools do you need?
What do you think?
-- Rafael Coss