IBM Data Studio
Having powerful and productive command line tools to administer database servers is something indispensable for every experienced DBA. But what about newcomers?
Graphical user interface (GUI) tools are great for users who are just starting with any product — especially when it comes to database management systems. Usually, GUI tools are of great help even for the most skilled professionals — it’s just not possible to memorize all the commands you need!
IBM Data Studio is the new standard GUI tool for DB2 databases administration, management and development. Through an integrated and modular environment, Data Studio provides collaborative database development tools for DB2 for Linux, UNIX, and Windows, DB2 for z/OS, DB2 for i, Informix and other non-IBM database products, with support for several programming languages.
You can download Data Studio at no-charge, or can you can get it together with DB2 when downloading the product (by selecting Data Studio as well through use of Download Director). It is also possible to download Data Studio when installing DB2 through the DB2 installation launchpad. The version of Data Studio that is currently available (at the time this tutorial was written) is 3.1.1.
Figure 12 shows what the start-up panel for this version looks like.
Figure 12. The start-up panel for Data Studio 3.1.1
There are 3 different components to choose from when downloading IBM Data Studio (all are available at no-charge):
- IBM Data Studio Administration client
- IBM Data Studio Full client
- IBM Data Studio Web console
The Administration client is a lightweight tool for administering databases and meets most of the basic development needs for DB2 for Linux, UNIX, and Windows and DB2 for z/OS. The Full client expands the functionality of the administration client to support development of Java, SQL PL and PL/SQL routines, XML, and other technologies. You must decide which version (administration or full client) will better meet your needs. Both full and administration client components can be installed on Linux and Windows platforms.
Web console complements Data Studio by adding monitoring and job management, both of which can be accessed through a web browser (you’ll see more about this product later).
If you are already used to Eclipse-based applications, you'll very likely feel comfortable with the Data Studio interface. After spending some time with the tool, users coming from the DB2 Control Center will find Data Studio very intuitive and easy to use. Figure 13 shows the basic interface for IBM Data Studio.
Figure 13. The basic interface for IBM Data Studio
One key concept in Data Studio is that almost all functionality is accessed through context menus, which are presented whenever you right-click on the object you want to work with. To find out what options are available for any object, simply highlight that object and put your mouse’s right button to work!
When you first start Data Studio, you are presented with the Task Launcher view, which welcomes you to Data Studio and provides you with a list of some of the tasks you can perform. The Task Launcher view can be seen in Figure 14.
Figure 14. The Task Launcher
To connect to a database from the Task Launcher, select Administer, followed by Connect and browse a database. Then, when the Select Connection dialog is presented, click New. (You can do the same thing by choosing the Administration Explorer view and clicking the button New. Figure 15 depicts the menu items that must be selected to establish a new connection in this manner.)
Figure 15. Establishing a new database connection
When the New Connection dialog appears, select DB2 for Linux, UNIX and Windows in the left-most box (to connect to a DB2 for Linux, UNIX, and Windows database), and enter the appropriate information such as database name, host name, port, user ID, and password. Figure 16 shows a New Connection dialog whose input filed have been populated with this type of information.
Figure 16. The New Connection dialog
(Before you can connect to any database, the database must first be created; DB2 ships with a SAMPLE database that can be created at any time by executing the command db2sampl,or by making the appropriate selection from the First Steps utility.)
The Test Connection button on the New Connection dialog can be used to check the validity of the data entered; when this button is pressed an attempt is made to connect to the database specified using the credentials and connectivity information provided.
It is important to note that you can use most of Data Studio’s functionality without having to install a DB2 client/driver package. That’s because Data Studio comes equipped with drivers that offer connectivity to several database products (usually through JDBC). Therefore, if you plan to administer DB2 remotely, or you plan to develop using a remote database server, having only IBM Data Studio installed locally should be enough.
Now that you know how to establish a database connection in Data Studio, let's take a look at Data Studio’s interface.
As an Eclipse-based application, IBM Data Studio makes it possible for you to choose between different perspectives, making its use more directed to what you intend to do. For example, when you choose the Database administration perspective, Data Studio’s interface changes to show you the view elements that are relevant to database administration like the Administration Explorer, the Job Manager, and so on. Figure 17 illustrates how you can select the Database Administration, Data and IBM Query Tuning perspectives.
Figure 17. Changing between perspectives
Perspectives, as the name implies, influence only the way in which you see what you are doing in Data Studio: when you switch between perspectives, nothing is lost — only the way in which you view what you have done is affected. A variety of perspectives are available. Figure 18 shows all the perspectives that exist in Data Studio full client.
Figure 18. The different perspectives that are available with Data Studio full client
Now, let’s turn our attention to some of the basic functionality that’s available with Data Studio.
Usually, the one thing users want to see in a database management tool is the ability to examine a database’s tables and views.
To view the tables within a database you can navigate through Administration Explorer view (under Database Administration perspective) and after
successfully connecting to a database, the Tables folder will be shown in the tree that’s presented on the left-hand side of the screen.
By clicking the Tables folder, you will be able to get a list of all the tables available in the Editors area.
Accessing tables through the Data perspective has a different path: you must go through the Data Source Explorer view and open the Schemas folder to obtain a list of the tables that have been defined within the schema chosen.
Figure 19 shows how to access tables in the SAMPLE database using the Database Administration perspective.
Figure 19. Accessing tables with the Database Administration perspective
As you can see, when using the Database Administration perspective, every folder in the Administration Explorer view shows you all existing objects of a particular type (for example, all tables, all schemas, all sequences, and so forth). But when you switch to the Data perspective, navigation follows a top-down hierarchy. Within a database, you can see the schemas, and inside each schemas, you can see tables, views, sequences and so on that have been defined within that schema). You get to choose the way you want to navigate.
Right-clicking on a table will show the context menu with all the options that can be performed on that table. And by using this context menu, you can operate the table specified as you wish.
Data Studio also has a diagram editor, which can be used to better visualize a database’s objects and the relationships between them. Figure 20 shows you the menu items that must be selected to invoke the diagram editor.
Figure 20. Invoking the diagram editor
You’ll be prompted to choose additional tables as you wish, and then Data Studio will present the tables you have selected, arranged in a diagram as requested. Figure 21 illustrates how a diagram produced by the diagram editor might look.
Figure 21. The diagram editor interface
Using a diagram like the one produced by the diagram editor makes it is easier to understand structures and object relationships.
Using context menus, it’s also possible to browse and edit data. (Simply right
click on a selected a table and choose the Browse Data or
Edit Data menu option).
You can edit data in line and when you save, Data Studio will show you the SQL statement(s) that were used to make the changes.
Data Studio highlights changes in yellow; Figure 22 illustrates an example where the data for a row and column has been changed. Figure 23 shows the SQL that was used to make the change.
Figure 22. How modified values are presented
Figure 23. The SQL that was used to make the change shown in Figure 22
Data Studio assists you when writing SQL or XQuery queries. To create a new script (that is, a script that does not contain any statements),
simply highlight a database object, right-click on it to bring up a context menu, and
select the New SQL Script option.
(The same thing can be done by right-clicking on an SQL Scripts folder in any project
you select under Data Project Explorer —
the context menu will be shown as "New SQL or XQuery Script").
All SQL or XQuery scripts you create will be shown on the Data Project Explorer, within the project you chose, when it is saved.
As with other Integrated Development Environments (IDEs), Data Studio can assist you as you enter queries in a script, by auto-completing object names. This functionality is provided via the Content Assist context menu item. Figure 24 illustrates how this menu item looks.
Figure 24. Using Content Assist
Data Studio can also help you format any unformatted SQL. Figure 25 illustrates how the "Format SQL" menu item can be used to apply the proper formatting to an unformatted SELECT statement.
Figure 25. Using Format SQL
There’s also the Content Tip function, which assists you with syntax tips as you write your scripts. Figure 26 illustrates how Content Tips are presented.
Figure 26. Content Tips
Once you are done and are ready to submit your script/query to DB2 for execution, you simply just press the Run SQL button (the green circle with a triangle in it that looks like a Play button). This can also be done by pressing the F5 key. And if your script contains multiple queries, you can execute an individual query by selecting just the text for the query that you want executed, before pressing the Run SQL button or the F5 key. Figure 27 illustrates how the Run SQL button looks (on the Data Studio tool bar).
Figure 27. The Run SQL button
With this toolbar, it is also possible to show the access plan for a query (Open Visual Explain button), tune a query (Start tuning button), import or export the SQL script (In-Box and Out-Box buttons), or schedule a query to be executed later (Clock button).
When you execute queries, Data Studio shows the results in either text form or in a grid. Figure 28 illustrates how query results are returned in a grid; Figure 29 shows query results being returned as text.
Figure 28. Query results shown in grid mode
Figure 29. Query results shown in text mode
In a perfect world, DB2 would always return the results for a particular query “in the blink of an eye." But in the real world, you are required to create appropriate indexes and materialized query tables (MQTs), keep statistics up to date, and eliminate any fragmentation in your data (through reorgs) before DB2 can deliver the best query response times possible. The DB2 Optimizer does an exceptional job in choosing the access plans that are used to retrieve the data that is needed to resolve a query (and is being continually improved as new versions are released), but there are times when some queries simply do not behave as expected. For these types of queries, Data Studio can help you identify problems and solve performance issues by advising on the appropriate action(s) to take.
Data Studio offers basic tuning features (Statistics advisor, Query formatting, Access Plan graph and Reports) free of charge and many of these features can be extended with the purchase of InfoSphere Optim Query Tuner for Linux, UNIX and Windows.
As with most of the Data Studio functionality, it is possible for you to invoke the query tuner from many places. One possible way of calling it is through the SQL script editor (by pressing the Start tuning graphical button.) After pressing this button and interacting with the query tuner assistant, you should get a screen like the one that’s shown in Figure 30.
Figure 30. The Query Tuner Workflow Assistant
If you look closely at Figure 30, you will see a data access plan that is being displayed for a particular query. By analyzing access plans, you can determine whether or not a query is using indexes efficiently. Data Studio can also suggest running statistics on tables that a query frequently accesses. You’ll have more tuning alternatives available when you use InfoSphere Optim Query Workload Tuner (paid-separately), or alternatively, the DB2 Advisor, which can be invoked by executing the db2advis command.
Earlier, we saw how to switch to the Database Administration perspective to facilitate database administration work inside Data Studio. After changing to this perspective, you’ll be presented with the Administration Explorer view.
Inside the Administration Explorer view, there are a lot of objects shown that you can choose to operate with. It is possible to change objects such as tables, columns, indexes, and for every change you make, Data Studio creates a change plan that analyses impacts and can be used to implement your changes — or schedule them to be done later. Change plans are saved in the “Change Plans" folder on the Administration Explorer view. And objects that have a pending change plan have their icons changed to a delta sign (like a triangle) to visually indicate that they have changes that are pending.
Figure 31 illustrates how to change a column in a table, and the menu items that must be selected to review the resulting change plan that gets generated.
Figure 31. Altering a table's structure, and reviewing it through a Change Plan
As you can see in Figure 31, there’s also an option called Review Undo Script which you can use to revert all changes, if necessary. When the Review and Deploy menu item is selected, the change plan that was generated will be displayed in the Review and Deploy dialog and you will be given the opportunity to run the plan immediately or schedule it to be executed at a later date and time. Figure 32 shows what this dialog looks like.
Figure 32. The Review and Deploy dialog
If you elect to run the change plan right away, the results of the execution will be displayed in the SQL Results window. Figure 33 shows what this window looks like.
Figure 33. The SQL Results window after changes to an object have been made
To execute administrative tasks, Data Studio connects to the database server specified using Secure Shell (SSH). DB2 10.1 includes a lightweight SSH server with Windows installs; on Linux and UNIX servers the SSH server that is available with the operating system is used instead. When connecting to older versions of DB2 on Windows, Data Studio can also make use of the Database Administration Server (DAS) if there’s no SSH support available. (DB2 8.2 and earlier only uses DAS; the DAS was deprecated in DB2 9.7 and may be discontinued in a later version.)
Data Studio web console is available as a separate download, and can be used to monitor database health and availability, as well as manage scheduled jobs through a web browser. Data Studio web console contains a web server which can be accessed from anywhere — any device that has a web browser and can connect to the web console can be used to monitor a DB2 database. Thus, you can keep an eye on a database using a smartphone or tablet — you’re not limited to having to use a computer. Data Studio web console is available on Linux, AIX, HP-UX, Solaris, and Windows platforms. Figure 34 shows what the opening screen of the Data Studio web console looks like. Figure 35 shows how the Data Studio web console can be used to monitor the health of a database.
Figure 34. The Data Studio web console
Figure 35. The Health Summary panel of the Data Studio web console