Metrics Database Tables

Topic Contents: ShowHide

Count Table
Component Tables
DataSmithBatch tables
Scheduler Tables
Database Diagram of M2 Tables

The intended audience for this topic is a CM4D administrator that will create reports with SQL queries to retrieve the required information.

The following database tables are used to store information related to Metrics and Monitoring. The burden is on the end-user to take the records contained in these tables to collate, query, and report the information that individual users may find useful.

Database Access

The database is considered to be read-only. CM4D will provide mechanisms for populating and maintaining the content of the tables. At no time should you alter the data in the database, add data to existing database tables, or alter existing stored procedures.

Any access to the data should only be made via SQL Queries. Direct access to the tables is neither recommended nor supported, and could result in incompatibilities with future versions of CM4D. ATS reserves the right to alter the database objects (tables, indexes, constraints, stored procedures) at any time. ATS will, endeavour to maintain consistency, and any changes will be documented.

Deletion and Orphans

If a record is deleted, all subordinate data in other tables will be deleted as well. Orphan data is not permitted.

Database Table Structure

The CM4D Metrics and Monitoring data structure is comprised of the following objects within the database. See also the section Database Diagram of M2 Tables at the end of this topic.

Count Table

The Count table records which users are running an active Scheduler service and how many jobs each user has in the Queue at the time of the count. Each user is assigned a unique ID in the table.

Object	Description	Db Object	Type
Count table		[M2Count]	Table
	Component Identifier - a unique number that identifies the record.	[M2C_ID]	ATS enumeration
	Type - what is being monitored -	[M2C_Type]	1 = Scheduler Job Queue
	Time - timestamp of when count was recorded.	[M2C_Time]	Date and Time of record (yyyy-mm-dd hh:mm:ss)
	Count Description - Username of user running the component; a record is made for each individual user.	[M2C_CountDesc]	User Label
	Count - number of items currently being processed by the component; in the CM4D Scheduler Manager Job Queue tab, this is the same as the field "Number of Jobs in Queue".	[M2C_Count]	Integer

As of now, only the Scheduler Job Queue can be monitored for a count. Monitoring for other services will be added in a future release.

Component Tables

The Component tables contain records related to when a services starts, is it still running, and when it is stopped. If the service does not end properly (forcibly closed, license server error, etc.), no record of the stopped component will be recorded in the database.

Object	Description	Db Object	Type
Component Launch table		[M2ComponentLaunch]	Table
	Identifier for Component Launcher - a unique string that identifies records related to a component launch.	[MCL_ID]	Database Auto Index Value
	Time - timestamp of when the component was launched (started).	[MCL_Time]	Date and Time of record (yyyy-mm-dd hh:mm:ss)
	Component Identifier - a number that indicates which component was launched (started). 105 = Launched Scheduler Client 106 = Unmanaged Scheduler Client 601 = CM4D Scheduler 1001 = DataSmithBatch 1701 = CM4D Launcher	[MCL_ComponentID]	ATS enumeration:
	Hostname - indicates the host computer from which the component was launched.	[MCL_Hostname]	User Label
	Process Identifer - a value that identifies the process that is running the component.	[MCL_PID]	System Value (number)
	Username - the CM4D user that is running the process (Windows username).	[MCL_Username]	User Label
Component Pulse table		[M2ComponentPulse]	Table; Foreign Key - MCL_ID (M2ComponentLaunch).
	Identifier for Component Pulse - a unique string that identifies the running component. This Pulse may also be referred to as the "Heartbeat" of a component.	[MCP_ID]	Database Auto Index Value
	Identifier for Component Launched - the ID of the component launch that is connected to the pulse.	[MCP_MCL_ID]	Database Auto Index Value
	Time - timestamp of when the component last sent a pulse to notify it is still running.	[MCP_Time]	Date and Time of record (yyyy-mm-dd hh:mm:ss)
Component Stop table		[M2ComponentStop]	Table; Foreign Key - MCL (M2ComponentLaunch).
	Identifier for Component Pulse - a unique string that identifies which component was stopped.	[MCS_ID]	Database Auto Index Value
	Identifier for Component Launched - a unique string that identifies which component launch was stopped.	[MCS_MCL_ID]	Database Auto Index Value
	Time - timestamp of when the component was stopped.	[MCS_Time]	Date and Time of record (yyyy-mm-dd hh:mm:ss)

DataSmithBatch tables

The DataSmithBatch tables contain records related to DataSmithBatch job processing, including when a job is started, completed, what file was processed, what the result of that file was, and information about the Routine(s) related to that job.

Object	Description	Db Object	Type
DataSmithBatch Job Started table		[M2DsBJobStart]	Table; Foreign Key - MCL_ID (M2ComponentLaunch)
	Identifier for Job Started - a unique string that identifies which Batch job is started.	[MDSBJS_ID]	Database Auto Index Value
	Identifier for Component Launched - the ID of the component launch that was connected to the Batch job started.	[MDSBJS_MCL_ID]	Database Auto Index Value
	Time - timestamp of when the Batch job was started.	[MDSBJS_Time]	Date and Time of record (yyyy-mm-dd hh:mm:ss)
	Host Identifier - indicates the host computer that is processing the Batch job; since there can only be one set of jobs per host, even if there are multiple servers with the same job names, the job can be distinguished by the host ID.	[MDSBJS_Hostname]	User Label
	Job Name - label of the DataSmithBatch job used when the component was launched. No path is included, as the full path can be referenced via the DataSmithBatch Manager.	[MDSBJS_Jobname]	User Label
DataSmithBatch Job Completed table		[M2DsBJobComp]	Table; Foreign Key - MDSBJS (M2DsBJobStart)
	Identifier for Job Completed - a unique string that identifies which Batch job is completed.	[MDSBJC_ID]	Database Auto Index Value
	Identifier for Job Started - the ID that identifies which started job was stopped.	[MDSBJC_MDSBJS_ID]	Database Auto Index Value
	Time - timestamp of when the Batch job was completed (finished).	[MDSBJC_Time]	Date and Time of record (yyyy-mm-dd hh:mm:ss)
DataSmithBatch File Processed table		[M2DsBFileProc]	Table; Foreign Key - MDSBJC (M2DsBJobComp)
	Identifier for File Processed - a unique string that identifies the file processed for the Batch job.	[MDSBFP_ID]	Database Auto Index Value
	Identifier for Job Completed - the ID of the DataSmithBatch Job that completed the file processing.	[MDSBFP_MDSBJC_ID]	Database Auto Index Value
	Name of Processed File - the name of the data file processed by the Batch job from the Inbox with label and extension, but no path.	[MDSBFP_FileName]	XML
DataSmithBatch File Processed Summary table		[M2DsBFileProSum]	Table; Foreign Key - MDSBFP (M2DsBFileProc)
	Identifier for Summary of File Processed - a unique string that identifies the summary for the file processed.	[MDSBFPS_ID]	Database Auto Index Value
	Identifier for File Processed - the ID of the file processed summarized.	[MDSBFPS_MDSBFP_ID]	Database Auto Index Value
	File Processed Summary Name - what happened to the file that was processed; Disposition.	[MDSBFPS_Name]	ATS enumeration
	File Processed Summary Value - where the file ended up after processing; Outbox or Errorbox.	[MDSBFPS_value]	ATS enumeration
DataSmithBatch Routine Processed table		[M2DsBRoutProc]	Table; Foreign Key - MDSBJS (M2DsBJobStart)
	Identifier for Routine Processed - a unique string that identifies the Routine that triggered the Batch job.	[MDSBRP_ID]	Database Auto Index Value
	Identifier for Job Started - identifies which started job processed the Routine.	[MDSBRP_MDSBJS_ID]	Database Auto Index Value
	Identifier for DataSource - identifies which DataSource the data was processed into.	[MDSBRP_DataSource_ID]	Database Auto Index Value
	Identifier for Routine - identifies the Routine the data was processed for.	[MDSBRP_Routine_ID]	Database Auto Index Value
DataSmithBatch Routine Processed Summary table		[M2DsBRoutProSum]	Table; Foreign Key - MDSBRP (M2DsBRoutProc)
	Identifier of the Routine Processed Summary - a unique string that identifies the summary for the Routine used by the processed file.	[MDSBRPS_ID]	Database Auto Index Value
	Identifier of the Routine Processed - the ID of the Routine processed related to the summary.	[MDSBRPS_MDSBRP_ID]	Database Auto Index Value
	Routine Processed Summary Name - the result of the processed file, a summary of the data processed. New Routine Feature Count New Feature Count Sample Count New Sample Count Actuals Count New Actuals Count	[MDSBRPS_Name]	XML
	Routine Processed Summary Name Value - the value of the processed file result. Routine Name Value of Count	[MDSBRPS_value]	ATS enumeration

Scheduler Tables

The Scheduler tables contain records related to CM4D Scheduler job processing, including when a job is received, queued, started, completed, and the result of the report when finished.

Object	Description	Db Object	Type
Scheduler Job Started table		[M2SchJobStart]	Table; Foreign Key - M2ComponentLaunch; M2SchJobQueue
	Identifier for Job Started - a unique string that identifies the Scheduler job started.	[MSJS_ID]	Database Auto Index Value
	Identifier for Component Launched - the ID of the component launched that started the Scheduler Job.	[MSJS_MCL_ID]	Database Auto Index Value
	Identifier for Job Queued - the ID of the Job Queued as a result of the Scheduler job started.	[MSJS_MSJQ_ID]	Database Auto Index Value
	Time - timestamp of when the Scheduler job was started.	[MSJS_Time]	Date and Time of record (yyyy-mm-dd hh:mm:ss)
Scheduler Job Queue table		[M2SchJobQueue]	Table; no foreign key;
	Identifier of Job Queue for DataSmithBatch - a unique string that identifies the DataSmith Batch job that triggered the Scheduler job in the Queue.	[MDSBJQ_ID]	Database Auto Index Value
	Identifier for DataSmithBatch Job Routine Processed - the ID of the Routine used by the processed data file in DataSmith Batch to trigger the Scheduler job; <null> is a valid entry, as Timed Jobs are not triggered by a Routine.	[MDSBJQ_MDSBRP_ID]	Database Auto Index Value
	Time - timestamp of when the Scheduler job was entered in the Job Queue.	[MDSBJQ_Time]	Date and Time of record (yyyy-mm-dd hh:mm:ss)
	Scheduler Job Identifier - a unique string that identifies the Scheduler job used to process the job in the Queue.	[MDSBJQ_SS_SCHEDULEID]	Database Auto Index Value
	Managed Document Identifier - a unique string that identifies the Managed Document used to process the Scheduler job.	[MDSBJQ_MD_ID]	Database Auto Index Value
	Sample Label - the label of the sample that triggered the queued job.	[MDSBJQ_S_LABEL]	ATS enumeration
Scheduler Job Received table		[M2SchJobReceived]	Table; Foreign Keys - MCL (M2ComponentLaunch); MSJS (M2SchJobStart)
	Identifier for Job Received - a unique string that identifies the job received in the Scheduler queue.	[MSJR_ID]	Client ID
	Identifier for Component Launched - the ID of the component launched that triggered the received job.	[MSJR_MCL_ID]	Database Auto Index Value
	Identifier for Job Started - the ID of the job started that triggered the received job.	[MSJR_MSJS_ID]	Database Auto Index Value
	Time - timestamp of when the Scheduler job was received.	[MSJR_Time]	Date and Time of record (yyyy-mm-dd hh:mm:ss)
Scheduler Job Completed table		[M2SchJobComp]	Table; Foreign Key - MSJR (M2SchJobReceived)
	Identifier for Job Completed - a unique string that identifies the Scheduler job that was completed.	[MSJC_ID]	Database Auto Index Value
	Identifier for Job Received - the ID of the job received that was completed.	[MSJC_MSJR_ID]	Database Auto Index Value
	Time - timestamp of when the Scheduler job was completed (finished).	[MSJC_Time]	Date and Time of record (yyyy-mm-dd hh:mm:ss)
Scheduler Job Completed Summary table		[M2SchJobCompSum]	Table; Foreign Key - MSJC (M2SchJobComp)
	Job Completed Summary Identifier - a unique string that identifies the completed job summary.	[MSJCS_ID]	Database Auto Index Value
	Job Completed Identifier - the ID of the completed Scheduler job that is summarized.	[MSJCS_MSJC_ID]	Database Auto Index Value
	Job Completed Summary Name - the result of the completed Scheduler job.	[MSJCS_Name]	ATS enumeration: FileNameOfReport - full path and filename of report TotalSheets - total number of sheets in report JobDisposition - end result of job; either JobFinished or JobFailed
	Job Completed Summary Name Value - the vaule of the result of the completed Scheduler job.	[MSJCS_value]	ATS enumeration: Path, Value, or Disposition

Database Diagram of M2 Tables

Below is a Database Diagram built by SQL Server Management Studio that includes all of the M2 (Metrics and Monitoring) tables in the Site database. This diagram shows how the tables are related to one another, and where Foreign Keys are present.