Discussion:
[freetds] Netezza and named pipes
Roger Hill
2014-10-04 08:04:22 UTC
Permalink
My first post to the list: please be gentle!

I need to copy a large volume of data every day from a Netezza database to
several other SQL server databases.

I have a solution that works: I use isql to extract to a named pipe, pass the
output through a couple of 'cut' steps to remove unwanted bits and pieces, and
then land the data on disk into a temporary file. I then use freebcp to load
it to the SQL server database. But it's convoluted, and I'm looking for a
better way.

Two questions come out of this: Firstly I know that freebcp did not support
input from named pipes as recently as 2011; is there support for named pipes
now?

And secondly, something like 'datacopy' between heterogeneous databases (in my
case Netezza to SQL server) would eliminate a lot of mucking about on the
intermediate box. Any chance of something like that?

Any other suggestions?


Cheers
Roger Hill


****************************************
Roger Hill
****************************************
Frediano Ziglio
2014-10-12 08:27:59 UTC
Permalink
Post by Roger Hill
My first post to the list: please be gentle!
I need to copy a large volume of data every day from a Netezza database to
several other SQL server databases.
I have a solution that works: I use isql to extract to a named pipe, pass the
output through a couple of 'cut' steps to remove unwanted bits and pieces, and
then land the data on disk into a temporary file. I then use freebcp to load
it to the SQL server database. But it's convoluted, and I'm looking for a
better way.
Well do you have any connector available? I don't know Netezza at all.
Basically assuming there is a connector for Netezza for sql server you
setup a new connection to Netezza in sql server that you can transfer data
from Netezza to sql server using a sort of INSERT INTO TABLE SELECT ...
FROM NETEZZA TABLE.
Post by Roger Hill
Two questions come out of this: Firstly I know that freebcp did not support
input from named pipes as recently as 2011; is there support for named pipes
now?
Head code does. But if I understood you are converting everything to
characters and then back to database. Pay lot of attention to separators or
you risk to corrupt data!
Post by Roger Hill
And secondly, something like 'datacopy' between heterogeneous databases (in my
case Netezza to SQL server) would eliminate a lot of mucking about on the
intermediate box. Any chance of something like that?
A tool that would use just ODBC would do. I don't know any such tool.
Datacopy is written to transfer sql server to sql server only. And use
dblib which is bound to sql server.
Post by Roger Hill
Any other suggestions?
Cheers
Roger Hill
Frediano
Roger Hill
2014-10-12 08:38:45 UTC
Permalink
Frediano:
Thanks for the notes.

I will investigate the SQL server connectors. I'm not a SQL server expert!


My solution is working ok...just trying to improve it is all.

Many thanks.

Roger
Post by Roger Hill
Post by Roger Hill
My first post to the list: please be gentle!
I need to copy a large volume of data every day from a Netezza database to
several other SQL server databases.
I have a solution that works: I use isql to extract to a named pipe, pass
the
Post by Roger Hill
output through a couple of 'cut' steps to remove unwanted bits and
pieces, and
Post by Roger Hill
then land the data on disk into a temporary file. I then use freebcp to
load
Post by Roger Hill
it to the SQL server database. But it's convoluted, and I'm looking for a
better way.
Well do you have any connector available? I don't know Netezza at all.
Basically assuming there is a connector for Netezza for sql server you
setup a new connection to Netezza in sql server that you can transfer data from
Netezza to sql server using a sort of INSERT INTO TABLE SELECT ...
FROM NETEZZA TABLE.
Post by Roger Hill
Two questions come out of this: Firstly I know that freebcp did not
support
Post by Roger Hill
input from named pipes as recently as 2011; is there support for named
pipes
Post by Roger Hill
now?
Head code does. But if I understood you are converting everything to
characters and then back to database. Pay lot of attention to separators or you
risk to corrupt data!
Post by Roger Hill
And secondly, something like 'datacopy' between heterogeneous databases
(in my
Post by Roger Hill
case Netezza to SQL server) would eliminate a lot of mucking about on the
intermediate box. Any chance of something like that?
A tool that would use just ODBC would do. I don't know any such tool.
Datacopy is written to transfer sql server to sql server only. And use
dblib which is bound to sql server.
Post by Roger Hill
Any other suggestions?
Cheers
Roger Hill
Frediano
_______________________________________________
FreeTDS mailing list
FreeTDS at lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds
****************************************
Roger Hill
****************************************

Loading...