DB2 Express-C, the developer-friendly alternative
Why DB2 Express-C?
DB2 Express-C is based on the same core technology as the DB2 Universal Database™ (UDB) Express Edition V8.2.2 product. DB2 Express-C is available on Linux® and Windows® platforms (32- and 64-bit) as a no-charge download from IBM. IBM introduced DB2 Express-C, a version of DB2 Universal Database Express Edition (DB2 Express), for the community on January 30, 2006. It offers a solid base to build and deploy all applications, including C/C++, Java, .NET, PHP, and more.
IBM Cloudscape™ and now Express-C are both available from IBM with a no-charge license, and they can be used for developing and deploying applications. Since both of these database servers are based on open standards, such as SQL and JDBC, it is easy to migrate your Java applications and databases between these two data servers. If you are using Cloudscape or Apache Derby today, you may want to try the no-charge utility, called the Cloudscape Workbench to migrate your database to DB2 and give the new DB2 Express-C a spin. DB2 Express-C has a larger download and disk footprint than Cloudscape, but DB2 Express provides many more tools. It also provides additional features, such as full support for .NET applications, and it is ideal for PHP applications. DB2 Express-C can fully utilize up to two CPUs and 4GB of memory, and the size of the database is not restricted. DB2 can distribute a database across many disks to improve scalability and performance.
DB2 Express-C is a production-ready database server that is based on innovative technology that has been enhanced with every new release of DB2 on Linux and Windows. DB2 has been available on Windows since 1995 and on Linux since 1999.
You may ask, "What if my database server requires more than 4GB of memory and 2 CPUs to power my applications?" You can easily move to UDB Enterprise Server Edition (ESE) on Linux, Windows, or UNIX®. DB2 ESE is a proven large-scale, data-serving platform capable of supporting terabytes of data. DB2 ESE installations can optionally add the Database Partitioning Feature (DPF) to perform parallel database operations. Moving from DB2 Express-C to other DB2 editions is simple since the core database engine is the same.
With the introduction of DB2 Express-C, a new interactive forum has been introduced, and it is staffed by DB2 experts from IBM. This forum is a great place to get your questions answered as you discover this new, no-charge data server. DB2 Express-C has a complete set of manuals available in either PDF format or in a comprehensive searchable Web site known as the DB2 Information Center. The DB2 Information Center is continually being updated online, and you can optionally download the latest version of the DB2 Information Center and install it on a dedicated server. DB2 UDB product manuals are available. Note that the DB2 Information Center is common for all editions of DB2 UDB on Linux, UNIX, and Windows, so you will need to understand which of the advanced capabilities, such as the Database Partitioning Feature (DPF), would not be applicable to a DB2 Express-C environment.
If you plan to use DB2 Express-C on Linux, it is a good idea to check the latest Linux distributions that have been validated. Another great reference for DB2 on Linux information is the DB2 Universal Database Version 8.2 for Linux HOWTO. (See "Related topics.")
Up and running
Installing DB2 Express-C
The minimum memory requirement for DB2 Express-C is 256MB without graphical
tools or 512MB with graphical tools. The machine used for this article was
more than adequate with 2GB of memory. For this article, I download DB2
Express-C for Windows (32-bit). The download was 391MB, and the
installation quite simple. Once the zip file has been inflated (on my
system the inflated directory was not much larger -- only 412MB), execute
setup executable. This will launch the DB2 Express
Launchpad, as shown in Figure 1. The only
questions asked by the graphical installer are the location where you want
to install DB2 Express-C and the userid and password of the DB2
Administration Server. I performed a typical installation, which
includes the development tools and libraries. The entire installation took
less than four minutes.
Figure 1. DB2 Express-C Launchpad
Following the installation, a new window (First Steps, as shown in Figure 2) is displayed. This is a great time
to go ahead and create your first DB2 database. There is a sample database
provided with DB2 Express-C with the somewhat obvious name of
SAMPLE. We will use some of the tools provided with DB2
Express-C to configure the SAMPLE database for a production-ready
environment and resolve a performance issue as one of the tables grows to
over a million rows of data.
Figure 2. DB2 First Steps
DB2 Express-C is very much a low-maintenance database server. It comes with a complete set of graphical and command line tools to help database administrators (DBAs). The most commonly used graphical tool is known as the DB2 Control Center, and the most commonly used command line tool is known as the DB2 CLP (Command Line Processor). Neither of these tools have particularly exciting names, but there are many innovative features lurking behind them, as you will soon discover.
The graphical tools provided with DB2 Express-C can be grouped into various categories, as shown in Figure 3. You will find the DB2 Control Center from the General Administration Tools category. Most of the other graphical tools are accessible from within the DB2 Control Center.
Figure 3. DB2 tools
The DB2 Control Center, as shown in Figure 4, provides the DBA with an operational status of the database. In this example, the SAMPLE database is 25MB in size, and it has not yet been backed up. Automatic maintenance has not yet been configured either.
Figure 4. DB2 Control Center - SAMPLE database before automation
The DB2 graphical tools wizards can be used to quickly learn how to perform common administration tasks with DB2. Each object from the DB2 Control Center can be manipulated by performing a right-click on the object from the object tree. There are many wizards, as shown in Figure 5, to help new DB2 DBAs to perform common tasks, such as creating a backup database image and restoring previous images.
Figure 5. DB2 tools wizards
In Figure 6, you can see how the SAMPLE database has grown over time to 537MB. The database has been configured for automated backups and maintenance. There is an alert condition that has been identified by DB2 Express-C, as shown in Figure 6. We will now examine the condition and resolve it quickly.
Figure 6. DB2 Control Center - SAMPLE database AFTER automation
A unique feature of DB2 is its ability to actively monitor its environment and notify the DBA of unusual conditions that should be addressed to achieve an ideal environment. In Figure 7, DB2 has detected a large number of lock escalations per hour for the SAMPLE database. DB2 ensures transaction consistency using locks on database objects. In this example, DB2 performed 1150 lock escalations within an hour time period. DB2 was unable to maintain a large number of row locks with the memory allocated to the DB2 server. The condition can be easily resolved by increasing the DB2 server memory for lock management. Corrective actions can either be performed by a DBA or they can be automated. This DB2 health monitoring capability is available with DB2 Express-C through the larger scale DB2 Enterprise Server Edition (ESE) environments. DB2 Express-C will always maintain transactional consistency for your applications. Each transaction is logged to ensure the integrity of the data in the database.
Figure 7. DB2 Health Center - Lock escalation
Database application performance can directly impact your customers' satisfaction with your solution, and achieving optimal performance is considered a combination of art and science. DB2 Express-C provides a full compliment of tools, both graphical and non-graphical, to ensure you can easily achieve a high performance and reliable application. In this scenario, we will attempt to determine the highest paid departments in our company. To achieve optimal performance, we will use a combination of tools provided with DB2 Express-C.
The tools used in this scenario include:
- Visual Explain
- Design Advisor
- Activity Monitor
The db2batch utility is a great tool for analyzing the runtime performance of queries. In this scenario, we will be using the following SQL statement to perform our analysis:
SELECT DECIMAL(AVG(salary),12,2) as average_sal, location, deptname FROM staff a, org b WHERE b.deptnumb=a.dept GROUP BY dept,location, deptname ORDER BY average_sal DESC
STAFF table has grown to over a million records, and the
query is therefore taking longer than it took in the past. In Figure 8, note that the elapsed execution time
for this query is 18 seconds (and the New York head office gets paid the
most -- this might not be a surprising revelation).
Figure 8. db2batch - Query execution
Figure 9 shows that an expensive table scan would be performed on the STAFF table and then a Hash Join operation would be performed based on the data from the ORG and STAFF table. There are no indexes on these tables for DB2 to consider using during the execution of the query. We will use the DB2 Design Advisor and ask DB2 for an appropriate new index on the ORG or STAFF table to improve the performance of this query.
Figure 9. Visual explain - Query analysis
Figure 10 displays the feedback from the DB2 Design Advisor. It is recommended to create an index on the STAFF table using the DEPT and SALARY columns. The query workload for the Design Advisor can be provided in a file, or it can be captured during the execution of applications.
Figure 10. DB2 Design Advisor - Index recommendation
Figure 11 demonstrates that the index has indeed been beneficial for the execution of this query. It shows that the elapsed execution time for the query has improved dramatically, it now takes only 7 seconds to complete.
Figure 11. db2batch - Query execution improvement
Figure 12 confirms that the new index on the STAFF table is being used in the query access plan. The estimated cost of executing the query has decreased dramatically, also.
Figure 12. Visual explain - Query plan change
Finally, you may wish to determine how the overall system resources are being used during the execution of this query. A new tool added in DB2 UDB V8.2, called the Activity Monitor, uses various SQL functions and procedures to retrieve performance data for a live system for analysis. In Figure 13, you see that the query performed two sorts and spent less than 12 seconds to complete. The Activity Monitor has many additional reports available to analyze a DB2 Express-C environment in production.
Figure 13. Activity Monitor - Runtime analysis
Developers, developers, developers
A typical installation of DB2 Express-C provides the necessary drivers and interfaces to develop C/C++, Java, and .NET applications. If you are interested in developing PHP or Perl applications with DB2, there are drivers, the PHP, and Perl available. These PHP and Perl interfaces have been developed and are maintained by engineers within the DB2 team. DB2 supports many database server-side programming languages, including stored procedures, triggers, and functions (table and scalar). DB2 stored procedures can be written using C/C++, COBOL, Java (JDBC or SQLJ), .NET (CLR compliant languages), and SQL Procedure Language (SQL PL). The choice is yours regarding stored procedure language of choice; however, the most commonly used languages are Java and SQL PL.
SQL Procedure Language (SQL PL) stored procedures are considered native, as they are stored as objects within DB2 databases on Linux and Windows. SQL PL has evolved as the only ANSI SQL standards-based stored procedure language. SQL PL is supported throughout the entire DB2 family of database servers from Linux, UNIX, Windows, iSeries, and z/OS. DB2 Express-C provides a very useful graphical tool, Development Center, to manage the development, testing, and deployment of SQL stored procedures, including Java and SQL PL procedures. In Figure 14, the DB2 Development Center is used to build, test, and deploy SQL PL procedures. The procedure shown here is called high_rollers, and it will be stored and executed within the DB2 Express-C. Since stored procedure execution can be tightly controlled within DB2, it provides a convenient technique of securing access to DB2 data to users.
Figure 14. Development Center - SQL PL
Developing Java applications with DB2 is easy, as every DB2 client and
server provides a JDBC 3-compliant (type-4) driver. There are actually two
different JDBC drivers currently included with DB2. The original driver
(db2java.zip) was sometimes known as the Application (type 2) and Network
(type 3) drivers, based on their architecture. The newer and more flexible
JDBC driver (db2jcc.jar) can be used in type 2 or type 4 modes, and it has
been used in the latest Java Client/Server industry benchmarks, such as
SPECjAppServer 2004 with DB2 and WebSphere® Application Server (see
"Related topics"). The DB2 driver for JDBC
(db2jcc.jar) is bundled in the DB2 Run-time client. If your application
will be pure Java then you could embed the
db2jcc.jar in your
CLASSPATH. These files are located in the <DB2
Installation location>\SQLLIB\java directory. Java 2 Platform
Enterprise Edition (J2EE) servers, such as IBM WebSphere Application
Server along with DB2 data servers, provides a mission-critical
distributed application infrastructure to power scalable applications.
PHP (recursive acronym for "PHP: Hypertext Preprocessor") is a widely-used, open source, general-purpose scripting language that is especially suited for Web development and can be embedded into HTML. PHP has been rapidly adopted worldwide as an excellent choice for scalable Web applications. DB2 support for PHP developers has been recently enhanced with the IBM-supported DB2 native extensions for PHP (see "Related topics"). PHP is an open source scripting language that powers millions of Web applications, and PHP continues to evolved since it was first introduced back in 1995. The DB2 extensions for PHP are provided as source code . You can also find the necessary library for Windows, if you would like to use this extension on Windows without compiling the code (see "Related topics").
One of the easiest methods of getting a DB2 environment up and running with PHP is to download the no-charge Zend Core™ for IBM, developed by IBM and Zend Technologies. Zend Core for IBM is a PHP development and production environment product that includes tight integration with DB2, the IBM Cloudscape database server. It delivers a rapid development and deployment foundation for database-driven applications. Support options are available for Zend Core for IBM from Zend Technologies when you decide that you require a fully supported Web stack including PHP support and DB2 support. Zend Core for IBM embeds a Cloudscape database server and a no-charge version of DB2 Express to ease the deployment. Support is available for the bundled DB2 Express product if you decide to purchase a full DB2 Express Edition license at a later date. In Figure 15, the Zend Core for IBM administration Web console shows that the native PHP extension for DB2 has been enabled properly.
Figure 15. Zend Core for IBM
DB2 Express-C provide a DB2 .NET 1.1 Data Provider and an excellent set of add-ins for Microsoft Visual Studio .NET 2003. The IBM explorer and the integrated DB2 help is within Visual Studio is shown in Figure 16. When DB2 Express-C is installed it will check for the existence of Visual Studio .NET 2003 and if it is present it will add DB2 specific capabilities to Visual Studio. These add-ins reduce the application development time for .NET applications. With the DB2 UDB v8.2 release .NET (CLR - Common Language Runtime) stored procedure support was added as a new option for .NET savvy developers.
Figure 16. DB2 add-ins for Visual Studio .NET 2003
There is also an optional no-charge download of the DB2 add-ins for Visual Studio 2005 - Developer Release. This Developer Release includes a DB2 .NET 2.0 Data Provider and a new set of add-ins for the recently released Microsoft Visual Studio 2005 IDE.
C/C++ and more
DB2 Express-C provides two primary C APIs for data access, DB2 CLI, and Embedded SQL. The most commonly used C/C++ API used today is the DB2 Call Level Interface (CLI), and it is based on the X/Open CLI standard. This API closely resembles the Microsoft ODBC (Open Database Connectivity) API used in many Windows applications. The Embedded SQL approach for DB2 data access involves the use of an application preprocessor to translate and replace all of the SQL references with native access interfaces to the DB2 server. Embedded SQL can be very effective when the SQL statements are well defined and known during application development, as the SQL can be validated, and an access plan can be created and locked in during development, resulting in improved application performance. Often the SQL statements are dynamic in nature, and dynamic SQL access techniques, like the DB2 CLI API, are favored.
There are many other developer options with DB2 Express-C, including COBOL, Perl, and Python. Perl drivers are available from the open source Perl community, and they are maintained and fully supported by IBM. There is an updated Python driver available for DB2 from SourceForge.net (see "Related topics"). The Perl, PHP, and Python drivers all require a valid DB2 Run-time client for deployments and a DB2 Application Development Client (or full DB2 Express-C) installation to compile and build applications.
Deploying database applications require a few considerations, including:
- Installing the DB2 server
- Installing the DB2 client
- Deploying the application itself
- Deploying the database
Installing a DB2 server environment can be accomplished using the DB2
graphical installer (
db2setup on Linux /
on Windows), or a silent installation can be performed using a response
db2setup -R on Linux /
setup /U <file-name> on Windows).
The DB2 Run-Time Client provides all of the application programming interfaces (API) and the required network client drivers to directly access DB2 on Linux, UNIX, or Windows. There is a more compact client available on Windows, known as the DB2 Run-Time Client Lite, which has a smaller footprint. The DB2 Run-Time Client Lite also provides Merge Modules that can be used by other Windows Installer-based installations to easily embed connectivity to DB2 in your Windows application.
Deploying your application is beyond the scope of this article, but just ensure that your application has the required DB2 client access libraries. Deploying the database itself can be accomplished using DB2 backup images, or the database can be reconstructed from data extracts into load files. Ensure that any application-dependant objects, such as stored procedures, triggers, views, and user-defined functions, are properly defined and accessible.
DB2 Express-C is based on the same core database server technology as the entire set of DB2 servers on Linux, UNIX, and Windows, and many features introduced in V8.1 and V8.2 of DB2 UDB has dramatically lowered the amount of active effort by a full-time DBA to monitor, tune, and manage the operations of DB2 servers. Full-time DB2 DBAs (Database Administrators) tend to manage very large multi-terabyte DB2 systems or many DB2 servers across a large organization. The same self-management and self-tuning capabilities that help large enterprise DB2 deployments are available in DB2 Express-C, so minimal DB2 DBA skills are required to maximize the availability of applications using a DB2 Express-C database server.
What about Cloudscape?
Cloudscape is a pure Java database server and it is ideal as an embedded database engine for Java applications or for light-medium scale Web applications. Cloudscape V10 was initially released by IBM in 2004 as a no-charge, licensed database server. At the same time, the Apache Derby Project was initiated, using the same code base as an incubator project within the Apache Software Foundation. (See "Related topics" for more information on the Apache Derby Project.) Cloudscape V10.1 was released as an update in August 2005. Just prior to the new Cloudscape 10.1 release, the Derby Project within the Apache Software Foundation graduated from its incubation phase and is now a member of the top level Apache DB project. There is a very active community of developers and users within the Derby project, and the database server is being used by many customers, researchers, and business partners. Cloudscape V10.1 is a snapshot of the Derby 10.1 code base with some additional installers, drivers, and tools. IBM provides a support option for Cloudscape V10.1 customers.
Resources for partners
The IBM Virtual Innovation Center, as shown in Figure 17, is specifically designed to provide application enablement assistance to ISVs (Independent Software Vendors) and other business partners. If you are planning to utilize DB2 Express Edition V8.2.2 into your solution, you should consider registering for and utilizing the resources available in this program.
Figure 17. IBM Virtual Innovation Center - DB2 Express
DB2 Express-C is an exciting new alternative data server with unique capabilities, and it is ready for developers and for production within minutes of the initial download. Download DB2 Express-C and join the growing community of DB2 users.
- DB2 Universal Database Express Edition for Linux and Windows product page: DB2 Universal Database Express Edition V8.2 combines the power, function, and reliability of an open standards-based database server with simplicity in packaging, installation and deployment at a minimal investment cost.
- DB2 Express-C: Download a no-charge version of DB2 Express-C .
- DB2 Universal Database, Version 8.2 for Linux HOWTO: Get more information about DB2 on Linux.
- The SPECjAppServer 2004 with DB2 and WebSphere Application Server uses the newer and more flexible JDBC driver db2jcc.jar.
- Apache Derby Project: The Derby project develops open source database technology that is pure Java, easy to use, small footprint, standards based, and secure.
- Interested in using DB2 for Visual Basic
.NET or C# .NET applications? Read the latest articles from Sonali
- "DB2 UDB for Visual Studio 2005 developers, Part 1: Overview of IBM Database Add-ins for Visual Studio 2005" (developerWorks, December 2005): This article provides an overview of the new functionality provided by IBM for Visual Studio 2005.
- "DB2 UDB for Visual Studio 2005 developers, Part 2: Build applications and Web sites for DB2 using IBM Database Add-ins for Visual Studio 2005" (developerWorks, December 2005): Build a Windows application and a Web site for DB2, using IBM tooling for Visual Studio 2005.
- Extensions for PHP:
- Updated Python driver available for DB2 from SourceForge.net.
- Learn more on this topic by listening to the webcast Power solutions with DB2 Express to deliver Information on Demand.