Wednesday, April 20, 2016

Using SQL User Defined Types

sql user defined types udt

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.

5 comments:

  1. 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.

    ReplyDelete
    Replies
    1. Oops.. correction made.
      Thank you for bringing that to my attention

      Delete
  2. 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?

    ReplyDelete
    Replies
    1. There is no ALTER TYPE that I can find, therefore, the only way to "change" a UDT is to DROP it & then CREATE.

      But you cannot DROP a UDT if it is used in a table.

      Delete
  3. You need to "set path" to work with udts on sql by using naming sql convention, for example if customer uses ODBC.

    Also nice to know udt in libary1 <> udt in libary2 even if the definition is exactly same.

    ReplyDelete

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.