--- title: "These are User-defined Data Types in DB2" description: "Here are the user-defined data types that you can create in Zos, Linux, UNIX, and Windows explained." url: https://srinimf.com/2022/12/24/these-are-user-defined-data-types-in-db2/ date: 2022-12-24 modified: 2026-10-10 author: "Srini" image: https://i0.wp.com/srinimf.com/wp-content/uploads/2022/12/user-defined-data-types-in-db2.jpg?fit=2240%2C1260&ssl=1 categories: ["DB2"] tags: ["User-defined data types"] type: post lang: en --- # These are User-defined Data Types in DB2 In DB2, a tricky subject is user-defined data types. In addition to the built-in data types, you can create user-defined data types similar to built-in. Here you will know the creation and usage of distinct user-defined data types: CANDOL and USADOL. ## **User-defined distinct** Define a new data type based on a built-in type. This new type has the same features **as the built-in type**, but you can use it to ensure that only values of the same type are compared. For example, you can define a **Canadian dollar type (CANDOL) and a US dollar type(USADOL),** both based on DECIMAL(10,2). Both types are based on the same built-in type, but you won’t be able to compare them unless a conversion function is applied. The following CREATE TYPE statements create the CANDOL and USADOL UDTs, ## Here’s [how to](https://srinimf.com/2010/04/12/db2-udf/) create a distinct user-defined data type The create type statement creates data type you want. ``` CREATE DISTINCT TYPE CANDOL AS DECIMAL(10,2) WITH COMPARISONS; CREATE DISTINCT TYPE USADOL AS DECIMAL(10,2) WITH COMPARISONS; ``` DB2 automatically generates functions to perform casting between the base type and the distincttype, and comparison operators for comparing instances of the distinct type. ### Here’s how to use user-defined data type ``` CREATE TABLE ITEMS (ITEMID CHAR(5), PRICE CANDOL); INSERT INTO ITEMS VALUES('ABC11',CANDOL(30.50) ); ``` These are other data types you can create in Linux, UNIX, and WINDOWS versions. ## DB2 user-defined data types list **User-defined array** An array type is defined as an array with elements of another data type. [This type supports in Zos too.](https://www.ibm.com/docs/en/db2-for-zos/11?topic=statements-create-type) **User-defined cursor** A cursor type represents a reference to an underlying cursor. **User-defined row** A row type is a structure that can be used by SQL PL as a data type for variables and parameters to represent a row for simpler manipulation. It can also be used as the data type of an array. **User-defined structured** A **structured** data type has a structure that contains a sequence of attributes each of which has a data type. A structured type may be used as the type of a table, view, or column. For example, you can create a structured type named ADDRESS that contains data for street number, street name, city, and so forth. Then you can use this type when defining other tables, such as employees or suppliers because the same data is required for both. Also, structured types can have sub [types in](https://srinimf.com/2012/07/10/sql-db2-and-joins/) a hierarchical structure. This lets you store objects that belong to a hierarchy. **User-defined reference** When using structured types, you can define references to rows in another table using reference types. These references appear similar to referential constraints; however, they do not enforce relationships between the tables. References in tables let you specify queries in a different way. Reference - [Here’re Examples of SQL Queries on User-defined Data Type ](https://www.ibm.com/docs/en/db2/9.7?topic=statements-create-type) Related - [DB2: How to create user-defined data type](https://srinimf.com/2012/05/27/db2-user-defined-data-type/)DateMay 27, 2012 - [How to Understand Triggers in DB2 SQL Context](https://srinimf.com/2015/09/17/4-types-of-triggers-in-db2/)DateSeptember 17, 2015 - [SQL: Column Vs Table Vs Row Vs Table Functions](https://srinimf.com/2016/10/12/the-ultimate-notes-on-sql-functions-to-make-you-stronger/)DateOctober 12, 2016 ### Share this: - - [Tweet](https://twitter.com/share) - [![](https://i0.wp.com/assets.pinterest.com/images/pidgets/pinit_fg_en_rect_gray_20.png)](https://www.pinterest.com/pin/create/link/?url=https%3A%2F%2Fsrinimf.com%2F2022%2F12%2F24%2Fthese-are-user-defined-data-types-in-db2%2F&media=https%3A%2F%2Fi0.wp.com%2Fsrinimf.com%2Fwp-content%2Fuploads%2F2022%2F12%2Fuser-defined-data-types-in-db2.jpg%3Ffit%3D1200%252C675%26ssl%3D1&description=These%20are%20User-defined%20Data%20Types%20in%20DB2) - - [Share on Tumblr](https://www.tumblr.com/share) - [Share on WhatsApp (Opens in new window) WhatsApp](https://srinimf.com/2022/12/24/these-are-user-defined-data-types-in-db2/?share=jetpack-whatsapp) - [More](#) - - [Print (Opens in new window) Print](https://srinimf.com/2022/12/24/these-are-user-defined-data-types-in-db2/?share=print) - [Share on Reddit (Opens in new window) Reddit](https://srinimf.com/2022/12/24/these-are-user-defined-data-types-in-db2/?share=reddit) - [Share on Telegram (Opens in new window) Telegram](https://srinimf.com/2022/12/24/these-are-user-defined-data-types-in-db2/?share=telegram) - [Email a link to a friend (Opens in new window) Email](mailto:?subject=%5BShared%20Post%5D%20These%20are%20User-defined%20Data%20Types%20in%20DB2&body=https%3A%2F%2Fsrinimf.com%2F2022%2F12%2F24%2Fthese-are-user-defined-data-types-in-db2%2F&share=email) - ### Like this: Like Loading…