Micro Focus Studio Enterprise Edition for Windows > Programming > Mainframe Programming > SQL Option for DB2 > Reference > SQL Reference > SQL Statements > ALTER DATABASE

ALTER DATABASE

Invocation

This statement can be embedded in an application program or issued interactively.

Authorization

To execute the ALTER DATABASE command, users must possess either DBADM or DBCTRL authority for the database, DROP privilege on the database, overall SYSADM or SYSCTRL authority for the location, or be the owner of the database affected.

Syntax

ALTER DATABASE database-name

[BUFFERPOOL bpname] 
    [INDEXBP bpname] 
    [STOGROUP stogroup-name] 
    [CCSID ccsid-value]

Parameters:

`database-name`	Identifies the database to be altered. The database must exist within the currently set location.
`BUFFERPOOL bpname`	Identifies the default buffer pool for the table spaces within the database. It does not apply to table spaces that already exist within the database. The 4K bufferpools are named BP0, BP1, BP2...BP49. The 32K bufferpools are named BP32K, BP32K1, BP32K2...BP32K9 If the database is a work file database, 8KB and 16KB buffer pools cannot be specified.
`INDEXBP bpname`	Identifies the default buffer pool for the indexes within the database. It does not apply to indexes that already exist within the database. The name must identify a 4KB buffer pool. INDEXBP cannot be specified for a work file database.
`stogroup-name`	Identifies an optional stogroup for this database, if stogroups are used.
`ccsid-value`	Identifies the default CCSID for table spaces within the database. It does not apply to existing table spaces in the database. ccsid-value must identify a CCSID value that is compatible with the current value of the CCSID for the database. CCSID cannot be specified for a work file data base or a TEMP database.

Description

The ALTER DATABASE command offers users the option of assigning a stogroup designation to a logical database. This command can also specify whether or not the database is used as a Read-Only Shared database.

BUFFERPOOL

This clause has no functional equivalent in the XDB Server operating environment. However, for DB2 compatibility purposes, the XDB Server records any of the sixty possible bufferpools (BP0...BP49, BP32K, BPK1...BPK9). The first fifty bufferpools are for tables with 4K page sizes, while the last ten are for tables with 32K page sizes. Although not functionally supported, this new information is semantically checked and recorded in the XDB Server catalog tables.

CCSID

This clause has no functional equivalent in the XDB Server operating environment.

STOGROUP

This clause allows the user to change the default stogroup to which all objects created under this database will be physically stored. This clause does not affect the storage locations of tables and indexes already created under the database. On an XDB Server, a stogroup designates a particular drive (and/or directory) on which all objects created under a logical database are physically stored. A stogroup is usually assigned to a database, or table space within a database, when these objects are created.

Example:

Change the default buffer pool for both table spaces and indexes within database ABCDE to BP2.

ALTER DATABASE ABCDE
    BUFFERPOOL BP2
    INDEXBP BP2;