|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:
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
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.
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
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.
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.
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 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