Administration Guide

Before Altering a Database

Some time after a database design has been implemented, a change to the database design may be required. You should reconsider the major design issues that you had with the previous design. You should pay particular attention to the following:

Changing Logical and Physical Design Characteristics
Changing the License Information
Changing Instances
Changing Environment Variables and the Profile Registry Variables
Changing the Node Configuration File
Changing the Database Configuration

Changing Logical and Physical Design Characteristics

Before you make changes affecting the entire database, you should review all the logical and physical design decisions. For example, when altering a table space, you should review your design decision regarding the use of SMS or DMS storage types. (See Designing and Choosing Table Spaces.)

Changing the License Information

As part of the management of licenses for your DB2 products, you may find that you have a need to increase the number of licenses. You can use the License Center within the Control Center to check usage of the installed products and increase the number of licenses based on that usage.

Changing Instances

Instances are designed to be as independent as possible from the effects of subsequent installation and removal of products.

In most cases, existing instances automatically inherit or lose access to the function of the product being installed or removed. However, if certain executables or components are installed or removed, existing instances do not automatically inherit the new system configuration parameters or gain access to all the additional function. The instance must be updated.

If DB2 is updated by installing a Program Temporary Fix (PTF) or a patch, all the existing DB2 instances should be updated using the db2iupdt command. You should also update the Administration Server (DAS) using the dasiupdt command.

You should ensure you understand the instances and database partition servers you have in an instance before attempting to change or delete an instance.

Listing Instances

To get a list of all the instances that are available on a system using the Control Center:

Expand the object tree until you see the Databases folder.
Right-click the database you want the list instances for, and select Add from the pop-up menu.
Click Refresh, and click the arrow at the end of the Database name field to see the list of instances.
Press Cancel.

To get a list of all the instances that are available on a system using the command line, enter:

   db2ilist

To determine which instance applies to the current session (on OS/2 or supported Windows platforms) use:

   set db2instance

Updating Instance Configuration

Running the db2iupdt command updates the specified instance by performing the following:

Replaces the files in the sqllib subdirectory under the instance owner's home directory.
If the node type is changed, then a new database manager configuration file is created. This is done by merging relevant values from the existing database manager configuration file with the default database manager configuration file for the new node type. If a new database manager configuration file is created, the old file is backed up to the backup subdirectory of the sqllib subdirectory under the instance owner's home directory.

The db2iupdt command is found in the instance subdirectory in the version and release subdirectory (the exact name varies by operating system).

The command is used as shown:

   db2iupdt InstName

The InstName is the log in name of the instance owner.

There are other optional parameters associated with this command:

-h or -?
Displays a help menu for this command.
-d
Sets the debug mode for use during problem determination.
-a AuthType
Specifies the authentication type for the instance. Valid authentication types are SERVER, CLIENT, DCS, or DCE. If not specified, the default is SERVER, if a DB2 server is installed. Otherwise, it is set to CLIENT. The authentication type of the instance applies to all databases owned by the instance.
On UNIX operating systems, DCE is not a valid the authentication type.
-e
Allows you to update each instance that exists and can be shown using db2ilist.
-u FencedID
Names the user under which the fenced user-defined functions (UDFs) and stored procedures will execute. This is not required if you install the DB2 client or the DB2 Software's Developer Kit. For other DB2 products, this is a required parameter.
Note: FencedID may not be "root" or "bin".
-k
This parameter preserves the current instance type. If you do not specify this parameter, the current instance is upgraded to the highest instance type available in the following order:
- Partitioned database server with local and remote clients (DB2 Enterprise - Extended Edition default instance type)
- Database Server with local and remote clients (DB2 Universal Database Enterprise Edition default instance type)
- Client (DB2 client default instance type)

Examples:

If you installed DB2 Universal Database Workgroup Edition or DB2 Universal Database Enterprise Edition after the instance was created, enter the following command to update that instance:
```
   db2iupdt -u db2fenc1 db2inst1
```
If you installed the DB2 Connect Enterprise Edition after creating the instance, you can use the instance name as the Fenced ID also:
```
   db2iupdt -u db2inst1 db2inst1
```
To update client instances, you can use the following command:
```
   db2iupdt db2inst1
```

Removing Instances

To remove an instance using the Control Center:

Expand the object tree until you see the instance you want to remove.
Right-click the instance name, and select Remove from the pop-up menu.
Check the Confirmation box, and click Ok.

To remove an instance using the command line, enter:

   db2idrop <instance_name>

The preparation and details to removing an instance using the command line are:

Stop all applications that are currently using the instance.
Stop the Command Line Processor by running db2 terminate commands in each DB2 command window.
Stop the instance by running the db2stop command.
Back up the instance directory indicated by the DB2INSTPROF registry variable. On UNIX operating systems, consider backing up the files in the INSTHOME/sqllib directory (where INSTHOME is the home directory of the instance owner). For example, you might want to save the database manager configuration file, db2systm, the db2nodes.cfg file, user-defined functions (UDFs), or fenced stored procedure applications.
(On UNIX operating systems only) Log off as the instance owner.
(On UNIX operating systems only) Log in as a user with root authority.
Issue the db2idrop command:
```
   db2idrop InstName
```
where InstName is the name of the instance being dropped.
This command removes the instance entry from the list of instances and removes the instance directory.
(On UNIX operating systems only) Optionally, as a user with root authority, remove the instance owner's user ID and group (if used only for that instance). Do not remove these if you are planning to re-create the instance.
This step is optional since the instance owner and the instance owner group may be used for other purposes.

The db2idrop command removes the instance entry from the list of instances and removes the sqllib subdirectory under the instance owner's home directory.

Changing Environment Variables and the Profile Registry Variables

You must consider which environment variables (if any) need to be changed on your particular operating system. If any environment variables are changed and you are not on a UNIX platform, you need to restart the system for the new environment variables to take effect. Review whether you should reset the profile registry variables in the Global Profile registry before altering your database. You can then reset the profile registry variables to those that are best suited to the new database environment. If only profile registry variables have been changed, the system does not need to be restarted.

Changing the Node Configuration File

If you are planning changes to any nodegroups (both adding or deleting nodes, or moving existing nodes), you should see Chapter 29, Scaling Your Configuration Through Adding Processors for details on what should be done.

Changing the Database Configuration

If you are planning changes to the database, you should review the values for the configuration parameters. Some of the values can be adjusted from time to time as part of the ongoing changes made to the database based on how it is used.

To change the database configuration, use the Performance Configuration Wizard in the Control Center. This wizard helps you tune performance and balance memory requirements for a single database per instance by suggesting which configuration parameters to modify and providing suggested values for them.
Note: If you modify any parameters, the values are not updated until:

For database parameters, the first new connection to the database after all applications were disconnected.
For database manager parameters, the next time you stop and start the instance.

In most cases the values recommended by the Performance Configuration Wizard will provide better performance than the default values, because they are based on information about your workload and you own particular server. However, note that the values are designed to improve the performance of, though not necessarily optimize, your database system. Think of them as a starting point on which you can make further adjustments to obtain optimized performance.

To change the database configuration using the Control Center:

Expand the object tree until you see the Databases folder.
Right-click the instance or database you want to change, and select Configure Performance Using Wizard from the pop-up menu.
Click on each page and change information as required.
Click on the Results page to review you work and apply any suggested configuration parameters.
When you are finished applying updates, click Finish.

To change the database configuration using the command line, enter:

   UPDATE DBM CFG FOR <database_alias> 
      USING <config_keyword>=<value>

You can update one or more <config_keyword>=<value> combinations in a single command. Most changes to the database configuration file become effective only after they are loaded into memory. For a server configuration parameter, this occurs during the running of the START DATABASE MANAGER command. For a client configuration parameter, this occurs when the application is restarted.

To view or print the current database manager configuration parameters, use the GET DATABASE MANAGER CONFIGURATION command.

See Chapter 31, Benchmark Testing and Chapter 32, Configuring DB2 for details on how to refine your system by benchmarking, and to configure your system.

For multiple partitions: When you have a database that is partitioned across more than one partition, the configuration file should be the same on all database partitions. Consistency is required since the SQL compiler compiles distributed SQL statements based on information in the local node configuration file and creates an access plan to satisfy the needs of the SQL statement. Maintaining different configuration files on database partitions could lead to different access plans, depending on which database partition the statement is prepared. Use db2_all to maintain the configuration files across all database partitions.

[ Top of Page | Previous Page | Next Page ]