unload and load data using External Table

There a few ways you can transfer Netezza data across another Netezza database.  One way is to use nz_migrate.  Another way through Aginity Tools -> Import and the third way is through External Table in Aginity.  Note:  You can only use this method from Aginity

Below example, dumps the data from the query to your local PC temp directory while connecting to the source database

UNLOAD DATA

CREATE EXTERNAL TABLE ext_db 'c:\temp\ext_db.dat'

USING

(

remotesource 'odbc'

delimiter '|'

ignorezero false

ctrlchars true

escapechar '\'

logDir 'c:\temp'

boolStyle 'T_F'

encoding 'internal'

nullValue 'N'

maxErrors 1

)

AS

SELECT database, owner, cast(createdate as date) as ts  from _v_database;

While connecting your Aginity to your target NPS host database, this method can be used to load the data

to your say Development environment.

LOAD DATA (in different NPS host database)

create external table abc

(

       DATABASE NATIONAL CHARACTER VARYING(255),

       OWNER NATIONAL CHARACTER VARYING(255),

       TS DATE

)

using

(

dataobject ('c:\temp\ext_db.dat')

Remotesource 'odbc'

Encoding 'INTERNAL'

EscapeChar '\'

CrInString true

CtrlChars true

Delimiter '|'

NullValue 'NULL'

)

;