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
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?)
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.