In today's enterprise, unstructured data -- video files, sound files, picture files, and even large text files -- is increasingly common. Nonetheless, database management systems have only recently been able to effectively deal with this data type. This tip from author Susan Lawson, provided by InformIT, discusses how DB2 admins might approach the problem: with LOBs and extenders.
When using an extender for a particular LOB type, there are options that actually allow the data to be stored in its native format in separate files, such as a picture that is a single JPEG file. In this example, the hierarchical path name would be stored in support tables that would allow the extender to use this indirect reference to process the actual data. The extenders also require administrative support tables that vary based on the extender used. These tables are also referred to as metadata tables, as their content enables the extenders to appropriately handle user requests, such as inserting audio, displaying images, and so on. These tables identify base tables and columns that are enabled for the extender and reference other support tables used to hold attribute information about LOB columns. Triggers supplied by the extenders are used to update many of these support tables when underlying LOB data is inserted, updated, or deleted. At the present time, there are six extenders available in the DB2 family: image, audio, video, text, XML, and spatial. Many others are planned, and vendors also supply extender packages.
Applications generally use SQL to retrieve pointers to the data, and UDFs (user-defined functions) are used to assist with more complex and unique operations. Extender APIs will be more commonly used as all the coding is supplied for dealing with the LOBs. The extender for image data comes with 18 UDFs; audio has 27 UDFs, video has 18 UDFs, and the QBIC (Query By Image Content) API has 24 UDFs. For applications, that is a considerable advantage both from a "not-having-to-program-it" standpoint and easing the pain of the learning curve. For example, when dealing with the image extenders, there are several different formats supported. The common ones (BMP, EPS, GIF, JPG, TIF) are provided, of course, along with over 15 other formats. This means that if I were browsing through a series of pictures, each LOB picture could be of a different format, but the program would not have to be aware of this because it would be taken care of by the extender. The same is true of the text extender. A user could browse through a series of text documents, one in Microsoft Word format, another in Word Perfect, and so on. But it is not browsing documents, playing a video, and streaming audio that represent the most power. It is the searching ability of the LOB extenders. For example, with the text extender, searching can be done by soundex, synonym, thesaurus, proximity, linguistic, and several other criteria. With images, the QBIC API is supplied to allow searching by image content, and this is a very extensive and powerful API.
Application programming for objects will generally require the use of the extenders. Without their use, there is basically little that can be done without extensive user programming. The power of objects comes with the UDFs and API libraries that are packaged with the extenders. They allow an application to easily store, access, and manipulate any of the supported object types. Although the current list supplied is only the six extenders previously mentioned, many others are in development and will be released in the future as they are completed. The application programmer will be able to use UDFs in the SQL to position to the necessary LOB and then use an API to manipulate it, such as to display a picture on the screen. So, there are really two completely different libraries to strategize from. As a simple example, without forcing it to match any particular programming language, the following represents first storing a picture in a LOB and then displaying it on the screen. First, insert the data into the LOB by using the DB2IMAGE extender:
EXEC SQL BEGIN DECLARE SECTION; storage_type; EXEC SQL END DECLARE SECTION; SET storage_type = MMDB_STORAGE_TYPE_INTERNAL EXEC SQL INSERT INTO MY_PERSONAL_DIGITAL_PICTURES VALUES ('OZ TRIP 2', 'Sydney Opera House', DB2IMAGE ( CURRENT SERVER, 'c:/My Pictures/1999/Australia/OpraHse.jpg', 'ASIS', :storage_type));
Second, retrieve and display the data on the screen using API DBiBROWSE:
EXEC SQL BEGIN DECLARE SECTION; image_handler; EXEC SQL END DECALRE SECTION; EXEC SQL SELECT PICTURE INTO :image_handler WHERE NAME = 'Sydney Opera House'; Set return_code to DBiBROWSE("ib %s, MMDB_PLAY_HANDLE, image_handler, MMDB_PLAY_BI_WAIT);
From the pseudocode, it is easy to see that the extenders offer significant power and enable applications to be written quickly. In addition, most of the work is going on at the server, and the client is simply the recipient of all that power. When implementing extenders, keep in mind that the program will need to have enough memory available to support the use of LOBs on GUI clients.
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 April 2003