Preparing an existing DB2 database

q6542125

Preparing an existing DB2 database

Objective
　　To prepare an existing DB2 database for use by the management server.

Background information
　　This section describes configuration tasks and requirements for setting up a DB2 database to work with a management server. For detailed information on DB2 architecture, commands, and procedures, consult the DB2 documentation.

Required authorization role
　　On UNIX, the user who performs this procedure must have root privileges. On Windows, the user who performs this procedure must have membership in the Administrators group.

Before you begin

Note:

The database for the management server must use encoding UTF8 in order to handle non-ASCII values (such as Chinese characters).
　　You must have a valid user name and password to give you access to the required database for the management server.

When you finish
　　None

Procedure
　　Create the database by doing the following:
　　Create the database to be used for the management server under a DB2 Instance associated with that client. To create the database, log on as an authorized user of the DB2 Instance or log on as the user under which the DB2 client was installed. The user under which the DB2 client was installed has access to all DB2 Instances associated with the client.

• Install the DB2 client on the computer to be used for the management server.
  
• Use the following command to create the database:
  Note:
  
  (Double-byte character environments only): You must enable your database to store monitoring information that comes in double-byte character strings. The following command for creating a database enables storage of double-byte character sets:
  - db2: create database database_name USING CODESET UTF-8 TERRITORY US
  
  
  db2 create database database_name
  where database_name is the name of the database.　　Example: If db2Admin is the user name under which the DB2 database client was installed on the management server, you can log on as db2Admin and create a database named itmtpDB to support the management repository.
  
  db2 create database itmtpDB
  

　　Create the database user on the operating system of the DB2 server computer.
　　Create a database user directly on the server or from within the DB2 client. database user refers to the system user account the management server uses to access the DB2 database that supports the management repository.
　　Example: If the DB2 server is installed on a Windows computer, enter the following command at a DOS prompt on that computer to create a database user account that the management server can use to access the management repository. This example creates a user named itmtp53 with password itmtp53. (The password is the second itmtp53 entry.)

net user itmtp53 itmtp53 /add
　　Create and implement a buffer pool by doing the following:

• To implement a buffer pool, you must disconnect all applications connected to the database.
  
• Connect to the database on which you are changing the buffer pool size.
  
• Create a buffer pool as large as possible without using operating system paging.
  Note:
  
  The default buffer pool and page size for DB2 are smaller than required for optimal performance by applications using the management repository. Use a buffer pool size of at least 250.
  　　Example: to create a buffer pool of size 250, with pagesize of 32K, enter the following command in DB2:
  
  create bufferpool buffpool32k size 250 pagesize 32k
  
• Update the database configuration for database_name using the APPLHEAPSZ 513 command.
  update database configuration for database_name using APPLHEAPSZ 513
  
• Enter the following command to view the new buffer pool:
  select * from syscat.bufferpools
  
• List all applications connected to the database using the following command:
  db2 list applications
  A list of applications similar to the following displays:
  Auth ID   Appl.  Appl.      Application ID            DB     #of
  Name   Handle                               Name  Agents
  --------  ----   ------  ---------------------------  ----  ------
  DB2INST1  java     14   *LOCAL.db2instl.000918161203  CSWA    1
  DB2INST1  java     15   *LOCAL.db2instl.000918161204  CSWA    1
  
• Close all connections to the database and repeat the following command for each application connected:
  db2 force application (applicationhandle)
  where applicationhandle is the application handle number listed in the Appl. Handle column of the list of applications (see Step 6).
  
• Reconnect to the database:
  
  
  
  • If you are logged on as a user authorized to the DB2 Instance under which the database was created, enter the following command:
    db2 "connect to databasename"
    where databasename is the name of the database to be used for the management server.
    
  • If you are not logged on as a user authorized to the DB2 Instance under which the database was created, enter the following command:
    db2 "connect to databasename user InstUser using InstUserPasswd"
    where databasename is the name of the database to be used for the management server and InstUser and InstUserPasswd are the userID and password of a user authorized to the DB2 Instance under which the database was created.　　Information similar to the following displays:
    
    $db2 connect to cswa
    Database Connection Information
    Database server      = DB2/NT 8.1.0
    SQL authorization ID = DB2INST1
    Local database alias = CSWA
    
  
  
  

• Grant permissions to the database user (the user account that the management server uses to access the database) by using the following commands:
  Note:
  
  The database user must have authority to create and edit tables.
  
  db2 "grant dbadm on database to user user"
  db2 "grant use of tablespace tablespace to user user"
  where user is the user ID of the database user and tablespace is the name of the tablespace to be created for the management server.　　Additional Information: These commands grant DBADM authority to the database and to the tablespace. To issue these commands, you must be logged on as a user authorized to the DB2 Instance under which the database was created.
  　　Example: If you created a database user named itmtp53 for the management server to use and a tablespace named userspace1, use the following commands to grant authority to the database and to the tablespace.
  
  db2 "grant dbadm on database to user itmtp53"
  db2 "grant use of tablespace userspace1 to user itmtp53"
  　　(Optional) You can use the following process to verify that the DB2 database configuration is optimized:
  
  Note:
  
  This sample procedure optimizes database configuration for use with IBM Tivoli Monitoring for Transaction Performance applications by increasing the number of log primaries and turning on the log retain function.
  
  
  • List the database configuration information by entering:
    db2 "get db cfg for databasename"
    
  • Increase the number of log primaries by entering:
    db2 "update db cfg for databasename using logprimary number"
    
  • Turn on the log retain function by entering:
    db2 "update db cfg for databasename using logretain on"
    
  • List all applications connected to the database by entering:
    db2 list applications
    A list of applications similar to the following is displayed:
    Auth ID   Appl.  Appl.      Application ID            DB     #of
    Name   Handle                               Name  Agents
    --------  ----   ------  ---------------------------  ----  ------
    DB2INST1  java     14   *LOCAL.db2instl.000918161203  CSWA    1
    DB2INST1  java     15   *LOCAL.db2instl.000918161204  CSWA    1
    
  • Close all connections to the database. Repeat the following command for each application:
    db2 force application (applicationhandle)
    where applicationhandle is the application handle number listed in the Appl. Handle column of the list of applications (see Step 1d).
    
  • Make an offline backup and apply db logs to roll forward. Enter the following:
    db2 "backup db databasename to backupdirectory"
    
  • Repeat Step 1d.
    
  　　Set up a DB2 client connection by doing the following:
  　　Follow these guidelines when installing and configuring the DB2 Client Application Enabler:
  
  Note:
  
  If the DB2 RDBMS is installed on a separate computer from the client (the management server), you must install the DB2 Client Application Enabler on the client computer and set up connectivity to the database.
  
  
  
  
  • Ensure that there is no DB2 Client Application Enabler installation (either complete or partial) on the system. If an installation already exists, uninstall it.
    
  • Customize the options to select Java database connectivity (JDBC). A JDBC driver is installed as part of the DB2 Client Application Enabler installation.
    
  • Ensure that approximately 2 MB are available in the selected home directory to accommodate the DB2 Client Application Enabler.
    
  • (UNIX only) Source the db2profile file (for the Korn or Bourne shell) or the db2cshrc file (for the C shell) to be able to run DB2 commands and utilities such as catalog and connect.　　The db2profile and db2cshrc files are located in the home directory of the DB2 instance owner, under the sqllib subdirectory. Source the appropriate file in the .profile or .cshrc file of each account that needs to use the DB2 environment and tools.
    
    
  • (AIX only) To verify the setup of the RDBMS and of the DB2 client, ensure that the following environment variables are set correctly. (If you have sourced the db2profile or db2cshrc file, these variables are correct.)
    
    
    
    • DB2DIR must point to the directory where the DB2 Client Application Enabler is installed.
      
    • DB2INSTANCE must be set to the database instance name. This is set during installation of the database.
      
    
    
    
  　　After the DB2 Client Application Enabler is installed, you must set up connectivity both to the RDBMS server and to the database. DB2 commands for setting up connectivity are issued from the client system. You must run these commands even when DB2 is running on the same computer that hosts the management server.
  
  
  
  
  • The following example command connects the client with an RDBMS server named ghost, which has the node name or alias ims_db2 and uses port 50000:
    db2 "catalog tcpip node ims_db2 remote ghost server 50000"
    
  • The following example command connects the client to a database named timsdev with the alias ims_dev, located on the server whose node name or alias is ims_db2:
    db2 "catalog database timsdev as ims_dev at node ims_db2"
    
  　　Provide extra shared memory segments for DB2 connections on AIX by doing the following:
  
  Note:
  
  By default, 32-bit applications cannot attach more than 11 shared memory segments per process, of which a maximum of 10 can be used for local DB2 connections. Use the EXTSHM (extended shared memory) functionality to ensure that an adequate number of shared memory segments are available for DB2 connections. The EXTSHM functionality is available for DB, Version 7.2 (which is the result of installation of DB2 UDB, Version 7.1 Fix Pack 3) or for later versions of DB2. If you do not provide an adequate number of shared memory segments, you might see the following error message:
  SQL1224N  A database agent could not be started to service a request,
  or was terminated as a result of a database system shutdown or a force
  command. SQLSTATE=55032
  
  　　Enable EXTSHM as follows:
  
  
  
  
  • In DB2 client sessions: export EXTSHM=ON
    
  • When starting the DB2 UDB Server:
    export EXTSHM=ON
    db2set DB2ENVLIST=EXTSHM
    db2start
    
  • On DB2 UDB EEE:
    EXTSHM=ON
    export EXTSHM
    You also add these lines to the sqllib/db2profile file.