Data Studio Update Part 1
31 January 2013
Now that Control Center has been discontinued as of DB2 v10.1, I wanted to ensure that everyone is getting the most out of the Optim Data Studio. Optim Data Studio is very useful for ALL flavors of DB2 including “LUW”, “Z” and “I” as well as Informix too! Even if you are a major command line buff it is useful, because you can have it generate the SQL or DDL by checking the various boxes for commands and have Data Studio generate the correct syntax for you and then you can copy where you like, potentially saving a lot of time. In this article I will briefly describe several of the features that I feel are most important like managing and building SQL scripts, query tuning, creating complex change scripts that preserve data, viewing ER diagrams of your databases, and the ability to easily view tables and other database objects. In future articles I will drill down into some of these items in more detail.
As I noted above Data Studio can be used for DB2 LUW, DB2/zOS, DB2/i and Informix. It also can be used on a number of non-IBM databases as well. However, not all features are supported for every platform. To see which features work with which platform see the Data Studio features by data server page.
Other Data Studio Articles
Part 3: View DB Objects and Attributes – View tables, columns, indexes and model diagram.
Part 4: Changing Database Objects – Generate changes that preserve data and objects
Data Studio Update Part 5: Query Building and Editing – Ways to create and edit queries in Data Studio.
Data Studio Update Part 6: Query Tuning for DB2 LUW and z/OS – Tune individual Queries.
Data Studio Update Part 7: Find and Tune Bad Queries from the Package Cache – Find bad queries in the package
What to install?
If you do not already have Data Studio or if you have a version older than version 3.1, then I highly recommend that you get the latest version of Data Studio. There is no charge to use it. In Version 3.1 the former Optim Database Administrator (aka DB2 Change Manager) that will create complex change scripts was added to Data Studio along with the features of the former Optim Development Studio. When you go to download the product you will see that there are two main options. I highly recommend downloading the “full client” that has everything that Data Studio offers and allows you to share with other IBM client components. The “administration client” takes somewhat less disk space, but also has limited functionality and I do not recommend it. Visit the Full Client Install page of the information center or Section 1.3 of the Getting Started with Data Studio for DB2 e-Book for install instructions. You will also notice the Data Studio Web Console download link. This is a separate web based tool that can be installed on your workstation or a server that can monitor the health of your databases and schedule jobs on them. You can read more about it in my article about Data Studio Web Console.
If you are not familiar with Eclipse based tools then Data Studio will take a little getting used to, but it is not hard. Like Control Center, you set up connections to your various databases and then you can do quite a few things, from browsing your objects like tables, indexes, tablespaces, etc. to executing queries and changing those objects. To do all of these things, Data Studio provides several views which are windows within the main Data Studio application. Some of these views appear when you open the application and others are shown when you want to do something, such generating the DDL for a table. Some views are called editors because they allow you to change something. An example of this is when you right click a table and choose to export data. In this case an editor window will open that allows you to choose all of the options for creating an export. Views are grouped together for useful sets of tasks that at are called perspectives. For a much better overview of how to use the tool, please read Section 1.4 in Chapter 1 of the Getting Started With Data Studio for DB2 e-Book.
Further you will need to get familiar with connecting to your databases and how to navigate through the various things that you can do. Please see chapter 2 of the Getting Started With Data Studio for DB2 e-Book for this information. In the rest of this article I will describe a number of the major features that will be of use to the former user of Control Center or the person just getting started with DB2 and wanting to do at least some database exploration and/or administration using DB2.
On a day to day basis it is useful just to connect to your databases and to be able to easily to look around at the objects and configurations in them. Data Studio is very handy for this. In my article I will mostly use the “Database Administration” perspective that includes a view called the Administration Explorer. This perspective contains the tools that allow you to view and modify instance level information and that lets you tell Data Studio to add or change objects like tables, tablespaces, indexes, etc. and to have Data Studio generate scripts for structural changes while preserving the data in any objects that had to be dropped and recreated. The Administration Explorer view shows the servers that you have defined and lets you drill down into the instances and then the databases within the instances. To open the Database Administration perspective, start Data Studio and choose the following options starting with the “Window” option from the list of choices shown across the top of the window:
Window | Open Perspective | Other …
And then choose the “Database Administration” perspective from the list presented to you.
Easily Scan Database Objects
For just looking around at objects within your database and making simple changes you can use “Data” perspective. This perspective has a view called the “Data Source Explorer”. It lists the databases that you have defined to Data Studio and lets you browse around to look at objects within them. It does not let you see or do much at the instance level. It has a different way of showing the data that you may like as compared the “Administration Explorer” in the Administration perspective.
In the Administration perspective using the Administration Explorer view, you can connect to any of the available databases by expanding the server and then the instance and then you can expand the database and see the various types of objects available including Table Spaces, Tables, Indexes, Roles, etc. Figure 1 provides an example of displaying the tables and then choosing one of the tables to view the properties of the table:
Figure 1. Viewing Column Properties
As you can see from this example I opened the server localhost, expanded the instance and database then clicked the Tables category under the database. This showed the list tables with other useful information in about those tables like row count and then I clicked the ORDER_DETAILS table which showed me the Properties tab in the lower part of the screen. I then clicked the Columns tab within the Properties view to show the columns of the table and their data type, length and other useful stuff. I could then have clicked the Privileges tab below that to see who has what authority on the table; I could click the Table Spaces Tab below Columns to see the tablespace that this table is in and could click other tabs to see other information. Then if I want to change any of things like putting the table into a different tablespace or adding, changing or dropping columns I could do that right in the Properties tab. I could continue changing or adding other objects like other tables, indexes, buffer pools and anything else that I wanted to do. Those activities would not actually make any changes to the database, but Data Studio would keep track of what I was changing. Finally I could have told Data Studio to generate a script to implement those changes and it is smart enough to know when an object can be altered and when it must be dropped and recreated. If the object needs to be dropped and recreated, it also generates the portions of the script to unload and reload the data (giving you choices of which utilities to use) and to recreate all dependent objects like privileges, indexes, views, etc. You can then choose to run that script or copy it and run it later yourself from a CLP or script.
Other Object Options
The browse and edit feature of Data Studio lets you click on a table and see the data that is in it in a nice tabular format similar to a spread sheet. This is very useful for reviewing and creating test data. From the list of tables as shown above in Figure 1 you merely right click on the table that interests you and either choose “Browse Data” or “Edit Data” and you will be shown the first 500 rows in the table. If you chose “edit” you can change the data. Further, when you right click on the table or other object displayed in the grid, you can run any utility allowed for it like RUNSTATS, EXPORT, LOAD, BACKUP and any others. You can also choose “Analyze Impact” and see all objects that are dependent on the object you chose. This is very useful when you are considering removing an object so that you can see what else would be impacted. There are lots of other things you can do from this point and I recommend that you experiment with Data Studio and try them.
Create Data Models
Another very nice feature is the ability to have Data Studio show you the physical data model (ER Diagram) of your database. It can be really handy to use this to easily visualize the relationships in your database. In Figure 2 I merely right clicked one of the Schemas called GOSALES and chose Show | Overview Diagram. Again you can use the properties tab at the bottom to changes the properties of the diagram. For example here I checked the “Show data types” box to have that information displayed. If you want to see more of the diagram, you can double-click the tab and see that view in full screen. To get back you just double click it again.
Create and Manage SQL
The next very useful aspect of Data Studio is in the area of creating, editing and managing SQL. In this area I much prefer Data Studio’s script building GUI to the Command Center that is part of Control Center. One of the most tedious aspects of writing SQL is finding and pasting in all of the column names and then getting them typed correctly with all of the commas and such in the right places. The SQL Query Builder makes this easy. You can just pick the tables you want by double clicking on them as shown in Figure 3 in the second view from the top. You can then check the column names in each of the tables and as you are doing so, all of this the SQL is added to the top pane. Further I was able to tell the tool how to join the tables just by dragging the columns from one table to the next. At any point I could have taken the SQL built to that point and started to edit it myself. In the figure shown below the only thing that I typed was the literal ‘35’ in the where clause. Finally I ran it by right clicking in the query pane and selecting Run SQL. The results are shown in the tab in the bottom right called “Result1”.
Figure 3. SQL Builder
Another really nice thing about the Query tool is the companion “SQL and XQUERY Editor”. With this feature you have a more free style SQL builder where it is more geared towards you typing in your SQL. In this query builder, any syntax errors or objects such as table names or column names that you typed incorrectly will be underlined in red so that you can easily see problems before even running the SQL. It also has a nice feature called content assist. With it, you can put your cursor somewhere in the query and then press CTRL-SPACE and a menu of choices will be presented that you can click to add. For example if you have completed the “FROM” clause in a query, you can now place the cursor after the SELECT key word and press ctrl-space and Data Studio will show you a pop-up with the list of possible column names and you just click one to choose it. You can just keep doing this until you have all of the columns that interest you. It also keeps a history of all of your query runs, so you can go back to a previous version anytime. There is a lot more to the query editor and query builder than what I showed here. I highly recommend that you review Chapter 5 in the Getting Started With Data Studio for DB2 e-Book for more information.
Finally in Figure 3 you will notice that there is view in the upper left corner called Data Project Explorer. This is where you can store your scripts and other objects. This is a great way to organize your scripts instead of just having them all in one big directory. You can easily create new projects by right clicking in the view and selecting New | Project | Other | Data Development Project and then giving them any name that you like. The project will be created with several default folders including one for SQL Scripts. So you can have several projects and divide your scripts by database, by task or anything that you like. It is quite easy to develop the scripts for one database and then point the database connection for the project to another database to run. Each of these projects has a folder on your hard drive with the corresponding name with all of the corresponding sub folders. So if you just want to grab a script, you don’t need to start Data Studio. All of these folders will be under the “Workspace” folder shown when Data Studio starts.
Stored Procedures UDFs and PL/SQL Packages
Data Studio also provides a great stored procedure builder that has the SQL editors built in and has the same content assist as the SQL editors for Java and SQL stored procedures. This is a great tool if don’t create procedures much and want a lot of help when you do. It also has a built in debugger to help test and find problems in your stored procedures. Please review Chapters 8 and 9 in the Getting Started With Data Studio for DB2 e-Book for more information on these topics.
Data Studio has good features for helping you tune your queries. These features include allowing you to generate explain plans from scripts that you have or by capturing SQL form the Package Cache on the database. The Package Cache is where DB2 holds its queries until the space is needed by other queries. It also will tell you if your statistics are up to date and if you your queries could benefit from collecting additional data by adding parameters to your existing RUNSTATS commands. Also if you have big, gnarly queries that are hard to read, you can tell Data Studio to format it for you. There are two main ways to enter the query tuning process. One is by right clicking in the query editor and choosing “Start Tuning” from the popup menu. Another way that is probably better if you want to grab queries out of the Package Cache is to select the “IBM Query Tuning” perspective (Window | Open Perspective | Other … | IBM Query Tuning) and start from there. For lots of great information about how to do this please see Chapter 7 in the Getting Started With Data Studio for DB2 e-Book
Generating change scripts
As I noted earlier in this article, you can tell Data Studio about changes you want to make to multiple database objects and it will build a script for you to implement those changes. It is intelligent enough to know what objects can be altered and which ones need to be dropped and recreated. For example, if you add a column in the middle of a table, the table must be dropped and recreated for that to take effect. This means that before dropping the table the data must be saved somewhere such as in an export file or shadow table. Data Studio is aware of these options and will allow you to select from a list. Further if you drop a table, then the script will be built to extract all existing privileges, foreign keys, indexes and other dependent objects and add lines to the scrip to put them back when the table is recreated. This saves a ton of effort and significantly reduces the chances of you forgetting the one thing that will cause the database to be unusable by the application after the change. Finally it will generate a script to undo all of the changes so that if the change fails in the middle, you can run the portion of the script to put the database back to the way it was before. Chapters 2-3 of the Getting Started With Data Studio for DB2 e-Book for all of the details about how to do this.
Think of the scenario of a months-long development project where you have been making various large and small changes to the test database through time. If you have been in this situation, you already know that it is very easy to forget about one little change that you made that is critical to the application that is being modified. To make this situation very easy, Data Studio can compare the test and production databases, give you a report of all of the differences and generate a script to run against production to make the structure look just like test, while preserving the production data. This can also be done for comparing a DDL file to an actual database too.
If you have stored procedures, functions or even queries that have business logic already built and you have a project that needs to begin using that logic, one easy way to provide this access is through a web service. A web service is easily called by a Java application on an application server with a web server like Tomcat or WAS. Data Studio can create the files needed by these types of applications to allow your queries or stored procedures to be called as a web service from this server. Further Data Studio can directly deploy the files to the web server and even configure the JDBC driver on it to connect to the DB2 database where the procedure or query lives. Read more about the web service builder in Chapter 10 of the Getting Started With Data Studio for DB2 e-Book
Another great resource for getting help with Data Studio is the Information Center. There are even more great things that Data Studio can do for you than listed in this article. If there are any that you particularly like that I did not mention here, please comment on this article in my Facebook Page or my db2Dean and Friends Community and help the extended community.