The ability to search for and find text inside your DB2 database is an important function that becomes increasingly difficult as the size of the database grows and the specificity or complexity of your search increases. While simple finds on indexed data such as ID numbers is part of any database product, more complex searches that are important to many businesses and research functions require additional capabilities. Often advanced text searching functions, also called "full text searches," require add-on products that create meta data structures for the faster performance needed to make those searches practical.
For example, consider a storefront build with Websphere Commerce Suite where you need to search a product description to locate a CPU cooler that fits a Slot A/370 adapter, is thin enough to fit into a space constrained dual processor system, and costs less than $15. All of this data is in your database, in different tables such as PRODUCTS, INVENTORY, and so on, but requires a complex search to locate the appropriate matches.
DB2 comes with full text search functions: Text Extender, Text Information Extender, as well as the product DB2 NetSearch Extender (now in version 7.2). IBM recommends DB2's Text Information Extender for most applications where large scalability and complex searches are not required. Text Information Extender offers you the best application integration and flexibility, but requires that each table have a single primary key. For higher end e-commerce solutions you are better served with NetSearch Extender since it has a better performance architecture. The classic application for NetSearch Extender is a query that is text search only and returns a limited number of results. You can use both Extenders in your applications. The third option, DB2 Text Extender is meant for sophisticated full text searches and supports multiple languages.
IBM has published a white paper on this topic that describes not only the uses of these products, but their architectures, and how they relate to DB2's search engine. You'll find this white paper here.
About the Author
Barrie Sosinsky (barries@sosinsky-group) is president of consulting company Sosinsky and Associates (Medfield MA). He has written extensively on a variety of computer topics. His company specializes in custom software (database and Web related), training and technical documentation.
For More Information
- Feedback: E-mail the editor with your thoughts about this tip.
- More tips: Hundreds of free DB2 tips and scripts.
- Tip contest: Have a DB2 tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
- Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
- Forums: Ask your technical DB2 questions--or help out your peers by answering them--in our active forums.
- Best Web Links: DB2 tips, tutorials, and scripts from around the Web.
This was first published in July 2003