Let me start by introducing myself. My name is Manas Dadarkar and I am the technical lead for Data Studio (the complimentary tooling for DB2 and IDS) and for High Performance Unload (HPU) for DB2 for Linux, UNIX, and Windows. I took on this role recently and am right in the middle of planning a lot of exciting things for Data Studio and HPU.
However, I will leave Data Studio for another day (blog posting), so today let’s talk about HPU, including the announcement today of the new release, 4.1.2, under the new name, Optim High Performance Unload for DB2 for Linux, UNIX and Windows.
For people not familiar with HPU, it's a product that offers high speed unload of DB2 data, thereby allowing DBAs to work with very large quantities of data easily and efficiently. In limited lab testing using a single large table, we have seen multiple magnitudes (6-10 times) performance improvement using HPU as compared to the DB2 EXPORT utility. The usual disclaimers apply: Your results will vary.
Since version 4.1, HPU has also supported automatic migrations. You can now migrate data directly from one database to another, including unloading, transferring, and loading of the data between the source database and target database without the need for intermediate storage. You can get more information about HPU here. It's also featured in our Day in the Life of a DBA video demo.
With Version 4.1.2, what's new is being able to unload or restore using incremental backup images. If you use incremental backups, it’s likely that those backups will provide you with a more current version of a dropped table. Check out the full announcement here.
For those of you who have moved to DB2 LUW 9.7 (or will be soon), you don’t need to wait for the 4.1.2 release, which is planned to be electronically available on November 20. We’ll shortly be releasing the support for 9.7, including the ability to work with DB2’s industry leading XML data support. I’ll post a short blog with the link once that is available and we’ll also put the word out on Twitter.
Also, if you are going to IOD2009, you can hear directly from a customer about HPU by attending session 1499 A day in the life of a DBA: How to keep your sanity.
Since all the action is in Las Vegas this week, the halls are quiet (or so I hear, since I am working from home today) and meetings are cancelled. This gives me time to provide you all with a little commercial for a little spot on the "internets" called the Integrated Data Management community space
. This space started out as the Data Studio community space by Grant Hutchinson
. When I joined the team, I took over management of the space including the move to IDM to be more inclusive of the whole Optim portfolio. If you've never worked with developerWorks spaces
, they are kind of cool. Anyone (internal or external) who gets approval can self-publish their own web space using the templates provided by developerWorks.
Anyway, I feel the IDM space needs a commercial because so many people still don't know about it (even people on my own team!), and I must send out the link several times a day. The intended purpose of the space is to try and put in one place links to all the blogs, discussion forums, articles, product documentation, classes, downloads, videos, demos, upcoming events, etc related (mostly) to the Optim portfolio.
Admittedly, the site isn't perfect as I am limited by the dw templates, and I still have work to do to get more of the heritage Optim materials integrated. But most people who discover it do think it's very useful. There is even a place to post messages, but that has not been used thus far. I think using our existing discussion forums is probably the best way to get questions answered anyway. Not sure where the forums are? Well, the links to all the forums are on the space!
As of today, the space is divided in to three tabs: Home, Articles and tutorials, and Downloads. Here is the Home tab:
As you can see in the screenshot above, you can add any of the portlets to your myYahoo or igoogle page (or as an RSS feed) by clicking on the orange plus icon. The screenshot below shows my Yahoo! page with the 'latest news' and forums portlet added.
Here is the downloads tab. It also includes some additional related software such as DB2 Express-C and the Replication Dashboard.
Anyway, I hope you like the space. Let me know if there's anything you want to see added, changed, or removed. And spread the word, please?
IOD was a busy time for the Optim team. I hosted three sessions and also was in the pedestal area and had a chance to interact with several DB2 and IDS customers. While all the solutions certainly got their attention, two solutions seemed to bubble up to the top in terms of customer discussion and questions. Optim pureQuery
was one of them, mainly from the DB2 for z/OS crowd. There is definitely a trend in the market of customers wanting to take advany tage of the wealth of data stored on the DB2 for z/OS platform rather than transport that data to another platform. This trend indicates that JAVA is the preferred language when developing these new applications; however, finding an acceptable data access layer both in terms of ease of use as well as high performance, has been a challenge. Many companies are looking at things like Hibernate to address their ease of use challenges, but unfortunately Hibernate doesn't address their high performance requirement. Several customers wanted to understand how pureQuery could help them in this situation and were very excited once we talked to them about not only the benefits of Static SQL
, but also the client optimization
features of pureQuery, and most importantly how we could visualize the SQL that Hibernate was generating to the developer and DBA. Seems to be a perfect fit for providing the high performance that DB2 z/OS customers expect for these new JAVA applications.
The second solution that seemed to get a lot of attention was our Performance Expert
solution. When it comes to problem diagnosis everyone is always looking for a better mousetrap and it seems that solutions in this area get hot and then run their course. Well, Performance Expert seems to be the up and coming hot product. I believe this is due to the sheer wealth of information this product collects. If , or should I say when, a problem occurs in production the most difficult challenge is gathering that information, especially because that moment in time is now past and you really need historical data to determine what went wrong. Performance Expert collects information and stores this information in intervals, making it incredibly easy to get the information needed all synchronized to the right time. You can see a short video of this capability here
. The other thing I found interesting was all the various reports and consoles customers were writing based on the data found in the Performance Warehouse. From capacity planning to SLA reporting, it seems to me that there is a lot of customization going on that would probably make a great birds-of-a-feather session in future conferences.
As I mentioned earlier, all the solutions got their attention. I hosted a joint session with Randy Wilson from Blue Cross Blue Shield of Tennessee where Randy described a very interesting outage situation (not fun!) and how Optim High Performance Unload
saved the day for them in terms of being able to supplement their recovery and provide the historical data they needed. Interesting how customers really think outside the box and come up with creative uses for our solution. After that session I had quite a few discussions with customers on how they could use High Performance Unload for situations like Randy's where back-level DB2 or dropped tables caused challenges in terms of recovery.
Well it was a busy week and now comes all the customer follow-up that happens after IOD!
We recently returned from IOD, which was quite an intense experience for those of you who have never been. We both gave lots of demos and talked to lots of customers about the Optim Query tuning solutions. And you can imagine that any session on query tuning, with or without a tools focus, was really packed. It seems as if people can never hear enough about query tuning, because it’s actually pretty interesting to do, and because it can have such an impact to the day to day life of a DBA (or whoever in your organization is tasked with reviewing and turning queries and query workloads). Ray blogged earlier
about how important it is that developers and DBAs collaborate more in the query tuning process. Not only can developers build up their skills, they can hopefully come to the DBA with some of the basic stuff taken care of, or at least a better understanding of what the issues are. The earlier in the cycle that issues are discovered, the less expensive and labor-intensive the tuning process is.
Anyway, we wanted to share with everyone who could not make the conference some scenario-based demonstrations of how query tuning solutions can work together. We are co-presenting at an upcoming Virtual Tech briefing (complimentary!) on November 19th.
The focus of this is on z/OS, so we’ll talk about query tuning from both a development and DBA perspective, and discuss how to use Optim (such as Optim Development Studio
, Optim Query Tuner
and Optim Query Workload Tuner
) and other z/OS tools to work through some typical scenarios. The scenarios we are planning to cover include:
- A development time scenario in which query tuning capabilities and reporting are available directly from the development environment.
- Invoking query tuning capabilities from a monitoring environment such as OMEGAMON and Query Monitor
- How you can use Query Workload Tuning in a version to version migration scenario
- Workload analysis capabilities and an index advisor walkthrough.
We hope you can join us. Register today
Ray and Saghi
Hey, DB2 for Linux, UNIX, and Windows DBAs! We’ve enhanced the Optim Database Management Professional Edition
with new editions of:
If you missed the summer announcement, this solution combines database administration, change management, performance monitoring, and high-speed unload capabilities into a conveniently packaged and attractively priced solution. Manas blogged
about the new release of Optim High Performance Unload 4.1.2, which includes extended offline capabilities to help you reduce the risk of business disruption. Check out the full announcement here
Optim Database Administrator V2.2.2 helps prevent errors and data loss when upgrading databases to support new applications. It helps you:
- Automate and script structural database changes
- Perform extended alters that require dropping and re-creating tables and managing data preservation
- Perform schema compare and synchronization with custom mapping features
- Migrate database objects, data, and privileges and generate maintenance utility commands
- Manage database objects, privileges, and utilities with an embedded components of Data Studio software
New and enhanced capabilities in Version 2.2.2 improve overall administrative tasks by supporting federated objects, enable off-peak task scheduling providing the ability to produce scripts that can run with the DB2 command line processor, add basic pureScale support, and improve analysis performance for large DB2 environments (think 1000s of tables). The announcement letter is here
In one of my last blogs, I wrote about the untold story of data privacy focusing on non-production systems. This past week, IBM made a significant acquisition of Guardium to improve support for compliance and the protection of privacy across all systems. As discussed in the previous blog, we often forget about the systems not in production and think that the current security in place is enough yet that is not the case.
The addition of Guardium to the mission of privacy, protection and compliance continues IBM’s mission in helping organizations at the enterprise level. Just like with data masking, often organizations don’t think of monitoring use, access and change to archives, but it is yet a risk. The combination of Guardium and Optim will provide that capability to monitor the SQL accesses and usage of data not only in production data sources, but also those in archives, development, test and training environments.
With Guardium comes an enterprise solution supporting most popular databases and application frameworks. Like the Princeton Softech and Ascential acquisitions of years ago, IBM plans to continue support for databases beyond those that are “Blue” and with the proof that they will based on now 4 years post Ascential and 2 years past Princeton Softech, I believe they will keep that promise. Most enterprises contain multiple technologies and enterprise solutions require support across them. Guardium continues IBM software’s vision of meeting the enterprises needs.
I wanted to let you know that today we published a new e-book called Getting Started with Data Studio (for DB2).
It's part of the DB2 on Campus
effort, but we wrote it with the idea that anyone (not just college students) learning DB2 and Data Studio
could use this free book to get comfortable with the user interface and for performing everyday database administration tasks and for routine and Data Web Services development. You can use this with any edition of DB2, including Express-C
, which you can also download for free. And although the book was written with DB2 for Linux, UNIX, and Windows in mind, those of you who use DB2 for z/OS can use this as well since there is significant overlap in capability.
Anyway, I hope you like the book. And, more importantly, I hope it encourages more people to get familiar with and try out the broader range of offerings for integrated data management. Several of these other offerings are also available for download on a limited trial basis. You can find links to all the Optim trials (and to Data Studio) on the Integrated Data Management community space
I was at the Lab in San Jose this week doing some planning and meeting with Business Partners. The folks here say the cold is pretty unreasonable, but I’m heading back to the upper Midwest, so I don’t think they have much room to complain.
Anyway, today we are shipping a set of Fix Packs and refreshes to our portfolio of database admin and tuning products. Some individual product architects will be blogging in more detail about what is in the Fix Packs, but I wanted to give you a head start on downloading by giving you a summary list of all the download documents that tell you how to find what you need. Reminder:
two product releases that Holly Hayes blogged
about in November also are electronically available today. Links to their download documents are here:
Have a great holiday.
Updated December 15 to correct a typo.
I am the software architect for InfoSphere Data Architect
, and I wanted to spend a few minutes telling you what we’ve been cooking at the Lab over the past few months to deliver Fix Pack 1
for IDA 7.5.2, which was made available on December 11.
In this Fix Pack, we’ve added new features and improvement in a number of key areas, which I’ll highlight here. Diagramming improvements:
We are excited to have started incorporating the ILOG diagramming technology into InfoSphere Data Architect to provide an enhanced diagram layout. The new diagramming capability will offer choices of layouts as well the option to specify the spacing of objects, all very important steps towards offering greater control and flexibility of visualization.Import DB2 physical objects from other tools:
For years, InfoSphere Data Architect has offered the capability to import models from other tools. Significant in Fix Pack 1, we have provided unique capability to help import DB2-specific properties for physical database objects, such as index and storage, faithfully into IDA from other tools like CA ERwin and CA Gen. Whereas generic export/import capabilities may cause you to lose this information, this enhancement in IDA will enable you to preserve your existing data design efforts. Import from COBOL source files and copybooks:
Although this capability was on a temporary leave, our z/OS friends will be happy to know that this capability is back in with this Fix Pack. This "legacy data" often contains critical information, so they need to be included in the data modeling process. Filtering improvements for productivity and performance:
It is now possible to have a couple different approaches of specifying filters for model comparison and synchronization: you can specify filter options at the workspace level to streamline and improve overall comparison performance, and you can specify filter options at individual comparison invocation to improve ease of use of the comparison editor. Integration with InfoSphere Discovery (forrmerly Exeros):
Those familiar with our history and approach know that we have a strong focus in building linkage points across IBM products. This ability to easily share and collaborate the metadata is crucial to the acceleration of projects. In IDA 184.108.40.206, we continue this focus with the introduction of the integration with InfoSphere Discovery
. With this new capability, you can import the discovered metadata from InfoSphere Discovery directly into InfoSphere Data Architect and share and use it in a wide range of scenarios including the Optim Data Archiving
and Data Privacy
solutions, as well as InfoSphere Foundation Tools
For more details on the contents of this Fix Pack, be sure to read the Release Notes
Let me close by saying that the InfoSphere Data Architect team loves getting your input so please keep them coming. Feel free to post your comments and questions on the IDA forum
. We are excited about what we are going to do in 2010!
With the holidays just around the corner, this is the right time to add to the spirit by announcing the new release of Data Studio
220.127.116.11 (the complimentary tooling for DB2 and IDS). Along with improved quality, following are the two significant functionalities we added:
- Administration of DB2 pureScale: If you aren't already aware, DB2's new version provides continuous availability through the use of highly reliable IBM PowerHA technology on IBM Power systems and a cluster- based shared disk architecture. DB2 pureScale provides practically unlimited capacity for any transactional workload. Thanks to a proven, scalable architecture, you can grow your application to meet the most demanding business requirements. Using Data Studio, you can do the following administrative tasks on DB2 pureScale
- Cluster Members: Start, Stop, Configure, Quiesce, Unquiesce
- PowerHA pureScale Server (CF): Start, Stop, Configure
- Basic single query tuning features: The following features have been incorporated from the deprecated Optimization Service Center for DB2 for z/OS:
- Capture queries from all data sources that Optimization Service Center for DB2 for z/OS supports and from XML files that are exported from DB2 Query Monitor for z/OS.
- View formatted queries.
- View access plan graphs.
- Capture information about the data server that queries run against, a feature which corresponds to Service SQL in Optimization Service Center for DB2 for z/OS
- Generate reports on the performance of queries.
- Run the Query Statistics Advisor to analyze the statistics that are available for the data that a query accesses, check for inaccurate, outdated, or conflicting statistics, and look for additional statistics that you might capture to improve how the data server processes the query.
Note that even if the above features have a legacy in the Optimization Service Center for DB2 for z/OS, you also get the same functionality for DB2 for Linux, UNIX, and Windows in Data Studio. For integration with Optim Development Studio
or for more advisors, such as Query Advisor, Access Path Advisor and Index Advisor, you need Optim Query Tuner
The above new features are only available from the stand-alone package of Data Studio, which is being refreshed. Hereis a link to the download document
that includes links to both the Data Studio stand-alone refresh and IDE Fix Pack.
Or, if you want to go ahead and download Data Studio stand-alone right now, here is a direct link
. And don’t forget the new free e-book, Getting Started with Data Studio for DB2, which you can download here
We have a lot of exciting things planned for 2010. So stay tuned and happy holidays!!
The Optim Development Studio 2.2 Fix Pack 1
was made available on December 11. Although a fix pack, the focus is on customer requirements, so there are many "new" features focused on pureQuery development. These features are suggestions from our customers, based on their extensive usage of pureQuery on large scale projects, to improve developer productivity. We hope you will benefit from these while building pureQuery applications. To see what pureQuery brings to your organization, read my earlier article
Here are some of the highlights of the fix pack.Speedy pureQuery application iterative testing
- As part of pureQuery development, you are always changing your SQL in the annotated style interfaces. You now have the ability to generate JUnits for those interfaces and speed up testing. Simply right click on the annotated interface from the package explorer and select Generate JUnit
from the menu. Improved SQL support in Java database applications
Improved developer and DBA handshake
- Bridging the gap between Java and SQL has been one of the key benefits of pureQuery since the first release. This feature has been improved to allow live validation of SQL against the database, if you have a live connection available. This is a sure shot way to ensure SQL is accurately validated, and you may want to always set this flag if you have a live database connection.
You can set this flag (called “Validate SQL problems against live database”) from Window->Preferences->pureQuery, or from the project properties.
- SQL merge statements can now be auto-generated when pureQuery code is generated from one or more tables and at least one primary key exists on the table(s). Simply check the boxes for SQL merge statements in the pureQuery code generation wizard (by default, they are checked).
We are providing yet another step towards seamless integration between the developer and DBA roles. When using Optim Development Studio to bind SQL into static SQL packages, you can now generate .bat or .sh files which can be shared with the DBA. The DBA can use that as a starting point to customize the bind calls, or use them as is.Improved usability for tuning queries in ODS using Optim Query Tuner
You can generate reports that suggest how to tune SQL statements for better performance, when Optim Query Tuner
is installed in a shell shared environment with Optim Development Studio. Simply right click on an SQL statement from your pureQuery application or from the SQL Outline, and select Generate Query Tuner report
from the pureQuery menu.Improved support for application development when using the pureQuery API
Applications often need to access SQL from multiple schemas within the same project. This is now possible when building pureQuery annotated style applications. You can specify different schemas (to quality unqualified tables/ views) or paths (to qualify unqualified procedures/ functions) for each annotated interface in a pureQuery application, and these values will be used for SQL development/editing within your interfaces. To enable this, right click on the annotated style interface and provide values using the Connection Settings.Improved support to use existing beans when using pureQuery API
If you use customized row handlers in your pureQuery applications, you can now get a head start in building these by customizing the ones generated by pureQuery. Using this new feature, you can externalize row handlers from an interface into separate files, and enable re-use. To see this feature in action, right click on the annotated style interface from the Package Explorer and select Create Handler Classes
from the pureQuery menu.
In addition to the above Optim Development Studio features, there are numerous enhancements in the Optim pureQuery Runtime Fix Pack
- Usability improvements in the static binder that allow you to specify package level bind options and allows more control over packages to bind from various artifacts
- Improved exception handling to get access to underlying sqlstate and sqlcode easily
- There are also improvements in serviceability and literal substitution features
We hope you will download the fix packs and try some of these enhancements.
As always, let us know what features you would like to see and please keep the feedback coming! Feel free to submit questions using the Optim Development Studio and pureQuery Runtime forum
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
I'm on the plane today returning from two customer visits in the D.C. area where I was talking about the advantages of pureQuery in DB2 for z/OS environments for DBAs (as opposed to applications developers). One uses WAS on z/OS and the other uses WAS on Linux for System z. Both customers were very familiar with how to manage and monitor CICS applications accessing DB2 data, and they felt out of control with Java application access to their mainframe. One customer even said, "we tried to tell our application teams to stop using Hibernate" because they couldn't see/review the SQL or know which application was running the dynamic SQL in their system. They said the application developers ignored them(!). I told them (hopefully, I wasn't too preachy) that they needed to stop resisting these contemporary application architectures (Java, .NET) and instead take back control by transforming those dynamic SQL statements to static SQL statements via pureQuery. I told them that if they continue the "no Java will access MY mainframe", that they are headed down a path of getting rid of their mainframe. The application development teams don't care what DBMS is used for their application, and if met with resistance from the z/OS team, they will simply deploy the applications on some other DBMS.
Related to this, I just watched this new short 7- minute video on pureQuery for DB2 for z/OS, and it's a great quick summary of the value that can be gotten with pureQuery. In my mind, it's a no-brainer to implement pureQuery when you have Java or .NET applications accessing DB2 for z/OS.
If you want to get a good idea of what the process is like in more detail, check out the upcoming virtual tech briefing on this very topic. It's entitled pureQuery Deep Dive Part 1: Client optimization, scheduled for February 4th. Patrick Titzler, one of the authors of the original tutorial on this topic, and Chris Farrar, client optimization technical lead, will be presenting. It should be good.
Our team blog coordinator has been getting on me about posting to kick off the new year and to reflect a bit on how things went last year. So let me try to do that as concisely as possible.
Reflecting on 2009, I have to say that I am pleased to see that people are really starting to understand the business value of integrated data management (IDM) to help reduce inefficiencies in communications and to enable enforcement of rules across IT roles. And there has been even stronger interest, particularly from the z/OS community, around potential cost reductions with use of pureQuery in the application infrastructure. I know that our tech sales and enablement teams have been very busy with pureQuery savings assessments (briefly introduced in this video demo) and are constantly on the road talking to customers about pureQuery.
We’ve had some good customer successes this year that reflect the realization of the benefits of integrated data management. Here are a couple of note:
Collaboration, innovation, and data governance with data modeling at La Caixa bank
La Caixa was challenged with control over an increasing number of database entities. They wanted to set up a way to enable administrative control over created entities that would help both their administrators and developers. They decided to go with InfoSphere Data Architect, in no small part because of its integration with Rational Software Development Platform and the rest of the Optim portfolio, as well as its extensibility. They are taking advantage of the support for source control to store physical data models in a central repository and allowing only approved objects to be promoted to production. And they’ve done some customization on the DDL files to enable the SCM to parse the files in order to decide where to install the other database objects. Data governance is improved, and the integration with Rational enables the administrators to grant access to data models to appropriate developers for improved collaboration.
Greater security and performance for openJPA applications using pureQuery at Payment Business Services (PBS)
PBS has developed a new Payment Services system programmed in Java using openJPA. The developers liked working with openJPA, but the database administrators were concerned about performance and security with dynamic SQL. So they decided to take advantage of the client optimization capabilities in pureQuery to leverage static SQL for that application. They have Optim pureQuery Runtime for z/OS under a WebSphere Application Server to capture the dynamic SQ,L and then they bind that captured SQL into DB2 packages for static execution. They use Optim Development Studio to view and work with the captured SQL for tuning, problem determination, and for communicating with developers. This marriage of pureQuery with new application infrastructures in the z/OS environment is really critical to helping enterprises to both grow technologically while maintaining appropriate levels of security and performance.
What’s coming in 2010?
Looking ahead, I’m driving the team toward delivering on specific aspects of the IDM vision. It is my belief that this year we will be able to deliver on even more integration among the portfolio members (and other IBM products) to fully realize a database performance management scenario that crosses roles from system operator all the way through to modifying and tuning the database access layer. You will likely see more integration with Cognos, SAP, and DataStage. And we’ll be delivering betas and new products to help DBAs do their jobs better. And, yes, there is a good deal of work being done to support z/OS this year. And, before you ask, yes, Windows 7 support is coming soon.
I hope to see you at our next big IOD EMEA event in Rome where I will go into more detail on some of the capabilities I can only hint at here.
I’m the performance architect for, among other things, the pureQuery platform. My team has the responsibility for not just ensuring that our products perform well, but also to help produce verifiable performance numbers that we can share with confidence.
I’m happy to say that we are ready to share our performance numbers for pureQuery access to DB2 for Linux, UNIX and Windows. (We already have some great numbers published for z/OS for both Java and .NET).
The goal of this particular performance test was to measure throughput improvement using static SQL execution, which is possible to do even for existing JDBC applications with no change to the application source code. The increased throughput comes mainly as a result of saving the cost of preparing the SQL when using static vs dynamic SQL. We typically don’t see the same level of interest in static execution from DB2 for LUW customers as we do from DB2 for z/OS customers because the LUW platform does not have the same memory constraints as z/OS – and therefore LUW customers might be more likely to throw hardware at the problem to achieve greater dynamic cache hit ratios and hence improve throughput.
However, static SQL also provides predictable performance because the access plan is pre-determined and I often find users are happier with predictable response times rather than ultra-fast response which can deteriorate over time.
Static SQL execution also provides much more than predictable performance. By using it, you can significantly improve problem determination and traceability. You can also reduce the risk of static SQL injection from dynamically executing applications. You can read about some of those benefits in this article. And there are additional benefits to pureQuery usage such as literal consolidation or the ability to make emergency fixes to application SQL without changing the application, which you can read about in Sonali’s article on 2.2 features.
OK, now that I’ve hopefully convinced you that there are many, many reasons to consider pureQuery and static SQL execution for DB2 LUW environments, I would like to go ahead and share our performance results.
The measurement environment
Our measurements were done with a typical 3-tier environment of a client, application server, and database server as shown here.
A word about the “ERWW” application we use. ERWW is an OLTP application based on an order entry and tracking system that is designed to exercise the database tier much more than the application tier (that is to say, there is not a lot of business logic in the application). The ERWW workload models a wholesale supplier managing orders, and consists of seven transaction types. The frequency of transactions is set to simulate a realistic scenario; the mix used in the benchmark environment was 47 percent update transactions, 53 percent read-only transactions. The workload is triggered by a Java client program which generates HTTP requests for the required transaction mix.
Before I go into our results, I have to offer up the standard disclaimer that any of you who are familiar with performance work are used to hearing. The tests that we ran were done in a controlled environment where we were able to carefully control extenuating factors that can influence the results. In particular, the type of application you run can significantly affect the results in terms of the mix of database-intensive work versus application-intensive work. The ERWW workload is a very database intensive workload and most of the work is done by the database server processing SQL requests. Therefore, by using pureQuery to optimize the database server side processing, we are in fact optimizing a large chunk of the workload. Consequently the performance gains for this workload are significant. We chose ERWW because it was readily available to us, and not because we thought it would give us the best results. I guess what I am trying to say is that your results will vary.
OK, now that that’s out of the way. We measured static execution using both client optimization of an existing JDBC application and also as a ‘new’ version of the application written in pureQuery annotated method style. The performance is reported in Normalized Throughput Rate - Transactions Per Second (ITR). The ITR is the notional throughput rate assuming that the CPUs are 100 percent busy. For example, consider an application with a transaction rate of 200 transactions per second at 75 percent CPU consumption. The ITR for this application would be 200 * 100/75 = 267 tps. This is the notional transaction rate that could be achieved if the CPUs were 100 percent busy, and no other bottleneck is hit first.
We measured the JDBC workload with both 90% and 95% package cache hit ratios. To achieve a 90% package cache hit ratio with the ERWW workload, the DB2 Package Cache (PCKCACHESZ) was sized to 180 x 4k pages, and for a 95% hit ratio it was sized to 210 x 4k pages.
Here are the results with a 90% cache hit ratio. The results are shown on the vertical axis as the database ITR improvements over the baseline of JDBC.
As you can see, client optimization almost doubled throughput over the existing JDBC application. The new application that uses pureQuery method style API more than doubled the database transaction throughput.
The results with a 95% cache hit ratio are shown here.
Note that we achieved significant throughput improvements even with a high package cache hit ratio.
In summary, pureQuery and static execution can offer many benefits, one of which may be improving the performance of your data servers with your applications. By changing the dynamic SQL to static SQL, pureQuery should help you either achieve better throughput on your existing hardware, or reduce CPU consumption of your existing hardware, allowing you to load more tasks onto it. I highly recommend that you also check out the bigger picture around Java acceleration (including the other benefits I mention) as shown in this video.
I wanted to point you to a new video series on pureQuery development, specifically developing a new application using pureQuery annotated methods. If you are new to pureQuery and would like a quick overview of the development experience and productivity benefits using Optim Development Studio, then these videos are a must see. When using DB2, the videos will show you in action, the potential for improving SQL performance in your application by using static SQL. The series is based on a tutorial in the Integrated Data Management information center, so you have that option for learning as well.
If you are a seasoned pureQuery architect or developer and want to introduce the capabilities to your co-workers or friends, this video series will come to your aid.
Plus, it’s divided into three relatively short modules, so you can view it if you have few free minutes, say, at lunch time! So, keep it handy and spread the word!
Hope you have the upcoming Virtual Tech Briefing on pureQuery by Information Champion Dan Galvin on your calendars. We had a meeting to discuss this today, and hearing about pureQuery (a topic I spend a fair amount of time on) from someone who is not an IBMer is refreshing, and I hope you feel the same. I think it’s important for people to hear a perspective from someone who is both really knowledgeable on the topic, and has real-world implementation expertise as well.
If you are an architect, developer, or an interested DBA, you will want to hear Dan’s perspective as he has lots of experience in building enterprise applications. Even as we discussed the topics and flow of the briefing, we got into some interesting discussions about Java data access patterns and data mapping approaches.
The nice thing about pureQuery is that you don’t necessarily have to get into battles over the benefits of one persistence approach over another. pureQuery acts as a complementary technology that can help developers and their DBAs with problem resolution and database performance with any JDBC based data access (like Hibernate, JPA , iBatis etc.). But it’s also nice to have the pureQuery API as a choice for people who really are interested in utilizing a light-weight, and more database-aware API that can leverage database best practices and has tooling slick tooling in Optim Development Studio for rapid generation of the data access layer.
Anyway, I hope you can join me, Dan, and Sonali (who will demo some cool tooling features) on March 11th at 10 AM Pacific. Please register, and even if you cannot attend that day, you can at least access the replay.
A quick note to let you know that I recently updated my article on packaging for the Optim solutions for database development, administration, and performance management (sometimes known as ‘heritage Data Studio’). The major changes were to include functional changes introduced with the Fix Packs that I blogged about earlier, to include the bundled solutions we offer, and to update links to announcement letters, relevant blog entries and more.
The intention of this article is to answer typical questions I get about feature/functionality, including supported databases, across the portfolio. I hope this helps.
Well, I'm glad I got your attention... One of the things that I've been pondering lately is what percentage of DBAs manage more than one database platform. Since this is an IBM blog, how many DBAs manage both DB2 for Linux, UNIX, and Windows and DB2 for z/OS? I've heard that they exist, but maybe I haven't been asking, and y'all haven't been telling. Does your shop have any of these elusive creatures? If so, what platforms do they support? If not, why not? Does this model work well for you? Why or why not? Is this something that you'd like to see happen?
One of our objectives in creating heterogeneous database tooling is to help reduce the learning curve when using another database platform. While this is still a work in progress for parts of our portfolio, I'd sure like to know how important this is to DBAs.
So, please tell... Post your answer here, or on the IBM Optim Facebook fan page (click on the Fans tab if you don’t see it), or, if you’re shy, you can just send me an email at bfsmith at us.ibm.com.
Technorati Tags: smith
Technorati Tags: ibm
Earlier this week, I was invited to attend and present at the Inaugural Iowa DB2 Users Group Semi-Annual Event in Des Moines. It was a very well-organized event and had great attendance. During one of the breaks, I had someone tell me, "I understand the vision of heterogeneous database tooling, but please don't divert your attention away from further work on the tooling in support of DB2, in exchange for, say Oracle." It was valid concern since we spend a generous amount of time in our presentations talking about support for Oracle, SQL Server, Sybase... on the horizon. I tried to reassure her that we are still very focused on DB2, and that much of our support extending to other database platforms is done when we can leverage things like common SQL interfaces to the other platforms. I referred them to Curt Cotner's slide (included below – I also added Guardium for completeness) that he showed during the kickoff presentation as further evidence of this. I think she was convinced, or at least felt that I understood her concern.
Technorati Tags: optim