Unloading data to a remote client system

You can unload data from a database table on a Netezza Performance Server host system to a remote client. This unload does not remove rows from the database but instead stores the unloaded data in a flat file (external table) that is suitable for loading back into a Netezza Performance Server database.

About this task

You can unload data to any of the supported Netezza Performance Server clients, which include Windows, Linux®, Solaris, AIX®, and HP-UX clients. You can unload all data types (including Unicode) and file types (uncompressed and compressed formats). Unloading for the fixed-length format is not supported.

To create an external table, you must be the admin user or have the Create External Table administration privilege. You must also have permission to write to the path of the data object.

Procedure

  1. Establish a connection between the client machine and the Netezza Performance Server system host in one of the following ways:
    • Create an ODBC, JDBC, or OLE DB connection through an application.
    • Create a connection by issuing the nzsql command with the -host option.
  2. Create an external table by issuing the CREATE EXTERNAL TABLE command.
    An example follows: 
    CREATE EXTERNAL TABLE emp_backup SAMEAS emp USING (
DATAOBJECT ('/tmp/emp.dat')
REMOTESOURCE 'ODBC');

    The DATAOBJECT file specification must refer to a valid file on the receiving machine. For the REMOTESOURCE option, specify the value that corresponds to your connection method: ODBC, JDBC, OLE-DB, NZSQL, or YES (the YES value is equivalent to the NZSQL value). If you do not specify a remote source, the system unloads the data to a file on the Netezza Performance Server system host.

  3. Insert data from the table on the Netezza Performance Server system host into the external table. An example follows: 
    
INSERT INTO emp_backup SELECT * FROM emp;