The following examples show how you can use UDTs, UDFs, and LOBs together in complex applications:
The following examples use type BLOB (Binary Large Object) data with a length of one megabyte as the base type of a UDT. The examples also use type BLOB host variables. BLOBs and the other types of LOB, as well as a technique for reducing LOB byte transfer between DB2 and the application, are covered in detail in Using Large Objects (LOBs).
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.
To create these UDFs, you must:
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.