ALTER AGGREGATE

Use the ALTER AGGREGATE command to change the aggregate object files, state, return value, memory usage options, or logging level.

The aggregate must be defined in the current database. You can also use this command to change the owner of the UDA. You cannot change the aggregate name or argument type list by using this command. To change an aggregate name, argument type list, or both, you must drop the aggregate and create an aggregate with the new name, argument type list, or both.

Synopsis

The ALTER AGGREGATE command has the following syntax:

ALTER AGGREGATE aggregate_name(argument_types)
[RETURNS return_type] [STATE (state_types)]
[FENCED | NOT FENCED] [MAXIMUM MEMORY mem]
[LOGMASK mask] [TYPE ANY | ANALYTIC | GROUPED]
[NO DEPENDENCIES| DEPENDENCIES deplibs]
[API VERSION [ 1 | 2 ] ]
[NO ENVIRONMENT | ENVIRONMENT 'name' = 'value' , 'name2' = 'value2' ]
[EXTERNAL CLASS NAME 'class_name']
[EXTERNAL HOST OBJECT 'host_object_filename']
[EXTERNAL SPU OBJECT 'SPU_object_filename']
ALTER aggregate_name(argument_types) OWNER TO name

Inputs

The ALTER AGGREGATE command takes the following inputs:

Table 1. ALTER AGGREGATE inputs
Input	Description
`aggregate_name`	The name of the aggregate that you want to change. You cannot change the aggregate name by using this command. The aggregate must be in the current database. For systems that support multiple schemas, you can specify a name in the format schema.aggregate to change a UDA in a different schema of the current database. You cannot change an aggregate in a different database.
`argument_types`	A list of fully specified arguments and types to uniquely identify the aggregate. You can also specify the VARARGS value to create a variable argument aggregate where users can input up to 64 values of any supported data type. VARARGS is a mutually exclusive value; you cannot specify any other arguments in the list. You cannot change the argument list or sizes. You can remove VARARGS from the argument list, or add it to an otherwise empty argument list. You cannot change the argument list by using this command. All Netezza Performance Server data types are supported. Strings must include either a size or ANY for generic sizes. NUMERIC types must include precision and scale or ANY for generic sizes.
RETURNS `return_type`	Specifies the aggregate return value as one fully specified argument and type. All Netezza Performance Server data types are supported. Strings must include a size and NUMERIC types must include precision and scale.
STATE `state_types`	Specifies a list of fully specified state data types, which cannot be empty. All Netezza Performance Server data types are supported. Strings must include a size and NUMERIC types must include precision and scale. These data items serve as the running accumulators for the aggregator. This aggregation state is maintained outside of the internal state of the aggregation implementation class by the Netezza Performance Server system for various efficiency reasons.
FENCED NOT FENCED	Specifies whether the aggregate is executed in a separate process in protected address space (fenced mode). To create an unfenced aggregate, you must have the Unfence admin privilege.
MAXIMUM MEMORY	Specifies an indication of the potential memory use of the aggregate. The size value can be an empty value or a value in the form of a number and the letters b (bytes), k (kilobytes), m (megabytes), or g (gigabytes). For example, valid values can be '0', '1k', '100k', '1g', or '10m'. The default is 0.
LOGMASK `mask`	Specifies the logging control level for the aggregate. Valid values are NONE, DEBUG, and TRACE, or a comma-separated combination of DEBUG and TRACE.
TYPE	The context in which the UDA can be called. Specify ANALYTIC if the UDA is allowed only for window aggregates, GROUPED if the UDA is allowed in grouped or grand aggregates, or ANY if the UDA is allowed in both contexts. For more information about windowing, see the IBM Netezza Database User’s Guide.
DEPENDENCIES `deplibs`	Specifies an optional list of user-defined shared library dependencies for the UDX. You can specify one or a comma-separated list of library names.
NO DEPENDENCIES	Specifies that there are no dependencies for the UDX, which is the default if DEPENDENCIES `deplibs` is omitted. You can use this option to clear any previous dependencies declared for the UDX.
API VERSION [1 \| 2]	Specifies the version of the UDX interface that is used by the aggregate. The API VERSION must match the compiled version of the object files for the host and SPU. The default is 1. If you include version 2 compiled objects, you must specify API VERSION 2.
ENVIRONMENT	Specifies a name-value pair that is available to the aggregate when it executes. You can specify several comma-separated name-value pairs.
NO ENVIRONMENT	To alter an existing set of one or more environment pairs, you must specify all the environment settings; the alter command replaces the current list with the list specified in the ALTER command. To clear the environment list, specify NO ENVIRONMENT.
EXTERNAL CLASS NAME '`class_name`'	Specifies the name of the C++ class that implements the aggregate. The class must derive from the Uda base class and must implement a static method that instantiates an instance of the class.
EXTERNAL HOST OBJECT '`host_object_filename`'	Specifies the path name to the compiled object for host execution.
EXTERNAL SPU OBJECT '`SPU_object_filename`'	Specifies the path name for the compiled object file of the Linux® SPU. Specify the spu10 compiled object for Rev10 SPUs on IBM® Netezza® 1000 and Netezza 100 models.

Outputs

The ALTER AGGREGATE command has the following outputs:

Table 2. ALTER AGGREGATE outputs
Output	Description
`ALTER AGGREGATE`	The message that the system returns if the command is successful.
`Error: AlterAggregate: existing UDX name(argument_types) differs in size of string/numeric arguments`	This error indicates that a UDX exists with the name but has different sizes that are specified for string or numeric arguments. To alter the aggregate, make sure that you specify the exact argument type list with correct sizes.
`ERROR: lookupLibrary: library libname does not exist`	The message that the system returns if it cannot find the user-defined shared library that is specified as a dependency.
`ERROR: Version mismatch for function udx_name. Specified version 2, but provided version 1 object file`	The compiled object files use API version 1 support, but the SQL command uses version 2 functionality. You must either create version 2 compiled objects, or remove options in the ALTER command that specify version 2 features.
`ERROR: Version mismatch for function udx_name. Specified version 1, but provided version 2 object file`	The compiled object files use API version 2 support, but the SQL command uses version 1 functionality. You must either specify version 1 compiled objects, or change the ALTER command to specify version 2 syntax.
`ERROR: Environment names can't be empty`	The `name` value of an environment setting cannot be an empty string.
`ERROR: type 'type' is not yet defined`	The specified return type is not a known Netezza Performance Server data type.

Description

You cannot alter a user-defined aggregate that is in use in an active query. After the active transaction completes, the Netezza Performance Server system processes the ALTER AGGREGATE command to update the aggregate.

Privileges required

To alter a UDA, you must meet one of the following criteria:

You must have the Alter privilege on the AGGREGATE object.
You must have the Alter privilege on the specific UDA object.
You must own the UDA.
You must be the database admin user, own the current database, or own the current schema for systems that support multiple schemas.
To alter an aggregate to be unfenced, you must have the Unfence admin privilege.

Note: When you issue an ALTER AGGREGATE command and specify new object files, the database processes the HOST OBJECT and the SPU OBJECT files as the user nz. The user nz must have read access to the object files and read and execute access to every directory in the path from the root to the object file.

Common tasks

You can use the ALTER AGGREGATE command to change the owner of an aggregate. Make sure that you specify the full signature of the aggregate (name and argument type list) as follows:

ALTER AGGREGATE aggregate_name(argument_types) OWNER TO name

Usage

The following provides sample usage.

To change the message logging to DEBUG level on a sample aggregate mycalc(int4) that is in the test schema, enter:
```
DEV.SCHEMA(MYUSER)=> ALTER AGGREGATE test.mycalc(int4) LOGMASK DEBUG;
```