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!
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 want to take a moment of your time to share the new support page that highlights the features and capabilities between the stand-alone and IDE packages of IBM Data Studio:
Feature in IBM Data Studio (Stand-alone versus IDE)
Previously, this feature comparison table was available only from the Features and Benefits page but now it has its own dedicated page and is available through the IBM Support site. Use this page to compare features and help you determine which package is appropriate for your needs.
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
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.
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.
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 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
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.
Today's entry is inspired by a recent Dilbert cartoon
where the pointy-haired boss tells Dilbert that he needs to get better at anticipating problems. While we'd all like to see problems before they happen, we need a little help here, and inspiring words from the pointy-haired boss just doesn't cut it.
Today's DBAs have a lot of responsibility; arguably more than they have had in the past in terms of number of systems and the complexity of these systems. Most DBAs have implemented early detection mechanisms for production systems, but what about non-production or less-critical systems like development or test systems? These are often called "non-critical systems" until a severe issue occurs with them, and then they suddenly become critical because they are preventing new work from being implemented on schedule. Sometimes it may be difficult to justify the cost of robust monitoring software like DB2 Performance Expert
, Tivoli OMEGAMON for DB2
, or IBM Tivoli Monitoring
for these labeled "less-critical" systems, so what's a pro-active DBA to do?
One solution is the Data Studio Administration Console (DSAC)
. It is a no-charge offering with your data server license that supports DB2 for z/OS and DB2 for Linux, UNIX, and Windows with an "at-a-glance" view to see the health and availability of these systems. It is not a full-blown performance monitor, but it does show several key indicators like whether the system is up/down, locking rates, resource utilization, etc.In new news, although DSAC used to be the delivery vehicle for the Q Replication Dashboard, we have just made available a new and improved Q Replication Dashboard One of our Gold Consultants, Frank Fillmore, will be discussing this dashboard in a webcast (two sessions to accommodate different timezones) with IBM on September 15. Get the details from his blog.
With this change, you might be asking what other changes are in store for DSAC? You may have heard us talking about our next generation performance manager. It has a new architecture along with a web browser interface that will support DB2 and eventually other DBMSs. Once we roll out this performance manager (be sure to attend IOD
to find out more), we plan to use this new architecture for the next release of DSAC. It will still provide the same high-level health and availability capabilities that DSAC 1.2 provides today, but the Web user interface will be refreshed and have consistency with our other Web UI offerings.
So, don't let the pointy-haired boss get you down the next time they ask you to anticipate problems better -- just smile, thank them for their leadership, and go take a look at DSAC to prevent those critical situations.
Many of us on this blog talk about what IBM is doing to improve the Java database applications space, both from an application development and database administration point of view.
Here is the way I like to categorize the areas in which these improvements are seen:
- A comprehensive monitoring capability that gives you a broad, yet contextual perspective on database and application performance, and early warning of emergent problems.
- Better insight into applications, and the ability to stabilize, and improve resource utilization in a non invasive way
- Building performance into the applications early on in the development cycle.
Performance Expert with Extended Insight, pureQuery, Optim Development Studio, and Query Tuner are all important components
of the solution that we call the Java Acceleration Solution
. But if you are a big picture person and want to see how these components work together, we put together this 15 minute video video
on developerWorks that we hope you'll find entertaining as well as giving you an example of this bigger picture as you follow along with our friends at the fictional Great Outdoors Company.
By the way, we also developed some hands on lab material that walks you through these scenarios so that you can explore, and try it out yourself. If you're interested, we'll be offering this as a lab at the Information on Demand Conference
in Las Vegas this year. if you are attending this year, and would like to try it out, be sure to look for the Java Acceleration Solution Hands-on Lab (HOL-1276) in the Integrated Data Management Track of IOD.
Hi, I’m the lead architect for Optim pureQuery Runtime
, and I want to start using this blog to help address questions that I get from people as they learn about or use pureQuery capabilities. In this first blog, I’ll discuss the new SQL replacement capability.
By using client optimization, an administrator can modify the SQL from a captured application. The enhanced tooling to support this capability is described in Sonali's article, What's new and cool in Optim Development Studio 2.2
. The intended usage of this feature is to let a DBA make a change to an SQL statement without the need to edit and recompile an application. This could be useful, for example, in late night or weekend emergencies when an application can't easily be changed. It is also useful in cases where a third party application embeds or generates sub-optimal SQL and a change to the application is not possible without contacting the vendor. In any of these cases, you should aim to change the application directly at the first practical opportunity to use the improved SQL.
When I talk about this capability to people, there are two questions that frequently come up:
- What is the extent of the change I can make to the SQL?
- Isn't there a security risk to allow editing of the SQL? How can we control access?
Great questions. I'll discuss each of them separately.What can I change in the captured SQL?
There are restrictions on what you can change when creating the replacement SQL. The Optim Development Studio pdqxml editor will prevent many of the restricted changes, which is why it is strongly recommend that you use this editor to create the replacement SQL. The primary restrictions on the replacement SQL are:
- You may not change the SQL statement type. For example, you can’t change a SELECT to an INSERT.
- The number and types of any input parameters or output result columns must be unchanged. For example, if your SELECT statement is expecting two columns of CHAR and INT as result, your changed SELECT statement must also expect a result of two columns of CHAR and INT.
At first glance, these may seem to greatly narrow the capability. But when you think about it, any changes like those described above couldn't work very well anyway without a corresponding application changes, at which point you would likely just change the SQL directly in the application.
Nevertheless, there are quite a number of useful changes that you could make that would not violate the restrictions. You can:
- Influence access path / index usage:
- Add an ORDER BY clause
- Add OPTIMIZE FOR 1 ROW
- Other "tricks" to influence the DB2 Optimizer (like adding OR 0 =1 to a predicate)
- For Oracle - add a comment hint to end of the statement
- Influence fetch size for distributed queries:
- Add FETCH FIRST n ROWS ONLY clause
- Add an OPTIMIZE FOR n ROWS clause
- Add FOR FETCH ONLY or FOR UPDATE clause
- Add a predicate that narrows the data returned - just be sure it uses literals and not parameters
- Change the locking behavior:
- Add WITH ISOLATION clause
- Add SKIP LOCKED DATA clause
- Add FOR UPDATE clause
- Directly manage the schema name for object references:
- Add or change the schema qualifier on a table or other object reference.
How can we control access?
- Help manage EXPLAIN DATA:
Some people have expressed concern that there is a security risk involved with the ability to change captured SQL. While there is some potential for abuse, there are means for controlling changes. I’ll discuss some control points within the context of how this feature can be used with either static or dynamic SQL.
Client optimization is most frequently used to convert dynamic SQL execution to static execution. To use SQL replacement here, you must modify the SQL before performing the bind operation. Any changes made to the SQL in the capture file after the bind are ignored. So control over the capture file contents needs to be managed across the capture/configure/bind process. In many instances, this would be done by the same administrator. Ultimately the bind is performed by an administrator with all the authority to perform all the SQL contained in the file. This is not very different than a traditional 3GL program deployment. And any bound SQL is then visible for inspection in the DB2 catalog.
There are scenarios where client optimization is used (for example, to gather performance metrics) even when the eventual execution mode is dynamic SQL. In these cases, the capture file is examined at execution time for the existence of replacement SQL. If present, that SQL is used in the prepare and execute. But you can disable that execution-time replacement. A pureQuery configuration property, enableDynamicSQLReplacement, controls whether this is allowed. The default is false, so you have to do something to turn on the execution time replacement. The gives control at a datasource or application level..
An important point here is that all the basic building blocks of security remain in place.
That is, SQL privileges are necessary to execute the bound packages or the dynamic statements. But even with that being true, additional care must be taken. To prevent unexpected changes to the file you must control write access to the file. It can be locked down on the executing server by making it read-only. It is also important to control the updateability of execution-time properties that can affect the application’s execution. The capture file, and any application properties files need to be thought of, along with any executables, as a collection of related resources, all of which need protection.
I hope you’ve found this useful. Let me know if there are other questions you have about pureQuery, and I’ll do my best to answer them.
-- Bill Bireley
After IBM bought Telelogic
I was (and still am) frequently asked “What is the difference between InfoSphere Data Architect and Telelogic (now Rational System Architect). Here is the information I have about the product roadmaps and how these two products complement each other.Before the acquisition:
First, some background to set the stage. As you know, even before the acquisition IBM marketed a data modeling tool which at that time was called Rational Data Architect, which had originated from the old Rational Rose and Rational Software Architect product line. It has since evolved into a robust data modeling solution that is today called InfoSphere Data Architect
Meanwhile, Telelogic was in the business of creating Enterprise Architecture
solutions. The best way to explain Enterprise Architecture is to look at a common application: an IT consolidation project. The goal of the business is to leverage the architecture blueprint of strategy, process, applications, systems, and data to best identify consolidation candidates, then understand the impact of change to the organization in order to successfully plan and implement the transformation. The business goal is to leverage architectural information to maintain, even increase, operational efficiency while reducing costs. Now how do you accomplish this? This is precisely what Enterprise Architecture enables you to do. It lets you come up with the plans and processes needed to move from the goals of what you are trying to achieve from a business perspective. To successfully build a current and accurate architecture for planning, information needs to be imported from various sources within the organization. This information can already exist within Visio (process), Excel or asset management tools (IT content), or data modeling tools (information). Telelogic System Architect was (and is) dedicated to building this higher level Enterprise Architecture, but other tools are more suitable for the detailed work in an individual domain (breadth versus depth). However, synchronization among these point solutions and enterprise architecture solutions is critical for consolidation of data and elimination of duplicate work. The situation today after the acquisition:
Because of the acquisition, IBM and Rational’s robust Solution and Data Architecture line, including InfoSphere Data Architect, provide the capabilities required by focused solution and data architecture teams. System Architect and InfoSphere Data Architect provide customers the solutions they need to address both their high level EA initiatives, and focused data architecture initiatives, while providing a path of alignment between the two efforts. Today, Telelogic Solution Architect has been renamed to Rational System Architect
. Sure, there is some overlap, but this overlap becomes a value-add to IBM customers. Going back to the IT consolidation example, more accurate decisions can be derived from the Enterprise Architecture if data in the architecture comes from current and accurate sources. System Architect’s import from InfoSphere Data Architect extracts information from an accurate and current source. This is one reason that Rational System Architect has a data modeling component.
So to say all of that in one sentence, InfoSphere Data Architect is meant to be the data modeling tool focused on data architects and database modeling, whereas the data modeling components in Rational System Architect are meant to be the tools that support the data aspects of the broader enterprise architecture strategy.Going forward
IBM’s plans for the Rational System Architect product are to expand the concept of Enterprise Architecture Management, making Enterprise Architecture more relevant for more stakeholders within the organization. This is being accomplished by expanding the harvesting, analysis and reporting capabilities in core System Architect, while expanding supported integrations between System Architect and other focused domain solutions. This means that you will see more and more requirements for the lower level tooling being fulfilled by legacy Rational products. As for InfoSphere Data Architect, we are continuing to compete with our peers for the hearts and minds of data architects, enhancing data modeling capabilities, while extending the value proposition of what data designers do by enabling policy-based, management-by-intention capabilities supporting data governance initiatives.
Hope this helps.
In my earlier posting on Oracle support
in Optim Development Studio, I briefly mentioned our support for Visual Explain. Now, I will provide more details and walk you through a couple of use case scenarios for Oracle Visual Explain.
Visual Explain for Oracle is modeled similarly to the Visual Explains for other platforms - DB2 LUW, DB2 for z/OS and Informix. The simple use case scenario is one in which you gather explain information for a SQL statement (SELECT, INSERT, DELETE, UPDATE or MERGE). Let's take a look at gathering and showing explain information for the following SQL statement:
select * from scott.emp union all
select * from scott.emp;
If you were using SQL *Plus you could enter the commands and view the output as shown in Figure 1.
Figure 1.Output for the sample SQL from Oracle SQL *Plus
In Optim Development Studio, there are several launch points for Visual Explain for any particular SQL statement:
- The Project Explorer
- The SQL Editor window
- The Java editor when using pureQuery
Once launched, using a wizard, you set the options including the trace settings,which plan
table to use (defaulted to PLAN_TABLE), and the optimizer mode to use
(defaults to CHOOSE). The result is graphical output with nodes
representing the operations and a parent/child relationship. Hovering over a node you can see details such as the node type, the cardinality, cost, operation name and additional details on the operation (the diagram shows abbreviated node names). Right clicking on a node and then clicking on Show Description
brings up further details. For example, if you click on the TBFULL node, this brings up details on the table EMP, with its columns, indexes and additional catalog information as shown in Figure 2.
Figure 2. Details are available for any highlighted node in the Explain graph
There's another way to get explain information via running a background explain from the SQL outline view. I'll review this procedure since this is really a great tool to get cost information for SQL statements.
- As Sonali explained in her article and in associated video, you can get explain information (and performance hot spot visualization) for Oracle by capturing the SQL from any JDBC application.
- After the SQL statements in the application are captured and are available in the SQL outline view, you can get explain data on them by enabling Background Explain. Do this by right clicking on the project and selecting Properties and then going to pureQuery->Background Explain and clicking on the check box as shown below in Figure 3.
Figure 3. Enable background explain as a project property
- Once the explain is enabled, you can view the Explain data for the SQL statements as shown in Figure 4.
Figure 4. SQL outline view with explain information
This feature is extremely useful for developers who want to screen the
SQL statements they are writing in their application. Any SQL statement
that shows up doing a number of joins and has a high cost can be taken
to a DBA for further analysis. When you download the Optim Development Studio, let us know what you think of the Visual Explain capability as well as the other capabilities for Oracle such as PL/SQL support and other pureQuery support.
In my last blog I focused on tooling support for the new DB2 BLU Acceleration and rightfully so. BLU Acceleration is a game changer and tooling support makes it even easier to use. I also want to point out there were many enhancements to Data Studio beyond support for BLU Acceleration. A number of requests from the Data Studio user base led to one of these enhancements, checkpoint support in the script editor.
Checkpoint's provide significantly more control over the execution of scripts. It allows the user to, at any point, evaluate the script execution to that point and make decisions on how to proceed. A Data Studio user can set a "checkpoint" at one or more SQL statements. When the script is executing, it will stop before executing any statement with a checkpoint and keep the transaction open. Users may now, in the editor, alter the script, run one or more statements or restart the script from any point. Remember, transactions stays open until committed or the script editor is closed. This can result in locks being held for a long time. Also, remember to commit your work before closing the editor. Any uncommitted statements will be rolled back when the window is closed or Data Studio is shut down.
Let me show you how easy this is to use. First, set your run time options. I recommend selecting "Open a new connection and reuse it to run scripts" and "Stop" on error. This keeps the transaction open and even if there is no checkpoint set, the script will stop at and select any line that generates an error.
To set a checkpoint, right click at the space right before the desired line and select "Add Checkpoint".
Now run the script or use one of the new run options available through the run icon:
Run all statements
Run statements starting from the cursor location
Run selected statements
When Data Studio 4.1 is available, check this feature out. I am sure you will find it valuable.