Leverage MySQL skills to learn DB2 Express, Part 3: DB2 versus MySQL graphical user interface

Skill transfer to DB2 for Windows

Managing databases is a way of life for DBAs, and inevitably most DBAs get their jobs done either typing away at the command line or through GUI point-and-click. These two techniques define two schools of DBAs. Often, the flashy characteristics of the GUI interface are overshadowed by the flexibility and intricacy of the command line. IBM® DB2® Express-C edition, however, without partiality, comes with a rich set of GUI features and functions for administrators to carry out daily tasks from space management, backup and recovery to development related tasks such as guided stored procedure creation, database automatic tuning, and SQL tuning.

Share:

Allan Tham, DB2 Presales Technical Specialist, ASEAN Techline, IBM

Author photoAllan Tham works as a DB2 Content Manager Technical Presales Support Specialist for Business Partners. He helps business partners solve a wide range of technical problems. Allan is certified for DB2 Content Management administration. Prior to joining IBM, Allan worked in an end-user environment, where he was an Oracle DBA for three years.



17 August 2006

Introduction

If you're in the process of learning to use DB2 Express, chances are you've had experience with another database already, such as MySQL. This article serves as a skills transfer for existing MySQL database administrators to learn about IBM DB2 Express-C GUI administration. IBM DB2 Express-C comes with feature-rich GUI administration capabilities which we will highlight in general, and then delve into the details. Coming from MySQL background, you'll find that IBM DB2 Express-C edition indeed offers more GUI options for administrators. DB2 Express-C edition essentially offers GUI options that correspond with all the command prompt capabilites, whereas the open source MySQL's GUI administration is not quite up to its command counterpart.

This article focuses primarily on IBM DB2 Express-C edition, version 9.1 capabilities with some highlights of the similarities and differences between MySQL and IBM DB2 Express-C edition.

We'll examine the following topics:

  • General administration
  • Backup and recovery
  • Performance related tasks
  • Development related tasks
  • Health related tasks

Overview

There are three F's to remember with considering DB2 Express-C:

  • Free to develop
  • Free to deploy
  • Free to distribute

You are limited to two physical CPUs and 4GB RAM per server. Plus, you should be aware that the following features do not come with DB2 Express:

  • Replication
  • Database Partitioning Feature
  • Connection Concentrator
  • DB2 Geodetic Extender
  • Query Patroller
  • Net Search Extender

These features are available with other editions of DB2 for Linux®, UNIX®, and Windws®, such as DB2 Workgroup and Enterprise editions.

To get a copy of IBM DB2 Express-C, visit the download page. Currently, IBM DB2 Express-C edition is supported on Windows and Linux platforms with validated Linux distributions that can be obtained online.


MySQL GUI capabilities - A quick overview

For MySQL, the option of GUI administration comes with a separate download and install. MySQL Administrator function can be broken down to few categories, including:

  • General information - Server information and services
  • Server health - Startup parameters, online health indicators, and error logs
  • Administration - Backup & recovery, user management, schema object management, and connection management

MySQL Administrator (version 1.1.9 at the time of this writing) provides the following features:

  • General information
    • Server information: This provides server up/down status, server hardware specifications, and so on.
      Figure 1. MySQL Server Information
      MySQL Server Info
    • Service Control: This provides start and stop MySQL service with online logging. It also allow configuration of startup configuration file to use, storage engine to use, and so on.
      Figure 2. MySQL Service Control
      MySQL Service Control
  • Server health
    • Startup parameters: These parameters include general memory settings, network port settings, and storage engine specific parameters.
      Figure 3. MySQL parameter settings
      MySQL Parameter Settings
    • Health indicator: This allows you to monitor database system health based on predefined parameters.
      Figure 4. MySQL Health Indicator
      MySQL Health Indicator
    • Logging: This provides error and general logs for MySQL.
      Figure 5. MySQL error logging
      MySQL Error Logging
  • Administration - Backup & restore enables databases to be backed up both online and offline, with the ability to restore to a point in time. User management allows creation of new users and groups by assigning privileges to them. Finally, schema objects can be managed as well.

Accompanying the MySQL Administrator tool is the MySQL Query Browser which provides drag-and-drop, point-and-click methods of managing database objects, and creating, managing and optimizing queries. Again, similar to MySQL Administrator, Query Browser is a separate download and install from MySQL server itself.


MySQL & IBM DB2 Express-C GUI quick comparison

At a glance, having reviewed the MySQL Administrator and before we explore IBM DB2 Express-C GUI capabilities, it's appropriate to have a bird-eye view of what is available or lacking in both databases. This way, as a MySQL DBA, you can quickly nagivate through the differences, digest the advantages that IBM DB2 Express-C provides, or winnow out those deemed unnecessary.

As a quick comparison, the below table shows what is available in MySQL Administrator at the current version and the mapping to IBM DB2 Express-C. The table also shows other DB2 Express-C GUI administration features that are powerful and desirable for daily administrative tasks. The DB2 Control Center provides far more features than what is available in MySQL Administrator. A glance at this table will indeed prove so.

Table 1. MySQL and IBM DB2 Express-C GUI administration comparison
Features/FunctionsAvailable in MySQL (MySQL Administrator)Available in IBM DB2 Express-C (DB2 Control Center)Comments
General information
Server and services informationBoth databases provide server and services related information.
General administration (Instance level)
Instance create/drop×This features is not available in MySQL.
Instance start/stop×This features is not available in MySQL.
Instance quiesce/un-quiesce×This features is not available in MySQL.
Communication protocol setupHowever, IBM DB2 Express-C edition supports not just TCP/IP protocol. It supports other network protocols such as NetBios, and named pipes from the GUI tools.
Online status for application connectionsThis feature is supported by both databases.
Online viewing memory usage statusBoth database allow real-time memory usage plotting.
Instance activity monitoring×IBM DB2 Express-C edition comes with performance monitoring in the following areas -
  • Application performance
  • Application concurrency
  • Resource consumption
  • SQL performance
  • Lock monitoring
General administration (database level)
Database create/drop×This feature is not supported by MySQL.
Database connect/disconnectConnection Managemenet is included in both databases.
Database start/stop Starting and stopping a database a feature provided.
Database quiesce/un-quiesce×IBM DB2 Express-C can lock out users from a database for maintenance purpose.
Privileges/authorizations managementUser and group management including privileges assignments are available for both databases.
Managing throttling utilities×IBM DB2 Express-C edition throttling feature enables resources to be used accordingly during peak and low hours. During peak hours, utilization of CPU power by the throttling utilities will be kept minimal and vice versa during low hours. One example of throttling utility is the database backup.
Automatic maintenance×IBM DB2 Express-C provides Automatic Maintenance in the following areas -
  • Database backup
  • Table and index reorg
  • Optimized data access
Design advisor×IBM DB2 Express-C edition comes with design advisor, a built in mechanism whose job is to advise database to make proper configuration decision. Though inherently meant for the following aspects, what we primarily concerned with is the index advising. It has the capability to know which index to build to optimize the query.
  • Indexes
  • MQTs
  • MDC
  • Table Re-partitioning
MQTs, MDCs and table partitoning using the data partitioning feature (DPF) are features available in other editions of the DB2 for Linux, UNIX, and Windows family. DPF is a separate purchase.
Configuration advisor×IBM DB2 Express-C edition configuration advisor is a built-in mechanism that select the optimal configuration parameters to use in order to achieve a good database performance.
Configure parametersBoth databases support manual parametes configuration.
Configure database loggingBoth databases support database logging configuration.
Database backup & restoreBoth databases support database backup & restore.
Database roll-forwardBoth databases support database roll-forward.
Show Explain plan×IBM DB2 Express-C edition shows the visual explain plan, the total cost and the cost associated with each access path.
Generate DDLs for database objects×IBM DB2 Express-C edition allows administrators to generate DDLs for database objects. These database objects can be any of of the following -
  • Tables
  • Views
  • Indexes
  • Triggers
  • Sequences
  • Stored procedures
  • Primary key, referential integrity, and check constraints
  • User defined functions
And more ...
Storage managementMySQL supports storage management for MyISAM and InnoDB tables.
Online status for application connectionsSame as instance level, IBM DB2 Express-C edition allows administrators to monitor all applications connected to a database. You can force some or all connections off from a database.
Database activity monitoringSame as instance level, IBM DB2 Express-C edition comes with database level performance monitoring in the following areas -
  • Application performance
  • Application concurrency
  • Resource consumption
  • SQL performance
  • Lock monitoring
Web service enabling and disabling×By far, one of the newest and most important features that come with IBM DB2 Express-C (version 9). This feature allows you to store/retrieve XML data natively without any mapping or conversion required.
User & group managementUser and groups management including privileges and authorizations are supported in both databases.
Schema objects managementBoth databases manage schema objects well.
Event monitoring×IBM DB2 Express-C edition keeps track of connections, deadlocks, statements, and transactions as events. Data collected in an event can be used to alert administrator should anomalies show up. Output of the event monitor can be written to a table, a file or a named pipe.
Table space management×IBM DB2 Express-C edition provides table space management which includes creating, dropping and also performing backup & restore.
Buffer pool managementIBM DB2 Express-C edition provides buffer pool management which includes creating, altering buffer pool values and dropping it.
Health indicators×IBM DB2 Express-C provides a means to check your database health by setting the health indicators alert threshold. This threshold can either be at the warning or alarm level. Notification can be configured to send alert based on warning or alarm to administrators for corrective measurements. Furthermore, script or task actions can be kicked off to solve the problem without administrators' intervention. This is one of the self-managed features provided IBM DB2 Express-C.
Federated object management×IBM DB2 Express-C edition provides a means to communicate to external database through the setting up of federated objects.
XML schema repository management (XSR)×Starting with IBM DB2 Express-C edition version 9, native XML store and retrieve capabilities are available.

IBM DB2 Express-C GUI capabilities

In contrast to MySQL, IBM DB2 Express-C edition comes with a rich set of tools without the need to have separate download and install for each code. This enables administrators to quickly deploy and make full use of the GUI administration capabilities after a typical, custom or complete install. The DB2 Express-C set of GUI tools bundled with the product can be categorized into the following categores: (only GUI tools is mentioned here)

  • General administration tools
    • Control Center
    • Journal
    • License Center
    • Replication Center
    • Task Center

    The DB2 Control Center is the centerpiece of the GUI tool suite provided by DB2 Express-C edition. It supports daily operation tasks such as schema object management, user and group management, and privilege and authorization management. In addition it supports monitoring capabilities such as visualizing the memory usage and event monitoring for various tasks to resolve performance issues. The DB2 Control Center includes three options for look and feel. Administrators can choose Basic, Advanced or Custom upon starting up. Though largely comprehensive and sufficient in itself, the DB2 Control Center can be extended as well using plug-ins (shipped with DB2 Express-C). These extensions allow the DB2 Control Center to include objects in menu trees, toolbars, and popups. Sample code is provided online, for example, to add a toolbar button.

    The Journal tool keeps historical events such as task history, database actions, database messages and notification logs. It often serves as an audit trail for the past events carried throughout the weeks.

    The Task Center is the place to create scheduled jobs or tasks. Tasks can be run sequentially based on the success or failure code defined.

    Figure 6. General Administration Tools
    General Administration Tools
  • Information
    • Check for DB2 updates
    • Information Center V9.0
  • Monitoring tools
    • Event Analyzer
    • Health Center
    • Indoubt Transaction Manager
    • Memory Visualizer

    The Event Analyzer is the tool to analyze event data that has been gathered by the Event Monitor. Event monitoring is used to monitor database performance; it provides information on database objects such as tables, buffer pools, table spaces, and application level monitors such as connection, SQL statments, deadlocks, and so on.

    Figure 7. Monitoring tools
    Monitoring Tools
  • Setup tools
    • Activity Monitor
    • Configuration Assistant
    • Configure DB2 .Net Data Provider
    • Default DB2 Selection Wizard
    • First Steps
    • Register Visual Studio Add-Ins
    Figure 8. Setup tools
    Setup Tools

Though available, we will not discusss Replication Center, DB2 .Net Data Provider and Visual Studio Add-Ins. We shall now proceed to look at each category in more details.


General administration tools

For normal day to day administrations, the tools that DB2 Express-C is equipped with to carry out these tasks are primarily the DB2 Control Center, Journal, and Task Center. There are indeed so many administration tasks that can be accomplished using DB2 Control Center that we will not go through all of them in detail.

Control Center

The DB2 Control Center allows many tasks to be performed ranging from daily operations such as schema object management, user and group management, and privilege and authorization management, to monitoring capabilities such as visualizing the memory usage and monitoring various tasks to resolve performance issues. The administrative tasks that can be administered via DB2 Control Center are divided into two categories, those that operate at an instance level, and those that operate at a database level.

  • DB2 instance level management - as seen below, DB2 instance level management can be adminstered using DB2 Control Center:
    Figure 9. DB2 instance management
    DB2 Instance Management

    Of the instance level administrative tasks that can be performed using DB2 Control, we will focus on parameter configuration, application connections monitoring, memory utilization, and activity monitoring.

    • Parameters configuration - This is a quick way to alter parameters that may suit your environment accordingly. There is no option to update this at the file level. Even though these parameters can be modified online, the command line processor (CLP) is provided as well. To query database instance level parameters, you can issue the command GET DATABASE MANAGER CONFIGURATION and to update them, issue the command, UPDATE DATABASE MANAGER CONFIGURATION. Note that some of these parameters require the instance to be restarted in order to take effect. For example, to change the parameter, AUTHENTICATION under Adminstration category from the default value, Server to client, the following output will show in the page. In this case, the pending value, client will only take effect after instance restart.
      Figure 10. Changing authentication method
      Changing authentication method

      As you can see from Figure 11 below, there are many areas of configuration parameters that can be set from the this page. They are broken down to the following categories:

      • Administration: Administrative level parameters such as plugins, authorizations and connections related settings
      • Applications: Application level parameters. For example, MAX_CONNECTIONS specifies the maximum number of client connections allowed per database partition. In DB2 Express-C edition, partition is not applicable.
      • Communications: Node and databases discovery related
      • Diagnostic: DB2 diagnosis, db2diag.log related parameters. For example DIAGLEVEL specifies which level of diagnostic will take place in db2diag.log. It ranges from 0-4.
        • 0 - No diagnostic data captured
        • 1 - Severe errors only
        • 2 - All errors
        • 3 - All errors and warnings
        • 4 - All errors, warnings and informational messages
      • Environment: The server environment for the database server. For example the parameter JDK_PATH comes with the default path C:\Program Files\IBM\SQLLIB\java\jdk and NUMDB parameter (default 8) allows 8 databases to be concurrently active.
      • Monitor: Snapshot monitor's related switches.
      • Parallel: For more advanced editions of DB2 for Linux, UNIX, and Windows family where DPF is used. This is not applicable to DB2 Express-C edition.
      • Performance: Performance related parameters
      Figure 11. Parameters configuration
      Parameters Configuration

      Alternatively, you can invoke this parameters configuration from DB2 Control Center action panel.

      Figure 12. Invoking parameters configuration - alternate way
      Invoking Parameters Configuration - Alternate Way
    • Application connections monitoring: One quick way of monitoring who and what applications are connecting to a database. The values that can be obtained from this page are :
      • Connection (either local or remote)
      • Database name
      • Application name - application that connect to the database listed in the same row.
      • Status
      • Application handler
      • Authorization ID
      Figure 13. Application connection
      Application Connection

      What's more, you could actually monitor the total locks acquired by an application and how the lock chain is inter-related to multiple applications. This way, effective locks usage can be monitored online and developers or administrators can monitor the possible deadlocks as well. For example, the following graphical notations are used for locks monitoring.

      Figure 14. Application lock chain
      Application Lock Chain

      Finally, when the situation arises, you can force an application offline. This is necessary for some maintenance tasks where no connection is allowed. Forcing an application offline is typically accomplished asynchronously.

    • View memory usage: Monitoring shared and private memory online
      Figure 15. Instance level memory monitoring
      Instance level memory monitoring
    • Activity monitoring: Monitors the application performance by monitoring resource consumption, application concurrency and SQL statement performance. There are few steps to setting up activity monitoring (depending on the option you choose):
      • Choose the database to monitor. Once the database is selected, click Next to continue.
        Figure 16. Choose the database to perform activity monitoring
        Choose the database to perform activity monitoring
      • Say we choose to monitor one of the four predefined monitor tasks, Tuning the dynamic SQL cache by customizing some of the selections. For this task, it is best to copy the system defined one to make a user defined one and click Continue. The picture below shows what we just did. Note also that the steps listed in the top left corner changes, depending on the monitor task you choose.
        Figure 17. Choose the database to perform activity monitoring
        Choose the database to perform activity monitoring
      • Selecting Tuning dynamic SQL statement cache radio box and clicking Next brings us to the following page. For simplicity's sake, I decided to monitor only three options from the predefined list. Any kind of monitoring is not without cost. Bear in mind the performance impact that you can incur when you have several monitoring tasks running concurrently.
        Figure 18. Select dynamic SQL cache reports
        Select dynamic SQL cache reports
  • DB2 database level management: There are a whole list of database level administration tasks which can be performed for DB2 Express-C databases. For example, a right click on the database on which you desire to perform a task shows you the following list:
    Figure 19. Tasks at the database level
    Tasks for Database level

    Though the list is long, we will focus on only the following few, as the rest are pretty much self-explanatory:

    • Authorities & privileges: User and group authorization management is accomplished in this page. By default, the user who created the database will be granted most authorization for that database. You can however, add authorizations to a user or group by choosing the authorities list.
      Figure 20. Managing authorities
      Managing Authorities

      Authorization isn't complete without privileges. Whilst authorization is given to perform a general task, privileges are required to access database objects in a particular way. To manage privileges using Control Center, use the User and Group Objects and under this icon, there are user and group management. A simple click on a user shows the options; all database schema objects privileges are presented and can be selected or de-selected for each user or group.

      Figure 21. Managing privileges
      Managing Privileges
    • Utilities management: Throttling utilities such as backup and runstat can be resource intensive. With the use of throttling, these utilities can be used even during peak hours. It ensures the workload during peak hours in a production system execute harmoniously with the throttling utilities. These utilities utilize or "steal" the CPU cycle whenever it is appropriate without impacting the current production workload. For example, during a backup exercise, you can choose the to set the throttle with a certain priority.
      Figure 22. Throttling management
      Throttling Management
    • Configuring automatic maintenance: Automatic maintenance is available to DB2 for Linux, UNIX, and Windows family since version 8.2. What it offers is a way to maintain activities such as backup, reorg and runstats automatically. Administrators only need to turn on the auto-pilot switch to make these activities self-managed. Typically, DB2 will decide if maintenance is required based on certain set of criteria. Should maintenance be needed, automatic maintenance will be carried out in the next maintenance window. This said, you can only bank on DB2 to kick start this maintenance as it has the intelligence to evaluate and execute whenever is required (you can't be over-anxious over this since DB2 knows best). In other words, even if a maintenance windows is specified, DB2 can choose to ignore the maintenance if it does not find a need to do so. Finally, you can set to notify the persons in charge for the maintenance activities. To configure the automatic maintenance, follow the few steps below:
      • Click Next to continue in order to turn on the automatic configuration. The other option is to disable the automatic maintenance.
        Figure 23. Automatic maintenance - Step 1
        Automatic Maintenance - Step 1
      • In this step, you have the option to configure both online and offline maintenance windows. Online maintenance window is made possible with the throttling features provided by DB2 Express-C.
        Figure 24. Automatic maintenance - Step 2
        Automatic Maintenance - Step 2
      • To add a contact for notification, you can search for SMTP servers available in your network, test the email address entered by selecting the Test button. Once assigned, users in the notification list will be notified of maintenance activities.
        Figure 25. Automatic maintenance - Step 3
        Automatic Maintenance - Step 3
      • Currently there are only three activities that can be automatically maintained; Backup, Reorg and Runstats. From this page, you can choose to run either on online or offline maintenance windows, choose to automate and notify.
        Figure 26. Automatic maintenance - Step 4
        Automatic Maintenance - Step 4
      • Finally, a summary page of what you have selected previously.
        Figure 27. Automatic maintenance - Step 5
        Automatic Maintenance - Step 5
    • Design Advisor: A tool to enhance the workload performance by deploying the intelligence DB2 built based on a set of criterias. To administrators, this design advisor is almost too good to be true. Haven't you have enough of those days where you spend days just to decide which indexes to create to optimize your query access? Though inherently used for other editions of the DB2 family products such as MQT and MDC optimization, for DB2 Express-C, this feature can be used to optimize index creation; such as when and what to create for an index. To select index feature to be evaluated by Design Advisor, follow the steps below:
      • Choose Indexes.
        Figure 28. Configuring Design Advisor - Step 1
        Configuring Design Advisor - Step 1
      • Enter the workload name and user id. You can modify the SQL in the workload by importing the SQL, or adding or changing them individually.
        Figure 29. Configuring Design Advisor - Step 2
        Configuring Design Advisor - Step 2
      • Select the tables to update the table statistics to make them up to date. Click Next for the next step as we just take the default.
        Figure 30. Configuring Design Advisor - Step 3
        Configuring Design Advisor - Step 3
      • You can schedule the recommendation calculation either immediate or later. In addition, you can limit the total time required to perform such calculation. We will take the default 10 mins.
        Figure 31. Configuring Design Advisor - Step 4
        Configuring Design Advisor - Step 4
      • From the recommendation drill-down such as cost before and after evaluation, we can make the decision which index to keep or reject.
        Figure 32. Configuring Design Advisor - Step 5
        Configuring Design Advisor - Step 5
      • Not only can we find what is required, we can determine what can be dropped as well as DB2 Design Advisor find them redundant for the workload specified. Typically, you need to be absolutely sure that dropping any of them will not impact the rest of the workload. Let's face it, there are times we just create unnecessary indexes and they are destined to be removed once Design Advisor figures those out.
        Figure 33. Configuring Design Advisor - Step 6
        Configuring Design Advisor - Step 6
    • Configuration Advisor: Most often or not, administrators cannot decide with insight the right parameters to use for database. Using Configuration Advisor with the minimal steps in the wizard, an administrator will have better recommendations than if he is to derive those parameters manually. Running Configuration Advisor should be scheduled periodically as database objects and system environment may change from time to time.
      • Choosing memory that can be used by database manager
        Figure 34. Configuring Configuration Advisor - Step 1
        Configuring Configuration Advisor - Step 1
      • Choose the OLTP transaction type as this is most applicable for DB2 Express-C edition.
        Figure 35. Configuring Configuration Advisor - Step 2
        Configuring Configuration Advisor - Step 2
      • Key in the transaction types; long or short. Also, estimate the total transactions per min.
        Figure 36. Configuring Configuration Advisor - Step 3
        Configuring Configuration Advisor - Step 3
      • The following step is to decide the size and number of log files to be created. A bigger log file size allows better performance but a longer recovery period.
        Figure 37. Configuring Configuration Advisor - Step 4
        Configuring Configuration Advisor - Step 4
      • To cater the table size, this step is required to make sure the parameters recommended will be accurate. For example, say if you have ten thousand rows initially for certain tables and they are increase to millions of rows, the old parameter recommended by Configuration Advisor will no longer be appropriate. It's recommended to re-run the Configuration Advisor in this scenario.
        Figure 38. Configuring Configuration Advisor - Step 5
        Configuring Configuration Advisor - Step 5
      • This step decides the MAXAPPLS parameter. For example, the total application connections allowed etc.
        Figure 39. Configuring Configuration Advisor - Step 6
        Configuring Configuration Advisor - Step 6
      • Isolation level can be set here or dynamically from the application codes. In this case, we set it to Cursor Stability as it is the most appropriate for a smaller environment. Note that DB2 Express-C edition is ACID compliant by nature. Isolation level decides how data is locked and isolated from other processes while the data is being accessed.
        Figure 40. Configuring Configuration Advisor - Step 7
        Configuring Configuration Advisor - Step 7
      • Finally, the suggested value will be shown as bold letters if it differs from the current one. As IBM's direction is towards automation for its database server, there is little doubt to cast for the recommended values. It may take a seasoned DBA just to come up with some of the configuration parameters, given the vast options and the inter-relationship of these parameters that need to be taken into consideration. This Configuration Advisor gives you a head start for maintaining your database at the optimal performance throughout. If you have heard of this feature but never ventured into it, it's advisible that you start using it sooner rather than later.
        Figure 41. Configuring Configuration Advisor - Step 8
        Configuring Configuration Advisor - Step 8
    • Configure prameters: This is a manual way to modify configuration parameters. Similar to instance level parameters, most database level parameters require database manager to be restarted in order to take effect. In database level parameters configuration, we are addressing the following categories (this is similar to instance level configuration, except that the parameter categories are different):
      • Applications
      • Environment
      • Logs
      • Maintenance
      • Performance
      • Recovery
      • Status
      Figure 42. Configure Parameters
      Configure Parameters

      A complete list of configuration parameters can be obtained online. Most often, you need to disconnect all appplications and restart the database to make the parameter setting effective.

    • Backup & restore with rollforward: The DB2 Control Center allows both offline and online backups. It also affords DBAs to rollforward database to a point in time or the latest state. For a complete offline and offline backup & recovery, refer to the writeup on DB2 Express-C edition Backup & Recovery. For example, to do a offline backup of a database, follow the steps below:
      • Click Next for the introductory page. There are few info that we can get from this page. For example, circular logging is being used and there is no automatic maintenance configured for backup.
        Figure 43. Offline backup - Step 1
        Offline Backup - Step 1
      • Add the fileysystem to backup the database to and click Next.
        Figure 44. Offline backup - Step 2
        Offline Backup - Step 2
      • Note if circular logging is being used, only offline database backup option is available and that you can only backup a database as whole. You can choose to throttle the backup activity and compress the backup image.
        Figure 45. Offline backup - Step 3
        Offline Backup - Step 3
      • We can leave the performance parameters as the defaults. Once we click Next, we can run the backup immediately or schedule it to run later.
        Figure 46. Offline backup - Step 4
        Offline Backup - Step 4

        By using DB2 Control Center, there is a complete control over offline and online backups including table space level backup. The restore options with roll forward are provided as well to allow admininistrators to quickly backup and restore their databases with just a matter of few mouse clicks. Best still, backup can be automatically maintained by configuring the Automatic Maintenance. By setting up backup to be automatically maintained, administrators' burden can be lessened as administrators will only be alerted in the event of abnormalities.

    • Explain Query and Statement History: Explain query is a way to tell the total cost and the query path taken from SQL execution. To have the accurate cost calculation, the runstat has to be up to date. An out of date statistics may render the entire explain query with its access plan useless.
      Figure 47. Explain Query
      Explain Query
    • Web services: In order to use the native XML capability with your database, you have to enable it. Touted as one of the biggest enhancements, DB2 Express-C edition version 9 provides native XML query and store capabilities. Also, with the native XML capabilities, the performance for XML increases significantly. To enable web services support for a database is easy; on the DB2 Control Center, right click on the database to choose Web Services and Enable Web Services. Once enabled, the following screen will be prompted.
      Figure 48. Enabling Web Services
      Enabling Web Services

Journal

Journal is integral part of DB2 Express-C edition GUI administration which keeps historical logs and events of tasks, database events, notifications etc that have taken place. In other words, the activities that have occurred are kept online for historical purpose.

You can start the Journal from either within Control Center or Start -> All Programs -> IBM DB2. There are four tabs associated with Journal. Let's look at each of them in more details.

  • Task History: all tasks either failed or succeeded will have entries in task history. By looking at the task history, administrators can tell which task has been executed successfully. In the event of failed tasks, administrators can re-schedule the task in Task Center. Other than the status of task executions, task history also provides the start, end time and duration.
    Figure 49. Task History
    Task History
    There is also a way to sort and filter the display. For example, to show the most recent executed tasks, choose the from the right corner arrow at the bottom of Journal panel. The pre-populated list of view can be customized further according to your taste.
    Figure 50. Select View in Journal
    Select View in Journal
    To further customize the look and feel of the display, click on the View button on the right corner at the bottom. You can customize the look and feel in the following ways:
    • By filtering - There are numerous filtering parameters you can select to narrow the display to a particular criterias.
      Figure 51. Filtering in Journal
      Filtering in Journal
    • By sorting - To customize sorting, move them to the right panel.
      Figure 52. Sorting in Journal
      Sorting in Journal
    • By customizing columns Similar to customizing sorting, move the desired columns to the right panel.
      Figure 53. Column Customization in Journal
      Column Customization in Journal

    Last but not least, there are few options that you can perform on an individual task such as editing the details for the task, show statistics, and so on.

    Figure 54. Task History Options
    Task History Options

    For example, the diagram below shows the tabs available for task details edit. We will leave the rest for you to explore.

    Figure 55. Editing Task Details
    Editing Task Details

    Finally, you can save the new customized view with a new name such as Backup-July 2006. For the rest of the tabs, database history, messages and notification logs, we will only briefly mention them without examining the details.

  • Database History:All database actions will be logged as entries in database history. Start and end date, start and end time etc columns are available. Customization on sorting, filtering and columns can be accomplished as well.
    Figure 56. Database History
    Database History
  • Messages: Customization on sorting, filtering and columns can be accomplished as indicated earlier.
    Figure 57. Messages in Journal
    Messages in Journal
  • Notification logs: Customization on sorting, filtering and columns can be accomplished as indicated earlier.
    Figure 58. Notification Logs
    Notification Logs

Task Center

Task Center is the DB2 GUI tool to use for scheduling a particular task or a set of tasks based on the success or failure of a task. Task Center allows administrators to perform the following tasks :

  • DB2 scripts
  • Operating system scripts
  • Grouping of tasks

Task Center affords the administrators to set notification via email or sending notification to DB2 Journal (see the discussion above). It has the grouping capability where multiple tasks will be executed based on the success or failure code returned. Success and failure code is set using success code sets. This code set defines a range of codes to be considered successful so whenever a return code is outside of the defined range, that particular will be considered a failed task. By default a return code set 0 is considered successful whilst all the rest are failures. This way, you can subsequently disable or run tasks based on the previous return codes.

Figure 59. Creating a new task in Task Center
Creating a new task in Task Center

As you can see from the digram below, there are many options an administrator can perform after creating a new task. For example, change, enable or disable the schedule, notification and task actions. Also, you can categorize and group multiple tasks together. Finally, history and statistics are available for audit trail purpose.

Figure 60. Task Options in DB2 Task Center
Task Options in DB2 Task Center

Though you can create new task from the DB2 Task Center, usually performing a task such as backing up a database using the DB2 Control Center ends with task scheduling. This step typically create an entry in the task Center, so you don't have to emphatically create new tasks from DB2 Task Center for simpler tasks. For example, at the end of backup wizard is the following task scheduling:

Figure 61. Scheduling Task in Backup
Scheduling Task in Backup

And should you need to modify the time and date, click on the Change button. You can either set it to run repeatedly or run once.

Figure 62. Scheduling Task in Backup
Scheduling Task in Backup

Monitoring tools

Monitoring system performance; be it network, server or database level performance, should be a daily part of life for administrators whose primary job it is to ensure system health. With the proper network bandwidth and hardware requirements, yet without the proper database level tuning and monitoring, a badly written SQL statement can bring the entire system to a halt. As well, with database object monitors, you can make efficient usage of indexes and predict tables growth more accurately.

Fortunately, DB2 Express-C deals with this issue by providing monitoring tools including the Event Analyzer, Health Center, InDoubt Transaction Manager and Memory Visualizer. These tools are provided with the same install (not a separate download) and stipulate "good enough" level of details in monitoring as most often, for small and medium database usage where non-mission-critical applications are running, these embedded monitoring tools are all you need to tackles your performance requirements. For a more comprehensive tool to monitor your DB2 databases (any flavour), you may want to opt for DB2 tools (a separate purchase) such as the IBM DB2 Performance Expert.

There are two monitoring methods administrators can engage with in monitoring namely Snapshot and Event Monitoring with the former works like a camera shot, freezes database conditions in a specific point whilst event monitor is for eventful occasions where database logs info for better analysis. Both methods allow output to be directed either to file or tables and can be presented in various ways such as via GUI, CLP or custom codes. For example, snapshot output can be re-directed to standard output using the sample codes provided.

In this article, we will abide with Event Monitoring. For complete guide, refer to System Monitor Guide and Reference.

Event Analyzer

Event monitors can be created from DB2 Control Center, whilst the actual analysis takes place in Event Analyzer. Event Analyzer is the means to monitor event monitors which can be database objects such as tables, buffer pools, table spaces, and application level monitors such as connection, SQL statments, deadlocks etc.

The following few steps bring you to creating an event monitor quickly.

  • To create a event monitor is simple; right click to Create on Event Monitors icon from DB2 Control Center menu tree.
    Figure 63. Creating a Event Monitor - Step 1
    Creating a Event Monitor - Step 1
  • For simplicity sake, we pick three events to monitor; table, table spaces and deadlocks. Note the list shown are the current events that can be monitored upon; of which connections, transanctions and statements can be filtered based on parameters such as authorization id, application id and application name.
    Figure 64. Creating a Event Monitor - Step 2
    Creating a Event Monitor - Step 1
  • Once an event monitor is created, an entry will be registered in DB2 Control Center.
    Figure 65. Registered Event Monitor in DB2 Control Center
    Registered Event Monitor in DB2 Control Center

To analyze the event, there are at least two ways; start up the Event Analyzer from Start->All Programs -> IBM DB2 -> Monitor Tools -> Event Analyzer or from DB2 Control Center action panel. Upon starting up, pick from the drop down list for the monitor to analyze (you can pick one at a time).

  • Start the Event Analyzer.
    Figure 66. Choosing the Event Monitor to analyze
    Choosing the Event Monitor to Analyze
  • From the event monitor start time or duration, we can either view all periods or zoom into a particular window to analyze a problem.
    Figure 67. Selecting monitoring period
    Selecting Monitoring Period
  • Once we selected the period we want to look into, for example, select table from theNavigate to drop-down list in the left corner at the bottom.
    Figure 68. Drill Down on Table Event Monitor
    Drill Down on Table Event Monitor
  • The Table level drill down shows the following data elements such as data_object_pages which represents the base table(s) size with index_object_pages, lob_object_pages, and long_object_pages represent index, lob and long objects space consumption respectively.
    Figure 69. Table Level Data Elements
    Table Level Data Elements

One thing to note about event monitoring is that you don't turn them on just for the sake of turning them on or do it out of curiousity, as monitoring does impact system performance.

Health Center

Health Center coupled with the automatic maintenance features are part of IBM's direction to implement automatic, self-managed database environments where administrators can be free to perform more important tasks. It applies the management-by-exception model whereby thresholds are set for database objects. During a threshold breach, an alert will be sent to administrators in charge. This way, administrators will just let the database system run until they are alerted. Health Center is an intricate piece as it knows what to monitor and how data are related to each other in DB2. Simply put, when an exception occurs, it evaluates the data, makes a proper diagnosis, and suggest an appropriate solution. As well, administrators can read details regarding the suggestions before accepting them. Since Health Center is a crucial piece, we will spend more time on it.

You can either start the Health Center from within the DB2 Control Center or select Start->All Programs->IBM DB2->Montitoring Tools->Health Center. From the main page, a few points can be observed quickly (refer to Figure 70),:

  • Health Center Status Beacon: The colourful icons at the left panel shows the alert states of the evaluated objects. For example from left icon to the right :
    • Objects in Alarm State
    • Objects in Alarm or Warning State
    • Objects in Any Alert State
    • All Objects
  • We can configure health indicator, automatic maintenance (we explored this topic earlier) and manage throttled utilities by right click on a database.
  • We can drill down on each alert. Simply right click on an alert, and three options will be given as a result :
    • Show Details: This is to show details on the alert.
    • Recommendation Advisor This allows DB2 to recommend the appropriate action to adminstrators to choose from.
    • Disable Evaluation: This option turns of the evaluation.
  • The view can be customized with filtering so that in the main page, adminstrators can segregate the object categories and columns display in order to have better view.
  • Notification log: Prompting notification log from within Health Center is possible.
  • Alert notification: Configuring alert notification for adminstrators to receive the alerts.
Figure 70. Health Center - Main Page
Health Center - Main Page

DB2 Express-C edition comes with a set of pre-defined health indicators which includes categories such as application concurrency, Database, Memory, and so on. In the remaining discussion, we will fix the automatic storage utilization breach and modify the alarm threshold to 85 (from the default 90). The following steps show how we can accomplish these in just a matter of few mouse clicks.

  • To fix the automatic storage utilization problem, highlight the alert and right click to choose Recommendation Advisor.
    Figure 71. Fixing Health Indicator breach - Step 1
    Fixing Health Indicator Breach - Step 1
  • The wizard first states what the health indicator is really about by providing enough description. Threshold values for warning and alarms are provided as well. You can view the history details as it captures (by default 10 minutes) so that the administrator can have a better idea of the history of the alerts. You can also read the additional information to obtain a feel for the growth rate.
    Figure 72. Fixing Health Indicator breach - Step 2
    Fixing Health Indicator Breach - Step 2
  • Clicking Next brings you to two possible options to solve the problems. An administrator may select to show all possible recommendations, or simply choose one that suits his environment best. In our case, we will just choose the first option to add additional storage path to the database.
    Figure 73. Fixing Health Indicator breach - Step 3
    Fixing Health Indicator Breach - Step 3
  • Click on Launch to open up the add a new storage path. The DB2 Health Center has the intelligence to show action dialogs according to context. For example, in our case, the add new storage path windows will open up. However, if we were to fix heap size breach, you will be shown to launch Memory Visualizer instead. DB2 Health Center retains the rights to give administrators what they need to see based on the recommended solution they chose. This way, administrators can reduce the total time required to fix a problem.
    Figure 74. Fixing Health Indicator breach - Step 4
    Fixing Health Indicator Breach - Step 4
  • Add a new storage path, for example E:\ to the existing C:\.
    Figure 75. Fixing Health Indicator breach - Step 5
    Fixing Health Indicator Breach - Step 5
  • Click OK and a succesfully screen such as the one below will be shown. Continue by clicking Next.
    Figure 76. Fixing Health Indicator Breach - Step 6
    Fixing Health Indicator Breach - Step 6
  • Click Close to accept the change. Refresh the health indicator data. All alerts should no longer be there.
    Figure 77. Fixing Health Indicator Breach - Step 7
    Fixing Health Indicator Breach - Step 7
  • Now, we proceed to make the alarm threshold to 85 (from the default 90). To configure the health indicator setting, go to Selected->Configure->Health Indicator Settings. Note that next to Health Indicator Settings is the Alert Notification configuration.
    Figure 78. Configure Health Indicator Settings - Step 1
    Configure Health Indicator Settings - Step 1
  • Click on Object Settings as we want to modify it at the individual level. The Global Settings option allows the settings to take effect globally within the same instance. However, the precedency will be on Object Settings; if both are set, Object Settings will take precedency.
    Figure 79. Configure Health Indicator Settings - Step 2
    Configure Health Indicator Settings - Step 2
  • Navigate down the tree to the database, Sample. Click OK to continue. The parameter page will show up.
    Figure 80. Configure Health Indicator Settings - Step 3
    Configure Health Indicator Settings - Step 3
  • Right click on Database Automatic Storage Utility, and select Edit. Note also, this is the page where you can see all parameters; some are with evaluation turned on whilst others have it turned off by default.
    Figure 81. Configure Health Indicator Settings - Step 4
    Configure Health Indicator Settings - Step 4
  • Change the Alarm threshold to 85. Note that evaluation box is checked and sensitivity is 0 mins. Should you need to run scripts to provide remedy to the problem when either the warning or alarm threshold breach happens, you can use the action tab to define a series of script or task actions to run. Finally, if you think you need to know more about this particular health indicator, click on the Tell Me More button for more explanation.
    Figure 82. Configure Health Indicator Settings - Step 5
    Configure Health Indicator Settings - Step 5

For a complete health indicator identifier, refer to Health indicators summary in the System Monitor Guide and Reference.


Conclusion

This article starts with a general overview of MySQL GUI administration capabilities with a table drawn to compare with DB2 Express-C edition. The rest of the dicussion highlighted the features and functions of the DB2 Express-C GUI administration tools, illustrated by explanations to give readers a good feel for how things are accomplished in the DB2 world. DB2 Express-C comes with a rich set of intelligent GUI administration tools for object management, performance monitoring, automatic configuration, and more, to lessen the burden for administrators and free them to perform other critical tasks.


Disclaimer

This article is written to the best of the author's knowledge. Should you find any discrepancy, please feel free to contact us.

Resources

Learn

Get products and technologies

  • 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 Edtion 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=154861
ArticleTitle=Leverage MySQL skills to learn DB2 Express, Part 3: DB2 versus MySQL graphical user interface
publish-date=08172006