Loading Datas using Netezza Utilities

Lets look at some of the Utilities available in Netezza 

Nzload :

nzload is a command line utility which can be run in the local or remote client.Nzload commad is used for bulk loading of data into tables .It is like external table but this works faster and multiple files can be parelley loaded into tables.For loading a file first create the table and then use the nzload command to load into table

Syntax for nzload :

nzload -db <database name> -h <host name> -u <username> -pw <password>

-t <tablename> -df <datafilename> -delim <delmiter>  -dateDelim <date delimiter in single quotes> -dateStyle <date format> -bf <bad filename> -lf <log filename>

nzload command returns the following status :

0--> the data is loaded successfully into the target table

1--> the data is loaded into the table but with errors and the errors are within the maxErrors option

2-->nzload failed

Other options available are 

maxErrors-->we can set the limit for errors

Skiprows --> for skipping header records

Fillrecord-->fills the trailing spaces to nulls

ignoreZero-->ignores zero byte record 

Badfile and Logfile :

nzload command generates log and bad files which will be in the format <tablename>.<database name>.bad/log.the files by default will be present in the working directory. we can override the defualt location by specifying the location in outputDir parameter .We can override the log and bad file usinf -lf and -bf parameters.Nzload internally creates an external table ,loads the data into the table and then drop the table after loading .Statistics are automatically generated for these tables upon completion

External tables :

External tables can also be used for loading data into the table 

statistics are automatically generated for an external table.we can drop or insert records into an external table.However we cannot delete or truncate an external table.Also we cant use it in union operation or for multiple table joins.When we create an external table ,it is a logical concept and the data actually resides in the file.When we select or insert records into external table data gets added in the file associated with the table.

Syntax for external table :

CREATE EXTERNAL TABLE <table_name>
[sameas table] | ([column_name [,...]])
USING (DATAOBJECT (<host_file_name>) DELIMITER <field_delimiter>) [options]

The options are same as that of unload command._v_external and _v_extobject can be used to get the  

 meta data about the external tables.

NZSQL: