Customization of Operational Rep by pengxiuhui

VIEWS: 275 PAGES: 9

									Customization of Operational Reporting for use with TSM 6.1
IBM Tivoli Storage Manager 6.1 introduces new extensive reporting functionality that replaces
TSM Operational Reporting. The new TSM 6.1 reporting is based on the IBM Tivoli Monitoring
and IBM Common Reporting solutions requiring a separate server to run. TSM Operational
Reporting is not supported with TSM 6.1. The new TSM 6.1 reporting functionality supports
reporting on both TSM 5.x and 6.1 servers.

There are situations where it may be of interest to continue using TSM Operational Reporting in
an interim phase, especially in small single Windows TSM server environments and for existing
TSM 5.x customers upgrading to TSM 6.1 that have made investments in the TSM 5.x reporting
functionality.

This document shows how to customize default reports and monitors in TSM 5.5.3 Operational
Reporting to make it work with a TSM 6.1 server.

Note: This customization is done at you own risk and is not supported by IBM in any way!

Please note that the change from a proprietary TSM database to standard DB2 with TSM 6.1
introduces fundamental changes to the TSM database and recovery log architecture and
corresponding TSM database schema used by TSM Operational Reporting. In some cases there
are no direct TSM 6.1 equivalents (maximum recovery log utilization as an example) to TSM 5.5
information. TSM 6.1 also introduces a stricter adherence to the SQL 92 standard. This has
impact on some Operational Reporting report sections and may also limit the usefulness of TSM
Operation Reporting as a monitoring solution.

Refer to “Changes to the SELECT command” in the TSM 6.1 Admin Guide for details on the
changes and also to information available in the TSM 6.1 Information Center:
http://publib.boulder.ibm.com/infocenter/tsminfo/v6/index.jsp?topic=/com.ibm.itsm.srv.upgrd.do
c/r_srv_upgrd_cmd_select.html

When upgrading a TSM environment to 6.1 the out of the box TSM Operational Reporting
reports and potentially customized reports may fail. Typically with messages similar to the ones
below citing syntax errors or references to non-existing columns.
The out-of-the-box Custom Summary report sections in Operational Reports and Hourly Monitor
is know to fail with TSM 6.1, but customized/home grown reports may also fail.

Preparations
TSM Operation Reporting is part of the TSM management console installed as part of a TSM 5.x
Windows TSM server install. The TSM management console may also be installed separately on
a Windows server to report on non-Windows TSM servers.
Operational Reporting is not available with the Windows management console shipped and
installed with TSM 6.1 and if a windows TSM 5.x server is upgraded to TSM 6.1, the 6.1
management console is installed without the Operational Reporting functionality.

There is a TSM 6.1 APAR that may cause TSM Operational Reporting may fail (APAR
IC63754)
“ANR0102E MESSAGE SEEN WHEN ISSUING SELECT * FROM OPTIONS”
ERROR DESCRIPTION:
Issuing SELECT * FROM OPTIONS results in the following message.

ANR0102E admsql.c(5099): Error 1 inserting row in table
"Options.SQL".

This is seen in situations where a duplicate devconfig line is
added into the server options file. For example.

DEVCONFig              devconfig
DEVCONFig              devconfig

Tivoli Storage Manager Versions Affected: 6.1

Initial Impact: Low

Additional Keywords: TSM, dev, config, DB2, insert

LOCAL FIX:
Remove one of the duplicate devconfig lines from the option
file.
To address this any duplicate DEVCONFIG and VOLHIST statements in dsmserv.opt has to be
removed. A common reason for having duplicate entries in dsmserv.opt is situations where the
TSM 5 to 6 upgrade process is restarted.

To customize TSM Operation Reporting to work with a TSM 6.1 server the TSM 5.5.x
Management Console (containing Operational Reporting) has to be installed on a separate
physical or virtual Windows server.

The latest TSM 5.5.x management console is available here
ftp://service.boulder.ibm.com/storage/tivoli-storage-management/maintenance/server/v5r5/WIN/5.5.3.0/



     1. Download and execute the 5.5.x.x-TIV-TSMCON-Windows.exe
     2. Configure TSM Operational Reporting according to the instructions here:
        http://publib.boulder.ibm.com/infocenter/tivihelp/v1r1/index.jsp?topic=/com.ibm.itsmcw.
        doc/anrwgd55668.htm
     3. Backup the TSM V5 XML reporting and monitor template files.
        Create backup copies of the default_rep_eng.xml and default_mon_eng.xml files
        located in C:\Program Files\Tivoli\TSM\console
     4. Create a new reporting XML template file
        Copy the C:\Program Files\Tivoli\TSM\console\default_rep_eng.xml file to
        C:\Program Files\Tivoli\TSM\console\default_rep_eng_TSM61.xml and use this as a
        basis for customization of Operational Reports
     5. Create a new monitor XML template file
        Copy the C:\Program Files\Tivoli\TSM\console\default_mon_eng.xml file to
        C:\Program Files\Tivoli\TSM\console\default_mon_eng_TSM61.xml and use this as a
        basis for customization of Operational Monitors


Customization of Operational Reporting Reports
The standard, out-of-the box TSM Operational Reporting contains a “model” template for a Daily
Report. This Daily Report contains different sections and the Custom Summary report section
must be modified.

     1. Start the TSM 5.5.x Management Console (Start/All Programs/Tivoli Storage
        Manager/Management Console)
     2. Select Operational Reports and create a new (or update an existing) report
     3. Select “Custom Summary” and click “Settings”.




     4. Browse for the newly created default_rep_eng_TSM61.xml template




     5. Then edit the different “Select Variables” described below and change the SQL select
        statements as described below.



LDBB / Last Database Backup
Original select statement

select substr(char(last_backup_date), 1, 16) as LDBB from db where cast((current_timestamp-last_backup_date)hours as decimal)<%s


New select statement
select substr(char(last_backup_date), 1, 16) as LDBB from db where cast(hour(current_timestamp-last_backup_date) as decimal)<%s




PCTUTILIZED / %Database Utilization
This has no direct equivalent in TSM 6.1.

The following are potential values that may be used as replacements
    TOT_FILE_SYSTEM_MB
    USED_DB_SPACE_MB
    FREE_SPACE_MB

They may however give misleading information as TOT_FILE_SYSTEM_MB is the total
filesystem that may contain other stuff than the DB.
FREE_SPACE/TOT_FILE_SYSTEM_MB is probably the most relevant parameter to monitor
Original select statement
select pct_utilized from db


Suggested new select statement

select sum(100-(free_space_mb*100) / tot_file_system_mb) from db

For TSM 6.1
select cast(TOT_FILE_SYSTEM_MB as decimal(14,0)), cast(USED_DB_SPACE_MB as decimal(14,0)),
cast(cast(USED_DB_SPACE_MB as decimal(14,0)) / cast(TOT
_FILE_SYSTEM_MB as decimal(14,0))*100 as integer), LAST_BACKUP_DATE, PHYSICAL_VO
LUMES from DB
Results will look like the following:
Unnamed[1]: 17344
Unnamed[2]: 448
Unnamed[3]: 2 <------------------ this would be the % utilized
LAST_BACKUP_DATE: 2009-06-15 16:46:32.000000
PHYSICAL_VOLUMES: 0

Note: The customer who tested this also provided an alternative select they developed as follows:
SELECT (DECIMAL(sum(USED_DB_SPACE_MB),19,5)*100/sum(TOT_FILE_SYSTEM_MB)) as PCT_Util FROM DB order by
PCT_Util

PCT_UTIL
----------------------------------
2.583025
(for additional information purpose only)
For TSM 5.5 the TCR select used is as follows:
select CAPACITY_MB, (CAPACITY_MB * PCT_UTILIZED), PCT_UTILIZED, LAST_BACKUP_DATE, PHYSICAL_VOLUMES
from DB




CACHE_PCT / Database Cache Hit Ratio

Original select statement
select CACHE_HIT_PCT as CACHE_PCT from DB


New select statement

select BUFF_HIT_RATIO as CACHE_PCT from DB




MAX_PCT_UTILIZED / % Maximum Recovery Log Utilization

Original select statement

select max_pct_utilized from log
This section will either have to be deleted as this is no direct equivalent TSM 6.1 or it can
alternatively be changed to report on current utilization for the active recovery log.

Suggested new select statement
select cast(sum(used_space_mb *100 / total_space_MB) as decimal(3,1)) from log




RESET_BUFFPOOL

This section will have to be deleted as this is not used with TSM 6.1.

Original select statement

reset bufpool


Delete the RESET_BUFFPOOL select variable




TMS / Number of tape mounts
Original select statement

select count(*) as TMS from summary where activity='TAPE MOUNT' and cast((current_timestamp-start_time)hours as decimal)<%s


New select statement

select count(*) as TMS from summary where activity='TAPE MOUNT' and cast(hour(current_timestamp-start_time) as decimal)<%s




MTM / Total number of minutes tape were mounted

Original select statement

select (sum(end_time-start_time))minutes as MTM from summary where activity='TAPE MOUNT' and cast((current_timestamp-
start_time)hours as decimal)<%s


New select statement
select (sum(minute(end_time-start_time))) as MTM from summary where activity='TAPE MOUNT' and cast(hour(current_timestamp-
start_time) as decimal)<%s
Click “OK” and “Yes” to save the changes




Customization of Operational Reporting Monitors

When upgrading a TSM environment to 6.1 the out of the box TSM Operational Monitor will fail.
Typically with messages similar to the ones below citing syntax errors or references to non-
existing columns.




The standard, out-of-the box TSM Operational Reporting Monitor contains a “model” template
for an Hourly Monitor. This Hourly Monitor contains a “Custom Summary” section that needs to
be modified.
   1. Start the TSM 5.5.x Management Console (Start/All Programs/Tivoli Storage
      Manager/Management Console)
   2. Select Operational Monitorss and create a new (or update an existing) Hourly Monitor
   3. Select “Custom Summary” and click “Settings”




   4. Browse for the new template (C:\Program
      Files\Tivoli\TSM\console\default_mon_eng_TSM61.xml)




   5. Then edit the different “Select Variables” described below and change the SQL select
      statements as described below.




PCTUTILIZED / %Database Utilization
This has no direct equivalent in TSM 6.1.

The following are potential values that may be used
TOT_FILE_SYSTEM_MB, USED_DB_SPACE_MB or FREE_SPACE_MB

They may however give misleading information as TOT_FILE_SYSTEM_MB is the total
filesystem that may contain other stuff than the DB.
FREE_SPACE/TOT_FILE_SYSTEM_MB is probably the most relevant parameter to monitor
Select “PCTUTILIZED / %Database Utilization” and select “Edit…”

Original select statement
select pct_utilized from db


Suggested new select statement

select sum(100-(free_space_mb*100) / tot_file_system_mb) from db




MAX_PCT_UTILIZED / % Maximum Recovery Log Utilization

Select “MAX_PCT_UTILIZED / % Maximum Recovery Log Utilization” and select “Edit…”

Original select statement

select max_pct_utilized from log


This section will either have to be deleted as this is no direct equivalent TSM 6.1 or alternatively
it can be changed to report on current utilization for the active recovery log.

Suggested new select statement
select cast(sum(used_space_mb *100 / total_space_MB) as decimal(3,1)) from log




Click “OK” and “Yes” to save the changes

								
To top