Netezza Storing Large data files to Local Systems

If you are working as a data analyst or business analyst then you may have to download the files to your local for analysis purpose. In this article, we will discuss on Netezza storing large data files to local systems.

If you have installed different Netezza SQL tools then it is very easy to store the large set of results to local system.

Netezza Storing Large data files to Local Systems- Methods

There are multiple methods that you can follow in Netezza Storing Large data files to Local Systems

Windows Command prompt

You will have the local copy of the nzload.exe or nzsql.exe files if system is installed with JDBC, ODBC or OLEDB drivers on your local system. Typical software path would be c:\Program Files (x86)\IBM Netezza Tools\Bin.

Read:

• Netezza nzload command and its Usage
• Netezza nzsql command and its Usage

Use below command to create the EXTERNAL TABLE in the local system by using nzsql.exe executable file.

C:>"c:\Program Files (x86)\IBM Netezza Tools\Bin\nzsql" -db testdb -host 192.168.118.139 -u user -pw password -db testdb –c “CREATE EXTERNAL TABLE ‘C:\\Users\\testdata\\samples.csv’ USING ( DELIMITER ‘,’ Y2BASE 2000 ENCODING ‘internal’ REMOTESOURCE ‘JDBC’ ESCAPECHAR ‘\’ ) AS SELECT COL1, COL2, COL3, COL, COL FROM YOUR_TABLE”;

Read: Netezza Query Tool Introduction, Features and Screenshots

Squirrel Sql Tool

If you are using Squirrel Sql Query tools than you might have noticed issues when it tries to return large result sets.  The tool is likely to crash in case if it tries to return large number of rows. This is because it needs to hold that large data set in memory.  You can also create the EXTERNAL file using Squirrel Sql Query tools. Below is the syntax:

CREATE EXTERNAL TABLE ‘C:\\Users\\testdata\\samples.csv’ 
USING ( DELIMITER ‘,’ Y2BASE 2000 ENCODING ‘internal’ REMOTESOURCE ‘JDBC’ ESCAPECHAR ‘\’ ) 
AS SELECT COL1, COL2, COL3, COL, COL FROM YOUR_TABLE”;

You can just run the above command in the Squirrel Sql Query command window and you are done, you will have the file in specified local path.

Aginity Workbench for Netezza

The above method will also works with Aginity Workbench for Netezza which uses the ODBC drivers. Below is the syntax:

Read: How to install Aginity Workbench for Netezza

CREATE EXTERNAL TABLE ‘C:\\Users\\testdata\\samples.csv’ 
USING ( DELIMITER ‘,’ Y2BASE 2000 ENCODING ‘internal’ REMOTESOURCE ‘ODBC’ ESCAPECHAR ‘\’ ) 
AS SELECT COL1, COL2, COL3, COL, COL FROM YOUR_TABLE”;

You can just run the above command in the Aginity Workbench Query window and you are done, you will have the file in specified local path.