Interacting with the vCenter Server Appliance embedded IBM DB2 database

Products

• VMware vCenter Server

Issue/Introduction

This article shows how to interact with the embedded version of the IBM DB2 database, which is the default database for the vCenter Server Appliance.

This article discusses how to check for running processes and current versions, and how to connect to the DB2 database to run SQL queries.

Environment

VMware vCenter Server 5.0.x
VMware vCenter Server Appliance 5.0.x

Resolution

1. Start a SSH session to the vCenter Server Appliance. When you are prompted to log in, use the user root and the password:
   
   
   • User: root
   • Password: vmware (default)
     
     
2. To check to see if the IBM DB2 is currently running, run the command:
   
   VCSA-50:~ # ps -ef | grep db2


3. To check what version of the IBM DB2 is installed, run the command:
   
   VCSA-50:~ # rpm -qa | grep db2


4. To connect to the current instance of the DB2 manager:
   
   
   1. Switch to the db2inst1 user with the command:
      
      VCSA-50:~ # su - db2inst1


   2. Determine if the DB2 database manager is running with the command:
      
      db2inst1@VCSA-50:~> db2start

      If the DB2 database manager is running, the output is similar to:
      
      The database manager is already active.

      Notes:
      
      • To stop the manager, run the command:
        
        db2inst1@VCSA-50:~> db2stop force
        
        
      • To start the manager, run the command:
        
        db2inst1@VCSA-50:~> db2start
        
        
5. To check the current version of the DB2 database manager, run the command:
   
   db2inst1@VCSA-50:~> db2ls


6. To list the running DB2 instances, run either of these commands:
   
   
   • db2inst1@VCSA-50:~> db2ilist
   • db2inst1@VCSA-50:~> db2 get instance


7. To get more detailed information about the created database(s), run the command:
   
   db2inst1@VCSA-50:~> db2 list db recovery


8. To open a connection to the database:
   
   
   1. Switch the user to db2inst1 with the commands:
      
      VCSA-50:~ # su - db2inst1

db2inst1@VCSA-50:~> db2 connect to VCDB
      
      
   2. To get more detailed information about the database, run the command:
      
      db2inst1@VCSA-50:~> db2 get db cfg for VCDB

Note: Make sure that the Database territory is US and that the Database Code Set is UTF-8.
      
      
9. To show DB2 profile registry variables that are currently set, run the command:
   
   db2inst1@VCSA-50:~> db2set -all

The prerequisites for the database are:
   
   
   • db2set DB2_ATS_ENABLE_YES
   • db2set DB2_SKIPINSERTED=ON
   • db2set DB2_EVALUNCOMMITTED=ON
   • db2set DB2_SKIPDELETED=ON


10. To query the DB2 database(s), perform one of these options:
    
11. • Option 1: Use isql to query the DB2 database.
      
      Note: Make sure you are at the root prompt/user. To return to the root prompt, run the command: VCSA-50:~ #


      1. Retrieve the password for the vCenter Server database user with the command:
         
         grep EMB_DB_PASSWORD /etc/vmware-vpx/embedded_db.cfg


      2. Using the retrieved password string, run this command to connect via isql:
         
         VCSA-50:~ # isql -v 'VMware VirtualCenter' vc password


      3. When connected, you see a SQL prompt that appears similar to SQL>. You can now query the DB2 database from the prompt.
         
         Example: SQL> select ver_id, version_value from vpx_version

         
    • Option 2: Use the db2 tool:
      
    • 1. Switch the user to db2inst1 with the commands:
         
         VCSA-50:~ # su - db2inst1

db2inst1@VCSA-50:~> db2

         The prompt changes to db2 => when connected
         
         
      2. To connect to the vCenter Server database, run the command:
         
         db2 => connect to VCDB


      3. To set the schema to vc, run the command:
         
         db2 => set schema vc


      4. You can now query the DB2 database.
         
         Example: db2 => select ver_id, version_value from vpx_version

Additional Information

For more information on configuring and managing DB2 databases, see the vSphere 5 Documentation Center.

[Internal] Interacting with the VMware vCenter Server Appliance embedded vPostgres database
VMware vCenter Server にアップグレードするとデータベースが過剰に大きくなる

Impact/Risks: