IBM Support

JR47892: BPM QUERIES HANG WHEN RUN IN MICROSOFT SQL SERVER

Subscribe

You can track all active APARs for this component.

 

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":"BU053","label":"Cloud & Data Platform"},"Product":{"code":"SSFTDH","label":"IBM Business Process Manager Standard"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"8.0","Edition":"","Line of Business":{"code":"LOB36","label":"IBM Automation"}}]

Document Information

Modified date:
09 December 2013