Monitor and manage database threads

You can view a detailed report of thread activity on any running database. Using the information available in the report, you can cancel threads or disconnect users that are negatively affecting database performance.

Monitor and manage database thread activity in Planning Analytics Workspace Local and Planning Analytics Workspace on Cloud

About this task

Note: This procedure applies only to Planning Analytics Workspace Local and Planning Analytics Workspace on Cloud. For details on similar capabilities in Planning Analytics Workspace on Cloud Pak for Data or Planning Analytics Workspace on Planning Analytics as a Service, see Additional administration capabilities for IBM Planning Analytics as a Service

To open a database thread report, click the Databases tab on the Databases page, then click the database for which you want to view a report. Next, click the Threads tab to display the report.

Tip: To view the full report, you might have to collapse the panel on the Databases page.
Illustration of pane being collapsed

When you open the Databases page as an administrator, you can see a list of all databases running in your environment. When you attempt to open a database thread report, Planning Analytics attempts to authenticate you as an administrator on the selected database using the same username and password you used to log into Planning Analytics. If your administrator credentials on the selected database are different from those you used to log into Planning Analytics, authentication will fail and you'll be prompted to provide a valid administrator username and password for the database.

A database thread report provides details on every thread on your database, in table format. You can't change the column widths in the report, but you can hover over any item in the report to see the full value.

You can search for multiple words on the Threads tab. Separate each word with a comma. For example, run,admin,Architect. Search is not case-sensitive.

Database thread report

For each thread you can view these details:

ID
The unique numeric ID of the thread.
Name
The name of the user who initiated the thread.
State
The current thread state.
  • Idle: not using resources
  • Wait: waiting on other threads, blocked
  • Running
  • Commit: committing updates made by a thread
  • Rollback: rolling back to a state before an update
  • Login: when a user logs in and initiates a thread
Type
Indicates a User or System thread.
Function
The API function being executed by the thread.
Wait
The length of time, in seconds, that the thread has been in a wait state.
Elapsed
The total elapsed time of the thread in its current state.
W/R/Ix locks
The number of write, read, and intent-to-write locks that the thread holds.

A write lock allows only the thread that holds the lock to access and write changes to an object. No other thread can read or modify the object until the W lock has been released.

A read lock allows many threads to read from an object at the same time, but does not allow another thread to modify or write to the object until all R locks have been released.

An intent-to-write lock reserves the right for a thread to obtain a write lock on an object when all read locks have been released. Only one thread at a time is allowed to have an Ix lock on an object.

Context
The connected Planning Analytics client that is associated with the thread.
Info

When a thread is in a wait state, this column displays the ThreadID of the thread that is blocking the waiting thread.

Object name
The name of the object upon which the thread is operating.
Object type
The type of object upon which the thread is operating.

You can click Manage Columns icon to selectively hide or show any of these columns in your report. The column display configuration is retained between sessions. The next time you open a thread report for any database, you'll see the same columns.

Procedure

  1. To sort values by any column, click directly on a column name to cycle through the sort options.
  2. To cancel a thread, click the Thread actions button Thread actions button, then click Cancel thread.
    You can cancel any user thread, including threads you own. You cannot cancel system threads.
  3. To disconnect a user, click the Thread actions button Thread actions button, then click Disconnect user.
    You can disconnect user threads, but not system threads. You cannot disconnect yourself.

Monitor and manage database thread activity in Planning Analytics Workspace Classic

About this task

To open a database thread activity report, click the database name on the tile of any running database on the Planning Analytics Monitoring dashboard.

Note: When you open the Databases page as an administrator, you can see tiles for all databases running in your environment. When you click a tile name to open a database thread report, Planning Analytics attempts to authenticate you as an administrator on the database using the same username and password you used to log into Planning Analytics. If your administrator credentials on the database are different from those you used to log into Planning Analytics, authentication will fail and you'll be prompted to provide a valid administrator username and password for the database.

A database thread activity report provides a summary of current thread status, showing the number of threads in Run, Wait, and Other states. The report displays a chart of thread states over time and a table showing details on every thread on your database.

Database activity report

You can hover over any bar on the Database usage chart to see details on users and thread states.

Chart details showing number of users and thread states.

The report also shows the number of connected users and the average number of connections per user, along with database usage metrics. These are the same metrics that you configure on the Thresholds and alerts page.

You can click Download database logs icon to download database log files.

For each thread you can view these details, arranged as columns in the report.

Thread ID
The unique numeric ID of the thread.
State
The current thread state. One of the following states:
  • Idle: not using resources
  • Wait: waiting on other threads, blocked
  • Running
  • Commit: committing updates made by a thread
  • Rollback: rolling back to a state before an update
  • Login: when a user logs in and initiates a thread
Thread info

When a thread is in a wait state, this column displays the ThreadID of the thread that is blocking the waiting thread.

Wait time
The length of time, in seconds, that the thread has been in a wait state.
Thread type
Indicates a User or System thread.
W locks
The number of write locks the thread holds. A write lock allows only the thread that holds the lock to access and write changes to an object. No other thread can read or modify the object until the W lock has been released.
Function
The API function being executed by the thread.
Thread name
The name of the user who initiated the thread.
Object type
The type of object upon which the thread is operating.
Elapsed time
The total elapsed time of the thread in its current state.
Context
The connected client that is associated with the thread. For example, TM1® Architect, TM1 Perspectives, or Cognos® Analytics.
Object name
The name of the object upon which the thread is operating.
R locks
The number of read locks the thread holds. A read lock allows many threads to read from an object at the same time, but does not allow another thread to modify or write to the object until all R locks have been released.
Ix locks
The number of intent-to-write locks the thread holds. An intent-to-write lock reserves the right for a thread to obtain a write lock on an object when all read locks have been released. Only one thread at a time is allowed to have an Ix lock on an object.

You can click Hide Columns icon to selectively hide or show any of these columns in your report.

Procedure

  1. To sort values by any column, click directly on a column name to cycle through the sort options.

    When you apply sorting or hide columns, the column configuration is retained between sessions. The next time you open an activity report for any database, you'll see the same columns using the same sort order.

  2. To cancel a thread, right-click any thread record, then click Cancel thread.
    You can cancel any user thread, including threads you own. You cannot cancel system threads.
  3. To disconnect a user, right-click any thread record, then click Disconnect user.
    You can disconnect user threads, but not system threads. You cannot disconnect yourself.