ODBC to load data into NPS

Answer

ODBC defines three ways of getting data into databases--bulk , parameterized , and singleton. These are in order from highest to lowest performance.

Netezza supports Parameterized and Singleton insert operations, and we also provide the External Table Interface to provide customers/partners the fastest possible means of loading data into NPS systems.

Bulk

The fastest method of loading data for "bulk" operations is using external tables. Issue the following sequence of commands:

For releases prior to 3.0.1 the following sequence of calls works.

SQL EXECUTE DIRECT CREATE EXTERANAL TABLE …..

SQL EXECUTE DIRECT INSERT – SELECT

On a second thread send the data to the external table.

Execute Direct DROP EXTERNAL TABLE

For release 3.0.1 and later, the following SQL will create the table and load it.

SQL EXECUTE DIRECT 'insert into target_table select * form external 'path/to/file' using ( external table options go here );'

On a second thread feed the 'path/to/file' with data, assuming file is a named pipe.

Parameterized

Parameterized Inserts are a fast way of inserting data over ODBC on Netezza. Parameterized operations use an array of rows of binary data sent from the program in a single operation to the back end, the array size is set by the application.

When NPS gets the data from the client it creates a temporary ASCII flat file on the host disk containing a copy of the records in the array this file is added to for each set of records sent by the application. When NPS gets a commit statement from the client, NPS internally issues a Postgres “copy” command to transfer the data from host disk to SPU. If the copy works, we return an SQL success message, if it fails we return and SQL failure message.

These files on the host are named “tempXXXXX” and can be found in /nz/tmp.

Note: Work is planned to stream line the process by removing the temp file creation on the host. Parameterized inserts execute one compile and execute cycle to insert all data to the SPU's.

Singleton

In a word Singleton inserts should be avoided because they require a full compile and execute cycle to insert one row on one SPU.