CREATE [OR REPLACE] AGGREGATE
Use the CREATE AGGREGATE command to create a user-defined aggregate. Use CREATE OR REPLACE AGGREGATE to create an aggregate or to update an existing aggregate with new object files, state, return value, memory usage, or logging level.
Synopsis
CREATE [OR REPLACE] AGGREGATE aggregate_name(argument_types)
RETURNS return_type STATE (state_types)
LANGUAGE CPP PARAMETER STYLE NPSGENERIC [FENCED | NOT FENCED]
[MAXIMUM MEMORY mem ] [LOGMASK mask]
[NO DEPENDENCIES| DEPENDENCIES deplibs]
[ TYPE ANY | ANALYTIC | GROUPED] [API VERSION [1 | 2]]
[ENVIRONMENT 'name'='value', 'name'='value']
[EXTERNAL CLASS NAME 'class_name']
[EXTERNAL HOST OBJECT 'host_object_filename']
[EXTERNAL SPU OBJECT 'SPU_object_filename']
Inputs
| Input | Description |
|---|---|
| aggregate_name | Specifies the name of the aggregate that you want to create.
This is the SQL identifier that is used to invoke the aggregate in
a SQL expression. If the aggregate exists, you cannot change the name by using the CREATE OR REPLACE command. For systems that support multiple schemas, you can specify a name in the format schema.aggregate to create an aggregate in a different schema of the current database. You cannot create an aggregate in a different database. |
| argument_types | Specifies a list of fully specified aggregate argument data types. 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. 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. If the aggregate exists, you cannot change the argument type list by using the CREATE OR REPLACE command. You can change some aspects of a UDAs argument types; for example, you can change the size of a string or the precision and scale of a numeric value. You can remove VARARGS from the argument list, or add it to an otherwise empty argument list. |
| 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 aggregator running accumulators. 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. |
| LANGUAGE | Specifies the programming language that is used for the aggregate. The default and only supported value currently is CPP (C++). |
| PARAMETER STYLE | Specifies the parameter style for the aggregate. The default and only valid value is NPSGENERIC. |
| 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. |
| 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. |
| 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. |
| 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 executing. You can specify several comma-separated name-value
pairs. To replace an existing set of one or more environment pairs, you must specify all the environment settings; the command replaces the current list with the list specified in the CREATE OR REPLACE command. |
| 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® 100 and Netezza 1000 models. |
Outputs
| Output | Description |
|---|---|
| CREATE AGGREGATE | The message that the system returns if the command is successful. |
| ERROR: User 'username' is not allowed to create/drop aggregates. | The system returns this message if your user account does not have Create Aggregate privilege. |
| ERROR: Synonym 'name' already exists | The system returns this message if a synonym exists with the name that you specified for the aggregate. |
| ERROR: AggregateCreate: aggregate name already exists with the same arguments | This error is returned when you issue a CREATE AGGREGATE command and an aggregate with the same name and argument type list exists in the database. Use CREATE OR REPLACE AGGREGATE instead. |
| NOTICE: AggregateCreate: existing UDX name(argument_types) differs in size of string/numeric arguments | This message indicates that a UDX exists with the name but has different sizes that are specified for string or numeric arguments. If you did not intend to change the aggregate signature, check the signature and ensure that it is correct. |
| 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 CREATE 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
When you create an aggregate, note that the aggregate’s signature (that is, its name and argument type list) must be unique within its database for systems that support single schemas. For systems that support multiple schemas in a database, the signature must be unique within a schema. No other UDX can have the same name and argument type list in the same database or schema.
You cannot change the aggregate name or the argument type list by using the CREATE OR REPLACE command. You can change some aspects of the argument types; for example, you can change the size of a string or the precision and scale of a numeric value. To change an aggregate name, argument type list, or both, you must drop the aggregate and then create an aggregate with the new name, argument type list, or both.
You cannot replace a user-defined aggregate that is in use in an active query. After the active query transaction completes, the Netezza Performance Server system processes the CREATE OR REPLACE AGGREGATE command to update the aggregate.
Privileges required
You must be the database admin user, own the current database, or own the current schema on systems that support multiple schemas. Other users must have Create Aggregate privilege to use the CREATE AGGREGATE command. Also, if you use CREATE OR REPLACE AGGREGATE to change a UDA, you must have Create Aggregate privilege and Alter privilege for the UDA to change it. To create an unfenced aggregate, you must have the Unfence admin privilege.
When you issue a CREATE AGGREGATE command, 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
Use the CREATE AGGREGATE command to create and become the owner of a new user-defined aggregate. You must create the aggregate C++ files and compile them by using nzudxcompile before you can use this command to register the aggregate with the Netezza Performance Server system.
ERROR: Records trailing string space set to 512 is too small: Bump it up using the environment variable NZ_SPRINGFIELD_SIZE
Usage
- To create the sample PenMax aggregate (described in Create user-defined aggregates):
MYDB.SCHEMA(MYUSER)=> CREATE AGGREGATE PENMAX(INT4) RETURNS INT4 STATE (INT4, INT4) LANGUAGE CPP PARAMETER STYLE NPSGENERIC EXTERNAL CLASS NAME 'CPenMax' EXTERNAL HOST OBJECT '/home/nz/udx_files/penmax.o_x86' EXTERNAL SPU OBJECT '/home/nz/udx_files/penmax.o_spu10';