Using SQL User Defined Types

One of the most important qualities of a well designed database is that all the like fields, e.g. invoice number, share the same characteristics. With DDS files I can do this using a reference file, which contains all the types of field I could want. In an earlier post I showed how you could create a SQL table using column definitions based upon those from another table or file, see Defining SQL tables using a reference file.

SQL offers an alternative, a way to define my own data types which can have their own unique characteristics. I can define a data type for example for Amount, Quantity, and Customer id, and then use these when creating tables, etc.

SQL User Data Types, UDT, are their own object type, *SQLUDT, and are creating using a CREATE TYPE statement in SQL. The syntax is straight forward:

CREATE TYPE <library>/<user date type name> AS <SQL data type> WITH COMPARISONS

The WITH COMPARISONS is only required for compatibility with other products in the DB2 family. I include it in all CREATE TYPE just for consistency.

If a library is not given then the UDT is placed in QGPL. Personally I do not like my objects in QGPL, therefore, I create them in a library which is common to all development groups. This allows all the developers to use them. And this library can be easily copied from one IBM i partition or server to others.

Let me define three UDTs. I can do this using any SQL client, including STRSQL or SQL from the Operations Navigator:

CREATE TYPE MYLIB/AMOUNT AS DECIMAL(9,2) CREATE TYPE MYLIB/QUANTITY AS DECIMAL(7,0) WITH COMPARISONS CREATE TYPE MYLIB/CUSTOMER_ID AS CHAR(7) CCSID 37 WITH COMPARISONS

Each time I create a UDT I receive the following message:

Distinct type or array type AMOUNT created in MYLIB. Distinct type or array type QUANTITY created in MYLIB. Distinct type or array type CUSTOMER_ID created in MYLIB.

If I want see these UDTs in MYLIB I can just use the Work Object command, WRKOBJ:

WRKOBJ OBJ(MYLIB/*ALL) OBJTYPE(*SQLUDT)

Which shows me:

Object Type Library AMOUNT *SQLUDT MYLIB CUSTO00001 *SQLUDT MYLIB QUANTITY *SQLUDT MYLIB

My UDT CUSTOMER_ID, is longer than ten characters, therefore, it has been abbreviated to CUSTO00001.

Fortunately IBM has provided a table of all the data types, both system and user created, called SYSTYPES. I am not going to list all the columns in the table, if you want to see all the columns you will find a link to the IBM documentation for this table at the bottom of this post.

The columns I care about are:

Short column name	Data type	Description
TYPENAME	VARCHAR(128)	Name of the data type
METATYPE	CHAR(1)	Type of data type. S=System predefined, T=User defined
LENGTH	INTEGER	Length if the data type
SCALE	SMALLINT	If applicable the number of decimal places, or null
SRCTYPE	VARCHAR(128)	Source data type for this data type
DEFINER	VARCHAR(128)	Name of the user who created this data type
TYPESCHEMA	VARCHAR(128)	Library where the data type resides
SYSTNAME	CHAR(10)	System name of the data type
CCSID	INTEGER	If applicable the CCSID for the data type, or null

I can now create a SQL statement to list all of the UDT on my IBM i partition:

SELECT CAST(TYPENAME AS CHAR(20)) AS TYPE_NAME, METATYPE,LENGTH,SCALE, CAST(SRCTYPE AS CHAR(10)) AS SOURCE_TYPE, CAST(DEFINER AS CHAR(10)) AS DEFINER, CAST(TYPESCHEMA AS CHAR(10)) AS LIBRARY, SYSTNAME AS SYS_NAME, CCSID FROM SYSTYPES WHERE METATYPE = 'T'

This returns:

TYPE_NAME METATYPE LENGTH SCALE SOURCE_TYPE DEFINER LIBRARY SYS_NAME CCSID AMOUNT T 9 2 DECIMAL SIMON MYLIB AMOUNT - QUANTITY T 7 0 DECIMAL SIMON MYLIB QUANTITY - CUSTOMER_ID T 7 - CHARACTER SIMON MYLIB CUSTO00001 37

If I wanted to see all the data types I would remove the "WHERE METATYPE = 'T'" line.

Having defined these UDTs I can now use them in a CREATE TABLE:

01 CREATE TABLE MYLIB/TABLE1 ( 02 CUSTOMER_ID FOR COLUMN "CUSTID" CUSTOMER_ID NOT NULL, 03 ORDER_NBR FOR COLUMN "ORDERNBR" CHAR(9) NOT NULL, 04 CUSTOMER_ORDER_NBR FOR COLUMN "CUSTORDER" CHAR(15), 05 ORDER_DATE FOR COLUMN "ORDERDTE" TIMESTAMP NOT NULL, 06 SALES_AMOUNT FOR COLUMN "SALESAMT" AMOUNT, 07 DISCOUNT_AMOUNT FOR COLUMN "DISCOUNT" AMOUNT, 08 SALES_QTY FOR "SALESQTY" QUANTITY, 09 PRIMARY KEY (CUSTOMER_ID,ORDER_NBR,ORDER_DATE) 10 )

Line 2: The CUSTOMER_ID column has been defined using the CUSTOMER_ID UDT. This could have been coded without the second CUSTOMER_ID and the compiler would look for a data type with the name of the column to define the file.

Lines 6 and 7: SALES_AMOUNT and DISCOUNT_AMOUNT have been defined using the AMOUNT UDT.

Line 8: SALES_QTY has been defined using the QUANTITY UDT.

To delete a UDT I can simply enter the following in my preferred SQL client:

DROP TYPE MYLIB/AMOUNT

Which responds with the following message:

Drop of AMOUNT in MYLIB complete.

There is one big gotcha when using UDTs, when a column is defined with a UDT it is considered "distinct" from all other data types. In my examples any column defined with as CUSTOMER_ID or AMOUNT cannot be compared to CHARACTER or DECIMAL directly. I need to use CAST to change the definition of the column's data. For example:

SELECT CUSTOMER_ID, SALES_AMOUNT, SALES_QTY, (SALES_AMOUNT / SALES_QTY) AS UNIT_PRICE FROM TABLE1

SALES_AMOUNT is defined as UDT AMOUNT, and SALES_QTY as UDT QUANTITY. Both of these UDTs were defined using DECIMAL. But when I run this statement I get an error:

Message ID . . . . . . : SQL0402 Severity . . . . . . . : 30 Message type . . . . . : Diagnostic Message . . . . : / use not valid. Cause . . . . . : An operand has been specified for the arithmetic function or operator / that is not valid. -- User-defined types cannot be specified as operands of operators or scalar functions. User-defined types can only be specified with operators and within user-defined functions created specifically for that type.

Therefore, I must CAST these two columns to DECIMAL so I can perform the division:

SELECT CUSTOMER_ID, SALES_AMOUNT, SALES_QTY, ((CAST(SALES_AMOUNT AS DECIMAL) / CAST(SALES_QTY AS DECIMAL)) AS UNIT_PRICE FROM TABLE1

The same is true with the CUSTOMER_ID UDT. Even though it was defined using CHAR, I cannot use it like a CHAR. For example:

SELECT 'CUSTOMER ID ' || CUSTOMER_ID FROM TABLE1

This simple concatenation fails with the following error:

Message ID . . . . . . : SQL0171 Severity . . . . . . . : 30 Message type . . . . . : Diagnostic Message . . . . : Argument 2 of function CONCAT not valid. Cause . . . . . : The data type, length, or value of argument 2 of function CONCAT specified is not valid.

I need to CAST CUSTOMER_ID to character before I use it:

SELECT 'CUSTOMER ID ' || CAST(CUSTOMER_ID AS CHAR(30)) FROM TABLE1

Despite the gotcha using User Data Types does make it easier to define multiple columns across multiple tables with the same attributes. Which in my opinion makes them a valuable part of any IBM i developers quiver of tools.

 

You can learn more about this from the IBM website:

• User defined data types
• CREATE TYPE (distinct)
• SYSTYPE view

 

This article was written for IBM i 7.2, and should work for earlier releases too.