Extend DB2 Beyond the Ordinary

by Ed Ort

The IBM Developer Connection Cross-Platform Developer Kit (XPDK) for Database Server is now available for Windows NT servers as well as for OS/2 and AIX servers. The XPDK for Database Server gives you the enabling support and server technology to deliver quality client and server applications. The Database Server technology is based on the rich function of DB2 Version 2.1.1. It provides high-performance usability through a suite of graphical tools and leading-edge database capability such as Web enablement and multimedia support.

The XPDK for Database Server enables you to develop and test applications for AIX, OS/2, and Windows NT servers and for a wide range of client platforms. It gives you a lot of flexibility, something especially important when you need to develop cross-platform applications to meet business needs.

DB2 Expands on the World Wide Web
You can view DB2 Version 2.1.1 in action by accessing the DB2 Product and Service Technical Library Web page at http://www.software.ibm.com/data/db2/support/servinfo. The page is built entirely on DB2 Version 2.1.1 technology, using the following functions available in the XPDK for Database Server:
 * DB2 for AIX
 * DB2 WWW Connection
 * DB2 Text Extender

DB2 Extenders Can Power Your DB2 Applications to a World Beyond the Ordinary
The XPDK for Database Server now includes a new, powerful tool for client and server application development DB2 Extenders. DB2 Extenders can take your DB2 applications beyond traditional numeric and character data to images, video, voice, and complex documents. And you can bring all of these non-traditional data types together with traditional data types in one SQL query.

For example, an advertising agency maintains a DB2 database of its advertisements In the past, the agency stored numeric and character data about each ad campaign, such as the name of the client and the date that the advertisement was completed. With the installation of the DB2 Extenders, the agency now also stores the content of its print, audio, and video ads in the database. The agency stores the text of ad scripts too. An account executive wants to see the video ads the agency produced for a specific client in 1996, but only those whose playing time is less than 30 seconds. Here is an extract of an embedded SQL program coded in C that retrieves and displays the ads: 
 * 1) include 

int count = 0; EXEC SQL BEGIN DECLARE SECTION; char hvClient[30]; /*client name*/ char hvCampaign[30]; /*campaign name*/ char hvSdate[8]; /*ship date*/

struct { short len; char data [250]; }hvVid_fname; /*video file name*/

EXEC SQL END DECLARE SECTION; EXEC SQL DECLARE c1 CURSOR FOR SELECT Client, Campaign, Ship_date, Filename(Ads_video) FROM ADS WHERE Client= BIGACCOUNT AND Ship_date> 01/01/1996 AND Duration(Ads_video)<30; FOR FETCH ONLY; EXEC SQL OPEN c1;

for { EXEC SQL FETCH c1 INTO hvClient, hvCampaign, hvSdate, hvVid_fname; if (SQLCODE ]= 0) break; printf("nRecord %d\n", ++count); printf("Client = %s\n", hvClient); printf("Campaign = %s\n", hvCampaign); printf("Sdate = %s\n", hvSdate); rc=DBvPlay(NULL,MMDB_PLAY_FILE,hvVid_fname,MMDB_PLAY_WAIT); }

EXEC SQL CLOSE c1; 

The Family of DB2 Extenders
The DB2 Extenders include the: Each extender defines a new data type and functions using DB2 Version 2's built-in support for user-defined types (UDTs) and user-defined functions (UDFs). In the previous example, Filename and Duration are UDFs that the DB2 Video Extender defines for its UDT, DB2VIDEO.
 * DB2 Text Extender
 * DB2 Image Extender
 * DB2 Audio Extender
 * DB2 Video Extender

The UDFs defined by the DB2 Extenders can be important aids in helping you create powerful queries in your applications. UDFs are requested in an SQL statement in the same way as other SQL functions In this way, one SQL statement can access image, audio, video, and text objects from a DB2 database together with traditional numeric and character data. You can specify the extender UDFs in embedded SQL statements as well as in Call Level Interface (DB2 CLI) calls.

The DB2 Extenders also provide a set of non-UDF application programming interfaces to perform operations as varied as displaying text documents and images, playing video and audio clips, and enabling databases and tables for the data types defined by the DB2 Extenders In the previous example, DBvPlay is an API that plays video objects.

From Synonym Search to Image Content Query to Video Scene Change Detection
The DB2 Extenders provide a wide variety of capabilities. For example, with the DB2 Text Extender, you can create applications that search for documents that contain a specific word or phrase, search for synonyms of a word, or search for word variations (e g, search for mice and find mouse too).

The following shows a text search using the DB2 Text Extender:  SELECT * FROM MyTextTable WHERE version = 2 AND DB2TX CONTAINS ( DB2BOOKS, authorization  IN SAME PARAGRAPH AS table  AND SYNONYM FORM OF delete ) = 1  In this example, the SQL statement searches for documents that contain the word authorization in the same paragraph as the word table and that also contain the word delete or its synonyms DB2TX CONTAINS, which is highlighted in the example, is a UDF defined by the DB2 Text Extender DB2BOOKS is a column in a DB2 database table that contains documents.

The DB2 Image Extender implements Query by Image Content (QBIC) technology. With QBIC, you can create applications that let users submit visual samples of color or texture patterns as query search criteria. The following example shows user interfaces for an application that includes QBIC Notice that a sample image is used to search for images stored in a DB2 database. The DB2 Image Extender analyzes the sample image and finds the images in the database that most closely match the sample s average color.



With the DB2 Audio Extender, you can integrate audio data, in various formats and from various sources, into business queries. For example, you can access audio data in MIDI or WAVE files and work with different file-based audio servers.

You can use the DB2 Video Extender to automatically segment a video clip into shots based on scene changes. When the DB2 Video Extender detects a scene change, it records data for the associated shot. This data describes the starting and ending frame of the shot and the content of a representative frame within the shot. You can store this data in a DB2 database and join it with related data. Imagine the interesting applications you can create with this technology. For example, you can develop an application for a television studio that retrieves video clips, shots within the clips, and related business data Your application can handle these requests in one SQL query.

DB2 Extenders and the Client/Server Model
The DB2 Extenders exploit the DB2 client/server model and require DB2 Version 2.1.1 (or higher) Server platforms include AIX, OS/2, and Windows NT Client platforms include AIX, OS/2, Windows 3.1, Windows NT, and Windows 95.

Summary
The XPDK for Database Server is now available for Windows NT servers as well as for OS/2 and AIX servers. The XPDK for Database Server (for Windows NT, OS/2, and AIX) is based on the rich function of DB2 Version 2.1.1. You can now view the power of DB2 Version 2.1.1 in a number of World Wide Web pages XPDK for Database Server also includes a powerful tool for developing applications that access multiple types of data in a DB2 database. The tool, named DB2 Extenders, gives your applications a single point of access to non-traditional types of data (such as images and complex documents) as well as traditional business data.