SQL Server passing tables Netezza

Question

Just wondering what is best practice to achieve:

We have stored procedure that runs in SQL Server and needs to do some calculations with a huge fact table to be stored on Netezza.

The flow:

1. Stored procedure will create temp tables on SQL Server
2. These will be sent to Netezza to be joined with the fact table
3. Calculations will be made in Netezza
4. Results will be passed back to SQL Server

What are the ways to pass the temp tables from SQL Server to Netezza?

Thanks

Answer 1

The Only way that I know of to get data in and out of Netezza is through flat files, or using the ODBC drivers to work with Netezza directly and hold a recordset in memerory and write to Netezza through an odbc connection..

Regardless of if you are on Windows or Unix, Netezza comes with client tools that you can use to connect to Netezza. Fyi, don't bother looking for them on the internet. You will have to get them from Netezza directly, or from the person that manages that relationship.

I would suggest looking into how I might be able to use the ODBC drives in SSIS to do the work for you. I'm not a pro at SSIS so I can't say I would know how to do that, but I would look into that first.

If I had to accomplish the task I would write something in C# to perform the following tasks.

• Create flat files from sql server
• Connect to Netezza Create external table that links to the flat file.
• Call procedures in Netezza to do the work and generate the data for export in a temp table.
• Export the new data to a flat file and import that back into sql server.

Now that I think about it you might also try the following, it is untested however. I wonder if you can create a linked table in sql server and an external table in Netezza that uses the same flat file. Baring a file lock, if they can, you can create a quasi-link to netezza from sql server.

To find out more about external tables in netezza. Look in the doc Netezza User Guide in chapter 5.

Netezza User Guide

Also, if you are interested in the coding side there is a very good link below to how to connect to netezza via c#.

Stack Post

I ended up using some of that post to build the method below to execute commands against Netezza.

OdbcDataReader GetReaderForCommand(string strCmd, string dbname)
{

    var conn = new OdbcConnection();
    conn.ConnectionString = "Driver={NetezzaSQL};servername=<servername>;port=5480;database="+dbname+"; username=<username>;password=<pwd>;";

    OdbcDataReader rdr = null;


        conn.Open();
        System.Data.Odbc.OdbcCommand cmd = new System.Data.Odbc.OdbcCommand(strCmd, conn);
        rdr = cmd.ExecuteReader();
        return rdr;


}

Lastly, here are a couple of links that I would follow up with.

enzeecommunity.com - User base to search and ask questions of.

A free management studio to use with Netezza.

http://www.aginity.com/ProductivityTools/WorkbenchOverview.aspx -