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.

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
   
   
   
2. Enter your database connection information as shown in Figure 2.
   
   Figure 2. Entering database connection information
   
   
   Note: 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 .
   
   Figure 3. Exporting database connections
   
   
   
5. Select the database connection profiles you want to export.
   
   Figure 4. 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.

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

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

Back to top

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.

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
   
   
   
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
   
   
   
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
   
   
   

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
   
   
   
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
   
   
   
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
   
   
   
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
   
   
   
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
   
   
   
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
   
   
   

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
   
   
   
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
   
   
   
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
   
   
   
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
   
   
   
6. After you finish editing the command, right click on it and select Run SQL.
   
   Figure 20. Running a command from the SQL editor
   
   
   
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
   
   
   

Back to top

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
   
   
   
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
   
   
   
6. Select the database you want to add into the working set and click Add.
   
   Figure 24. 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
   
   
   

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.

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 .
   
   Figure 26. Switching to the flat presentation view
   
   
   
3. This 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
   
   
   
4. Click on the Object List tab to show all the tables in the GSDB database.
   
   Figure 28. Object List view
   
   
   
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 icon.

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 , navigate to the Schemas folder.
3. Right click on the Schemas folder and select Filter...
   
   Figure 29. Selecting 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
   
   
   
6. Click OK to complete.
   
   Figure 31. 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

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 , navigate to the Tables folder.
3. The Object List view now shows an unsorted list of tables. Click the sort icon to open the Sort Settings wizard.
   
   Figure 32. 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
   
   
   
5. After specifying the settings you want to use, click OK to see the results.

Back to top

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

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
   
   
   
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
   
   
   
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
   
   
   
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
   
   
   
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
   
   
   
7. In the Change Management Script Editor, click Preview Commands.
   
   Figure 39. 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
   
   
   
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
    
    
    
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
    
    
    

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
   
   
   
2. From the Undo Commands dialog, click Run Undo....
   
   Figure 44. 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
   
   
   
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.

Back to top

Prompt intuitive database changes from Data Source Explorer

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
   
   
   
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
   
   
   
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
   
   
   
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.

Back to top

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.

Back to top

Download

Information about download methods

Resources

Learn

• In the Data Studio area on developerWorks, get the resources you need to advance your skills on Data Studio.
  
  
• "Using Common Connections in Data Studio Developer"
  
  
• "Data Studio software: The big picture"
  
  
• The "Overview of database administration from the Data Source Explorer" topic in the Integrated Data Management Information Center provides a list of commands that you can run from the SQL editor for Data Studio Administrator.
  
  
• The "Managing privileges for data objects" topic in the Integrated Data Management Information Center provides information on how to manage privileges for data objects.
  
  

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

• Participate in the discussion forum.
  
  
• Data Studio Community Space on developerWorks.
  
  
• Data Studio Team Blog on developerWorks.
  
  

About the author

Tina 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.

Comments (Undergoing maintenance)

Back to top

Trademarks  |  My developerWorks terms and conditions

Table of contents

• Introduction
• Enable collaboration among DBAs and developers
• Give DBAs an integrated tool for day-to-day DBA tasks
• Increase productivity by quickly locating data sources
• Simplify database changes using intuitive copy and paste capabilities
• Prompt intuitive database changes from Data Source Explorer
• Summary
• Download
• Resources
• About the author
• Comments

Next steps from IBM

• Try: DB2 Enterprise
• Demo: DB2 serving ATOM feed
• Webcast: A smarter way to successful SOA - Information as a trusted strategic asset
• Briefing: Information on Demand
• Buy: DB2 Enterprise

My developerWorks community

Dig deeper into Information Management on developerWorks

DB2 pureScale

See how true transparent scaling architecture can be achieved on distributed platforms

Special offers