IDS Forum

Re: External Table + zipped files..

Posted By: Kern Doe
Date: Thursday, 21 March 2013, at 4:31 p.m.

In Response To: Re: External Table + zipped files.. (Art Kagel)

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 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.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>

--f46d0447878ffd33a404d874d151

*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.

Messages In This Thread

• External Table + zipped files..
  Cesar Inacio Martins -- Thursday, 21 March 2013, at 10:51 a.m.
  • Re: External Table + zipped files..
    Kern Doe -- Thursday, 21 March 2013, at 11:56 a.m.
    • Re: External Table + zipped files..
      Cesar Inacio Martins -- Thursday, 21 March 2013, at 12:44 p.m.
    • Re: External Table + zipped files..
      Art Kagel -- Thursday, 21 March 2013, at 1:15 p.m.
      • Re: External Table + zipped files..
        Kern Doe -- Thursday, 21 March 2013, at 1:28 p.m.
      • Re: External Table + zipped files..
        Cesar Inacio Martins -- Thursday, 21 March 2013, at 1:50 p.m.
        • Re: External Table + zipped files..
          Art Kagel -- Thursday, 21 March 2013, at 1:53 p.m.
          • Re: External Table + zipped files..
            Kern Doe -- Thursday, 21 March 2013, at 3:34 p.m.
            • Re: External Table + zipped files..
              Art Kagel -- Thursday, 21 March 2013, at 4:02 p.m.
              • Re: External Table + zipped files..
                Kern Doe -- Thursday, 21 March 2013, at 4:31 p.m.
                • Re: External Table + zipped files..
                  JACK PARKER -- Wednesday, 17 December 2014, at 3:52 p.m.
            • Re: External Table + zipped files..
              Cesar Inacio Martins -- Friday, 22 March 2013, at 7:05 a.m.
  • Re: External Table + zipped files..
    RICHARD KOFLER -- Thursday, 21 March 2013, at 6:11 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.