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.
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 Tool | InfoSphere Optim Tool |
|---|---|
| Control Center and Wizards Command Editor Visual Explain User Interface to Spatial Extender | IBM Data Studio |
| Task Center | IBM Data Studio with Data Studio Web Console |
| Health Center | IBM 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
See Resources for a list of all Data Studio 3.1 features by data server and component.
Navigating the Data Studio interface
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
Table 2. Comparison of the three main screen areas of Control Center and Data Studio
| Control Center | Data Studio | Purpose |
|---|---|---|
| Object Tree | Administration Explorer view | Connect to databases and navigate through different database object types. Data Studio includes support for DB2 pureScale hosts. |
| Content pane | Object List area | Lists database objects. In Data Studio, several tabs can be open simultaneously for different purposes. |
| Content Details pane | Properties view | After 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
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
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
After the connection object is created, the database icon changes to reflect the successfully established connection.
Figure 6. 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
Another example is the context-based tasks menu for databases (Figure 8).
Figure 8. 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):
- Connection shows database connection details.
- Settings specifies options for the database commands to be run.
- Command displays the generated commands for the selected settings. Users can edit these generated commands before execution or save them for later use.
- Messages shows the status of executing the commands.
Figure 9. 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 Wizard | Location 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 Wizard | Table 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
Figure 11. 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 Dataon 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
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
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:
- 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
- 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
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
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
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
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
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
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
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
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
Figure 24. SQL results in text mode
Figure 25. 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. SeeResources 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
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
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
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
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
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
- Read Getting started with IBM Data Studio for DB2.
- The Data Studio V3.1 information roadmap provides several resources grouped by categories.
- Visit the Data Studio V3.1 Information Center and Data Studio web console Information Centerfor details on functions available on these tools.
- Learn more about Administering databases with task assistants.
- The DB2 V9.7 Information Center provides recommended tools to use in place of the deprecated Control Center tools.
- Explore Data Studio V3.1 features by data server and component.
- Read "Managing database connections with the IBM Data Studio web console" to learn how to manage and share database connections with the IBM Data Studio web console.
- Read "Creating scripts more efficiently in the SQL and XQuery editor."
- Improve performance by using Data Studio's capabilities for tuning SQL statements.
- Learn how to share projects using CVS capabilities built into Data Studio.
- Learn more about Information Management at the developerWorks Information Management zone. Find technical documentation, how-to articles, education, downloads, product information, and more.
- Stay current with developerWorks technical events and webcasts.
- Follow developerWorks on Twitter.
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
- Participate in the discussion forum.
- Check out the developerWorks blogs and get involved in the developerWorks community.
No hay comentarios:
Publicar un comentario