ALTER DATABASE

The ALTER DATABASE statement changes the description of a database at the current server.

Invocation

This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared only if DYNAMICRULES run behavior is implicitly or explicitly specified.

Authorization

The privilege set that is defined below must include at least one of the following:

The DROP privilege on the database
Ownership of the database
DBADM or DBCTRL authority for the database
SYSADM or SYSCTRL authority
System DBADM

If the database is implicitly created, the privileges must be on the implicit database or on DSNDB04.

Privilege set: If the statement is embedded in an application program, the privilege set is the privileges that are held by the owner of the package. If the statement is dynamically prepared, the privilege set is the union of the privilege sets that are held by each authorization ID and role of the process.

Syntax


>>-ALTER DATABASE--database-name-------------------------------->

   .-----------------------------------.   
   V  (1)                              |   
>----------+-BUFFERPOOL--bpname------+-+-----------------------><
           +-INDEXBP--bpname---------+     
           +-STOGROUP--stogroup-name-+     
           '-CCSID--ccsid-value------'

Notes:

The same clause must not be specified more than one time.

Description

DATABASE database-name

Identifies the database that is to be altered. The name must identify a database that exists at the current server and must not identify an implicitly created system database.

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.

If the database is a work file database, 8 KB and 16 KB buffer pools cannot be specified.

If the table space is implicitly created, DB2® selects the buffer pool as described in Implicitly defined table spaces.

See Naming conventions for more details about bpname.

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 can identify a 4 KB, 8 KB, 16 KB, or 32 KB buffer pool. See Naming conventions for more details about bpname.

STOGROUP stogroup-name

Identifies the storage group to be used, as required, as a default storage group to support DASD space requirements for table spaces and indexes within the database. It does not apply to table spaces and indexes that already exist within the database.

CCSID 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. Notes contains a list that shows the CCSID to which a given CCSID can be altered.

CCSID cannot be specified for a work file database.

Notes

Altering the CCSID: The ability to alter the default CCSID enables you to change to a CCSID that supports the Euro symbol. You can only convert between specific CCSIDs that do and do not define the Euro symbol. In most cases, the code point that supports the Euro symbol replaces an existing code point, such as the International Currency Symbol (ICS).

Changing a CCSID can be disruptive to the system and requires several steps. For each encoding scheme of a system (ASCII, EBCDIC, and Unicode), DB2 supports SBCS, DBCS, and mixed CCSIDs. Therefore, the CCSIDs for all databases and all table spaces within an encoding scheme should be altered at the same time. Otherwise, unpredictable results might occur.

The recommended method for changing the CCSID requires that the data be unloaded and reloaded. See DB2 Installation Guide for the steps needed to change the CCSID, such as running an installation CLIST to modify the CCSID data in DSNHDECP, when to drop and re-create views, and when to rebind invalidated packages.

The following lists show the CCSIDs that can be converted. The second CCSID in each pair is the CCSID with the Euro symbol. The CCSID can be changed from the CCSID that does not support the Euro symbol to the CCSID that does, and vice versa. For example, if the current CCSID is 500, it can be changed to 1148.

EBCDIC CCSIDs
---------------

37         1140
273        1141
277        1142
278        1143
280        1144
284        1145
285        1146
297        1147
500        1148
871        1149

ASCII CCSIDs
---------------

850        858
874        4970
1250       5346
1251       5347
1252       5348
1253       5349
1254       5350
1255       5351
1256       5352
1257       5353

Example

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

   ALTER DATABASE ABCDE
     BUFFERPOOL BP2
     INDEXBP BP2;