Using the dbload utility with named row types

The procedure for using the dbload utility with named row types is somewhat different than the procedure for using dbload with other complex data types, because named row types are actually user-defined data types.

Suppose you have a table named person that contains one column with a named row type. Also suppose that the person_t named row type contains six fields: name, address, city, state, zip, and bdate.

CREATE ROW TYPE person_t 
   ( 
      name VARCHAR(30) NOT NULL, 
      address VARCHAR(20), 
      city VARCHAR(20), 
      state CHAR(2), 
      zip VARCHAR(9), 
      bdate DATE 
   ); 
CREATE TABLE person of TYPE person_t;

To load data for a named row type (or for any user-defined data type)

1. Use the UNLOAD statement to unload the table to an input file. In this example, the input file sees the named row type as six separate fields:

   Brown, James|13 First St.|San Francisco|CA|94070|01/04/1940|
   Karen Smith|1820 Elm Ave #100|Fremont|CA|94502|01/13/1983| 

2. Use the dbschema utility to capture the schema of the table and the row type. You must use the dbschema -u option to pick up the named row type.

        dbschema -d stores_demo -u person_t > schema.sql
        dbschema -d stores_demo -t person >> schema.sql
   

3. Use DB-Access to re-create the person table in the new database.

   For detailed steps, see Use dbschema output as DB-Access input.

4. Create the dbload command file. This dbload command file inserts two rows into the person table in the new database.

   FILE person.unl DELIMITER '|' 6;
   INSERT INTO person;

   This dbload example shows how to insert new data rows into the person table. The number of rows in the INSERT statement and the dbload command file must match:

   FILE person.unl DELIMITER '|' 6; 
      INSERT INTO person 
      VALUES ('Jones, Richard', '95 East Ave.', 
              'Philadelphia', 'PA', 
      '19115', 
      '03/15/97');

5. Run the dbload command:

   dbload -d newdb -c uds_command -l errlog