This example set demonstrates how you can create a table that specifies a UDT column in a primary or secondary index or both.
First define the UDTs that are used for the example columns.
UDT tbl_integer is a distinct UDT based on the INTEGER data type.
CREATE TYPE tbl_integer AS INTEGER FINAL;
UDT tbl_char50 is also a distinct UDT and is based on the CHARACTER data type.
CREATE TYPE tbl_char50 AS CHARACTER(50) FINAL;
EXSP_STRUCTURE_LOB is a structured UDT based on the INTEGER and CLOB data types.
CREATE TYPE EXSP_STRUCTURE_LOB AS ( age INTEGER, c1 CLOB(512), c2 CLOB(512), cnt INTEGER, c3 CLOB(512)) NOT FINAL CONSTRUCTOR METHOD exsp_structure_lob( file1 VARCHAR(20), file2 VARCHAR(20), file3 VARCHAR(20)) RETURNS exsp_structure_lob SPECIFIC lobconstructor SELF AS RESULT NO SQL PARAMETER STYLE TD_GENERAL DETERMINISTIC LANGUAGE C;
The first example table defines its UPI on the distinct UDT column id.
CREATE TABLE table_1 ( id tbl_integer, emp_name tbl_char50) UNIQUE PRIMARY INDEX(id);
The second example table defines its NUPI on the distinct UDT column id.
CREATE TABLE table_2 ( id tbl_integer, emp_name tbl_char50) PRIMARY INDEX(id);
The third example table defines a USI on the distinct UDT column emp_name.
CREATE TABLE table_3 ( id tbl_integer, emp_name tbl_char50) UNIQUE INDEX(emp_name);
The fourth example table defines a NUSI on the distinct UDT column emp_name.
CREATE TABLE table_4 ( id tbl_integer, emp_name tbl_char50, start_date DATE) INDEX(emp_name);
The fifth example table defines a composite NUSI on the distinct UDT column emp_name and the DATE column start_date.
CREATE TABLE table_5 ( id tbl_integer, emp_name tbl_char50, start_date DATE) INDEX(emp_name, start_date);