UNLOAD to a pipe instead of to a file
Answered: green (solid confidence) — OUTPUT TO PIPE program WITHOUT HEADINGS in dbaccess gives the unnamed-pipe behaviour; the asker confirmed it and adapted it into a script.
Advisory only.
Posted in 2025
Jacob Salomon wanted to UNLOAD from dbaccess to an unnamed pipe. Paul Watson suggested unloading to a named pipe file; Hrvoje Zokovic showed a dbexport FIFO to ssh example. David Williams pointed to OUTPUT TO PIPE program WITHOUT HEADINGS, which Jacob confirmed works, with awk to drop blank lines and stderr discarded. Solved.
Topics: Server Administration, Migration, Import/Export & Data Conversion
Since my previous thread ended to quickly and painlessly, I decided to try my luck again. Gain without the pain. :-)
I have this memory from ages ago that I was able to to an UNLOAD command in dbaccess but instead of to a file, I unloaded to a pipeline to another program. The syntax was something like:
unload to pipe "program"select <columns> from <table>..
The above syntax does not work, of course. I definitely have unloaded to a named FIFO but the syntax was the same as for a file so dbaccess was none the wiser. Bat the unnamed pipe? Not so much.
So, is my memory playing Freudian tricks on me? (Hey, it's supposed to be my friend!)
I have an easy enough workaround to use in a script: unload to a temp file in dbaccess, and cat the output in the shell, then delete the temp file. But I was hoping for a more direct method to run dbaccess and capture that output straight into a shell variable.
If not possible (and my memory has extra feathers) I have that workaround. If possible, please post the syntax here.
Thanks!
------------------------------
Jacob Salomon
------------------------------
Unload to "'my.pipe"
And make my.pipe a fileCheers
Paul
Paul Watson
Oninit LLC
+1-913-387-7529
www.oninit.com
Oninit® is a registered trademark of Oninit LLC
I used sth like this when machine with database did not have enough space in file systems
##############
#dbexport pipe + network
export TAPEDEV=/home/informix/tmp/tapedev
mkfifo $TAPEDEV
cat $TAPEDEV | ssh root@serverB "gzip -c | cat > /BIG_FS/exports/mydb_export.gz" &
dbexport mydb -ss -t $TAPEDEV -b 4096 -s 0 -f mydb_schema.sql
#dbimport
ssh root@serverB "gzip -cd /BIG_FS/exports/mydb_export.gz" | cat > $TAPEDEV
dbimport mydb -t $TAPEDEV -b 4096 -s 0 -f mydb_schema.sql
HTH
Hrvoje
------------------------------
Hrvoje Zokovic
Technical Support Director
ALFATEC Group
Zagreb
------------------------------
Hi Jacob, https://www.ibm.com/docs/en/informix-servers/12.10.0?topic=statements-output-statement OUTPUT TO PIPE program WITHOUT HEADINGS !! Regards, David. ------------------------------ David Williams Senior Database Platform Engineer Flutter London ------------------------------
Dave!
You zapped it just perfect! I was looking for an option under "unload". Like looking for my pants in the refrigerator. (Not that old yet..;-)
If you don't know the name of a feature, ain't no MAN or manual page gonna find it for you! (I get by with a little help from my friends...)
I did need to adjust the output a trifle. This relates to my previous question about when the server started up or stats was zeroed.
In a shell script:
dbaccess sysmaster - 2>/dev/null <<EOQ | awk 'NF>0 {print}'
output to pipe "cat" without headings
select dbinfo('UTC_TO_DATETIME', sh_boottime),
dbinfo('UTC_TO_DATETIME', sh_pfclrtime)
from sysshmvals;
EOQ
That awk was needed in the pipeline because dbaccess generate extra empty lines to stdout. And, of course, I didn't want the "database selected" and "1 rows retrieved" messages that dbaccess sends to stderr so I sent those to /dv/null.
Thanks for recognizing where I gone astray.
------------------------------
Jacob Salomon
------------------------------