Data Dictionary for Monitoring Database

For analyzing data dictionary in SQuirrel client, the create table scripts can be generated along with all constraints / indexes:

Refer to the attached file ‘create_table_trendingdb_derby.sql‘ for the generated DDL.

Alternatively following queries can be used to extract the data dictionary:

select t.TABLENAME, t.TABLETYPE, s.SCHEMANAME
from SYS.SYSTABLES t, SYS.SYSSCHEMAS s
where t.schemaid = s.schemaid
and s.schemaname='APP';







----

select t.TABLENAME, c.CONSTRAINTNAME, c.TYPE, s.SCHEMANAME
from SYS.SYSCONSTRAINTS c, SYS.SYSTABLES t, SYS.SYSSCHEMAS s
where c.schemaid = s.schemaid
and c.tableid = t.tableid
and s.schemaname='APP';








---

select s.SCHEMANAME, t.TABLENAME, g.conglomeratename, g.isindex, g.isconstraint
from SYS.SYSTABLES t, SYS.SYSSCHEMAS s, SYS.SYSCONGLOMERATES g
where g.schemaid = s.schemaid
and g.tableid = t.tableid
and s.schemaname='APP'
and (g.isindex = 'true' or g.isconstraint='true')
order by t.TABLENAME;








---

**Note: Default row limit in SQuirrel client is 100. This limit is configurable or the setting can be turned off altogether (no limits). The setting is present in the
SQuirrel client on the SQL tab towards top right.

A clear trend which comes up based on the output of the above queries / script:

• Only tables, indexes and constraints are present in monitoring database. No views, procedures, materialized views etc. exists
• Auto-generated sequence keys are used as Primary Keys for all the four tables
• Enforced referential integrity i.e. PK-FK relationship exists between
  • TREND_DETAILS (PK) and MANAGED_ENTITY_STATUS_DETAILS (FK)
  • TREND_DETAILS (PK) and TREND_DATA (FK)
• Index type is either unique or non-unique
• Timestamp is stored in BIGINT format (epoch time) in TREND_DATA and MANAGED_ENTITY_STATUS_DETAILS table

Building Monitoring Report Queries

Some common monitoring reporting scenarios are listed below:

Example scenarios:

• List of different metrics available in the BOE system:

select distinct td.METRICNAME, td.TYPE

from TREND_DETAILS td

where td.TYPE=’Metric’;

——

• List of watches

select distinct w.CUID, w.NAME, td.METRICNAME, td.TYPE

from TREND_DETAILS td, MANAGED_ENTITY_STATUS_METRICS w

where td.CUID = w.CUID;

—-

• List of watches associated with metrics

select distinct w.NAME, td.METRICNAME, td.TYPE

from TREND_DETAILS td, MANAGED_ENTITY_STATUS_METRICS w

where td.DETAILSID = w.DETAILSID

–and td.TYPE=’Metric’ –Optional filter

order by w.NAME;

—-

• Trend values of metrics for a specific watch since 09-Feb-2015

select w.NAME, td.METRICNAME, t.MESSAGEKEY, t.TIME,

{fn TIMESTAMPADD( SQL_TSI_SECOND, t.TIME/1000, timestamp(‘1970-01-01-00.00.00.000000’))} UTC ,

t.VALUE

from TREND_DETAILS td, TREND_DATA t, MANAGED_ENTITY_STATUS_METRICS w

where td.DETAILSID = t.DETAILSID

and td.DETAILSID = w.DETAILSID

and w.NAME='<Node>. InputFileRepository Watch’  —This is an example

and t.TIME >= 1423440000000; —equivalent epoch time in milliseconds for 09-Feb-2015 00:00:00 UTC

–—

**The above query converts epoch time to regular time in UTC.

Concluding Remarks

The above write-up is not an exhaustive reference on monitoring database or monitoring functionality. The readers are encouraged to validate the above contents in line with standard BI Platform admin guide. Comments are welcome to further enhance the contents of this blog post. Thanks for your time 🙂