You can use a named pipe to copy data from one HCL
OneDB™ instance
to another without writing the data to an intermediate file.
Before you begin
You can use a named pipe to unload data from one HCL
OneDB instance
and load it into another instance without writing data to an intermediate
file. You can also use a named pipe to copy data from one table to
another on the same HCL
OneDB instance.
In the following example,
data is copied from a source table on one instance to a destination
table on a second instance.
Depending on the hardware platform
you are using, you must first create a named pipe using one of the
following commands. For this example, the named pipe is called pipe1.
% mkfifo /work/pipe1
% mknod /work/pipe1
About this task
Follow these steps to copy data from a table on a source
instance to a table on a destination instance on the same computer.
Procedure
- Create the source table on the source instance. In this
example, the source table is called source_data_table:
CREATE TABLE source_data_table
(
empid CHAR(5),
empname VARCHAR(40),
empaddr VARCHAR(100)
);
- Create the external table on the source instance. In this
example, the external table is named ext_table:
CREATE EXTERNAL TABLE ext_table
(
empid CHAR(5),
empname VARCHAR(40),
empaddr VARCHAR(100)
)
USING
(DATAFILES
(
'PIPE:/work/pipe1'
)
);
- Create the destination table on the destination instance.
In this example, the destination table is called destin_data_table:
CREATE TABLE destin_data_table
(
empid CHAR(5),
empname VARCHAR(40),
empaddr VARCHAR(100)
);
- Create the external table on the destination instance.
In this example, the external table is named ext_table:
CREATE EXTERNAL TABLE ext_table
(
empid CHAR(5),
empname VARCHAR(40),
empaddr VARCHAR(100)
)
USING
(DATAFILES
(
'PIPE:/work/pipe1_1'
)
);
- Run the following command from a UNIX™ shell. The command redirects data from /work/pipe1 to /work/pipe1_1
cat /work/pipe1 > /work/pipe1_1
- Run the following command on the destination instance to
direct data from the named pipe to the destination table:
INSERT INTO destin_data_table SELECT * FROM ext_table;
- Run the following command on the source instance to spool
data to the named pipe:
INSERT INTO ext_table SELECT * FROM source_data_table;
You
can use more than one pipe by inserting multiple PIPE statements in
the DATAFILES clause and creating a named pipe for each.