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
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 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
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
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
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
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
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
For the above example, the database 'mydb' will be created using all the defaults. Note that you must perform the
command while you are
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:
- Click on Databases on the left panel of the Control Center
- Right click on Databases and choose create -> Database Using Wizard .
- 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
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)
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:
- Click on Tables on the left panel of the Control Center.
- Right click on Tables and choose Create .
- Follow the panels from the create table wizard.
Figure 11. Create Table from the Control Center
Figure 12. 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:
- Click on Tables on the left panel of the Control Center.
- Click on the table name you want to alter.
- Right click on the table name and choose Alter .
- Follow the 'Alter Table' panels.
Figure 13. 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.
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
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
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
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:
- On the left panel of the Control Center and within the desired database tree, click on User and Group Objects -> DB Users .
- Click on the desired user on the right panel of the Control Center.
- Right-click on the desired user and choose change.
Figure 17. 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)
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:
- Use a Profile
- Search the network
- Manually configure a connection to a database
Figure 19. 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
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)'.
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.
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!