These examples show how UDT columns can be used to define unique and nonunique secondary indexes for a table or join index.
First define the UDTs that are used for the example columns.
The type tbl_integer is a distinct UDT based on the INTEGER data type.
CREATE TYPE tbl_integer AS INTEGER FINAL;
The type tbl_char50 is also a distinct UDT and is based on the CHARACTER data type.
CREATE TYPE tbl_char50 AS CHARACTER(50) FINAL;
Assume you define the following tables, where tbl_integer and tbl_char50 are both UDT data types. Note that the UPI for table_1 and the NUPI for table_2 are also defined on UDT columns.
CREATE TABLE table_1 ( id tbl_integer, emp_name tbl_char50) UNIQUE PRIMARY INDEX(id); CREATE TABLE table_2 ( id tbl_integer, emp_name tbl_char50, start_date DATE) INDEX(emp_name);
The following USI is defined on the UDT column emp_name of table_1.
CREATE UNIQUE INDEX idx_1(emp_name) ON table_1;
The following NUSI is defined on the UDT columns emp_name and start_date of table_2.
CREATE INDEX idx_2(emp_name, start_date) ON table_2;