CREATE GROUP
Use the CREATE GROUP command to create a group.
A Netezza Performance Server group
can be one or both of the following types:
- User group
- A group with one or more members is a user group. Each member of a user group inherits its privileges and other settings, with the exception of its resource minimum, resource maximum, and job maximum settings. User groups are used to simplify access management.
- Resource group
- A group that specifies a nonzero minimum resource percentage is a resource group. Each resource group also specifies a resource maximum and job maximum, either explicitly or by default. These three settings are called the group's resource settings. Each user is assigned to exactly one resource group. Resource groups are used for workload management.
- A user might be assigned to a resource group but not be a member of that group. That user is unaffected by any privileges or settings of that group, except for the resource settings.
- A user might be a member of a user group but be assigned to a different resource group. That user is unaffected by the user group's resource settings.
Syntax
Syntax for creating
a group:
CREATE GROUP name [WITH <clause> [<clause>…] ]Where
<clause> represents
one of:SYSID <groupid> |
DEFPRIORITY {CRITICAL|HIGH|NORMAL|LOW|NONE} |
MAXPRIORITY {CRITICAL|HIGH|NORMAL|LOW|NONE} |
ROWSETLIMIT <rslimit> |
SESSIONTIMEOUT <sessiontimeout> |
QUERYTIMEOUT <querytimeout> |
CONCURRENT SESSIONS <concsessions> |
RESOURCE MINIMUM <min_percent> |
RESOURCE MAXIMUM <max_percent> |
JOB MAXIMUM <jobmax> |
COLLECT HISTORY {ON|OFF|DEFAULT} |
ALLOW CROSS JOIN {TRUE|FALSE|NULL} |
PASSWORDEXPIRY <days>
ACCESS TIME {ALL|DEFAULT|(<access_time>[,<access_time>…])} |
USER <user>[,<user>…]
Where
<access-time> represents:DAY { ALL | <day>[,<day>…] } [ START <time> END <time> ]Inputs
The CREATE GROUP command takes the following inputs:
| Input | Description |
|---|---|
| <name> | The name of the group to be created. This name must be unique among global objects, that is, it cannot be shared by another group, or by any database, user, or scheduler rule. |
| SYSID | The ID to be associated with the new group.
The default is the larger of:
|
| DEFPRIORITY | The default priority for queries submitted by the users who are members of the group. |
| MAXPRIORITY | The maximum priority for a query submitted by the users who are members of the group. |
| ROWSETLIMIT | The maximum number of rows that a query made
by a user who is a member of the group can return. The value can be:
|
| SESSIONTIMEOUT | The number of minutes that a session can be
idle before the system terminates it. The value can be:
|
| QUERYTIMEOUT | The number of minutes that a query can run before
the system notifies the administrator. The value can be:
For the administrator to receive the issued message, the RunAwayQuery event rule must be enabled. This rule is described in the IBM® Netezza® System Administrator’s Guide. |
| CONCURRENT SESSIONS | The maximum number of concurrent sessions a
member of this group can have. The value can be:
|
| RESOURCE MINIMUM | The smallest percentage of net system resources that are to be made available to the group. The value can be from 0 - 100. The default is 0. A group that has a resource minimum that is greater than 0 is called a resource group. This parameter replaces the deprecated RESOURCELIMIT setting. |
| RESOURCE MAXIMUM | The largest percentage of net system resources that the resource group is to receive, regardless of whether other resource groups are using the system. The value can be in the range 1 - 100. The default is 100. This parameter can be specified only if the resource minimum of the group is nonzero. |
| JOB MAXIMUM | The maximum number of concurrent jobs that can be run by the
resource group. Additional jobs are queued.
|
| COLLECT HISTORY | Whether the system is to collect history data
for the sessions of a user who is a member of this group:
|
| ALLOW CROSS JOIN | Whether a user who is a member of this group
can carry out explicit cross joins:
|
| PASSWORDEXPIRY | For each user account assigned to this group,
the number of days that the account password is valid. If you do not
specify the option, or if you specify a value of 0, the password does
not expire. After the password expires, the database displays the
message Restricted Session. User password has expired the
next time that the user logs in to the database. The user must change
the account password such as by using the ALTER USER command to regain
full capabilities. The expiration is used for locally authenticated accounts. The system checks and uses the expiration specified for the user account first, then the lowest value specified for any group in which the user is a member, and lastly the system-wide expiration if specified. |
| ACCESS TIME | When a user who is a member of this group is
allowed to start sessions on the system:
|
| USER | The user or users who are to be the members of this group. Specifying one or more members makes this group a user group. If this parameter is specified, it must be the last parameter in the SQL command. |
Output
The command has the following output:
| Output | Description |
|---|---|
| CREATE GROUP | The command completed successfully. |
Privileges
You must be the admin user or your account must have the Create Group privilege.
Usage
The following provides
sample usage.
- Create an empty group:
MYDB.SCH1(USER)=> CREATE GROUP staff; - Create a group that specifies a password expiration rate of 45
days:
MYDB.SCH1(USER)=> CREATE GROUP staff WITH PASSWORDEXPIRY 45; - Create a user group, that is, a group with members:
MYDB.SCH1(USER)=> CREATE GROUP marketing WITH USER jonathan,david; - Set the maximum priority for a user group:
MYDB.SCH1(USER)=> CREATE GROUP workers WITH MAXPRIORITY critical; - Create a resource group, that is, a group with a nonzero resource
minimum:
MYDB.SCH1(USER)=> CREATE GROUP rg1 WITH RESOURCE MINIMUM 30;