ALTER GROUP

Use the ALTER GROUP command to change properties of a group.

Use the ALTER GROUP command to:

Add users to a group.
Remove users from a group.
Change the owner of a group.
Rename a group
Change properties of a group, such as its session timeout, rowset limit, query timeout, or default priority.

Syntax

Syntax for modifying a group:

ALTER GROUP name { {ADD | DROP } USER  <user>[,<user>…] | 
  OWNER TO <user> | RENAME TO <new_group_name> |
  WITH <clause> [<clause>…] }

Where <clause> represents one of:

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>…])} |

Where <access-time> represents:

DAY { ALL | <day>[,<day>…] } [ START <time> END <time> ]

Inputs

The ALTER GROUP command takes the following inputs:

Table 1. ALTER GROUP inputs
Input	Description
<name>	The name of the group to be altered.
USER	The user or users who are to be added to or dropped from the group. Dropping a user from a group does not drop the user from the system, but only from the group.
OWNER TO	The new owner of the group.
RENAME TO	The new group name.
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: A number in the range 1 - 2,147,483,647 0 for an unlimited number of rows (this is the default)
SESSIONTIMEOUT	The number of minutes that a session can be idle before the system terminates it. The value can be: A number in the range 1 - 35,791,394 0 for an unlimited number of minutes (this is the default)
QUERYTIMEOUT	The number of minutes that a query can run before the system notifies the administrator. The value can be: A number in the range 1 - 35,791,394 0 for an unlimited number of minutes (this is the default) 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: A number in the range 1 - 35,791,394 0 for an unlimited number of sessions (this is the default) If the setting is 0, a limit might still be imposed by a group of which the user is a member. If the user is a member of several groups, the minimum setting applies.
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 from 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. The integer 0 (or OFF) specifies that the group has no maximum for the number of concurrent jobs. The group is restricted by the usual system settings and controls for concurrent jobs. An integer in the range 1-48 to set the job maximum to the specified integer value. The integer -1 (or AUTOMATIC) specifies that the system calculates a job maximum value that is based on the group's resource minimum multiplied by the number of GRA scheduler slots. For example, if a group has a resource minimum of 20% and there are 48 slots (this is the default), the job maximum is (0.20 * 48) or approximately 9. This parameter can be specified only if the resource minimum of the group is nonzero.
COLLECT HISTORY	Whether the system is to collect history data for the sessions of a user who is a member of this group: ON History is collected only if the user is connected to a database for which COLLECT HISTORY is set to ON. OFF History is not collected for the user. DEFAULT History is collected for the user only if the user is connected to a database for which COLLECT HISTORY is set to ON and if one of the following criteria apply: This user is not a member of any user group. All the user groups of which this user is a member have COLLECT HISTORY set to DEFAULT. This user is a member of at least one user group that has COLLECT HISTORY set to ON.
ALLOW CROSS JOIN	Whether a user who is a member of this group can carry out explicit cross joins: TRUE The user can carry out explicit cross joins. FALSE The user cannot carry out explicit cross joins. NULL The user can carry out explicit cross joins unless the user is a member of one or more user groups for which ALLOW CROSS JOIN is set to FALSE.
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: ALL The user can start sessions at any time and on any day. DEFAULT The combination of the access time settings of the groups of which the user is a member determine when the user can start sessions. For example, if a user is a member of two groups, one of which specifies ACCESS TIME '1,2,3' and the other of which specifies ACCESS TIME '5,6,7', the user can start a session only on Wednesday (day 4). <access_time> Each access time subclause specifies one or more days of the week and one time interval during which the user can start a session. Each day is represented by an SQL day number (1 = Sunday, 7 = Saturday, etc.). The keyword ALL is equivalent to specifying days 1,2,3,4,5,6,7. An access time subclause optionally contains one time bound. If no time bound is specified, then the group can create a session at any time on the specified day.

Outputs

The ALTER GROUP command has the following output

Table 2. ALTER GROUP output
Output	Description
`ALTER GROUP`	The command completed successfully.

Privileges

You must be the admin user, the owner of the group, or your account must have Alter privilege for the group or for the Group object class. If you alter the group owner, you must also have List privilege for the specified user.

Usage

The following provides sample usage:

To add the users karl and john to the group staff:

MYDB.SCH1(USER)=> ALTER GROUP staff ADD USER karl, john;

To change the session idle time of the group staff:

MYDB.SCH1(USER)=> ALTER GROUP staff WITH SESSIONTIMEOUT 300;

To remove the user beth from the group workers:

MYDB.SCH1(USER)=> ALTER GROUP workers DROP USER beth;

To change the maximum priority of the group workers:

MYDB.SCH1(USER)=> ALTER GROUP workers WITH MAXPRIORITY critical;

To change the expiration setting for the group sales_temp to 30 days:
```
MYDB.SCH1(USER)=> ALTER GROUP sales_temp WITH PASSWORDEXPIRY 30;
```