What's new in IBM Data Studio Administrator 2.1

Reduce database down time and increase DBA productivity

IBM® Data Studio Administrator 2.1 enhances DBA productivity by simplifying day-to-day database administration tasks. Focusing on accuracy, and thereby reducing risk, Data Studio Administrator streamlines database changes and migrations by automatically generating change commands. This reduces disruption caused by incorrect DDL, mis-coded system commands, or missing dependencies. Data Studio Administrator also simplifies DB2® object management by giving DBAs the ability to stop and start DB2 instances, run common commands and utilities, and navigate quickly to database objects within a single tool.

Share:

Tina Chen (chenti@us.ibm.com), Solution Architect, IBM

Tina ChenTina Chen is a solution architect from IBM's Data Studio development organization, based in IBM Silicon Valley Lab in San Jose, Calif. Tina is responsible for the technical development of partnerships and customer advocates for IBM Data Studio.



12 February 2009

Also available in Chinese Russian

Introduction

Version 2.1 of Data Studio Administrator became available in December of 2008. The new release focuses on making day-to-day tasks for DBAs easier (especially for those DBAs who are managing large databases with many objects). Many of the enhancements came as the result of extensive usability testing. Also, the new release is based on a new level of Eclipse (3.4), which is the same as other Eclipse-based Data Studio products. The key themes for the new release are:

  • Enable collaboration among DBAs and developers
  • Give DBAs an integrated tool for day-to-day DBA tasks
  • Increase productivity by providing views and filters to help quickly locate data sources
  • Simplify database changes using intuitive copy and paste capabilities
  • Prompt intuitive database changes from Data Source Explorer

The remainder of this article shows how the new features in the release support the above themes.

Prerequisites to run the hands-on scenarios

To run the scenarios described in this article, you need the following:

  • Data Studio Administrator 2.1
  • DB2 for Linux®, UNIX®, and Windows® (DB2 Express-C also works fine)
  • A sample database named GSDB. Follow these steps to create the database:
    • Get the zip file from the Download section of this article and extract the GSDB_Database.sql file from it.
    • Open a DB2 Command Window.
    • Navigate to the location where you saved the GSDB_Database.sql file.
    • Enter: db2 -td~ -f GSDB_Database.sql

Enable collaboration among DBAs and developers

Do you need to share database connection information with developers, DBAs, or data architects? Are you tired of emailing the information or searching for a better way to communicate changes to the connections? Being able to share connection information with your team members enhances collaboration. Exporting and importing connections eliminates communication errors and allows you to cascade connection information to your team members that use other Data Studio tools such as Data Studio Developer. Data Studio Administrator 2.1 improves the sharing process significantly with the addition of a shared configuration repository that does not require use of a shared file system for sharing connection information.

Exporting and importing database connection information

With a few clicks, you can export and share database connection configurations with other users and across Data Studio Eclipse-base tools.

Tips for sharing database connections

Data Studio lets you control how much of the database connection information you want to share or encrypt. For example, if you save your user name and password in a database connection, when the connection information is exported, the user name and password are also exported. For this reason, you should encrypt the information that is exported. If you do not save your user name and password, only the connection information, such as database name and host, is exported.

Follow these steps to use Data Studio Administrator to export a connection:

  1. From the Data Source Explorer, right click on Database Connections and select New.
    Figure 1. Selecting New... in the Data Source Explorer
    Selecting New... in the Data Source Explorer
  2. Enter your database connection information as shown in Figure 2.
    Figure 2. Entering database connection information
    Entering database connection informationNote: if you select Save password, Data Studio Administrator will export the User name and Password.
  3. Click Finish to establish the new database connection.
  4. From the Data Source Explorer, click the Export icon Export icon.
    Figure 3. Exporting database connections
    Exporting database connections
  5. Select the database connection profiles you want to export.
    Figure 4. Selecting a database connection profile
    Selecting a database connection profile
  6. Click Browse... and specify the location where you want to save the exported database connection.
  7. If you want to include the user name and password for the database connection, check Encrypt file content.
  8. Click OK.

Easily access and reuse connection information

Team members can easily share and reuse database connection information by saving their connection configurations into a connection repository. A central connection repository ensures that your whole team has the most up to date connection properties and can reuse them. To find out more about common connections, read the developerWorks article "Using common connections with Data Studio Developer," which you can find linked to from the Resources section.

Follow these steps to use Data Studio Administrator to import a connection:

  1. From the Data Source Explorer, click the Import icon Import icon.
    Figure 5. Importing database connections
    Importing database connections
  2. Click Browse... and locate the database connection file you want to import.
    Figure 6. Locating a database connection file
    Locating a database connection file
  3. Click OK to complete importing the database connection.

Give DBAs an integrated tool for day-to-day DBA tasks

Data Studio Administrator has built-in features that let you perform common database administration tasks within the Eclipse environment, such as starting or stopping a DB2 instance, backing up a database, creating database object, and running DB2 commands or SQL scripts. This integration eliminates the need to open or switch between multiple tools.

Starting and stopping a DB2 instance

You can use Data Studio Administrator to run a variety of DB2 commands such as stopping or starting a DB2 instance.

Did you know you can run SQL scripts and DB2 commands in the SQL editor?

In the SQL editor for Data Studio Administrator, you can run all your SQL scripts such as DDL or DML in the editor. The editor even lets you run some DB2 commands such as DB2STOP or DB2START. For a full list of commands that you can invoke, refer to the "Overview of database administration from the Data Source Explorer" topic in the Integrated Data Management Information Center, which is linked to from the Resources section.

Follow these steps to use Data Studio Administrator to stop an instance of DB2:

  1. In the Data Source Explorer, right click on Instance and select Stop Instance....
    Figure 7. Selecting Stop Instance... in the Data Source Explorer
    Selecting Stop Instance... in the Data Source Explorer
  2. Optionally, you can select Preview Command to see the script prior to running it, or click Open in SQLX Editor to modify it. When you are ready, click Run to execute the command.
    Figure 8. Using the Stop Instance DB2 task assistant
    Using the Stop Instance DB2 task assistant
  3. Look in the Messages section of the task assistant to see if you have successfully stopped the DB2 instance.
    Figure 9. Reviewing messages in the task assistant
    Reviewing messages in the task assistant

Creating a backup of the database

As a DBA, it is often necessary for you to create backups of databases. Instead of performing this task by opening a command line and manually entering the correct command, you can use Data Studio Administrator and create a backup with just a few simple clicks.

Follow these steps to use Data Studio Administrator to backup a database:

  1. In the Data Source Explorer, right click on the GSDB database and select Backup....
    Figure 10. Selecting Backup... in the Data Source Explorer
    Selecting Backup... in the Data Source Explorer
  2. On the Back up dialog, Click on the 2. Image tab. This is where you can specify the type and location for the backup. For Media type, select File System. Click Browse... to specify the location where you want to store the backup of the database.
    Figure 11. Specifying the type and location for a backup
    Specifying the type and location for a backup
  3. You can click on the 3. Options tab to specify other options for the backup command. For example, you can specify that you want to quiesce the database prior to the backup.
    Figure 12. Specifying other options for the backup command
    Specifying other options for the backup command
  4. Optionally, you can select Preview Command to see the script prior to running it, or click Open in SQLX Editor to modify it. When you are ready, click Run to execute the command. You can also choose to save the file to execute at a later time.
    Figure 13. Running a backup command in the task assistant
    Running a backup command in the task assistant
  5. Look in the Messages section of the task assistant to see if the backup succeeded or if there are any errors you need to address before trying to run the command again.
    Figure 14. Reviewing messages in the task assistant
    Reviewing messages in the task assistant
  6. Go to the SQL Results tab to see additional information about the backup, such as the timestamp for the backup image.
    Figure 15. Viewing details on the SQL Results tab
    Viewing details on the SQL Results tab

Creating database objects

Often, you may need to create database objects such as databases, tables, or table spaces to address incoming business requirements. Data Studio Administrator provides new task assistants that make you more productive and efficient by guiding you through the database object creation process. The task assistants let you deploy the scripts within the tool.

Follow these steps to use Data Studio Administrator to create a new database:

  1. From the Data Source Explorer, expand the GSDB node, right click on Instance, and select Create Database....
    Figure 16. Selecting Create Database... in the Data Source Explorer
    Selecting Create Database... in the Data Source Explorer
  2. The fields in the Create database task assistant provide a framework to guide you through the create database command syntax. You can specify the database name, database location, alias, comments, and whether you want automatic storage. Optionally, you can use the 2. Storage Paths and 3. Locale tabs to enter other parameters.
    Figure 17. Using the Create database task assistant
    Task assistants guide users through creating database
  3. Optionally, you can select Preview Command to see the script prior to running it, or click Open in SQLX Editor to modify it. The next steps show you how to use the SQLX editor.
    Figure 18. Opening the SQLX editor
    Opening the SQLX editor
  4. After you click Open in SQLX Editor, you receive a warning that changes made in the SQLX editor will not be reflected in the task assistant. Click OK to continue.
  5. In the SQLX editor, you can edit the SQL command generated by the task assistant.
    Figure 19. Using the SQLX Editor
    Using the SQLX Editor
  6. After you finish editing the command, right click on it and select Run SQL.
    Figure 20. Running a command from the SQL editor
    Running a command from the SQL editorr
  7. Go to the SQL Results tab to see if the database was created successfully.
    Figure 21. Viewing database creation status on the SQL Results tab
    Viewing database creation status on the SQL Results tab

Increase productivity by quickly locating data sources

The time you spend just trying to find the objects you need to work on can have a negative impact on your productivity, especially when you are working with multiple database applications and with very large numbers of database objects. The new release of Data Studio Administrator includes a number of enhancements that are designed to make it easier for you to find what you need quickly.

Group databases together (working sets)

Wouldn't it be nice if you could group together databases that are dependent on each other? Grouping databases together can make it easier and faster for you to locate and access the database you need when you need it. For example, you might group together the development, test, and production databases for your company Web site. Data Studio Administrator refers to these groups as working sets.

Follow these steps to use Data Studio Administrator to create a new working set:

  1. Make sure you have more than one database connection in your Data Source Explorer. This example uses GSDB and GS_DB_TEST, but you could also create a connection to SAMPLE.
  2. From the Data Source Explorer, right click on Database Connections and select Configure Connections Working Sets....
    Figure 22. Selecting Configure Connections Working Sets... from the Data Source Explorer
    Selecting Configure Connections Working Sets... in the Data Source Explorer
  3. In the Connections Working Set wizard, click New.
  4. In the New Working Set wizard, enter a name for your new working set. (In this example, the new working set is named MyWorkingSet.)
  5. Expand the local host and DB2 instances to view the database as shown in Figure 23.
    Figure 23. Viewing expanded local host and DB2 instances
    Viewing expanded local host and DB2 instances
  6. Select the database you want to add into the working set and click Add.
    Figure 24. Adding a database to the working set
    Adding a database to the working set
  7. Continue adding other databases into the working set. When you are done, click Finish.
  8. Click OK to add the working set to Data Source Explorer.
  9. Now you can use Data Source Explorer to easily find the databases you grouped together in the working set.
    Figure 25. Viewing the new working set in Data Source Explorer
    Viewing the new working set in Data Source Explorer

Easily locate data objects you care about

With the previous release of Data Studio Administrator, you had to use a hierarchical presentation to navigate to database objects with the same parent type, such as a common schema. If you were looking for a table but you didn't know its schema, it could be hard to find. With Version 2, you have the option of using a flat presentation view for navigation. The flat presentation organizes all the database objects by type, which enables you to sort objects by name, schema, connection alias, and so on. You can toggle between the hierarchical and flat presentation styles.

Flat presentation vs. hierarchical presentation

Each of the presentation views has advantages that make it better suited for a particular type of task. Use the hierarchical presentation to work with objects that share the same parent, such as objects within the same schema. Use the flat presentation to work with several database objects that have different parents, such as viewing all the tables within a database.

Follow these steps to work with database objects from the Data Studio Administrator flat view:

  1. If you do not already have an Object List view open, navigate to Window > Show View > Other > Connectivity > Object List
  2. In the Data Source Explorer, click the icon to switch to the flat presentation view Hierarchical presentation icon.
    Figure 26. Switching to the flat presentation view
    Switching to the flat presentation view
  3. This icon Flat presentation icon indicates that Data Source Explorer is presenting the database object in a flat presentation. Open the GSDB database connection and navigate to the Tables folder.
    Figure 27. Navigating to the tables folder
    Navigating to the tables folder
  4. Click on the Object List tab to show all the tables in the GSDB database.
    Figure 28. Object List view
    Navigate to the tables folder
  5. You can sort the items in the Object List table by clicking on the column headers.
  6. To return to the hierarchical presentation view, click on the Flat presentation iconicon.

How to apply filters in the Data Source Explorer

You can significantly increase your productivity by creating customizable views. Customizable views give you flexibility in grouping, viewing, and hiding data sources. Data Studio Administrator provides an array of views that enable you to sort and filter your database objects.

Follow these steps to use Data Studio Administrator to apply filters on the schema:

  1. Ensure you are connected to the GSDB database.
  2. Using the hierarchal presentation view of Data Source Explorer Hierarchical presentation icon, navigate to the Schemas folder.
  3. Right click on the Schemas folder and select Filter...
    Figure 29. Selecting Filter... in the Data Source Explorer
    electing Filter... in the Data Source Explorer
  4. In the Connection Filter Properties wizard, clear the Disable Filter checkbox.
  5. Click the Selection radio button, and check the items you want to include.
    Figure 30. Selecting items to include in schema filter
    Selecting items to include in schema filter
  6. Click OK to complete.
    Figure 31. Viewing a database with filters applied
    Viewing a database with filters applied
  7. You can use these same steps to apply filters on other database objects such as tables, table spaces, aliases, and sequences.

How to sort and apply filters to the Object List view

Tips on sorting

You can apply a simple sort by clicking on the column such as schema, name, or table space.

Follow these steps to use Data Studio Administrator to apply filters on the table in the object list view:

  1. Ensure you are connected to the GSDB database.
  2. Using the flat presentation view of the Data Source Explorer Flat presentation icon, navigate to the Tables folder.
  3. The Object List view now shows an unsorted list of tables. Click the sort icon Sort icon to open the Sort Settings wizard.
    Figure 32. Starting the Sort Settings wizard
    Starting the Sort Settings wizard
  4. In the Sort Settings wizard, specify the Sort Order and Sort Direction you want to use.
    Figure 33. Specifying Sort Order and Sort Direction on the Sort Settings wizard
    Specifying Sort Order and Sort Direction on the Sort Settings wizard
  5. After specifying the settings you want to use, click OK to see the results.

Simplify database changes using intuitive copy and paste capabilities

DBAs often need to create a development database that is a subset of a production database. This is to avoid endangering the production database with untested code. A common means to do this is by backing up the production database and restoring it onto the development server. However, you usually don't need the entire production image or all of its data.

The copy and paste or drag and drop feature of Data Studio Administrator lets you copy database objects from one data server and paste them into another. This is intended for database migration scenarios where DBAs know which database objects they want to copy and paste.

How to promote database changes using copy and paste

Drag and drop options

Besides copy and paste, you can also use the drag and drop operations to drag database objects from a database and place them in the change management script editor.

Follow these steps to use Data Studio Administrator to promote database changes using copy and paste:

  1. Make a connection to the GSDB database and navigate to the GOSALESCT schema.
  2. Right click on the GOSALESCT schema and select Copy.
    Figure 34. Selecting Copy in the Data Source Explorer
    Selecting Copy in the Data Source Explorer
  3. Make a connection to a target database (for example, GSDBDEV). Right click on the NULLID schema and select Paste.... This opens the Paste Database Objects wizard.
    Figure 35. Selecting Paste... in the Data Source Explorer
    Selecting Paste... in the Data Source Explorer
  4. The Paste Database Objects wizard guides you through the change management script creation. On the first screen, select Create a New Change Management Script and click Next.
    Figure 36. Using the Change Management Script Selection screen
    Using the Change Management Script Selection screen
  5. On the Schema Selection screen, select NULLID, or another schema that you want to be included in the change management script, and click Next.
    Figure 37. Using the Schema Selection screen
    Using the Schema Selection screen
  6. On the Copy Options screen, you can specify if you want to copy the database objects, data, or both. For this example, select Copy database objects and data.
    Figure 38. Using the Copy Options screen
    Using the Copy Options screen
  7. In the Change Management Script Editor, click Preview Commands.
    Figure 39. Using the Change Management Script Editor
    Using the Change Management Script Editor
  8. Data Studio Administrator also provides you with an HTML report that provides a summary of the changes you made. You can easily post the report on your team's Web page prior to executing the changes.
    Figure 40. Viewing the HTML Summary of Changes Report
    Viewing the HTML Summary of Changes Report
  9. Return to the Change Management Script editor and scroll down to the Impacted Objects section. This section lists the objects that will be impacted by the copy, paste, or delete action.
  10. Scroll down to the Commands section to view the script that has been generated. At this point, you can also modify the script before running it. To change the script, click Open in SQLX Editor.... To customize the way the associated data is exported, click Customize.... The Customize wizard lets you use the integration of Data Studio Administrator with High Performance Unload as well as EXPORT and LOAD features.
    Figure 41. Using the Commands section
    Using the Commands section
  11. Click Run... to migrate all the database objects and data into the GSDBDEV database.
  12. Data Studio Administrator provides another HTML report that describes the changes that were deployed onto the database.
  13. Look in the Messages section to see what was deployed onto the GSDBDEV database.
    Figure 42. Viewing the status of the deployment in the Messages section
    Viewing the status of the deployment in the Messages section

How to recover from errors during deployment

There is no need to worry if an error occurs while you are deploying a change management script. You have two options for recovery. You can undo the commands that completed before the error occurred or resolve the issues that caused the error and then restart the deployment from the command that failed.

Follow these steps to undo partially deployed changes:

  1. Open the command dialog and click Undo Commands.
    Figure 43. Generating undo commands
    Generating undo commands
  2. From the Undo Commands dialog, click Run Undo....
    Figure 44. Previewing the generated undo commands
    Previewing the generated undo commands
  3. After reviewing the undo commands, click Finish.

Follow these steps to restart partially deployed changes:

  1. Open the command dialog and click Undo Commands.
    Figure 45. Generating undo commands
    Generating undo commands
  2. Resolve the errors and click Restart Commands to generate commands to continue from where the error first occurred.
  3. After reviewing the restart commands, click Finish.

Prompt intuitive database changes from Data Source Explorer

When should I use Change Management Script Editor?

The Change Management Script Editor helps you by letting you view the impacts of your planned changes, letting you view and modify scripts, and letting you view and modify undo commands to revert back to a previous state. But if you know which database objects you want to drop and don't care about the consequences, you can drop the database without the Change Management Script Editor by selecting the Data Object Editor.

DBAs are constantly adjusting the database based on new business requirements coming in from the market or developers. DBAs need a controlled process to manage the constant changes of the database objects. Data Studio Administrator gives you the flexibility to change multiple database objects as the requirements come in. DBAs have the ability to collect the changes and later run those database modifications in a batch. For example, you can browse your database for tables that need to be modified or dropped, perform extended alters, and add columns to an existing table. Data Studio Administrator can collect all the changes you need to make in the task assistant and later run them as a batch.

Follow these steps to use Data Studio Administrator to drop a database

  1. Make a connection to the GSDB database. Navigate to the GOSALESCT schema and then to the Tables folder.
  2. Right click on the CUST table and select Drop.
    Figure 47. Dropping a table from the Data Source Explorer
    Dropping a table from the Data Source Explorer
  3. From the Select the Editor to Use dialog, select Change Management Script Editor so that task assistants can guide you through the change process. (Alternatively, if you only want to drop the table, select Data Object Editor.)
  4. Click OK.
    Figure 48. Selecting the Change Management Editor
    Selecting the Change Management Editor
  5. You can also drop and drop other database changes or prompt changes by clicking CREATE, ALTER, or DROP.
    Figure 49. Prompting database changes from the Change Management Script Editor
    Prompting database changes from the Change Management Script Editor
  6. After you have gathered all the database changes you want to make, click Preview Command to look at all the commands.
  7. Click Run to deploy the database changes.

Manage privileges for data objects

Data Studio Administrator gives you the ability to view and manage privileges on data objects for your database. In the Data Source Explorer, you can view privileges by highlighting the object (such as a table) and viewing the privileges in the Properties view. You can view privileges for authorization IDs (such as users, roles, or groups), or for an individual object (such as a table or view). To find out more about managing privileges for data objects, refer to the "Managing privileges for data objects" topic in the Integrated Data Management Information Center, which is linked to from the Resources section.

Run DB2 LUW administration commands and utilities from the SQL Editor

Data Studio Administrator has enhanced the SQL editor to enable you to run DB2 LUW administration commands, SQL scripts, and utilities from within the editor. Now you can create your own SQL commands and DB2 commands in one file, and even copy and paste text from task assistants to further customize or enhance scripts prior to deployment. Additionally, you can use the editor to perform a syntax check on the commands. For a full list of supported DB2 LUW administration commands, refer to the "Overview of database administration from the Data Source Explorer" topic in the Integrated Data Management Information Center, which is linked to from the Resources section.


Summary

Data Studio Administrator is a comprehensive tool for change management and for database administration. It can help simplify everyday tasks DBAs perform, such as database maintenance and managing user privileges. It also provides the means for enhancing collaboration with other team members. One of the most important features of Data Studio Administrator is that it helps DBAs react quickly to changing requirements within a dynamic business environment. DBAs can manage complex database changes more quickly and with less risk by using Data Studio Administrator functions that automatically generate database change scripts, discover the impacts of proposed changes, and provide the capability to undo changes.


Download

DescriptionNameSize
GSDB sample database for this articleGSDB_database.zip284KB

Resources

Learn

Get products and technologies

  • Download Data Studio Administrator 2.1 to try the scenarios described in this article.
  • 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=369081
ArticleTitle=What's new in IBM Data Studio Administrator 2.1
publish-date=02122009