Table, Trigger, and View Information for DB2
This section describes the table, trigger, and view information for DB2.
DB2 Table Sizes
When determining the sizing and administration procedures for these tables, take their growth characteristics into consideration.
DB2 Tables that are Stable in Size
Once all of their names (node_name, app_name, instance_name, and parameter_name) have been inserted once, the following tables do not grow; thus, these tables reach a stable size quickly:
- P_NODES
- P_APPS
- P_INSTANCES
- P_PARAMETERS
DB2 Tables that Continuously Grow
These tables incur inserts after every PATROL parameter polling interval.
- P_HISTORY
- P_HISTORY_DATA
For information about keeping the P_HISTORY table size manageable, see Improving-the-PATROL-HL-KM-Performance.
DB2 Tables that are Purged after Each Transaction
The following tables are purged of data after each transaction; so they only need to be large enough to support the largest transaction.
- P_TMP_HISTORY_DATA
- P_APPS_TEMP
- P_HISTORY_TEMP
- P_INSTANCES_TEMP
- P_NODES_TEMP
- P_PARAMETERS_TEMP
DB2 Tables Populated by PATROL History Loader Knowledge Module
The the PATROL HL KM populates the following DB2 tables:
- P_NODES
- P_NODES_TEMP
- P_APPS
- P_APPS_TEMP
- P_INSTANCES
- P_INSTANCES_TEMP
- P_HISTORY
- P_HISTORY_DATA
- P_HISTORY_TEMP
- P_PARAMETERS
- P_PARAMETERS_TEMP
- P_TMP_HISTORY_DATA
DB2 P_NODES Table
Field Name | Data Type | Null? | Definition |
|---|---|---|---|
node_id | INTEGER | NOT NULL | node identifier; primary key |
node_name | VARCHAR(32) | NOT NULL | node name |
DB2 P_NODES_TEMP Table
Field Name | Data Type | Null? | Definition |
|---|---|---|---|
node_name | VARCHAR(32) | NOT NULL | node name |
DB2 P_APPS Table
Field Name | Data Type | Null? | Definition |
|---|---|---|---|
app_id | INTEGER | NOT NULL | application identifier; primary key |
node_id | INTEGER | NOT NULL | node identifier; foreign key to P_NODES |
app_name | VARCHAR(32) | NOT NULL | PATROL application name |
DB2 P_APPS_TEMP Table
Field Name | Data Type | Null? | Definition |
|---|---|---|---|
node_id | INTEGER | NOT NULL | node identifier; foreign key to P_NODES |
app_name | VARCHAR(32) | NOT NULL | PATROL application name |
DB2 P_INSTANCES Table
Field Name | Data Type | Null? | Definition |
|---|---|---|---|
instance_id | INTEGER | NOT NULL | instance identifier; primary key |
instance_name | VARCHAR(32) | NULL | PATROL instance name |
DB2 P_INSTANCES_TEMP Table
Field Name | Data Type | Null? | Definition |
|---|---|---|---|
instance_name | VARCHAR(32) | NULL | PATROL instance name |
DB2 P_HISTORY Table
Field Name | Data Type | Null? | Definition |
|---|---|---|---|
history_id | INTEGER | NOT NULL | history identifier; primary key |
app_id | INTEGER | NOT NULL | application identifier; foreign key to P_APPS |
instance_id | INTEGER | NOT NULL | instance identifier; foreign key to |
DB2 P_HISTORY_DATA Table
Field Name | Data Type | Null? | Definition |
|---|---|---|---|
history_id | INTEGER | NOT NULL | history identifier; foreign key to |
parameter_id | INTEGER | NOT NULL | parameter identifier |
time_stamp | TIMESTAMP | NOT NULL | date and time occurrence of value |
DB2 P_HISTORY_TEMP Table
Field Name | Data Type | Null? | Definition |
|---|---|---|---|
app_id | INTEGER | NOT NULL | application identifier |
instance_id | INTEGER | NOT NULL | instance identifier |
DB2 P_PARAMETERS Table
Field Name | Data Type | Null? | Definition |
|---|---|---|---|
parameter_id | INTEGER | NOT NULL | parameter identifier; primary key |
DB2 P_PARAMETERS_TEMP Table
Field Name | Data Type | Null? | Definition |
|---|---|---|---|
parameter_name | VARCHAR(32) | NOT NULL | PATROL parameter name |
DB2 P_TMP_HISTORY_DATA Table
Field Name | Data Type | Null? | Definition |
|---|---|---|---|
history_id | INTEGER | NULL | history identifier; foreign key to |
parameter_id | INTEGER | NULL | parameter identifier; foreign key to |
tmp_node_name | VARCHAR(32) | NULL | node name |
tmp_app_name | VARCHAR(32) | NULL | PATROL application name |
tmp_value | VARCHAR(255) | NULL | value of the parameter |
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 tmp_value |
DB2 Triggers
The PATROL HL KM creates the following DB2 triggers:
- P_APPS_TRIG1
- P_APPS_TRIG2
- P_HISTORY_TRIG1
- P_HISTORY_TRIG2
- P_INSTANCES_TRIG1
- P_INSTANCES_TRIG2
- P_NODES_TRIG1
- P_NODES_TRIG2
- P_PARAMETERS_TRIG1
- P_PARAMETERS_TRIG2
DB2 Views
The PATROL HL KM populates the following DB2 view:
DB2 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 | TIMESTAMP | NOT NULL | date and time of occurrence of value |
value | DOUBLE | NULL | value of the PATROL parameter |