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:
This article was written for IBM i 7.2, and should work for earlier releases too.
You have a list with three items. The links are reversed for the bottom two. Cicking CREATE TYPE (distinct) directs to SYSTYPE view, and vice versa.
ReplyDeleteOops.. correction made.
DeleteThank you for bringing that to my attention
What happens when you need to change a *UDT? For instance making an amount bigger from 9,2 to 11,2. Do you need to alter the tables affected?
ReplyDeleteThere is no ALTER TYPE that I can find, therefore, the only way to "change" a UDT is to DROP it & then CREATE.
DeleteBut you cannot DROP a UDT if it is used in a table.
You need to "set path" to work with udts on sql by using naming sql convention, for example if customer uses ODBC.
ReplyDeleteAlso nice to know udt in libary1 <> udt in libary2 even if the definition is exactly same.