DB2 user-defined data type UDT

DB2 User-Defined data type UDT DB2 User-Defined data type (User-Defined distinct types, UDT), which involves the custom Single-value type based on the DB2 built-in data type. 1. Creation Syntax: CREATE [distinct] TYPE distinct_type_name AS source_data_type with comparisons source_data_type: DB2 built-in data TYPE, character TYPE (char, varchar, etc.), date and time TYPE (date, time, timestamp, etc.), numeric type (integer, double, decimal, etc.), considering the platform compatibility, some data types need to be replaced by other types: float-> double or real: float Type should be replaced by double or real data type. Numeric-> decimal long varchar-> varchar, dlob, clob long vargraphic-> vargraphic or dbclobWITH COMPARISONS: allows the system to generate a conversion function to convert between a custom type and a base type. Because the user-defined single-value data type cannot be directly compared with the DB2 built-in type, you need to use the conversion function. The default created conversion function name is the same as the type name distinct_type_name. Note that:★This type of name must exist only as an object in the same schema of the database. duplicate names are not allowed.★Even the UDT created based on the same DB2 basic type cannot be compared directly. Because DB2 SQL PL supports strong data types, the comparison between different UDT needs to be displayed and converted.★The following functions are automatically created: the function for converting DB2 basic data types to UDT: The function for converting distinct_type_name from UDT to basic type: data_type_name if the base type is smallint, UDT can convert the integer type to a custom type. If the base type is char, UDT can convert the varchar type to a custom type. If the base type is graphic, UDT can convert the vargraphic type to a custom type.★With comparisons does not support the long varchar and long vargraphic data types. The following two custom data types are created: kilometers, miles db2 => create type kilometers as integer with comparisonsDB20000I SQL. Db2 => create type miles as integer with comparisonsDB20000I SQL command is successfully completed. After the two udts are created, the system automatically creates the following function: miles (integer): converts the integer data type to the miles data type integer (miles ): convert the miles data type to the integer data type kilometers (integer): Convert the integer data type to the kilometers type integer (kilometers ): convert kilometers type to integer type 2. create a table using the following to store travel information: create table travel (id char (9) not null, -- number kdistance kilometers, -- distance, unit kilometer, 1 km ≈ 0.6 miles mdistance miles, -- distance, unit mile, 1 mile ≈ 1.6 km constraint pk_travel primary key (id) production table structure: db2 => describe ta Ble travel data type column name mode data type name long decimal place NULL hour ------------------------------ ------ id sysibm character 9 0 No kdistance administ> KILOMETERS 0 0 0 is mdistance administ> MILES 0 0 is 3 records selected. Add data: db2 => insert into travel (id, kdistance) values ('jlcc00000000001 ', 12), ('gzgy00000000001', 20) The DB20000I SQL command is successfully completed. Db2 => select * from travelID kdistance mdistance --------------- ----------- JLCC00001 12 0GZGY00001 20 0 2 records have been selected. Update Data: db2 => update travel set mdistance = kdistanceDB21034E this command is processed as an SQL statement because it is an invalid "command line processor" command. During SQL processing, it returns: The SQL0408N value is not compatible with the Data Type of the value assignment target. The target name is "MDISTANCE ". SQLSTATE = 42821 correct update: convert kilometers data to a base integer, and perform other processing as follows: db2 => update travel set mdistance = integer (ceil (integer (kdistance) * 0.6) DB20000I SQL command is successfully completed. Db2 => select * from travelID kdistance mdistance --------------- ----------- JLCC00001 12 8GZGY00001 20 12 two records have been selected. Comparison between different data types: db2 => select * from travelID kdistance mdistance --------- --------------------- JLCC00001 12 8GZGY00001 20 12SCCD00001 29 34LNDL00001 45 33 4 records selected. Db2 => select count (*) from travel where mdistance> kdistanceSQL0401N operation ">" the Data Type of the operand is incompatible or cannot be compared. SQLSTATE = 42818 db2 =>? 42818 SQLSTATE 42818: The operands of an operator or function are incompatible or cannot be compared. By converting the kilometers type to its base type integer, you can compare it with the data of the miles type after converting to the miles type. In fact, all the comparison operations based on the custom Single-value data type need to convert the UDT to its base type for comparison or conversion operations. Db2 => select * from travel where mdistance> miles (integer (kdistance) id kdistance mdistance --------- ----------- SCCD00001 29 34 1 record selected. Db2 => select * from travel where mdistance> 20SQL0401N operation ">" the Data Type of the operand is incompatible or cannot be compared. SQLSTATE = 42818 db2 => select * from travel where mdistance> integer (20) SQL0401N operation ">" the Data Type of the operand is incompatible or cannot be compared. SQLSTATE = 42818 db2 => select * from travel where mdistance> miles (20) id kdistance mdistance --------- ----------------- SCCD00001 29 34LNDL00001 45 33 2 records selected.★In short, data of the same type can be compared! 3. When deleting drop [distinct] type type_name to delete a custom data type, note that no columns or other objects reference this UDT; otherwise, an error is reported: SQL0478N cannot perform DROP, ALTER, transfer ownership, or REVOKE on the object TYPE "TYPE" because it has a subordinate object "ADMINISTRATOR" of the TYPE "TABLE. TRAVEL ". SQLSTATE = 42893 db2 => drop type kilometersDB20000I SQL command is successfully completed. Db2 => the drop type milesDB20000I SQL command is successfully completed. Source: http://blog.csdn.net/bobo12082119/article/details/8770549--the End --