Data Studio Overview

24 March 2009

9 July 2009

 

In June 2009 most Data Studio tools were renamed “Optim” and some repackaging was done that changed some of the information on this page.  All of the changes to this document are in blue.  Please also see this DeveloperWorks link for a lot of other good information.

 

Since I wrote this article in 2009, significant changes and improvements have been added to Data Studio and this article is out of date.  Please see my new set of Data Studio articles starting with Data Studio Update Part 1.  Part 1 has links to the other parts. 

 

By now you may have heard about Data Studio and its various components that can help a DBA get more database work done in a shorter time.  If you have only been seeing it in dribs and drabs, then you are probably confused about it like it was until I took some recent training.  For anyone who is working with DB2 or Informix databases, knowing about Data Studio will become more important as time goes on, because it will be the platform from which many of our database and development tools will be launched and integrated.  By integrated I mean that each additional tool that you add such as Control-Center-like tools, database object change management tools, or data modeling tools will just be additional views and functions that get added to the same launch pad called Data Studio.  For example, if you have ever used Control Center you almost always started by expanding the left pane to drill down to the object such as “table” with which you wanted to work.  In Data Studio, all database related products will have a “view” called the Data Source Explorer.  Each new product will use the existing views and add additional views where you can do things that it allows.  Here is a comparison Control Center and Data Studio, both showing the tables in the SAMPLE database:

 

Control Center

Data Studio

 

Notice that I got to “Tables” from the “Data Source Explorer” view located on the left side of the Data Studio window.  At the risk of being really boring and repeating myself, all of the database related products will have the “Data Source Explorer” view along with their other views.  You should note that as of DB2 v9.7, the Control Center has been deprecated, meaning that it will no longer be enhanced and will go away in some later version.

 

To illustrate what I mean when I say that each new application will just add additional views, I have opened a physical data model view in this example of the Data Studio Administrator product (now called Optim Database Administrator).  It is the view with the Entity/Relationship diagram in the upper right:

 

 

 

As you can see by this screen shot, The Data Source Explorer view is still in the lower left corner with the new data model view of Optim Database Administrator in the upper right.  If you look closely at the title of the box, you can see that the title of the window ends with “IBM Data Studio Developer” even though the physical data model view shown is from the Optim Database Administrator application.  How could this be?  Well, this again just goes to show that even though you install different products they can all be linked together such that when you start one application you have access to all of them in one window.  You just start different functions that show different views. 

 

Why do I care?

You care because this is where all of the database related tools are headed, including Optim Database Administrator (formerly know as Change Management Expert and then Data Studio Administrator), InfoSphere Data Architect (formerly known as Rational Data Architect), High Performance Unload, Performance Expert and others.  You can think of Data Studio as your DBA toolkit or DBA Swiss Army Knife where you go to one place regardless of what function you need to perform.  You also care because these tools now work together very easily.  Let’s say you reverse engineered your database into a physical model as I did in the diagram above and you want to create a logical data model in InfoSphere Data Architect (IDA).  Assuming that you have installed IDA, you can now just click a menu option in this same window, telling it to transform your physical model into a logical model.  In this same window in a different view you could then add text descriptions of each entity, make changes to the logical model for your next project, transform the model back to a physical model, and finally tell Optim Database Administrator to generate a script to make the changes to your test database that also preserves your data.  Again you can do all this just by choosing the right sequence of commands in this integrated environment. 

 

You also care because if your developers are using IBM Rational development tools or other Eclipse based products, you can now share models and other objects among the different groups. For example, you can generate your physical model with Optim Database Administrator and then send it to your developers who can just import the model into his framework and view it as he wishes.  This can make your projects easier to manage.

 

I’ll begin this discussion with the free basic version of Data Studio because I highly recommend getting yourself a copy to try, but the greatest value of Data Studio is its ability to integrate the basic functions with the more powerful tools like Optim Database Administrator, High Performance Unload and InfoSphere Data Architect that I will also discuss. 

 

Data Studio

Good news!  The basic functions of Data Studio that include most of the functions of Control Center and Stored Procedure Builder are included in a free download.  You can now download just the free part (or Data Studio Base) and it is now just called Data Studio.  It comes in two flavors:  the IDE package and the stand-alone package.  The IDE package includes all free features and can be integrated with the other Eclipse based tools described in this article.  The “stand-alone package” is a smaller foot print and does not come with all of the Eclipse stuff that allows integration.  It also does not come with some of the development tools like the stored procedure builder and debugger.  Download either free package and trials of other tools at this link.

 

You can also download the trial version of one of our other products and get the free Data Studio as will as a trial version of the tool that you choose.  After thirty days the non-“base” stuff stops working but the base features keep on going.  For DBA’s I recommend downloading Optim Database Administrator.  You will just see the following nag each time you start the software, until you decide that you like the trial features so much that you call your buddy, db2Dean, and buy it.

 

 

Data Studio includes most of the functions of the Control Center including table, view, tablespace and other object administration; running utilities like runstats, backup, export and load; and doing health monitoring and administering Replication.  It pretty much does everything that Control center does now except HADR, WLM, DPF, storage management and copy/paste of Nicknames. It also is an improved version of stored procedure builder with an SQL and XQuery query editor and helps you build SQL and XQuery scripts.  It also helps you create SQL and Java routines and has debuggers for both types of routines.  In most places you can just right click on SQL statements to run Visual Explain to help you develop the most efficient SQL.  It also has an XML Schema Editor and helps you manage all of your various SQL scripts if you care to import them.  You should note that as of DB2 v9.7, the Control Center has been deprecated, meaning that it will no longer be enhanced and will go away in some later version.

 

One really cool thing that the base does is to create web services from a query or stored procedure.  So if you have some stored procedures that your legacy applications use to perform certain functions and you would like your developers to be able to consume those functions without rewriting them then this is the feature for you.  HA!  I said “consume” in the context of software!  I feel like such a Web 2.0 guy now instead of the stodgy old DBA that I am!

 

Along with the base you get the Data Studio Administration Console (DSAC) that is essentially a browser based version of Health Center that you can see from Control Center.  It is a great way to check the health of your database from any device with a browser.  On it you can see any alerts based on thresholds that you have set and it lets you view a 72 hour history of alerts.  The download in the link above includes documentation that you should read before installing it.  It also allows you to see a Q Replication dashboard if you are running that product.

 

I’ll now give a brief description of some of the related products.  As I said earlier the real power of Data Studio is in these additional tools.  In future articles I may explain some of these in more depth.

 

 

Optim Database Administrator

The primary thing that Optim Database Administrator (ODA) does for you is to generate scripts that preserve all database objects and data when you make a structural change to a DB2 database.  When you make one simple change to a table that requires the table to be dropped and recreated, you may have a lot of work to do to script the data export, load, capture and script the grants, dependent views, referential integrity, stored procedures, triggers, etc.  All this for one just one table is a lot.  This drudgery grows exponentially when you begin a project that requires several tables to be changed.  You can spend days scripting, all the time knowing that just one minor mistake can cause lost data or the cancellation of the production change on implementation night.  ODA can automate all of this for you.  For changes that you want to design and implement you can reverse engineer your database into ODA, make the changes graphically in its physical modeling tool, and then have ODA generate a script to implement all changes.  ODA can also compare two databases and generate a script to make one database look like the other preserving all data.  For example, let’s say your development project has been going for months with large and small changes being made to the test database the whole time.  You know that forgetting to make just one of those changes in production can cause the whole production implementation fail.  With ODA you can just compare the two databases just before going live and have it generate all of the scripts need to implement the changes in production while preserving all production data.  It has several other features that allow you to save time and make changes without errors. 

 

InfoSphere Data Architect

IDA is a logical and physical modeling tool.  It plays very well Data Studio Administrator.  By using them together, you can use IDA to make changes to your model and then use ODA to compare your model with a database and then create a script that makes all changes while saving all data, grants, constraints and other dependent objects.  I wrote about this product about a year ago, and you can see that description by clicking on the link at the beginning of this paragraph.

 

Optim Development Studio and Optim pureQuery Runtime

These tools were formerly known as Data Studio Developer and Data Studio pureQuery Runtime respectively.  It is always most effective to use these two tools together.  They are two sides of the same coin – that coin being one that saves you “coin”.  OK, that doesn’t make sense, but what I’m getting at is that these components are used to save you money by allowing your existing application SQL to consume fewer database server resources without changing your applications.  Some lucky customers who are using Java applications that connect to a DB2 database on z/OS can start saving MLC charges almost right away by using these components on their application servers.  This is done by automatically replacing the dynamic SQL issued by the application with static SQL.  Issuing static SQL to the database server, including z/OS DB2 database servers, is typically less expensive than dynamic SQL.  The explanation about how the SQL replacement is done is rather involved, so I’ll leave that to another article.  Using static SQL not only saves database server resources, but it also makes response time more predictable.  This feature can also be use to lock out “SQL Injection” where hackers can cause the application to issue harmful SQL. Finally this feature can be used to trace problem SQL that you find back to the issuing application. 

 

These tools in the Data Studio toolkit also save you time and money because they allow your Java and .NET developers to write good quality SQL without having to spend a lot of time learning the intricacies of SQL.  They can focus on writing that Java and .NET code.  In addition to the ease of SQL coding these products also allow the developers to convert their dynamic SQL into static SQL in the application saving more database server resources because static is usually more efficient. 

 

 

Other Data Studio Compatible Products

There are several other products that can now work with Data Studio or will be enabled at a later time.  Some of the current ones are:

 

 

HOME | Search