New features and changes in Release 7.2.1

The following sections describe the new features and changes in NPS® Release 7.2.1.

Concurrent truncate support

This release includes documentation updates for the enhancement delivered in NPS Release 7.2.0.4 and later that enables support for SELECTs in concurrent transactions against a truncated table.

For NPS releases before 7.2.0.4, disk storage for a truncated table is freed when the truncating transaction commits. (A truncating transaction can also safely be rolled back, preserving its storage and content.) In addition, after a truncating transaction commits, any SELECT operations from concurrent older transactions against the truncated table fail with the error Cannot access table because of concurrent truncate.

In NPS 7.2.0.4 and later, disk storage for a truncated table is freed only when there are no active concurrent transactions that started before the truncate committed. If there are no such concurrent transactions at the time a truncate commits, the storage is freed a few seconds after the commit. Open transactions that started before the TRUNCATE committed can still see the rows in the table from before the TRUNCATE.

For more information about the TRUNCATE command behavior, see the IBM Netezza Database User’s Guide.

Table-oriented zone maps

NPS Release 7.2.1 introduces a new design for organizing zone maps. Zone maps are a feature of the IBM® Netezza® appliances that help to reduce or eliminate unnecessary disk scans by skipping portions of the table that do not contain any rows that match the WHERE restrictions specified in a SQL query. For more information about zone maps, see the IBM Netezza System Administrator’s Guide.

For releases before 7.2.1, Netezza appliances use a column-oriented zone map structure where all zone map statistics for the same column number across all tables are stored together in the dataslice. In Release 7.2.1, Netezza adds the table-oriented zone map feature that organizes the zone maps such that the zone map statistics for all columns from the same table are stored together in the dataslice. Table-oriented zone maps help to improve load and delete operations to the tables, especially in environments that apply table updates incrementally (also called trickle-feed updates). The zone map column statistics are the same, so both offer the query performance advantages for avoiding unnecessary disk scans.

The zone map format is a system-wide setting. For systems that are initialized with 7.2.1, the default is to use table-oriented zone maps. When you upgrade an NPS appliance to Release 7.2.1, the existing column-oriented zone maps are preserved. To use table-oriented zone maps, you use the nzzonemapformat -table command to convert the zone maps. You must pause the system to run the conversion. The conversion process could take from a few minutes to an hour, depending on the amount of data stored on the appliance. Take that time into account when scheduling the service window to perform the conversion to minimize the impact on database users and applications.

Note that if your system uses table-oriented zone maps, you cannot downgrade to a release before 7.2.1 unless you convert the zone maps back to column oriented format using the nzzonemapformat -column command. The downgrade process stops with a message that zone maps must be converted back to column-oriented zone maps if table-oriented zone maps are present. After performing the zone map conversion using the nzzonemapformat utility, run the downgrade again.

Data connectivity clients

NPS Release 7.2.1 introduces the following updates to the data connectivity clients:

  • The JDBC clients are updated for JDK Release 1.7. If your Netezza host is running Release 7.2.1 or later, your JDBC clients require JDK Release 1.7 or later. For configuring the JDBC data source, the Data Source Configuration window includes new fields. The JDBC driver was tested for conformance with Version 4.1 of the Oracle JDBC API specification; unsupported methods are documented.
  • Clients can connect using the Netezza .NET Framework data provider, called NZdotNET. This data provider supports Microsoft .NET Framework 4.5 or later and is integrated with Visual Studio 2012 or later. The NZdotNET .NET Framework data provider is based on the ADO.NET 3.0 data provider specification; unsupported methods and properties are documented. For more information, see the IBM Netezza ODBC, JDBC, OLE DB, and .NET Installation and Configuration Guide.

Client support packages

NPS Release 7.2.1 introduces the following updates to the NPS client support:

  • There is a new NPS client package that contains ODBC and JDBC drivers for Red Hat Enterprise Linux® 7.1 POWER8® Little Endian platforms. Release 7.2.1.1 adds support for the NPS clients on this platform.
  • Release 7.2.1.1 adds support for Windows 2008 platforms for NPS clients, ODBC, JDBC, OLE DB, and .NET.
  • Release 7.2.1.1 adds support for Red Hat Enterprise Linux 7.1 running on POWER8 Little Endian clients.

Data loading changes

NPS Release 7.2.1 introduces the following updates to the data loading support:

  • The nzload command now supports merge operations through the use of three new options:
    • -merge option—Specifies whether to merge the contents of the data file into the target table by inserting rows, updating rows, or both
    • -mergeSchema option—Specifies the schema of the target table and, optionally, a match condition for the -merge option
    • -mergeOn option—Specifies a match condition, a filter, or both for the -merge option
  • The nzsql command can be used to establish a connection before a load from a remote external table or an unload into a remote external table. When creating the external table for the remote load or unload, specify the new NZSQL value (or the equivalent value, YES) for the RemoteSource external table load option.
  • This release enhances the use of delimiters in data loading, as follows:
    • Adds support for the text-delimited format to the RecordDelim external table load option, which previously supported only the fixed-length format. You can use this option to specify a string literal to use as the row or record delimiter. Do not use backslash (\) as a record delimiter; there is a known issue that backslash is not supported for load operations.
    • Adds the LFinString external table load option. You can use this option to specify whether an embedded newline value that is also the record delimiter is treated as real data. This option is not supported for the fixed-length format.
  • There are two new external table load options for changing the default delimiter of a space (' ') in time and timestamp values:
    • MeridianDelim option - Specifies a single-byte character that separates the seconds component from the AM or PM token in the 12-hour delimited and undelimited formats of a time value
    • DateTimeDelim option - Specifies a single-byte character that separates the date and time components of the timestamp data type

Call home changes

In Release 7.2.1, call home has the following changes and enhancements:

  • If your environment uses proxy routing from the NPS appliance through your network to the IBM servers that support call home, you can now configure the proxy server name and port in the callHome.txt file. For more information, see the call home section in the IBM Netezza System Administrator’s Guide.
  • The nzcallhome command now includes a -verifyConnectivity option to test the appliance call home connectivity.
  • Call home now supports the ability for users to customize the time ranges within which events are appended to an existing recent open ticket using de-duplication settings in the callHome.txt file.
  • The call home feature now collects and transmits more log files to help Support diagnose and troubleshoot disk errors.

Database and SQL language

NPS Release 7.2.1 introduces the following updates to the Netezza SQL language and database:

  • The CREATE TABLE command now includes support for the IF NOT EXISTS syntax, which causes the CREATE TABLE command to ignore the error when a table already exists with the specified name. Note that the system does not perform any checks to verify that the table shape and content matches the data in the CREATE TABLE command.
  • The DROP TABLE command now includes support for the IF EXISTS syntax, which causes the DROP TABLE command to ignore the error when a table of the specified name does not exist.
  • The fixed-length format for data loading now supports a configurable record delimiter for text-delimited load and unload using the external table attributes recorddelim and recDelim respectively. If users do not specify -recDelim, the default value is a newline.
  • The MERGE command is now supported for Netezza database loading operations. You can use MERGE to insert new rows or replace matching rows in an existing table based on a match clause that you create. Versioned tables and tables that use row-level security are not supported with MERGE. For more information about the MERGE support, see the IBM Netezza Data Loading Guide.
  • You can now specify a delimiter for date and time values as well as time and meridian values using the external table options DATETIMEDELIM and MERIDIANDELIM.
  • The ALTER USER ... AUTH "LOCAL" is now supported when the system is configured for Kerberos authentication.

IBM Security Key Lifecycle Manager support for AEKs on IBM PureData System for Analytics N3001 appliances

Release 7.2.1 adds the ability for administrators to store the authentication encryption keys for their IBM PureData® System for Analytics N3001 appliances in an IBM Security Key Lifecycle Manager (ISKLM) server. If you auto-lock your N3001 systems and you have ISKLM set up in your environment, you can configure the NPS hosts to store and retrieve the AEKs from ISKLM. ISKLM helps by keeping your AEKs in a secure location away from the N3001 appliance. Note that you cannot generate and rotate the SED authentication key using ISKLM at this time. The ISKLM support allows the storage and retrieval of the keys, but you must manually create and rotate the key per your company's policies using the nzkey generate command. For more information, see the SED section of the IBM Netezza System Administrator’s Guide

Kerberos authentication changes

Release 7.2.1 adds support for NPS administrators to create locally authenticated database users as exception cases when the system is configured to use Kerberos authentication for database user accounts. In previous releases, Kerberos did not support both local and Kerberos authenticated user accounts on the same system.

Commands and system administration

NPS Release 7.2.1 introduces the following updates to the system administration tasks and Netezza command line interfaces:

  • The nzsql command is now available in the Netezza Windows client kit. You can now use the nzsql command from your Windows client to open sessions to the Netezza databases on your appliances.
  • The nzload command now supports -merge and -mergeschema to perform the MERGE operation from a command line, as well as -dateTimeDelim and -meridianDelim for specifying the delimiter between the data and time or between the time and meridian. The command also supports a configurable -recDelim record delimiter for text-delimited load and unload. If users do not specify -recDelim, the default value is a newline.
  • The nzkey, nzkeybackup, nzhostbackup, and nzkeydb commands now support options to manage AEKs stored in a IBM Security Key Lifecycle Manager (ISKLM) server.
  • The nzkmip command is new in this release and is used for migrating a local SED keystore to an ISKLM keystore, and for testing the connections.
  • This release adds support for new 64-bit clients for IBM Spectrum Protect (formerly Tivoli® Storage Manager), EMC NetWorker, and Veritas (formerly Symantec) NetBackup backup and restore connectors.
    Note: Throughout the Netezza topics, the terms Tivoli Storage Manager and TSM are also used to refer to the applications, clients, and features of the IBM Spectrum Protect product family.
  • The nzzonemapformat command is new in this release and converts zone maps from column-oriented format to table-oriented format, or back again, and can show the current zone map format on the system.

System configuration settings

NPS Release 7.2.1 introduces the following updates to the system configuration settings:

  • When a system reaches the maxConnections limit, no new database sessions are allowed on the system. If there is an issue that requires investigation, some commands that open database connections could be blocked. In 7.2.1, the system now allows up to 10 admin user connections to support troubleshooting and recovery tasks that might be required on the system after the user connections limit has been reached.