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:
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.)
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.
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.
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
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.
Running the db2iupdt command updates the specified instance by performing the following:
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:
Displays a help menu for this command.
This parameter sets the debug mode for use during problem determination.
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:
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". |
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:
Examples:
db2iupdt -u db2fenc1 db2inst1
db2iupdt -u db2inst1 db2inst1
db2iupdt db2inst1
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:
The instance name; the default is the current instance.
The computer name of the Windows NT workstation on which the node resides; the default name is the computer name of the local machine.
Specifies the logical port number used for the database partition server if the logical port is not zero (0).
The TCP/IP host name that is used by FCM for internal communications if the host name is not the local host name.
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.
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).
To remove an instance, perform the following steps:
db2idrop InstNamewhere 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.
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.
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.
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.
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:
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.