This example shows some UDTs and UDFs being defined, as well as some tables with UDT columns. For an example that inserts rows into a table with UDT columns, refer to Example.
/* From CLI sample create.c */
/* ... */
/* Initialize SQL statement strings */
SQLCHAR * stmt[] = {
"CREATE DISTINCT TYPE CNUM AS INTEGER WITH COMPARISONS",
"CREATE DISTINCT TYPE PUNIT AS CHAR(2) WITH COMPARISONS",
"CREATE DISTINCT TYPE UPRICE AS DECIMAL(10, 2) "
"WITH COMPARISONS",
"CREATE DISTINCT TYPE PRICE AS DECIMAL(10, 2) "
"WITH COMPARISONS",
"CREATE FUNCTION PRICE( CHAR(12), PUNIT, char(16) ) "
"returns char(12) "
"NOT FENCED EXTERNAL NAME 'order!price' "
"NOT VARIANT NO SQL LANGUAGE C PARAMETER STYLE DB2SQL "
"NO EXTERNAL ACTION",
"CREATE DISTINCT TYPE PNUM AS INTEGER WITH COMPARISONS",
"CREATE FUNCTION \"+\"(PNUM, INTEGER) RETURNS PNUM "
"source sysibm.\"+\"(integer, integer)",
"CREATE FUNCTION MAX(PNUM) RETURNS PNUM "
"source max(integer)",
"CREATE DISTINCT TYPE ONUM AS INTEGER WITH COMPARISONS",
"CREATE TABLE CUSTOMER ( "
"Cust_Num CNUM NOT NULL, "
"First_Name CHAR(30) NOT NULL, "
"Last_Name CHAR(30) NOT NULL, "
"Street CHAR(128) WITH DEFAULT, "
"City CHAR(30) WITH DEFAULT, "
"Prov_State CHAR(30) WITH DEFAULT, "
"PZ_Code CHAR(9) WITH DEFAULT, "
"Country CHAR(30) WITH DEFAULT, "
"Phone_Num CHAR(20) WITH DEFAULT, "
"PRIMARY KEY (Cust_Num) )",
"CREATE TABLE PRODUCT ( "
"Prod_Num PNUM NOT NULL, "
"Description VARCHAR(256) NOT NULL, "
"Price DECIMAL(10,2) WITH DEFAULT , "
"Units PUNIT NOT NULL, "
"Combo CHAR(1) WITH DEFAULT, "
"PRIMARY KEY (Prod_Num), "
"CHECK (Units in (PUNIT('m'), PUNIT('l'), PUNIT('g'), PUNIT('kg'), "
"PUNIT(' '))) )",
"CREATE TABLE PROD_PARTS ( "
"Prod_Num PNUM NOT NULL, "
"Part_Num PNUM NOT NULL, "
"Quantity DECIMAL(14,7), "
"PRIMARY KEY (Prod_Num, Part_Num), "
"FOREIGN KEY (Prod_Num) REFERENCES Product, "
"FOREIGN KEY (Part_Num) REFERENCES Product, "
"CHECK (Prod_Num <> Part_Num) )",
"CREATE TABLE ORD_CUST ( "
"Ord_Num ONUM NOT NULL, "
"Cust_Num CNUM NOT NULL, "
"Ord_Date DATE NOT NULL, "
"PRIMARY KEY (Ord_Num), "
"FOREIGN KEY (Cust_Num) REFERENCES Customer )",
"CREATE TABLE ORD_LINE ( "
"Ord_Num ONUM NOT NULL, "
"Prod_Num PNUM NOT NULL, "
"Quantity DECIMAL(14,7), "
"PRIMARY KEY (Ord_Num, Prod_Num), "
"FOREIGN KEY (Prod_Num) REFERENCES Product, "
"FOREIGN KEY (Ord_Num) REFERENCES Ord_Cust )",
( char * ) 0,
} ;
/* ... */
/* Execute Direct statements */
i = 0 ;
while ( stmt[i] != ( char * ) 0 ) {
printf( ">Executing Statement %ld\n", ( i + 1 ) ) ;
rc = SQLExecDirect( hstmt, stmt[i], SQL_NTS ) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
i++ ;
}