DB2 Express-C, the developer-friendly alternative

There are many no-charge RDBMS (Relational Database Management System) options available today. Some of these are open source, and some are available from commercial vendors at no charge. If you are developing applications using C/C++, Java™, .NET, or PHP and looking for a proven data server with innovative technology and a growing developer community base that is already deployed in many critical business solutions, it's time to take a look at the new DB2® Express-C. This article examines how you can get started quickly using DB2 Express-C for all of your applications, and it also provides an automation and tuning scenario to optimize your application.

Share:

Grant Hutchison (ghutchis@ca.ibm.com), Senior Product and Release Manager, IBM 

Grant HutchisonGrant Hutchison is a Senior Product and Release Manager in the IBM Data Server development team. He is focused on deliverying the next generation of tools and drivers for Web 2.0 applications and web based administration tools for DB2 and Informix Dynamic Server (IDS). Grant has been a member of the IBM Information Management team for 15 years holding various technical and management positions in development, sales, and marketing. He has a graduate degreee in Software Engineering from the University of Waterloo and a B.Sc.from Wilfrid Laurier University. He also co-authored the first DB2 UDB Certification Guide (1996) and frequently presents at conferences.



02 February 2006

Also available in Russian

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. If you are interested in the history of DB2, I would recommend the article "The Big Picture: IBM DB2 Information Management Software and DB2 Universal Database" (developerWorks, January 2003).

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. All of the DB2 UDB product manuals are also 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 "Resources.")


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 the 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
DB2 Express 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 First Steps

Tools

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
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
DB2 tools

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
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
DB2 Control Center 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
DB2 Health Center

Analysis scenario

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:

  • db2batch
  • 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

The 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
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
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
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
db2batch2 - 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
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
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
Development Center - SQL PL

Java

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 "Resources"). 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_license_cu.jar and 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

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 "Resources"). 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 "Resources").

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
Zend Core for IBM

.NET

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
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 "Resources"). 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.


Deployment guidance

Deploying database applications require a few considerations, including:

  1. Installing the DB2 server
  2. Installing the DB2 client
  3. Deploying the application itself
  4. Deploying the database

Installing a DB2 server environment can be accomplished using the DB2 graphical installer (db2setup on Linux / setup on Windows), or a silent installation can be performed using a response file (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.


Operational guidance

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 "Resources" 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
IBM Virtual Innovation Center - DB2 Express

Summary

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.

Resources

Learn

Get products and technologies

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=103083
ArticleTitle=DB2 Express-C, the developer-friendly alternative
publish-date=02022006