| LOB data types ( BLOB , CLOB , and DBCLOB ) provide an easy way to store large, unstructured data in DB2 databases. But LOBs are nondescript. The only thing you know about them is a general idea of the type of data: But DB2 comes equipped with extenders that can be used to provide additional meaning and functionality to LOBs. DB2 Extenders are available for image, audio, video, and text data. A DB2 Extender provides a distinct type for the LOB, and a set of user -defined functions for use with objects of its distinct type. Additionally, the DB2 Extenders automatically capture and maintain attribute information about the objects being stored. They also provide APIs for your applications to use. Basically, the DB2 Extenders provide the functionality to make LOBs useful for your applications. With the DB2 Extenders, you could store LOBs, but doing anything very useful with them would be difficult and require a lot of work. The DB2 Extenders use the MMDBSYS schema for all objects, including UDTs and UDFs. The following UDTs are created by the DB2 Extenders to support image, audio, and video data: | DB2AUDIO | A variable-length string containing information needed to access an audio object, also called an audio handle. | | DB2IMAGE | A variable-length string containing information needed to access an image object, also called an image handle. | | DB2TEXTH | A variable-length string containing information needed to access a text document, also called a text handle. | | DB2TEXTFH | A variable length string containing information required for indexing an external text file, also referred to as a file handle. | | DB2VIDEO | A variable-length string containing information needed to access a video object, also known as a video handle. |
The DB2AUDIO , DB2IMAGE , and DB2VIDEO UDTs are based on a VARCHAR(250) data type. The DB2TEXTH UDT is based on a VARCHAR(60) data type with FOR BIT DATA . The information in a text handle includes a document ID, the name of the server where the text is to be indexed, the name of the index, information about the text file, and information about the location of the file. File handles are stored in columns that Text Extender creates and associates with each group of external files. The audio, image, and video handles are stored in columns created by each specific extender for handling that type of data ”audio, image, or video. When enabled, each of the DB2 Extenders ”audio, image, text, and video ”also creates user-defined functions for use on columns defined as the UDT. The UDFs created by each of the DB2 Extenders are outlined in Tables 9.4, 9.5, 9.6, and 9.7. Table 9.4. UDFs Created By the Audio Extender | UDF Name | Purpose of the UDF | | AlignValue | Gets the bytes per sample value of the audio | | BitsPerSample | Returns the number of bits used to represent the audio | | BytesPerSec | Returns the average number of bytes per second of audio | | Comment | Retrieves or modifies user comments | | Content | Retrieves or modifies the audio content | | ContentA | Updates the audio content with user-supplied attributes | | DB2Audio | Stores the audio content | | DB2AudioA | Stores the audio content with user-supplied attributes | | Duration | Retrieves the audio playing time | | Filename | Retrieves the name of the file that contains the audio | | FindInstrument | Retrieves the number of the audio track that records a specific instrument in an audio | | FindTrackName | Retrieves the track number of a named track in an audio recording | | Format | Retrieves the audio format | | GetInstruments | Retrieves the names of the instruments in the audio recording | | GetTrackNames | Retrieves the track names in an audio recording | | Importer | Retrieves the user ID of the importer of an audio | | ImportTime | Retrieves the timestamp when an audio was imported | | NumAudioTracks | Retrieves the number of recorded tracks in an audio | | NumChannels | Retrieves the number of audio channels | | Replace | Modifies the content and user comments for an audio recording | | ReplaceA | Modifies the content and user comments for an audio recording with user-supplied attributes | | SamplingRate | Retrieves the sampling rate of the audio | | Size | Retrieves the size of an audio in bytes | | TicksPerQNote | Retrieves the number of clock ticks per quarter note used in recording an audio | | TicksPerSec | Retrieves the number of clock ticks per second used in recording an audio | | Updater | Retrieves the user ID of the updater of an audio | | UpdateTime | Retrieves the timestamp when an audio was updated |
Table 9.5. UDFs Created By the Image Extender | UDF Name | Purpose of the UDF | | Comment | Retrieves or modifies user comments | | Content | Retrieves or modifies the image content | | ContentA | Updates the image content with user-supplied attributes | | DB2Image | Stores the image content | | DB2ImageA | Stores the image content with user-supplied attributes | | Filename | Retrieves the name of the file that contains an image | | Format | Retrieves the image format (for example, GIF) | | Height | Retrieves the height of an image in pixels | | Importer | Retrieves the user ID of the importer of an image | | ImportTime | Retrieves the timestamp when an image was imported | | NumColors | Retrieves the number of colors used in an image | | Replace | Updates the content and user comments for an image | | ReplaceA | Updates the content and user comments for an image with user-supplied attributes | | Size | Retrieves the size of an image in bytes | | Thumbnail | Retrieves a thumbnail- sized version of an image | | Updater | Retrieves the user ID of the updater of an image | | UpdateTime | Retrieves the timestamp when an image was updated | | Width | Retrieves the width of an image in pixels |
Table 9.6. UDFs Created By the Text Extender | UDF Name | Purpose of the UDF | | CCSID | Returns the CCSID from a handle | | Contains | Searches for text in a particular document | | File | Retrieves or modifies the path and name of a file in an existing handle | | Format | Retrieves or modifies the document format setting in a handle | | Init_Text_Handle | Retrieves a partially initialized handle containing information such as format and language settings | | Language | Retrieves or modifies the language setting in a handle | | NO_of_Matches | Searches for matches and returns the number of matches found | | Rank | Retrieves the rank value of a found text document | | Refine | Returns a combined search argument from a specified search argument and refining search argument | | Search _ Result | Returns an intermediate table with the search result of the specified search string |
Table 9.7. UDFs Created By the Video Extender | UDF Name | Purpose of the UDF | | AlignValue | Gets the bytes per sample value of the audio track of the video | | AspectRatio | Returns the aspect ratio of the first track of an MPEG1 and MPEG2 video | | BitsPerSample | Returns the number of bits used to represent the audio | | BytesPerSec | Returns the average number of bytes per second of the audio track of the video | | Comment | Retrieves or modifies user comments | | CompressType | Returns the compression format of a video (for example, MPEG-2) | | Content | Retrieves or modifies the video content | | ContentA | Updates the video content with user-supplied attributes | | DB2Video | Stores the video content | | DB2VideoA | Stores the video content with user-supplied attributes | | Duration | Retrieves the video playing time | | Filename | Retrieves the name of the file that contains the video | | Format | Retrieves the video format | | Importer | Retrieves the user ID of the importer of the video | | ImportTime | Retrieves the timestamp when the video was imported | | MaxBytesPerSec | Retrieves the maximum throughput of a video in bytes per second | | NumAudioTracks | Retrieves the number of audio tracks in the video | | NumChannels | Retrieves the number of audio channels in the audio track of the video | | NumFrames | Retrieves the number of frames in the video | | NumVideoTracks | Retrieves the number of video tracks in a video | | Replace | Modifies the content and user comments for the video | | ReplaceA | Modifies the content and user comments for the video with user-supplied attributes | | SamplingRate | Retrieves the sampling rate for an audio track of the video | | Size | Retrieves the size of the video audio in bytes | | Updater | Retrieves the user ID of the updater of an audio | | UpdateTime | Retrieves the timestamp when an audio was updated | | Width | Retrieves the width in pixels of a video frame |
XML Extender  | DB2 V7 supports XML using a new data type extender for XML documents: the XML Extender. The XML Extender is similar to the other extenders for video, image, audio, and text that were added to DB2 V6. Like those previous extenders, the XML Extender combines user-defined distinct types, user-defined functions, and triggers to provide extended data type functionality for DB2 databases. |
The XML Extender enables XML documents to be integrated with DB2 databases. By integrating XML into DB2 databases, you can more directly and quickly access the XML documents. You can search and store entire XML documents using SQL. You also have the option of combining XML documents with traditional data stored in relational tables. When you store or compose a document, you can invoke DBMS functions to trigger an event to automate the interchange of data between applications. An XML document can be stored complete in a single text column. Alternatively, XML documents can be broken into component pieces and stored as multiple columns across multiple tables. The XML Extender provides user-defined data types (UDTs) and user-defined functions (UDFs) to store and manipulate XML in the DB2 database. The XML Extender defines UDTs for XMLVARCHAR , XMLCLOB , and XMLFILE . After the XML is stored in the database, the UDFs can be used to search and retrieve the XML data as a complete document or in pieces. The UDFs supplied by the XML Extender include -
Storage functions to insert XML documents into a DB2 database -
Retrieval functions to access XML documents from XML columns -
Extraction functions to extract and convert the element content or attribute values from an XML document to the data type that is specified by the function name -
Update functions to modify element contents or attribute values (and to return a copy of an XML document with an updated value) The UDFs created by the XML Extender are outlined in Table 9.8. Table 9.8. UDFs Created By the XML Extender | UDF Name | Purpose of the UDF | | XMLVarcharFromFile | Reads an XML document from a file and returns the document as an XMLVARCHAR type. | | XMLCLOBFromFile | Reads an XML document from a file and returns the document as an XMLCLOB type. | | XMLFileFromVarchar | Reads an XML document from memory as VARCHAR , writes it to an external file, and returns the file name and path as an XMLFILE type. | | XMLFileFromCLOB | Reads an XML document as a CLOB locator, writes it to an external file, and returns the file name and path as an XMLFILE type. | | Content | Depending on parameters, will retrieve from XMLFILE to a CLOB; or, retrieve from XMLVARCHAR to an external file; or, retrieve from XMLCLOB to an external file. | | extractInteger | Extracts the element content or attribute value from an XML document and returns the data as an INTEGER data type. | | extractSmallint | Extracts the element content or attribute value from an XML document and returns the data as a SMALLINT data type. | | extractDouble | Extracts the element content or attribute value from an XML document and returns the data as a DOUBLE precision data type. | | extractReal | Extracts the element content or attribute value from an XML document and returns the data as a REAL data type. | | extractChar | Extracts the element content or attribute value from an XML document and returns the data as a CHAR data type. | | extractVarchar | Extracts the element content or attribute value from an XML document and returns the data as a VARCHAR data type. | | extractCLOB | Extracts a portion of an XML document, including its element and attribute markup, as well as the content of elements and attributes (including sub-elements). This function differs from the other extract functions; they return only the content of elements and attributes. Use extractClob to extract document fragments ; use extractVarchar or extractChar to extract simple values. | | extractDate | Extracts the element content or attribute value from an XML document and returns the data as a DATE data type. | | extractTime | Extracts the element content or attribute value from an XML document and returns the data as a TIME data type. | | extractTimestamp | Extracts the element content or attribute value from an XML document and returns the data as a TIMESTAMP data type. | | Update | Updates a specified element or attribute value in one or more XML documents stored in the XML column. | | generate_unique() | Returns a character string that is unique compared to any other execution of the same function. No arguments should be specified for this function, but the empty parentheses are required. The result of the function is a unique value and cannot be null. |
NOTE You can add an "s" to the extract functions if you so desire . The functionality is the same as the function without the "s" on the end. For example, extractInteger will function the same as extractIntegers ; the terms are synonymous.
For additional information on DB2's support for XML please consult Chapter 17, "DB2 and the Internet." V8 XML Support  | DB2 V8 provides even further support for XML within DB2 databases. |
A new XML data type is introduced in DB2 V8. This new data type is not like any other existing data type. It is a transient data type in that it exists only during query processing. There is no persistent data of this type and it is not an external data type that can be declared in application programs. In other words, the XML data type cannot be stored in a database or returned to an application. A DB2 XML data type can be an element, a forest of elements, the textual content of an element, or an empty XML value. Furthermore, there are restrictions for the use of the XML data type. An XML data type is not compatible with any other DB2 data type. Query results cannot contain an XML data type; nor can columns in a view contain an XML data type. Furthermore, XML data cannot be used in a sort . DB2 V8 also introduces the XML publishing functions. The XML publishing functions are built-in DB2 functions that run inside the DB2 address spaces. Contrast that with normal external User Defined Functions that run in a WLM managed address space outside of DB2. This operational aspect of the XML publishing functions provides better performance. For more details on the XML publishing functions consult Chapter 3, "Using DB2 Functions." Net Search Extender | | Additionally, DB2 V7 added another new extender, the Net Search Extender. The Net Search Extender complements the Text Extender to provide an efficient, format="GIF" depth="274" width="450" role="stdwidth"/>-text retrieval engine for DB2 Web applications. The Net Search Extender works with Net.Data, Java, and DB2 CLI application programs. |
The primary benefits and features of the Net Search Extender include very fast indexing, sophisticated search capabilities including fuzzy searching and document tagging for limiting searches, and advanced formatting of search results. The Net Search Extender provides four DB2 stored procedures to provide search functionality: -
textsearch ” Performs a standard search with no ranking -
textsearch_t ” Performs a standard search with tracing but no ranking -
textsearch_r ” Performs a standard search with ranking -
textsearch_rt ” Performs a standard search with ranking and tracing DB2 Extender Guidelines The following guidelines cover the additional issues you may encounter as you implement and plan for DB2 Extender usage. Be Aware of WLM Requirements for DB2 Extenders The system administrator must enable the DB2 Extenders you wish to use at your site. When the extender is enabled it creates the UDTs, UDFs, administrative tables, and supporting APIs for the extender. The extenders require the use of WLM (Work Load Manager) application environments for the UDFs and stored procedures that are created. The extenders use stored procedures to process API requests . After the DB2 extenders are installed, you need to establish WLM environments for the extender UDFs and stored procedures. Be Aware of Security Implications Before you use the DB2 Extenders, you need to consider the security and authorization issues your will encounter. First, you must decide how to secure access to the actual content of the audio, image, text, and video data. Additionally, the DB2 Extenders create administrative support tables to store additional information about the extenders. Some administrative support tables identify user tables and columns that are enabled for an extender. Other administrative support tables contain attribute information about objects in enabled columns. One example is the QBIC tables created by the Image Extender (QBIC stands for Query By Image Content). You must decide who should have access to the metadata in the administrative support tables. Secondly, you need to determine how to manage the privileges that are automatically granted when the DB2 Extender is enabled. For example, when a DB2 Extender is enabled, USE privilege is granted to PUBLIC for the UDT, its related CAST functions, and all the UDFs for the extender. This may, or may not, be acceptable in your shop. If you REVOKE the privileges and GRANT them to specific authids, be prepared for the potential headache of administering the list of authorized users of the extender's functionality. Your audio, image, and video data can be stored in files external to DB2. In that case you can also control access to the content in external files. This can be achieved using operating system security commands, which are usually performed by a separate security group. By limiting access to the external files you limit the ability to retrieve the objects for the extender's data type. CAUTION The files must be in a file system that is compatible with Unix System Services (USS); for example, a hierarchical file system. USS was previously known as MVS Open Edition.
Another consideration is MMDBSYS . All of the DB2 Extenders use the MMDBSYS SQLID. The UDT and all of the UDFs created by the DB2 Extender will be created in the MMDBSYS schema. You should consider creating the MMDBSYS userid to manage the administrative support tables. Use an appropriate external security package (such as ACF2 or RACF) to create an MMDBSYS userid. The DB2 Extenders also create administrative APIs, many of which require special authority. For example, SYSADM or the SELECT privilege on audio columns in all searched tables is required for the DBaAdminGetInaccessibleFiles API. This API returns the names of inaccessible files that are referenced in audio columns of user tables. Finally, you must consider who can issue administration commands to the db2ext command line processor for DB2 Extenders. The administrative APIs and administration commands are documented in the DB2 Extenders manuals: Image, Audio, and Video Extenders Administration and Programming Guide Net Search Extender Administration and Programming Guide Text Extender Administration and Programming Guide XML Extender Administration and Programming Guide |
