Administration Guide

Before Altering a Database

Some time after a database design has been implemented, you may be considering a change to the database design. As a result, you should reconsider the major design issues that you had with the previous design. You should consider 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

Existing 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, enter:

   db2ilist

To determine which instance applies to the current session,

   set db2instance

Listing Database Partition Servers in an Instance

Use the db2nlist command to obtain a list of database partition servers that participate in an instance.

   db2nlist

When using this command as shown, the default instance is the current instance (set by the DB2INSTANCE environment variable). To specify a particular instance, you can specify the instance using:

   db2nlist /i:instName

where instName is the particular instance name you want.

You can also optionally request the status of each partition server by using:

   db2nlist /s

The status of each database partition server may be one of: starting, running, stopping, or stopped.

Updating Instances

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

  This parameter sets the debug mode for use during problem determination.

• -a AuthType

  This parameter 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.

  Notes:

  1. The authentication type of the instance applies to all databases owned by the instance.

  2. On UNIX operating systems, the authentication type DCE is not a valid choice.

• -u FencedID

  This parameter is 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 Extended Enterprise 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
  

Adding a Database Partition Server to an Instance

Use the db2ncrt command to add a database partition server (node) to an instance.

Note:

Do not use the db2ncrt command if there are databases already existing in this instance. Instead, use the db2start addnode command. This ensures that the database is correctly added to the new database partition server. DO NOT EDIT the db2nodes.cfg file, since changing the file may cause inconsistencies in the partitioned database system.

The command has the following required parameters:

   db2ncrt /n:node_number
           /u:username,password

The first parameter (/n:) is the unique node number to identify the database partition server. The number can be from 1 to 999 in ascending sequence.

The second parameter (/u:) is the logon account name and password of the DB2 service.

There are also several optional parameters:

• /l:instance_name

  The instance name; the default is the current instance.

• /m:machine_name

  The computer name of the Windows NT workstation on which the node resides; the default name is the computer name of the local machine.

• /p:logical_port

  Specifies the logical port number used for the database partition server if the logical port is not zero (0).

• /h:host_name

  The TCP/IP host name that is used by FCM for internal communications if the host name is not the local host name.

• /o:instance_owning_machine

  The computer name of the machine that is the instance-owning machine; the default is the local machine. This parameter is required when the db2ncrt command is invoked on any machine that is not the instance-owning machine.

Dropping a Database Partition Server from an Instance

Use the db2ndrop command to drop a database partition server (node) from an instance that has no databases. If you drop a database partition server, its node number can be reused for a new database partition server.

Exercise caution when you are dropping database partition servers from an instance. If you drop the instance-owning database partition server node zero (0) from the instance, the instance will become unusable. If you want to drop the instance, use the db2idrop command.

Note:

Do not use the db2ndrop command if there are databases in this instance. Instead, use the db2stop drop command. This ensures that the database is correctly removed from the database partition server. DO NOT EDIT the db2nodes.cfg file, since changing the file may cause inconsistencies in the partitioned database system.

The command has the following required parameter:

   db2ndrop /n:node_number

The parameter (/n:) is the unique node number to identify the database partition server. The number can be from zero (0) to 999 in ascending sequence. Recall that node zero (0) represents the instance-owning machine.

There is an optional parameter (/i:instance_name) which is the instance name; the default is the current instance (set by the DB2INSTANCE environment variable).

Removing Instances

To remove an instance, perform the following steps:

1. End all applications that are currently using the instance.

2. Stop the Command Line Processor by running db2 terminate commands in each DB2 command window.

3. Stop the instance by running the db2stop command.

4. Back up the instance directory indicated by the DB2INSTPROF registry variable. On UNIX operating systems, you might 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.

5. (On UNIX operating systems only) Log off as the instance owner.

6. (On UNIX operating systems only) Log in as a user with root authority.

7. 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.

8. (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 refer to "Scaling Your Configuration" in the Administration Guide, Performance 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 SmartGuide. This SmartGuide 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. To use this SmartGuide:

1. From the Control Center, click with mouse button 2 on the database for which you want to configure performance.

2. Select Configure Performance from the pop-up menu. The Performance Configuration SmartGuide opens.

3. Follow the steps in the SmartGuide and answer the questions it asks.

4. Note that if you select to update the parameters, they 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 SmartGuide 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. They should be thought of as a starting point on which you can make further adjustments to obtain optimized performance.

For details on how to refine your system by benchmarking, and to configure your system, refer to "Benchmark Testing" and "Configuring DB2" in the Administration Guide, Performance.

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 create the same configuration file on all database partitions.

[ DB2 List of Books | Search the DB2 Books ]