LiveCycle WebSphere Database Connectivity

Just want to document this in case anyone else runs into issue:

Problem:
- You are trying to initialize the database on WebSphere 6.1.0.19 or higher but the initialization step failed. When you look at the log, you see bunch of these: Caused by: com.ibm.ws.Transaction.IllegalResourceIn2PCTransactionException: Illegal attempt to enlist multiple 1PC XAResources

Note: this may cause problems for folks who had an earlier installation of WebSphere 6.1.0.7, installed LiveCycle, then tried to patch WebSphere up to 6.1.0.19. You will see the Illegal attempt to enlist multiple 1PX XAResources. This may cause instability in your system. I would recommend updating to use the solution below.

Reason:
- In a version between 6.1.0.7 and 6.1.0.19, WebSphere depends to be smart, and try to identify the GenericDataStoreHelper class by looking into the JDBC libraries. So the default DataStore helper class is set to the specific one for the driver, for e.g. MicrosoftSQLServerDataStoreHelper, instead of the GenericDataStoreHelper.

Resolution:
1. login to websphere admin console (http://localhost:9080/admin)
2. Click on resources > data sources
3. click on IDP_DS,
4. For datastore helper class, select to use a custom one, and type the following in: com.ibm.websphere.rsadapter.GenericDataStoreHelper
5. repeat for EDC_DS
6. test data connections
7. run through initialize database from livecycle configuration manager (/opt/adobe/LiveCycle8.2/ConfigurationManager/bin/configurationManager.sh

Setting JBOSS to leverage database fail over

We have a client who runs a clustered SQL Server instance leveraging a clustered JBOSS environment. We ran into problems in LiveCycle when the client fails over the database server from one node to the other. What we ended up doing is to set up jboss to round-robin a set of database connection URL, such that when one fails, it will try the other connection string. Here's how to set up the adobe-ds.xml and mssql-ds.xml to support database fail over:

<datasources>
<ha-local-tx-datasource>
<jndi-name>IDP_DS</jndi-name>
<connection-url>jdbc:sqlserver://DBSERVER1:PORT;DatabaseName=DBNAME|sqlserver://DBSERVER2:PORT;DatabaseName=DBNAME</connection-url>
<url-delimeter>|</url-delimeter>
<driver-class>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver-class>
<!-- newly added to test out connection retry -->
<connection-property name="autoReconnect">true</connection-property>
<connection-property name="maxReconnects">10</connection-property>
<connection-property name="initialTimeout">10</connection-property>
<user-name>DBUSER</user-name>
<password>DBPASSWORD</password>
<min-pool-size>1</min-pool-size>
<max-pool-size>30</max-pool-size>
<blocking-timeout-millis>60000</blocking-timeout-millis>
<idle-timeout-minutes>10</idle-timeout-minutes>
<prepared-statement-cache-size>100</prepared-statement-cache-size>
<transaction-isolation>TRANSACTION_READ_COMMITTED</transaction-isolation>
<!-- sql to call when connection is created
<new-connection-sql>some arbitrary sql</new-connection-sql>
-->
<!-- sql to call on an existing pooled connection when it is obtained from pool-->
<check-valid-connection-sql>select count(*) from edcprincipalentity</check-valid-connection-sql>

<!-- corresponding type-mapping in the standardjbosscmp-jdbc.xml (optional) -->
<metadata>
<type-mapping>MS SQLSERVER2000</type-mapping>
</metadata>
</ha-local-tx-datasource>
</datasources>