Background
Back in the early 1990s when I first started working on database language architecture, DB2 for Linux, Unix, and Windows was a new DB2 database manager (I believe we called it DB2 for common servers back then) and making a big jump in the SQL that it supported along with implementing the Starburst compiler with the research team from the Almaden Research Center. During that time, we started to make SQL more user-extensible with user-defined objects for procedures, functions and data types. The first kind of user-defined data type was the distinct type. User-defined distinct types provided a way for the SQL user to give names to data types which provides a self-documenting way of of defining relationships between columns, parameters, and variables. The key feature of the distinct type was strong typing - that is, it is distinct from all the other data types except for assignment to built-in SQL data type on which it was based. Even if 2 distinct types like HATSIZE and SHOESIZE that have the same base SQL type like SMALLINT, they can not be directly assigned to each other or compared to each other within SQL operations. When you defined a distinct type, a set of comparison functions was implicitly generated to allow comparison of operands with the same distinct type and a set of functions to cast between the distinct type and it's base type (in some cases like distinct types based on a fixed char type it even generated a function to cast from the data type of the constant associated with the data type family of the base data type, in this case a varchar). Overall, a nice feature.
Move forward about 15 years and our users wanted us to relax the boundaries between SQL data type from different type families. So, in DB2 9.7, we started to blur SQL data type boundaries for SQL types with more implicit casting between data type families. For example, this meant that values with character string data types could be compared with values that had numeric data types and possibly even used as arguments to functions that were defined with numeric parameters. Great! That meant I did not always have to remember to add that explicit cast when I knew the values could be used that way.
But what about user-defined distinct types? Well, they stayed strong! For those users that like strong typing, we really cannot take that capability away. But, not everyone wants such strong typing. We heard that many users just want to be able to give the SQL data type another name so you can use it to relate columns, parameters, and variables that share a common meaning and underlying data type. Then, if that data type changes, there is just one place to change it. However, the strong-typing characteristic of the distinct type just seems to cause you extra work casting back and forth or creating functions to handle operations that already work for the base type.
Weakly-typed distinct types
A feature added in DB2 10.1 Fix Pack 2 should provide those users what they would prefer. There is a now a way to create a distinct type that has weak typing rules instead of strong typing rules. When a distinct type is defined with weak type rules, it effectively acts as an alias for the SQL data type specified as the base data type. Just like a strongly-typed distinct type, this new form of distinct type can be used for columns, routine parameters, local, or global SQL variables. For example, if my application was dealing with colour codes, I could define a distinct type like this:
CREATE TYPE COLOURCODE AS CHAR(3) WITH WEAK TYPE RULES
The distinctly new clause here is "WITH WEAK TYPE RULES" which pretty clearly spells out how this is different from the strong type rules that had been the only form of distinct type since Version 2. By the way, you can now explicitly specify in the syntax that you want WITH STRONG TYPE RULES which will continue as the default when defining a distinct type.
So how does this weakly-typed distinct type called COLOURCODE behave?
Quite simply, it behaves as if the data type is CHAR(3). Whenever a column, parameter or variable of type COLOURCODE is an operand in a comparison or assignment, the behaviour is the same as that of a CHAR(3). This is also true for function resolution, where an argument of type COLOURCODE will match as if the argument had data type CHAR. One implication of this is that you cannot overload a function within the same schema to have all other parameters alike and only difference in definition being the weakly-typed distinct type and it's base type.
Constraining the distinct type values
By now you may be wondering about the "constrained twist" I referred to in the title.
Well that would be the ability to constrain the allowable values values for a distinct type with weak rules.
The simple constraint is enforcing that a null value is not allowed. This is done by specifying NOT NULL in the type definition. So, if we wanted the colour code to never be a null value, we could write the definition as:
CREATE TYPE COLOURCODE AS CHAR(3) WITH WEAK TYPE RULES NOT NULL
The real constraint flexibility comes with the capability to specify a data type check constraint with your weak type definition that looks very much like a table check constraint. The thing you need to know is how to refer to the value that you want to check within the check condition you specify. It is simply called VALUE. Easy enough to remember, right?
Let's see how this works by extending the COLOURCODE type example. Assume that the only colours allowed were red, green and blue with 3 character colour code values of 'RED', 'GRN', and 'BLU' respectively.
CREATE TYPE COLOURCODE AS CHAR(3) WITH WEAK TYPE RULES NOT NULL
CHECK (VALUE IN ('RED', 'GRN', 'BLU'))
Now we can try out the new data type using a global variable.
create variable colour colorcode
DB20000I The SQL command completed successfully.
set colour='GRN'
DB20000I The SQL command completed successfully.
values colour
1
---
GRN
1 record(s) selected.
So far, so good. We have a variable defined and it allowed a valid value. What happens with an invalid value?
set colour='YEL'
SQL20552N The cast or assignment failed because the value does not conform to
the data type constraint of the user-defined type. User-defined type:
"SWAGRMAN.COLOURCODE". Value: "YEL". SQLSTATE=23528
This simple example shows that an assignment to variable of type COLOURCODE using a value that is excluded by the data type constraint causes an error to be returned. I like being able to add a data rule for the data type once and then let the system manage the checking for you when ever there is an assignment or a cast to that data type. Same thing happens using a cast specification.
values cast('PNK' as colourcode)
1
---
SQL20552N The cast or assignment failed because the value does not conform to
the data type constraint of the user-defined type. User-defined type:
"SWAGRMAN.COLOURCODE". Value: "PNK". SQLSTATE=23528
Aside:
Those of you familiar with Oracle PL/SQL might be thinking that this is just like subtypes. It allows that capability, but using a data type check constraints goes beyond the capability for a subtype to have a numeric range checking specified. Sure, you can define a something to match the POSITVEN subtype like this:
CREATE TYPE POSITIVEN AS INTEGER WITH WEAK TYPE RULES
NOT NULL CHECK (VALUE BETWEEN 1 AND 2147483647)
You can also do similar statements for POSITIVE, NATURAL and NATURALN.
Cautionary words
There is a catch though when a data type constraint is specified. These data type constraints can only be used at the programming levels for variables and parameters. A weakly-typed distinct type with data type constraints can not be used as a column data type. Determining the appropriate behaviours for various actions involving both table check constraints and data type check constraints posed some interesting semantic and implementation challenges. Instead of holding back the capability from the use in the world of SQL PL, we proceeded with the restriction. The work around is to define the column with the base type, matching nullability constraint, and a table check constraint that matches the data type check constraint. This work around does not help with a scenario where you want to change something about the weakly-typed distinct type. Here's an example with the work around.
CREATE TABLE COLOURS (CODE CHAR(3) NOT NULL CHECK (CODE IN ('RED', 'GRN', 'BLU')),
DESCRIPTION VARCHAR(30))
Now I'll write a little procedure to insert a row in this table, but I'm going to intentionally make a sloppy coding error.
CREATE PROCEDURE ADDCOLOUR (IN CODE COLOURCODE, DESC VARCHAR(30))
BEGIN
DECLARE C1 COLOURCODE;
INSERT INTO COLOURS VALUES (C1, DESC);
END
CALL ADDCOLOUR ('GRN', 'GREEN')
SQL20552N The cast or assignment failed because the value does not conform to
the data type constraint of the user-defined type. User-defined type:
"SWAGRMAN.COLOURCODE". Value: "NULL". SQLSTATE=23528
What happened here? Well, I purposely neglected to do the assignment from the parameter called CODE to the local variable C1 so this would return the error. My first use of the local variable C1 occurs in the INSERT statement. This first reference checks that the value is a valid COLOURCODE. Except, as the error message indicates, C1 defaults to the value NULL, which violates the NOT NULL constraint. While I clearly meant to assign value to C1, the important message here is that when a weakly-typed distinct type is defined to be not null, any variables defined using the type should either have a non-null default specified or you need to ensure that the first reference is an assignment.
Summary
Distinct types can now be defined with weak type rules and with constraints on the values using the full power of a check constraint. One of the places where these weakly-typed distinct types with constraints can be really useful is when developing a module that includes a set of related functions, procedures, types, variables, and conditions. Using these types within a module you are developing can help you manage the related variables and parameters within the module and even evolve them as your design changes during development with chasing through the module objects to find all the places where you used a related parameter or variable.
Why not try these weakly-typed distinct types out for yourself!
Posted by: Rick Swagerman
100
