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 LOAD utility 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:

Using a regular SQL operator such as "=" or 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'
			

or

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 LIKE with '%'; 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 'work':

SELECT reader_comments
FROM article
WHERE CONTAINS(title, 'work') = 1
			

The 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" with the 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 READER_COMMENTS column of the ARTICLE table. The search is being performed under the XML element articleinfo.

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
• The CONTAINS and SCORE SQL functions
• The xmlcolumn-contains XML function
• 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.

Back to top

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 backup, runstats, prune history, and so on. Next, add the task to the administrative task scheduler, and specify when and how often the task will run.

The list of tasks in the administrative task scheduler is managed through the built-in procedures ADMIN_TASK_ADD, ADMIN_TASK_UPDATE, and ADMIN_TASK_REMOVE. You can also monitor the task list and the status of executed tasks using administrative views.

Back to top

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.

Back to top

pureXML enhancements

With DB2 Express-C 9.5.2, you can now use the LOAD utility to insert XML documents into DB2 tables. The 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 manipulation features.

Back to top

.NET enhancements

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.

Back to top

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 com.ibm.db2.jcc.DB2Array.
• Decimal floating-point data type (DECFLOAT) is supported.
• Client reroute feature is enhanced.
• Connection concentrator and Sysplex workload balancing support for DriverManager connections is added.
• setXXXStream methods without an explicit length are supported.
• Java™ application support is added for XML schema updates.
• PreparedStatement.setObject calls with Reader and InputStream objects 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.)

Back to top

Conclusion

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

Back to top

What's next?

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.

Resources

Learn

• "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.
  
  

Discuss

• Participate in the discussion forum.
  
  
• Participate in developerWorks blogs and get involved in the developerWorks community.
  
  

About the author

Raul F. Chong is the DB2 on Campus program manager, based at the IBM Toronto Laboratory. His main responsibility is to grow the DB2 Express community, helping members interact with one another, and contributing to the forum. Raul is focusing on universities and other educational institutions, promoting the DB2 on Campus program. Raul holds a Bachelor of Science degree in Computer Science from the University of Toronto, and is a DB2 Certified Solutions Expert in both DB2 administration and application development.

Rate this article

Comments

Back to top

Table of contents

• DB2 Text Search
• Administrative task scheduler
• Locale-sensitive UCA-based collations
• pureXML enhancements
• .NET enhancements
• Client and driver enhancements
• Conclusion
• What's next?
• Resources
• About the author
• Comments

Next steps from IBM

• Try: DB2 Express-C
• Demo: Use Ajax with DB2
• Community: DB2

Dig deeper into Information Management on developerWorks

Discover knowledge paths

Skill-building guides for Information Management, Linux, open source, cloud, Java, business analytics, and more.

Special offers