Unloading data with named pipes

You can use a named pipe to unload data from the database to external tables.

To use named pipes to unload data to external tables, follow these steps:

  1. Specify the named pipe in the DATAFILES clause of either the CREATE EXTERNAL TABLE statement or the SELECT INTO EXTERNAL statement of SQL.
    DATAFILES ("PIPE:/usr/local/TAPE")
  2. Create the named pipes that you specified in the DATAFILES clause. Use operating-system commands to create the named pipes.
  3. Open the named pipes with a program that writes to the named pipe.
  4. Unload data to the named pipe.
    CREATE EXTERNAL TABLE emp_ext 
       ( name CHAR(18) EXTERNAL CHAR(20),
         hiredate DATE EXTERNAL CHAR(10),
         address VARCHAR(40) EXTERNAL CHAR(40),
         empno INTEGER EXTERNAL CHAR(6) )
       USING (
          FORMAT 'FIXED',
          DATAFILES 
          ("PIPE:/usr/local/TAPE")
           );
    
    INSERT INTO emp_ext SELECT * FROM employee;
Important: If you do not create and open the named pipes before you execute the SELECT or INSERT statement, the unload fails with the ENXIO error message (no such device or address).

Copyright© 2019 HCL Technologies Limited