Copying data from one instance to another using the PIPE option

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

  1. 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)
    );
  2. 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'
      )
    );
  3. 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)
    );
  4. 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'
      )
    );
  5. 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
  6. 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;
  7. 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.