(Updated on 10/08/2008 to fix typos)
You’ve heard a lot about Data Studio’s integration and shell sharing, but might still be wondering exactly what that means in Data Studio Administrator. As my examples show, Data Studio Administrator integrates with both the design and development phases of the data management lifecycle to provide seamless deployment and change management of:
Both Rational Data Architect and Data Studio Development have sophisticated object management tuned for development environments. However, once changes move beyond design and development environments, it’s time to start thinking in terms of change management. In test and production environments you’ll want to assure data is preserved during extended alters (see my previous blog for more about extended alters) and that dependent objects are left in the proper states after the change.
Rational Data Architect is a comprehensive modeling tool that can be used to integrate and model information changes from a variety of different sources. You can promote object changes from a physical data model created in Rational Data Architect into a Data Studio Administrator deployment script. Then the deployment script can be used to manage dependencies, side effects, and impacts of that change. There’s an excellent developerWorks article article that describes this in detail.
Data Studio Developer provides a complete environment for developing and debugging stored procedures, user-defined functions, and database applications. Changes to application code often precipitate changes to the database schema. The Data Object Editor in Data Studio Developer is optimized for iterative development; that is for quickly making one change at a time. When it comes to promoting changes, Data Studio Administrator makes it easy to aggregate and move multiple changes. Once development is done and you’re ready to move on to test, these changes can be imported into Data Studio Administrator directly from the development database, or from scripts.
The two integration points mentioned above do not require shell sharing. If you only have Data Studio Administrator installed, the data architect could email you the model or you could get the Data Design Project directly out of a source code control system etc. You do not need another product installed on your system to migrate changes from a physical data model. Likewise, you don’t need another product installed to aggregate and promote changes initially created by a Data Studio product.
However, if your job description includes data architecture, database application development and/or database administration, you have the option to install these products as a single application i.e. in the same Eclipse shell. This means a smaller memory footprint, one set of connections, the ability to use one workspace, one set of preferences, and more Data Studio features at your finger tips e.g. modeling, diagramming, debugging, object comparison, XML tools, data and object comparison, change management etc. It’s easy to see the tremendous benefits of shelling sharing.
Please use the comment link below or send an email to firstname.lastname@example.org if you have any feedback on this topic or if you want to hear about other specific topics.
IBM recently announced WebSphere Application Server V7
. It’s a release with lots of great features. I want to bring one new feature in particular to your attention in case you don't catch it in the high level announcement material.
In its last version, WebSphere began shipping an implementation of the Java Persistence Architecture
(JPA), which is the persistence mechanism specified for EJB 3, part of the J2EE 5 standard. JPA takes many of the best features of frameworks such as TopLink and Hibernate to create a standard, more flexible, leaner persistence architecture than that provided in EJB2.
For enterprise applications that require container management and full-blown object-relational mapping, the JPA implementation in WebSphere is definitely something you should investigate using. (For new data-centric applications that don’t require object management or complex ORM, I will of course be a proponent of using the pureQuery API. :-) Remember, WebSphere sMash
uses pureQuery for its data access, so it’s not just me saying this.)
WebSphere Application Server ND v7 now leverages pureQuery. It accelerates execution with Heterogeneous Batching and it gives a fast track to static SQL with a new generator.
Heterogeneous batching provides the capability to save on network costs by batching up updates across multiple objects (tables) in a single network call. See Vitor's article
for more about heterogeneous batch.
I assume most of you know the benefits of static SQL in terms of performance, access path lockdown, and security. If you don’t, go read this article.
The performance benefits of static SQL are further quantified in this article
The procedure to enable the JPA app for static SQL is fairly simple. You run a command (wsdb2gen) on the JPA application to gather up and generate the possible SQL statements (i.e. CRUD statements) for your objects. Also it will generate SQL for all the named queries in your application. You can then bind the output from this command line tool using the DB2 Bind commands (or tools) to create the SQLDB2 packages (you can also do this from the WAS Console) and then you deploy them on the appropriate DB2 servers. Our enablement team is working on some educational materials and demos to show how this works, and I’ll be sure to let you know when those are available.
The nice thing about this WebSphere JPA/pureQuery integration is that you don’t have to run your application to gather the SQL. Simply run the wsdb2gen tool against the application. No need to exercise different code paths, etc. And, since you need the IBM Data Studio pureQuery Runtime anyway, you can always use the client optimization
capability in the pureQuery Runtime to go back and capture any SQL that other parts of your program may be producing.
Over time, you’ll see more and more capabilities designed to make the DB2 and WebSphere combination work better together, with pureQuery acting as the enhancer for that optimized capability.
Let me know if you have any more questions about this capability (or anything else you may want to know about Data Studio) by clicking on the comment link below or sending an email to email@example.com.
If you want to hear more about JPA and pureQuery, there are a couple of IOD
sessions you should sign up for on Friday, October 31.
My colleague, David Wisneski, will be going into a lot of detail in his talk:TAD-1537 Developing and Deploying JPA Applications with Static SQL and pureQuery
(Friday, October 31 at 10:00 a.m. at the Mandalay Bay South Conventiion Cetner, Breakers I)
Right after that, I will be presenting on:TAD-2476 JPA, Hibernate, and pureQuery – the best of Both Worlds.
(Friday, October 31, at 11:30 a.m in the Mandalay Bay South Convention Center, Breakers H)
And if you want to talk to me earlier, you can catch me at my Meet the Experts
session (MTE-3169A) on Thursday, October 30th, at 9am. Hope to see you there!
Traditionally query tuning has been perceived to be a function only performed by DBAs, but with a product such as DB2 Optimization Expert for z/OS, I’d like to suggest that this perception could change and become a task that a developer could perform. At the very least, developers could do some due diligence before calling in a DBA. As a former DBA, I was always more impressed with developers who were willing to do some of their own analysis before calling me.
It’s understandable why developers don’t give much thought to query tuning. First of all, developers tend to only focus on the results a query returns. Am I getting the data I need? Secondly, developers tend to be under tight deadlines to complete projects, and don’t have the time to truly examine how queries perform. Thirdly, understanding how the EXPLAIN tables relate and how to interpret the information contained in them can be intimidating and cryptic.
Optimization Expert offers several tools and advisors that can help a developer write more efficient SQL:
This tool takes a query and formats it so that is easier to read, and provides the ability to expand and collapse sections of the SQL. If you click on a table in the FROM clause, it will highlight related columns in the SELECT and WHERE clauses. Conversely, when you select a column in the SELECT or WHERE clause, it will highlight the table the column is related to. Additionally, if your statement references a view, Query Annotation provides the ability to drill down into the SQL in the view. The figure below is an example of a simple SQL statement that references a view, and how you can drill into the view to see the underlying SQL. An additional feature offered in this tool is Annotation, which gives you pertinent statistics used by the optimizer at the table and column level.
Access Plan Graph
This tool generates a diagram of the current access plan for a query, showing how DB2 accesses data to process a query. This information is the same information that is in the PLAN_TABLE, but is presented in an easy-to-read, visual format. Yes it is similar to the graph displayed in Visual Explain, but has some additional features such as infopops, which display information as you hover your mouse pointer over a node. The information displayed is dependent on the type of node that you hover over.
In the example below, the infopop provides information about the TBSCAN node, the Cardinality, Total Cost, I/O Cost, CPU Cost, that a sequential prefetch is being perfomed, as well as page ran access since this table is partitioned. Currently the Query node is selected, indicated by the squares in the four corners, and the Node Descriptor is providing costing information for this query. If you were to select the TBSCAN node additional information beyond what is displayed in the infopop would be displayed in the Node Descriptor.
Access Path Advisor
This advisor interrogates the plan table and highlights potential access path performance issues and provides recommendations on how to eliminate such issues. An explanation and description of issues are given to you, and the recommendations are ranked by severity as Low, Medium, High and Disaster. The figure below shows the results of running Access Path Advisor with three low severity warnings. When you select one of the warnings, a description and explanation for that warning are displayed, as well and the plan table information.
This advisor is the one single tool in Optimization Expert that can assist a developer in writing efficient SQL. It interrogates the SQL statement and makes recommendations on how to rewrite the query to make it more efficient based on best practices rules. When you click on a recommendation, it highlights the line of the SQL statement that the recommendation pertains to. Once again, an explanation and description are provided to you. The figure below shows the results of running Query Advisor. In this example there are two query rewrite recommendations. When you select a recommendation, the line of SQL is highlighted, and you are presented with a description of how to rewrite the query, as well as an explanation of the recommendation.
Most SQL performance issues are never discovered until they are in a production environment, where they impact Service Level Agreements, consume precious resources, or in extreme situations cause application outages. If an organization were to use Optimization Expert and expose it to their developers, production SQL performance issues could be eliminated or greatly reduced. Additionally, it could be used as an educational tool for coding efficient SQL.
Currently Optimization Expert is an Eclipse RCP application, but we will soon be releasing a version that will integrate into Data Studio and support shell-sharing. This is another reason for developers to incorporate this into their development process.
If you’re a DBA, do you think any of your developers would like to learn this tool? Do any of you have experience in your shop with developers doing query tuning? If so, I’d love to hear from you about what works and what doesn’t work, or any other suggestions that would make this tool even better for developers and DBAs.
One final note, for those of you attending IOD in October, I encourage you to attend the following hands-on lab: 2568A – SQL Tuning for Everyone: Using IBM Data Studio Optimization Expert for DB2. This lab will be led by two members of the Optimization Expert team. I think it will be well worth your time.
-- Ray Willoughby
Have you heard the news? The new release of IBM's premiere data modeling tool, Rational Data Architect (RDA) 7.5 has been announced and will be available for download on September 26. The key themes for Rational Data Architect, now and in the future, include integration and to provide the foundational modeling capability you need to support your information initiatives, including data privacy support.
This release includes our first step in integrating with Optim Data Privacy solutions and of course, the integration with the other architecture management products in the Rational 7.5 portfolio. Because we're committed to supporting a heterogeneous database environment, we've also extended our support for both Oracle and SQL server databases.
I’ll cover each of these briefly.
Rational Data Architect 7.5 integration with Optim data privacy and test data management solutions
If you are not familiar with Optim , this portfolio of products are associated with data privacy, test data management and data archiving along with other related products. These products came into the IBM family with the acquisition of Princeton Softech. More and more organizations have to deal with protection of personal data.
You have probably heard a lot of stories in the news of large companies that have been compromised because confidential information of its clients has been stolen. A lot of theft happens within the company and so it becomes difficult to manage. Optim gives you tools to enforce a data governance strategy.
What we have done in Rational Data Architect is now allow you to specify data privacy attributes (policies, data masking rules) and export the data privacy and test data definition artifacts for easy import to an Optim directory that can be used for creating the physical test databases. We have extended the domain model in RDA so that you can specify data privacy classifications and masking rules right in the domain model and then apply it to the various columns in your data model. This will then create a file that Optim can consume and read. Not only do you have a privacy specification that can be reused across your test databases, it saves a lot of manual effort. And you can also generate a compliance report that will come in handy for audits!
If you want to try this integration yourself, go to the IOD web site and sign yourself up for the following hands on lab that I and my colleague in Optim, Rick Buglio, will teach:
HOL-2712 Integrating IBM Rational Data Architect and IBM Optim Thu, 30/Oct, 10:00 AM - 01:00 PM
Rick and I are also working on a developerWorks tutorial that we hope to have available in the October timeframe.
Integration with Rational Software Development Platform 7.5
The key thing about this announcement is the timing. Rational Software Architect (RSA) which is a superset of Rational Application Developer and Rational Software Modeler also announced their 7.5 release on the same date as RDA. One of the key benefits of this is that all the Rational tooling including Rational Data Architect are now lined up on the same Eclipse version 3.4.
This gives you a lot of benefits in terms of functionality. RDA still maintains its close integration with RSA so that you can do things like transform a data model to an application model, etc. We have enhanced some further transformations especially in the XML space. Also there is a RDAM (Rational Data Architect + Rational Software Modeler) bundle based on 7.5 that gives you a great deal on these two products together.
Enhanced support for non-IBM databases
We're addressing customer requirements for non-IBM databases including support for Oracle parallel degree, synonyms for stored procedures, trigger and index pages. In addition, we've added SQL Server integrated authentication.
Where is the trial?
The trial will be available after eGA at the end of this month. I’ll update this blog when it’s actually available, or you can check then at the RDA product web site .
I look forward to seeing you at IOD. For those of you doing data architecture, be sure to join me and other Rational developers at a Birds of a Feather session on Wednesday, October 29th at 6.00 pm in the Mandalay Bay North Convention Center - Tropics B. The number is BOF-3280. If you can’t join us, please use the comments link below or send an email to firstname.lastname@example.org to send feedback on our product or if you are interested in more discussion on particular areas of data architecture.
My name is Thuan Bui and I am part of the Data Studio Enablement team. One of my jobs is to create demos that illustrate the capabilities and business values of the Data Studio portfolio. A key one we just recorded and put on the Web is a two-part demo
that uses a story to bring to life the things you can do with these products and how the integration can enhance teamwork.
The demos we build are not mocked up screen shows (at least most of the time). Because people use these for live demos as well as recording, we really have to come up with more than just a story. We need to create a relevant database schema, load it with data, build supporting applications and so forth. And of course we use Data Studio to help us with all that :) . (And, yes, we occasionally find bugs and usability issues that we report back to the development team.)
This scenario-based demo shows how and why Data Studio portfolio is used throughout the entire data lifecycle including design, development, deployment, and management stages. We start showing how pureQuery client optimization
is used to stabilize performance for an existing JDBC application, then show how to use Rational Data Architect for data design tasks, then feed the model to Data Studio Administrator for data model changes and deployment, and how to use pureQuery Outline for impact analysis of a potential schema change. (By the way, if you have no idea what I mean about pureQuery outline, see this article
.) We use Data Studio Developer tools for SQL, Java application and Web services development and deployment, and finally show how to use the web-based Data Studio Administration Console for database and system health monitoring.
One of the challenges in producing this demo is that we have lots of different components to highlight, with the right level of information, within a time limit. We try not to make it too long nor give too many details so that both technical and non-technical viewers can consume it and don’t lose interest.
Although the story is for a fictional enterprise, our goal is to try and show problems and resolutions that could apply to companies in the real world. Perhaps the most challenging thing for those of us inside IBM is to come up with scenarios that will resonate with you, the people who have to deal with the data management lifecycle every day. Let us know if you think we’re hitting the mark or if there’s something more or different we should be showing, maybe an example from your own experience.
Our next demo will focus on the story for z/OS environments.
We’re looking forward to hear from you – just add a comment using the Add a comment link below or send an email to email@example.com.
Have you ever tried to change a database only to realize it’s a lot more complicated than you thought? As the architect for Data Studio Administrator, I’d like to tell you about new capabilities in Data Studio that can help.
Now that Data Studio Administrator has come out, one commonly asked question is, “What’s the difference between the base Data Studio (included in my data server license) and Data Studio Administrator?” The simple answer is that today Data Studio Administrator provides database change management by extending the object management capabilities that exist in the 'base' Data Studio. As Bryan blogged about previously , it will be extended even further in an upcoming release, but for now, this is the simplest way to view the difference.
I'll illustrate with an example. Suppose you want to increase the length of a VARCHAR column. DB2 provides an ALTER statement that will change the database in one command. In simple object management there’s a single command that does exactly what you want. These great DDL statements are sometimes referred as schema evolution.
Those still reading have probably encountered a situation where simple object management was too simple; i.e., there is no single schema evolution command available that would implement the desired change. A change that requires multiple commands is called an extended alter.
For instance, suppose you need to:
- Add a column in the middle of table
- Delete a column
- Decrease the size of a character field
- Change the class of a column data type
These actions require that you drop and recreate the table.
The base Data Studio can analyze dependencies and generate SQL to handle some extended alters. Unfortunately, it doesn’t do everything. For instance, it doesn’t automatically preserve data, or run utilities.
Some extended alters require a great number of commands perhaps across a number of different objects. The commands might include DDL, DB2 commands, DCL, DB2 commands, utilities etc. Further, the changes might require changes be made to multiple objects. Change management tooling not only understands complex relationships, dependencies, and impacts but can aggregate these changes and deploy them in the proper order.
You can get database change management capabilities for DB2 on Linux, Unix, and Windows in Data Studio Administrator.
Suppose you must make an extended alter but also have a dependent trigger, a view, a procedure, and/or a user defined function.
Using Data Studio Administrator, you have the option to unload and reload data using DB2 utilities like IMPORT, EXPORT, LOAD, or LOAD FROM CURSOR. If you also own High Performance Unload you could use that too. You can automatically generate REBIND, RUNSTATS, and REORG utility commands for affected objects.
Suppose those trigger bodies, view definitions, and SQL UDFs need additional changes.
You can use Data Studio Administrator to:
- Show the impacted objects
- Add additional changes to the same change e.g. update a view definition
- Leave objects in a valid state.
- Show inconsistencies at deploy time instead of run time.
- Quickly undo a change.
As you can see, Data Studio provides you the solutions to understand the full change impact, keep it simple, and deploy it safely.
Please write to firstname.lastname@example.org or add a comment using the comment link to below if there are other specific topics you'd like me to blog about.
Just a reminder that I'm here to take input on our Data Studio administration story.
Through the grapevine, I've been hearing of some commonly asked questions ever since we announced IBM Data Studio Administrator
. I'll answer a few of the questions here, and will let Jeff Ruggles, our lead architect on Data Studio Administrator, tackle some of the more indepth ones later. Q: Will Data Studio tooling replace the DB2 Control Center?
A: Yes, eventually. DB2 Control Center has evolved over many releases, so it will take a while for the Data Studio tooling to fully encompass the functionality in DB2 Control Center, but it is certainly our intention to eventually replace DB2 Control Center with Data Studio tooling. The next major release of Data Studio Administrator when combined with IBM Data Studio Developer
should provide for DB2 Linux, UNIX, and Windows a majority of the functions that DB2 Control Center provides today.Q: When will Data Studio Administrator support other databases (especially DB2 for z/OS)?
A: Data Studio's mission is to provide uniform tooling across all popular RDBMS', and we want Data Studio Administrator to be the preferred tool for DBAs for general administration. Providing support for DB2 for z/OS is high on the priority list, but it isn't the highest, because we (IBM) already provide a very comprehensive solution with the IBM DB2 Administration Tool for z/OS
and the IBM DB2 Object Comparison Tool for z/OS
products that has comparable function to Data Studio Administrator. So, stay tuned... and know that we're working hard to deliver Data Studio across all of your RDBMS'.
I should start by introducing myself. My name is Vijay Bommireddipalli, and I am part of the Data Studio Enablement team. Our team works with customers and IBM Business Partners to get up and running on the latest and greatest stuff coming out of the development teams of Data Studio.
I just finished a couple of single day customer events that are part of a multi-city U.S. roadshow about Data Studio and IBM Mashup Center. The theme of this roadshow is about enabling a variety of enterprise data assets to end users in a services oriented paradigm, allowing business users instant access to information and freeing up IT from application development overload. These roadshows are *free* events that anyone can attend, so I'dlike to give you a little taste of what you can expect if you decide to come to one of the remaining shows in Chicago, Dallas, New York, or Boston. (See the front page of the Data Studio community space for links to registration or contact your favorite IBM sales rep.)
The morning session focuses on Mashups. Here my colleagues, Tom Deutch and Chris Gruber, showed how Mashups and other Web 2.0 technologies are changing the role of IT in an enterprise. IBM introduced IBM Mashup Center, which enables a situational application environment inside the enterprise. Using IBM Mashup Center, IT can relieve themselves from being a bottleneck for application requirements and become an application enabler. You can read more about IBM Mashup Center here. The afternoon session focuses on one aspect of Data Studio, which is creating the most efficient data access mechanism to backend data sources. This focuses on architectural approaches to data access in general. The idea is that IT can enable data assets by taking best practices, efficiency, and security etc. into account while relieving themselves of churning out end user applications. This is a key aspect of eliminating the applications backlog that most IT departments typically seem to face. These data assets can then be accessible to a variety of applications (including Mashups).
The way we present the material is walk the attendees through building Mashups on the screen, all the way to accessing and organizing data in their enterprise backend sources, showing a number of live demos of the technologies along the way. The attendees at a recent session in California seemed to "get" that picture. It was good to see the reaction of the attendees and the affirmation that some of the problems we are solving with IBM Data Studio and Mashup Center are actual problems they face on a daily basis.
On Thursday (Aug 21st), I will be in Chicago on the next leg of this roadshow. Hope to catch you at one of the future road shows. I also hope to catch some live blues music while in Chicago :)
It is hardly surprising that there is a certain amount of interest among our customers over the relationship between the two “pures” that are shipped from IBM Information Management. As the architect for pureQuery, here's my perspective on it.
Let’s begin by looking at each of the “pures” independently:
pureXML was delivered first. It refers to the technology that was shipped in DB2 V9 (aka“Viper”) that provides a new query language, new storage technology, new indexing technology, and other features to support XML data. (See this article by Cindy Saracco for a good introduction). The "pure" in "pureXML" was chosen to indicate that XML data is stored and processed in its inherent hierarchical structure, and that XML data is *not* converted to relational format and also not stored as text. In other words, DB2 pureXML offers the "purest" from of storing and managing XML data, which results in maximum performance and flexibility. "Pure" means no compromises and first class relational-XML integration.
pureQuery refers to a data access platform that simplifies developing, managing, securing, and optimizing Java data access. It consists of tools (Data Studio Developer includes such tools), a runtime, and the pureQuery API. The reason security and optimization are in the definition is because pureQuery can help developers code data access in a way that can help optimize performance. And also, the ease of switching to static SQL when accessing DB2 data help with both performance (static generally runs faster) and with security (package level security vs. table level, also avoidance of SQL injection), and with managing the development environment (package versioning, etc). Note that although pureQuery refers to Java data access right now, we’re actively investigating how we can bring the pureQuery benefits to other dynamic access as well.
So how did this platform get the name pureQuery? It didn’t start out that way (as is often the case with new products or technologies). We had two internal code names for the project, Data Zero (because it’s the data access layer for Project Zero) and JLinQ, because it compares very favorably with Microsoft's LINQ initiative (all the power of database access without having to change the C# and Visual Basic languages – we achieved all the major features of LINQ but stayed pure standard Java). pureQuery embraces the full database access without watering down the SQL or database capabilities. It avoids least common denominator approaches that dilute the ability to access what you really need in a database and integrates all aspects of the life cycle in both runtime and tooling, "Pure" means no compromises and first class Java-SQL-database integration.
As the project moved closer to release, it was time to choose a name. Because the project represented application plus database integration at what we feel is the best, truest form, without all the mess that sometimes bogs software down, early users called it "pure data access" or "pure data query (PDQ)." The pure concept, and the PDQ acronym, really resonated with the marketing team (the same team that named pureXML), and the pureQuery name was born.
So, that’s how pureQuery got its name. Is there a technical relationship between pureXML and pureQuery? The data web services and XML result handler are the first parts of the integration and we are rolling out more XML as we go. (And pureQuery tools (Data Studio Developer) provides support for XML data as well. But for now anyway, the relationship between pureXML and pureQuery is primarily that of being the new cool kids on the block.
-- Steve Brodsky
I announced our latest Data Studio offerings in this blog about 4 weeks ago. Since that time, I've been getting feedback that many people are confused about how we are packaging the Data Studio offerings, and they aren't sure how these new offerings fit into the existing Data Studio development and administration tools. In a nutshell, we now have three offerings:
- The Data Studio "base" offering is a no-charge product that contains the tools that customers get when they buy a DB2 LUW, DB2 Connect, or IDS product from IBM. The base offering is made up of the application development and database administration tools that shipped with Data Studio 1.1.x (Visual Explain, stored procedure builder and debugger, SQL builder, simple ER diagram support, table editor, etc.). This same no-charge product can also be downloaded without purchasing another IBM product here: http://www.ibm.com/software/data/studio/
- Data Studio Developer V1.2 consists of the Data Studio base offering above, with a number of additional tools that help customers build pureQuery applications. This includes tools that allow the developer to capture JDBC queries during unit test, and bind those queries as static SQL using the pureQuery runtime. This allows you to benefit from the pureQuery technology advantages, without changing your application source code to use the pureQuery APIs directly.
- Data Studio Administrator V1.2 consists of the Data Studio base offering with many additional improvements in database administration for DB2 LUW that greatly reduce the human labor associated with making complex database schema changes:
- Synchronizes, copies, clones, or merges database schema definitions from a source DB2 system to a target DB2 system
- Remotely runs DB2 commands, such as utilities, application rebind, and data import/export
- All generated commands can be viewed, added, deleted, and validated before running them
- Graphical wizards lead novice users through the DB2 schema change management process:
- Manages side-effects and dependencies
- Generates Data Definition Language (DDL) to change the database
- Preserves data
- Preserves authorizations
- Preserves application binding
- Automatically propagates changes to related objects, streamlining change management
The IBM Data Studio Administrator v1.2 and IBM Data Studio Developer v1.2 are now available as a 30-day trial package from the following website:
Data Studio Administrator 1.2:http://www14.software.ibm.com/webapp/download/search.jsp?go=y&rs=swg-dsa12
Data Studio Developer 1.2:http://www14.software.ibm.com/webapp/download/search.jsp?go=y&rs=swg-dsd12
The trial packages will allow use of new features of Data Studio Administrator and Data Studio Developer v1.2 products for 30 days. It will revert to the existing Data Studio v1.1.x no-charge functionality at day 31.
In addition, please check the following link for a video overview of the Data Studio Developer v1.2 features.http://channeldb2.ning.com/video/video/show?id=807741%3AVideo%3A10108
I hope you have a chance to download these new Data Studio offerings and see what they have to offer. If you have any feedback about this blog entry, either add a comment to this blog or send an email to email@example.com.
-- Curt Cotner
I am a design architect for DB2 Optimization Expert for z/OS (OE), which is IBM’s query optimization solution. I would like to give you a brief overview of what OE is and how it can help you identify and resolve query optimization issues.
Prior to joining IBM I was a DB2 for z/OS DBA for twenty plus years and can relate to the challenges DBAs face every day, such as:
- Reacting to situations where a query is consuming system resources and impacting overall system performance
- Assisting application developers in designing and deploying new applications
- Monitoring existing workloads to identify poor performing SQL and optimizing query performance
Back then query tuning was a very manual process, and like me, I’m sure many of you developed your own processes and methodologies to capture the information needed to analyze query access paths. Then IBM released Visual Explain which provided a nice graphical view of the access path, as well as catalog information related to a query. Later a Statistics Advisor feature was added which helped identify what statistics should be gathered to improve query performance. This was good stuff, but Visual Explain only performed analysis on a single query, and there were limitations on what sources the SQL could be captured from.
In March 2007 when DB2 for z/OS V9.1 was delivered, two new query optimization tools were released: OSC (Optimization Service Center) and OE. OSC is a no-charge offering of the tools and offers a subset of OE features. I will explain more about the features in the chargeable vs. no-charge offerings in a future post. OE V1.1 only supported DB2 for z/OS V9.1, but based on customer feedback, DB2 for z/OS V8.1 and data sharing support were added and delivered in March 2008 with OE V1.2.
So what does OE offer? It has three distinct sets of features:
- Tools and advisors that focus on single query statements
- Tools and advisors that focus on workloads (a group of statements that you define)
- Workload performance monitoring and optimization.
Thesee are the tools and advisors that operate on a single query:
- Access Plan Graph
- Query Report
- Visual Plan Hint
- Statistics Advisor
- Index Advisor
- Query Advisor
- Access Path Advisor
And these are the tools and advisors that work on a workload (a set of statements):
- Workload Statistics Advisor
- Workload Index Advisor
- Workload Query Advisor
Finally, here is the capability of workload performance monitoring (only available for V9.1 environments):
- Creation of monitor profiles for normal processing
- Creation of monitor profiles for user-defined exception processing
This monitoring capability is not intended to be a replacement for Tivoli OMEGAMON XE for DB2 Performance Expert on z/OS, DB2 Query Monitor for z/OS, or related tools that are full-blown monitors with sophisticated interfaces.
Each of these feature sets can be discussed in great detail and I will defer that discussion to future posts.
I would like to close by posing a few questions to you. You can cut and paste these questions into a note and send them to firstname.lastname@example.org, or post your comments here by clicking on the Add a Comment link below.
1. What are your primary pain points in tuning SQL?
2. Who do you see as the primary audience of SQL tuning tools?
3. What tools are you currently using to resolve query problems?
4. Do you currently use or plan to use one of the Data Studio products?
5. In addition to DB2 for z/OS, what other databases would you like to see supported with Optimization Expert capability?
Until next time.
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
Hello all. I'm the product manager for Rational Data Architect (RDA), IBM's strategic data modelling tool. I visit lots of customers and talk to my own colleagues about this product, and I would like to take this opportunity to clear up a few things that I keep hearing over and over. Here are three things you may not know about RDA:
Number 1: Rational Data Architect is part of the IBM Data Studio family (don't let the name fool you)
As my teammate Holly Hayes describes in her Data Studio "big picture" article, RDA is the data design component of the integrated data management lifecycle. Rational Data Architect is built on top of the Rational Software Development platform and is designed to integrate well with Rational Software Architect and other Rational Software products. As a matter of fact, other components of Data Studio also are built on this platform and can extend the Rational application development environment with complementary data-centric capabilities. So, if you're a DBA or data architect, yes, you should be interested in something named Rational (especially since it's very likely that RDA can work with most if not all of your existing databases, IBM or non-IBM).
Number 2: Support for Cognos and Telelogic is already in the product.
Ever since the acquisition of both Cognos and Telelogic, I get questions about whether RDA supports them. Yes, the ability to support both is included in RDA V7.0 today, which means you can import a Telelogic(Popkin) System Architect file or a Cognos Framework file into RDA. We'll make this support more obvious in the UI in the next release, but here's how you can do it now:
1. Edit the following file: C:\Program Files\IBM\SDP70Shared\plugins\com.ibm.datatools.metadata.wizards.miti.win32_1.0.1.v200803201158MetaIntegration
2. Search for Cognos or Telelogic and set the interface you want to import from to 'true' and save the file.
3. Then when you go into RDA and go File-> Import-> Data-> Data Model Import Wizard you will see the newly created interface that you enabled in the drop down box.
Number 3: Rational Data Architect is NOT just for architects.
There is a misconception that only hard core data modelers can benefit from Rational Data Architect. Although RDA gives the hard core data modelers the advanced capability that they need in a data modeling tool, it also provides other roles with a good representation of the data and the relationship of all these data items. So what other people can benefit from RDA in your organization? Let's start with these:
- Business analysts can use RDA to help them understand the business entities in their organization and the relationships that exist. It also helps them to create reports that they need to keep track of all their information.
- Data Governance Stewards benefit by being able to store and define naming standards. It also helps this group to identify new standard requirements and store domain and naming standards.
- Database administrators use RDA to quickly analyze database structures and generate DDL for their data models. Other features are compare and synchronize, and it also allows DBAs to understand any database changes for a specific database release.
- Developers can quickly understand their physical system design. A lot of people miss this group but the reality is that they are developing their applications for the data in their organizations. Only RDA allows you to do things like convert a data model to an application model and vice versa. How do you do this? You can do this by using RDA in conjunction with Rational Software Architect. It also gives you a convenient place to store all your SQL Scripts in one tool.
Rational Data Architect has really gained popularity over the last couple of years since it was released. With the next release, which is planned to be available later this year, you will see more ground being made by building on top of the Eclipse 3.4 foundation and more enhancements put in place to make RDA the premier tool for database modeling. More on that later....
-- Anson Kokkat
Late last month, I asked you DBAs to give me some feedback on our tools for administrators. I'm still looking for more! Thanks to Fred and Rahul both for your comments. Rahul, I believe your issues with DDL generation in Data Studio Developer are being addressed through the forum.
Anyway, since both comments were talking about DDL, it seemed to me like this was the perfect opportunity to talk about Data Studio Administrator, which we just announced on July 8th. Some of you may be familiar with this product under its previous name – DB2 Change Management Expert.
Data Studio Administrator is really designed from the ground up to handle complex database changes. Let’s face it, database changes can be tough. Understanding the dependencies, the authorizations, the effect on applications…. It’s a really big deal to get it right because the consequences of getting it wrong can range from annoying to devastating. The goal of Data Studio Administrator is to make it much easier for you to model the target database, compare two sets of objects to see where they differ, migrate a set of objects to the target, or redefine the target objects to be like the source. Changes automatically roll through all related objects, streamlining the entire change management process.
And, because we all make mistakes, you can automatically undo all changes, if necessary.
There are a lot of other cool things like reporting on the impact of proposed changes to identify dependencies and/or mitigate risk. You can also publish an HTML change report as part of the DDL generation, which looks like this:
So, when you need to do complex, or even simple, schema/DDL changes, the Data Studio Administrator acts as a great process control flow engine to keep you out of hot water. I strongly encourage you to check this webcast
on July 23rd by our lead engineer on this project to learn more. If you miss it, there will be a replay available.
Data Studio is all about collaboration, and Data Studio Administrator helps you achieve that with your team. It can integrate input from multiple group members who are participating in the change process. It integrates with Rational Data Architect making it easy to move from logical modeling to physical implementation.
Data Studio Administrator currently supports DB2 for LUW, and we plan to extend it to other DBMS platforms (both IBM and non-IBM) in the future. Stay tuned…
-- Bryan Smith
It’s been just over a month since I last wrote. It’s been pretty busy around here getting ready for the announcement we made today for new releases of some Data Studio products.
Before I get into the details of what we’re announcing, I think I need to clarify exactly what we mean when we say “Data Studio.” Some people still equate Data Studio strictly with the Eclipse-based database development tools. But, really, Data Studio is a family of software designed to deliver an integrated, yet modular, data management environment for all phases of the data management lifecycle, with the goal of increasing collaboration and productivity as data moves through from “requirements to retirement.” And because realizing this vision is a process in itself, we are still in the midst of building out integrations among new and existing tools. This means, the Data Studio family contains a mix of variously branded products. An example of this is Rational Data Architect, which is our key product offering in the data design phase of the data management lifecycle.
One of my colleagues has written a really good developerWorks article that goes into more depth on both the strategic vision of Data Studio as well as how our current offerings address key tasks and roles in a data-centric environment.
So here are the new releases we are announcing today:
· IBM Data Studio Developer 1.2
· IBM Data Studio pureQuery Runtime 1.2
· IBM Data Studio pureQuery Runtime for z/OS 1.2
· IBM Data Studio Administrator for DB2 for Linux, UNIX, and Windows 1.2
The value these new releases deliver includes:
· Improve Java data access performance for DB2 – without changing a line of code
· Speed up problem isolation for developers – even when using Java frameworks like Hibernate, OpenJPA, Spring, and others
· Build out the family integration in the administration space with Data Studio Administrator
Let me go through each of these in a little detail:
Improve Java data access performance for DB2 – without changing a line of code
IBM Data Studio Developer 1.2 together with IBM Data Studio pureQuery Runtime 1.2 now provide client optimization. This release provides client optimization for JDBC access that lets you turn dynamic JDBC execution into static execution without changing a single line of source code - in fact, you don’t even have to have the source code. We capture the SQL from the application and provide tooling in Data Studio Developer to help the developer or DBA view, edit, bind, verify, and version the packages to enable static execution.
As you may know, I’m a big proponent of static SQL because it can improve throughput, make response times more stable, improve security, provide more information for capacity planning, and drive down CPU cycles. You can read more about static SQL benefits here.
I’m also very happy to have pureQuery runtime capability on z/OS for both DB2 V8 and V9. You can now develop pureQuery applications, Web services, and stored procedures for native z/OS deployment, and use client optimization for JDBC access for existing z/OS Java applications. This can add up to huge savings for z/OS environments. Make sure you see this article that includes details of our testing to quantify the performance benefits and CPU savings that pureQuery can bring to DB2 for z/OS.
Speed up problem isolation for developers – even when using Java frameworks like Hibernate, OpenJPA, Spring, and othersHow much time do you (whether a developer or DBA) spend trying to isolate performance issues first to a specific SQL statement, then to the source application, then to the originating code? It gets more complex with the three-tier architectures and when popular frameworks are used. Developers may never even see the SQL generated by the framework. Data Studio Developer 1.2 makes it easier to isolate problematic SQL. First, it is much easier to associate SQL with static packages rather than a generic package name used with dynamic JDBC. And second, Data Studio Developer provides an outline view that traces SQL statements back to the originating line in the source application (whether you’re executing it statically or not). In addition, it shows SQL and table relationships so impact analysis is easier since we can get answer to questions like “Where is this column used in my application” or “What tables does this application access” or “What SQL does that application issues against this table?”.
Build out the family integration in the administration space with Data Studio Administrator
This release re-brands DB2 Change Management Expert to IBM Data Studio Administrator 1.2 for DB2 for Linux, UNIX, and Windows. The re-branding reflects the new integration with Rational Software Delivery Platform V7 and other Data Studio offerings. This improves productivity and reduces deployment costs because you now have a common, integrated workspace on the desktop for architects, developers, and administrators. You can co-install Rational RequisitePro, Rational Software Architect, Rational Data Architect, Rational Application Developer, Data Studio Developer, and Data Studio Administrator, making it easy to move from one task to another. Plus Rational ClearCase integration manages object versioning and sharing across teams to keep everyone on the same page. Data Studio Administrator 1.2 enhancements also improve DBA productivity, change management quality, impact analysis, and auditability.
OK, time for me to go. See the announcement letters for both the z/OS and Linux, Unix, Windows releases for more information about these new releases and when they are expected to be available. And keep an eye on this blog and the Data Studio community space for more details and to join in with other users.
Talk to you soon.
-- Curt Cotner