Enabling you to define new data types to DB2 gives you considerable power and extends your capabilities with the built-in data types. You are no longer restricted to using system-supplied built-in data types to model your businesses and capture the semantics of your data. A user-defined distinct type is the DB2 mechanism you can use for this purpose.
There are several benefits associated with UDTs:
By defining new types, you can indefinitely increase the set of types provided by DB2 to support your applications.
You can specify any semantics and behavior for your new type by using user-defined functions (UDFs) to augment the diversity of the types available in the system.
Strong typing insures that your UDTs will behave appropriately. It guarantees that only functions defined on your UDT can be applied to instances of the UDT.
The behavior of your UDTs is restricted by the functions and operators that can be applied on them. This provides flexibility in the implementation since running applications do not depend on the internal representation that you chose for your type.
The definition of user-defined functions on types can augment the functionality provided to manipulate your UDT at any time. (See "User-Defined Functions (UDF)")
Distinct types are highly integrated into the database manager. Because distinct types are internally represented the same way as built-in data types, they share the same efficient code used to implement built-in functions, comparison operators, indexes, etc. for built-in data types.
UDTs are the foundation for most object-oriented features. They represent the most important step towards object-oriented extensions.
UDTs, like other objects such as tables, indexes, and UDFs, need to be defined with a CREATE statement.
Use the CREATE DISTINCT TYPE statement to define your new UDT. Detailed explanations for the statement syntax and all its options are found in the SQL Reference.
For the CREATE DISTINCT TYPE statement, note that:
| Note: | As part of a UDT definition, DB2 always generates cast functions to: |
These functions are important for the manipulation of UDTs in queries.
The function path is used to resolve any references to an unqualified type name or function, except if the type name or function is
For information on how unqualified function references are resolved, see "Using Qualified Function Reference".
The following are examples of using CREATE DISTINCT TYPE:
Suppose you are writing applications that need to handle different currencies and wish to ensure that DB2 does not allow these currencies to be compared or manipulated directly with one another in queries. Remember that conversions are necessary whenever you want to compare values of different currencies. So you define as many UDTs as you need; one for each currency that you may need to represent:
CREATE DISTINCT TYPE US_DOLLAR AS DECIMAL (9,2) WITH COMPARISONS
CREATE DISTINCT TYPE CANADIAN_DOLLAR AS DECIMAL (9,2) WITH COMPARISONS
CREATE DISTINCT TYPE GERMAN_MARK AS DECIMAL (9,2) WITH COMPARISONS
Note that you have to specify WITH COMPARISONS since comparison operators are supported on DECIMAL (9,2).
Suppose you would like to keep the form filled by applicants to your company in a DB2 table and you are going to use functions to extract the information from these forms. Because these functions cannot be applied to regular character strings (because they are certainly not able to find the information they are supposed to return), you define a UDT to represent the filled forms:
CREATE DISTINCT TYPE PERSONAL.APPLICATION_FORM AS CLOB(32K)
Because DB2 does not support comparisons on CLOBs, you do not specify the clause WITH COMPARISONS. You have specified a schema name different from your authorization ID since you have DBADM authority, and you would like to keep all UDTs and UDFs dealing with applicant forms in the same schema.
After you have defined several UDTs, you can start defining tables with columns whose types are UDTs. Following are examples using CREATE TABLE:
Suppose you want to define tables to keep your company's sales in different countries as follows:
CREATE TABLE US_SALES
(PRODUCT_ITEM INTEGER,
MONTH INTEGER CHECK (MONTH BETWEEN 1 AND 12),
YEAR INTEGER CHECK (YEAR > 1985),
TOTAL US_DOLLAR)
CREATE TABLE CANADIAN_SALES
(PRODUCT_ITEM INTEGER,
MONTH INTEGER CHECK (MONTH BETWEEN 1 AND 12),
YEAR INTEGER CHECK (YEAR > 1985),
TOTAL CANADIAN_DOLLAR)
CREATE TABLE GERMAN_SALES
(PRODUCT_ITEM INTEGER,
MONTH INTEGER CHECK (MONTH BETWEEN 1 AND 12),
YEAR INTEGER CHECK (YEAR > 1985),
TOTAL GERMAN_MARK)
The UDTs in the above examples are created using the same CREATE DISTINCT TYPE statements in "Example: Money". Note that the above examples use check constraints. For information on check constraints see the SQL Reference.
Suppose you need to define a table where you keep the forms filled out by applicants as follows:
CREATE TABLE APPLICATIONS
(ID SYSIBM.INTEGER,
NAME VARCHAR (30),
APPLICATION_DATE SYSIBM.DATE,
FORM PERSONAL.APPLICATION_FORM)
You have fully qualified the UDT name because its qualifier is not the same as your authorization ID and you have not changed the default function path. Remember that whenever type and function names are not fully qualified, DB2 searches through the schemas listed in the current function path and looks for a type or function name matching the given unqualified name. Because SYSIBM is always considered (if it has been omitted) in the current function path, you can omit the qualification of built-in data types. For example, you can execute SET CURRENT FUNCTION PATH = cheryl and the value of the current function path special register will be "CHERYL", and does not include "SYSIBM". Now, if CHERYL.INTEGER type is not defined, the statement CREATE TABLE FOO(COL1 INTEGER) still succeeds because SYSIBM is always considered as COL1 is of type SYSIBM.INTEGER.
You are, however, allowed to fully qualify the built-in data types if you wish to do so. Details about the use of the current function path are discussed in the SQL Reference.
One of the most important concepts associated with UDTs is strong typing. Strong typing guarantees that only functions and operators defined on the UDT can be applied to its instances.
Strong typing is important to ensure that the instances of your UDTs are correct. For example, if you have defined a function to convert US dollars to Canadian dollars according to the current exchange rate, you do not want this same function to be used to convert German marks to Canadian dollars because it will certainly return the wrong amount.
As a consequence of strong typing, DB2 does not allow you to write queries that compare, for example, UDT instances with instances of the UDT source type. For the same reason, DB2 will not let you apply functions defined on other types to UDTs. If you want to compare instances of UDTs with instances of another type, you have to cast the instances of one or the other type. In the same sense, you have to cast the UDT instance to the type of the parameter of a function that is not defined on a UDT if you want to apply this function to a UDT instance.
The following are examples of manipulating UDTs:
Suppose you want to know which products sold more than US $100 000.00 in the US in the month of July, 1992 (7/92).
SELECT PRODUCT_ITEM
FROM US_SALES
WHERE TOTAL > US_DOLLAR (100000)
AND month = 7
AND year = 1992
Because you cannot compare US dollars with instances of the source type of US dollars (that is, DECIMAL) directly, you have used the cast function provided by DB2 to cast from DECIMAL to US dollars. You can also use the other cast function provided by DB2 (that is, the one to cast from US dollars to DECIMAL) and cast the column total to DECIMAL. Either way you decide to cast, from or to the UDT, you can use the cast specification notation to perform the casting, or the functional notation. That is, you could have written the above query as:
SELECT PRODUCT_ITEM
FROM US_SALES
WHERE TOTAL > CAST (100000 AS us_dollar)
AND MONTH = 7
AND YEAR = 1992
Suppose you want to define a UDF that converts Canadian dollars to U.S. dollars. Suppose you can obtain the current exchange rate from a file managed outside of DB2. You would then define a UDF that obtains a value in Canadian dollars, accesses the exchange rate file, and returns the corresponding amount in U.S. dollars.
At first glance, such a UDF may appear easy to write. However, C does not support DECIMAL values. The UDTs representing different currencies have been defined as DECIMAL. Your UDF will need to receive and return DOUBLE values, since this is the only data type provided by C that allows the representation of a DECIMAL value without losing the decimal precision. Thus, your UDF should be defined as follows:
CREATE FUNCTION CDN_TO_US_DOUBLE(DOUBLE) RETURNS DOUBLE
EXTERNAL NAME '/u/finance/funcdir/currencies!cdn2us'
LANGUAGE C
PARAMETER STYLE DB2SQL
NO SQL
NOT DETERMINISTIC
NO EXTERNAL ACTION
FENCED
The exchange rate between Canadian and U.S. dollars may change between two invocations of the UDF, so you declare it as NOT DETERMINISTIC.
The question now is, how do you pass Canadian dollars to this UDF and get U.S. dollars from it? The Canadian dollars must be cast to DECIMAL values. The DECIMAL values must be cast to DOUBLE. You also need to have the returned DOUBLE value cast to DECIMAL and the DECIMAL value cast to U.S. dollars.
Such casts are performed automatically by DB2 anytime you define sourced UDFs, whose parameter and return type do not exactly match the parameter and return type of the source function. Therefore, you need to define two sourced UDFs. The first brings the DOUBLE values to a DECIMAL representation. The second brings the DECIMAL values to the UDT. That is, you define the following:
CREATE FUNCTION CDN_TO_US_DEC (DECIMAL(9,2)) RETURNS DECIMAL(9,2)
SOURCE CDN_TO_US_DOUBLE (DOUBLE)
CREATE FUNCTION US_DOLLAR (CANADIAN_DOLLAR) RETURNS US_DOLLAR
SOURCE CDN_TO_US_DEC (DECIMAL())
Note that an invocation of the US_DOLLAR function as in US_DOLLAR(C1), where C1 is a column whose type is Canadian dollars, has the same effect as invoking:
US_DOLLAR (DECIMAL(CDN_TO_US_DOUBLE (DOUBLE (DECIMAL (C1)))))
That is, C1 (in Canadian dollars) is cast to decimal which in turn is cast to a double value that is passed to the CDN_TO_US_DOUBLE function. This function accesses the exchange rate file and returns a double value (representing the amount in U.S. dollars) that is cast to decimal, and then to U.S. dollars.
A function to convert German Marks to U.S. dollars would be similar to the example above:
CREATE FUNCTION GERMAN_TO_US_DOUBL(DOUBLE)
RETURNS DOUBLE
EXTERNAL NAME '/u/finance/funcdir/currencies!german2us'
LANGUAGE C
PARAMETER STYLE DB2SQL
NO SQL
NOT DETERMINISTIC
NO EXTERNAL ACTION
FENCED
CREATE FUNCTION GERMAN_TO_US_DEC (DECIMAL(9,2))
RETURNS DECIMAL(9,2)
SOURCE GERMAN_TO_US_DOUBL (DOUBLE)
CREATE FUNCTION US_DOLLAR(GERMAN_MARK) RETURNS US_DOLLAR
SOURCE GERMAN_TO_US_DEC (DECIMAL())
Suppose you want to know which products sold more in the US than in Canada and Germany for the month of March, 1989 (3/89):
SELECT US.PRODUCT_ITEM, US.TOTAL
FROM US_SALES AS US, CANADIAN_SALES AS CDN, GERMAN_SALES AS GERMAN
WHERE US.PRODUCT_ITEM = CDN.PRODUCT_ITEM
AND US.PRODUCT_ITEM = GERMAN.PRODUCT_ITEM
AND US.TOTAL > US_DOLLAR (CDN.TOTAL)
AND US.TOTAL > US_DOLLAR (GERMAN.TOTAL)
AND US.MONTH = 3
AND US.YEAR = 1989
AND CDN.MONTH = 3
AND CDN.YEAR = 1989
AND GERMAN.MONTH = 3
AND GERMAN.YEAR = 1989
Because you cannot directly compare US dollars with Canadian dollars or German Marks, you use the UDF to cast the amount in Canadian dollars to US dollars, and the UDF to cast the amount in German Marks to US dollars. You cannot cast them all to DECIMAL and compare the converted DECIMAL values because the amounts are not monetarily comparable. That is, the amounts are not in the same currency.
Suppose you have defined a sourced UDF on the built-in SUM function to support SUM on German Marks:
CREATE FUNCTION SUM (GERMAN_MARKS)
RETURNS GERMAN_MARKS
SOURCE SYSIBM.SUM (DECIMAL())
You want to know the total of sales in Germany for each product in the year of 1994. You would like to obtain the total sales in US dollars:
SELECT PRODUCT_ITEM, US_DOLLAR (SUM (TOTAL))
FROM GERMAN_SALES
WHERE YEAR = 1994
GROUP BY PRODUCT_ITEM
You could not write SUM (us_dollar (total)), unless you had defined a SUM function on US dollar in a manner similar to the above.
Suppose you want to store the form filled by a new applicant into the database. You have defined a host variable containing the character string value used to represent the filled form:
EXEC SQL BEGIN DECLARE SECTION;
SQL TYPE IS CLOB(32K) hv_form;
EXEC SQL END DECLARE SECTION;
/* Code to fill hv_form */
INSERT INTO APPLICATIONS
VALUES (134523, 'Peter Holland', CURRENT DATE, :hv_form)
You do not explicitly invoke the cast function to convert the character string to the UDT personal.application_form because DB2 lets you assign instances of the source type of a UDT to targets having that UDT.
If you want to use the same statement given in "Example: Assignments Involving UDTs" in dynamic SQL, you can use parameter markers as follows:
EXEC SQL BEGIN DECLARE SECTION;
long id;
char name[30];
SQL TYPE IS CLOB(32K) form;
char command[80];
EXEC SQL END DECLARE SECTION;
/* Code to fill host variables */
strcpy(command,"INSERT INTO APPLICATIONS VALUES");
strcat(command,"(?, ?, CURRENT DATE, CAST (? AS CLOB(32K)))");
EXEC SQL PREPARE APP_INSERT FROM :command;
EXEC SQL EXECUTE APP_INSERT USING :id, :name, :form;
You made use of DB2's cast specification to tell DB2 that the type of the parameter marker is CLOB(32K), a type that is assignable to the UDT column. Remember that you cannot declare a host variable of a UDT type, since host languages do not support UDTs. Therefore, you cannot specify that the type of a parameter marker is a UDT.
Suppose you have defined two sourced UDFs on the built-in SUM function to support SUM on US and Canadian dollars, similar to the UDF sourced on German Marks in "Example: Sourced UDFs Involving UDTs":
CREATE FUNCTION SUM (CANADIAN_DOLLAR)
RETURNS CANADIAN_DOLLAR
SOURCE SYSIBM.SUM (DECIMAL())
CREATE FUNCTION SUM (US_DOLLAR)
RETURNS US_DOLLAR
SOURCE SYSIBM.SUM (DECIMAL())
Now suppose your supervisor requests that you maintain the annual total sales in US dollars of each product and in each country, in separate tables:
CREATE TABLE US_SALES_94
(PRODUCT_ITEM INTEGER,
TOTAL US_DOLLAR)
CREATE TABLE GERMAN_SALES_94
(PRODUCT_ITEM INTEGER,
TOTAL US_DOLLAR)
CREATE TABLE CANADIAN_SALES_94
(PRODUCT_ITEM INTEGER,
TOTAL US_DOLLAR)
INSERT INTO US_SALES_94
SELECT PRODUCT_ITEM, SUM (TOTAL)
FROM US_SALES
WHERE YEAR = 1994
GROUP BY PRODUCT_ITEM
INSERT INTO GERMAN_SALES_94
SELECT PRODUCT_ITEM, US_DOLLAR (SUM (TOTAL))
FROM GERMAN_SALES
WHERE YEAR = 1994
GROUP BY PRODUCT_ITEM
INSERT INTO CANADIAN_SALES_94
SELECT PRODUCT_ITEM, US_DOLLAR (SUM (TOTAL))
FROM CANADIAN_SALES
WHERE YEAR = 1994
GROUP BY PRODUCT_ITEM
You explicitly cast the amounts in Canadian dollars and German Marks to US dollars since different UDTs are not directly assignable to each other. You cannot use the cast specification syntax because UDTs can only be cast to their own source type.
Suppose you would like to provide your American users with a view containing all the sales of every product of your company:
CREATE VIEW ALL_SALES AS
SELECT PRODUCT_ITEM, MONTH, YEAR, TOTAL
FROM US_SALES
UNION
SELECT PRODUCT_ITEM, MONTH, YEAR, US_DOLLAR (TOTAL)
FROM CANADIAN_SALES
UNION
SELECT PRODUCT_ITEM, MONTH, YEAR, US_DOLLAR (TOTAL)
FROM GERMAN_SALES
You cast Canadian dollars to US dollars and German Marks to US dollars because UDTs are union compatible only with the same UDT. Note that you have to use the functional notation to cast between UDTs since the cast specification only lets you cast between UDTs and their source types.