IBM BPM Advanced 8.5.5 - Interesting Times - 2 of 2

This follows my previous post: -

IBM BPM Advanced 8.5.5 - Interesting Times - 1 of 2

pertaining to three mis-scoped WAS variables.

This time around, I saw the following exceptions: -

SystemOut.log

[1/9/15 7:38:51:489 GMT] 00000001 wle           E com.lombardisoftware.client.security.ServerTypeUtil isRepository PreparedStatementCallback; bad SQL grammar [select propvalue from lsw_system where propkey=?]; nested exception is com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-551, SQLSTATE=42501, SQLERRMC=DB2ADMIN;SELECT;DB2ADMIN.LSW_SYSTEM, DRIVER=4.11.69
                                 org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [select propvalue from lsw_system where propkey=?]; nested exception is com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-551, SQLSTATE=42501, SQLERRMC=DB2ADMIN;SELECT;DB2ADMIN.LSW_SYSTEM, DRIVER=4.11.69
at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.translate(SQLExceptionSubclassTranslator.java:78)

[1/9/15 8:54:34:553 GMT] 00000192 FfdcProvider  W com.ibm.ws.ffdc.impl.FfdcProvider logIncident FFDC1003I: FFDC Incident emitted on c:\IBM\WebSphere\AppServer\profiles\AppSrv01\logs\ffdc\AppClusterMember1_dfd42b56_15.01.09_08.54.34.5535478247694713267328.txt com.lombardisoftware.utility.spring.ProgrammaticTransactionSupport isTransactionFailure-NoRetryForException

[1/9/15 8:54:34:553 GMT] 00000192 wle           E   CWLLG2229E: An exception occurred in an EJB call.  Error: PreparedStatementCallback; SQL [insert into lsw_system (propkey,propvalue) values (?,?)]; DB2 SQL Error: SQLCODE=-407, SQLSTATE=23502, SQLERRMC=TBSPACEID=2, TABLEID=189, COLNO=1, DRIVER=4.11.69; nested exception is com.ibm.db2.jcc.am.SqlIntegrityConstraintViolationException: DB2 SQL Error: SQLCODE=-407, SQLSTATE=23502, SQLERRMC=TBSPACEID=2, TABLEID=189, COLNO=1, DRIVER=4.11.69
                                 org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [insert into lsw_system (propkey,propvalue) values (?,?)]; DB2 SQL Error: SQLCODE=-407, SQLSTATE=23502, SQLERRMC=TBSPACEID=2, TABLEID=189, COLNO=1, DRIVER=4.11.69; nested exception is com.ibm.db2.jcc.am.SqlIntegrityConstraintViolationException: DB2 SQL Error: SQLCODE=-407, SQLSTATE=23502, SQLERRMC=TBSPACEID=2, TABLEID=189, COLNO=1, DRIVER=4.11.69
at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.translate(SQLExceptionSubclassTranslator.java:69)
at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.translate(SQLErrorCodeSQLExceptionTranslator.java:237)

Process Center web page

Error 500: java.lang.RuntimeException: java.security.PrivilegedActionException: com.lombardisoftware.client.delegate.BusinessDelegateException: PreparedStatementCallback; SQL [insert into lsw_system (propkey,propvalue) values (?,?)]; DB2 SQL Error: SQLCODE=-407, SQLSTATE=23502, SQLERRMC=TBSPACEID=2, TABLEID=189, COLNO=1, DRIVER=4.11.69; nested exception is com.ibm.db2.jcc.am.SqlIntegrityConstraintViolationException: DB2 SQL Error: SQLCODE=-407, SQLSTATE=23502, SQLERRMC=TBSPACEID=2, TABLEID=189, COLNO=1, DRIVER=4.11.69 

etc.

This occurred when I tried to access the Process Center URL on a newly built IBM BPM Advanced 8.5.5 environment, and was shown both in the AppCluster JVM log.

I ran through the installation process a number of times, but the problem persisted.

However, rather than giving up hope (!), I thought about the other WAS node-related issue ( referenced in the previous post ).

This started me thinking about the way that the node had been built, specifically the WAS profile.

So I then looked at the response files that I was using to create the DM and Node profiles: -

create
adminUserName=wasadmin
adminPassword=p455w0rd
enableAdminSecurity=true
cellName=ibmbpmCell01
nodeName=ibmbpmCellManager01
profilePath=c:\\IBM\\WebSphere\\AppServer\\profiles\\Dmgr01
winserviceCheck=false
hostName=ibmbpm.uk.ibm.com
profileName=Dmgr01
serverType=DEPLOYMENT_MANAGER
templatePath=c:\\IBM\\WebSphere\\AppServer\\profileTemplates\\BPM\\BpmDmgr

create
hostName=ibmbpm.uk.ibm.com
applyPerfTuningSetting=standard
profileName=AppSrv01
enableAdminSecurity=false
nodeName=ibmbpmNode01
cellName=ibmbpmNode01Cell
serverName=server1
profilePath=c:\\IBM\\WebSphere\\AppServer\\profiles\\AppSrv01
winserviceCheck=false
templatePath=c:\\IBM\\WebSphere\\AppServer\\profileTemplates\\default

Note that I've highlighted the templatePath variable in both cases.

Also note that, whilst the DM profile response file used a BPM-specific template, the node profile did NOT.

Yep, you've guessed it.

I'd erred in the node response file, which should've looked like this: -

create
hostName=ibmbpm.uk.ibm.com
applyPerfTuningSetting=standard
profileName=AppSrv01
enableAdminSecurity=false
nodeName=ibmbpmNode01
cellName=ibmbpmNode01Cell
serverName=server1
profilePath=c:\\IBM\\WebSphere\\AppServer\\profiles\\AppSrv01
winserviceCheck=false
templatePath=C:\\IBM\\WebSphere\\AppServer\\profileTemplates\\BPM\\BpmNode

In other words, I needed to use a BPM-specific profile template.

To prove this theory, I de-federated the node from the cell ( thus removing it from the Deployment Environment ), deleted and recreated the profile, and added the newly created node ( which was using the RIGHT template ) back into the cell and Deployment Environment.

This time around, the AppCluster no longer showed any exceptions, and I was able to happily log into Process Center.

Also, and not coincidentally, I did NOT have the WAS variable-related issues referred to in the previous post.

So, long story short, make sure you use the CORRECT WAS profiles when building an IBM BPM environment :-)