FileNet Content Engine upgrade/migration from 4.5 to 5.1 fails attempting to initialize the stored procedure xp_sqljdbc_xa_init
Errors in the Application Server log:
2011-09-23T20:16:06.357Z 5ADE5ADE GCD FNRCE0000W - WARN A database availability issue was encountered when checking the GCD's epoch - using last cached instance. Exception was: The database access failed with the following error: ErrorCode 0, Message 'enlist: caught Exception'
2011-09-23T20:16:06.357Z 5ADE5ADE GCD FNRCE0000I - INFO Setting HostNames on ServerInstance 'server1'.
2011-09-23T20:16:06.372Z 5ADE5ADE ENG FNRCD0009E - ERROR Failure occurred checking topology objects - rolling back transaction.
com.filenet.api.exception.EngineRuntimeException: FNRCD0009E: DB_ERROR:
The database access failed with the following error: ErrorCode 0,
Caused by: java.sql.SQLException: enlist: caught Exception
Caused by: javax.resource.ResourceException: enlist: caught Exception
Caused by: javax.transaction.SystemException: XAResource start association error:XAER_PROTO
... 60 more
Caused by: javax.transaction.xa.XAException
... 64 more
Either the required JDBC distributed transaction script, (xa_install.sql)wasn't run or it wasn't run against the master db.
Windows and MS SQL
Diagnosing the problem
When the JDBC distributed transaction script (xa_install.sql) is run against the master db, the script installs sqljdbc_xa.dll as an extended stored procedure and creates the SqlJDBCXAUser role in the Master database. If the dll or role are missing from the master DB, this would confirm the script was not run, or not run against the master db.
Resolving the problem
Follow the defined task in the P8 Platform Install and Upgrade Guide via the Info Center link:
Steps from the Info Center:
You must enable XA transactions on every Microsoft SQL Server that will have a Content Engine database.
To enable XA transactions for Content Engine databases.
- Download the Microsoft SQL Server JDBC Driver that is referenced in the IBM FileNet P8 Hardware and Software Requirements document for Content Engine SQL Server databases.
Tip: Installation procedures for JDBC settings can vary by release. See the Microsoft website for full details.
- Copy the sqljdbc_xa.dll from the JDBC installation directory to the binn folder of the instance, although a pre-2.0 version of the driver also functions correctly from the tools\binn folder. For the 32-bit version of Microsoft SQL Server , use the sqljdbc_xa.dll file in the x86 folder. For the 64-bit version of Microsoft SQL Server, use the sqljdbc_xa.dll file in the x64 folder.
- Log on as the sa administrator or as a user with equivalent permissions and execute the database script xa_install.sql on the master database on every SQL Server instance that will participate in distributed transactions.
Important: Use SQL Server database credentials, not Windows credentials, to log on. Windows Integrated Logon to SQL Server is not supported with IBM® FileNet® P8.
This script installs sqljdbc_xa.dll as an extended stored procedure and creates the SqlJDBCXAUser role in the Master database.
- Add each database account (ce_db_user) that Content Engine uses to access SQL Server to the SqlJDBCXAUser role. This action grants permissions to those accounts to participate in distributed transactions with the JDBC driver.
- Click Component Services
- My Computer
- Security Configuration and ensure that XA transactions are enabled.