Space View (DB2 LUW Performance Analyst)

From RapidSQL
Jump to: navigation, search

Go Up to IBM DB2 (W/U/L) Performance Analyst statistics

While DBAs focus on memory settings and tuning SQL, they often forget just how dangerous and insidious storage problems can be. This is not a good mindset because storage headaches can play a major role in wrecking an otherwise well-running database.

Storage problems generally take one of two forms:

  • The 'hit-the-wall' variety that can bring things to a complete standstill
  • The 'performance vampire' kind that slowly drains the performance of a database over time

Storage problems can quickly lead to performance problems, such as when a transaction log runs out of free space. But storage problems can also silently work behind the scenes to slowly but surely rob a database of its performance.

In addition to a Home page, the Space category of DB2 Performance Analyst includes the following tabbed pages:

Home

The Home page of the Space performance category view displays statistics i the following categories:

Tablespace Analysis Pane

The Tablespace Analysis section includes a list of all of the tablespaces in the database. The following information is presented for each tablespace:

  • Tablespace Name: The name of the tablespace
  • Type: system managed (SMS) or database managed (DMS) tablespace
  • State: This element describes the current state of the tablespace
  • Used Space: The space currently in use on tablespace
  • Free Space: The space currently free on the tablespace. (This is not applicable to SMS tablespaces)
  • Content Type: The type of data that can be stored on the tablespace. It can be one of the following:
  • Any (any type of data)
  • Long (LOB/ Structured type / Index data)
  • System Temporary (work area used by database manager to perform operations like sorts and joins.
  • User Temporary (Stores declared global temporary tables)
  • Page Size: Size of pages used by the tablespace
  • Extent Size: Number of pages that will be written to a container before writing to the next container on the tablespace
  • Prefetch Size: Number of pages that will be read from the tablespace by the prefetchers in a single prefetch
  • Total Space: Total space (Used + Free) used by the tablespace on the storage device(s). This will be the same as used space for SMS tables
  • High Watermark: Highest amount of used space for the tablespace (This is not applicable to SMS tablespaces)

Other statistics appearing in this section include:

Total Tablespaces

The total tablespaces statistic is the total number of tablespaces in the database.

SMS Tablespaces

The SMS tablespaces statistic is the number of system managed (SMS) tablespaces in the database.

DMS Tablespaces

The DMS tablespaces statistic is the number of database managed (DMS) tablespaces in the database.

Bottleneck Analysis Pane

Space shortfalls or inaccessible tablespaces can quickly bring a database to a halt. The space bottleneck analysis section provides an at a glance look at the items that may require your attention. You can drilldown to the details pages to see further details of the various metrics.

The metrics presented here are:

The following statistics, available on this pane, are duplicates of statistics found on the Bottleneck Analysis Pane of the Home View (DB2 LUW Performance Analyst) page:

DMS Tablespaces at Utilization >80%

The DMS tablespaces at utilization greater than 80% metric gives the number of DMS tablespaces in the database where the used space on the tablespace is more than 80% of the total available space.

Location

IBM DB2 (W/U/L) Performance Analyst statistics > Space View (DBArtisan - DB2 LUW Performance Analyst) > Home > Bottleneck Analysis Pane

Metrics

This metric is an indicator that some of the tablespaces in your database may be running out of space. You should drill down to the Space metrics to identify the tablespaces that may be at the critical utilization level. In tablespaces where there are little on no inserts after the initial data is loaded, little or no free space may have been left on purpose. In such cases, it is normal to see a high utilization

Correction

If this metric goes beyond the normal operational value for your database, consider creating additional space on the DMS tablespaces that are running out of space. You can do this by performing one or more one of the following tasks: Clean up unused space by deleting the tables/indexes that are no longer required. Resize or extend the existing containers in the tablespaces. Add new containers to the tablespaces.

Inaccessible Tablespaces

The inaccessible tablespaces statistic is the number of tablespaces that are offline.

Location

IBM DB2 (W/U/L) Performance Analyst statistics > Space View (DBArtisan - DB2 LUW Performance Analyst) > Home > Bottleneck Analysis Pane

Metrics

Sometimes a problem (such as inaccessible containers) will cause a tablespace to go offline. Even when the problem is corrected, such tablespaces are not accessible by any application and need to be brought online explicitly.

Correction

From Tablespace Analysis section, identify the tablespaces whose state is set to Offline Drill down to space details to make sure all of the containers for these tablespaces are accessible. If they are not, first fix the problem with the containers Disconnect all applications from the database (you can use DBArtisan DB Monitor). Reconnect to the database. Use the ALTER TABLESPACE .. SWITCH ONLINE command to bring the tablespace back online. If the tablespace can be brought up successfully after issuing the command, or if the tablespace was not in the OFFLINE state to begin with, DB2 returns an SQLCODE of 0. If the tablespace cannot be brought up successfully because there are still problems with one or more of the containers, DB2 returns an SQLCODE of --293. You can force the database to restart by using the RESTART ... DROP PENDING TABLESPACE, but have to drop any faulty table spaces afterward.

SQL Analysis Pane

The metrics presented here are:

DDL Statements Executed

This metric indicates the number of SQL Data Definition Language (DDL) statements that have been executed.

Location

IBM DB2 (W/U/L) Performance Analyst statistics > Space View (DBArtisan - DB2 LUW Performance Analyst) > Home > SQL Analysis Pane

Metrics

You can use this element to determine the level of database activity at the application or database level. DDL statements are expensive to run because of their impact on the system catalog tables. As a result, if the value of this element is high, you should determine the cause, and possibly restrict this activity from being performed. DDL statements can also impact: The catalog cache, by invalidating table descriptor information and authorization information that are stored there and causing additional system overhead to retrieve the information from the system catalogs The package cache, by invalidating sections that are stored there and causing additional system overhead due to section recompilation.

Insert/Updates/Deletes Statements Executed

This is the number of SQL UPDATE, INSERT, and DELETE statements that were executed.

Location

IBM DB2 (W/U/L) Performance Analyst statistics > Space View (DBArtisan - DB2 LUW Performance Analyst) > Home > SQL Analysis Pane

Metrics

You can use this element to determine the level of database activity at the application or database level. This information can be useful for analyzing application activity and throughput.

Select Statements Executed

This statistic is the number of SQL SELECT statements that were executed.

Location

IBM DB2 (W/U/L) Performance Analyst statistics > Space View (DBArtisan - DB2 LUW Performance Analyst) > Home > SQL Analysis Pane

Metrics

You can use this element to determine the level of database activity at the application or database level. This information can be useful for analyzing application activity and throughput.

Total SQL Executed

You can use this element to calculate the total number of SQL statements at the database level.

Location

IBM DB2 (W/U/L) Performance Analyst statistics > Space View (DBArtisan - DB2 LUW Performance Analyst) > Home > SQL Analysis Pane

Metrics

This element can also help you in determine reasons for poor performance. Failed statements mean time wasted by the database manager, and as a result, lower throughput for the database. You can use this element in conjunction with the other SQL metrics to understand the portion of SQL activity that can be attributed to various statement types.

Storage Analysis Pane

While DBAs focus on memory settings and tuning SQL, they frequently forget how dangerous and insidious storage problems can be. This is not a good mindset because storage headaches can play a major role in wrecking an otherwise well-running database. Storage problems generally take one of two forms:

  • The ‘hit-the-wall’ variety that can bring things to a complete standstill.
  • The ‘performance vampire’ kind that slowly drains the performance of a database over time.

Storage problems have the capability to bring the curtain down on a database very quickly, as in the case of a transaction log running out of free space. But storage problems can also silently work behind the scenes to slowly, but surely rob a database of its performance. For example, a hub table in a busy database might be accessed very quickly when an application is first given life, but over time, if it develops a heavy forwarded row problem, it can cause things to run very differently.

The Storage Analysis section of the Performance Analyst Home page displays the total used and free space of all DMS tablespaces in the database. It also provides the total and available log space for the database.

The following ratios succinctly communicate the general overall performance levels of the datasource:

The following statistics, available on this pane, duplicate statistics available on the Storage Analysis Pane of the Home page:

Log Analysis Pane

The Log Analysis section gives you at-a-glance information about all your log configuration parameters and the high watermarks for the log space usage. You can use this information to quickly determine the logging mechanism in place for your database and if the high watermarks are getting too close to the maximum allocations for the log space.

The metrics presented here are:

Log Configuration

The log configuration metric gives a list of space related log configuration parameters for your database. The values of these parameters determine how the logger uses storage space on the database:

  • Block on Log Disk Full(blk_log_dsk_ful): this configuration parameter can be set to prevent disk full errors from being generated when DB2 cannot create a new log file in the active log path. Instead, DB2 will attempt to create the log file every five minutes until it succeeds. After each attempt, DB2 will write a message to the administration notification log. The only way to confirm that your application is hanging because of a log disk full condition is to monitor the administration notification log. Until the log file is successfully created, any user application that attempts to update table data is not able to commit transactions. Read-only queries may not be directly affected; however, if a query needs to access data that is locked by an update request or a data page that is fixed in the bufferpool by the updating application, read-only queries also appear to hang.
Setting blk_log_dsk_ful to YES causes applications to hang when DB2 encounters a log disk full error. You are then able to resolve the error and the transaction can continue. A disk full situation can be resolved by moving old log files to another file system, or by increasing the size of the file system so that hanging applications can complete.
If blk_log_dsk_ful is set to NO, a transaction that receives a log disk full error fails and is rolled back. In some cases, the database comes down if a transaction causes a log disk full error.
  • Log File Size(logfilsiz): specifies the size of each configured log, in number of 4-KB pages.
There is a 256-GB logical limit on the total active log space that you can configure. This limit is the result of the upper limit on logfilsiz, which is 262144, and the upper limit on (logprimary + logsecond), which is 256.
The size of the log file has a direct bearing on performance. There is a performance cost for switching from one log to another. So, from a pure performance perspective, the larger the log file, size the better. This parameter also indicates the log file size for archiving. In this case, a larger log file is size it not necessarily better, since a larger log file size may increase the chance of failure or cause a delay in log shipping scenarios. When considering active log space, it may be better to have a larger number of smaller log files. For example, if there are two very large log files and a transaction starts close to the end of one log file, only half of the log space remains available.
Every time a database is deactivated (all connections to the database are terminated), the log file that is currently being written is truncated. So, if a database is frequently being deactivated, it is better not to choose a large log file size because DB2 will create a large file only to have it truncated. You can use the ACTIVATE DATABASE command to avoid this cost, and having the bufferpool primed will also help with performance.
Assuming that you have an application that keeps the database open to minimize processing time when opening the database, the log file size should be determined by the amount of time it takes to make offline archived log copies.
Minimizing log file loss is also an important consideration when setting the log size. Archiving takes an entire log. If you use a single large log, you increase the time between archiving. If the medium containing the log fails, some transaction information will probably be lost. Decreasing the log size increases the frequency of archiving but can reduce the amount of information loss in case of a media failure since the smaller logs before the one lost can be used.
  • Log Retain(logretain): if logretain is set to RECOVERY, archived logs are kept in the database log path directory and the database is considered to be recoverable, meaning that rollforward recovery is enabled.
Note: The default value for the logretain database configuration parameter does not support rollforward recovery and must be changed if you are going to use it.
  • User Exit(userexit): causes the database manager to call a user exit program for archiving and retrieving logs. The log files are archived in a location that is different from the active log path. If userexit is set to ON, rollforward recovery is enabled.
Note: The userexit database configuration parameter must be enabled to set logsecond parameter to-1
  • Primary Logs(logprimary): specifies the number of primary logs of size logfilsz that will be created.
A primary log, whether empty or full, requires the same amount of disk space. Thus, if you configure more logs than you need, you use disk space unnecessarily. If you configure too few logs, you can encounter a log-full condition. As you select the number of logs to configure, you must consider the size to make each log and whether your application can handle a log-full condition. The total log file size limit on active log space is 256 GB.
If you are enabling an existing database for rollforward recovery, change the number of primary logs to the sum of the number of primary and secondary logs, plus 1. Additional information is logged for LONG VARCHAR and LOB fields in a database enabled for rollforward recovery.
  • Secondary Logs(logsecond): specifies the number of secondary log files that are created and used for recovery, if needed.
If the primary log files become full, secondary log files (of size logfilsiz) are allocated, one at a time as needed, up to the maximum specified by this parameter. If this parameter is set to -1, the database is configured with infinite active log space. There is no limit on the size or number of in-flight transactions running on the database.
Note: If this parameter is set to -1, crash recovery time may be increased since DB2 may need to retrieve archived log files.

Location

IBM DB2 (W/U/L) Performance Analyst statistics > Space View (DBArtisan - DB2 LUW Performance Analyst) > Home > Log Analysis Pane

High Watermarks

The high watermarks statistic shows the maximum total and secondary log space used by the database.

Location

IBM DB2 (W/U/L) Performance Analyst statistics > Space View (DBArtisan - DB2 LUW Performance Analyst) > Home > Log Analysis Pane

Metrics

The total log space used high watermark in conjunction with the total log space allocated, helps you determine if applications have come close to using the total log space available to your database. The total secondary log space used high watermark in conjunction with total log space used high watermark helps you determine your current dependency on secondary logs.

Correction

High values of either high watermarks can indicate that you either need to increase the log space on the database or that the applications using the database need to perform more frequent COMMITS. You can increase the overall log space available by adjusting one or more of the following database configuration parameters: logfilsiz, logprimary, logsecond, and logretain.

Usage tab

The Space Detail Usage tab section shows you the list of tablespaces for the database. The following information is presented for each tablespace:

Tablespace Details

  • ID: Internal DB2 Tablespace identifier.
  • Name: Tablespace Name
  • Type: Tablespace Type – SMS (System Managed Space) or DMS (Database Managed Space)
  • Content Type: Shows the type of data that can be stored on the tablespace. The valid types are:
    • Any meaning can store any type of data
    • Sys Temp meaning, used internally by DB2 to store intermediate result data
    • User Temp meaning, can be used to create temporary tables
    • Long meaning, can be used to store LOB/LONG/Index data
  • State: Can be one or more of the following:
    • Normal (see the definition SQLB_NORMAL in sqlutil.h)
    • Quiesced: SHARE
    • Quiesced: UPDATE
    • Quiesced: EXCLUSIVE
    • Load pending
    • Delete pending
    • Backup pending
    • Roll forward in progress
    • Roll forward pending
    • Restore pending
    • Recovery pending (not used)
    • Disable pending
    • Reorg in progress
    • Backup in progress
    • Storage must be defined
    • Restore in progress
    • Offline and not accessible
    • Drop pending
    • Storage may be defined
    • Storage Definition is in 'final' state
    • Storage Definition was changed prior to rollforward
    • DMS rebalancer is active
    • TBS deletion in progress
    • TBS creation in progress
  • Page Size: The size of tablespace pages
  • Extent Size: Number of pages that will be written to a container before writing to the next container on the tablespace
  • Prefetch Size: The maximum number of pages the prefetcher gets from the disk at a time
  • Usable Space: The total space in a table space minus overhead space. (Note: For SMS tablespaces, this element will have the same value as Used Space).
  • Used Space: The total space in the tablespace that is currently used in the tablespace (including overhead)
  • Free Space: The total space that is free to be used for storage of data. This element is not applicable for SMS tablespace. free space for an SMS tablespace is the OS file system free space for the SMS containers.
  • Total Space: The total space used by the tablespace
  • Pending Free Pages: The number of pages in the tablespace that would become free if all pending transactions are committed or rolled back and new space is requested for an object. (Applies only to DMS tablespaces)
    1. Containers: The number of containers in the tablespace
  • Usable Pages: The total pages in a table space minus overhead pages. (Note: For SMS tablespaces, this element will have the same value as Used Pages).
  • Used Pages: The total pages in the tablespace that is currently used in the tablespace (including overhead)
  • Free Pages: The total pages that are free to be used for storage of data. This element is not applicable for SMS tablespace. Free pages for an SMS tablespace are dependent on the OS file system free space for the SMS containers.
  • Total Pages: The total pages used by the tablespace
  • Page HWM: For DMS, this element represents the page number of the first free extent following the last allocated extent of a table space. Note that this is not really a "high water mark", but rather a "current water mark", since the value can decrease. For SMS, this is not applicable.

Location

IBM DB2 (W/U/L) Performance Analyst statistics > Space View (DBArtisan - DB2 LUW Performance Analyst) > Usage tab IBM DB2 (W/U/L) Performance Analyst statistics > Space View (DBArtisan - DB2 LUW Performance Analyst) > Rebalance tab

Container Details

This section shows the list of containers for the selected tablespace. The information presented here includes:

  • ID: A value that uniquely identifies the container within the tablespace
  • Name: The name of the container. Typically this is the full path of the file/directory/device where the container exists.
  • Type: The type of container. An SMS Container will be a directory. A DMS Containers will be a raw device/file/stripped disk/ or stripped file. Together with Container name, and partition, this metric identifies the physical location of the container.
  • DB Partition: The database partition where the container exists
  • Usage Pages: Usable pages in the container (Applicable to DMS Tablespaces only).
  • Total Pages: Total pages in the container
  • Stripe Set: The stripe set that a container belongs to.
  • Accessible: This element describes if a container is accessible or not

Location

IBM DB2 (W/U/L) Performance Analyst statistics > Space View (DBArtisan - DB2 LUW Performance Analyst) > Usage tab

Metrics

This section can be used to get a better understanding of the physical layout of a tablespace. Also, it lets you quickly identify if any containers are inaccessible.

Logging tab

The Logging tab of the Space Detail view displays Logging Space statistics:

Log Read/Writes

This section shows the level of read/write activity being performed by the logger. The information presented here is:

  • Log Reads: The number of log pages read from the disk by the logger per second.
  • Log Writes: The number of log pages written to the disk by the logger.

Location

IBM DB2 (W/U/L) Performance Analyst statistics > Space View (DBArtisan - DB2 LUW Performance Analyst) > Logging tab

Metrics

You can use this page with the operating system level I/O metrics, to quantify the amount of I/O on a device that is attributable to database activity.

Indoubt Transactions

This section shows the number of outstanding indoubt transactions in the database.

Location

IBM DB2 (W/U/L) Performance Analyst statistics > Space View (DBArtisan - DB2 LUW Performance Analyst) > Logging tab

Metrics

Indoubt transactions hold log space for uncommitted transactions, which can cause the logs to become full. When the logs are full, further transactions cannot be completed. The resolution of this problem involves a manual process of heuristically resolving the indoubt transactions. This monitor element provides a count of the number of currently outstanding indoubt transactions that must be heuristically resolved.

Log Usage

This section shows the following log usage details:

  • Total Used Space: Total space used by log files
  • Total Free Space: Total space still available to the logger
  • Total Used Space Highwatermark: The maximum amount of total log space used.
  • Secondary Space Used High Watermark: The maximum amount of secondary log space used.

Location

IBM DB2 (W/U/L) Performance Analyst statistics > Space View (DBArtisan - DB2 LUW Performance Analyst) > Logging tab

Metrics

Use these metrics to check your current log space usage and the highest amount of space used by the logger. If the usage is high and the high watermarks are also near the total available space, you may need to increase the amount of log space or end/commit/rollback the oldest transaction.

Correction

You can use the following database configuration parameters to change the amount of log space available: logfilsiz, logprimary, and logsecond. You can end (force) a connection with a long running transaction using the DBArtisan database monitor.

Secondary Logs

This section shows the secondary logs allocations by the database over the monitoring period.

Location

IBM DB2 (W/U/L) Performance Analyst statistics > Space View (DBArtisan - DB2 LUW Performance Analyst) > Logging tab

Metrics

When the primary log files become full, the secondary log files are allocated one at a time as needed, up to a maximum number as controlled by the logsecond database configuration parameter. Secondary log files are useful in preventing occasional log fill ups but they may not be as efficient as primary log and they also increase the recovery time. If you see a constant reliance on secondary log files, it may indicate infrequent commits by database applications or insufficient primary log space.

Correction

First examine the database applications to see if secondary log files are due to long running transactions and whether these transactions can be shortened. If the transactions cannot be shortened or you still see a very frequent use of secondary logs after transaction tuning, increase the primary log size (logprimary,logfilsiz Database configuration parameters) to reduce the dependence on secondary logs. If there is an occasional long running transaction, and you see your see transaction log full (SQL0964C) error messages, you can either increase the number of secondary log files (logsecond database configuration parameter) or set the number to -1 (no limit on number of secondary log files).

Bufferpools tab

The Bufferpools tab of the Space Detail View shows the list of tablespaces, with the current bufferpool associated with each tablespace and also the bufferpool used by the database at next startup

Objects tab

The Objects tab of the Space Detail View shows the following statistics:

Tablespace Object Summary

This section shows information about how tablespaces are being used on the database. The information presented here includes:

  • Tablespace: Name of the tablespace
  • Table Count: Number of tables on the tablespace
  • Page Size: Tablespace page size
  • Index Count: Number of indexes on the tablespace
  • Table Space: Space used by tables on the tablespace
  • Index Space: Space used by indexes on the tablespace

Location

IBM DB2 (W/U/L) Performance Analyst statistics > Space View (DBArtisan - DB2 LUW Performance Analyst) > Bufferpools tab

Tablespace Object Details

This section shows the details of the objects on the tablespaces shown in the Tablespace Object summary section. The user can toggle between the list of tables and indexes for the tablespace.

The information presented here for tables includes:

  • Table Schema: Schema name of the table
  • Table Name: Name of the tables
  • Avg Row Length: Approximate length of the table row.
    1. Rows: Table Cardinality
    2. Pages Allocated: Number of pages allocated to the table
  • Table Size: Total space used by the rows on the table
  • Available: Total available space on the table
  • Total: Total space used by the table pages
Note: The information presented here is based on the table/index statistics and if the statistics haven’t been collected /updated, this information may be out of date.

Location

IBM DB2 (W/U/L) Performance Analyst statistics > Space View (DBArtisan - DB2 LUW Performance Analyst) > Bufferpools tab

Quiesce tab

The Quiesce tab of the Space Detail view shows displays the following information:

Quiesced Tablespaces

This section shows a list of quiesced tablespaces. A quiesce is a persistent lock. Its benefit is that it persists across transaction failures, connection failures, and even across system failures (such as power failure, or reboot).

Location

IBM DB2 (W/U/L) Performance Analyst statistics > Space View (DBArtisan - DB2 LUW Performance Analyst) > Quiesce tab

Metrics

A quiesce is owned by a connection. If the connection is lost, the quiesce remains, but it has no owner, and is called a phantom quiesce. For example, if a power outage caused a load operation to be interrupted during the delete phase, the tablespaces for the loaded table would be left in delete pending, quiesce exclusive state. With a database restart, this quiesce would be an unowned (or phantom) quiesce. The removal of a phantom quiesce requires a connection with the same user ID used when the quiesce mode was set. This section is useful in identifying the tablespaces that are in a quiesced state.

Correction

To remove a phantom quiesce: Connect to the database from DBArtisan with the same user ID used when the quiesce mode was set. Re-quiesce the table space using the current quiesce state from DBArtisan. Reset the quicesce from DBartisan.

Quiescer Details

This section shows the list of quiescers for the selected quiesced table.

Location

IBM DB2 (W/U/L) Performance Analyst statistics > Space View (DBArtisan - DB2 LUW Performance Analyst) > Quiesce tab

Metrics

Any quiescers whose agent ids are not listed under Users Detail >Attributes are phantom.

Correction

To remove a phantom quiesce: Connect to the database from DBArtisan with the same user ID used when the quiesce mode was set. Re-quiesce the table space using the current quiesce state from DBArtisan. Reset the quicesce from DBartisan.

Rebalance tab

The Rebalance tab of the Space Detail page displays the following information:

The following statistic, available on this pane, are duplicates of statistics on the Space View (DBArtisan - DB2 LUW Performance Analyst) Usage tab.

Tablespace Details

Rebalance Status

This section shows a graph of the percentage of extents that have been processed and the percentage that remains incomplete for the selected tablespace.

Location

IBM DB2 (W/U/L) Performance Analyst statistics > Space View (DBArtisan - DB2 LUW Performance Analyst) > Rebalance tab

Reorganization tab

The Reorganization tab of the Space Details View displays the following statistics:

Table Details

This section shows the details of all the tables on which reorganization has been attempted since the start of monitoring. It also shows the status of tables that are presently undergoing reorganization. The information presented in this includes:

  • Table Schema: Table Schema of the table on which reorganization is being/was performed
  • Table Name: Table Name of the table on which reorganization is being/was performed
  • Tablespace: Tablespace of the table on which reorganization is being/was performed
  • Start: Start time of the reorganization
  • End: End Time of the reorganization
  • Phase Start: Start time of a phase of reorganization
  • Current Counter: Percentage of reorganization operation completed (0 if the reorganization status is complete)
  • Max Phase: The maximum number of reorganization phases that will occur during reorganization processing. This applies to classic (offline) reorganizations only.
  • Status: The status of an in-place (online) table reorganization. This is not applicable to classic (offline) table reorganizations. An in-place table reorganization can be in one of the following states:
    • Started/Resumed
    • Paused
    • Stopped
    • Completed
    • Truncate
  • Completion: Table reorganize success indicator.
  • Phase: Table reorganize phase. This applies to classic (offline) table reorganizations only).
  • For classic table reorganization, the following phases are possible:
    • Sort
    • Build
    • Replace
    • Index Recreate
  • % Complete: Shows the completion status of the reorganization jobs

Location

IBM DB2 (W/U/L) Performance Analyst statistics > Space View (DBArtisan - DB2 LUW Performance Analyst) > Reorganization tab

Reorganization Status

Shows a graphical status view of the currently selected reorganization.

Location

IBM DB2 (W/U/L) Performance Analyst statistics > Space View (DBArtisan - DB2 LUW Performance Analyst) > Reorganization tab

Reorganization Attributes

This section shows the reorganization attributes of the currently selected reorganization. The attributes include:

  • Allow Write Access
  • Allow Read Access
  • Allow No Access
  • Recluster Via Index Scan
  • Reorg Long Field LOB Data
  • No Table Truncation
  • Reclustering
  • Reclaiming
  • Inplace Table Reorg
  • Table Reorg
  • Recluster Via Table Scan
  • Reorg Data Only.

Location

IBM DB2 (W/U/L) Performance Analyst statistics > Space View (DBArtisan - DB2 LUW Performance Analyst) > Reorganization tab

Rollforward tab

Recovering database changes is time-consuming. You can use the database system monitor to monitor the progression of a recovery. This section provides the following elements to help you monitor the status of rollforwards:

  • Timestamp: Timestamp of the log being processed. This is an indicator of the data changes that will be recovered
  • Tablespace: Tablespace being rolled forward. This element identifies the tablespaces involved in the rollforward
  • Type: An indicator of whether the recovery is happening at a database or tablespace level
  • Log#: The log being currently processed. This identifies the log involved in the rollforward.
  • Status: The status of rollforward recovery. This metric indicates if the recovery is in an undo (rollback) or redo (rollforward) phase.
    1. Tablespaces: Number of tablespaces involved in the rollforwar
  • Node: Indicates the database partition number for the rollforward operation