Create external table? Conversion error

Hi, Running Informix 11.70.FC5GE on Windows Server 2008 r2 64bit. I want to make use of the "external tables" feature. Every night we 'unload' (using informix unload command) our entire database to disk as one of our several alternative backup methods. It is some of these files I'm trying to mount as an external table. When I select against the external table I get error: [SELECT - 0 row(s), 0.000 secs] [Error Code: -26168, SQL State: IX000] Conversion err:(file,offset,reason,col)=(487.UNL,0,UNSUPPORTED_ROW_SIZE,<none>). So, should I expect this feature to work with Growth Edition? If yes this is an example of what I do: create external table ext_racedivs SAMEAS racedivs USING (DATAFILES ("DISK:\\\\\\\\MyDomain.com\\\\Backup\\\\dbunload\\\\487.UNL"), DELIMITER "|"); That completes but any query (e.g. select * from ext_racedivs) against it fails with above error. I've tried changing the datafiles disk portion to point to a local file ("DISK:c:\\\\temp\\\\487.unl") or use a mapped drive to the backup location ("DISK:W:\\\\backup\\\\dbunload\\\\487.un") - in fact a mapped drive gives a different error of "[SELECT - 0 row(s), 0.000 secs] [Error Code: -26381, SQL State: IX000] w:\\\\backup\\\\dbunload"... a few lines of the datafile looks like: 18890|10||3||5.05| 18890|11||3||1.75| 18890|12||5||1.6| schema looks like: create table "informix".racedivs ( rhdr_id integer, div_type smallint, race_no smallint, book_nos_str char(18), not_available char(1), div decimal(10,2) ); To my mind this looks very straight forward data and should just load up happy. I've run this as user 'informix'. I tried dbaccess with same results. I've tried it with various tables. I've tried it with explicitly listing the schema instead of using SAMEAS. Have I missed something or done something wrong above? Any pointers? Or maybe a bug with windows version and I should open a case? Thanks, Bryce Stenberg.

It works for me! CREATE EXTERNAL TABLE "art".ext_racedivs ( rhdr_id INTEGER, div_type SMALLINT, race_no SMALLINT, book_nos_str CHAR(18), not_available CHAR(1), div DECIMAL(10,2) ) USING ( FORMAT "DELIMITED", DATAFILES ( "disk:/home/art/racedivs.unl" ), DELIMITER "|" ); CREATE TABLE "informix".racedivs ( rhdr_id INTEGER, div_type SMALLINT, race_no SMALLINT, book_nos_str CHAR(18), not_available CHAR(1), div DECIMAL(10,2) ) IN rootdbs EXTENT SIZE 16 NEXT SIZE 16 LOCK MODE ROW; cat >racedivs.unl 18890|10||3||5.05| 18890|11||3||1.75| 18890|12||5||1.6| > select * from ext_racedivs; rhdr_id div_type race_no book_nos_str not_available div 18890 10 3 5.05 18890 11 3 1.75 18890 12 5 1.60 Hmm, try using UNIX style forward slashes in the filepath for the unload files! Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, Feb 20, 2013 at 10:10 PM, BRYCE STENBERG <bryce@hrnz.co.nz> wrote: > Hi, > > Running Informix 11.70.FC5GE on Windows Server 2008 r2 64bit. > > I want to make use of the "external tables" feature. Every night we > 'unload' > (using informix unload command) our entire database to disk as one of our > several alternative backup methods. It is some of these files I'm trying to > mount as an external table. > > When I select against the external table I get error: > [SELECT - 0 row(s), 0.000 secs] [Error Code: -26168, SQL State: IX000] > Conversion > err:(file,offset,reason,col)=(487.UNL,0,UNSUPPORTED_ROW_SIZE,<none>). > > So, should I expect this feature to work with Growth Edition? > > If yes this is an example of what I do: > > create external table ext_racedivs SAMEAS racedivs > USING (DATAFILES ("DISK:\\\\\\\\MyDomain.com\\\\Backup\\\\dbunload\\\\487.UNL"), DELIMITER > "|"); > > That completes but any query (e.g. select * from ext_racedivs) against it > fails with above error. > I've tried changing the datafiles disk portion to point to a local file > ("DISK:c:\\\\temp\\\\487.unl") or use a mapped drive to the backup location > ("DISK:W:\\\\backup\\\\dbunload\\\\487.un") - in fact a mapped drive gives a > different > error of "[SELECT - 0 row(s), 0.000 secs] [Error Code: -26381, SQL State: > IX000] w:\\\\backup\\\\dbunload"... > > a few lines of the datafile looks like: > > 18890|10||3||5.05| > 18890|11||3||1.75| > 18890|12||5||1.6| > > schema looks like: > create table "informix".racedivs > ( > rhdr_id integer, > div_type smallint, > race_no smallint, > book_nos_str char(18), > not_available char(1), > div decimal(10,2) > ); > > To my mind this looks very straight forward data and should just load up > happy. > I've run this as user 'informix'. I tried dbaccess with same results. I've > tried it with various tables. I've tried it with explicitly listing the > schema > instead of using SAMEAS. > > Have I missed something or done something wrong above? Any pointers? > Or maybe a bug with windows version and I should open a case? > > Thanks, > Bryce Stenberg. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --f46d040838b3c0b03404d6345dea

>Art wrote: > >It works for me! > >CREATE EXTERNAL TABLE "art".ext_racedivs ( >rhdr_id INTEGER, >div_type SMALLINT, >race_no SMALLINT, >book_nos_str CHAR(18), >not_available CHAR(1), >div DECIMAL(10,2) >) USING ( >FORMAT "DELIMITED", >DATAFILES ( >"disk:/home/art/racedivs.unl" >), >DELIMITER "|" >); Hi Art, I tried the forward slashes as suggested, it still made no difference - same error 'UNSUPPORTED_ROW_SIZE'. I also added the piece 'FORMAT "DELIMITED"' like you have and I didn't, but that also made no difference. Open to other suggestions... Thanks, Bryce

SOLVED. I had problem with creating external tables on a windows system from files created using the 'unload' command - the error was: [Error Code: -26168, SQL State: IX000] Conversion err:(file,offset,reason,col)=(FileName.UNL,0,UNSUPPORTED_ROW_SIZE,<none>) Thought I'd follow up with the solution - on Windows systems you need to use the syntax: RECORDEND '\\\\012' example: CREATE EXTERNAL TABLE ext_racedivs SAMEAS racedivs USING (DATAFILES ("DISK:C:\\\\temp\\\\racedivs.unl"), RECORDEND '\\\\012'); On windows you can use backslashes or forwardslashes in path - it doesn't seem to matter. However, you can't use a network location - file has to be local to the server. Thanks go to Jason at Informix support in Australia for the solution. I'm told it is in the documentation - I just never found it at the time, still can't find it in 11.70 documentation but now I know what keyword to search on I did find it in some 11.50 documentation via a google search: "On Windows systems, if you use the DB-Access utility or the dbexport utility to unload a database table into a file and then plan to use the file as an external table datafile, you must define RECORDEND as '\\\\012' in the CREATE EXTERNAL TABLE statement." Cheers, Bryce Stenberg.