IBM PureData System for Analytics, Version 7.1

ALTER DATABASE

Use the ALTER DATABASE command to change properties of a database; for example, the default character set, the name, the default schema, the owner of the database, or whether history data is to be collected for the database.

Syntax

Syntax for altering a database:

ALTER DATABASE <db_name> [ OWNER TO <user> ] [ RENAME TO <new_db_name> ] 
  [ SET DEFAULT SCHEMA <schema_name> ] [ SET DEFAULT CHARACTER SET LATIN9 ] ]
  [ COLLECT HISTORY {ON|OFF|DEFAULT} ]

Inputs

The ALTER DATABASE command has the following inputs:

Table 1. ALTER DATABASE inputs
Input	Description
<db_name>	The name of the database.
OWNER TO	The name of the new database owner.
RENAME TO	The new database name.
SET DEFAULT SCHEMA	The default schema for users who connect to the database. If you change the default schema, users who inherited access to the previous default schema lose access to that schema and inherit access to the new schema. There users no longer have access to the previous default schema unless they were explicitly granted access to it.
SET DEFAULT CHARACTER SET	If you upgraded before IBM® Netezza® release 2.2, the default character set is set to UNDECLARED. Set the default character set to LATIN9 to be able to compare, join, or cast char or nchar class data.
COLLECT HISTORY	Whether history data is to be collected for sessions attached to this database. ON History is collected only if the user is a member of at least one group for which COLLECT HISTORY is set to ON. OFF History is not collected for the database. DEFAULT History is collected for the database only if the user is a member of at least one group for which COLLECT HISTORY is set to ON and if one of the following criteria apply: The user is not a member of any user group. All the user groups of which the user is a member have COLLECT HISTORY set to DEFAULT. The user is a member of at least one user group that has COLLECT HISTORY set to ON.

Outputs

The ALTER DATABASE command has the following outputs:

Table 2. ALTER DATABASE outputs
Output	Description
`ALTER DATABASE`	The command completed successfully.
`ERROR: ALTER DATABASE: database "db1" is being accessed by other users`	The database is being used by other users.

Privileges

You must be the admin user, the database owner, or your account must have the Alter privilege for the database or for the Database object class. To change the database owner or the default schema, you must have List privilege for that user or schema.

Usage

The following provide sample usage:

Change the default character set for the emp database:

MYDB.SCH1(USER)=> ALTER DATABASE emp SET DEFAULT 
CHARACTER SET LATIN9;

Change the owner of the emp database:

MYDB.SCH1(USER)=> ALTER DATABASE emp OWNER TO admin3;

Rename the emp database:

MYDB.SCH1(USER)=> ALTER DATABASE emp RENAME TO employees;