ALTER DATABASE statement

Invocation for ALTER DATABASE

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 in effect. For more information, see Authorization IDs and dynamic SQL.

Authorization for ALTER DATABASE

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
• Installation SYSOPR authority (when the current SQLID of the process is set to SYSINSTL)

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.

Description for ALTER DATABASE

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 in SQL 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 in SQL 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 for ALTER DATABASE 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 for ALTER DATABASE

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. 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, see Working with international data.

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 for ALTER DATABASE

   ALTER DATABASE ABCDE
     BUFFERPOOL BP2
     INDEXBP BP2;