IBM Support

JR47870: OPTIMIZE REPOSITORY QUERY FOR TOOLKIT SNAPSHOTS AND THEIR DEPENDENCIES

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as fixed if next.

Error description

  • In Process Center console, displaying the Process Application
    or Toolkit Snapshot page is very slow when number of snapshots
    are very large. Page goes blank after while and remains blank.
    Even switching to different tabs or refreshing the page does
    not resolve the issue.
    .
    If the thread is slow enough to complete a WSVR0605W message
    will be generated with this java stack.
    .
    [11/21/13 7:04:19:639 EST] 00000002 ThreadMonitor W   WSVR0605W:
     Thread "WebContainer : 5" (0000009c) has been active for 718237
    milliseconds and may be hung.  There is/are 1 thread(s) in
    total in the server that may be hung.
    Java Stack
    at java/net/SocketInputStream.socketRead0(Native Method)
     at
    java/net/SocketInputStream.read(SocketInputStream.java:140(Compi
    led
    Code))
     at com/ibm/db2/jcc/t4/z.b(z.java:199(Compiled Code))
     at com/ibm/db2/jcc/t4/z.c(z.java:259(Compiled Code))
     at com/ibm/db2/jcc/t4/z.c(z.java:372(Compiled Code))
     at com/ibm/db2/jcc/t4/z.v(z.java:1147(Compiled Code))
     at com/ibm/db2/jcc/t4/eb.b(eb.java:140(Compiled Code))
     at com/ibm/db2/jcc/t4/c.Xb(c.java:294(Compiled Code))
     at com/ibm/db2/jcc/am/o.g(o.java:324(Compiled Code))
     at com/ibm/db2/jcc/t4/a.g(a.java:626(Compiled Code))
     at com/ibm/db2/jcc/am/o.a(o.java:206(Compiled Code))
     at com/ibm/db2/jcc/am/kn.b(kn.java:3799(Compiled Code))
     at com/ibm/db2/jcc/am/kn.fc(kn.java:702(Compiled Code))
     at com/ibm/db2/jcc/am/kn.executeQuery(kn.java:672(Compiled
    Code))
      at
    com/ibm/ws/rsadapter/jdbc/WSJdbcPreparedStatement.pmiExecuteQuer
    y(WSJdbcPreparedStatement.java:1110(Compiled Code))
      at
    com/ibm/ws/rsadapter/jdbc/WSJdbcPreparedStatement.executeQuery(W
    SJdbcPreparedStatement.java:712(Compiled Code))
      at
    org/springframework/jdbc/core/JdbcTemplate$1.doInPreparedStateme
    nt(JdbcTemplate.java:648(Compiled Code))
      at
    org/springframework/jdbc/core/JdbcTemplate.execute(JdbcTemplate.
    java:591(Compiled Code))
      at
    org/springframework/jdbc/core/JdbcTemplate.query(JdbcTemplate.ja
    va:641(Compiled Code))
      at
    org/springframework/jdbc/core/JdbcTemplate.query(JdbcTemplate.ja
    va:670(Compiled Code))
      at
    org/springframework/jdbc/core/JdbcTemplate.query(JdbcTemplate.ja
    va:678(Compiled Code))
      at
     com/lombardisoftware/server/ejb/repositoryservices/SnapshotSupp
    ort.getToolkitSnapshots(SnapshotSupport.java:280)
      at com/lombardisoftware/server/ejb/repositoryservices/
    RepositoryServicesCore.getToolkitSnapshots
    (RepositoryServicesCore.java:441)
    .
    .---------------------------------------------------------
       This is the query being run that takes so long to complete.
    SELECT toolkitSnapshots.*, projects.*
                FROM
                    (SELECT snapshot.SNAPSHOT_ID,
                            snapshot.CREATED_BY_USER_ID AS
     SNAPSHOT_CREATED_BY_USER_ID,
                            snapshot.CREATED_ON AS
    SNAPSHOT_CREATED_ON,
                            snapshot.NAME AS SNAPSHOT_NAME,
                            snapshot.DESCRIPTION AS
    SNAPSHOT_DESCRIPTION,
                            snapshot.BRANCH_ID,
                            snapshot.SEQ_NUM,
                            snapshot.CHANGE_DATA,
                            snapshot.ERROR_COUNTER,
                            snapshot.IS_ARCHIVED AS
    SNAPSHOT_IS_ARCHIVED,
                            snapshot.ARCHIVED_ON AS
    SNAPSHOT_ARCHIVED_ON,
                            snapshot.STATUS
                        FROM lsw_branch branch INNER JOIN
    lsw_snapshot
    snapshot ON branch.branch_id = snapshot.branch_id
                        WHERE branch.branch_id = ? AND
                            snapshot.is_archived = ? AND
          snapshot.name IS NOT NULL
                    ) toolkitSnapshots
                    LEFT OUTER JOIN
                    (SELECT project.PROJECT_ID,
                            project.CREATED_BY_USER_ID AS
     PROJ_CREATED_BY_USER_ID,
                            project.CREATED_ON AS PROJ_CREATED_ON,
                            project.LAST_MODIFIED AS
    PROJ_LAST_MODIFIED,
                            project.LAST_MODIFIED_BY_USER_ID AS
     PROJ_LAST_MODIFIED_BY_USER_ID,
                            project.IS_ARCHIVED AS PROJ_IS_ARCHIVED,
                            project.ARCHIVED_ON AS PROJ_ARCHIVED_ON,
                            project.NAME as PROJ_NAME,
                            project.DESCRIPTION AS PROJ_DESCRIPTION,
                            project.SHORT_NAME,
                            project.RELEASE_PROCESS_ID,
                            project.RELEASE_PROCESS_BRANCH_ID,
                            project.IS_TOOLKIT,
                            project.IS_BRANCHING_ENABLED,
                            project.IS_HIDDEN,
          project.IS_SHARED,
          project.SHARED_BY_USER_ID AS PROJ_SHARED_BY_USER_ID,
          project.SHARED_ON AS PROJ_SHARED_ON,
                            dependency.target_snapshot_id as
     DependencySnapshotId,
          versions.start_seq_num,
          versions.end_seq_num
                     FROM lsw_project project INNER JOIN lsw_branch
    branch
    ON project.project_id = branch.project_id
                            INNER JOIN lsw_po_versions versions ON
    branch.branch_id = versions.branch_id
                            INNER JOIN lsw_project_dependency
    dependency ON
    versions.po_version_id = dependency.version_id
          INNER JOIN lsw_snapshot snapshot on project.project_id =
     snapshot.project_id
                     WHERE project.is_archived = 'F'
         AND snapshot.seq_num >= start_seq_num
         AND ((snapshot.name is not null and (end_seq_num >
    snapshot.seq_num
    OR end_seq_num is null)) OR (snapshot.name is null and
    end_seq_num is
     null))
         AND dependency.is_active = 'T'
                    ) projects ON projects.DependencySnapshotId =
     toolkitSnapshots.snapshot_id
        ORDER BY toolkitSnapshots.snapshot_created_on DESC
    
                                     TYPE FORWARD ONLY (1003)
                                     CONCUR READ ONLY (1007)
    

Local fix

  • N/A
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:  Users who have very large amount of         *
    *                  snapshots of process applicatons or         *
    *                  toolkits.                                   *
    ****************************************************************
    * PROBLEM DESCRIPTION: Displaying the Process Applicaton or    *
    *                      Toolkit snapshots page is very slow.    *
    *                      Page may goes blank after while.        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    ****************************************************************
    Slowness of snapshot page is caused by slow database query
    which is looking at every snapshot of every Process
    Application or Toolkit in the repository. Snapshot information
    is delivered along with its dependencies.
    

Problem conclusion

Temporary fix

  • No, but there is a partial workaround. Increase the refresh
    rate to maxium value to stop the page from blanking out
    when refresh occurs.
    

Comments

  • DB query is split such that only snapshot information is
    retrieved when the page first loads. Dependencies are only
    loaded if user expand the "Where used" twistie.
    

APAR Information

  • APAR number

    JR47870

  • Reported component name

    BPM STANDARD

  • Reported component ID

    5725C9500

  • Reported release

    801

  • Status

    CLOSED FIN

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2013-09-19

  • Closed date

    2013-10-23

  • Last modified date

    2015-04-02

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

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

Fix information

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.1","Edition":"","Line of Business":{"code":"LOB36","label":"IBM Automation"}}]

Document Information

Modified date:
02 April 2015