Copying data from one instance to another using the PIPE option

You can use a named pipe to copy data from one Informix® 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 Informix® 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 Informix® 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.