Working with User-Defined Distinct Types

User-defined distinct types are simple user-defined data types (UDTs) which are defined on existing DB2 data types. DB2 also supports other kinds of UDTs, which are beyond the scope of this book. In this book, UDT is only used to refer to the user-defined distinct type.

UDTs can be used to give your data semantic meaning. The syntax of creating UDTs is shown in Figure 3.3.

Figure 3.3. CREATE DISTINCT TYPE syntax.

 >>-CREATE DISTINCT TYPE--distinct-type-name--AS-----------------> >--| source-data-type |--WITH COMPARISONS---------------------->< 

The source-data-type can be any DB2 built-in data type discussed in this chapter. The WITH COMPARISONS clause allows you to use system-provided operators for source data types on your UDTs. The WITH COMPARISONS clause is not allowed with BLOB, CLOB, DBCLOB, LONG VARCHAR, LONG VARGRAPHIC, or DATALINK source data types.

Note

In DB2 UDB for iSeries and zSeries, the WITH COMPARISON clause is optional. Comparison operator functions will be created for all allowed source data types except for the DATALINK.

You can use the UDTs to enforce your business rules and prevent different data from being used improperly because DB2 SQL PL enforces strong data typing. Strong data typing requires more explicit casting when comparing different data types because the data types are not implicitly cast.

To show you an example, suppose you define the two following variables:

 DECLARE v_in_mile       DOUBLE; DECLARE v_in_kilometer  DOUBLE; 

Nothing will prevent you from performing incorrect operations such as

 IF (v_in_mile > v_in_kilometer) 

This operation is meaningless because you cannot compare miles with kilometers without converting one of them first. But DB2 is unable to tell this. To DB2, both variables are floating-point numbers. It is perfectly normal to add them or directly compare them. UDTs can be used to prevent such mistakes.

You can create two new data types: miles and kilometers.

 CREATE DISTINCT TYPE miles AS DOUBLE WITH COMPARISONS; CREATE DISTINCT TYPE kilometers AS DOUBLE WITH COMPARISONS; 

Then you can declare your variables using the UDTs instead:

 DECLARE v_in_mile       miles; DECLARE v_in_kilometer  kilometers; 

Now you will receive an SQL error

 SQL0401N The data types of the operands for the operation ">" are not compatible. LINE NUMBER=7.  SQLSTATE=42818 

if you try to execute the same statement:

 IF (v_in_mile > v_in_kilometer) 

If this error is somewhat expected, you might be surprised to learn that the following statement will also result in the same SQL error:

 IF (v_in_mile > 30.0) 

What is happening here? The answer is that DB2 requires you to explicitly cast both DOUBLE and kilometers data type to miles data type.

When you create one user-defined distinct data type, DB2 generates two casting functions for you: one to cast from UDT to the source data type and another to cast back.

In this example, for miles UDT, you have these two functions:

 MILES (DOUBLE) DOUBLE (MILES) 

Similarly, you have these two functions for kilometers UDT:

 KILOMETERS (DOUBLE) DOUBLE (KILOMETERS) 

In order for these two statements to work, they need to be rewritten using the casting functions:

 IF (v_in_mile > MILES(DOUBLE(v_in_kilometer)/1.6)) IF (v_in_mile > miles(30.0)) 

You have to cast the v_in_kilometers twice because there is no casting function between miles and kilometers unless you create it manually. The factor of 1.6 is added to convert kilometers into miles.