Informix - Problem description
Problem IT28256 | Status: Closed |
SPECIFY HARD CODE DEFAULT RECORDEND "N" WHEN CREATING EXTERNAL TABLE FOR UNLOAD, THE RESULT DATA FILE DOES NOT HAVE EOL | |
product: | |
INFORMIX SERVER / 5725A3900 / C10 - IDS 12.10 | |
Problem description: | |
Per our documentation: "If no RECORDEND value is specified, record_delimiter defaults to the Newline character ( )." However, if you specify RECORDEND " " in create external table syntax, the resultant data file does not appear to exhibit EOL. For instance, unix commands like wc, split, tail, head will not recognize multiple lines in the data file. If you do not specify RECORDEND which should by default use " " then the data files are in more expected format and these command will recognize multiple lines. The test case below shows this. The problem was originally opened for a migration effort using external tables to unload from Solaris64 12.10.FC10 and to load these data files into Linux x86_64 same version. The customer's schema involved byte data types stored in their own blob spaces, but the simple case below shows that was not a specific part of the problem. Consider the following SQL illustrating this issue: create database wf with log; create table tab1 ( c1 integer not null, c2 text in sandbox_et1_blobsp ) extent size 512 next size 256 lock mode row; create table tab1tmp ( c1 integer not null, c2 char(100) ) extent size 512 next size 256 lock mode row; insert into tab1tmp values(1,"adsfasdflkjahsdflkajshdfklads"); insert into tab1tmp values(3, "klajshdflaksjdfhas"); insert into tab1tmp values(2,"lkjasfdfdlhjk"); unload to tab1.unl select * from tab1tmp; load from tab1.unl insert into tab1; CREATE EXTERNAL TABLE tab1_re_ext SAMEAS tab1 USING ( format "delimited", DATAFILES ( "DISK:./tab1_recordend.unl" ), DELIMITER "|", ESCAPE, REJECTFILE 'tab1_re.rej', RECORDEND ' ', MAXERRORS 1 ); CREATE EXTERNAL TABLE tab1_nore_ext SAMEAS tab1 USING ( format "delimited", DATAFILES ( "DISK:./tab1_norecordend.unl" ), DELIMITER "|", ESCAPE, REJECTFILE 'tab1_nore.rej', MAXERRORS 1 ); insert into tab1_re_ext select * from tab1; insert into tab1_nore_ext select * from tab1; Now if you use one of the aforementioned unix commands, you can see the issue: $ wc -l tab1_* 3 tab1_norecordend.unl 0 tab1_recordend.unl 3 total Description | |
Problem Summary: | |
**************************************************************** * USERS AFFECTED: * * Users of IDS prior to 12.10.xC13. * **************************************************************** * PROBLEM DESCRIPTION: * * If you specify a hard coded default RECORDEND " " when * * creating an external table for unload, the resulting data * * file does not seem to have an End of Line. * **************************************************************** * RECOMMENDATION: * **************************************************************** | |
Local Fix: | |
Solution | |
Workaround | |
not known / see Local fix | |
Timestamps | |
Date - problem reported : Date - problem closed : Date - last modified : | 28.02.2019 24.09.2019 24.09.2019 |
Problem solved at the following versions (IBM BugInfos) | |
12.10.xC13 | |
Problem solved according to the fixlist(s) of the following version(s) |