External tables ?

Hello ... In the datafiles clause on the create external table command ... can you put more than one file to spread the data? If so, does anyone have the syntax ... All the examples I have seen only go to one file .. yet the keyword is "datafiles" ... Thanks for any help ... IDS 11.50.fc6 Aix 6.1 Peter Logan Senior Database Administrator Phone: 616/878-8309

Do this in your DATAFILES clause: DATAFILES ( "DISK:/tmp/ext_dir/table.%r(1..3)" ) Also check this out: http://informixrules.blogspot.com/2010/04/iiug-thoughts-and-external-tables.html #links

Dude I could not get my own syntax to work... here is what I ended up doing: create table ext_agentsameas agent using (DATAFILES("DISK:/tmp/agent1.out", "DISK:/tmp/agent2.out", "DISK:/tmp/agent3.out")); That worked for me - when I loaded the ext table the disk files were used round robin like... MM

IB it looks like: ... USING ( DATAFILES( file1.txt, file2.txt, file3.txt ) ... ) ... of you can use the one wildcard to substitute a range of numbers, so: ... USING ( DATAFILES( file%(1..3).txt ) ... ) ... Which includes the same three files. Don't remember if you need to use quotes in there and I don't have manuals handy, but that should be in the examples in the manual. BTW, both of those examples are definitely in the manuals. I remember that because during the Beta I made them clarify the text describing the wildcarding because the original text made it seem that you could use normal filesystem wildcards and you can't. Only that one numeric range operator. Anyway, I remember that they expanded the examples also to make it easier to understand. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) See you at the 2010 IIUG Informix Conference April 25-28, 2010 Overland Park (Kansas City), KS www.iiug.org/conf 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 Fri, Apr 30, 2010 at 3:51 PM, Peter_Logan@spartanstores.com < Peter_Logan@spartanstores.com> wrote: > Hello ... In the datafiles clause on the create external table command > .... can you put more than one file to spread the data? If so, does > anyone have the syntax ... All the examples I have seen only go to one > file .. yet the keyword is "datafiles" ... > > Thanks for any help ... > > IDS 11.50.fc6 > Aix 6.1 > > Peter Logan > Senior Database Administrator > Phone: 616/878-8309 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001636e0a4ab761b5204857ab89c

OK, Mike nailed it in one. I just looked it up and his syntax is correct even for the formatted or wildcard version. Mine are too simplistic. So make that: .... USING ( DATAFILES( "DISK:file1.txt", "DISK:file2.txt", "DISK:file3.txt" ) ... ) ... of you can use the one wildcard to substitute a range of numbers, so: .... USING ( DATAFILES( "DISK:file%r(1..3).txt" ) ... ) ... You can even mix the two: .... USING ( DATAFILES( "DISK:file1.txt", "DISK:list_of_file%r(1..10).txt", "DISK:file12.txt" ) ... ) ... Here's an example from the manual showing three disk files each with associated blob and clob directories: CREATE EXTERNAL TABLE exttab ( id SERIAL, lobc CLOB, lobb BLOB) USING (DATAFILES( “DISK:/work1/exttab1.dat;BLOBDIR:/work1/blobdir1;CLOBDIR:/work1/clobdir1”, “DISK:/work1/exttab2.dat;CLOBDIR:/work1/clobdir2”, “DISK:/work1/exttab3.dat”), DELIMITER '|'); Commas separate each file spec and semi-colons separate the file spec from the blob and clob directory specs. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) See you at the 2010 IIUG Informix Conference April 25-28, 2010 Overland Park (Kansas City), KS www.iiug.org/conf 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 Fri, Apr 30, 2010 at 5:14 PM, Art Kagel <art.kagel@gmail.com> wrote: > IB it looks like: > > .... USING ( DATAFILES( file1.txt, file2.txt, file3.txt ) ... ) ... > > of you can use the one wildcard to substitute a range of numbers, so: > > .... USING ( DATAFILES( file%(1..3).txt ) ... ) ... > > Which includes the same three files. Don't remember if you need to use > quotes in there and I don't have manuals handy, but that should be in the > examples in the manual. BTW, both of those examples are definitely in the > manuals. I remember that because during the Beta I made them clarify the > text describing the wildcarding because the original text made it seem that > you could use normal filesystem wildcards and you can't. Only that one > numeric range operator. Anyway, I remember that they expanded the examples > also to make it easier to understand. > > Art > > Art S. Kagel > Advanced DataTools (www.advancedatatools.com) > IIUG Board of Directors (art@iiug.org) > > See you at the 2010 IIUG Informix Conference > April 25-28, 2010 > Overland Park (Kansas City), KS > www.iiug.org/conf > > 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 Fri, Apr 30, 2010 at 3:51 PM, Peter_Logan@spartanstores.com < > Peter_Logan@spartanstores.com> wrote: > > > Hello ... In the datafiles clause on the create external table command > > .... can you put more than one file to spread the data? If so, does > > anyone have the syntax ... All the examples I have seen only go to one > > file .. yet the keyword is "datafiles" ... > > > > Thanks for any help ... > > > > IDS 11.50.fc6 > > Aix 6.1 > > > > Peter Logan > > Senior Database Administrator > > Phone: 616/878-8309 > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --001636e0a4ab761b5204857ab89c > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001636e0a638f4b6cc04857b5fc5