Use SQL statements to unload data from relational tables
into external tables, which are data files that are in table format,
and then load the data from the data files into the dimensional tables.
Before you begin
Before beginning, document a strategy for mapping data
in the relational database to the dimensional database.
About this task
To unload data from the relational database into external
tables and then load the data into the dimensional database:
Procedure
- Unload the data from a relational database to external
tables.
Repeat the following steps to create as many external
tables as are required for the data that you want to move.
- Use the CREATE EXTERNAL TABLE statement to describe
the location of the external table and the format of the data.
The following sample CREATE EXTERNAL TABLE statement creates
an external table called
emp_ext, with data stored
in a specified fixed format:
CREATE EXTERNAL TABLE emp_ext
( name CHAR(18) EXTERNAL CHAR(18),
hiredate DATE EXTERNAL CHAR(10),
address VARCHAR(40) EXTERNAL CHAR(40),
empno INTEGER EXTERNAL CHAR(6) )
USING (
FORMAT 'FIXED',
DATAFILES
("DISK:/work2/mydir/employee.unl")
);
- Use the INSERT...SELECT statement to map the relational
database table to the external table.
The following
sample INSERT statement loads the employee database table into the
external table called
emp_ext:
INSERT INTO emp_ext SELECT * FROM employee
The data from the employee database table is stored in a data
file called employee.unl.
- If necessary, copy or move the data files to the system
where the dimensional database is located.
- Load the data from the data files to the dimensional database.
Repeat the following steps to load all the data files that you
created in the previous steps.
- Use the CREATE EXTERNAL TABLE statement to describe
the location of the data file and the format of the data.
The
following code is a sample CREATE EXTERNAL TABLE statement:
CREATE EXTERNAL TABLE emp_ext
( name CHAR(18) EXTERNAL CHAR(18),
hiredate DATE EXTERNAL CHAR(10),
address VARCHAR(40) EXTERNAL CHAR(40),
empno INTEGER EXTERNAL CHAR(6) )
USING (
FORMAT 'FIXED',
DATAFILES
("DISK:/work3/mydir/employee.unl")
);
- Use the INSERT...SELECT statement to map the data from
the data file to the table in the dimensional database.
The
following sample INSERT statement loads the employee data file into
the employee database table:
INSERT INTO employee SELECT * FROM emp_ext