Informix Error -26383: DATAFILES string (%s) with file type PIPE is not supported with BLOB/CLOB types
Cause and resolution
DATAFILES string (%s) with file type PIPE is not supported with BLOB/CLOB types
The PIPE option is not supported in the DATAFILES clause when BLOB or CLOB data types are specified in the external table definition or in the table referenced in the SAMEAS clause. The following is an example of when this error is returned:
CREATE EXTERNAL TABLE exttab (col1 INTEGER, col2 BLOB, col3 CLOB) USING ( DATAFILES ( "PIPE:/tmp/ext_byte.dat.%r(1..2); CLOBDIR:/tmp/clobdir1.%r(1..2); BLOBDIR=/tmp/blobdir1.%r(1..2)" ), DELUXE, DELIMITER "%", recordend "\n", maxerrors 20, rejectfile "/tmp/ext_byte.log" );
To fix the problem in example above, please change the definition to use file type DISK instead of PIPE.
Oninit® Troubleshooting Guidance
Reasons / Common Causes
-26383 fires when a CREATE EXTERNAL TABLE's DATAFILES file type is PIPE while the
table has BLOB/CLOB columns (directly, or via SAMEAS) — per the official guidance.
PIPEused in theDATAFILESclause withBLOB/CLOBcolumns present, per the official guidance — the direct, only cause;PIPEisn't supported forBLOB/CLOB.
Solutions / Resolution
- Change the
DATAFILESfile type fromPIPEtoDISK, per the official guidance.
Examples
PIPE with a BLOB/CLOB column
CREATE EXTERNAL TABLE exttab (col1 INTEGER, col2 BLOB, col3 CLOB)
USING (
DATAFILES ("PIPE:/tmp/ext_byte.dat.%r(1..2); CLOBDIR:/tmp/clobdir1.%r(1..2);
BLOBDIR=/tmp/blobdir1.%r(1..2)"),
DELUXE, DELIMITER "%", recordend "\n", maxerrors 20,
rejectfile "/tmp/ext_byte.log"
);
-- -26383: PIPE isn't supported with BLOB/CLOB columns
Corrected
CREATE EXTERNAL TABLE exttab (col1 INTEGER, col2 BLOB, col3 CLOB)
USING (
DATAFILES ("DISK:/tmp/ext_byte.dat.%r(1..2); CLOBDIR:/tmp/clobdir1.%r(1..2);
BLOBDIR=/tmp/blobdir1.%r(1..2)"),
DELUXE, DELIMITER "%", recordend "\n", maxerrors 20,
rejectfile "/tmp/ext_byte.log"
);
Diagnostic Checks
- Check the
DATAFILESfile type when the table hasBLOB/CLOBcolumns, and changePIPEtoDISK.
Related Errors / Related Topics
- -26384 — "FORMAT (%s) is not supported with BLOB, CLOB, BYTE or TEXT types" A
related
BLOB/CLOB-column external-table restriction, on theFORMATclause rather thanDATAFILESfile type.
DATAFILES used file type PIPE on a table with BLOB/CLOB columns — use DISK
instead.