Spotlight on DB2 6.9.3 - User Guide

Setting up a connection profile

Before Spotlight can monitor a DB2 database or database partition, you must connect to that system. If you have not connected to the database or database partition in Spotlight before, you must set up a connection profile for that system.

For an overview of the process, see To connect to systems in Spotlight basics help. For Spotlight on IBM^® DB2^® LUW, use the procedure below.

Note: Options in the DB2 Monitor Switch Settings section of the Properties window are not used for connections to DB2 version 9. For version 9 and later, you need to alter the status of instance-level monitor switches to collect the desired data.

To set up a connection profile

1. Click the Connect icon in the Spotlight toolbar to open the Spotlight Connection Manager.

2. In the Connections bar on the left, click Spotlight on IBM DB2 LUW to display the connections currently defined for DB2 for LUW databases and database partition.

3. In the Spotlight on IBM DB2 LUW pane on the right, double-click New connection.

4. In the New Connection window, identify the connection:

   Select connection type	Select Spotlight on IBM DB2 LUW.
   New connection name	Enter a unique name for the connection. This name will identify the connection on the Spotlight browser.

5. Click OK to open the Properties window for the new connection. The Details tab on this window is in focus.

6. In the DB2 Details section, enter the information that connects Spotlight to the DB2 database or specific database partition:

   DB2 Instance	The name of the DB2 instance as defined on the database server.
   DB2 Database	Enter the alias of the DB2 database that you want to monitor in this connection.
   DB2 Auto Monitor Nodes	For a multiple-partition database, select this option if you want the Connection Manager to set up a separate connection automatically for every partition (node) for the database. (The alternative for monitoring a multiple-partition database is to select an option in the DB2 Node field.) When you select DB2 Auto Monitor Nodes, the DB2 Node field is disabled.
   DB2 Auto Monitor Active Databases	Select this option if you want Spotlight to monitor all active databases residing in the instance in which the database or database partition for which you are creating this connection resides. When Spotlight connects to this database or database partition, it also connects to all the other active databases in the instance. These databases are listed in the browser pane. You can monitor their home pages and drilldowns just as you can for the database whose connection you set up.
   DB2 Node	For a multiple-partition database, select Global if you want the Connection Manager to establish a single connection that aggregates statistics for all partitions for the database. Or select the number for the specific partition to establish a connection for that database partition only. For a single-partition database, select Global.
   DB2 User	Enter the user ID with which to connect to the DB2 database.
   DB2 Password	Enter the password with which to connect to the DB2 database.

7. In the SQL Archive Options section, set the options that run the Spotlight SQL Statement Archiver for this connection; these are used for static SQL statements:

   Archive Default Startup	To turn the Archiver on or off for this connection, select Archive SQL statements or Do not archive SQL statements, respectively.
   Archive Capacity	Enter the number of distinct static SQL statements that the Archiver will collect before it starts dropping statements. Enter 0 (zero) if you want to set no limit on the number of distinct SQL statements that the Archiver can collect.

8. In the Method to Retrieve Data section, select an option from which to retrieve data.

   Shapshot Routines	Select this option to use the snapshot function to retrieve data from the server.
   Monitor Functions	Select this option to use the mon-table to retrieve data from the server.

9. In the OS Details section, provide the information that Spotlight needs to monitor the operating system on which the database or database partition runs. For detailed information about connecting to a specific operating system type, refer to the Spotlight on Unix, Spotlight on Windows, or Spotlight on Windows Clusters help (accessed from the Help|Contents option).

   OS Monitor	Select this option if you want Spotlight to monitor the operating system on which the database or database partition runs. When you select this option, specific fields listed below are enabled.
   OS Type	Choose the operating system type of the target machine. For Unix machines, make sure that either RECEX or SSH is installed.
   OS Local Machine	(For a Windows target machine only) Select this option if the machine you are currently using is the one you want to monitor. If you select this option, you need to complete only the OS Address field.
   OS Domain	(For a Windows target machine only) Enter the domain to which the specified user (in the OS User field) belongs. If you entering no user in the OS User field, leave this field blank.
   OS Address	Enter the address of the target machine: For a Windows machine, enter its IP address, hostname , or URL. For a Linux or Unix machine, enter its connection string.
   OS User	Enter a valid user ID or account to log on to the target machine. For Windows machines, Spotlight needs to use an account with the privileges required to retrieve server information, query the registry, and access WMI and performance monitor objects. An account with administrative rights to the target machine allows this.
   OS Password	Enter the password used to log on to the target machine.
   OS Port	(For a Unix SSH target machine only) Enter the port number that Spotlight will use for its SSH (secure shell) connection to the Unix host. The default value is 22.

10. Select Save password details (for this connection) to save the user ID and password information with this connection. Each time you open this connection in Spotlight, you will not have to provide the user ID and password.

If you unselect this option, you are prompted to provide the DB2 User and DB2 password values each time you open this connection in Spotlight. If Spotlight monitors the operating system also for this connection, you are prompted to provide the OS User and OS Password information as well.

11. Click OK to add the new connection to the list of DB2 for LUW connections.

Calibrating your database

What happens during the Spotlight calibration process

The dataflow components that display on the DB2 for LUW (Linux, Unix, and Windows) database, database partition, or instance home page on your Spotlight desktop show the rate of different types of dataflows in your system over time. The speed of a home page dataflow varies based on the volume of data being transferred within the database or the instance. The greater the data traffic, the faster the flow.

Each home page dataflow corresponds to a type of dataflow in your database, partition, or instance. In order that home page dataflows represent actual dataflows in your system accurately, Spotlight must know the normal range of activity for this system. For example, if the normal range of a specific type of data flow in your database during a specific span of time is from 0 to 100, a value of 8 is slow¾the corresponding home page dataflow moves slowly. However, if the range is from 0 to 10, a value of 8 is very fast¾the home page dataflow moves much faster.

Spotlight provides a calibration tool that calculates the normal range of dataflows in your connection¾that is, in the database or database partition to which you are connected and in the instance to which this database or partition belongs. When you start the calibration process, Spotlight measures the flow of data through your system over a set period of time. It then uses this data to set the upper limit of each dataflow on the database or database partition home page and on the corresponding instance home page. (Spotlight always uses zero as the lowest value when calculating the normal range of values for your system.)

The calibration window

The first time you open a connection you created in Spotlight, the calibration window is displayed, prompting you to enter a length of time for the calibration. After you provide the information and close the window, the calibration process begins.

Connections to multiple-partition databases

You can set up a connection to a multiple-partition DB2 for LUW database is several ways. The way the connection is configured determines how the calibration process is run:

• If the connection is set up with Global selected in the DB2 Node field, the calibration process simultaneously calibrates all of the selected database’s associated partitions (nodes) for the same length of time.  
• If the connection is set up with the DB2 Auto Monitor Nodes option selected, the Connection Manager creates a separate connection automatically for each partition for the database. (However, you connect to and disconnect from these partitions simultaneously through the single database connection set up in the connection profile.) When you open this connection for the first time, Spotlight displays a succession of calibration windows, one for each of the partitions, prompting you for the length of time for the calibration. Calibration begins on each partition when you close its calibration window.
• If your connection is set up with a specific partition selected in the DB2 Node field, the calibration is run on that database partition only.  

During the course of a calibration the user may still cancel, change, or stop the calibration process. The action (cancel, change, stop) affects the database and its associated partitions in the following ways:  

• If your connection is set up with the Global option selected in the DB2 Node field, the action is performed at the database level across all partitions simultaneously.
• If the connection is set up at with the DB2 Auto Monitor Nodes option selected, the action is performed at the database level and across all partition connections simultaneously.
• If your connection is set up for the specific database partition specified in the DB2 Node field, the action is performed on that database partition only.

When to run calibration

Spotlight automatically runs calibration when you open a open a connection for the first time in Spotlight database for the first time. However, you can choose to run the calibration at any time.

If the data traffic in your database or database partition has increased or decreased significantly since you last ran a calibration, calibrate that system again so that your home page dataflows accurately depict your system’s activity.

Saving the calibration values

When you disconnect from the database or database partition connection, you are prompted to save any connection changes. This save includes the calibration values for the connection home pages. The calibration values for the connection are used until you change them or until you perform another calibration.

Related topics

Calibrating Spotlight connections

Setting up a connection profile for a DB2 for LUW database or partition  

Archiving SQL statement data

The SQL Statement Archiver collects data in the background for static SQL statements that have executed on the DB2 instance. You can view metrics for these statements in the Static SQL tab in the Top SQL drilldown.  

The Archiver starts to collect data on static SQL as soon as you open the connection. The longer the Archiver runs, the more SQL history you can view on the drilldown. However, running the Archiver requires overhead. As a result, you might not want it activated at all times.

You can use SQL Archive options in the connection profile for a database to control whether the SQL Archiver is on or off. You can also use these options to set capacity for the SQL Archiver.

To change SQL archiving options

1. If Spotlight is currently connected to the database or partition connection for which you are changing archiving options, right-click the connection in the Spotlight browser and select Disconnect.

2. Click the Connect icon in the Spotlight toolbar to open the Spotlight Connection Manager.

3. In the Connections bar on the left, click the category from which to select the Spotlight connection to the database or database partition.

4. Right-click the connection in the list, and select Properties to open the Properties window for the connection. The Details tab on this window is in focus. 

5. In the SQL Archive Options section, complete the following:

6. Click OK to save the changes.

Related topics

Static SQL tab on the top SQL drilldown

Setting up a connection profile

Navigating between database, node, and instance home pages

Using the Spotlight browser, you can easily navigate between the home pages and drilldowns for the various databases and database partitions that you are monitoring.

Spotlight also provides the option to view the home page and associated drilldowns for the DB2 instance to which the database or partition currently selected in the Spotlight browser belongs. At the instance-level, the home page and drilldowns display an aggregate of performance information captured across all active databases in the DB2 instance.

To open the home page or drilldown for another DB2 database or partition

• From the Spotlight browser, select the name of the database or partition that you want to monitor. The home page or drilldown is redisplayed , showing performance information for the system you selected.

Any other drilldown you open from this home page or from the toolbar will show performance information for this selected database or partition.

To open the home page for the DB2 instance to which a database or database partition belongs

• With the database or database partition selected in the Spotlight browser, click the DB2 Instance button in the Spotlight toolbar.

The instance home page is displayed, showing performance information for the DB2 instance to which the database or database partition selected in Spotlight browser belongs. Any drilldown you open from this home page (or from the toolbar) will show performance information captured across all active databases in the instance.

To switch from the instance home page or drilldown back to the database- or partition-level home page

• Click the button in the Spotlight tool bar.

The home page for the database or database partition that is highlighted in the Spotlight browser (and from which you launched the instance home page) is redisplayed . Any drilldown you open from this home page (or from the toolbar) will now show performance information for this database or partition.