Teradata Multi-System Manager is an application for monitoring and administration of single and multi-system environments. TMSM Report Viewer allows TMSM (Teradata Multi-System Manager) users to create and view reports about the Teradata ecosystem using data collected by TMSM. The TMSM data model is in Appendix E of the Configuration Guide. For more information about TMSM, please read this article.
This article provides information about TMSM reporting capabilities, shows how to create custom reports, and reviews the out-of-the-box reports provided.
The TMSM Report Viewer Portlet provides a number of features to the user:
The following steps you through how an Adminstrator will create a report.
The following outlines the steps used to share a report that has been created. This can only be done by a user with TMSM administrator priviledges.
The following provides a brief description of each report type that is available with TMSM Report Viewer (Bar, Line, Table, Pie). This section includes example reports with the SQL code used to produce the resultset and the report output when necessary.
For Bar and Line charts, the portlet will support large result sets by providing pagination. The example below represents one page of data (the preferences for this report was set to show 10 rows per page).
This example report is for Process Duration for 3 processes (catload, pload, sload)
SUM(CASE WHEN ResourceId LIKE 'load_cat%' THEN prr.TotalRunTime ELSE NULL END) AS "catload",
SUM(CASE WHEN ResourceId LIKE 'load_pro%' THEN prr.TotalRunTime ELSE NULL END) AS "pload",
SUM(CASE WHEN ResourceId LIKE 'load_sal%' THEN prr.TotalRunTime ELSE NULL END) AS "sload"
SELECT ResourceId, ResourceType, TDPID, RunDate, SUM(RunTime) TotalRunTime FROM
SELECT e.ResourceId, e.ResourceType, e.TDPID, e.UOWId, MIN(CAST(e.EventTS AS DATE)) RunDate,
WHEN (MAX(CASE WHEN EventType='START' THEN EventTS END)) (Named RunStart) IS NULL
OR (MAX(CASE WHEN EventType='END' THEN EventTS END)) (Named RunEnd) IS NULL
THEN NULL ELSE TimestampDiffSeconds( RunStart,RunEnd) END) RunTime
FROM TMSMViews.MSMEvent e
WHERE EventType IN ('START','END') AND e.UOWId<>'NA' AND ResourceId LIKE 'load%'
GROUP BY 1,2,3,4
GROUP BY 1,2,3,4
GROUP BY 1;
Result set data and format:
The first column is type label (either time or name). The next columns are the value series for each measured object where the column titles represent the measured objects (processes in this example).
Pie charts are not paginated and can only accommodate up to 20 rows of data. If the data returned has more than 20 rows the portlet will generate an error.
SELECT ResourceType as name, count(distinct ResourceId) as cnt FROM TMSMViews.MSMEvent
GROUP BY 1
WHERE EventDate between current_date - 60 and current_date
Result set format is in name and value pairs.
The Table format report type is shown in a spreadsheet style with pagination.
Example of report code for viewing MSM events:
SELECT r.ResourceCategory, r.ResourceSubCategory, e.EventType,
e.EventTS, e.TDPID, e.ResourceId, e.ResourceType, e.JobName,
e.JobStep, e.UOWId, e.EventMsg EVENT_MESSAGE, e.UOWHealthAmt,
e.UOWHealthStr, e.AlertCode, e.SeverityLvl Condition, e.EcosystemId,
e.EventSourceSystem, e.EventSourceUser, e.EventDML, e.AffectedDatabaseName DatabaseName,
e.AffectedTableName TableName, e.Optional1, e.Optional2, e.Optional3,
e.ApplicationId, e.StateCode State, OREPLACE(e.LogFile,'\','\') LogFile,
FROM MSMEvent e LEFT OUTER JOIN MSMResource r
There are no format restrictions for the Table result sets.
Only a few sample reports are released with the TMSM product, but we expect that there will be many customers and professional services people who will design clever and useful reports that could be helpful for others. If you are one of those people we request that you either publish your SQL template on DevX or provide the SQL and the explanation to us and we will publish for you on the Teradata Developer Exchange.