In previous sections, you learned how to define and use the individual DB2 object extensions (UDTs, UDFs, and LOBs). However, as you will see in this section, there is a lot of synergy between these three object extensions.
According to the concept of object-orientation, similar objects in the application domain are grouped into related types. Each of these types have a name, an internal representation, and behavior. By using UDTs, you can tell DB2 the name of your new type and how it is internally represented. A LOB is one of the possible internal representations for your new type and is the most suitable representation for large, complex structures. By using UDFs, you can define the behavior of the new type. Consequently, there is an important synergy between UDTs, UDFs, and LOBs. An application type with a complex data structure and behavior is modeled as a UDT that is internally represented as a LOB, with its behavior implemented by UDFs. As described in Chapter 8. "Using the Active DBMS Capabilities", the rules governing the semantic integrity of your application type will be represented as constraints and triggers. To have better control and organization of your related UDTs and UDFs, you should keep them in the same schema.
The following examples show how you can use UDTs, UDFs, and LOBs together in complex applications:
Suppose you would like to keep the electronic mail (e-mail) sent to your company in DB2 tables. Ignoring any issues of privacy, you are planning to write queries over such e-mail to find out their subject, how often your e-mail service is being used to receive customer orders, and so on. Because e-mail can be quite large, and because it has a complex internal structure (a sender, a receiver, the subject, date, and the e-mail content), you decide to represent the e-mail by means of a UDT whose source type is a large object. You define a set of UDFs on your e-mail type, such as functions to extract the subject of the e-mail, the sender, the date, and so on. You also define functions that can perform searches on the content of the e-mail. You do the above using the following CREATE statements:
CREATE DISTINCT TYPE E_MAIL AS BLOB (1M)
CREATE FUNCTION SUBJECT (E_MAIL)
RETURNS VARCHAR (200)
EXTERNAL NAME '/u/mail/funcdir/e_mail!subject'
LANGUAGE C
PARAMETER STYLE DB2SQL
NO SQL
DETERMINISTIC
NO EXTERNAL ACTION
CREATE FUNCTION SENDER (E_MAIL)
RETURNS VARCHAR (200)
EXTERNAL NAME '/u/mail/funcdir/e_mail!sender'
LANGUAGE C
PARAMETER STYLE DB2SQL
NO SQL
DETERMINISTIC
NO EXTERNAL ACTION
CREATE FUNCTION RECEIVER (E_MAIL)
RETURNS VARCHAR (200)
EXTERNAL NAME '/u/mail/funcdir/e_mail!receiver'
LANGUAGE C
PARAMETER STYLE DB2SQL
NO SQL
DETERMINISTIC
NO EXTERNAL ACTION
CREATE FUNCTION SENDING_DATE (E_MAIL)
RETURNS DATE CAST FROM VARCHAR(10)
EXTERNAL NAME '/u/mail/funcdir/e_mail!sending_date'
LANGUAGE C
PARAMETER STYLE DB2SQL
NO SQL
DETERMINISTIC
NO EXTERNAL ACTION
CREATE FUNCTION CONTENTS (E_MAIL)
RETURNS BLOB (1M)
EXTERNAL NAME '/u/mail/funcdir/e_mail!contents'
LANGUAGE C
PARAMETER STYLE DB2SQL
NO SQL
DETERMINISTIC
NO EXTERNAL ACTION
CREATE FUNCTION CONTAINS (E_MAIL, VARCHAR (200))
RETURNS INTEGER
EXTERNAL NAME '/u/mail/funcdir/e_mail!contains'
LANGUAGE C
PARAMETER STYLE DB2SQL
NO SQL
DETERMINISTIC
NO EXTERNAL ACTION
CREATE TABLE ELECTRONIC_MAIL
(ARRIVAL_TIMESTAMP TIMESTAMP,
MESSAGE E_MAIL)
Suppose you populate your table by transferring your e-mail that is maintained in files into DB2. You would execute the following INSERT statement multiple times with different values of the HV_EMAIL_FILE until you have stored all your e_mail into DB2:
EXEC SQL BEGIN DECLARE SECTION
SQL TYPE IS BLOB_FILE HV_EMAIL_FILE;
EXEC SQL END DECLARE SECTION
strcpy (HV_EMAIL_FILE.NAME, "/u/mail/email/mbox");
HV_EMAIL_FILE.NAME_LENGTH = strlen(HV_EMAIL_FILE.NAME);
HV_EMAIL_FILE.FILE_OPTIONS = 2;
EXEC SQL INSERT INTO ELECTRONIC_MAIL
VALUES (CURRENT TIMESTAMP, :hv_email_file);
Because all the function provided by DB2 LOB support is applicable to UDTs whose source type are LOBs, you have used LOB file reference variables to assign the contents of the file into the UDT column. You have not used the cast function to convert values of BLOB type into your e-mail type because DB2 let you assign values of the source type of a distinct type to targets to the distinct type.
Suppose you need to know how much e-mail was sent by a specific customer regarding customer orders and you have the e-mail address of your customers in the customers table.
SELECT COUNT (*)
FROM ELECTRONIC_MAIL AS EMAIL, CUSTOMERS
WHERE SUBJECT (EMAIL.MESSAGE) = 'customer order'
AND CUSTOMERS.EMAIL_ADDRESS = SENDER (EMAIL.MESSAGE)
AND CUSTOMERS.NAME = 'Customer X'
You have used the UDFs defined on the UDT in this SQL query since they are the only means to manipulate the UDT. In this sense, your UDT e-mail is completely encapsulated. That is, its internal representation and structure are hidden and can only be manipulated by the defined UDFs. These UDFs know how to interpret the data without the need to expose its representation.
Suppose you need to know the details of all the e-mail your company received in 1994 which had to do with the performance of your products in the marketplace.
SELECT SENDER (MESSAGE), SENDING_DATE (MESSAGE), SUBJECT (MESSAGE)
FROM ELECTRONIC_MAIL
WHERE CONTAINS (MESSAGE,
'"performance" AND "products" AND "marketplace"') = 1
You have used the contains UDF which is capable of analyzing the contents of the message searching for relevant keywords or synonyms.
Suppose you would like to obtain information about a specific e-mail without having to transfer the entire e-mail into a host variable in your application program. (Remember that an e-mail can be quite large.) Since your UDT is defined on a LOB, you can use LOB locators for that purpose:
EXEC SQL BEGIN DECLARE SECTION
long hv_len;
char hv_subject[200];
char hv_sender[200];
char hv_buf[4096];
char hv_current_time[26];
SQL TYPE IS BLOB_LOCATOR hv_email_locator;
EXEC SQL END DECLARE SECTION
EXEC SQL SELECT MESSAGE
INTO :hv_email_locator
FROM ELECTRONIC MAIL
WHERE ARRIVAL_TIMESTAMP = :hv_current_time;
EXEC SQL VALUES (SUBJECT (E_MAIL(:hv_email_locator))
INTO :hv_subject;
.... code that checks if the subject of the e_mail is relevant ....
.... if the e_mail is relevant, then...............................
EXEC SQL VALUES (SENDER (CAST (:hv_email_locator AS E_MAIL)))
INTO :hv_sender;
Because your host variable is of type BLOB locator (the source type of the UDT), you have explicitly converted the BLOB locator to your UDT, whenever it was used as an argument of a UDF defined on the UDT.