Release 7.1.0.0 includes the following changes to the database
operations and SQL language syntax and commands.
- Added new SQL commands [CREATE | ALTER | SET | DROP | SHOW ] SCHEDULER
RULE to manage the scheduler rules and a new privilege Schedule Rule
to grant users permission to manage the scheduler rules.
- For stored procedures, added new EXCEPTION clause for TRANSACTION
ABORT to specify the statements to run when a stored procedure fails
with a transaction abort error.
- Added the DSA_KEYPAIR_2048 data type to create crypto keys that
support the enhanced SP 800-131a cryptography, and that can be used
to digitally sign audit history configurations that are configured
for use in an SP 800-131a environment.
- Added a snippet results cache on the SPUs, which can help to improve
the performance of small queries. The snippet results cache saves
the intermediate results of snippets and can reuse them rather than
incur the processing time to recompute them when needed. The cache
can help to improve the performance of small queries.
- Updated the documentation for the SET AUTHENTICATION command with
examples and information for using the IBM® Tivoli® Directory Server as an
LDAP server for Netezza® database
user account authentication. Netezza was
tested with IBM Tivoli Directory Server, OpenLDAP, and Microsoft Active Directory.
The LDAP authentication should work with any server that conforms
to the LDAP protocol.
- Improved the DROP DATABASE command to drop a database that contains
up to 260,000 objects. In release 7.0.4, the command added a warning
to notify users when a database could not be dropped because it had
more that 64,000 objects. In Release 7.1.0.0 that maximum has increased
to 260,000. If a database has more than 260,000 objects, the DROP
DATABASE command displays an error similar to the following message:
ERROR: DROP DATABASE: Database "MYDB" has 269968 tables and/or sequences.
Objects must be manually dropped until the number is less than 260000.
To
drop a database with more than 260,000 objects, you must manually
drop objects in the database until the number is below 260,000, and
then you can use the DROP DATABASE command to drop the database.
- Improved the transaction array processing to increase the limit
on the number of table and sequence IDs that are in use from 65,534
to 262, 142. This can help to reduced cases where transactions on
busy systems could fail with ERROR: … no more space for TXMgr
tables array.
- Added a warning message to contact IBM Netezza Support
in the pg.log file when the system is low on
Postgres transaction IDs.
- Added support for IBM DB2® 24:00:00 time support in the Netezza time and timestamp
data types. With this change, users can now load this format using
INSERT or external table loads, unload the format using external tables,
and they can use this form in the time or timestamp values of SELECT
queries. The ability to support this format is through the postgresql.conf setting
enable_time24, which is a Boolean value. The default is false which
does not allow 24:00:00 in the Netezza database or
operations. Users must add the value to the postgresql.conf file
with a value of true (and restart the NPS® software)
to enable the 24-hour time support. JDBC does not support the 24:00:00
format. Instead, JDBC uses the value 23:59:59:999999 to 00:00:00 of
the next day.
- Added an nzsql \dO <table> option
that displays the columns of the table sorted in alphabetical order.
- Changed the output of the \d option for an external
table to support NULL values in the catalog for certain fields. For
Boolean fields that had false as a default value, note that the output
now shows the default value as NULL.
- Release 7.1.0.1 adds support to allow the admin user to restrict
and manage the locations on the Netezza appliance where
users can create external tables. By default, users can create external
locations in any directory on the host. The admin user or any database
user who has been granted Manage System privilege can then define
external table locations on the system where users who have Create
External Table privilege can write the external data object file.
This release adds the SQL commands [ADD | REMOVE | SHOW] EXTERNAL
TABLE LOCATION commands to establish and manage the external table
locations for any new external tables created on the system.
Object ID allocation improvements
Release
7.1.0.0 improves the allocation and assignment of object IDs (OIDs)
for user-created objects. These improvements make the rate of consumption
for the OID values more efficient, and they support the ability to
wrap to the beginning of the OID range when a system reaches the maximum
OID value. The IBM Netezza system
supports up to two billion OIDs, and although this range supports
a majority of environments, it is a rare but possible case that a
system could reach the OID maximum value, which causes the system
to stop.
In previous releases, users could back up their databases,
reinitialize the system, and reload their data to restart the OID
values at the beginning of the range. With release 7.1.0.0, the system
consumes OIDs less frequently than in previous releases, which reduces
the overall OID usage rate. When a system reaches the OID limit, the
system automatically wraps to the beginning of the OID range and starts
to allocate the unused OIDs in the range. After the OID counters wrap,
there is a small overhead to verify that an OID is not in use, but
the effect should not be noticeable. If there is an impact in your
environment, you could perform the back up, reinitialization, and
reload operation to start over and avoid the used OID checks.
Query history database version update
In
release 7.1.0.0, the query history configuration version incremented
from 2 to 3. The version 3 history tables and views have updates to
support the optional client information fields for sessions to the
database and to the scheduler rules that can be applied to queries.
If you upgrade to release 7.1.0.0 or later and your system has a version
1 or version 2 history database configured, you can continue to log
data to that history database, but the information could be missing
key details that are related to the 7.1.0.0 fields. After you upgrade
the software, create a new version 3 history database and create new
history configurations to load data to the new database. For more
information about the version 3 history views and configuration process,
see the IBM Netezza System Administrator’s
Guide.
IBM PureData System for Analytics, Version 7.1