Fixes are available
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 number
JR47892
Reported component name
BPM STANDARD
Reported component ID
5725C9500
Reported release
800
Status
CLOSED PER
PE
NoPE
HIPER
NoHIPER
Special Attention
NoSpecatt
Submitted date
2013-09-23
Closed date
2013-12-09
Last modified date
2013-12-09
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
[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSFTDH","label":"IBM Business Process Manager Standard"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"8.0","Line of Business":{"code":"LOB45","label":"Automation"}}]
Document Information
Modified date:
08 January 2022