User-defined distinct types (UDDTs) are very useful in many situations. Let us consider a simple example and see for ourselves. Here is the table we shall be working with:
CREATE TABLE BOX(LENGTH FLOAT, WIDTH FLOAT, HEIGHT FLOAT, WEIGHT FLOAT, DESCRIPTION VARCHAR(100));
The developer's intention was to select information on all the boxes fitting into a pickup truck's trunk, but he made a typo (he typed weight instead of height):
SELECT * FROM BOX WHERE LENGTH<72 AND WIDTH<72 AND WEIGHT<60;
From the database engine's point of view, this is a perfectly legal query. The engine just executed the query with no warnings at all. Obviously the results returned by the query are incorrect. This typo is one of those bugs that are very easy to make and very difficult to find.
In fact, DB2 comes with a very useful feature, named user defined distinct types. Using them, you can protect your SQL from such mistakes. Let us see how we do it. First, we need to create two user defined types:
CREATE DISTINCT TYPE INCHES AS FLOAT WITH COMPARISONS; CREATE DISTINCT TYPE POUNDS AS FLOAT WITH COMPARISONS;
Having created the types, we can utilize them in the table's definition:
DROP TABLE BOX; CREATE TABLE BOX(LENGTH INCHES, WIDTH INCHES, HEIGHT INCHES, WEIGHT POUNDS, DESCRIPTION VARCHAR(100));
This is a self-documenting approach: it is clear from the very definition of the table that length, width and height are measured in inches. DB2 will not let you compare inches to just numbers, you need to explicitly convert them to inches, like this:
SELECT * FROM BOX WHERE LENGTH<INCHES(72) AND WIDTH<INCHES(72) AND WEIGHT<INCHES(60);
Note: when the type INCHES was created, DB2 also automatically created the function that converts the base type, FLOAT, into INCHES.
An attempt to run this query results in an error:
SQL0401N The data types of the operands for the operation "< " are not compatible.
And incompatible they are! WEIGHT may not be compared to inches. As we have seen, now DB2 was able to recognize the typo and raised an error.
DB2 will not allow you to compare inches to centimeters either, you will have to provide a function to handle the conversion for you:
CREATE DISTINCT TYPE CENTIMETERS AS FLOAT WITH COMPARISONS; CREATE FUNCTION INCHES_FROM_CM(length CENTIMETERS) RETURNS INCHES RETURN INCHES( CAST(length AS FLOAT) / 2.54)
Let us populate the table BOXES with some sample data and make sure that the conversion works:
INSERT INTO BOX VALUES(INCHES(10), INCHES(10), INCHES(10), POUNDS(20), 'SMALL HEAVY BOX'), (INCHES(20), INCHES(20), INCHES(20), POUNDS(200), 'LARGE HEAVY BOX'); SELECT * FROM BOX WHERE WIDTH<INCHES_FROM_CM(CENTIMETERS(25.5));
Also note that there is a system view named SYSIBM.USER_DEFINED_TYPES. Feel free to refer to it, like this:
SELECT CAST(USER_DEFINED_TYPE_NAME AS CHAR(20)) AS NAME, DATA_TYPE FROM SYSIBM.USER_DEFINED_TYPES NAME DATA_TYPE ---------------- --------------------- CENTIMETERS DOUBLE PRECISION POUNDS DOUBLE PRECISION INCHES DOUBLE PRECISION
Under the hood, user-defined distinct types are stored as their base types. For example, columns of types INCHES and POUNDS use up exactly as many bytes as a column of base type FLOAT would. Regarding performance, the simple cast functions that we were discussing also do not slow down execution at all.
About the author
Alexander Kuznetsov has 15 years of experience in software design, development and database administration. Currently he improves performance of applications running in a multi-terabyte database environment. Alexander is an IBM Certified Advanced Technical Expert (DB2 Cluster) and an IBM Certified Solutions Expert (Database Administration and Application Development).
For More Information
- Feedback: E-mail the editor with your thoughts about this tip.
- More tips: Hundreds of free DB2 tips and scripts.
- Tip contest: Have a DB2 tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
- Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
- Forums: Ask your technical DB2 questions--or help out your peers by answering them--in our active forums.
- Best Web Links: DB2 tips, tutorials, and scripts from around the Web.
This was first published in December 2003