Itcamfor DB2 Agentbest Practice Guide

ITCAMfor DB2 AgentBest Practice Guide

Document version 0.2

[Xu Jian, Lv Bin]

[Software Engineer]

US Government Users Restricted Rights – Use, duplication or disclosure restricted by GSA ADP Schedule Contract with IBM Corp.

Page 1 of 33

Contents

List of Figures

List of Tables

Revision History

1Preparation

1.1Platform and DB2 Support Matrix

2Installation

2.1Installation User and Agent Run Time User

2.232-Bit DB2 Server in a 64-Bit Kernel OS Platform

3Configuration

3.1DB2 Authority

3.2Diagnose Log Monitor

4Performance Tuning of Agent

4.1Tips Of Diagnose Log Monitor

5Apply Of Interim Fix(ifix)

5.1Latest Ifix Level and Download Location

5.2Adding Of Application Support

5.3Verify Of Apply Result

6FAQ

6.1Common Tracing

6.2Why is there no data shown in tep after DB2 agent started?

6.3CONNECT privilege required by DB2 agent to gather some specific metrics

6.4What's the difference between IBM Tivoli Monitoring DB2 Agent and DB2 Agent shipped with DB2?

6.5How to reset db2 snapshot?

6.664-bit DB2 Support

6.7What access rights does the ODBC connection used by ITM6 for Databases(DB2) V6.2 require? Read only or Read, Write and Update?

6.8KCIIN0205E Unable to stop DB2 agent

6.9Monitoring multiple partitions with DB2 Agent 6.2

7Technote(Troubleshooting)

7.1DB2 agent cannot stop properly on AIX

7.2DB2 system will be orphaned, when TADDM discovers without OS Agent

7.3The agent stops because of a stack overflow on AIX systems

7.4The agent is started by the root user instead of the original user from the Tivoli Enterprise Portal

7.5DB2 agent Language support not working on 64bit Linux

7.6Impact of environment variable KUD_MONITOR_ALL_DATABASES

List of Figures

List of Tables

Revision History

Date / Version / Revised By / Comments
2010-11-24 / 0.1 / Xu Jian / Finish the version 0.1
2011-10-19 / 0.2 / Lv Bin / Add and Edit some content of fixes and technotes

ITCAM for DB2 Agent Best Practice Guide

1Preparation

1.1Platform and DB2 Support Matrix

V6.2.0:

V6.2.2:

Notice: DB2 agent V6.2.2 doesn’t support DB2 V8.

2Installation

2.1Installation User and Agent Run Time User

The DB2 agent can run under any user ID. Each DB2 agent acquires its permissions from the user ID under which the DB2 agent is running.

When DB2 agent is running under a non-root user ID on a UNIX or Linux system, you must log in with the same user ID to stop the agent. Use the following command to obtain the non-root user ID:

install_dir/bin/cinfo -r

2.232-Bit DB2 Server in a 64-Bit Kernel OS Platform

Since DB2 v8 supports 32-bit instance working on 64-bit Unix, DB2 Agent also supports that DB2 Agent 32-bit core monitoring DB2 32-bit instance on 64-bit Unix. When installing applicable platforms Both 32-bit and 64-bit versions of agent installed. At agent startup, install will start the appropriate version, based on the bit-ness of the DB2 instance starting the agent.

3Configuration

3.1DB2 Authority

The DB2 agent can run under any user ID. Each DB2 agent acquires its permissions from the user ID under which the DB2 agent is running.

The ID used to run the DB2 agent must have DB2 SYSADM authority. SYSADM authority is required in order for the agent to turn on all monitor switches.

3.2Diagnose Log Monitor

Field Name / Description
The db2diag log file path / The directory of DB2 diagnostics log file. If the db2diag log file is in the default directory, you can leave this field blank; if the file is not in the default directory, enter the path of the directory. The default directory is ITMinstall_dir\IBM\SQLLIB\DB2 on Windows systems, and ITMinstall_dir/sqllib/db2dump on UNIX® and Linux® systems.
MSGID filter in regular expression / This an optional field. Fill MSGIDs in this field to monitor only the messages that you are interested in. The MSGID is a combination of the message type, message number, and severity level. You can also use a regular expression. For example, ADM1\d*1E|ADM222\d2W.

4Performance Tuning of Agent

4.1Tips Of Diagnose Log Monitor

DB2 Agent doesn’t support “*”, “?” or special signs for MSGID filter in regular expression. The special signs could cause DB2 Agent to crash.

If want to disable diagnose log monitor function, change the db2diag log file path to the directory, which has no DB2 diagnose logs. But don’t leave parameter empty, otherwise the agent would use the default db2diag log file path.

5Apply Of Interim Fix(ifix)

5.1Latest Ifix Level and Download Location

610: DB2 6.1.0.1-TIV-ITM_DB2-IF0013

620: DB2 6.2.0-TIV-ITM_DB2-IF0010

622:6.2.2.1-TIV-ITM_DB2-IF0001

5.2Adding Of Application Support

1. Transfer the appropriate archive file (on UNIX systems.tar and on Windows systems.zip) to the IBM Tivoli Enterprise Monitoring Servers, IBM Tivoli Enterprise Portal Servers, or Tivoli Enterprise Portal Desktops.

2. Expand the archive file using the tar command on UNIX systems or an unzip utility on Windows systems. Expanding the file creates a directory structure that contains fixes for all of the supported platforms.

3. Expand the archive file (.tar or .zip) that contains the updates for the Tivoli Enterprise Monitoring Server, Tivoli Enterprise Portal Server, and Tivoli Enterprise Portal Desktop using the tar command on UNIX systems or an unzip utilityon Windows systems. Expanding the file creates a directory structure that includes a subdirectory called CD-ROM with the necessary updates.

4. Start the Application Support Installer GUI to install the fix. The GUI can be started by using one of the following commandsfrom within the CD-ROM directory where setup.jar is located:

On UNIX systems:

> $CANDLEHOME/JRE/<platform>/bin/java -jar setup.jar

On Windows systems:

> <"C:\Program Files\IBM\Java142">\jre\bin\java -jar setup.jar

Where <"C:\Program Files\IBM\Java142"> is the default drive and location of IBM Java 1.4.2. The location of IBM Java 1.4.2on your system might vary. When prompted by the Application Support Installer for the installable media directory, select the CD-ROM directory, not the component directory. The installer can install updates for multiple components at the same time.

5. The next panel presented by the Application Support Installer asks for the selection of Tivoli Monitoring components to which you wouldlike to add application support. The select the check boxes as follows:

checked - Tivoli Enterprise Monitoring Server (TEMS)

checked - Tivoli Enterprise Portal Server (TEPS)

checked - Tivoli Enterprise Desktop Client (TEPD)

Continue through the remaining GUI panels selecting the DB2 support to complete the installation.

6. If the Tivoli Enterprise Portal Desktop or Tivoli Enterprise Portal Browser was running when the update was installed, it must be restarted.

5.3Verify Of Apply Result

To verify that the agent was updated correctly, use the tacmd commandto view the current version of the agent after the agent is restarted.You are required to log in to a Tivoli Enterprise MonitoringServer before viewing the agent version.

For example:

On UNIX systems, where $CANDLEHOME is the IBM Tivoli Monitoringinstallation directory. The default location is '/opt/IBM/ITM'.

> $CANDLEHOME/bin/tacmd login -s <server>

-u <itmuser>

-p <password>

> $CANDLEHOME/bin/tacmd listSystems -t UD

On Windows systems, where %CANDLE_HOME% is the IBM TivoliMonitoring installation directory. The default locationis 'C:\IBM\ITM'.

> %CANDLE_HOME%\bin\tacmd login -s <server>

-u <itmuser>

-p <password>

> %CANDLE_HOME%\bin\tacmd listSystems -t UD

Note:

The component (-t) for the tacmd listSystems command is specified as two characters, UD, notthree characters, KUD.

6FAQ

6.1Common Tracing

KBB_RAS1=ERROR (UNIT:KUD ALL) (UNIT:CSS ALL) (UNIT:GLO ALL) (UNIT:KRAUD ALL)

where:

KUD = General DB2 agent logging

CSS = DB2 snapshot, DB and DBM config output

GLO = DB2 SQL query output

KRAUD = Data returned to TEMS (UD = just for DB2 agent)

For more selective tracing enablement, see DB2 6.2 Go Training for detailed options.

Agent Build Date. In the DB2 Agent6.1 FP1 & 6.2 log file

UNIX: <hostname>ud_kuddb2<time>-<nn>.log

Windows: <hostname>ud<instance>kudcma<time>-<nn>.log

In the DB2 Agent6.2.2 & 6.2.2 FP1 log file

UNIX: <hostname>ud_<instance name>_kuddb2<time>-<nn>.log

Windows: <hostname>ud<instance>kudcma<time>-<nn>.log

Towards the top you will see the component "kud" and a timestamp. This is the timestamp that the version of the agent was built, which should typically correspond to the date the patch was published. This can be used to help verify the correct version of the agent is actually running. So even though "cinfo/kincinfo -i" says that for example, the agent is at 06.20.00.02, if the date shows a date of "Oct 27 2007 then the install of the LA did not successfully lay down the updated agent executable.

Component: kud

Driver: Devxxxxxxx

Timestamp: Oct 27 2007 16:25:14

Oct 27 2007 = Date of 6.2 GA code

Jul 21 2008 = Date of 6.2 LA02 fix

6.2Why is there no data shown in tep after DB2 agent started?

1.Verify the agent is running

Unix: issue "ps -ef | grep kud"

Verify agent is started as instance owner

Unix: run <install_dir>/bin/cinfo –r

Windows: run <install dir>\InstallITM\kincinfo –r

2.Verify there is at least one active database. Run "db2 list active databases" as the instance owner.

3.ON DB2 Agent 6.1, verify that if the fully qualified hostname is greater than 32 characters that the CTIRA_HOSTNAME=<short hostname> in ud.config file.

4.ON DB2 Agent 6.22, verify that if the DB2 instance isn’t ESE(Enterprise Server Edition). If yes, install 6.2.2-TIV-ITM_DB2-IF0001 or upgrade to DB2 Agent 622FP1.

5.Verify agent is able to collect data from DB2.

Verify detailed tracing enabled:

KBB_RAS1=ERROR (UNIT:KUD ALL) (UNIT:CSS ALL) (UNIT:GLO ALL) (UNIT:KRAUD ALL)

Is any data being returned to TEMS? Check the following lines in log file. If there are rows of data being returned with what look like valid values, then the DB2 Agent is getting data from DB2 and trying to pass it back up to TEMS. The problem then is something between the TEMA and TEMS communications.

kraud<xxx>.cpp PrintSelf - Data reutrned to TEMS.

_data {

count=3, allocated=100, allocSize=100

{

sampleno=0

rowno=0

originnode="DB2:machine:UD

kuddbname="TEPS"

kudver=8

If valid data is not being sent to TEMS, then verify that that agent is able to obtain data from DB2. In the kuddb2 log file, verify you see data being returned from DB2. For example:

cssmain.cpp ParseMonitorStream - output from DB2 get snapshot

cssmain.cpp ParseMonitorStream Monitor Element 'SQLM_ELM_CONTAINER_TOTAL_PAGES' (size 4) (U32) 4662

css<xxx>.cpp CollectParsedSQLMA_TABLESPACE<xxx> - after obtaining data from DB2, any processing that the agent does to the values (such as calculations)

csstbagt.cpp CollectParsedSQLMA_TABLESPACE_int") Final table space usable pages is 4662

If no data is being returned from DB2, there should be a DB2 error message when trying to issue the snapshot or SQL query. Query Cast or Google to see if you can find more information on the error.

6.3CONNECT privilege required by DB2 agent to gather some specific metrics

DB2 agent can be started using a userid with SYSMON group privileges only. All seems to work fine, but an error message is issued in the agent log from time to time.

Agent log shows the following error message:SQLSTATE = 08004, Native Error Code = -1060, msg = [IBM][CLI Driver] SQL1060N User "userid " does not have the CONNECT privilege. SQLSTATE=08004

The reason is some attributes collected by the ITM DB2 agent require that the CONNECT privilege is granted to the userid starting the agent. If this privilege is not granted, these attributes are not collected and the error message SQL1060N is written in the agent log.

Most of the time, for security reasons, you may be not allowed to grant SYSADM privilege to the userid used to run monitoring agents. DB2 agent can be executed also granting only the SYSMON privilege to the involved userid. But in this case some attributes would not be collected because they also require CONNECT privilege.

The impacted attributes and attributes groups when CONNECT privilege is missing are:

- DB2 Apply Program

|- apply qualifier

|- total apply sub fail

|- total apply sub lag

- DB2 Apply Subscription

|- apply id

|- target owner

|- target table

|- apply sub status

|- apply num reqs refresh

|- apply sub lag time

- DB2 Table

|- reorg needed

- DB2 Database01

|- number of db tablespaces

|- number of event monitors

|- number of invalid triggers

|- number of system tablespaces

|- number of tables

|- number of tablespaces

|- number of tablespaces with datatype long

|- number of triggers

|- number of user indexes

|- number of views

|- number of invalid packages

|- number of invalid system packages

|- number of errors encountered by the Capture program

|- last timestamp recorded by the Capture program

|- number of rows in the unit-of-work (UOW) table

Of course, by granting also the CONNECT privilege, you will have above attributes correctly gathered from DB2 agent and the related error message in the agent log will be no longer issued.

6.4What's the difference between IBM Tivoli Monitoring DB2 Agent and DB2 Agent shipped with DB2?

DB2 monitoring agent bundled with DB2 - This is an agent shipped and supported by DB2. It does share code from IBM Tivoli Monitoring for DB2 Agent, however it is not to be used within a Tivoli infrastructure. The DB2 Agent shipped with DB2 bundles in a version of IBM Tivoli Enterprise Monitoring Server 6.2 along with the agent. It does not ship Tivoli Enterprise Portal Server; it uses the DSAC console for the GUI and uses SOAP to get the data. The version of the agent is listed as "06.21.00.00".

This agent is to be used within the DB2 DSAC environment. Support for this agent (DSAC) is handled through the DB2 support team, not ITM.

Usually the default installation path

Windows

Unix and Linux

IBM Tivoli Monitoring DB2 Agent - This is the agent that should be installed if a customer wants to monitor DB2 within a Tivoli infrastructure. The DB2 agent versions in the field applicable to monitoring in a Tivoli infrastructure include from 6.2 to 6.2.2

Usually the default installation path

Unix and Linux

6.5How to reset db2 snapshot?

1. Update database manager configuration before the first DB2 Agent startup

Set all monitor switches“OFF”:

db2 update dbm cfg using DFT_MON_BUFPOOL off

db2 update dbm cfg using DFT_MON_LOCK off

db2 update dbm cfg using DFT_MON_SORT off

db2 update dbm cfg using DFT_MON_STMT off

db2 update dbm cfg using DFT_MON_TABLE off

db2 update dbm cfg using DFT_MON_TIMESTAMP off

db2 update dbm cfg using DFT_MON_UOW off

db2 update dbm cfg using HEALTH_MON off

Use command “db2 get dbm cfg” to check the result.

2. When restart DB2 Agent, the DB2 snapshot will be reset. For DB2 Agent 610, use “kill” to stop the agent process:

# ps –ef|grep kuddb2

……

# kill –TERM $process_id

6.664-bit DB2 Support

DB2 can monitor both 32-bit and 64-bit DB2 instances on machine.

6.1 - TechNote 1229257 had manual steps

6.1 FP1 & 6.26.2.2 & 6.2.2 FP1 - Install changed, when installing applicable platforms Both 32-bit and 64-bit versions of agent installed.

At agent startup, install will start the appropriate version, based on the bit-ness of the DB2 instance starting the agent.

See DB2 Agent User's Guide or DB2 6.2 Go Training for platforms.

Data

In ITM 6.2 DB2 Agent, preliminary work was done to support 64-bit data. Data is collected from DB2 by the agent in 64-bit values. Calculations are done with 64-bit values, and will return correct data as long as it does not exceed 32-bit values. When data is returned to TEMS/TEPS, it will be capped at signed 32-bit values if it exceeds 32-bit.

In ITM DB2 Agent6.2.2 & 6.2.2 FP1, the agent supported 64-bit data completely. Data is collected from DB2 by the agent in 64-bit values. Calculations are done with 64-bit values, and will return correct data as long as it does not exceed 64-bit values. When data is returned to TEMS/TEPS, it will be capped at signed 64-bit values if it exceeds 64-bit.

6.7What access rights does the ODBC connection used by ITM6 for Databases(DB2) V6.2 require? Read only or Read, Write and Update?

Usually the ODBC System DSN is only used for its database alias and driver library association. In other words, just because you've configured the DSN with the username and password and have successfully tested the connection from within the ODBC System DSN user interface, doesn't mean a connecting application utilizing that DSN doesn't still have to provide a correct username/password combination. So the security is still there, the User Guide just makes it seem like any application can point to the name of a ODBC System DSN to connect to a database without providing credentials, but that's not the case. That's why we still have to provide username and password information in our WPA, TEPS and S&P configuration GUIs.

6.8KCIIN0205E Unable to stop DB2 agent

They get the following error:

KCIIN0205E Unable to stop agent or process

Check whether the agent was started as root or the instance owner. Whichever ID was used to start the agent must also be used to stop it.

6.9Monitoring multiple partitions with DB2 Agent 6.2

A custom query must be created in order to populate the DB2 Agent information to the TEPS.

Right click on the tablespace workspace and go to properties.

Click on the button “Click here to assign a query”

Click on create new query

Select DB2 Tablespace attribute group.

Select the attributes db_partition, NodeName and other attributes required attributes.

Fill the value for db_partition to the necessary partition number.

Fill in the node name as $NODE$

Click the ok button

Now you will see data for the corresponding partition.