bcp unload and named pipes

[Jim Morrissey]

I need to unload 1Tb of data into flat ascii files from ase 12.5 (Solaris 8,
E10k), but there is not nearly enough disk space left to do this. I'd like
to dump it using bcp, but I heard that bcp will not support named pipes, but
only files. Is this true still? Can anyone suggest a reasonable alternative
for this volume of data?

Thanks,

-Jim

[Sherlock, Kevin]

Using a named pipe is certainly possible. If I understand what you want to do
correctly that is.

You don't have enough disk space for the tables, but perhaps you'd like to
compress them as they bcp out?

# mkfifo bcp_pipe
# bcp mydb.dbo.mytable out bcp_pipe -n -Usa -SMYSERVER -Pmypass -A8192 &
# compress -c bcp_pipe > mytable.dat.Z

Post by Jim Morrissey
I need to unload 1Tb of data into flat ascii files from ase 12.5 (Solaris 8,
E10k), but there is not nearly enough disk space left to do this. I'd like to
dump it using bcp, but I heard that bcp will not support named pipes, but only
files. Is this true still? Can anyone suggest a reasonable alternative for this
volume of data?
Thanks,
-Jim

[Jim Morrissey]

Thanks Kevin.

Some documentation I read said it was NOT possible to use a named pipe. If
your example below works, that's what I need.

-Jim

Post by Sherlock, Kevin
Using a named pipe is certainly possible. If I understand what you want
to do correctly that is.
You don't have enough disk space for the tables, but perhaps you'd like to
compress them as they bcp out?
# mkfifo bcp_pipe
# bcp mydb.dbo.mytable out bcp_pipe -n -Usa -SMYSERVER -Pmypass -A8192 &
# compress -c bcp_pipe > mytable.dat.Z

Post by Jim Morrissey
I need to unload 1Tb of data into flat ascii files from ase 12.5 (Solaris
8, E10k), but there is not nearly enough disk space left to do this. I'd
like to dump it using bcp, but I heard that bcp will not support named
pipes, but only files. Is this true still? Can anyone suggest a reasonable
alternative for this volume of data?
Thanks,
-Jim

[Sherlock, Kevin]

It works. I use it all the time.

Note that I did use a large packet size "-A8192" in the command example. You
have to adjust that as necessary. If your ASE allows large packet sizes, then
use the largest setting possible on your bcp command line.

Post by Jim Morrissey
Thanks Kevin.
Some documentation I read said it was NOT possible to use a named pipe. If
your example below works, that's what I need.
-Jim

Post by Sherlock, Kevin
Using a named pipe is certainly possible. If I understand what you want to
do correctly that is.
You don't have enough disk space for the tables, but perhaps you'd like to
compress them as they bcp out?
# mkfifo bcp_pipe
# bcp mydb.dbo.mytable out bcp_pipe -n -Usa -SMYSERVER -Pmypass -A8192 &
# compress -c bcp_pipe > mytable.dat.Z

Post by Jim Morrissey
I need to unload 1Tb of data into flat ascii files from ase 12.5 (Solaris 8,
E10k), but there is not nearly enough disk space left to do this. I'd like to
dump it using bcp, but I heard that bcp will not support named pipes, but
only files. Is this true still? Can anyone suggest a reasonable alternative
for this volume of data?
Thanks,
-Jim

[Sherlock, Kevin]

I should also say that it's important to put the "bcp" in the background using
"&" at the end of the command. If not, you'll need to run your "compress" from
a different terminal.

If you put bcp in the background, then you will immediately get a shell prompt
back on the current terminal session so you can issue your "compress" command
from the same shell.

Post by Sherlock, Kevin
It works. I use it all the time.
Note that I did use a large packet size "-A8192" in the command example. You
have to adjust that as necessary. If your ASE allows large packet sizes,
then use the largest setting possible on your bcp command line.

Post by Jim Morrissey
Thanks Kevin.
Some documentation I read said it was NOT possible to use a named pipe. If
your example below works, that's what I need.
-Jim

Post by Sherlock, Kevin
Using a named pipe is certainly possible. If I understand what you want to
do correctly that is.
You don't have enough disk space for the tables, but perhaps you'd like to
compress them as they bcp out?
# mkfifo bcp_pipe
# bcp mydb.dbo.mytable out bcp_pipe -n -Usa -SMYSERVER -Pmypass -A8192 &
# compress -c bcp_pipe > mytable.dat.Z

Post by Jim Morrissey
I need to unload 1Tb of data into flat ascii files from ase 12.5 (Solaris 8,
E10k), but there is not nearly enough disk space left to do this. I'd like
to dump it using bcp, but I heard that bcp will not support named pipes, but
only files. Is this true still? Can anyone suggest a reasonable alternative
for this volume of data?
Thanks,
-Jim