Loading from and Unloading to a Named Pipe

You can use a named pipe, also referred to as a first-in-first-out (FIFO) data file, to load from and unload to a nonstandard device, such as a tape drive.

Unlike ordinary operating-system files, named pipes do not have a 2-gigabyte size limitation. The operating system opens and checks for the end of file differently for named pipes than for ordinary files.

Loading with Named Pipes

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

1. Specify the named pipes in the DATAFILES clause of the CREATE EXTERNAL TABLE statement in SQL.

   Suppose you have a database server system with 16 coservers, but only 3 coservers have tape drives attached (for example, coservers 2, 5, and 9).

   1. Set up a cogroup to facilitate the load and unload commands. To set up the cogroup, use the onutil command, as the following example shows:

      % onutil
      1> CREATE COGROUP tape_group
      2> FROM cosv.2, cosv.5, cosv.9;
      Cogroup successfully created.

   2. Specify the file locations for the named pipes in the CREATE EXTERNAL TABLE statement.

      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:tape_group:/usr/local/TAPE.%c")
          );

      The filenames expand as follows:

      DATAFILES ("PIPE:2:/usr/local/TAPE.2",
         "PIPE:5:/usr/local/TAPE.5",
         "PIPE:9:/usr/local/TAPE.9")

2. Create the named pipes that you specified in the DATAFILES clause. Use operating-system commands to create the named pipes.

   Use the mknod UNIX command with the -p option to create a named pipe. To avoid blocking open problems for pipes on UNIX, start separate UNIX processes for pipe-readers and pipe-writers or open the pipes with the O_NDELAY flag set.

3. Open the named pipes with a program that reads the named pipe.

   The database server provides a sample pipe-reader program in $INFORMIXDIR/demo/xmp/rpipe.c and a sample pipe-writer program in $INFORMIXDIR/demo/xmp/wpipe.c.

4. Execute the INSERT statement in SQL.

   INSERT INTO employee SELECT * FROM emp_ext;

Using FIFO Virtual Processors

The database server uses FIFO virtual processors (VPs) to read and write to external tables on named pipes. The NUMFIFOVPS configuration parameter specifies the number of FIFO VPs for each coserver. The default number is 2.

The database server uses one FIFO VP for each named pipe that you specify in the DATAFILES clause of the CREATE EXTERNAL TABLE statement. For example, suppose you define an external table with the following SQL statement:

CREATE EXTERNAL TABLE atab_ext
   SAMEAS atab
   USING (
      DATAFILES("PIPE:1:/tmp/pipe1",
               "PIPE:1:/tmp/pipe2",
               "PIPE:1:/tmp/pipe3"
      ));

If you use the default value of 2 for FIFO VPs, the database server does not read from pipe3 until it finishes reading all the data from pipe1 or pipe2.

Unloading with Named Pipes

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

1. Specify the named pipes in the DATAFILES clause of either the CREATE EXTERNAL TABLE statement or the SELECT INTO EXTERNAL in SQL.

   DATAFILES ("PIPE:tape_group:/usr/local/TAPE.%c")

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.

   The database server provides a sample pipe-reader program in $INFORMIXDIR/demo/xmp/rpipe.c and a sample pipe-writer program in $INFORMIXDIR/demo/xmp/wpipe.c.

4. Unload to the named pipe.

   You can use the SELECT INTO EXTERNAL statement to unload from named pipes, as the following example shows:

   SELECT * FROM employee 
         WHERE hiredate > "1/1/1996" 
      INTO EXTERNAL emp_ext
      USING (
         FORMAT 'DELIMITED', 
       DATAFILES
         ("PIPE:2:/usr/local/TAPE.2",
         "PIPE:5:/usr/local/TAPE.5",
         "PIPE:9:/usr/local/TAPE.9")
       );

   Alternatively, you can use the following SQL statement sequence to unload from named pipes:

   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:tape_group:/usr/local/TAPE.%c")
          );
   
   INSERT INTO emp_ext SELECT * FROM employee;