WebSphere Commerce Instance Creation Creates Database as Wrong User

Posted by

This post is specific to WebSphere Commerce.

We’ve reported this issue to IBM, but since we’ve run into it several times across more than a year, and in multiple different Fix Packs of Commerce, I thought I’d share it in case someone else runs into it. The main way it manifests initially is like this:

/configureWorkspaces.xml:245: com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-551, SQLSTATE=42501, SQLERRMC=DB2INST1;SELECT;WSCOMUSR.KEYS, DRIVER=4.14.122

The user ‘DB2INST1’ in the above may change, but basically an error like this comes up somewhere along the line saying that your DB2 instance owner does not have permissions on the database.

We only see this for an instance creation where isRemote is set to false.

I’m not sure that anyone cares about the root cause of the issue, but our engineers narrowed it down to a single line that fails in one of the Commerce scripts – COMMERCE_HOME/bin/createdb.db2.sh in Fixpack 5, line 155(also in later fixpacks, at least to FP7):

db2 -v attach to $DB2INSTANCE user $USER using $PASSWORD >> $LOG 2>&1

$DB2INSTANCE is undefined anywhere else during this process. It results in a minor error during DB creation:

attach to user db2inst1 using db2inst1
SQL0104N  An unexpected token "db2inst1" was found following "USER".  Expected tokens may include:  "USER".  SQLSTATE=42601

The underlying problem is that the db2profile was not sourced for the user id running the instance creation prior to running the instance creation.

This issue is only seen if you are using separate ids for DBAuser, DBuser, and running the instance creation (which you should be!). It appears to me that WebSphere Commerce is assuming you are using the same ID for everything, so you would already have the db2profile sourced.

The key to preventing this is to ensure that the db2profile is sourced by the user in question prior to executing the instance creation. You may have to do this in the .profile, .bash_profile or .bashrc, but it is easy to add lines like this (changing paths depending on where your $INSTANCEHOME is):

if [ -f /db2home/db2inst1/sqllib/db2profile ]; then
    . /db2home/db2inst1/sqllib/db2profile
fi

Lead Db2 Database Engineer and Service Delivery Manager , XTIVIA
Ember is always curious and thrives on change. Working in IT provides a lot of that change, but after 17 years developing a top-level expertise on Db2 for mid-range servers and more than 7 years blogging about it, Ember is hungry for new challenges and looks to expand her skill set to the Data Engineering role for Data Science. With in-depth SQL and RDBMS knowledge, Ember shares both posts about her core skill set and her journey into Data Science. Ember lives in Denver and work from home for XTIVIA, leading a team of Db2 DBAs.

2 comments

  1. Hello,
    We do have run into the above problem in our env., The Commerce Instance is already created. So now, what is the workaround to fix this issue?
    (Apart from adding the lines to .profile and creating the Commerce Instance again?)

    1. There is no way to change the ownership of the database once it is created. But you can use that ID to grant DBADM to other IDs, and that is the workaround. You may even be able to revoke most privileges from the creating ID once you have done that. If you have the ability, I prefer to drop and re-create the instance – it’s just cleaner and takes care of details you might not otherwise consider.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.