Unload question
Question: can an Informix UNLOAD statement append to an existing file instead of overwriting it? The consensus is no — plain UNLOAD (dbaccess) always overwrites — but several workarounds were offered: UNLOAD to a named pipe/FIFO with a background 'cat >> file' (shell example given); use a single UNLOAD with a UNION of the queries; use SQLCMD, which supports 'UNLOAD APPEND TO FILE "x" SELECT ...' (and /dev/stdout), or Marco Greco's SQSL; or simply unload to separate files and 'cat' them together.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
I have been ask a question on unloads which I have not found an answer. Is there a way to issue an unload statement and have it append to the unload file rather than overwriting the file?? Bruce Simms Data Base Services TALX UCeXpress 10101 Woodfield Lane St. Louis, MO 63132 (314) 684-2238 FAX (314) 983-3238 bsimms@talx.com
unload to a pipe
j.
>From: Bruce Simms <BSimms@talx.com>
>Date: Thu May 04 15:33:59 CDT 2006
>To: ids@iiug.org
>Subject: Unload question [6658]
>
>I have been ask a question on unloads which I have not found an answer.
>
>Is there a way to issue an unload statement and have it append to the unload
>file rather than overwriting the file??
>
>Bruce Simms
>Data Base Services
>TALX UCeXpress
>10101 Woodfield Lane
>St. Louis, MO 63132
>
>(314) 684-2238
>FAX (314) 983-3238
>bsimms@talx.com
>
>
>*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
On 5/4/06, Bruce Simms <BSimms@talx.com> wrote:
>
> I have been ask a question on unloads which I have not found an answer.
>
> Is there a way to issue an unload statement and have it append to the unload
> file rather than overwriting the file??
SQLCMD supports:
UNLOAD APPEND TO FILE "mine" SELECT * FROM Table1;
UNLOAD APPEND TO FILE "mine" SELECT * FROM Table2;
IDS supports:
UNLOAD TO "mine" SELECT * FROM Table1 UNION SELECT * FROM Table2;
SQLCMD also supports "/dev/stdout" as a device, even on systems
without such a device; DB-Access et al will support it on Unix/Linux
systems where it is a real device.
--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/
Bruce Simms wrote: > I have been ask a question on unloads which I have not found an answer. > > Is there a way to issue an unload statement and have it append to the unload > file rather than overwriting the file?? use SQSL > > Bruce Simms > Data Base Services > TALX UCeXpress > 10101 Woodfield Lane > St. Louis, MO 63132 > > (314) 684-2238 > FAX (314) 983-3238 > bsimms@talx.com > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Ciao, Marco ______________________________________________________________________________ Marco Greco /UK /IBM Standard disclaimers apply! Structured Query Scripting Language http://www.4glworks.com/sqsl.htm 4glworks http://www.4glworks.com Informix on Linux http://www.4glworks.com/ifmxlinux.htm
unload/load, don´t have the posibility of append a file, but in other mail,
someone recomend you use a pipe.
If you are in a UNIX sistem, you can use this shell scrip:
# Append a file via pipe.
mknod /tmp/mypipe p
# My first unload create the asc file
nohup 'cat /tmp/mypipe > myappendfile.asc' &
echo "unload to '/tmp/mypipe' select * to mytable1" >a.sql
dbaccess mydb a.sql
# the second unload append the asc file
nohup 'cat /tmp/mypipe >> myappendfile.asc' &
echo "unload to '/tmp/mypipe' select * to mytable1" >a.sql
dbaccess mydb a.sql
# end of shell
Check the file 'myappendfile.asc'. It contains the two results of unload
statement
-----Mensaje original-----
De: Marco Greco [mailto:marco@4glworks.com]
Enviado el: Viernes, 05 de Mayo de 2006 03:25 a.m.
Para: ids@iiug.org
Asunto: Re: Unload question [6670]
Bruce Simms wrote:
> I have been ask a question on unloads which I have not found an answer.
>
> Is there a way to issue an unload statement and have it append to the
unload
> file rather than overwriting the file??
use SQSL
>
> Bruce Simms
> Data Base Services
> TALX UCeXpress
> 10101 Woodfield Lane
> St. Louis, MO 63132
>
> (314) 684-2238
> FAX (314) 983-3238
> bsimms@talx.com
>
>
>
****************************************************************************
***
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Ciao,
Marco
____________________________________________________________________________
__
Marco Greco /UK /IBM Standard disclaimers apply!
Structured Query Scripting Language http://www.4glworks.com/sqsl.htm
4glworks http://www.4glworks.com
Informix on Linux http://www.4glworks.com/ifmxlinux.htm
****************************************************************************
***
Forum Note: Use "Reply" to post a response in the discussion forum.
If you're using a script :
echo "unload to 'file1.first' select * from mytable1;" | dbaccess somedb -
echo "unload to 'file1.second' select * from mytable2;" | dbaccess somedb -
cat file1.first file1.second > file1
:-)
On Monday 08 May 2006 17:12, Julio Gerar.... wrote:
> unload/load, don´t have the posibility of append a file, but in other mail,
> someone recomend you use a pipe.
>
> If you are in a UNIX sistem, you can use this shell scrip:
>
> # Append a file via pipe.
>
> mknod /tmp/mypipe p
>
> # My first unload create the asc file
>
> nohup 'cat /tmp/mypipe > myappendfile.asc' &
>
> echo "unload to '/tmp/mypipe' select * to mytable1" >a.sql
>
> dbaccess mydb a.sql
>
> # the second unload append the asc file
>
> nohup 'cat /tmp/mypipe >> myappendfile.asc' &
>
> echo "unload to '/tmp/mypipe' select * to mytable1" >a.sql
>
> dbaccess mydb a.sql
>
> # end of shell
>
> Check the file 'myappendfile.asc'. It contains the two results of unload
> statement
>
> -----Mensaje original-----
> De: Marco Greco [mailto:marco@4glworks.com]
> Enviado el: Viernes, 05 de Mayo de 2006 03:25 a.m.
> Para: ids@iiug.org
> Asunto: Re: Unload question [6670]
>
> Bruce Simms wrote:
> > I have been ask a question on unloads which I have not found an answer.
> >
> > Is there a way to issue an unload statement and have it append to the
>
> unload
>
> > file rather than overwriting the file??
>
> use SQSL
>
> > Bruce Simms
> > Data Base Services
> > TALX UCeXpress
> > 10101 Woodfield Lane
> > St. Louis, MO 63132
> >
> > (314) 684-2238
> > FAX (314) 983-3238
> > bsimms@talx.com
>
> ***************************************************************************
>* ***
>
> > Forum Note: Use "Reply" to post a response in the discussion forum.
--
Mike Aubury