ALTER DATABASE

Use the ALTER DATABASE command to change the 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} ] 
  [ DATA_VERSION_RETENTION_TIME <number-of-days> {NOCASCADE | CASCADE} ]
  [ GROOM BACKUPSET {<backupset> | 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 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 that are 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 applies:
  • 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.
DATA_VERSION_RETENTION_TIME

Alters the retention time on a database. Specify DATA_VERSION_RETENTION_TIME if you want to run time travel queries.

The maximum allowed value is 99 days.

With the NOCASCADE option specified, the command updates the value of DATA_VERSION_RETENTION_TIME for the specified database.

The new value has no impact on existing schemas and tables in the database. But, the value determines the default value that is inherited by a subsequent CREATE SCHEMA command in the database that does not explicitly specify this property.

With the CASCADE option specified, the command updates the value of DATA_VERSION_RETENTION_TIME for the specified database, and the retention time intervals of all schemas and tables in the database are updated.

For more information about time travel and DATA_VERSION_RETENTION_TIME, see Getting started with time travel.

GROOM BACKUPSET

If set to DEFAULT, the GROOM TABLE command automatically determines the default backup set for the table's database.

If set to a specific backupsetid string, GROOM TABLE uses the specified backup set as the default backup set for the table's database.

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;
  • Change a temporal database to a nontemporal database:
    ALTER DATABASE DB1 DATA_VERSION_RETENTION_TIME 0 CASCADE;
  • Change a nontemporal database to a temporal database:
    ALTER DATABASE DB1 DATA_VERSION_RETENTION_TIME 30 NOCASCADE;

watsonx.data considerations

The following table lists the ALTER DATABASE command options whether allowed or not allowed against watsonx.data.

Table 3. ALTER DATABASE command options
ALTER DATABASE option Allowed/ Not allowed
ALTER DATABASE <db_name> SET DEFAULT CHARACTER SET IDENT Not allowed
ALTER DATABASE <db_name> SET DEFAULT SCHEMA schema_name Not allowed
ALTER DATABASE <db_name> OWNER TO <user> Allowed
ALTER DATABASE <db_name> RENAME TO <new_db_name> Allowed
ALTER DATABASE <db_name> GROOM BACKUPSET <backupset> Not allowed
ALTER DATABASE <db_name> GROOM BACKUPSET DEFAULT Not allowed
ALTER DATABASE <db_name> DATA_VERSION_RETENTION_TIME <number-of-days> {NOCASCADE | CASCADE} Not allowed