JR47892: BPM QUERIES HANG WHEN RUN IN MICROSOFT SQL SERVER

Version 8.0.1 Fix Pack 2 for the IBM Business Process Manager products
Version 8.5.0 Fix Pack 1 for the IBM Business Process Manager products
Version 8.5 Refresh Pack 5 for the IBM Business Process Manager products

APAR status

• Closed as program error.

Error description

• The threads running the BPM queries are logged in WSVR0605W
  messages.  The java stack of these messages are
  .
  "WebContainer : 1" (00000034) has been active for 641449
  milliseconds
  and may be hung.  There is/are 1 thread(s) in total in the
  server that
  may be hung.
      at java.net.SocketInputStream.socketRead0(Native Method)
      at
  java.net.SocketInputStream.read(SocketInputStream.java:140)
      at
  com.microsoft.sqlserver.jdbc.TDSChannel.read(IOBuffer.java:1525)
      at
  com.microsoft.sqlserver.jdbc.TDSReader.readPacket(IOBuffer.java:
  3274)
      at
  com.microsoft.sqlserver.jdbc.TDSCommand.startResponse(IOBuffer.j
  ava:4437
  )
      at
  com.microsoft.sqlserver.jdbc.SQLServerResultSet$CursorFetchComma
  nd.doExe
  cute(SQLServerResultSet.java:4750)
      at
  com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:40
  26)
      at
  com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(
  SQLServe
  rConnection.java:1416)
      at
  com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(S
  QLServer
  Statement.java:185)
      at
  com.microsoft.sqlserver.jdbc.SQLServerResultSet.doServerFetch(SQ
  LServerR
  esultSet.java:4787)
      at
  com.microsoft.sqlserver.jdbc.SQLServerResultSet.next(SQLServerRe
  sultSet.
  java:980)
      at
  com.ibm.ws.rsadapter.jdbc.WSJdbcResultSet.next(WSJdbcResultSet.j
  ava:3120
  )
      at
  com.lombardisoftware.server.eai.engine.ResultSetSerializationHan
  dler.res
  ultSet2XMLString(ResultSetSerializationHandler.java:95)
      at teamworks.Queries.executeSQL(Queries.java:256)
      at teamworks.Queries.executeSQL(Queries.java:306)
      at teamworks.Queries.executeSQL(Queries.java:310)
  
  
  
  1. [7/11/13 5:00:29:702 EDT] 0000001e clientinfoplu >
  executeQuery
  Entry
  
  com.ibm.ws.rsadapter.jdbc.WSJdbcStatement@c65a7e6
  
                select S.snapshot_id, I.count_i from
                  (select snapshot_id from lsw_snapshot
                      where name is not null) S
                  left outer join
                  (select snapshot_id, count(*) as count_i from
  lsw_bpd_instance
                      where execution_status=1 or
  execution_status=6 or
  execution_status=3
                      group by snapshot_id) I
                  on S.snapshot_id=I.snapshot_id
  
  The analysis I was doing last night shows that this query is
  running on
  each (at least on four of them, I have not finished the work to
  confirm
  if it is in fact all) of the servers when the problem happened
  
  2. [7/11/13 5:14:55:536 EDT] 00000065 clientinfoplu >
  executeQuery
  Entry
  
  com.ibm.ws.rsadapter.jdbc.WSJdbcStatement@ecaaff6
                                   select i.bpd_instance_id as
  instanceId, i.instance_name as instanceName, bpd.bpd_id as
  bpdId,
  iprj.short_name as instanceProcessApp, isnap.name as
  instanceSnapshot,
  i.snapshot_id as instanceSnapshotId, t.subject as taskSubject,
  tnarr.narrative as taskNarrative, t.due_date as taskDueDate,
  t.rcvd_datetime as taskReceivedDate, tpriority.name as
  taskPriority,
  tstatus.name as taskStatus, treceivedUser.user_name as
  taskReceivedFrom, t.close_datetime as taskClosedDate,
  tuser.user_name
  as assignedToUser, trole.display_name as
  assignedToRoleDisplayName,
  t.activity_name as taskActivityName, t.task_id as taskId,
  t.at_risk_date as taskAtRiskTime, case when (t.at_risk_date IS
  NOT NULL
  AND t.at_risk_date < GETDATE()) OR t.due_date < GETDATE() THEN
  1 ELSE 0
  END as taskIsAtRisk, t.attached_form_ref as
  taskAttachedInfoPathFormRef, t.attached_ext_activity_ref as
  taskAttachedExtActivityRef, cbb.bpd_id as taskCreatedByBpdId,
  t.created_by_bpd_flow_object_id as taskCreatedByBpdFlowObjectId,
  trole.group_name as assignedToRole, istatus.name as
  instanceStatus,
  tpriority.ranking as taskPriorityRanking from lsw_task t with
  (nolock)
  left join lsw_bpd_instance i with (nolock) on t.bpd_instance_id
  =
  i.bpd_instance_id left join lsw_task_status_codes tstatus on
  t.status =
  tstatus.status_value left join lsw_bpd_status_codes istatus on
  i.execution_status = istatus.status_id left join lsw_priority
  tpriority
  on t.priority_id = tpriority.priority_id left join lsw_bpd bpd
  on
  i.cached_bpd_version_id = bpd.version_id left join lsw_usr_xref
  tuser
  on t.user_id = tuser.user_id left join lsw_usr_grp_xref trole on
  t.group_id = trole.group_id left join lsw_usr_xref
  treceivedUser on
  t.rcvd_from = treceivedUser.user_id left join lsw_task_narr
  tnarr on
  t.task_id = tnarr.task_id left join lsw_bpd cbb on
  t.cached_cbb_version_id = cbb.version_id left join lsw_snapshot
  isnap
  on i.snapshot_id = isnap.snapshot_id left join lsw_project iprj
  on
  isnap.project_id = iprj.project_id  where ( tstatus.name in (
  'New','Received' ) AND istatus.name = 'Active' AND ( t.user_id
  = 1152
  OR t.task_id in (
                  select t.task_id
                  from lsw_task t
                  inner join lsw_usr_grp_mem_xref m
                    on t.group_id = m.group_id
                  where m.user_id = 1152
                    and t.user_id = -1
              ) OR t.task_id in (
                  select t.task_id
                  from lsw_task t
                  inner join lsw_grp_grp_mem_exploded_xref x
                    on t.group_id = x.container_group_id
                  inner join lsw_usr_grp_mem_xref m
                    on m.group_id = x.group_id
                  where m.user_id = 1152
                    and t.user_id = -1
              ) ) )  order by taskIsAtRisk desc, taskDueDate,
  taskId,
  instanceId, taskPriorityRanking
  

Local fix

• You can add NOLOCK to the queries in the custom100.xml file
  

Problem summary

• Distributed Transaction Coordinator blocks in BPM production
  Database with Microsoft SQL Serevr. WSVR0605W is reported in the
  system log.
  
  PROBLEM DETAILED DESCRIPTION:
  The database locking of Microsoft SQL Server causes in some
  situations long waits and deadlocks for some queries.
  

Problem conclusion

• The locking of the involved queries has been changed.
  
  FIX AVAILABILITY:
  iFix for 8.0.0.0 is available on Fix Central, search for APAR
  JR47892 at http://www.ibm.com/support/fixcentral/
  Fix is also targetted for inclusion in next fixpack for BPM
  BPM 8.0.1, BPM 8.5.0
  
  When obtaining any of the above fixes, be sure to download the
  accompanying readme, for itself, and any prerequisite fixes, and
  review them thorougly.
  

Temporary fix

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

Fix information

• Fixed component name

  BPM STANDARD

• Fixed component ID

  5725C9500

Applicable component levels

• R800 PSY

     UP