Table and View Information for Microsoft SQL Server
This section describes the table and view information for Microsoft SQL Server.
Microsoft SQL Server Table Sizes
When determining the sizing and administration procedures for these tables, take their growth characteristics into consideration.
Microsoft SQL Server Tables that are Stable in Size
Once their names (app_name and instance_name) have been inserted once, the following tables do not grow; thus, these tables reach a stable size quickly:
- P_APPS
- P_INSTANCES
Microsoft SQL Server Tables that Continuously Grow
The P_HISTORY table incurs inserts after every PATROL parameter polling interval. For information about keeping the P_HISTORY table size manageable, see Improving-the-PATROL-HL-KM-Performance.
Microsoft SQL Server Tables that are Purged After Each Transaction
The P_TMP_HISTORY_DATA table is purged of data after each transaction; so it only needs to be large enough to support the largest transaction.
Microsoft SQL Server Tables Populated by the PATROL HL KM
The PATROL HL KM populates the following Microsoft SQL Server tables:
- P_APPS
- P_INSTANCES
- P_HISTORY
- P_TMP_HISTORY_DATA
MS SQL Server P_APPS Table
Field Name | Data Type | Null? | Definition |
|---|---|---|---|
app_id | INT | NULL | application identifier; primary key |
node_name | VARCHAR(32) | NOT NULL | node identifier; foreign key to p_nodes |
app_name | VARCHAR(32) | NOT NULL | PATROL application name |
parameter_name | VARCHAR(32) | NULL | PATROL parameter name |
MS SQL Server P_INSTANCES Table
Field Name | Data Type | Null? | Definition |
|---|---|---|---|
instance_id | INT | NULL | instance identifier; primary key |
instance_name | VARCHAR(32) | NOT NULL | PATROL instance name |
MS SQL Server P_HISTORY Table
Field Name | Data Type | Null? | Definition |
|---|---|---|---|
history_id | INT | NULL | history identifier; primary key |
app_id | INT | NOT NULL | application identifier; foreign key to p_apps |
instance_id | INT | NOT NULL | instance identifier; foreign key to p_instances |
timestamp | DATETIME | NOT NULL | date and time of occurrence of value |
value | FLOAT | NULL | value of the PATROL parameter |
MS SQL Server P_TMP_HISTORY_DATA Table
Field Name | Data Type | Null? | Definition |
|---|---|---|---|
app_id | INT | NULL | application identifier; foreign key to p_apps |
instance_id | INT | NULL | instance identifier; foreign key to p_instances |
tmp_app_name | VARCHAR(32) | NULL | PATROL application name |
tmp_instance_name | VARCHAR(32) | NULL | PATROL instance name |
tmp_parameter_name | VARCHAR(32) | NULL | PATROL parameter name |
tmp_time_stamp | VARCHAR(32) | NULL | date and time of occurrence of value |
tmp_value | VARCHAR(255) | NULL | value of the PATROL parameter |
tmp_node_name | VARCHAR(32) | NULL | node identifier; foreign key to p_nodes |
Microsoft SQL Server Views
The PATROL HL KM populates the following Microsoft SQL Server view:
MS SQL Server P_VIEW Table
Field Name | Data Type | Null? | Definition |
|---|---|---|---|
node_name | VARCHAR(32) | NOT NULL | node name |
app_name | VARCHAR(32) | NOT NULL | PATROL application name |
instance_name | VARCHAR(32) | NULL | PATROL instance name |
parameter_name | VARCHAR(32) | NULL | PATROL parameter name |
time_stamp | DATETIME | NOT NULL | date and time of occurrence of value |
value | FLOAT | NULL | value of the PATROL parameter |