© 2003 International Business Machines Corporation. All rights reserved.
In part 1 of this overview of the DB2® Universal DatabaseTM (UDB) Express GUI Tools, I described how to use the tools for performing basic database operations, controlling access to database objects, and setting up connectivity. In part 2 I will show you how to automate database tasks, perform some basic performance tuning, and perform some troubleshooting for typical problems when using the DB2 UDB GUI Tools. As in the first part, I expect you to be either new to relational database management systems (RDBMS), or have used other database products like MS Access, MS Fox Pro or MS SQL Server, but have never worked with DB2 UDB.
Automating your database tasks
A task is simply a script which can contain DB2, SQL, or operating system commands, and is associated with schedules and notifications. With the Task Center you can automate the execution of common database tasks and implement some conditional logic.
In order to use the Task Center, a 'Tools Catalog' must exist. This tools catalog contains information about the administrative tasks that you configure with the Task Center and Control Center. Please refer to the GUI tools troubleshooting section should you encounter an error related to the Tools Catalog.
You should also note that in order to send e-mails or pages through the task center, an SMTP Server must be set up in your machine.
The figures below provide an example of how to set up a task to perform once automatically on May 29th, 2003 at 23:00. It will be called 'MyFirstTask', and the assumption is that the task 'SecondTask' has been created beforehand. The MyFirstTask task runs on the instance 'DB2' on server 'RAULCHONG' under user 'db2admin'. It performs a connection to the sample database, followed by a query to the department table. If the task succeeds, it will write an entry to the journal GUI tool, and if it fails, it will notify 'Raul F. Chong' by sending him an email. Besides the notifications, upon success, the task will invoke the 'SecondTask' task, and upon failure, it will delete itself. With respect to authorizations for this task, user 'db2admin' can read, write and execute (run) the task.
Note in Figure 1 below that each statement is separated with '@'. This terminator is used because the field DB2 statement termination character has a value of '@'.
Figure 1. Using the Task Center to automate database operations - Task and Command Script tabs
In the Run properties tab, shown in the Figure 2, you can define what you consider is a successful event. A return of 0 is normally considered a success. If a task can return multiple return codes that are considered successful, you will need to define a success code set. Two success code sets were defined in this example (just for illustration purposes):
- My_Success_Code (when the returned code is 0, not shown in the figure) and
- My_Second_Success_Code. (when the returned code is > 100)
Figure 2. Using the Task Center to automate database operations - Run properties tab
In Figure 3, note that the Group tab is grayed out. This tab will only be enabled if the type of task you choose is a 'Grouping Task'. (See 'type' field on figure 1.) The Group tab can be used to specify a task containing other tasks.
On the Schedule tab we indicated this task was to run only once at 23:00 on May 29th, 2003, and then we clicked on Add to add the task to the list of scheduled tasks. The user id and password to be used to run this task must also be specified.
Figure 3. Using the Task Center to automate database operations - Group and Schedule tabs
Next, on the left panel of the Notification tab, you indicate who to notify on success or failure of the task. Once you select who to notify, you can click on Add to add an entry in the list of notifications (right panel of the Notification tab). In Figure 4, we have created two notifications: If the task is successful, an entry in the Journal GUI tool should be created. If the task fails, an email is to be sent to Raul F. Chong (obtained from a list of contacts).
Figure 4. Using the Task Center to automate database operations - Notification tab
As we can see from Figure 5, the Task Actions tab is similar to the Notification tab above, but rather than specifying who to contact on success or failure, we indicate in this tab what to do on success or failure. For Figure 5 we are specifying that on success, another task, 'SecondTask', should be executed. On failure the current task 'MyFirstTask' should be deleted.
Figure 5. Using the Task Center to automate database operations - Task Actions tab
On the Security tab, you can specify the privileges for users of the task. For this example, user 'db2admin' has all the privileges to perform the MyFirstTask task.
Figure 6. Using the Task Center to automate database operations - Security tab
Finally, Figure 7 shows a summary of the two tasks currently set up in this system.
Figure 7. Using the Task Center to automate database operations (continued)
Once the tasks are triggered, the Journal GUI tool as shown in Figure 8 can be used to review the outcome of the tasks. For the example, we can see that both tasks 'MyFirstTask and 'Second_Task' performed successfully.
Figure 8. The Journal - Task History Tab
If you select any of the task entries above and double click on it, you can obtain more details about the task outcome. Figures 9 and 10 below show each of the four tabs for the Journal GUI tool when displaying details for task 'MyFirstTask'.
Figure 9. The Journal - Results and Command Script tabs
Figure 10. The Journal - Output and Task actions tabs
You can use two DB2 UDB GUI tools to considerably improve the performance of your database with minimal effort. The tools are:
- The Configuration Advisor
- The Design Advisor
The Configuration Advisor shown in Figures 11 and 12 will determine the best values for DB2 configuration parameters based on a few questions it asks you related to your DB2 UDB server like the memory available, type of workload, and so on.
Figure 11. The Configuration Advisor
Figure 12. The Configuration Advisor (continued)
The Design Advisor provides you with an easy and fast way to determine which indexes are best for your workload. You can run your application against your database first which puts all the SQL statements executed into the cache. Next, you can run the Design Advisor which retrieves these statements in memory, and determines the necessary indexes. Figures 13 and 14 show the Design Advisor.
Figure 13. The Design Advisor
Figure 14. The Design Advisor (continued)
Four common errors may be encountered when working with GUI Tools:
The instance where your database resides has not been started.
To solve this problem, you can open the Control Center, click on the desired instance, right click on it and choose Start as shown in Figure 15:
Figure 15. Troubleshooting: Starting an instance
The DB2 Administration Server (DAS) has not been started.
This problem would affect remote GUI administration. To confirm the DAS is running, issue the db2admin start command as shown in Figure 16 from the Command Line Processor or a command window:
Figure 16. Troubleshooting: Using the Command Line Processor (CLP) to test if the DB2 Admin Server is running
If the DAS had not been started, the above command would have started it. If the DAS was already started, you would receive the message shown in Figure 16.
The tools catalog database was not created.
If you receive a message like the one in Figure 17, chances are that your Tools Catalog database has not been created. Normally this database is created at installation time.
Figure 17. Troubleshooting: Tools Catalog had not been created
To create the Tools Catalog using GUI tools, select Tools -> Tools Settings -> Scheduler Settings from within any GUI Tool as shown in Figures 18. Next, click on the Create New button under the Defining Tools Catalog section and you will get a window as shown in Figure 19.
Figure 18. Troubleshooting: Creating the Tools Catalog database
Figure 19. Troubleshooting: Creating the Tools Catalog database (Continued)
Note that most GUI Tools provide a Show Command button, which shows you the command that is actually sent to DB2. These commands can be performed from the Command Center.
You are not attached to the instance you want to work with.
If you received an error indicating you need to attach to the instance, select the desired instance, right click on it and choose 'Attach' as shown in Figure 20:
Figure 20. Troubleshooting: Attaching to an instance
In this article, as in the first part, we have introduced you to several DB2 UDB Express GUI Tools to perform basic database operations. We showed you how to automate some database tasks and perform some basic DB2 tuning. We also showed you some of the typical errors you may encounter when using DB2 UDB GUI Tools. Though we used the DB2 UDB Express GUI Tools in the article, note that these 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.
Raul F. Chong is a database consultant from the IBM Toronto Laboratory and works primarily with IBM Business Partners. Raul has worked for five years in IBM, three of them in DB2 Technical Support, and two of them as a consultant specializing in database application development and migrations from other RDBMS to DB2. You can reach him at rfchong@ca.ibm.com.
Comments (Undergoing maintenance)
Table of contents
Next steps from IBM
My developerWorks community
Tags
Use the slider bar to see more or fewer tags.
Popular tags shows the top tags for this particular content zone (for example, Java technology, Linux, WebSphere).
My tags shows your tags for this particular content zone (for example, Java technology, Linux, WebSphere).
Popular article tags |
My article tagsSkip to tags list
Popular article tags |
My article tags
