dbaccess unload to a pipe ~ Answered: amber (solid confidence) — Jonathan Leffler corrects the asker's hypothesis that a non-blocking named pipe is dropping rows (pipes are blocking devices) and redirects to checking the Perl code or using SQLCMD/SQLUNLOAD for large-file unloads instead of dbaccess UNLOAD TO a pipe.

Advisory only.

We have several tables that if unloaded to a file would be larger than 2GB. To manage this I have written a pipe in perl. I use dbaccess' unload to to unload to the pipe file that my perl program issitting behind. My program will catch rows and slam them into a file until the file gets to around 1.7 GB (just in case my calculations are wrong). I then close that file and open a new one and continue throwing rows into the new file. In testing this I have found that I am dropping rows. I have poured through my code and cannot find any reason for this. The question: I was wondering if maybe when I stop reading the pipe so that I can switch output files that the unload doesn't block and it just keeps dumping rows that my pipe isn't catching? __________________________________ Do you Yahoo!? Yahoo! Mail - More reliable, more storage, less spam http://mail.yahoo.com sending to informix-list

Are you sure that you are flushing the Perl buffers before closing the file. You could download JL sqlcmd, AFAIK this copes better with big files Cheers Paul DL Redden wrote: > > We have several tables that if unloaded to a file > would be larger than 2GB. To manage this I have > written a pipe in perl. I use dbaccess' unload to to > unload to the pipe file that my perl program is> sitting behind. My program will catch rows and slam > them into a file until the file gets to around 1.7 GB > (just in case my calculations are wrong). I then close > that file and open a new one and continue throwing > rows into the new file. > > In testing this I have found that I am dropping rows. > I have poured through my code and cannot find any > reason for this. > > The question: > > I was wondering if maybe when I stop reading the pipe > so that I can switch output files that the unload > doesn't block and it just keeps dumping rows that my > pipe isn't catching? > > __________________________________ > Do you Yahoo!? > Yahoo! Mail - More reliable, more storage, less spam > http://mail.yahoo.com > sending to informix-list -- Paul Watson # Oninit Ltd # Growing old is mandatory Tel: +44 1436 672201 # Growing up is optional Fax: +44 1436 678693 # Mob: +44 7818 003457 # www.oninit.com #

"DL Redden" <redden96@yahoo.com> wrote in message news:c3d1ek$8db$1@terabinaries.xmission.com... > > We have several tables that if unloaded to a file > would be larger than 2GB. To manage this I have > written a pipe in perl. I use dbaccess' unload to to > unload to the pipe file that my perl program is> sitting behind. My program will catch rows and slam > them into a file until the file gets to around 1.7 GB > (just in case my calculations are wrong). I then close > that file and open a new one and continue throwing > rows into the new file. > > In testing this I have found that I am dropping rows. > I have poured through my code and cannot find any > reason for this. If you have Jonathan Leffler's sqlunload, you can use this command:- $ sqlunload -d database -t table | gzip -9 > table.unl.gz.

DL Redden wrote: > We have several tables that if unloaded to a file > would be larger than 2GB. To manage this I have > written a pipe in perl. I use dbaccess' unload to to > unload to the pipe file that my perl program is> sitting behind. My program will catch rows and slam > them into a file until the file gets to around 1.7 GB > (just in case my calculations are wrong). I then close > that file and open a new one and continue throwing > rows into the new file. > > In testing this I have found that I am dropping rows. > I have poured through my code and cannot find any > reason for this. If you want, you can consider posting your Perl code so we can pore over it too, before pouring scorn^H^H^H^H^H^H^H^H^H^H^H^H^H we provide a diagnosis. :-)) > The question: > > I was wondering if maybe when I stop reading the pipe > so that I can switch output files that the unload > doesn't block and it just keeps dumping rows that my > pipe isn't catching? I guess you mean that you are using a named pipe, rather than just piping the output of dbaccess to perl (which will likely work; send the output to /dev/stdout or /dev/fd/1). Actually, it doesn't matter; both named and anonymous pipes are blocking devices; when they reach capacity, the writers are held up pending a reader being available to read it. So, I don't think rows are being lost because the records are being slammed onto the pipe and overwritten before they are read. A couple of people mentioned SQLCMD. It should configure with large file support automatically - so it should be able to write direct to large files. The SQLUNLOAD (aka sqlcmd -U) command is very succinct for full table dumps. SQLCMD also has the ability to do: UNLOAD TO PIPE "program of your choosing" SELECT ...; However - you are being WARNED - it does not handle SIGPIPE well if there's a problem while the output is being unloaded. It is not recommended to try UNLOAD TO PIPE "sed 1q > /tmp/first.line", for example. That requires a bit of work in the output.c file code which hasn't occurred yet - due to a severe lack of round tuits. (It also has skeletal support for LOAD FROM PIPE and RELOAD FROM PIPE. Faintly similar comments apply - but the problem is a lot less severe.) -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/