An Introduction to DB2 UDB Express GUI tools: Connecting, controlling access, and performing basic tasks

Part 1 of 2

This article introduces you to the GUI tools for DB2 UDB Express on DB2 UDB for Linux, UNIX, and Windows Version 8 and describes how to use them for performing basic database operations, restricting access to objects, and setting up connectivity.

Raul Chong (rfchong@ca.ibm.com), DB2 Express Community Facilitator, IBM Toronto Lab

Raul F. Chong is a DB2 Express community facilitator at the IBM Toronto Lab. He recently assumed responsibility for building the DB2 Express community, helping members interact with one another and contributing to the forum. Raul holds a Bachelor of Science degree in Computer Science from the University of Toronto, and is a DB2 Certified Solutions Expert in both DB2 Administration and Application Development. Raul joined IBM in 1997. As a DB2 UDB consultant, Raul helped IBM business partners with migrations from other relational database management systems to DB2, and with database performance and application design issues. As a DB2 Technical support specialist, Raul helped resolve DB2 problems on the OS/390, z/OS, Linux, UNIX and Windows platforms. Raul has also worked as an information developer for the Application Development Solutions team, where he was responsible for the CLI guide and Web services material.


developerWorks Contributing author
        level

17 July 2003

Introduction

If you are new to Relational Database Management Systems (RDBMS), or have used other database products like Microsoft® Access, Microsoft Fox Pro or Microsoft SQL Server, but have never worked with IBM® DB2® Universal Database™ (UDB), this document is for you. Using DB2 UDB Express -- the DB2 product specifically targeted to small and medium sized businesses -- we will show you, using a step-by-step approach, how to accomplish some of the most important database tasks. As with many other database products, database tasks can be performed in several ways. This document focuses on how to perform those tasks using only GUI Tools.


DB2 GUI Tools overview

Let's first start with a quick overview of the DB2 UDB Express GUI Tools. Figure 1 shows the different tools that can be invoked from the IBM DB2 Menu. We won't cover all of these tools in this article, just the ones you most likely will need for your day-to-day database operations.

Figure 1. The IBM DB2 Menus
The IBM DB2 Menus

From the above menus, I'll use the following GUI Tools to describe how to perform daily database operations:

The Control Center

This is without a doubt the most important DB2 GUI tool. It provides you with a whole picture of your instances and databases, and allows you to perform most database operations in DB2. As you can see in Figure 2, the left panel (object pane) shows you the tree structure in your local and remote systems, and the right panel (contents pane) provides more detail about the specific item selected. We will describe the Control Center in more detail in the next sections.

Figure 2. The Control Center
The Control Center

The Configuration Assistant

You have DB2 UDB Express installed in your desktop in Toronto, but would like to connect to a DB2 Server in Sao Paulo, Brazil. How can you connect to this remote server? The Configuration Assistant GUI tool can help you with the set up required, and you can also test that the connection is working using this tool. I'll describe how to use the Configuration Assistant in detail in the section Setting up connectivity .

Figure 3. Configuration Assistant
Configuration Assistant

The Task Center

Say you would like to back up your database every day; however, you would prefer to perform this operation at 3:00 a.m. when there is no activity in your system. You can either get up at 3:00 a.m. every morning to perform this task, or you can save some sleep by automating the backup process. The task center can help you with this. Moreover, you can set up this task to send you an email if the operation was successful, and to page you if it was not.

Figure 4. Task Center
Task Center

The Journal

This GUI tool keeps track of all script invocations, DB2 messages, and DB2 recovery information. Should you need to investigate a problem, you can use this tool to find out what happened.

Figure 5. Journal
Journal

The Command Center

This tool allows you to input SQL statements or DB2 commands in an interactive window and see the results.

Figure 6. Command Center
Command Center

Health Center

Use the Health Center GUI tool to set up thresholds that, when exceeded, will prompt alert notifications, or even actions to relieve the situation. In other words, you can have the database manage itself!

Figure 7. Health Monitor
Health Monitor

Performing basic database operations

In this section we will describe how to perform some basic operations using GUI tools:

Creating a database

The easiest way to create a database using all the defaults is to type the following from the Command Center and clicking on the Execute button as shown in Figure 8:

Figure 8. Create Database using the Command Center
Create Database using the Command Center

For the above example, the database 'mydb' will be created using all the defaults. Note that you must perform the create database command while you are not connected to any other database. If you were connected, execute the command connect reset prior to creating the database.

Another easy way to create a database which allows you to perform some customization is by using the 'create database wizard' from the Control Center. The three steps to follow are:

  1. Click on Databases on the left panel of the Control Center
  2. Right click on Databases and choose create -> Database Using Wizard .
  3. Follow the panels from the create database wizard.

As you can see, like using any other GUI tool, performing operations with the Control Center is very intuitive.

Figure 9. Create database Wizard from the Control Center
Create database Wizard from the Control Center

The 'create database wizard' will walk you through several self-explanatory steps where you can specify the name and alias for the database, the type of table spaces to use for user, system catalog and system temporary tables, the locale and so on. Figure 10 shows some of the 'create database wizard' options.

Figure 10. Create database Wizard from the Control Center (Continued)
Create database Wizard from the Control Center (Continued)

If you need more information about an option, you can either point your mouse to the given option, and hover help will come up. Alternatively, you can select the option in question and press F1. This action also displays the hover help. This is illustrated by the box in Figure 10 above containing the text 'Specifies that storage will be managed by the system.'

Creating a table

From the Control Center again we would need three steps:

  1. Click on Tables on the left panel of the Control Center.
  2. Right click on Tables and choose Create .
  3. Follow the panels from the create table wizard.
Figure 11. Create Table from the Control Center
Create Table from the Control Center
Figure 12. Create Table from the Control Center (Continued)
Create Table from the Control Center (Continued)

By now it should be obvious to you how an object can be created using the Control Center. Simply look for the object in the left panel, right click on it and choose create. Following this same procedure you can create aliases, views, indexes, triggers, schemas, and so on.

Altering a table

If you want to alter some of the characteristics of a table, you first need to specify which table you want to alter. Thus, using the Control center's right pane (contents pane), follow these four steps:

  1. Click on Tables on the left panel of the Control Center.
  2. Click on the table name you want to alter.
  3. Right click on the table name and choose Alter .
  4. Follow the 'Alter Table' panels.
Figure 13. Altering a Table using the Control Center
Altering a Table using the Control Center

The example above used a table as the object to alter; however, a similar approach can be used to alter other types of objects. Note that not all attributes of an object can be altered. The GUI tool will only let you make the changes that are allowed. For example, in the case of a view, it can only be altered to add a comment. If you need other types of changes, you would need to drop the object and recreate it. As you can see from the above figure, the Drop option is also included in the menu when you right click on the object to be affected. From the menu, you can also choose Sample Contents, which will retrieve rows from your table; you can then alter some of the values directly if you want. Other operations like importing data to a table or exporting data from a table are also shown in the menu. We encourage you to explore these options on your own.

Performing queries

Now that you know how to create a database and its objects, you probably want to insert, update, delete or select information from these objects. The Command Center is the best GUI Tool to perform such operations. The figure below shows the Command Center with the Interactive tab selected. Based on the tab that is selected the menu bar will change its options. For Figure 14, a connection to the sample database has already been made. After the query 'select * from department' is executed, the output is immediately displayed in the results tab by default.

Figure 14. Performing queries with the Command Center
Performing queries with the Command Center

If you would like to see which access plan DB2 chose to retrieve a query, type the query in the Interactive tab, and then choose in the menu bar the option: Interactive -> Create Access Plan. If this is the first time you have performed such an operation, you will get an informational message indicating some 'Explain' tables were created to store access plan information. Then you will note that the Access Plan tab is selected showing you the path chosen by the DB2 optimizer. Figure 15 below shows the access plan for the query 'select * from department.'

Figure 15. Creating an Access Plan from the Command Center
Creating an Access Plan from the Command Center

The Interactive tab of the Command Center is normally used when you want to perform one SQL statement at a time. If you would like to perform several statements at a time, you probably want to use the Script tab. Figure 16 shows the Command Center with the Script tab selected. For the example in the figure, two statements will be executed one after the other after clicking on the 'execute' button. Note that each statement ends with a semicolon. This is needed by default to indicate the end of a statement. If you choose in the menu bar the option Tools -> Tools Settings, you will see that the entry 'use statement termination character' is checked by default with a value of ';'.

Figure 16. Performing scripts with the Command Center
Performing scripts with the Command Center

Restricting access to your objects

If you would like to keep confidential data inaccessible to regular users, you can always specify the privileges a user has against objects. Using the Control Center, it is easy to view which authorizations or privileges a user has. Follow these steps:

  1. On the left panel of the Control Center and within the desired database tree, click on User and Group Objects -> DB Users .
  2. Click on the desired user on the right panel of the Control Center.
  3. Right-click on the desired user and choose change.
Figure 17. Managing Security using the Control Center
Managing Security using the Control Center

Figure 18 shows the objects that user 'db2admin' has access to. For the example, we have selected only the Tables tab. On this panel you can also revoke some privileges from this user.

Figure 18. Managing Security using the Control Center (Continued)
Managing Security using the Control Center (Continued)

Setting up connectivity

In this section we will describe in more detail the Configuration Assistant GUI Tool, which is used to set up the connectivity between your DB2 UDB Express server and other DB2 Servers.

Choose Selected -> Add Database Using Wizard from the menu bar as shown in the Figure 19. The 'Add Database Wizard' window comes up where you have three choices:

  1. Use a Profile
  2. Search the network
  3. Manually configure a connection to a database
Figure 19. The Configuration Assistant
The Configuration Assistant

The panel in Figure 19 contains a brief description of each option. Assuming your network is not large and does not contain many routers or hubs, the second choice 'Search the network' may be the easiest to use to set up connectivity to other DB2 Servers. Once you select this option and click Next, you will see the following panel:

Figure 20. Search the Network using the Configuration Assistant
Search the Network using the Configuration Assistant

You have the option to add the system in the 'Known Systems' folder by clicking on Add System, or alternatively, if you don't know the system you are trying to access, you can let DB2 search your entire network, and will display a tree with your server machine names, instance names and database names. All you need to do to establish connectivity to a particular database in this tree is to select it. The figure above shows the panel for 'Known Systems'. The tree structure would be similar in the case of 'Other Systems (Search the Network)'.


Summary

In this article we have introduced you to several DB2 UDB Express GUI Tools to perform basic database operations like creating a database, creating a table, altering a table, performing queries, restricting access to objects, and so on. The DB2 UDB Express GUI Tools are the same across the DB2 UDB family. With this article we have demonstrated how powerful and easy to use these tools are. We hope with this introduction you feel more comfortable working with DB2 UDB.


What's Next

Move on to Part 2 of this introduction article. In part 2 we'll cover the topics of database automation, basic performance tuning and GUI Tools troubleshooting. Again, the focus is on DB2 GUI Tools.

We hope you have enjoyed your introduction to the DB2 UDB GUI tools and we welcome you to the world of DB2 UDB!

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=13598
ArticleTitle=An Introduction to DB2 UDB Express GUI tools: Connecting, controlling access, and performing basic tasks
publish-date=07172003