Expanding the Netezza Performance Server instance and redistributing tables

Deployment options: Netezza Performance Server for Cloud Pak for Data System

Learn how to expand the Netezza Performance Server instance.

Procedure

Important: Ensure that you are familiar with all of the information that is described in Expansion overview and Caveats.

Ensure that no data slice is degraded.
```
nzds -issues
```
Make sure that Netezza Performance Server is in a healthy state. Also, allow regens to complete.
Ensure that no node is in the FAILED state.
```
nzhw -issues
```
If there are failed disks, it is preferable to resolve them before expansion.
If the system is going through rebalance or node failover, wait for the system to come Online before expanding.
Provision the Cloud Pak for Data System hardware nodes by using an automation script.
1. Identify the master node.
  The automated SPU node configuration script must be run from the master node.
```
ap node
```
  Output example:
```
------------------- ----------- --------------- ----------- ----------- 
| Node             | State   | Personality | Monitored | Is Master    | 
------------------- ----------- --------------- ----------- ----------- 
| enclosure1.node1 | ENABLED | CONTROL     | YES       | NO           |
| enclosure1.node2 | ENABLED | CONTROL     | YES       | YES    |
```
  In this example, enclosure1.node2 is the master node.
2. Convert the new UNSET nodes to SPU nodes.
  Download the following tgz file and upload to the system: nps_expansion_icpds1.0-1.0.0.3_release .
3. Copy the tgz file on e1n1 bare metal node and extract contents by using following command:
```
    tar -xzf nps_expansion_icpds1.0-1.0.0.3_release.tgz -C /tmp/
```
4. Run the command on the master node to set up enclosure 5 and 6 to SPU nodes.
```
/opt/ibm/appliance/platform/xcat/scripts/xcat/automation_script/ips_add_spu_nodes -n e{5..6}n{1..4}
```
  This setup process takes 45 - 60 minutes, depending on system size.
  
  When the configuration is completed, the SPU nodes are shut down.
Run table row counts to validate the data.
```
nz_db_tables_rowcount
```
For online redistribution, see Online redistribution
Starting from version 11.2.1.1, nzredr is modified for more accurate estimates and improved redistribution performance. The updated nzredr command skips tables that are distributed on random that satisfy both of the following criteria:
- The number of extents on the largest data slice is no more than NZREDR_RANDOM_DIST_THRESH1. The default is one extent).
- The total number of extents is no larger than NZREDR_RANDOM_DIST_THRESH2.
The threshold values must be passed in the environment with the nzredrexpand command.
Example: Skip the redistribution of randomly distributed tables with 2 or fewer extents on the largest slice.
```
export NZREDR_RANDOM_DIST_THRESH1=2 
export NZREDR_RANDOM_DIST_THRESH2=768 
nzredrexpand
```
When expansion needs to be resumed.
```
export NZREDR_RANDOM_DIST_THRESH1=2 
export NZREDR_RANDOM_DIST_THRESH2=768 
nzredrexpand --resume
```
Expand the instance and redistribute tables.
```
nzredrexpand
```
You are suggested to run nzredrexpand through a screen session that is started on the host. The tool outputs progress and status messages to both the console and to the log file, nzredrexpand.log. The same information is written to both the console and the log file (duplicated).

Note: For Netezza Performance Server 11.2.1.11 and later, use nzredrexpand --full instead of the above command.

During the topology expand phase, you can monitor the sysmgr log or use nzpush -a status -a to monitor the progress.
For more information about the process, see Expansion overview.

Online redistribution

Note: Make sure to add the hardware nodes as SPUs.

Note: Expect reduced performance when redistribution is active, restrict the number of users to limit the performance degradation.

Set up a scheduler rule to limit the resources used by the redistribution.
To redistribute the data, run:
```
/nz/support/bin/nz_redistribute
```
Different options are available to manage the impact on the workload:
- All databases and tables: Run the command with no arguments.
```
nz_redistribute -run
```
  Note: The tables are redistributed in alphabetical order (smaller tables first).
- Critical databases first: Run the command multiple times.
```
nz_redistribute -run <DBNAME>
```
  Upon completion, run the command to redistribute the tables in the remaining databases.
```
nz_redistribute -run
```
- Critical tables first: Run the command multiple times followed by 'All databases and tables' or 'Critical databases first' to cover the remaining tables.
```
nz_redistribute -run <DBNAME>[ -schemas <SCHEMALIST> ] [ -in | -NOTin | -like | -NOTlike <TABLE-LIST-OR-PATTERN> ]
```
For verification of the data redistribution completeness, run:
```
nz_redistribute
```
- No remaining tables to redistribute: Redistribution is now complete. Revoke any imposed limitations on the workload.
- One or more tables still need redistribution: Schedule downtime for the offline redistribution of the pending tables.
  Note: The downtime can be avoided by running the command once, for the remaining tables.
```
nz_redistribute -run
```
  Disk space limitations can lead to failure of redistribution.
- For the remaining tables to redistribute, stop all database activity and run:
```
/nz/kit/share/install/scripts/nzredrexpand --redistributeonly
```
Continue with Post-Expansion after successful completion of data redistribution.
Note: Contact IBM support for any redistribution failure.
Run table row counts to validate the data again:
```
nz_db_tables_rowcount
```
For troubleshooting, see Failures and troubleshooting.

The nz_redistribute Command - Online redistribute

nz_redistribute’ is a script that resides in /nz/support/bin (on the Netezza Performance Server host container; on a client machine, it would be wherever the Netezza Performance Server support tools are installed). It can be used to redistribute tables after expansion across all databases or in one database, with options for limiting the tables redistributed within a database by schema or by table name pattern. This is the main use of nz_redistribute, and requires that it be invoked with `-run` on the command line. Without any other arguments, nz_redistribute -run will process all databases in alphabetical order. Within each database, tables across all schemas will be redistributed in increasing order of table size. Finer control over which databases, schemas, and tables are redistributed is available via the following optional arguments. The complete description of nz_redistribute syntax and semantics can be obtained by running `nz_redistribute -h`.

Other arguments and options

Table 1. Other arguments and options
Arguments	Description
SpaceEstimate <number of data slices>	Provides the required estimated space to redistribute all the tables. `<number of data slices>` is the number of data slices after expansion.
neither -run nor -SpaceEstimate specified	Provide a report of tables needing redistribution, without performing any actual redistribution.
-status [<seconds>]	Only available with -run. While processing larger tables, the script will automatically display status information every <seconds> seconds (if an individual table takes more than <seconds> seconds to be redistributed). The default value of <seconds>, if none is specified, is 60.
-check	Only available with -run. Add this option if you want additional sanity checks (e.g., more SQL) run as part of the data redistribution. This triggers 2 additional scans of the table in order to verify that the # of rows before redistribution matches the number of rows after redistribution. If the numbers don't match up then all changes that were made to that table will be rolled back. In general, this option should not be needed as everything will already be rolled back if any individual step were to fail. These additional checks increase the overall runtime of the script by roughly 15%.

“First-time” nz_redistribute processing

The first time `nz_redistribute -run`, is invoked after an NPS expansion, whether for all databases, schemas, and tables or a subset, it performs the following common actions (across all databases):

GROOM VERSIONS against all ALTERed / versioned tables, so that each of them now has a single physical version in storage. This should generally be performed in Preparing to expand Cloud Pak for Data System.
Suspend active materialized views. This will free up additional storage, and reduce the amount of time it takes to redistribute the base table. A script will be generated, that can later be run, allowing to easily refresh the affected materialized views.
Enables that meet either of the following criteria are not actually “redistributed”, but are immediately marked with the new (expanded) number of data slices so that they are not examined again and do not count as “needing redistribution”:
- The table is empty (there are no rows to redistribute).
- The table has RANDOM distribution and has 250,000 rows or less and is currently distributed over 10 or fewer dataslices.
- These thresholds can be overridden on the command line using the ‘-random_rows’ and ‘-random_slices’ arguments.

Planning and Scheduling Online Redistribution

The -db (optional, if the database name is the first argument), -schemas, and table filtering command-line options of nz_redistribute provide considerable flexibility in choosing when to redistribute which databases, schemas, and tables. As noted earlier, nz_redistribute -run with no additional arguments will redistribute all tables (that need redistribution) in all schemas in all databases. The default order is to iterate through all databases in alphabetical order, and within each database redistribute all tables in increasing size order (unless -alpha was specified, to redistribute tables within a database by schema.tablename order). The customer may selectively want to redistribute some databases, schemas, and tables before others, and can do that by running nz_redistribute at different times (concurrent invocation is not recommended) with different arguments. For example,

One or more nz_redistribute -run [-db] <dbname> commands to process more critical databases before others.
Within a database, -schemas <schema1> [<schema2> …] to process more critical schemas before others.
Within a database (and optionally within one or more schemas using -schemas), one or more commands with { -in | -NOTin | -like | -NOTlike } <TABLE-OR-PATTERN-LIST> to process more critical tables before others. “Critical” here could be based on which tables are needed by which workloads, and/or table size (e.g., if the customer wishes to redistribute some very large tables sooner rather than later).

In all cases, the user can follow a more narrowly-specified command, e.g., nz_redistribute -run -db <dbname>, with a more broadly-specified one, e.g., nz_redistribute -run -db <dbname>, to complete the redistribution of tables that were not already redistributed by the earlier command. Ultimately, when the user is satisfied that they have redistributed the tables that are considered the most important, they must invoke nz_redistribute -run with no additional arguments (unless this was the first invocation of nz_redistribute and it ran to completion) to redistribute remaining tables if any that have not already been redistributed, and for nz_redistribute to record completion of redistribution across the system as a whole (see view _v_dslice_counts).

Note: The only exception to nz_redistribute -run to redistribute remaining tables is if the user specifically wants these remaining tables to be redistribute “offline”. If nz_redistribute reports that some tables are too large to be redistributed online, then offline redistribution will be needed to complete redistribution of those tables.

Concurrent invocation of nz_redistribute

An invocation of nz_redistribute -run will only redistribute (and lock) one table at a time. Multiple table redistribution can be performed in parallel, by with multiple parallel invocations of nz_redistribute -run, with different arguments.

Note:

You should try to avoid multiple invocations trying to redistribute the same table. They could run parallel redistribution for different databases, for example, or for different subsets of tables within a database, using the available database, schema, and table name filters. Nonetheless, if concurrent invocations of nz_redistribute find a common table that needs redistribution, only the one that gets to that table first will actually perform redistribution of the rows.
Multiple invocations of nz_redistribute will use more system resources than a single invocation, and should generally be restricted to times when there is little or no customer workload running on the system.
Multiple invocations of nz_redistribute will also use more free disk space, in order to copy rows for more than one table at the same time. Care should be taken to not redistribute two or more very large (relative to available disk space) tables at the same time, as they could exhaust disk space and fail when they may have succeeded if redistributed one at a time.

Monitoring, Failure Handling, Logging, and Restarting

Other than optional -status output from nz_redistribute -run itself, there is no facility provided for running invocations of monitoring nz_redistribute. These can be identified using the shell ps command, and investigated or manipulated if needed (e.g., to ‘kill’ a/the running invocation to free up system resources). Running queries from nz_redistribute can be monitored using standard Netezza Performance Server query monitoring tools.

Terminate using ctrl-C, ‘kill’, etc. Current table redistribution is interrupted and rolled back.

There is no particular “restart” capability, one would generally just reissue the interrupted or failed nz_redistribute command with the same arguments (unless you want to run it with different arguments).

Using SQL to Check Whether Tables Need Redistribution

The same capabilities are available via SQL queries against the following new public views:

The new view _v_dslice_counts will have columns:

Table 2. Public views
CURR_DSLICE_COUNT	The current (post-expansion, if there was an expansion) data slices in the system
LAST_DSLICE_COUNT	The pre-expansion number of data slices, if any tables are still in need of redistribution, otherwise (if all tables have been redistributed, or there never was an expansion) the same as CURR_DSLICE_COUNT
REDISTRIBUTE_NEEDED	False if LAST_DSLICE_COUNT equals CURR_DSLICE_COUNT; True if they differ, i.e., there is at least one table in need of redistribution

The new view _v_tables_to_redist has the same columns as _v_tables, plus a new dslicecount column. This view only shows those rows from _v_table representing tables in the current database that have not been redistributed.

Overview - nz_redistribute

As noted above, invoking nz_redistribute with neither -run nor -SpaceEstimate will report tables that are in need of redistribution. The scope of this report will be

The entire system, if no database argument is specified on the command line.
A single database, if database argument is specified.

Running redistribution of tables is one of three action arguments that can be given to nz_redistribute (other arguments will be described presently). More detailed explanation of these and other command-line options are available in nz_redistribute commands

Action	Description
-SpaceEstimate [<number of data slices>]	This command option was described in Preparing to expand Cloud Pak for Data System to provide input to Redistribution methods. It reports whether there is sufficient disk space to redistribute all tables online. When run prior to expansion, at which time Netezza Performance Server has only the pre-expansion number of data slices, the expanded number of data slices must be provided as an explicit argument. Disk space estimation can also be run after expansion. In this case, no argument is required, as nz_redistribute is able to determine the original number of data slices from expansion meta-data that is saved.
[default action: neither -run nor -SpaceEstimate specified] [<optional selection criteria>]	Provide a report of tables needing redistribution, without performing any actual redistribution.
-run [<optional selection criteria>] [-check] [-status <seconds>]	Redistribute all or a subset of tables. Only available with -run. Add this option if you want additional sanity checks (e.g., more SQL) run as part of the data redistribution. This triggers 2 additional scans of the table in order to verify that the # of rows before redistribution matches the number of rows after redistribution. If the numbers don't match up then all changes that were made to that table will be rolled back. In general, this option should not be needed as everything will already be rolled back if any individual step were to fail. These additional checks increase the overall runtime of the script by roughly 15%. Only available with -run. While processing larger tables, the script will automatically display status information every <seconds> seconds (if an individual table takes more than <seconds> seconds to be redistributed). The default value of <seconds>, if none is specified, is 60.

Action

Description

-SpaceEstimate [<number of data slices>]

This command option was described in Preparing to expand Cloud Pak for Data System to provide input to Redistribution methods. It reports whether there is sufficient disk space to redistribute all tables online. When run prior to expansion, at which time Netezza Performance Server has only the pre-expansion number of data slices, the expanded number of data slices must be provided as an explicit argument.

Disk space estimation can also be run after expansion. In this case, no argument is required, as nz_redistribute is able to determine the original number of data slices from expansion meta-data that is saved.

[default action: neither -run nor -SpaceEstimate specified] [<optional selection criteria>]

Provide a report of tables needing redistribution, without performing any actual redistribution.

-run [<optional selection criteria>]

[-check]

[-status <seconds>]

Redistribute all or a subset of tables.

Only available with -run. Add this option if you want additional sanity checks (e.g., more SQL) run as part of the data redistribution. This triggers 2 additional scans of the table in order to verify that the # of rows before redistribution matches the number of rows after redistribution. If the numbers don't match up then all changes that were made to that table will be rolled back. In general, this option should not be needed as everything will already be rolled back if any individual step were to fail. These additional checks increase the overall runtime of the script by roughly 15%.

Only available with -run. While processing larger tables, the script will automatically display status information every <seconds> seconds (if an individual table takes more than <seconds> seconds to be redistributed). The default value of <seconds>, if none is specified, is 60.

The optional selection criteria that can be specified are:

Table 3. Optional selection criteria
Criterion	Description
[-db] <database>
-schemas <schema1> …
{-in \| -notin} <table1> …
{-like \| -notlike} <pattern1> …

Note: By carefully choosing selection criteria, you can redistribute tables in an order different from the default (for -run with no selection criteria). Each invocation of nz_redistribute can be performed at a time of your choosing.