This post is specific to WebSphere Commerce. We spent a fair amount of time on this, both on a SUSE server and a Red Hat Server. The problem manifested in different ways. The main thing that became obvious to me is that Commerce was not creating the Commerce database as the dbaUser specified in createInstance.properties file, but was instead using the id that was running the Commerce Instance creation. While one workaround is simply to allow this to happen, and grant dbadm and secadm to the user you wanted in the first place later, we were on the first server of at least 4 in a build and wanted to have a reproducible process.
Error Messages Encountered
So you’ll get different error messages at different stages depending on how you’ve set up ids. You should always have a different dbaUser from the main Commerce user. And likely you’ll be using yet a third id to run the instance creation. If you’re mingling ids you may not run into problems here at all, but mingling ids is bad for a number of reasons, not the least of which is security and PCI. If the id you’re using to run the Commerce instance creation is not in the SYSADM group defined in db2’s dbm cfg, you’ll see an error message that references the id that you’re using for commerce instance creation not having authority to create the database. I’m sorry that I don’t have the exact syntax for that – if anyone else does, please comment with it, and I’ll update the post with it.
If you don’t catch this during instance creation, you’ll later see this when you try to use the id you thought was DBADM to do something:
Error perform operation "SELECT" on object "WSCOMUSR.STGMERTAB". SQLSTATE=42501 : SQL0551N "DB2INST1" does not have the required authorization or privilege to Error perform operation "SELECT" on object "WSCOMUSR.STGSITETAB". SQLSTATE=42501 : SQL0551N "DB2INST1" does not have the required authorization or privilege to
IBM’s Tech Note on This Issue
So I’m not entirely sure that this Tech Note is the same issue, but it’s what first gave me the idea that it might be DB2 9.7 FixPack 5. We had plenty of 9.7 installs with no issues on earlier FixPacks of 9.7.
https://www-304.ibm.com/support/docview.wss?uid=swg21572427
Resolving This Issue
So the fix for us was simply to do Commerce instance creation on 9.7 Fixpack 4, and we were able to successfully apply DB2 9.7 FixPack 5 afterwards with no ill effects.
Communicating with IBM will be important if you run into this one. I’m not sure we’ve managed to convince WebSphere Commerce Support of the importance on this one yet.
Update February 9, 2012: Commerce support has confirmed the issue we saw was NOT directly related to the Tech Note above. Still waiting to see if they point us to another issue or give us an APAR or what.
Could this be because db2inst1 did not have DATAACESS privilege on the database?
GRANT DBADM (including the implicit grant for the db creator) by default provides DATAACCESS privileges. That’s how it was till Fixpak 4 atleast.
If the default has changed in FP5, it is either a bug or an undocumented change.
Of course, I do not find a document supporting my theory 😉
Thanks
That’s the other workaround, I suppose, to just grant dbadm and and secadm to db2inst1. But if the database is created by the proper user, we wouldn’t have to do that. We didn’t have to in FixPack 3 and before, and we didn’t have to when we did the workaround of doing instance creation on DB2 9.7 FixPack 4 instead of DB2 9.7 FixPack 5. You’d also have to revoke permissions from whatever id you’re using to run the Commerce Instance creation.
[…] WebSphere Commerce Instance Creation Doesn’t Like DB2 9.7 FixPack 5 […]