DB2 User Interfaces
Home Tutorials Reviews Weblog
Feature: IBM DB2 Version 9.1 has 3 major interfaces for Development and Administration
Motivation: 3 major interfaces into DB2 are easy to use and targeted for different tasks


Database development and operation now permeate all aspects of an organizations. Many of these databases are of course embedded into either BI applications through data warehouses or BAM-Business Activity Monitoring or through application stacks like Financial and ERP-Enterprise Resource Planning systems. In these cases the applications provide the front-ends and GUI interfaces to the underlying databases - and in fact that is one of their principle value-adds to organizational information flow.

But more than ever before the simple administration and operation of databases requires a step up from the traditional command line interface beloved by many sys ops and or DBAs-DataBase Administrators. Likewise on the design and development side, there are a whole range of tools for modeling, database maintenance and performance monitoring/control that use sophisticated graphical, charting, GUI controls to keep tabs on database designs and development activities(see, for example, our overview of database modeling and design tools here).

Again there are a range of third party tools, but the major database vendors are providing some of those key control and development frameworks with their databases. IBM's DB2 is no exception - DB2 has 3 major interface into the database:
1)the Control Center which is a Java GUI app that runs on most Linux, Windows, and most major Unix clients workstations and has all of the administration and control functionality of the database at its disposal;
2)a DOS-like command line interface for character mode access favored by developers and some administrators; and
3)an Eclipse-based Database Developers Workbench that permits users to use a variety of data design and development tools to both monitor and develop DB2 applications.
This is a well balanced set of interfaces into DB2 with one notable missing piece – there is only limited Web-based interfaces from IBM (but several from 3rd parties). This review looks at these interfaces and examines their utility for different users.


The Control Center is a Java application which shows off well the versatility of a Java GUI application. The Control Center has the same look and feel for a variety of different PC working environs including Linux, Unix and Windows. Given that the Control Center's basic layout started with DB2 version 7 this means DBAs and developers have a common and consistent interface to work with. However, causal end users may find the interface some what intimidating – a power user familiar with say Windows Explorer and its treeview of file folders might feel more comfortable with the Control Center. Power users will certainly like the ability to point at different objects on the database treeview at the left and instantly all the data objects contained appear in the upper Objects Pane and below all the properties of the specific object. Also note that DB2 provides a list of DBA maintenance commands for the database including Start/Stop, Backup, View Storage, Monitor DBHealth, and Maintenance. It is nice to have all the commands clustered together.

Notice also in the top left there are two tabs – Object View which is currently active and Command Editor1 which contains a SQL command and results. This is another nice feature of the Control Center UI .Users, like in a tabbed browser, can stack together several tabbed queries into a database. This is particularly helpful when doing complex multiple join or sub-query retrievals. One can debug the parts of the query and then cut and paste into the master query.

There are several other nice UI touches in Control Center. The Selected menu item has the Create... sub-menu option. If you are pointing to a TABLE it will bring up the Create Table Wizard, to VIEW then the Create Views Wizard, etc. In this case, a DATABASE is the data object being pointed to so clicking on the Create.. sub-menu option would start the Create Database Wizard. The Wizards use a series of buttons down the left side that are grayed out until you have filled in the proper information. The fields that must be filled in are outlined in red. There are hover tips that pop up over key fields or buttons if you leave the mouse over an object, etc, etc. In sum, IBM developers have pulled out all the stops in making Control Center not only easy to use but also self-guiding on how to operate as much as possible.


Command Line into DB2

At first I thought how so gauche and antediluvian – a command line interface into DB2. Who would want to use that dinosaur ? But then I considered how often I used the MySQL command line interpreter despite the improved interfaces there. And then I remembered how fast the operations can be – character mode UIs were cutoff too soon by Mac and Windows. But the unbelievably fast response time is perfect for queries and utility operations where one wants quick results.

So I tried DB2.exe, the CLP Command Line Processor. I was not disappointed. The response time is blisteringly fast. The restraint is my typing speed. But as in so many CLP's use of the up arrow key allows users to retrieve previously entered commands. And of course with SQL and command utilities you are constantly refining the command by adding GROUP BY, HAVING and other clauses including XML XQuery processing. And then refining the results. I found myself working with the CLP much more than I had expected.

But there was a disappointment. There must be some way to run a command file of SQL and other commands from the CLP command line. There appears to be 3 ways to run the DB2CLP – 1)interactive mode which is most convenient but only allows CALLs to stored procedures; 2)batch mode which enables one to mix OS (in this case Windows) prompts with “db2” prefaced CLP commands; and 3)file processing mode which is “db2 -ffilename” which does allow external DB2 .clp and other command files to be executed. However. The db2look utility generated .clp command files would not execute to completion. Apparently when db2look puts in characters like “;” statement delimiters “db2 -ffilename” does not like such characters and literally vomits back a thousand error messages – but blisteringly fast! This may be a bug to be worked out.

Despite the command file bugs, I found myself in CLP mode a lot . It is simple to use and as noted very, very fast.



Developer WorkBench(DWB) is the Eclipse workbench adapted for DB2. Because Eclipse is a Java app that means it runs on most OS platforms. It allows users to connect to most editions of DB2 including DB2 Viper with XML support, IBM Cloudscape, and Apache Derby databases. DWB-Databae WorkBench is designed to:
- Create, view, and edit database objects (such as tables and schemas)
- Then explore and edit data in those tables
- Visually build SQL and and XQuery statements with Wizard-like support
- Develop queries and routines with XML data
- Also develop and deploy stored procedures, user defined functions (UDFs), and scripts
- Debug those SQL and Java stored procedures
- Develop SQLJ Java-based applications - allow simple ETL(extract, transform, load ) ops
- Allow collaboration with team members using CVS, ANT and other resources
The Help that comes with DWB is just good enough - offering some exercises to illustrate how to do the above tasks. But there are missing links on how to use DWB and even more vexing is how to integrate it into your existing Eclipse installation if you already have one.

Another difficulty with Eclipse is that it has become so rich (some would say rococo); that like working with Visual Studio - you pay a steep learning curve price. To its credit, DWB does take advantage of Wizards and Eclipse's Perspectives to simplify working with DB2 database and tables. This reviewer will be on the look out to see in the application development set of reviews how IBM helps developers cope with this complexity. Also expect an upcoming review on DWB itself.

Summary of the DB2 UIs

IBM's developers have provided a GUI tool for DBAs and administrators, a traditional command line window for power developers and DBAs, and an Eclipse-based Developers Workbench for a broader base of developers and some power users. All of the tools have been retooled for the new native XML with, for example, XQuery Builder in Control Center and Database Developers Workbench. There are IBM and 3rd party tools that provide DB2 interfaces as well:

IBM's list of DB2 support tools
BMC - SmartDBA and other database utility products
Embarcadero - DBArtisan Professional - wide range of mgmt,monitoring and development
Quest/ToadSoft - Toad for DB2 - also wide range of admin, monitoring and developer tools

What is missing from this list of tools are applications from IBM that make access to the database easy for end users or that provide a Web based interface into the DB2 Viper's new hybrid XML + RDBMS goodies. End user access is primarily from 3rd party tools. However that does not take away from the Herculean effort IBM has made to update and refine its DBA and developer work tools for the massive update that is Viper. As we shall see in our reviews of Installing DB2 Viper and Loading DB2 Viper with Data the new DB2 Wizards and UI tools make approaching and using DB2 not just doable but very approachable. On that note it is worthwhile recommending that users download at least one of the two versions of DB2.
DB2 Express C version - the free version must have "C" attached
DB2 Developers Edition - $1250US for complete set of DB2 tools and utilities
What is the difference between DB2 Express and DB2 Express C? Go to the link to find all the details but here in capsule form is what is different according to IBM:
DB2 Express is the fee-based version of DB2 Express-C. It includes the following extended features not available with DB2 Express-C:
- Warehouse Manager Tools & servers
- Extender support
- Informix Data Source Replication
- Replication Data Capture
- APPC and NetBIOS support
-
DB2 Express offers fee-based 24x7 support, while DB2 Express-C is only supported through a free online community forum.
So the difference is not just support as has been implied in some quarters but also the extent of the features - notably extenders and data warehouse support.

Likewise DB2 Developer Edition goes well beyond DB2 Express adding:
DB2 Personal Edition - Single user full-function RDBMS, built-in replication remotely managed
DB2 Workgroup Server Edition - workgroup version with data warehousing features
DB2 Enterprise Server Edition - version 8.2 Enterprise packaging
DB2 Connect Personal Edition - provides APIs and infrastructure to enable direct connectivity from Windows and Linux desktop applications to mainframe and iSeries database servers.
DB2 Connect Enterprise Edition - broader 2-way connect among DB2 servers
DB2 Data Links Manager - allows the management of files residing outside the database as though they are logically within the database.
DB2 Net Search Extender - offers fast full-text retrieval with many concurrent users
DB2 Spatial Extender - spatial/GIS data integration with DB2 UDB 8.1 or earlier
DB2 Warehouse Manager - legacy ETL product superseded by WebSphere DataStage.
DB2 Audio, Image, and Video Extenders - a set of database media objects
This line up of features is under constant change. We just removed the DB2 Intelligent Miner tools which IBM has withdrawn support for. Much of the Mining and OLAP capabilities have been incorporated into the DB2 DataWarehouse Edition.

In sum, IBM has given developers a broad set of choice for not just UI interfaces into DB2 but also in the broader development space. From "free and I will do it myself" to more features and support for a modest price to full developer edition at at full price, IBM is setting the table with a broad set of development choices not just in database but also UI interfaces.




Top of Page  Home  Tutorials