Using dbload with Collection Data Types

You can use dbload with collection data types such as SET, LIST, and MULTISET.

SET Data Type Example

In a SET, each element is unique, and no nulls are allowed. The number of elements in a SET can vary. The following statement creates a table in which the children column is defined as a SET:

CREATE TABLE employee 
   ( 
      name char(30), 
      address char(40), 
      children SET (varchar(30) NOT NULL) 
   );

The data from the employee table is unloaded into the employee.unl file. Each data row contains four delimited fields. The first set contains three elements (Karen, Lauren, and Andrea), whereas the second set contains four elements. The SET constructor precedes each SET data row.

Muriel|5555 SW Merry 
Sailing Dr.|02/06/1926|SET{'Karen','Lauren','Andrea'}| 
   Larry|1234 Indian Lane|07/31/1927|SET{'Martha', 
      'Melissa','Craig','Larry'}|

This dbload example shows how to insert data that contains SET data types into the employee table in the new database. Put double quotes around each SET data type or the insert does not work.

FILE employee.unl DELIMITER '|' 4; 
INSERT INTO employee 
VALUES ('Marvin', '10734 Pardee', '06/17/27', 
   "SET{'Joe', 'Ann'}");

LIST Data Type Example

A list is an ordered collection of elements that allows duplicate values. The following statement creates a table in which the month_sales column is defined as a LIST:

CREATE TABLE sales_person  
   (
      name CHAR(30),  
      month_sales LIST(MONEY NOT NULL)  
   );

The data from the sales_person table is unloaded into the sales.unl file. Each data row contains two delimited fields, as follows:

Jane Doe|LIST{'4.00','20.45','000.99'}|
Big Earner|LIST{'0000.00','00000.00','999.99'}|

This dbload example shows how to insert data that contains LIST data types into the sales_person table in the new database. Put double quotes around each LIST data type or the insert does not work.

FILE sales_person.unl DELIMITER '|' 2;
INSERT INTO sales_person 
VALUES ('Jenny Chow', "{587900, 600000}");

You can load multisets in a similar manner.