nzload command syntax

You can use the nzload command to load data. You can accept the defaults or specify options by using the command line, the control file, or environment variables.

Syntax

The nzload command uses the following syntax:
nzload [-h|-rev] [options]

Inputs

The precedence of the input values is as follows:
  • Command line.
  • Control file. Without a control file, you can do only one load at a time; the use of a control file allows multiple loads.
  • Environmental variables. You can use these only for the user, password, database, and host.
  • Built-in defaults.

Option names are not case sensitive. Every option has a standard name for use either on the command line or in the control file.

Many options include an argument, which you can enclose in either single or double quotation marks. The nzload command ignores letter casing for the characters in option arguments. For example, -boolStyle YES_NO is equivalent to -boolStyle yes_no. You must use quotation marks for options that require a punctuation character as an argument and use an escape character if quotation marks are part of the argument.

In many cases, the nzload command accepts external table options that have the same names as those described in External table options, except that the command-line version of the option for the nzload command uses a hyphen (for example, –boolStyle instead of BoolStyle). The following table contains additional external table options that you can specify for the nzload command. For a complete list of external table options that you can specify for the nzload command, see Mapping of external table options.
Table 1. Additional external table options for the nzload command
Option Description
-bf filename Specifies the bad or rejected rows file name. The file is overwritten if it exists.
-df filename Specifies the data file to load. If you do not specify a path, the system uses the special token <stdin> to store the file path string. This option corresponds to the DataObject external table option, which you can use for SQL.
-fileBufSize

-fileBufByteSize

 Specifies the chunk size (MB for fileBufSize or bytes for fileBufByteSize) at which to read the data from the source file. These options correspond to the SocketBufSize external table option, which you can use in a control file for the nzload command or for SQL.
-lf filename Specifies the log file name. If the file exists, it is appended to.
-outputDir dir Specifies the output directory for the log and bad or rejected rows files. This option corresponds to the LogDir external table option, which you can use for SQL..

Additional options

The nzload command takes the following additional options:
Table 2. Additional options for the nzload command
Option Description
-allowReplay n If set to a non-zero number, enables load continuation by specifying the number of allowable query restarts if an SPU is reset or fails over. To enable load continuation, the option sets the LOAD_REPLAY_REGION session variable to 1 and the MAX_QUERY_RESTARTS session variable to the specified number. The -allowReplay option has no default value. If you do not specify the option or you set it to 0, load continuation is not enabled, and the system defaults to the Postgres default setting.
-caCertFile path Specifies the path name of the root CA certificate file on the client system. This argument is used by Netezza Performance Server clients that use peer authentication to verify the Netezza Performance Server host system. The default value is NULL, which skips the peer authentication process.
-cf filename Specifies the control file.
-db database Specifies the database to load (overrides the value of the NZ_DATABASE environment variable).
-h Displays help.
-host name Specifies the host name or IP address (overrides the value of the NZ_HOST environment variable). If you do not specify a value, the command runs on the local host. If you set this option to any name other than localhost or any IP address other than the reserved one (127.0.0.1), the system sets the remotesource option to ODBC.
-logFileSize n Sets the value of the LOAD_LOG_MAX_FILESIZE session variable, which specifies the size in MB of the log and bad or rejected rows files. The default is 2000 MB (2 GB).
-loginTimeout int-seconds Specifies the login timeout, in seconds. This option overrides the default value of 30 seconds.
-merge operation Specifies that the contents of the data file are to be merged into the target table. The possible values of operation are as follows:
INSERT
Inserts new rows into the table.
UPDATE
Updates existing rows in the table.
INSERTUPDATE
Inserts new rows into the table and updates existing rows in the table.

You cannot choose which columns to update or insert. Also, there is no delete functionality.

To identify whether to insert or update a particular row, you must also specify a match condition by using the -mergeSchema option, the -mergeOn option, or both. If the match condition is met (the row exists in the table), the row is updated. If the match condition is not met (the row does not exist in the table), the row is inserted.
-mergeOn "condition" Specifies a match condition, a filter, or both for the -merge option, in the form of an SQL expression:
  • The match condition determines whether the value of a particular column in the data file matches the value of the same column in the table. A match condition has the following form: 
    src.column_name = table_name.column_name
    Qualify the name of the column with src for the data file and table_name for the table. Use the same column_name value for both the data file and table. An example follows:
    -mergeOn "src.col1 = T1.col1"

    If you do not specify a match condition by specifying the primary_key value, you must specify a match condition by using the -mergeOn option. If you use both the primary_key value and -mergeOn option to specify match conditions, those conditions are combined.

  • A filter identifies a subset of records in the data file, target table, or both. Specify one or more column names and operators. Qualify the name of a column with src for the data file and table_name for the table. Use the same column_name value for both the data file and table. An example follows:
    -mergeOn "src.col1 > 3"
-mergeSchema "column_name data_type [primary_key]" Specifies the schema of the target table and, optionally, a match condition for the -merge option. An example follows: 
-mergeschema "col1 int primary_key,col2 int,col3 int"
The following rules apply:
  • You must specify all the columns that are in the table, although the case of the names that you specify does not need to match the case of the names in the table.
  • The order in which you specify the columns and the order of the columns in the data file must match. However, the order of the columns in the table can be different.
  • The data type that you specify for a column must match the data type for the column in the table. Specify the double precision data type as double_precision.
  • The interval data type is not supported.

The primary_key value identifies a column whose value is used as a match condition. The match condition determines whether the value of a particular column in the data file matches the value of the same column in the table. For example, if you specify col1 int primary_key, the system searches for a match between the value of the col1 column in the data file and the value of the col1 column in the table. If you do not specify the primary_key value, you must specify a match condition by using the -mergeOn option. If you use both the primary_key value and -mergeOn option to specify match conditions, those conditions are combined.
-port number Specifies the port to use, which you can use to override the default.
-pw password Specifies the password of the Netezza Performance Server user (overrides the value of the NZ_PASSWORD environment variable).
-rev Displays the Netezza Performance Server level.
-schema schema Specifies the schema in which to load the table for a Netezza Performance Server system that supports multiple schemas in a database. If you do not specify the -schema option, the system uses the value of the NZ_SCHEMA environment variable, if you set it. If the value of the NZ_SCHEMA environment variable is not set, the system uses the default schema for the database.
-securityLevel level Specifies the security level for the session. The option has four values:
preferredUnSecured
This value is the default value. Specify this value when you prefer an unsecured connection but you will accept a secured connection if the Netezza Performance Server system requires one.
onlyUnSecured
Specify this value when you want an unsecured connection to the Netezza Performance Server system. If the Netezza Performance Server system requires a secured connection, the connection is rejected.
preferredSecured
Specify this option when you want a secured connection to the Netezza Performance Server system but you will accept an unsecured connection if the Netezza Performance Server system is configured to use only unsecured connections.
onlySecured
Specify this option when you want a secured connection to the Netezza Performance Server system. If the Netezza Performance Server system accepts only unsecured connections or you attempt to connect to a Netezza Performance Server system that is running a release before 4.5, the connection is rejected.

If you specify an invalid value for the -securityLevel option, the preferredUnSecured level is used by default.
-t table Specifies the table name. You can specify a fully qualified name.
-u user Specifies the database user name (overrides the value of the NZ_USER environment variable).

Outputs

See nzload command status reporting.