Home | Previous Page | Next Page   Disk, Memory, and Process Management > Loading with External Tables for Extended Parallel Server > Loading Simple Large Objects >

Using Pipe Staging

Pipe staging is the process of writing simple large objects to a temporary file when they are too large to fit in memory. The database server uses staging with simple large objects when all of the following conditions apply:

Using Parallel Execution

The reader thread handles the staging of simple large objects to disk. The onstat command and the SET EXPLAIN ON output display this reader thread as the SQL operator xlread, which means load reader.

The database server creates multiple xlread instances on each coserver, but no more than the number of CPU virtual processors. Figure 82 shows how multiple xlread instances on each coserver might handle the pipe staging.

Figure 82. Pipe Staging
begin figure description - This figure is described in the surrounding text. - end figure description

Using Temporary Space for Pipe Staging of Simple Large Objects

The database server creates multiple xlread instances for parallel execution. You need temporary space for each xlread instance.

When you load simple large objects from named pipes in delimited format, you might need to increase the temporary space if the simple large objects are large. If a delimited row is larger than 136 kilobytes, the load routine stages it to disk.

The database server uses temporary space for the staging file. You can use one of the following temporary spaces for the staging files:

For the following reasons, Informix recommends that you use DBSPACETEMP instead of the PSORT_DBTEMP environment variable to provide temporary file space:

To estimate and create temporary space on coservers for pipe staging
  1. The size of the staging file required for each xlread operator instance is the size of the largest input data row, including its TEXT or BYTE data. Estimate the number of bytes required for the largest input data row with the following formula: 
    bytes_per_xlread = row_size + overhead

    Estimate overhead as 34 kilobytes rounded up to the next whole page for each 34 kilobyte of TEXT or BYTE data. Page size is user configurable, with a default of 4 kilobytes. With a page size of 4 kilobytes, each 34 kilobytes of data uses 36 kilobytes of temporary space. Therefore the 40-kilobyte simple large object would require 72 kilobytes of space.

    The preceding formula is most accurate for rows larger than 1 megabyte. For smaller rows, the row might overflow the 136-kilobyte buffer because part of it might be in the memory buffer while the rest is in the staging file. With larger rows, the buffer size is relatively insignificant.

  2. The total temporary space needed on a coserver is the sum of spaces required for all xlread instances on the coserver. Calculate it with the following formula: 
    tempspace_per_coserver = bytes_per_xlread * number_xlr

    The number of xlread instances is not more than the number of data files or the number of CPU virtual processors.

  3. Although you can use standard dbspaces for the temporary files with the appropriate DBSPACETEMP setup, It is recommended you create the temporary dbspace with onutil:
    1. For an even distribution of temporary dbspaces across the coservers, use the onutil CREATE TEMP DBSLICE option.
    2. For more temporary dbspaces on specific coservers, use the onutil CREATE TEMP DBSpaCE option for each specific coserver.

For information about monitoring temporary space for pipe staging, refer to Monitoring Loads and Unloads of Data.

Home | [ Top of Page | Previous Page | Next Page | Contents | Index ]