External Table + zipped files..
Cesar (IDS 11.50 on AIX 6.1) wanted external tables to read gzip-compressed UNLOAD files transparently, so a plain SELECT would decompress on the fly. Pointing the DATAFILES pipe: option at a shell script running 'gzip -cd' failed with error 26158 ("File is incorrectly specified as a PIPE type"). Kern Doe suggested a named FIFO fed by gunzip plus dbload, but that needs manual startup, and there was no disk space to decompress (380 MB becomes 4.8 GB). Art Kagel concluded external tables can't read compressed files. No working solution was recorded; a side note warned that renaming/removing external files before dropping the table leaves open file handles.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi ,
ifx 11.50 FC9X6 , AIX 6.1
We have some history tables unloaded (with unload command) and compressed
with gzip.
What we need is able the database access this files , where will be
sporadic access .
My desire is use External Tables for that.
The problem is : This access will occur by our system , so I not able to
use PIPE files since there is no way start the decompress automatically , I
need this work "on-fly".
If I direct the the PIPE to shell with the command gzip-cd <file> , the
engine not accept.
create external table his_2012_01 sameas h_mvt using (
datafiles('pipe:/tmp/ext201201.sh') , format 'delimited', express, dbdate
'dmy4/', numrows 9999999 );
select * from his_2012_01;# ^
#26158: File is incorrectly specified as a PIPE type:
(file)=(/tmp/ext201201.sh).
#
Any ideas how make this work or workaround.
Regards
Cesar
--047d7bfcf9b6b5b63b04d8706399
You indicate the desire to use external table, so
1. create the external table (just a normal one, no pipe)
2. load data from that gzip file to the external table.
mknod load.pipe p
chmod 660 load.pipe
gunzip -c thatgzipfile.gz > load.pipe &
dbload -d yourdbname -c load.cmd -l load.log
cat load.cmd:
file "load.pipe" delimiter "|" <# columns>;
insert into <thatexternaltable>;
3. delete <thatgzipfile.gz> if you like to save space
There must be some other better ways, and I would like to know too.
For the time being, try this, let us know if it works.
________________________________
From: Cesar Inacio Martins <cesar.inacio.martins@gmail.com>
To: ids@iiug.org
Sent: Thursday, March 21, 2013 9:51 AM
Subject: External Table + zipped files.. [29856]
Hi ,
ifx 11.50 FC9X6 , AIX 6.1
We have some history tables unloaded (with unload command) and compressed
with gzip.
What we need is able the database access this files , where will be
sporadic access .
My desire is use External Tables for that.
The problem is : This access will occur by our system , so I not able to
use PIPE files since there is no way start the decompress automatically , I
need this work "on-fly".
If I direct the the PIPE to shell with the command gzip-cd <file> , the
engine not accept.
create external table his_2012_01 sameas h_mvt using (
datafiles('pipe:/tmp/ext201201.sh') , format 'delimited', express, dbdate
'dmy4/', numrows 9999999 );
select * from his_2012_01;# ^
#26158: File is incorrectly specified as a PIPE type:
(file)=(/tmp/ext201201.sh).
#
Any ideas how make this work or workaround.
Regards
Cesar
--047d7bfcf9b6b5b63b04d8706399
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Hi Kern,
Sorry , maybe I don't clear about my need.
The steps you wrote , I able to do without problem.
My problem is do it without any manual interaction .
When the user/system execute "select * from my_ext_table" , the engine need
to get the data from output of gzip command .
If they appoint to PIPE file (mknod / mkfifo) will depend from anyone start
the "gzip -cd file > fifo_file" or no data will incoming when the select
run.
With HPL I use a lot the "gzip -cd <file.unl>" as pipe parameter to bulk
loads, but this not work with external table.
I try cheating by shell script... without success..
I can't decompress this files and use the FILES parameter because there is
no space into File system where they are .
2013/3/21 Kern Doe <kern_doe@yahoo.com>
> You indicate the desire to use external table, so
> 1. create the external table (just a normal one, no pipe)
>
> 2. load data from that gzip file to the external table.
> mknod load.pipe p
> chmod 660 load.pipe
>
> gunzip -c thatgzipfile.gz > load.pipe &
>
> dbload -d yourdbname -c load.cmd -l load.log>
> cat load.cmd:
>
> file "load.pipe" delimiter "|" <# columns>;
>
> insert into <thatexternaltable>;
>
> 3. delete <thatgzipfile.gz> if you like to save space
> There must be some other better ways, and I would like to know too.
>
> For the time being, try this, let us know if it works.
>
> ________________________________
> From: Cesar Inacio Martins <cesar.inacio.martins@gmail.com>
> To: ids@iiug.org
> Sent: Thursday, March 21, 2013 9:51 AM
> Subject: External Table + zipped files.. [29856]
>
> Hi ,
>
> ifx 11.50 FC9X6 , AIX 6.1
>
> We have some history tables unloaded (with unload command) and compressed
> with gzip.
>
> What we need is able the database access this files , where will be
> sporadic access .
> My desire is use External Tables for that.
>
> The problem is : This access will occur by our system , so I not able to
> use PIPE files since there is no way start the decompress automatically , I
> need this work "on-fly".
>
> If I direct the the PIPE to shell with the command gzip-cd <file> , the
> engine not accept.
>
> create external table his_2012_01 sameas h_mvt using (
> datafiles('pipe:/tmp/ext201201.sh') , format 'delimited', express, dbdate
> 'dmy4/', numrows 9999999 );
> select * from his_2012_01;> # ^
> #26158: File is incorrectly specified as a PIPE type:
> (file)=(/tmp/ext201201.sh).
> #
>
> Any ideas how make this work or workaround.
>
> Regards
> Cesar
>
> --047d7bfcf9b6b5b63b04d8706399
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--047d7bfcf9b644611004d8720ef1
I assume the OP doesn't have room for the uncompressed unload file,
otherwise why not just gunzip the file and point the external table at the
uncompressed unload file?
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 Thu, Mar 21, 2013 at 11:56 AM, Kern Doe <kern_doe@yahoo.com> wrote:
> You indicate the desire to use external table, so
> 1. create the external table (just a normal one, no pipe)
>
> 2. load data from that gzip file to the external table.
> mknod load.pipe p
> chmod 660 load.pipe
>
> gunzip -c thatgzipfile.gz > load.pipe &
>
> dbload -d yourdbname -c load.cmd -l load.log>
> cat load.cmd:
>
> file "load.pipe" delimiter "|" <# columns>;
>
> insert into <thatexternaltable>;
>
> 3. delete <thatgzipfile.gz> if you like to save space
> There must be some other better ways, and I would like to know too.
>
> For the time being, try this, let us know if it works.
>
> ________________________________
> From: Cesar Inacio Martins <cesar.inacio.martins@gmail.com>
> To: ids@iiug.org
> Sent: Thursday, March 21, 2013 9:51 AM
> Subject: External Table + zipped files.. [29856]
>
> Hi ,
>
> ifx 11.50 FC9X6 , AIX 6.1
>
> We have some history tables unloaded (with unload command) and compressed
> with gzip.
>
> What we need is able the database access this files , where will be
> sporadic access .
> My desire is use External Tables for that.
>
> The problem is : This access will occur by our system , so I not able to
> use PIPE files since there is no way start the decompress automatically , I
> need this work "on-fly".
>
> If I direct the the PIPE to shell with the command gzip-cd <file> , the
> engine not accept.
>
> create external table his_2012_01 sameas h_mvt using (
> datafiles('pipe:/tmp/ext201201.sh') , format 'delimited', express, dbdate
> 'dmy4/', numrows 9999999 );
> select * from his_2012_01;> # ^
> #26158: File is incorrectly specified as a PIPE type:
> (file)=(/tmp/ext201201.sh).
> #
>
> Any ideas how make this work or workaround.
>
> Regards
> Cesar
>
> --047d7bfcf9b6b5b63b04d8706399
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--bcaec555549484588804d8727d15
I have not yet try this before but good to know because it saves a big step.
________________________________
From: Art Kagel <art.kagel@gmail.com>
To: ids@iiug.org
Sent: Thursday, March 21, 2013 12:15 PM
Subject: Re: External Table + zipped files.. [29862]
I assume the OP doesn't have room for the uncompressed unload file,
otherwise why not just gunzip the file and point the external table at the
uncompressed unload file?
Art
Art S. Kagel
Advanced DataTools (http://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 Thu, Mar 21, 2013 at 11:56 AM, Kern Doe <kern_doe@yahoo.com> wrote:
> You indicate the desire to use external table, so
> 1. create the external table (just a normal one, no pipe)
>
> 2. load data from that gzip file to the external table.
> mknod load.pipe p
> chmod 660 load.pipe
>
> gunzip -c thatgzipfile.gz > load.pipe &
>
> dbload -d yourdbname -c load.cmd -l load.log>
> cat load.cmd:
>
> file "load.pipe" delimiter "|" <# columns>;
>
> insert into <thatexternaltable>;
>
> 3. delete <thatgzipfile.gz> if you like to save space
> There must be some other better ways, and I would like to know too.
>
> For the time being, try this, let us know if it works.
>
> ________________________________
> From: Cesar Inacio Martins <cesar.inacio.martins@gmail.com>
> To: ids@iiug.org
> Sent: Thursday, March 21, 2013 9:51 AM
> Subject: External Table + zipped files.. [29856]
>
> Hi ,
>
> ifx 11.50 FC9X6 , AIX 6.1
>
> We have some history tables unloaded (with unload command) and compressed
> with gzip.
>
> What we need is able the database access this files , where will be
> sporadic access .
> My desire is use External Tables for that.
>
> The problem is : This access will occur by our system , so I not able to
> use PIPE files since there is no way start the decompress automatically , I
> need this work "on-fly".
>
> If I direct the the PIPE to shell with the command gzip-cd <file> , the
> engine not accept.
>
> create external table his_2012_01 sameas h_mvt using (
> datafiles('pipe:/tmp/ext201201.sh') , format 'delimited', express, dbdate
> 'dmy4/', numrows 9999999 );
> select * from his_2012_01;> # ^
> #26158: File is incorrectly specified as a PIPE type:
> (file)=(/tmp/ext201201.sh).
> #
>
> Any ideas how make this work or workaround.
>
> Regards
> Cesar
>
> --047d7bfcf9b6b5b63b04d8706399
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--bcaec555549484588804d8727d15
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Hi Art,
Exactly , we don't have space to unzip this files , since they are text
have a big rate compression... so a file compressed have 380 MB ,
decompressed go to 4.8 GB.
We have a lot of files like this...
2013/3/21 Art Kagel <art.kagel@gmail.com>
> I assume the OP doesn't have room for the uncompressed unload file,
> otherwise why not just gunzip the file and point the external table at the
> uncompressed unload file?
>
> 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 Thu, Mar 21, 2013 at 11:56 AM, Kern Doe <kern_doe@yahoo.com> wrote:
>
> > You indicate the desire to use external table, so
> > 1. create the external table (just a normal one, no pipe)
> >
> > 2. load data from that gzip file to the external table.
> > mknod load.pipe p
> > chmod 660 load.pipe
> >
> > gunzip -c thatgzipfile.gz > load.pipe &
> >
> > dbload -d yourdbname -c load.cmd -l load.log> >
> > cat load.cmd:
> >
> > file "load.pipe" delimiter "|" <# columns>;
> >
> > insert into <thatexternaltable>;
> >
> > 3. delete <thatgzipfile.gz> if you like to save space
> > There must be some other better ways, and I would like to know too.
> >
> > For the time being, try this, let us know if it works.
> >
> > ________________________________
> > From: Cesar Inacio Martins <cesar.inacio.martins@gmail.com>
> > To: ids@iiug.org
> > Sent: Thursday, March 21, 2013 9:51 AM
> > Subject: External Table + zipped files.. [29856]
> >
> > Hi ,
> >
> > ifx 11.50 FC9X6 , AIX 6.1
> >
> > We have some history tables unloaded (with unload command) and compressed
> > with gzip.
> >
> > What we need is able the database access this files , where will be
> > sporadic access .
> > My desire is use External Tables for that.
> >
> > The problem is : This access will occur by our system , so I not able to
> > use PIPE files since there is no way start the decompress automatically
> , I
> > need this work "on-fly".
> >
> > If I direct the the PIPE to shell with the command gzip-cd <file> , the
> > engine not accept.
> >
> > create external table his_2012_01 sameas h_mvt using (
> > datafiles('pipe:/tmp/ext201201.sh') , format 'delimited', express, dbdate
> > 'dmy4/', numrows 9999999 );
> > select * from his_2012_01;> > # ^
> > #26158: File is incorrectly specified as a PIPE type:
> > (file)=(/tmp/ext201201.sh).
> > #
> >
> > Any ideas how make this work or workaround.
> >
> > Regards
> > Cesar
> >
> > --047d7bfcf9b6b5b63b04d8706399
> >
> >
> >
> >
>
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
> >
> >
>
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --bcaec555549484588804d8727d15
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--089e013c6ab09134c804d872f811
Then you have a problem. I don't think that there is anyway for external
tables to work with a compressed file.
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 Thu, Mar 21, 2013 at 1:50 PM, Cesar Inacio Martins <
cesar.inacio.martins@gmail.com> wrote:
> Hi Art,
>
> Exactly , we don't have space to unzip this files , since they are text
> have a big rate compression... so a file compressed have 380 MB ,
> decompressed go to 4.8 GB.
> We have a lot of files like this...
>
> 2013/3/21 Art Kagel <art.kagel@gmail.com>
>
> > I assume the OP doesn't have room for the uncompressed unload file,
> > otherwise why not just gunzip the file and point the external table at
> the
> > uncompressed unload file?
> >
> > 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 Thu, Mar 21, 2013 at 11:56 AM, Kern Doe <kern_doe@yahoo.com> wrote:
> >
> > > You indicate the desire to use external table, so
> > > 1. create the external table (just a normal one, no pipe)
> > >
> > > 2. load data from that gzip file to the external table.
> > > mknod load.pipe p
> > > chmod 660 load.pipe
> > >
> > > gunzip -c thatgzipfile.gz > load.pipe &
> > >
> > > dbload -d yourdbname -c load.cmd -l load.log> > >
> > > cat load.cmd:
> > >
> > > file "load.pipe" delimiter "|" <# columns>;
> > >
> > > insert into <thatexternaltable>;
> > >
> > > 3. delete <thatgzipfile.gz> if you like to save space
> > > There must be some other better ways, and I would like to know too.
> > >
> > > For the time being, try this, let us know if it works.
> > >
> > > ________________________________
> > > From: Cesar Inacio Martins <cesar.inacio.martins@gmail.com>
> > > To: ids@iiug.org
> > > Sent: Thursday, March 21, 2013 9:51 AM
> > > Subject: External Table + zipped files.. [29856]
> > >
> > > Hi ,
> > >
> > > ifx 11.50 FC9X6 , AIX 6.1
> > >
> > > We have some history tables unloaded (with unload command) and
> compressed
> > > with gzip.
> > >
> > > What we need is able the database access this files , where will be
> > > sporadic access .
> > > My desire is use External Tables for that.
> > >
> > > The problem is : This access will occur by our system , so I not able
> to
> > > use PIPE files since there is no way start the decompress automatically
> > , I
> > > need this work "on-fly".
> > >
> > > If I direct the the PIPE to shell with the command gzip-cd <file> , the
> > > engine not accept.
> > >
> > > create external table his_2012_01 sameas h_mvt using (
> > > datafiles('pipe:/tmp/ext201201.sh') , format 'delimited', express,
> dbdate
> > > 'dmy4/', numrows 9999999 );
> > > select * from his_2012_01;> > > # ^
> > > #26158: File is incorrectly specified as a PIPE type:
> > > (file)=(/tmp/ext201201.sh).
> > > #
> > >
> > > Any ideas how make this work or workaround.
> > >
> > > Regards
> > > Cesar
> > >
> > > --047d7bfcf9b6b5b63b04d8706399
> > >
> > >
> > >
> > >
> >
> >
>
>
*******************************************************************************
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > >
> > >
> > >
> > >
> >
> >
>
>
*******************************************************************************
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > >
> > >
> >
> > --bcaec555549484588804d8727d15
> >
> >
> >
> >
>
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --089e013c6ab09134c804d872f811
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--bcaec5524216113cba04d873037b
This is a little off the main topic, but I wonder how Cesar manages all his
external files for external tables. You see, it's very easy for DBA to
accidently remove, rename, or gzip the external file belongs to the external
table w/o dropping the external table first. Incorrectly managing this
external file doesn't crash the engine or anything but it leaves open "inode"
or "open file" or whatever that is (open files can be seen at the end of the
output from onstat -g iof). As a result, file system size (from df -k) does
not changed or reduced even after the external table was dropped.
My own lesson learned is that there is a certain order I must follow:
1. create externale table
2. load data
3. do whatever with this table
4. drop this table when done
5. remove the external file (if you need)
________________________________
From: Art Kagel <art.kagel@gmail.com>
To: ids@iiug.org
Sent: Thursday, March 21, 2013 12:53 PM
Subject: Re: External Table + zipped files.. [29866]
Then you have a problem. I don't think that there is anyway for external
tables to work with a compressed file.
Art
Art S. Kagel
Advanced DataTools (http://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 Thu, Mar 21, 2013 at 1:50 PM, Cesar Inacio Martins <
cesar.inacio.martins@gmail.com> wrote:
> Hi Art,
>
> Exactly , we don't have space to unzip this files , since they are text
> have a big rate compression... so a file compressed have 380 MB ,
> decompressed go to 4.8 GB.
> We have a lot of files like this...
>
> 2013/3/21 Art Kagel <art.kagel@gmail.com>
>
> > I assume the OP doesn't have room for the uncompressed unload file,
> > otherwise why not just gunzip the file and point the external table at
> the
> > uncompressed unload file?
> >
> > 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 Thu, Mar 21, 2013 at 11:56 AM, Kern Doe <kern_doe@yahoo.com> wrote:
> >
> > > You indicate the desire to use external table, so
> > > 1. create the external table (just a normal one, no pipe)
> > >
> > > 2. load data from that gzip file to the external table.
> > > mknod load.pipe p
> > > chmod 660 load.pipe
> > >
> > > gunzip -c thatgzipfile.gz > load.pipe &
> > >
> > > dbload -d yourdbname -c load.cmd -l load.log> > >
> > > cat load.cmd:
> > >
> > > file "load.pipe" delimiter "|" <# columns>;
> > >
> > > insert into <thatexternaltable>;
> > >
> > > 3. delete <thatgzipfile.gz> if you like to save space
> > > There must be some other better ways, and I would like to know too.
> > >
> > > For the time being, try this, let us know if it works.
> > >
> > > ________________________________
> > > From: Cesar Inacio Martins <cesar.inacio.martins@gmail.com>
> > > To: ids@iiug.org
> > > Sent: Thursday, March 21, 2013 9:51 AM
> > > Subject: External Table + zipped files.. [29856]
> > >
> > > Hi ,
> > >
> > > ifx 11.50 FC9X6 , AIX 6.1
> > >
> > > We have some history tables unloaded (with unload command) and
> compressed
> > > with gzip.
> > >
> > > What we need is able the database access this files , where will be
> > > sporadic access .
> > > My desire is use External Tables for that.
> > >
> > > The problem is : This access will occur by our system , so I not able
> to
> > > use PIPE files since there is no way start the decompress automatically
> > , I
> > > need this work "on-fly".
> > >
> > > If I direct the the PIPE to shell with the command gzip-cd <file> , the
> > > engine not accept.
> > >
> > > create external table his_2012_01 sameas h_mvt using (
> > > datafiles('pipe:/tmp/ext201201.sh') , format 'delimited', express,
> dbdate
> > > 'dmy4/', numrows 9999999 );
> > > select * from his_2012_01;> > > # ^
> > > #26158: File is incorrectly specified as a PIPE type:
> > > (file)=(/tmp/ext201201.sh).
> > > #
> > >
> > > Any ideas how make this work or workaround.
> > >
> > > Regards
> > > Cesar
> > >
> > > --047d7bfcf9b6b5b63b04d8706399
> > >
> > >
> > >
> > >
> >
> >
>
>
*******************************************************************************
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > >
> > >
> > >
> > >
> >
> >
>
>
*******************************************************************************
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > >
> > >
> >
> > --bcaec555549484588804d8727d15
> >
> >
> >
> >
>
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --089e013c6ab09134c804d872f811
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--bcaec5524216113cba04d873037b
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Kern: Your #2 is not needed if the data is already in the file. Otherwise
I agree.
You are only thinking of external tables for exporting data, they can be
used to import data or just to peruse a flat file inside the database as
well in which case the file(s) will already exist when the external table
is created.
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 Thu, Mar 21, 2013 at 3:34 PM, Kern Doe <kern_doe@yahoo.com> wrote:
> This is a little off the main topic, but I wonder how Cesar manages all his
> external files for external tables. You see, it's very easy for DBA to
> accidently remove, rename, or gzip the external file belongs to the
> external
> table w/o dropping the external table first. Incorrectly managing this
> external file doesn't crash the engine or anything but it leaves open
> "inode"
> or "open file" or whatever that is (open files can be seen at the end of
> the
> output from onstat -g iof). As a result, file system size (from df -k) does
> not changed or reduced even after the external table was dropped.
> My own lesson learned is that there is a certain order I must follow:
> 1. create externale table
> 2. load data
> 3. do whatever with this table
> 4. drop this table when done
> 5. remove the external file (if you need)
>
> ________________________________
> From: Art Kagel <art.kagel@gmail.com>
> To: ids@iiug.org
> Sent: Thursday, March 21, 2013 12:53 PM
> Subject: Re: External Table + zipped files.. [29866]
>
> Then you have a problem. I don't think that there is anyway for external
> tables to work with a compressed file.
>
> Art
>
> Art S. Kagel
> Advanced DataTools (http://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 Thu, Mar 21, 2013 at 1:50 PM, Cesar Inacio Martins <
> cesar.inacio.martins@gmail.com> wrote:
>
> > Hi Art,
> >
> > Exactly , we don't have space to unzip this files , since they are text
> > have a big rate compression... so a file compressed have 380 MB ,
> > decompressed go to 4.8 GB.
> > We have a lot of files like this...
> >
> > 2013/3/21 Art Kagel <art.kagel@gmail.com>
> >
> > > I assume the OP doesn't have room for the uncompressed unload file,
> > > otherwise why not just gunzip the file and point the external table at
> > the
> > > uncompressed unload file?
> > >
> > > 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 Thu, Mar 21, 2013 at 11:56 AM, Kern Doe <kern_doe@yahoo.com> wrote:
> > >
> > > > You indicate the desire to use external table, so
> > > > 1. create the external table (just a normal one, no pipe)
> > > >
> > > > 2. load data from that gzip file to the external table.
> > > > mknod load.pipe p
> > > > chmod 660 load.pipe
> > > >
> > > > gunzip -c thatgzipfile.gz > load.pipe &
> > > >
> > > > dbload -d yourdbname -c load.cmd -l load.log> > > >
> > > > cat load.cmd:
> > > >
> > > > file "load.pipe" delimiter "|" <# columns>;
> > > >
> > > > insert into <thatexternaltable>;
> > > >
> > > > 3. delete <thatgzipfile.gz> if you like to save space
> > > > There must be some other better ways, and I would like to know too.
> > > >
> > > > For the time being, try this, let us know if it works.
> > > >
> > > > ________________________________
> > > > From: Cesar Inacio Martins <cesar.inacio.martins@gmail.com>
> > > > To: ids@iiug.org
> > > > Sent: Thursday, March 21, 2013 9:51 AM
> > > > Subject: External Table + zipped files.. [29856]
> > > >
> > > > Hi ,
> > > >
> > > > ifx 11.50 FC9X6 , AIX 6.1
> > > >
> > > > We have some history tables unloaded (with unload command) and
> > compressed
> > > > with gzip.
> > > >
> > > > What we need is able the database access this files , where will be
> > > > sporadic access .
> > > > My desire is use External Tables for that.
> > > >
> > > > The problem is : This access will occur by our system , so I not able
> > to
> > > > use PIPE files since there is no way start the decompress
> automatically
> > > , I
> > > > need this work "on-fly".
> > > >
> > > > If I direct the the PIPE to shell with the command gzip-cd <file> ,
> the
> > > > engine not accept.
> > > >
> > > > create external table his_2012_01 sameas h_mvt using (
> > > > datafiles('pipe:/tmp/ext201201.sh') , format 'delimited', express,
> > dbdate
> > > > 'dmy4/', numrows 9999999 );
> > > > select * from his_2012_01;> > > > # ^
> > > > #26158: File is incorrectly specified as a PIPE type:
> > > > (file)=(/tmp/ext201201.sh).
> > > > #
> > > >
> > > > Any ideas how make this work or workaround.
> > > >
> > > > Regards
> > > > Cesar
> > > >
> > > > --047d7bfcf9b6b5b63b04d8706399
> > > >
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>
>
*******************************************************************************
> > > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > > >
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>
>
*******************************************************************************
> > > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > > >
> > > >
> > >
> > > --bcaec555549484588804d8727d15
> > >
> > >
> > >
> > >
> >
> >
>
>
>
*******************************************************************************
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > >
> > >
> >
> > --089e013c6ab09134c804d872f811
> >
> >
> >
> >
>
>
>
***********************
I probably did not use the right terminology. "Load" in this case means
"insert into <external_table>", yes, we do this to get an exported file fast,
and if the data is already in the file, like I said, it's interesting because,
I have not yet tried that, and thank you Art for this information.
________________________________
From: Art Kagel <art.kagel@gmail.com>
To: ids@iiug.org
Sent: Thursday, March 21, 2013 3:02 PM
Subject: Re: External Table + zipped files.. [29869]
Kern: Your #2 is not needed if the data is already in the file. Otherwise
I agree.
You are only thinking of external tables for exporting data, they can be
used to import data or just to peruse a flat file inside the database as
well in which case the file(s) will already exist when the external table
is created.
Art
Art S. Kagel
Advanced DataTools (http://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 Thu, Mar 21, 2013 at 3:34 PM, Kern Doe <kern_doe@yahoo.com> wrote:
> This is a little off the main topic, but I wonder how Cesar manages all his
> external files for external tables. You see, it's very easy for DBA to
> accidently remove, rename, or gzip the external file belongs to the
> external
> table w/o dropping the external table first. Incorrectly managing this
> external file doesn't crash the engine or anything but it leaves open
> "inode"
> or "open file" or whatever that is (open files can be seen at the end of
> the
> output from onstat -g iof). As a result, file system size (from df -k) does
> not changed or reduced even after the external table was dropped.
> My own lesson learned is that there is a certain order I must follow:
> 1. create externale table
> 2. load data
> 3. do whatever with this table
> 4. drop this table when done
> 5. remove the external file (if you need)
>
> ________________________________
> From: Art Kagel <art.kagel@gmail.com>
> To: ids@iiug.org
> Sent: Thursday, March 21, 2013 12:53 PM
> Subject: Re: External Table + zipped files.. [29866]
>
> Then you have a problem. I don't think that there is anyway for external
> tables to work with a compressed file.
>
> Art
>
> Art S. Kagel
> Advanced DataTools (http://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 Thu, Mar 21, 2013 at 1:50 PM, Cesar Inacio Martins <
> cesar.inacio.martins@gmail.com> wrote:
>
> > Hi Art,
> >
> > Exactly , we don't have space to unzip this files , since they are text
> > have a big rate compression... so a file compressed have 380 MB ,
> > decompressed go to 4.8 GB.
> > We have a lot of files like this...
> >
> > 2013/3/21 Art Kagel <art.kagel@gmail.com>
> >
> > > I assume the OP doesn't have room for the uncompressed unload file,
> > > otherwise why not just gunzip the file and point the external table at
> > the
> > > uncompressed unload file?
> > >
> > > 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 Thu, Mar 21, 2013 at 11:56 AM, Kern Doe <kern_doe@yahoo.com> wrote:
> > >
> > > > You indicate the desire to use external table, so
> > > > 1. create the external table (just a normal one, no pipe)
> > > >
> > > > 2. load data from that gzip file to the external table.
> > > > mknod load.pipe p
> > > > chmod 660 load.pipe
> > > >
> > > > gunzip -c thatgzipfile.gz > load.pipe &
> > > >
> > > > dbload -d yourdbname -c load.cmd -l load.log> > > >
> > > > cat load.cmd:
> > > >
> > > > file "load.pipe" delimiter "|" <# columns>;
> > > >
> > > > insert into <thatexternaltable>;
> > > >
> > > > 3. delete <thatgzipfile.gz> if you like to save space
> > > > There must be some other better ways, and I would like to know too.
> > > >
> > > > For the time being, try this, let us know if it works.
> > > >
> > > > ________________________________
> > > > From: Cesar Inacio Martins <cesar.inacio.martins@gmail.com>
> > > > To: ids@iiug.org
> > > > Sent: Thursday, March 21, 2013 9:51 AM
> > > > Subject: External Table + zipped files.. [29856]
> > > >
> > > > Hi ,
> > > >
> > > > ifx 11.50 FC9X6 , AIX 6.1
> > > >
> > > > We have some history tables unloaded (with unload command) and
> > compressed
> > > > with gzip.
> > > >
> > > > What we need is able the database access this files , where will be
> > > > sporadic access .
> > > > My desire is use External Tables for that.
> > > >
> > > > The problem is : This access will occur by our system , so I not able
> > to
> > > > use PIPE files since there is no way start the decompress
> automatically
> > > , I
> > > > need this work "on-fly".
> > > >
> > > > If I direct the the PIPE to shell with the command gzip-cd <file> ,
> the
> > > > engine not accept.
> > > >
> > > > create external table his_2012_01 sameas h_mvt using (
> > > > datafiles('pipe:/tmp/ext201201.sh') , format 'delimited', express,
> > dbdate
> > > > 'dmy4/', numrows 9999999 );
> > > > select * from his_2012_01;> > > > # ^
> > > > #26158: File is incorrectly specified as a PIPE type:
> > > > (file)=(/tmp/ext201201.sh).
> > > > #
> > > >
> > > > Any ideas how make this work or workaround.
> > > >
> > > > Regards
> > > > Cesar
> > > >
> > > > --047d7bfcf9b6b5b63b04d8706399
> > > >
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>
>
*******************************************************************************
> > > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > > >
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>
>
*******************************************************************************
> > > > Forum Not
..... ifx 11.50 FC9X6 , AIX 6.1 We have some history tables unloaded (with unload command) and compressed with gzip....... You might want to look into: https://groups.google.com/group/comp.databases.informix/browse_thread/thread/e22 4d1ba3186ba22?hl=de&noredirect=true Also there is an example of using named pipes, external tables and fifo vps to copy tables, either in V11.70 docs or on developer works. dic_k
Hi Kern ,
We do not use external tables today .
We export all with unload (old scripts) and after exported they are
gzipped, backuped to tape and aren't removed from our FS.
Anyway, we keep the files at special partition on server with read-only
access.
So , is low risk to anyone remove/rename this files and the way I desire to
work the problem with FDs openend if someone rename/remove the file should
never be a problem because if I able to use PIPE (gzip command) I believe
there a minor chance to not get any error and automatic close the FD.
I need able specific users "see" this data by our system on few and very
specific reports. I trying avoid load this data into the database because
they will overload unnecessary our chunks/dbspaces and grow our
archive/ontape and I trying avoid create a new instance only for that too
(like a history instance).
My plan/desire was :
* create a external table , something like : create external table xyz
sameas zyx using (datafiles ('pipe:/usr/bin/gzip -cd /history/file1')....
* create a private synonym with same name of our official table to user
what need access to this history...
(I know, not work this way, probably I will need rename the original
table and then make a public and private synonym to get the effect I want.
User A see the "file data" and all others users see the "regular data".)
2013/3/21 Kern Doe <kern_doe@yahoo.com>
> This is a little off the main topic, but I wonder how Cesar manages all his
> external files for external tables. You see, it's very easy for DBA to
> accidently remove, rename, or gzip the external file belongs to the
> external
> table w/o dropping the external table first. Incorrectly managing this
> external file doesn't crash the engine or anything but it leaves open
> "inode"
> or "open file" or whatever that is (open files can be seen at the end of
> the
> output from onstat -g iof). As a result, file system size (from df -k) does
> not changed or reduced even after the external table was dropped.
> My own lesson learned is that there is a certain order I must follow:
> 1. create externale table
> 2. load data
> 3. do whatever with this table
> 4. drop this table when done
> 5. remove the external file (if you need)
>
> ________________________________
> From: Art Kagel <art.kagel@gmail.com>
> To: ids@iiug.org
> Sent: Thursday, March 21, 2013 12:53 PM
> Subject: Re: External Table + zipped files.. [29866]
>
> Then you have a problem. I don't think that there is anyway for external
> tables to work with a compressed file.
>
> Art
>
> Art S. Kagel
> Advanced DataTools (http://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 Thu, Mar 21, 2013 at 1:50 PM, Cesar Inacio Martins <
> cesar.inacio.martins@gmail.com> wrote:
>
> > Hi Art,
> >
> > Exactly , we don't have space to unzip this files , since they are text
> > have a big rate compression... so a file compressed have 380 MB ,
> > decompressed go to 4.8 GB.
> > We have a lot of files like this...
> >
> > 2013/3/21 Art Kagel <art.kagel@gmail.com>
> >
> > > I assume the OP doesn't have room for the uncompressed unload file,
> > > otherwise why not just gunzip the file and point the external table at
> > the
> > > uncompressed unload file?
> > >
> > > 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 Thu, Mar 21, 2013 at 11:56 AM, Kern Doe <kern_doe@yahoo.com> wrote:
> > >
> > > > You indicate the desire to use external table, so
> > > > 1. create the external table (just a normal one, no pipe)
> > > >
> > > > 2. load data from that gzip file to the external table.
> > > > mknod load.pipe p
> > > > chmod 660 load.pipe
> > > >
> > > > gunzip -c thatgzipfile.gz > load.pipe &
> > > >
> > > > dbload -d yourdbname -c load.cmd -l load.log> > > >
> > > > cat load.cmd:
> > > >
> > > > file "load.pipe" delimiter "|" <# columns>;
> > > >
> > > > insert into <thatexternaltable>;
> > > >
> > > > 3. delete <thatgzipfile.gz> if you like to save space
> > > > There must be some other better ways, and I would like to know too.
> > > >
> > > > For the time being, try this, let us know if it works.
> > > >
> > > > ________________________________
> > > > From: Cesar Inacio Martins <cesar.inacio.martins@gmail.com>
> > > > To: ids@iiug.org
> > > > Sent: Thursday, March 21, 2013 9:51 AM
> > > > Subject: External Table + zipped files.. [29856]
> > > >
> > > > Hi ,
> > > >
> > > > ifx 11.50 FC9X6 , AIX 6.1
> > > >
> > > > We have some history tables unloaded (with unload command) and
> > compressed
> > > > with gzip.
> > > >
> > > > What we need is able the database access this files , where will be
> > > > sporadic access .
> > > > My desire is use External Tables for that.
> > > >
> > > > The problem is : This access will occur by our system , so I not able
> > to
> > > > use PIPE files since there is no way start the decompress
> automatically
> > > , I
> > > > need this work "on-fly".
> > > >
> > > > If I direct the the PIPE to shell with the command gzip-cd <file> ,
> the
> > > > engine not accept.
> > > >
> > > > create external table his_2012_01 sameas h_mvt using (
> > > > datafiles('pipe:/tmp/ext201201.sh') , format 'delimited', express,
> > dbdate
> > > > 'dmy4/', numrows 9999999 );
> > > > select * from his_2012_01;> > > > # ^
> > > > #26158: File is incorrectly specified as a PIPE type:
> > > > (file)=(/tmp/ext201201.sh).
> > > > #
> > > >
> > > > Any ideas how make this work or workaround.
> > > >
> > > > Regards
> > > > Cesar
> > > >
> > > > --047d7bfcf9b6b5b63b04d8706399
> > > >
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>
>
*******************************************************************************
> > > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > > >
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>
>
*******************************************************************************
> > >