by Doug Doering

ITM monitoring agent for DB2 databases - UD - can be used to monitor DB2 usage for transaction logs.
The UD agent contains two attribute groups - Database01 (KUD_DB2_Database01) and Log (KUD_DB2_LOG) - either of which can be used to monitor the Log Used Percent.
The attributes in these two attribute groups for log use percent are calculated the same way.

Database01 (KUD_DB2_Database01) attributes
Current Primary Log Used Percent The percentage of primary log space that is currently in use.
Current Secondary Log Used Percent The percentage of secondary log space that is currently in use.
Primary Log Used Percent The percentage of total log space used by the primary log. Use the returned value to help you evaluate the allocated amount of primary log space and refine the log buffer size, log file size, and primary log configuration parameters. The returned value is valid only if circular logging is used.
Primary Log Used Top The maximum bytes of primary logs used.
Secondary Log Used Percent The percentage of maximum log space used by the secondary log. Use the returned value to show the current dependency on secondary logs. Secondary logs are used when you have circular logging (log retention off) and the primary log files are full.

Log (KUD_DB2_LOG) attributes
Current Primary Log Used Percent The percentage of the primary log space that are currently used.
Current Secondary Log Used Percent The percentage of the secondary log space that are currently used.
Primary Log Used Percent The percentage of total log space used by the primary log. Use the returned value to help you evaluate the allocated amount of primary log space and refine the log buffer size, log file size, and primary log configuration parameters. The returned value is valid only if circular logging is used.
Sec Logs Allocated The total number of secondary log files that are currently being used for the database. Use this attribute with the Secondary Log Used Top and Total Log Used Top attributes to show the current dependency on secondary logs. If this value is consistently high, you might need larger log files, more primary log files, or more frequent COMMIT statements within your application.
Sec Log Used Percent The percentage of maximum log space used by the secondary log. Use the returned value to show the current dependency on secondary logs. Secondary logs are used when you have circular logging (log retention off) and the primary log files are full.
Sec Log Used Top The maximum amount of secondary log space (in bytes) that has been used. Use this attribute with the Secondary Logs Allocated and Total Log Used Top attributes to show the current dependency on secondary logs. If this value is high, you might need larger log files, more primary log files, or more frequent COMMIT statements within your application. Values that are greater than or equal to 9223372036854775807 are indicated with the Value Exceeds Maximum text in the portal.

Which attributes to use to monitor transaction log use will depend on whether the DB2 environment is using circular or archival transaction logging.

DCF 1671252

DB2 agent attribute for Primary and Secondary Log Used Percent 

The verbal description for "Sec Log Used Percent" is unclear.  It includes reference to the "maximum log space used by the secondary log" and then in the next sentence implies it is the "current dependency on secondary logs", and then in the third sentence indicates that secondary logs are used when primary log files are full.

In order to understand whether a situation alert is triggering incorrectly (a "false" alert), or in cases where the UD agent attribute value is believed to be invalid, the end user needs to understand how the attribute value is calculated. 

The "DB2 Log" attribute group is also referred to as "KUDLOG" and "KUD_DB2_LOG".

The attribute named "Current Secondary Log Used Percent" is "CSLUP" and "curr_sec_log_used_pct"
The attribute named "Sec Log Used Percent" is "SLGUP" and "sec_log_used_pct"
The attribute named "Sec Log Used Top" is "SLGUTP" and "sec_log_used_top"

The attribute named "Sec Logs Allocated" is "SLGALLC" and "sec_logs_allocated" 

The formula used to calculate the "Sec Log Used Percent" attribute is:
sec_log_used_pct= 100 * sec_log_used_top/(logsecond * logfilsiz * 4096)

Where logsecond and logfilsiz can be obtained from executing following db2 command:
db2 GET DATABASE CONFIGURATION FOR <database_name>
Example output:
Log file size (4KB) (LOGFILSIZ) = 1024
Number of primary log files (LOGPRIMARY) = 8
Number of secondary log files (LOGSECOND) = 60

and sec_log_used_top  and sec_logs_allocated can be obtained from executing command:
db2 GET SNAPSHOT FOR DATABASE ON <database_name>
Example output:

        Log space available to the database (Bytes)= 244315359
        Log space used by the database (Bytes)     = 484641
        Maximum secondary log space used (Bytes)   = 1205816400
        Maximum total log space used (Bytes)       = 581636
        Secondary logs allocated currently         = 24

NOTE: that if both or any one from LOGSECOND and LOGFILSIZ is less than/equal to 0 then sec_log_used_pct will be 0.

As an example, if the db2 outputs showed:

sec_log_used_top = 1205816400
logsecond = 60
logfilsiz = 5000

"Sec Log Used Percent" attribute value.
sec_log_used_pct = 100 * 1205816400 / (60 * 5000 * 4096) = 98.13%

...

The formula used to calculate the "Current Secondary Log Used Percent" attribute is:

curr_sec_log_used_pct = sec_logs_allocated / logsecond

NOTE: if LOGSECOND is equal to 0 then curr_sec_log_used_pct will be 100.

As an example, if the db2 outputs showed:

logsecond = 60
Secondary logs allocated currently         = 24

"Current Secondary Log Used Percent" attribute value:

curr_sec_log_used_pct = 24 / 60 = 40%

Compare the calculated value with what is seen in the TEP for the "Database Logging Overview" workspace (or situation alert details) to see if the value from UD agent matches what would be expected based on DB2 commands.

The "Database Logging Overview" workspace" is under the "Log Manager" navigator icon, and shows that the query that populates the "Active Log" view is using "DB2 Archive Log All (622)" query, which relies on the "KUDLOG" attribute group:

To verify the values returned by DB2 to UD agent, enable the following RAS1 tracing and refresh the workspace in the TEP, or recreate the issue with the "false" situation alert.

KBB_RAS1=ERROR (UNIT:KUDLGAGT.CPP ALL) (UNIT:KRA ALL) (UNIT:CSSLGAGT.CPP ALL)

Example tracepoints:

LOGFILSIZ:
csslgagt.cpp,647,"GetDBConfigParams") DB config logfilsiz is 5000

LOGSECOND:
csslgagt.cpp,655,"GetDBConfigParams") DB config logsecondary is 60

sec_log_used_top:
csslgagt.cpp,289,"CollectParsedSQLMA_DBASE2ALLint") Sec logs used top is 1205816400

Keywords:
drd401709
5724B96DO
kuddb2

Additional ITM Agent Insights series of IBM Tivoli Monitoring Agent blogs are indexed under ITM Agent Insights: Introduction.