Migrating from DB2 Control Center to IBM Data Studio

A comprehensive guide for DB2 Control Center users to learn and start using IBM Data Studio

Since the IBM® DB2® Control Center tools have been deprecated in DB2 9.7, users have been encouraged to adopt IBM Data Studio and IBM InfoSphere® Optim™ tools for managing DB2 databases. IBM Data Studio 3.1 was released in October 2011 with several enhancements over previous versions, making it an excellent replacement for Control Center. This article aims to help DB2 users who are familiar with Control Center tools to quickly transition to IBM Data Studio.

Antonio Maranhao (amaranha@ca.ibm.com), Manager, IM Technology Ecosystem, IBM China

Antonio MaranhaoAntonio Maranhao works in the IBM Toronto Lab where he manages a team of technical specialists responsible for training IBM's business partners and clients on DB2 for LUW. He is a passionate DB2 advocate and has delivered several DB2 trainings to audiences worldwide. He has participated and coordinated the development of several DB2 training courses, articles and certifications. Being a certified DB2 database administrator with more than five years of experience, he is very familiar with DB2 tools and has extended his skills to Optim database tools.



Kevin Jiang (jiangk@ca.ibm.com), Technical Enablement Specialist, IM Technology Ecosystem, IBM China

Kevin jiangKevin Jiang is a technical enablement specialist on the Information Management Technology Ecosystem team in IBM. In addition to developing and managing critical software infrastructure for the team, he has travelled worldwide to attend conferences on latest information technologies, and to train IBM business partners and clients about DB2. As a certified DB2 application developer and database administrator, he has developed a number of DB2 education materials.



19 January 2012

Also available in Chinese Vietnamese

Introduction

The DB2 Control Center has been around since Version 5 of DB2 for Linux®, UNIX®, and Windows®. With the release of DB2 9.7 in 2009, IBM announced that the Control Center tools were officially deprecated. Since then, users have been encouraged to adopt IBM Data Studio and IBM InfoSphere Optim tools for managing DB2 databases.

IBM Data Studio 3.1 was released in October 2011 with significant enhancements over previous versions that not only make it an excellent replacement for Control Center but also offers a variety of extra features in a modern GUI. Even better, IBM Data Studio is a fully licensed product available at no charge.

This article aims to help DB2 users transition from Control Center to Data Studio. It guides you on how to use Data Studio to perform the same administration tasks available in the Control Center tools and more.


Data Studio quick overview

Data Studio is part of the IBM InfoSphere Optim family of data lifecycle management tools and solutions. Together, these tools provide an integrated environment for managing databases and developing database applications throughout the data and application life cycle.

For example, InfoSphere Data Architect provides a robust data modeling solution that integrates seamlessly with Data Studio. Use InfoSphere Optim Query Workload Tuner to expand on the basic query-tuning features in Data Studio with seamless integration. Data Studio users can also take advantage of InfoSphere Optim Performance Manager to help ensure that database applications are built from the ground up with performance in mind.

The following table shows the mapping between the DB2 deprecated tools and the InfoSphere Optim tools. Please note that Replication Center has not been deprecated and is still the recommended tool to set up replication on DB2 databases.

Table 1. Mapping DB2 deprecated tools to InfoSphere Optim database tools
Control Center ToolInfoSphere Optim Tool
Control Center and Wizards
Command Editor
Visual Explain
User Interface to Spatial Extender
IBM Data Studio
Task CenterIBM Data Studio with Data Studio Web Console
Health CenterIBM Data Studio with Data Studio Web Console
Optim Performance Manager
Memory Visualizer
Query Patroller Center
Activity Monitor
Event Analyzer
Optim Performance Manager

See Resources for more information on recommended mappings between InfoSphere Optim tools and deprecated Control Center tools.

So what is Data Studio, really?

Data Studio is a comprehensive data management tool. You can use it to design and develop database applications, administer and tune databases, and monitor database health. It supports multiple database servers, such as DB2 for Linux®, UNIX®, and Windows® (LUW); DB2 for z/OS®; Informix®; Sybase; MySQL; Oracle; SQL Server; and others. It can be installed on Red Hat® Linux, SUSE Linux, Windows, and AIX (limited to Data Studio web console component) operating systems.

Data Studio comprises three components: full client, administration client, and web console. The administration client is a lightweight tool for administering databases and to meet most of the basic development needs for DB2 for LUW 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 editor, and other technologies.

For monitoring database health and availability, and managing scheduled jobs, use the Data Studio web console, available as a separate download, also at no charge.

Data Studio 3.1 consolidates the functionality of Optim Development Studio, Optim Database Administrator, and previous versions of IBM Data Studio into a single product. This way, Data Studio offers improved usability for database administration, and it supports most functionality found in Control Center. As a bonus, Data Studio includes enhanced Java database development capabilities as was previously provided by Optim Development Studio pureQuery™ support.

Figure 1. IBM Data Studio 3.1
Image shows that IBM Data Studio 3.1 consolidates the functionality of Optim Development Studio, Optim Database Administrator, and previous versions of IBM Data Studio into a single product

See Resources for a list of all Data Studio 3.1 features by data server and component.


If you compare Control Center's interface layout with the Database Administration perspective in Data Studio, you will find them quite similar.

Figure 2. Comparing the Control Center and Data Studio interfaces
Image shows that Data Studio and Control Center interface layouts are quite similar
Table 2. Comparison of the three main screen areas of Control Center and Data Studio
Control CenterData StudioPurpose
Object TreeAdministration Explorer viewConnect to databases and navigate through different database object types. Data Studio includes support for DB2 pureScale hosts.
Content paneObject List areaLists database objects. In Data Studio, several tabs can be open simultaneously for different purposes.
Content Details paneProperties viewAfter selecting an object, its properties can be seen in this area. Again, Data Studio can have multiple tabs in this area for different purposes.

Figure 3 provides a more detailed view of the Data Studio interface, along with its three main areas: the Administration Explorer; the Object List area; and the bottom panel, where object properties and the result of SQL execution can be seen.

Figure 3. The IBM Data Studio interface
Screen capture shows IBM Data Studio interface

Connecting to databases

Database connections are established using JDBC in Data Studio, in contrast to Control Center's cataloging of databases on local DB2 client's database directory.

If DB2 databases have already been catalogued on the machine where Data Studio is installed, the database connection profiles automatically get created within Data Studio when it is launched. Users are still required to provide login credentials to establish a connection.

To add a database connection manually, click New > New Connection to a database in the Administration Explorer.

Figure 4. Creating a database connection
Image shows creating a database connection

A dialog appears, allowing users to configure the connection parameters such as database manager, JDBC driver, host, port, and login credentials.

Figure 5. New database connection dialog window
New database connection dialog window

After the connection object is created, the database icon changes to reflect the successfully established connection.

Figure 6. Icons for database connection status
Icons for database connection status

If disconnected, right-click the database entry and select Connect.

Just like Control Center, Administration Explorer provides an object tree for browsing database objects. The objects are grouped into folders based on type, such as buffer pools, tables, indexes, views, materialized query tables (MQTs), federated objects, partition groups, etc.

In Control Center, when you click on a database object type such as tables in the Object Tree, a list of those objects is displayed in the Content Pane. On selecting a particular table, its details such as columns definition would be displayed automatically in the Content Details pane. This navigation behavior remains the same in Data Studio, where the list of objects appears in the Object List area, and the object properties are displayed under the Properties tab in the bottom pane.


Running administrative tasks with task assistants

Now that you have connected to a database, you can start managing DB2 instances and databases.

In addition to regular DB2 instances management, DB2 pureScale and partitioned database environment instances can be managed from the Administration Explorer as well. When a DB2 pureScale database is expanded in Administration Explorer, a DB2 pureScale Members and CFs folder will be listed, in addition to the other standard folders.

Control Center provides a series of tools, wizards, and advisers to carry out administrative tasks, such as the Configuration Assistant, which is an important tool for DBAs to configure instance and database parameters. This functionality is also available in Data Studio.

In the Administration Explorer, right-click on an object for a context-based menu of tasks. If you then select a particular task, a dialog would appear in an editor tab to help you execute the task you want, these are called task assistants in Data Studio.

Below is an example of a context-based tasks menu for instances:

Figure 7. Context-based menu for instances
Image shows context-based menu for DB2 instances

Another example is the context-based tasks menu for databases (Figure 8).

Figure 8. Context-based menu for databases
Image shows context-based menu for databases

Task assistants work similarly to the wizards and advisers in Control Center. Since task assistants appear in editors, multiple task assistants can be open simultaneously, while you can only have one active wizard open at a time in Control Center. This means you can navigate to other parts of Data Studio and return to the task assistants you opened earlier.

A task assistant dialog consists of four sections: Connection, Settings, Command, and Messages (Figure 9):

  1. Connection shows database connection details.
  2. Settings specifies options for the database commands to be run.
  3. Command displays the generated commands for the selected settings. Users can edit these generated commands before execution or save them for later use.
  4. Messages shows the status of executing the commands.
Figure 9. Database backup task assistant
Image shows database backup task assistant

In a partitioned database environment, task assistants facilitate administration with the ability to run commands against all of the partitions, one or more specific partitions, or partition groups. Task assistants also can execute commands against partitions in parallel, which is particularly useful for long-running commands.

To administer a DB2 pureScale environment, task assistants provide additional functionality to start, stop, quiesce and configure members, and cluster caching facilities (CFs).

Table 3 lists Control Center wizards along with the menu path to access the corresponding task assistant in Data Studio.

Table 3. Mapping Control Center wizards to Data Studio task assistants
Control Center WizardLocation of Task Assistant on Data Studio
Backup Wizard[Database] > Back up and Restore > Back up...
Restore Data Wizard[Database] > Back up and Restore > Restore...
Configuration Advisor[Database] > Set up and Configure > Configure...
Configure Automatic Maintenance[Database] > Set up and Configure > Configure Automatic Maintenance...
Configure Database Logging Wizard[Database] > Set up and Configure > Configure Database Logging...
Set up High Availability Disaster Recovery (HADR) Databases[Database] > Set up and Configure > Set up HADR...
Create Database Wizard
Create Database with Automatic Maintenance
[Instance] > New Database...
Load Wizard[Table] > Load > With Load Utility...
Design Advisor[SQL Editor] > Start Tuning...
Create Table Space WizardTable Spaces folder > Create System Temp Table Space
Table Spaces folder > Create User Temp Table Space
Table Spaces folder > Create Large Table Space
Table Spaces folder > Create Regular Table Space
Create Table Wizard"Tables" folder > Create Table

See Resources for more details on administering databases with task assistants, including DB2 pureScale and partitioned database environments.


Working with database objects

Browsing objects

As mentioned, the Object List area is for displaying or editing content, such as lists of tables or records inside a table. When browsing database objects, you will notice a navigation bar at the top of the list that serves multiple purposes (Figure 10 and Figure 11).

Figure 10. Browsing database objects with the Object List
Image shows browsing database objects with the Object List
Figure 11. Object List navigation bar
Image shows Object List navigation bar

The navigation bar provides:

  • Web-browser-like back/forward navigation.
  • Breadcrumb navigation that facilitates moving through the hierarchy of objects. Clicking the arrow icon between objects will display all objects at that level of hierarchy.
  • A button for creating an object based on the current context of the view.
  • In-place search function. Type the object name or part of it, and the list of objects is dynamically updated. Wild-card characters are supported.
  • Options to sort, filter, and refresh the list.

Database object management

Similar to Control Center, managing database objects in Data Studio can be initiated from the right-click context menu. Some common options are standard to all objects, such as Alter, Drop, Copy, Generate DDL, and Properties. Depending on the type of the object, more options are available on the context menu, such as Reorg Table (tables), Update Statistics (MQTs), Back Up (table spaces), and many more.

Data Studio supports management of the following types of objects for DB2 for LUW databases: aliases, packages, PL/SQL packages, stored procedures, user-defined functions, user-defined types (row, array, distinct, and structured), buffer pools, constraints (foreign key, unique, and check constraint), federated stored procedures, nicknames, remote servers, wrappers, user mappings, indexes, materialized query tables, partition groups, pureScale members and CFs, schemas, sequences, tables, table spaces, triggers, user groups, roles, users, views, XML schemas.

Editing table data

The Object List area can also be used to edit records in a table. Users can simply right-click on a table in the Object List area and select Edit Data on the context menu.

When editing data in the query result table in Control Center, pending changes made to individual rows would hold a lock on the data. In Data Studio, changes are local to the client machines until they are committed to avoid unnecessary locks to the data. In addition, Data Studio highlights any pending changes (Figure 12).

Figure 12. Editing table data
Image shows editing table data

Click the Commit data changes icon or press Ctrl+S to commit data changes. Results are displayed in the SQL Results view at the bottom panel (Figure 13).

Figure 13. Results of updated data
Image shows results of updated data

Managing changes to database objects

Data Studio provides a more comprehensive way to deal with changes made to database objects compared with Control Center.

Changing database objects in DB2 for z/OS

Database objects management for DB2 for z/OS works in a similar fashion, except that only one object is managed at a time, and changes are deployed from the Properties view.

In Data Studio, all changes made to a database are automatically saved in a change plan in the local workspace. A database can have multiple change plans, but only one plan can be active at a time, which means that changes are added to the current active plan. A change plan remains active until the user deploys the changes to the target database, closes the plan to work on it later or makes another plan the active plan.

When a change plan is active, the Object List area includes an additional toolbar for the plan. The list of change plans for the database can be found within the Change Plans folder in the Administration Explorer. To demonstrate the use of change plans, we will use the scenario of altering a table:

  1. To alter a table definition, users can simply right-click on a table and select Alter on the context menu.
    Figure 14. Accessing the Alter Table option
    Image shows accessing the Alter Table option
  1. Modifications can be made directly from the Properties panel. In this example, we want to add a new column named LOCATION to table EMPLOYEE in the SAMPLE database.
    Figure 15. Altering a table
    Altering a table in Properties view

When the new column is added, an additional toolbar for the change plan is automatically added to the Object List area as shown on Figure 16. The timestamp of that which the plan is created is attached to the plan name on the toolbar by default. Objects that have pending changes are highlighted by an icon resembling the Greek letter delta. A number of options are available for users to work on change plans, such as view object change, move to next/previous change, deploy, save, and cancel.

Figure 16. Change plan toolbar in Object List area
Image shows change plan toolbar in Object List area

A pending change plan can be compared with the original object's definition from the context menu. This feature is useful in many cases, such as deploying or synchronizing schemas across different databases. Additional options when comparing objects include generating difference report, impact analysis, filter configuration, etc. (Figure 17).

Figure 17. Comparing objects
Screenshot shows object comparison

After all object changes are completed, you can review and deploy these changes by clicking Review and Deploy Changes. A set of automatically generated commands will be listed. Users can edit these commands, run them immediately, or schedule as a job for later execution (Figure 18).

If you click on Advanced Options, you will have the option to choose the maintenance related commands you would want to include in the script, such as RUNSTATS commands.

Figure 18. Review and deploy changes
Screenshot shows review and deploy changes dialog

Changing security privileges

To manage privileges, users can select Manage Privileges from the context menu of a database object. As when altering an object definition, privileges can be directly edited using the Properties tab, which would be open by default. Privileges can be granted or revoked by checking individual cells in the table, and two checkmarks in the same cell means that the corresponding user has WITH GRANT OPTION on that privilege. (Figure 19)

Figure 19. Managing privileges
Image shows managing privileges in Properties view

Working with the SQL Editor

In Control Center, Command Editor is the tool designed for executing SQL statements. In Data Studio, we use the SQL Script Editor, which also offers development and debugging capabilities for database routines (user-defined functions, triggers, and stored procedures). Better than Command Editor, the SQL Script Editor provides syntax highlighting, content assist, and several other well-integrated tools to facilitate database application development.

To launch the editor, go to Administration Explorer and click New > New SQL Script (Figure 20).

Figure 20. Launching the SQL Editor
Image shows launching the SQL Editor

Figure 21 shows an example of the SQL Script Editor. You can see that as the query is typed, the content assistant can be invoked to display a list of valid options to complete your SQL statement.

Figure 21. SQL Script Editor
Image shows SQL Script Editor

In the SQL Script Editor, an integrated toolbar on the top provides convenient links to tools, such as:

  • Visual Explain
  • Tuning
  • Import/export
  • Job Manager (equivalent to Task Center in Control Center)

There are four tabs at the upper half of the editor to allow users to configure how the SQL statements should be executed:

  • Configuration to specify execution environment, commit control, error control, etc.
  • Validation to validate syntax against a specific data server, set statement terminator, etc.
  • Special Registers to specify current schema and current path.
  • Performance Metrics to specify an Optim Performance Manager profile for gathering performance metrics.

Click Run SQL to execute your SQL statements, similar to using the Ctrl+Enter shortcut in Control Center, you can press F5 on your keyboard to execute the script in Data Studio.

In Control Center, execution results are displayed in a tab called Queries Results inside Command Editor. In Data Studio, results are displayed in the SQL Results tab at the lower panel in your Data Studio workspace. Execution time, execution results, and past execution history are also available in the SQL Results tab (Figure 22).

Figure 22. SQL Results view
SQL Results view

In addition, Data Studio provides a few more options to allow users to continue working on SQL results, such as display results in graphical (Figure 23) or text mode (Figure 24), export results into different formats, filter the results, etc. (Figure 25).

Figure 23. SQL results in graphical mode
Image shows SQL results in graphical mode
Figure 24. SQL results in text mode
Image shows SQL results in text mode
Figure 25. Options in SQL results view
Image shows options in SQL results view

See Resources for more information on developing SQL and XQuery scripts in Data Studio.

Additional features of the SQL editor

Some useful keyboard shortcuts to use inside the SQL editor:

  • Ctrl+Space— Content assist
  • Ctrl+Shift+Space— Content tip (e.g., tip on SQL syntax)
  • Ctrl+Shift+F— Format SQL (i.e., automatically format and indent SQL statements for easier reading, especially useful for long and complex statements)
  • Ctrl+/— Quickly toggle comment/uncomment of selected statement(s)

SQL scripts can be further refined using the options on the integrated toolbar (Figure 21).

To get a quick glimpse of the Visual Explain graph for your SQL script, click the Open Visual Explain icon in the toolbar. For further analysis on fine-tuning your script, the IBM Query Tuner should be used. It can be invoked with the Start Tuning icon in the toolbar.

IBM Query Tuner provides a set of tuning tools for better understanding queries visually, and it can generate detailed statistics reports. See Resources for more information on how to tune SQL statements with Data Studio and IBM Query Tuner.

Figure 26 shows a sample analysis of an SQL statement.

Figure 26. Visual Explain
Image shows Visual Explain

Figure 27 shows a sample Query Tuner report that provides detailed information on the analyzed SQL statement and options for tuning its execution.

Figure 27. IBM Query Tuner analysis report
Image shows IBM Query Tuner analysis report

You can save your SQL scripts and graphs for later use by saving them into projects. In Data Studio, a project is a collection of artifacts such as SQL scripts, database models, etc. To save your scripts, from Data Studio's top menu bar select File > Save As, then choose the project where the files will be saved.

Figure 28. Saving a project
Image shows saving a project

Monitoring databases with Data Studio Web Console

Control Center provides users with Health Center, Task Center, and Journal to monitor database health and availability, and to manage jobs on DB2 databases.

Data Studio offers the same functionality through the Data Studio web console component, which is a separate component of Data Studio, also free to download and use. You can access the web console interface from inside Data Studio by right-clicking on a database connection (in the Administration Explorer) and selecting one of the Monitor options available.

There are two main components in the Data Studio web console: the job manager and the health monitor.

Using job manager, you can create, manage, and schedule jobs for DB2 for LUW and DB2 for z/OS databases. Figure 29 shows the job manager interface where four tabs are available:

  • Job List— Create jobs for your databases or run existing jobs directly against a database without scheduling.
  • Schedules— Create and manage schedules for jobs.
  • Notifications— Manage email notifications for the execution of jobs.
  • History— Display the status of jobs.
Figure 29. List of scheduled jobs
Image shows list of scheduled jobs

With the Health Monitor, you can view database health information at several levels of detail: from a summary view of the overall health and availability of all your databases to the details of a particular problem on a specific database. In addition to regular DB2 databases, monitoring for DB2 pureScale members has been added in Data Studio V3.1.

Figure 30. The Health Monitor interface
Image shows Health Monitor interface

Conclusion

With the deprecation of Control Center tools in DB2 V9.7, users have been encouraged to start using IBM Data Studio as a replacement. This article introduced Data Studio's capabilities and also related them to their counterpart in Control Center. This approach makes it easier for Control Center users to transition to Data Studio, as the article points out how to use Data Studio to perform the same functions users can find in Control Center. Moreover, the article explores the areas where Data Studio provides extra capabilities over Control Center.

Resources

Learn

Get products and technologies

  • Build your next development project with IBM trial software, available for download directly from developerWorks.
  • Now you can use DB2 for free. Download DB2 Express-C, a no-charge version of DB2 Express Edition for the community that offers the same core data features as DB2 Express Edition and provides a solid base to build and deploy applications.

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=788358
ArticleTitle=Migrating from DB2 Control Center to IBM Data Studio
publish-date=01192012