IBM BPM and DB2 and Permissions and Users and So On and So Forth :-)

I've seen this exception: -

com.ibm.db2.jcc.am.SqlSyntaxErrorException: com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704, SQLERRMC=DB2USER1.LSW_SYSTEM_SCHEMA, DRIVER=4.18.60

before, as per these posts: -

IBM BPM to DB2 - You WILL respect my authority

IBM BPM 8.5.0.1 - Booting the Bootstrap

However, this time it's subtly different.

gives us an SQL exception code, which DB2 can debug for us: -

db2 ? SQL0204

which returns: -

SQL0204N  "<name>" is an undefined name.

Note that the exception relates to DB2USER1.LSW_SYSTEM_SCHEMA whereas we have: -

db2 list tables for all|grep -i LSW_SYSTEM

LSW_SYSTEM                      BPMDB2USER      T     2017-06-15-10.19.40.987764
LSW_SYSTEM_SCHEMA               BPMDB2USER      T     2017-06-15-10.19.41.033429

This reflects a time when we tried/failed to use a Unix / DB2 user account that was LONGER than eight characters: -

DB2 - Still 8 characters after all these years ...

Looking at the scripts that WAS generates for the database here: -

/opt/IBM/WebSphere/AppServer/profiles/PSDmgr01/dbscripts/

I can see that the schema name has been correctly set by the BPMConfig.sh tool when we rebuilt the Deployment Environment with the new shorter user - db2user1: -

PSCell1.PSCell1De1/DB2/PSBPMDB/createSchema_Advanced.sql:CREATE TABLE db2user1.LSW_SYSTEM (
PSCell1.PSCell1De1/DB2/PSBPMDB/createSchema_Advanced.sql:ALTER TABLE db2user1.LSW_SYSTEM ADD CONSTRAINT "PROPKEY" CHECK (CHARACTER_LENGTH("PROPKEY",CODEUNITS16) <= 256)  ENFORCED ENABLE QUERY OPTIMIZATION
PSCell1.PSCell1De1/DB2/PSBPMDB/createSchema_Advanced.sql:ALTER TABLE db2user1.LSW_SYSTEM ADD CONSTRAINT "PROPVALUE" CHECK (CHARACTER_LENGTH("PROPVALUE",CODEUNITS16) <= 256)  ENFORCED ENABLE QUERY OPTIMIZATION
...
PSCell1.PSCell1De1/DB2/PSBPMDB/createSchema_Advanced.sql:    db2user1.LSW_SYSTEM("PROPKEY",
PSCell1.PSCell1De1/DB2/PSBPMDB/createSchema_Advanced.sql:    db2user1.LSW_SYSTEM_SCHEMA("PROPNAME",

but I'm guessing that we didn't drop/recreate the databases after that change :-(

Now to test …

For the record, I initially thought that this was a permissions error, and started looking at this: -

Retrieving all privileges granted to users

with: -

SELECT * FROM SYSIBMADM.PRIVILEGES WHERE AUTHID = 'DB2USER1' AND AUTHIDTYPE = 'U'

and: -

db2 grant dbadm on database to user db2user1

Can you say "Red herring" ??