Example of distinct types, user-defined functions, and LOBs

The example in this topic demonstrates the following concepts:

• Creating a distinct type based on a LOB data type
• Defining a user-defined function with a distinct type as an argument
• Creating a table with a distinct type column that is based on a LOB type
• Defining a LOB table space, auxiliary table, and auxiliary index
• Inserting data from a host variable into a distinct type column based on a LOB column
• Executing a query that contains a user-defined function invocation
• Casting a LOB locator to the input data type of a user-defined function

Suppose that you keep electronic mail documents that are sent to your company in a DB2® table. The DB2 data type of an electronic mail document is a CLOB, but you define it as a distinct type so that you can control the types of operations that are performed on the electronic mail. The distinct type is defined like this:

CREATE DISTINCT TYPE E_MAIL AS CLOB(5M);

You have also defined and written user-defined functions to search for and return the following information about an electronic mail document:

• Subject
• Sender
• Date sent
• Message content
• Indicator of whether the document contains a user-specified string

The user-defined function definitions look like this:

CREATE FUNCTION SUBJECT(E_MAIL)
  RETURNS VARCHAR(200)
  EXTERNAL NAME 'SUBJECT'
  LANGUAGE C
  PARAMETER STYLE SQL
  NO SQL
  DETERMINISTIC
  NO EXTERNAL ACTION;

CREATE FUNCTION SENDER(E_MAIL)
  RETURNS VARCHAR(200)
  EXTERNAL NAME 'SENDER'
  LANGUAGE C
  PARAMETER STYLE SQL
  NO SQL
  DETERMINISTIC
  NO EXTERNAL ACTION;

CREATE FUNCTION SENDING_DATE(E_MAIL)
  RETURNS DATE
  EXTERNAL NAME 'SENDDATE'
  LANGUAGE C
  PARAMETER STYLE SQL
  NO SQL
  DETERMINISTIC
  NO EXTERNAL ACTION;

CREATE FUNCTION CONTENTS(E_MAIL)
  RETURNS CLOB(1M)
  EXTERNAL NAME 'CONTENTS'
  LANGUAGE C
  PARAMETER STYLE SQL
  NO SQL
  DETERMINISTIC
  NO EXTERNAL ACTION;

CREATE FUNCTION CONTAINS(E_MAIL, VARCHAR (200))
  RETURNS INTEGER
  EXTERNAL NAME 'CONTAINS'
  LANGUAGE C
  PARAMETER STYLE SQL
  NO SQL
  DETERMINISTIC
  NO EXTERNAL ACTION;

The table that contains the electronic mail documents is defined like this:

CREATE TABLE DOCUMENTS
  (LAST_UPDATE_TIME  TIMESTAMP,
   DOC_ROWID      ROWID NOT NULL GENERATED ALWAYS,
   A_DOCUMENT        E_MAIL);

Because the table contains a column with a source data type of CLOB, the table requires an associated LOB table space, auxiliary table, and index on the auxiliary table. Use statements like this to define the LOB table space, the auxiliary table, and the index:

CREATE LOB TABLESPACE DOCTSLOB
  LOG YES
  GBPCACHE SYSTEM;
 
CREATE AUX TABLE DOCAUX_TABLE
  IN DOCTSLOB
  STORES DOCUMENTS COLUMN A_DOCUMENT;
 
CREATE INDEX A_IX_DOC ON DOCAUX_TABLE;

To populate the document table, you write code that executes an INSERT statement to put the first part of a document in the table, and then executes multiple UPDATE statements to concatenate the remaining parts of the document. For example:

EXEC SQL BEGIN DECLARE SECTION;
  char hv_current_time[26];
  SQL TYPE IS CLOB (1M) hv_doc;
EXEC SQL END DECLARE SECTION;
/* Determine the current time and put this value */
/* into host variable hv_current_time.           */
/* Read up to 1 MB of document data from a file  */
/* into host variable hv_doc.                    */
⋮
/* Insert the time value and the first 1 MB of   */
/* document data into the table.                 */
EXEC SQL INSERT INTO DOCUMENTS
  VALUES(:hv_current_time, DEFAULT, E_MAIL(:hv_doc));
 
/* Although there is more document data in the   */
/* file, read up to 1 MB more of data, and then  */
/* use an UPDATE statement like this one to      */
/* concatenate the data in the host variable     */
/* to the existing data in the table.            */
EXEC SQL UPDATE DOCUMENTS
  SET A_DOCUMENT = A_DOCUMENT || E_MAIL(:hv_doc)
  WHERE LAST_UPDATE_TIME = :hv_current_time;

Now that the data is in the table, you can execute queries to learn more about the documents. For example, you can execute this query to determine which documents contain the word "performance":

SELECT SENDER(A_DOCUMENT), SENDING_DATE(A_DOCUMENT),
  SUBJECT(A_DOCUMENT)
  FROM DOCUMENTS
  WHERE CONTAINS(A_DOCUMENT,'performance') = 1;

Because the electronic mail documents can be very large, you might want to use LOB locators to manipulate the document data instead of fetching all of a document into a host variable. You can use a LOB locator on any distinct type that is defined on one of the LOB types. The following example shows how you can cast a LOB locator as a distinct type, and then use the result in a user-defined function that takes a distinct type as an argument:

EXEC SQL BEGIN DECLARE SECTION
  long hv_len;
  char hv_subject[200];
  SQL TYPE IS CLOB_LOCATOR hv_email_locator;
EXEC SQL END DECLARE SECTION
⋮
/* Select a document into a CLOB locator.        */
EXEC SQL SELECT A_DOCUMENT, SUBJECT(A_DOCUMENT)
  INTO :hv_email_locator, :hv_subject
  FROM DOCUMENTS
  WHERE LAST_UPDATE_TIME = :hv_current_time;
⋮
/* Extract the subject from the document.  The   */
/* SUBJECT function takes an argument of type    */
/* E_MAIL, so cast the CLOB locator as E_MAIL.   */
EXEC SQL SET :hv_subject =
  SUBJECT(CAST(:hv_email_locator AS E_MAIL));
⋮