Symptom

 Your Process Commander deployment uses Microsoft SQL Server 2005 JDBC driver version 1.2. The application server hangs then crashes. The Process Commander logs show the error java.lang.OutOfMemoryError: Java heap space.
You can encounter this error in a variety of contexts: when copying a rule from a locked RuleSet version to the active version or during process flow transactions.

Out-of-Memory exceptions can arise from a variety of causes. This article describes a specific cause. For a general approach to diagnosing such exceptions, consult the Support Play article cited at the end of this article.

Example

Here"s an example of a log excerpt:

2009-08-05 14:20:01,371 [ttp-8085-Processor25] [ABTHREAD1/] [ MyInsCoNBB:01.05.55] (    pegarules.services.HttpAPI) ERROR lastlkt61|LASTLKT61.rpega.com [email protected] - LASTLKT61.rpega.com: com.pega.pegarules.pub.PRRuntimeError
com.pega.pegarules.pub.PRRuntimeError: PRRuntimeError
      at com.pega.pegarules.engine.context.base.ThreadRunner.runActivitiesAlt(ThreadRunner.java:640)
      at com.pega.pegarules.engine.context.PRThreadImpl.runActivitiesAlt(PRThreadImpl.java:588)
      at com.pega.pegarules.services.HttpAPI.runActivities(HttpAPI.java:1815)
      at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684)
      at java.lang.Thread.run(Thread.java:595)
Caused by: java.lang.OutOfMemoryError: Java heap space
2009-08-05 14:20:01,371 [   PegaRULES-Batch-1] [  STANDARD] [     PegaRULES:05.05] (ine.context.BatchRequestorTask) ERROR   - Batch activity "System-Queue-ServiceLevel.ProcessEvent" threw:
java.lang.OutOfMemoryError: Java heap space

The error and server failure are triggered by a Process Commander limitation that is amplified by a SQL Server JDBC driver 1.2 defect. If an activity calls the stored procedure sppr_rr_class_filtered, the OutofMemoryError: Java heap space occurs because the variable tinyint in the statement declare @class_end_idx tinyint of the stored procedure cannot hold values larger than 255 . The length of the classList most likely exceeds 255.

Solution

Workaround

Upgrading to SQL Server JDBC driver version 2.0 prevents the application server crash and the Out of Memory error, but it does not correct the root cause of the problem, which is the tinyint variable in one of the Declare statements of the stored procedure, sppr_rr_class_filtered.

To correct the problem, edit the stored procedure sppr_rr_class_filtered, replacing the statement declare @class_end_idx tinyint with this statement: declare @class_end_idx smallint. If you edit the stored procedure, you can continue to use SQL Server JDBC driver version 1.2.

Resolution

To edit the stored procedure, sppr_rr_class_filtered, complete the following steps:

1. Open Microsoft SQL Server Management Studio Express.
2. Open the database used by Process Commander.
3. Navigate to Programmability > Stored Procedure.
4. Locate the procedure named dbo.sppr_rr_class_filtered.
5. Right-click the procedure name to display the context menu and click Modify.

   

6. The procedure opens to display its syntax, similar to this:

create procedure sppr_rr_class_filtered
@aRuleType varchar(64),
@aInsId varchar(128),
@aClassList varchar(500),
@aTableName varchar(64)
AS
declare @class_begin_idx smallint
declare @class_end_idx tinyint
declare @class_name varchar(64)
declare @SQL nvarchar(1000)
declare @SQLvars nvarchar(1000)
declare @errorState int
declare @err int

7. Replace the statement declare @class_end_idx tinyint with declare @class_end_idx smallint. The procedure syntax should look similar to this:

create procedure sppr_rr_class_filtered
@aRuleType varchar(64),
@aInsId varchar(128),
@aClassList varchar(500),
@aTableName varchar(64)
AS
declare @class_begin_idx smallint
declare @class_end_idx smallint
declare @class_name varchar(64)
declare @SQL nvarchar(1000)
declare @SQLvars nvarchar(1000)
declare @errorState int
declare @err int

8. From the menu bar, click Execute.

Related Topics

Issue: SQLState: 50002 Arithmetic Overflow error for data type tinyint with SQL Server 2005, JDBC 2.0

Troubleshooting 'SQLException: the server failed to resume' (WebSphere 6.1 and SQL Server)

Support Play: Analyzing OutOfMemory Exceptions

Microsoft Developer Network Reference

int, bigint, smallint, and tinyint (Transact-SQL)