As you probably know, DB2 Express-C is the free community edition of DB2. You can use it in production, distribute it embedded in your applications, or simply use it for development purposes. All for free! DB2 Express-C can be installed on servers of any size; though it will only use two cores and 2GB of memory; moreover, there is no limit in the database size. This is a pretty generous offering compared to what other vendors allow you to do with their community editions.
Version 9.5 of DB2 Express-C was released on November of 2007. Since then, several important improvements and new features have enhanced the product. Though available on the fee-based versions of DB2 for some time, these new features are now incorporated into the newly refreshed image of DB2 Express-C 9.5.2.
At the time of this writing, the DB2 Express-C team is finishing the last quality tests to deliver the official version for all supported platforms of this community edition. If you're reading this article before the official release is publicly available, you can still try the beta version of DB2 Express-C 9.5.2, which has been available for download since October of 2008 (see Resources for a download link).
With DB2 Express-C 9.5.2, take advantage of the following new functionalities:
- DB2 Text Search, a new integrated component of the product that allows you to perform fast searches on text columns
- The administrative task scheduler, which allows developers to programmatically schedule tasks.
- Support for locale-sensitive collations, which provide culturally-expected sorting
- pureXML enhancements like the support of the
LOADutility to insert XML documents
- Many client and driver enhancements for JDBC, SQLJ, and .NET
In addition to improvements and new functionality, fixes to defects found since DB2 Express-C 9.5 was released are also incorporated with 9.5.2. For a list of fixes, see the Resources section of this article.
DB2 Text Search
DB2 Text Search is probably the most important new feature of DB2 9.5.2.
DB2 Text Search allows you to issue SQL, SQL/XML, and XQuery statements to
perform text search queries on data stored in a DB2 database. For example,
let's say you have developed a Web application for your company, an IT
Magazine. The company Web site allows readers to post comments to articles
and stores this information in a DB2 table called
Article, as shown in
Table 1. Article table
|ID integer||title varchar(30)||reader_comments varchar(100)|
|101||The Job market for Database Professionals||Awesome article! I've been working as a DBA for 10 years, and this one is right on the mark!|
|101||The Job market for Database Professionals||What is the difference between a Database Architect and a Database Administrator?|
|105||Working with ORM frameworks||Nice to know how these frameworks work!|
|102||New with DB2 Express-C 9.5.2||Glad to see new features coming out with DB2's new release. Thanks IBM!|
|105||Working with ORM frameworks||Great article. Thanks!|
Using a regular SQL operator such as "
predicates such as "
like", you can only perform
matches with search patterns. For example:
SELECT reader_comments FROM article WHERE title = 'Working with ORM frameworks'
SELECT reader_comments FROM article WHERE title like 'Worki%'
In the first case, you have to type the entire article title exactly as
stored in the database. In the second case, you can use
however, you still need to know which characters were used at the start of
the title. With DB2 Text Search, on the other hand, you can use the
CONTAINS() function, which can perform searches
within a text column such as
VARCHAR, and you
don't need to specify the exact search argument, nor the order in which it
is located in the string. It uses linguistic processing; that is, it can
find different forms of the search term within the text. For example, the
following query retrieves the comments for the article titled "Working
with ORM frameworks" even though the search argument used was
SELECT reader_comments FROM article WHERE CONTAINS(title, 'work') = 1
CONTAINS() function returns a 1 (true) if
the search argument is found. For this example, DB2 Text Search would also
find reader comments that contain other forms of the word "work", such as
"works" or "worked".
You can also create a synonym dictionary and input words that could be
found as synonyms to others. For example, if you add the words "Database
administrator" and "DBA" as synonyms, then, if you are searching for "DBA"
CONTAINS() function, it would also
retrieve rows where "Database administrator" shows up on the text.
DB2 Text search also complements DB2's XML support provided with pureXML: XQuery is supported as a primary language in DB2, allowing for optimal search of XML documents. However, within an XML element, there may be large portions of text that are not easily searchable with XQuery. This type of data is where DB2 Text Search can be useful.
In the above example the "reader_comments" column was defined as a
VARCHAR. Had you defined this as an
XML column, and stored all comments as XML, you
could also use DB2 Text Search in the same way; but instead of using the
CONTAINS function, you would use the function
xmlcolumn-contains(). For example:
xquery db2-fn:xmlcolumn-contains('ARTICLE.READER_COMMENTS', 'DBA')/articleinfo
In the above example, the XQuery statement is looking for 'DBA' in the XML
document stored in the
ARTICLE table. The search is being
performed under the XML element
In addition to English, DB2 Text Search supports different languages for document processing like Arabic, Czech, Chinese, Danish, German, Greek, Spanish, Finnish, French, Italian, Korean, Polish, Portuguese, Russian, and so on.
DB2 Text Search is powered by the IBM OmniFind™ Text Search server, and these are some other features it provides:
- Installation and configuration performed when installing DB2
- Full-text search in text, HTML, and XML documents, including Boolean and wildcard search
- Fully integrated SQL, SQL/XML, and XQuery support, including XPath syntax subset to search XML documents
- Invisible authentication
- SQL codes for error handling
- Fast indexing of large amounts of data
- Incremental and asynchronous index updating with scheduling option
- Built-in SQL functionality combined with the DB2 Optimizer
- Linguistic processing in all supported languages
- Synonym dictionary support
Similar functionality provided with DB2 Text Search is also available in a DB2 extender called the Net Search Extender (NSE). NSE is being phased out in favor of DB2 Text Search, which is being integrated into the product rather than being an extension as NSE was.
For more information about DB2 Text Search, see the Resources section.
Administrative task scheduler
The administrative task scheduler is another improvement available with DB2 Express-C 9.5.2. With this new utility, developers can now build applications to programmatically enable a DB2 data server to automate the execution of tasks. This utility runs independently of the Task Center, which does not have this programmable SQL interface, and is dependant on the DAS.
To create a task with this utility, use the
ADMIN_CMD procedure. Invoking this procedure,
you can run administrative commands such as
prune history, and so on. Next, add the task to
the administrative task scheduler, and specify when and how often the task
The list of tasks in the administrative task scheduler is managed through
the built-in procedures
ADMIN_TASK_REMOVE. You can also monitor the
task list and the status of executed tasks using administrative views.
Locale-sensitive UCA-based collations
With DB2 Express-C 9.5.2, when you create a Unicode database, you can now specify a collation, which provides culturally-expected sorting. For example, in the Spanish language (Latin America) the alphabet goes like this:
A, B, C, CH, D, E...
Note that 'CH' is a letter of its own. If this is not considered in Spanish-speaking countries in Latin America, you may get words sorted in the wrong order. For example, the following Spanish words with a default collation mighty be ordered as follows:
Caballo, Chofer, Cometa
But if the locale for Spanish speaking countries in Latin America is used, the right order would be:
Caballo, Cometa, Chofer
These collations can also be tailored to provide case- and accent-insensitive ordering. Locale-sensitive collations in DB2 9.5.2 are based on Version 5.0 of the Unicode Collation Algorithm, which provides a specification for comparing two Unicode strings in a way that conforms to the requirements of the Unicode Standard.
You can also use locale-sensitive UCA-based collations with the
COLLATION_KEY_BIT SQL scalar function.
With DB2 Express-C 9.5.2, you can now use the
LOAD utility to insert XML documents into DB2
LOAD utility offers better
performance than an
IMPORT utility, as it
normally writes directly into the database with minimal logging, allowing
for more efficient data movement.
The ability to load XML data also allows you to use load-specific options
on tables that contain XML columns, such as loading from the
CURSOR file type, and a variety of data
With DB2 Express-C 9.5.2, the .NET common language runtime (CLR) routines (including stored procedures and user-defined functions) can now be deployed in 64-bit environments.
Client and driver enhancements
Though the following enhancements apply to clients and drivers, these are also included with a DB2 Express-C 9.5.2 server. Therefore, if you have been using your DB2 Express-C server as a client to connect to a database, or have been using the drivers that come with it, you can now take advantage of the following features:
IBM Data Server Driver for JDBC and SQLJ enhancements
The following is a list of the enhancements made to the IBM Data Server Driver for JDBC and SQLJ. (For a full explanation of the enhancements, see the Resources section.)
- The db2jcc_license_*.jar license files are no longer required in your CLASSPATH.
- XML data type encryption is supported using Type 4 connectivity.
- Progressive streaming is supported for LOBs.
- ResultSet and DatabaseMetaData methods for dynamic scrollable cursors are added.
- Arrays are supported using the interface
- Decimal floating-point data type (
DECFLOAT) is supported.
- Client reroute feature is enhanced.
- Connection concentrator and Sysplex workload balancing support for
DriverManagerconnections is added.
- setXXXStream methods without an explicit length are supported.
- Java™ application support is added for XML schema updates.
InputStreamobjects are supported.
- The javax_jcc.jar file is removed, so it doesn't have to be included in CLASSPATH.
- Optimistic locking is supported.
- Timeout methods are supported.
- More JDBC 3.0 methods are added.
- Internal statement caching is supported with Type 4 connectivity.
- sqlj4.zip is included, which, along with db2jcc4.jar, are the driver files. These files support JDBC 4.0 and earlier, and require a minimum level for the SDK for Java to be 6.
IBM Data Server Provider for .NET has been enhanced
- The IBM Data Server Provider for .NET contains ASP.NET providers.
- Trusted context for delivering faster and more secure 3-tier applications is supported.
- The LINQ Entity Framework included in the .NET Framework 3.5 Service Pack 1 Update is supported in a beta driver after installing DB2 Express-C 9.5.2. (See Resources for a link to the beta driver.)
There are more details about DB2 Express-C 9.5.2 and client/driver enhancements. They can be found in the DB2 Information Center. (See the Resources section for a link.)
IBM continues to show its commitment to the community by releasing another update to the free DB2 Express-C edition: DB2 Express-C 9.5.2.
Most of these new features and enhancements have been available for some time to customers using the fee-based DB2 editions, as well as for customers using DB2 Express-C with paid subscription.
This article has briefly described some of the most important enhancements
to the product. In particular, the community has been voicing out their
desire for the DB2 Text Search feature. This new feature allows users to
perform powerful text searches, and it is supported on several languages
like Chinese, Russian, and Portuguese. Other enhancements include a new
administrative task scheduler, which can be used by developers to schedule
tasks programmatically; support for collations, which provide
culturally-expected sorting; support for the
LOAD utility to insert a large amount of XML
document faster into a DB2 database; and a number of enhancements made to
the clients and drivers that also come with the DB2 Express-C 9.5.2
We suggest you to download and install DB2 Express-C 9.5.2 first. If you are new to DB2 Express-C, learn it quickly with this free book and these free videos! For technical questions, check out the DB2 Express-C forum. More information about DB2 Express-C can be found from the DB2 Express-C home page.
- "Full-text search with DB2 Text Search" (developerWorks, October 2008): Follow this detailed guide to using DB2 Text Search. Set up a database to support text search, and walk through a scenario to get some experience for setting up your own text searches.
- "XML full-text search in DB2" (developerWorks, June 2006): Learn the basics of DB2 text search, including text index creation and administration. Explore specific features of DB2 full-text search, such as searching for keywords or phrases in the XML document structure with fuzzy search, proximity search, and stemming.
- DB2 Express-C product home page: Learn more about DB2 Express-C.
- What's New overview (DB2 Information Center): Find information about new functionality and changes in existing functionality from Version 9.1.
- DB2 Express-C 9.5.2 fixes: Explore the list of fixes included in the fix packs for DB2 9.5.
- DB2 Express-C 9.5.2 security patches: Review the list of security vulnerability, and deploy one of the fix packs to remove the vulnerabilities on your affected DB2 installations.
- developerWorks Information Management zone: Learn more about Information Management. Find technical documentation, how-to articles, education, downloads, product information, and more.
- Stay current with developerWorks technical events and webcasts.
- Technology bookstore: Browse for books on these and other technical topics.
Get products and technologies
- DB2 Express-C: Download the free version of DB2 Express 9 database server.
- IBM Data Server Provider for .NET - LINQ Beta1: Get the IBM Data Server Provider for .NET LINQ beta driver.
- Build your next development project with IBM trial software, available for download directly from developerWorks.
- Participate in the discussion forum.
- Participate in developerWorks blogs and get involved in the developerWorks community.