Sample runtime metrics collection procedures and SQL dashboard

IBM provides procedures for you to use on the runtime metrics collection data in the MariaDB database and also sample Structured Query Language (SQL) dashboards to filter and view the results.

Sample Metabase dashboard

A sample Metabase dashboard demonstrates how you can filter and view the runtime metrics collection data in the MariaDB database.

The following tables list the filters and cards in the sample Metabase dashboard.

Table 1. Filters that are implemented in the sample Metabase dashboard
Filter Description
Group ID Specify the identifier of a collection group in the group_table table.

For more information, see group_table.
Collection ID Specify the identifier of a collection in the collection_table table.

For more information, see collection_table.
Baseline Collection ID Specify the identifier of a collection to be used as the baseline collection. This can be a collection ID from the collection_table table or the baseline collection_table table.

For more information, see collection_table.
NVP Breakdown Name Specify the name-value pair name that is used to break down the results. The available name-value pair names are identified in the Horizontal Names Table and Vertical Names Table cards.

For more information about the cards, see Table 2.
Find Value of Specify the name-value pair name to see all of the name-value pair values that are used in the collection for that name-value pair name. The available name-value pair names are identified in the Horizontal Names Table and Vertical Names Table cards. The results are shown in the Values Table card.

For more information the cards, see Table 2.
Horizontal NVP Specify the horizontal name-value pair name and value to be used to filter the results.

The available name-value pair names are identified in the Horizontal Names Table card. Find the available values for a name-value pair name in the Values Table card by entering a name-value pair name in the find value of filter.

You can specify multiple name-value pairs. Each name-value pair must be separated by a comma, for example, msgType=Booking, origin=mobile.

For more information about the cards, see Table 2.
Vertical NVP Specify the vertical name-value pair name and value to be used to filter the results.

The available name-value pair names are identified in the Vertical Names Table card. Find the available values for a name-value pair name in the Values Table card by entering a name-value pair name in the find value of filter.

You can specify multiple name-value pairs. Each name-value pair must be separated by a comma, for example, IOwnerHi=Booking, IPBI=BSS.

For more information about the cards, see Table 2.
Metrics Specify the names of the metrics in the runtime metrics collection you want to see deltas and trends for. Each name of the metric must be separated by a comma, for example, SWBK, CMBK.

For more information about the metrics, see Runtime metrics collection database metrics.
Table 2. Cards that are implemented in the sample Metabase dashboard
Card Description
Group Table Shows all of the collection groups.

For more information, see group_table.
Collection Table Shows all of the collections for the specified collection group.

For more information, see collection_table.
Baseline Collection Table Shows all of the collections in the data_for_group_table_baseline table.

For more information, see data_for_group_x.
Horizontal Names Table Shows all of the horizontal name-value pair names for a collection group.
Vertical Names Table Shows all of the vertical name-value pair names for a collection group.
Values Table Shows all of the values for a specified name-value pair name in the find value of filter.
Metrics Table Shows all the available metrics for the specified collection group.
Percent of Filtered Traffic1 on a Collection Shows the percentage of the filtered traffic for each name-value pair value for the name-value pair name that is specified in the NVP breakdown name filter.

Use the horizontal name-value pair filter and vertical name-value pair filter to narrow the scope of the traffic.
Percent of Filtered Traffic Deltas, Collection vs Baseline Shows the following information:
  • The percentage of the filtered traffic for each name-value pair value for the name-value pair name that is specified in the NVP breakdown name filter for the collection that is specified in the collection ID filter.
  • The percentage of the filtered traffic for each name-value pair value for the name-value pair name that is specified in the NVP breakdown name filter for the baseline collection that is specified in the baseline collection ID filter.
  • The delta between the previous two values.

Use the horizontal name-value pair filter and vertical name-value pair filter to narrow the scope of the traffic.

Call the mbGetPercentOfFilteredTrafficByCollectionAndBaseline procedure to implement this card. For more information, see the mbGetPercentOfFilteredTrafficByCollectionAndBaseline procedure in Table 3.
Filtered Traffic Breakdown History Shows the history of the percentage of the filtered traffic for each name-value pair value for the name-value pair name that is specified in the NVP breakdown name filter for the collection that is specified in the collection ID filter.

Use the horizontal name-value pair filter and vertical name-value pair filter to narrow the scope of the traffic.
Percent of Filtered Traffic by Collection (Message Count) Shows the message count of the filtered traffic for each name-value pair value for the name-value pair name that is specified in the NVP breakdown name filter.

Use the horizontal name-value pair filter and vertical name-value pair filter to narrow the scope of the traffic.
Filtered Metrics Delta Shows the following information:
  • The metrics that are specified by the metrics filter for the filtered traffic for the collection that is specified in the collection ID filter.
  • The metrics that are specified by the metrics filter for the filtered traffic for the baseline collection that is specified in the baseline collection ID filter.
  • The delta between the previous two values.

Call the mbGetKeyMetrics procedure to implement this card. For more information, see mbGetKeyMetrics in Table 3.
Filtered Metrics History

Shows the history for each metric that is specified in the metrics filter for the filtered traffic for the collection group that is specified in the collection group ID filter.

Call the mbGetMetricsAverages procedure to implement this card. For more information, see mbGetMetricsAverages in Table 3.
Note:
  1. Filtered traffic is the collection results that are filtered by the horizontal NVP filter and the vertical NVP filter.

Sample stored procedures in the MariaDB database

The Metabase cards are implemented by calling stored procedures in the MariaDB database. You can use the sample stored procedures as guidelines for how to interact with the runtime metrics collection data in the MariaDB database. The sample procedures provide the following guidelines:
  • How to interact with the horizontal and vertical data. Different methods are used to access the horizontal and vertical data.
  • How to translate name-value pair names and values by using the relationship_id field to determine the dimension_id field to access the data that you want.

    For more information about the relationship_id field, see relationship_table. For more information about the dimension_id field, see dimension_table.

Load the sample procedures to the MariaDB database by running the tpf_data_sci/Docker/tpf_mariadb_docker_files/tpf_nvpc_stored_procedures.sql file in the z/TPF real-time insights dashboard starter kit. Consider using the tpf_setup_mariadb.sh script that is discussed in the real-time insights dashboard starter kit readme file. You can access the real-time insights dashboard starter kit readme file on the z/TPF real-time insights dashboard starter kit download page.

Table 3. Sample procedures that are provided for use by an SQL dashboard tool.
Sample procedure Description
mbGetBaseCollectionList Provides a list of collections in the data_for_group_table_baseline table.

For more information about the data_for_group_table_baseline table, see data_for_group_x.
mbGetCollectionHorizontalNames Provides a list of the horizontal name-value pair names that are used in the specified collection group.
mbGetCollectionList Provides a list of collections in the specified group.

For more information, see collection_table.
mbGetCollectionVerticalNames Provides a list of the vertical name-value pair names that are used in the specified collection group.
mbGetGroupTable Provides a list of the collection groups.
mbGetKeyMetrics Calculates the delta between the specified metrics average for the specified collection and baseline collection.

Call this procedure to implement the filtered metrics delta card. For more information, see filtered metrics delta in Table 2.
mbGetMetricsAverages Finds the average values for the specified metrics across all collections in a collection group.

Call this procedure to implement the filtered metrics history card. For more information, see filtered metrics history in Table 2.

For an example that uses this procedure to calculate the average values for the specified metrics for each collection in a collection group, see An example of a runtime metrics collection procedure.
mbGetMetricsList Provides a list of all the metrics in a collection group.

For more information, see data_for_group_x.
mbGetPercentOfFilteredTrafficByCollection Finds the name-value pair value, message count, and percentage for the specified name-value pair name.
mbGetPercentOfFilteredTrafficByCollectionAndBaseline Calculates the delta between the percentage for each name-value pair value for the specified NVP breakdown name for the specified collection and baseline collection.

Call this procedure to implement the percent of filtered traffic deltas, collection vs baseline card. For more information, see percent of filtered traffic deltas, collection vs baseline in Table 2.
mbGetPercentOfFilteredTrafficByGroup Finds the name-value pair value and percentage for the specified NVP breakdown name across all collections in a collection group.
mbGetValues Provides a list of all the name-value pair values for a specified name-value pair name.

Sample runtime metrics collection procedures to manage collections

You can use the sample runtime metrics collection procedures to manage the runtime metrics collection data in the MariaDB database. The provided procedures are listed in Table 4.

Load the sample procedures to the MariaDB database by running the tpf_data_sci/Docker/tpf_mariadb_docker_files/tpf_nvpc_stored_procedures.sql file. For more information about each stored procedure, see the prolog of each procedure.

Table 4. Procedures to use on the runtime metrics collection data
Procedure Description
createBaselineCollection Creates a baseline collection from collections in the collection group. This procedure demonstrates how you can create simple average values between multiple collections.
removeCollection Removes all tables for a collection and all references to a collection from the database.