vCenter Server Appliance. Part 3 – Managing the embedded DB2 database

December 2, 2011 — 12 Comments

In this last post about the vCenter Server Appliance we will see a glimpse on how to manage the embedded database that comes bundle with the VCSA.

First I must say that I AM NOT A DB2 ADMIN. I got this info by playing with the VCSA in my homelab, digging a bit into the DB2 documentation and googling a lot. Use the information provided in this post at your own risk.

If you still want to risk the integrity of your precious appliance please keep reading :-)

“Disassembling” the database installation

Before starting to launch commands against the database we need to know a bit about it. Since this is SuSE Linux check the rpm packages installed.

image

Now that we now it is DB2 Express version 9.7.2-1, list the files in the package.

image

This is very interesting, the package doesn’t contain the database but the installation files. The reason for this is very simple, by default DB2 is not installed in the appliance. The Web UI gives you the option to use an Oracle external database or an embedded one.

When you select embedded and click Save Settings is when VCSA starts the installation and configuration of the database. Change to /opt/db2/db2expc_9.7.2_install and look at the contents.

There are four are files, the same showed by the rpm command.

  • db2exc_972_LNX_x86_64.tar.gz – The DB2 database itself.
  • do_db2_install – The installation script.
  • db2_create_script.sql – SQL script used by the installation script to create the vCenter database and the schema.
  • db2expc.rsp – An answer file used during the installation.

Feel free to take a more detailed look at the files.

Now move to the parent directory, /opt/db2, here you will find the installation directory and two links named current and home. The first will always point to the latest installed version and the second to the home directory for the db2 user. We’ll see more about this user later.

image

Change to current and the database directory structure will show up. There is a logs symlink, this link point the installation log directory.

image

The log file is called db2install.history again my advice is to review this log file, along with the installation files it can be a real learning experience about the DB2 installation process.

Identifying the database

OK we know how the database was installed now we need to know how it works. Check for the DB2 processes.

image

Apart from root there are three other users:

  • db2inst1
  • db2fenc1
  • dasusr1

Look for these users in /etc/passwd.

image

The user db2inst1 is the only one who has a login shell, this is the database admin user.

The home directory for the three users is the same that symlink home pointed at in /opt/db2. This is where the DB2 environment is loaded from.

Make yourself db2inst1 to load the DB2 environment. For the majority of the operations we will use the db2 command. Db2 is the IBM DB2 Command Line Processor, it runs SQL statements against the database and it can be used in interactive mode, command mode and batch processing mode.

First thing is to know which DB2 version is installed. Use the db2ls command.

image

As you can see the VCSA is running IBM DB2 9.7.0 FixPack 2. The Install Path is also displayed.

Next is checking DB2 database manager. Use the db2start command to check if the manager is running.

image

Try to stop the manager with db2stop.

image

Since the vCenter database is active the operation is not allowed, to force the stop use db2stop force.

image

Then start again the database manager.

image

We are going now to get the running instances. There are two commands to perform this operation. db2ilist and db2 get instance.

image

As you probably know many databases can be created within the same instance so we are going to list the databases created.

image

As expected only one database is created and its name is VCDB… Surprise!

In a DB2 installation we can also list the active databases, of course in the VCSA only one will be active.

image

Open a connection to the database and retrieve connection state.

image

Once the connection is established we can get detailed information about the database, using again the db2 command line processor.

image

List the tablespaces of the database.

image

There are many more options available within db2 utility, I’ll let up to you to investigate them further.

Querying the DB2 database

The final part of our trip is to interrogate the DB2 database. We will use the isql utility, that comes bundles with the VCSA, to perform a few basic SQL queries. This tool is part of the unixODBC project, you can find more about it in their website.

And again we will use the db2 command line processor.

– isql

You don’t need db2inst1 user to use isql, being root will suffice. To connect to the vCenter database first we need the vc user credentials. This is not a system user but a database one.

To get vc user password list the contents of /etc/vmware-vpx/embedded_db.cfg.

image

The EMB_DB_PASSWORD variable contains the password.

Open a connection to the database passing the database ID, user and password as arguments.

image

Now we will interrogate the database tables. Please take into account that in my installation these tables are empty since this a lab environment, in a production one they will be populated.

If you want to know which tables are created have a look at the SQL file VCDB_db2.sql. This file is in the vCenter Server media, the Windows one, in the vCenter-Server\dbschema folder. This file is used by the Windows-based vCenter to create the database schema during the installation process when it is connected to an IBM DB2 database.

image

Following are a couple of SQL commands you can use. Feel free to investigate the above file, I found it very helpful to understand how he vCenter database is constructed.

Get contents of vpx_product and vpx_version tables.

image

Get the virtual datacenter ID, contained in the vpx_datacenter table.

image

– db2 command line processor

Make yourself db2inst1 user and launch the db2 shell.

image

Connect to the database using the same connect to VCDB statement we saw in the previous section.

Now we can run our SQL queries. In db2 there is no need to end the command with ‘;’ as we did in isql.

For the tables you need to prefix the tables with vc, the owner of the tables.

image

Or set the schema at the beginning.

image

And with this we are done with the vCenter Server Appliance series. Hope it will be of help for any of you my dear readers. Please feel free to comment with questions, corrections or any additional tip.

Juanma.

12 responses to vCenter Server Appliance. Part 3 – Managing the embedded DB2 database

  1. 

    Hi Juanma,

    this is an excellent blog! I would like to propose you cooperation with Veeam Software but I can’t find your e-mail in blog.

    Could you please contact me and we will discuss the details?

  2. 

    Hi,
    At first, DB2 was only available on IBM mainframes, but during the 1990’s DB2 spread to many other platforms, LUW (Linux, Unix, Windows), mid-range computers i5/OS (formerly AS/400) and even on PDAs.

  3. 

    Hello,
    There is some degree of consensus in the security, risk and control of DB2 databases. An organization that understands the DB2 risks and implements the appropriate controls will go a long way toward a secure DB2 environment.

    Thanks

  4. 

    Hello,
    DB2 controls are implemented to reduce the risks described above. Controls are required to secure user access, authentication and privileges. SQL injection is identified as a high risk that requires strong controls.

    Thanks
    Blueberry

  5. 
    http://bestnaturalhealthsupplements.com March 19, 2012 at 22:50

    It is appropriate time to make some plans for the long run and it is time to be happy. I’ve learn this post and if I may I desire to recommend you few fascinating things or tips. Maybe you could write subsequent articles regarding this article. I wish to learn even more issues approximately it!

  6. 

    How do you install the update manager on a separate windows machine and connect to the vCSA? I would appreciate your help.

  7. 

    Hi there, this is not possible anymore with 5.1 VCSA, since the DB is now a PostgreSQL.

    Anyway the approach could be the same and you can find the config file using ssh, it is located in /etc/vmware-vpx/embedded_db.cfg. In that you will find the user/pass/name of the instance, and you can then easily connect for example using an ODBC connection from a Windows Box with the PostgreSQL driver.

    Enjoy..!

    • 

      Hi Michael,

      Thanks for the comment!

      Yes I know the database is not DB2 anymore, in fact it was moved to vPostgres in the 5.0 Update release.

      Anyway thanks a lot for the great tip :-)

      Juanma.

      • 

        Hi Juan. You are welcome! Thanks a lot for what you are doing on this blog, which is actually very useful for the community. By the way i forgot to say that the PGadmin III tool is very useful to connect and administrate the vPostgres DB from a Windows box.

  8. 

    I’m extremely inspired together with your writing skills as well as with the structure for your blog. Is that this a paid subject or did you modify it your self? Either way stay up the excellent high quality writing, it is rare to see a great weblog like this one today..

  9. 

    You will eventually miss the board entirely no
    matter how good of a dart player you are. The market is flooded with this brand that comes with various make and types.
    “Lewis has twice won the PDC World Darts Championship — in 2011 and 2012 — and given the opportunity, he would love to win gold at the Olympic Games as well, he said.

Trackbacks and Pingbacks:

  1. VSA Posts « My Prep Guide for VCP5.0 - December 9, 2011

    […] vcenter-server-appliance-part-3-managing-the-embedded-db2-database LD_AddCustomAttr("AdOpt", "1"); LD_AddCustomAttr("Origin", "other"); LD_AddCustomAttr("theme_bg", "ffffff"); LD_AddCustomAttr("theme_border", "eeeeee"); LD_AddCustomAttr("theme_text", "555555"); LD_AddCustomAttr("theme_link", "36bcab"); LD_AddCustomAttr("theme_url", "36bcab"); LD_AddCustomAttr("LangId", "1"); LD_AddCustomAttr("Autotag", "technology"); LD_AddCustomAttr("Tag", "vsa"); LD_AddSlot("wpcom_below_post"); LD_GetBids(); Share this:TwitterFacebookLinkedInLike this:LikeBe the first to like this post. […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s