Some of the content described in this article is available in meshIQ Manage versions 12.1 and greater.
For a quick overview of what's new or changed, visit the meshIQ Highlights page for a version-by-version breakdown.  

Jump to Section:
Version 12.x and Later
Version 11.3 and Earlier

Version 12.x and Later

The data displayed is controlled by the schema selected from the Default schema drop-down located at the top right of the MQ Statistics in Console tab. This is the schema that was selected during your previous session and will remain in effect until you select a different schema. 

To change your view, select a different schema from the Default schema dropdown. You can also create a new schema.

The management of statistics schemas is governed by the Manage Global Display Schemas and Manage Private Display Schemas rights. See Navigator GUI Feature Rights for details.

Create a New Schema

1. Select the object and choose MQ Statistics... from the Selected menu. A new tab opens in the Console panel.
   
2. Choose the method you want to use to create a new schema.  

• To edit the current one, click Edit current schema .

You cannot edit the Default Queue Status Query schema.

• Or, to create a completely new schema, click Manage MQ statistics schemas , then click Add.

3. In the Manage Statistics Schemas dialog, click Add. The Add Statistics Schema dialog opens.
   
4. Enter the required details for the new schema, such as the Statistics schema name and query fields (SELECT, FROM, WHERE, etc.).

When creating a new schema, it is recommended to make a copy of an existing schema and use that as a starting point (instead of overwriting an existing schema).

After you make your selection, the Manage Statistics Schemas dialog displays the existing schemas on the left side. Selecting a schema displays its query on the right side of the dialog.

The queries may vary slightly depending on the database used.

Schema Field Descriptions

SELECT statement
Specifies the attributes displayed in the widget. The Default Queue Status Query schema selects all attributes (*).
You can specify individual attributes by listing them and separating them with commas.

You can copy and paste attribute names from widget column headers directly into the SELECT field.

You can also customize column header names using:

attribute_name AS <ColumnTitle>

Numeric attributes can be combined using +. For example, multiple put count fields can be added together and displayed in a single column such as TOTALPUT.

Adding Fields and Changing Display Names

Using Views

FROM statement
Defines the table name.

WHERE statement
Contains query conditions. It can include defined or dynamic parameters. Dynamic parameters are automatically populated based on the selected object.

• Possible dynamic parameters include:

• {WGS_NAME} – Workgroup server name

• {NODE_NAME} – Node name

• {QMGR_NAME} – Queue manager name

• {OBJ_NAME} – Object name

• {INTERVAL_START} – Interval start time (Unix timestamp)

• {INTERVAL_END} – Interval end time (Unix timestamp)

GROUP BY or LIMIT statement
Defines sorting and/or limits the number of rows returned. For example:

GROUP BY MQNODE_NAME ASC LIMIT 1000

The syntax may vary depending on the database vendor.

Chart by field
Specifies a data field used to display the results as a graph. For more information, see Viewing a Statistics Graph.

Getting Help (If You Have No SQL Experience)

The Manage Statistics Schemas dialog is an advanced feature that requires knowledge of SQL. If you are not familiar with SQL, ask your administrator for assistance. Your administrator can provide queries that you can copy and paste into the Manage Statistics Schemas dialog.

An easier alternative is to use Views. Views are database tables created and saved by your administrator. Multiple views can be created. When using a view, specify the view name in the FROM statement, as shown below. The WHERE statement remains the same.

Example of MySQL Query

In this example, two queues from different nodes are selected:

• MQM/NODE1/T1/LQ1

• MQM/NODE2/T2/LQ2

The local time specified in the statistics widget is converted to a Unix timestamp:

• Start time: 2019-08-21 10:00 AM → 1566370800

• End time: 2019-08-21 11:15 PM → 1566418500

The following schema was used for the queue.

Actual query:

SELECT * FROM statqueue WHERE
      (
            MANAGER_NAME = 'MQM' AND MQNODE_NAME = 'NODE1' AND MQMGR_NAME = 'T1' AND QUEUE_NAME = 'LQ1' AND STAT_TIME_STAMP >= 1566370800 AND STAT_TIME_STAMP <= 1566418500
      )
      OR
      (
            MANAGER_NAME = 'MQM' AND MQNODE_NAME = 'NODE2' AND MQMGR_NAME = 'T2' AND QUEUE_NAME = 'LQ2' AND STAT_TIME_STAMP >= 1566370800 AND STAT_TIME_STAMP <= 1566418500
      )
      LIMIT 1000

Queries are executed using the nastel_apwmq database.

Schemas are flexible and support various types of queries, including queries unrelated to MQ statistics. The syntax depends on the SQL database being used. For example, queries written for MySQL may differ from those used in PostgreSQL.

Viewing a Statistics Graph

Statistics widgets can be displayed as a graph by specifying a data field in the Chart by field option.

For example, to view the message backout count (messages withdrawn from a queue due to transaction issues), enter the field name BACKOUT_COUNT in the Chart by field field.

After you specify the field, the Console tab displays the results as a graph showing the number of backed-out messages during the selected time period.

________________________________________________________________________________________________________________

Version 11.3 and Earlier

The data displayed is controlled by the schema selected from the Default schema drop-down located at the top right of the viewlet.  This is the schema that was selected during your previous session and will remain in effect until you select a different schema. 

To change your view, select a different schema from the Default schema dropdown.  You can also create a new schema.

The management of statistics schemas is governed by the Manage Global Display Schemas and Manage Private Display Schemas rights. See Navigator GUI Feature Rights for details.

Create a new schema

1. Choose the method you want to use to create a new schema.  

• Edit the current one .
  You cannot edit the Default Queue Status Query schema.
• or create a completely new schema . 

When creating a new schema, it is recommended to make a copy of an existing schema and use that as a starting point (instead of overwriting an existing schema).

2. After you make your selection, the Manage Statistics Schemas window opens.  This is where you specify the query to determine the attributes displayed. The left side of the window displays the existing schemas. 
   
3. Select a schema to view its query on the right side of the window.  The queries will have slight differences depending on the database you utilize. 

See below for an explanation of the schema fields.

• SELECT statement: The attributes to display in the viewlet are specified within the SELECT statement.  The Default Queue Status Query schema selects all attributes.  You can change this by entering all desired attributes and separating them with a comma. 

You can copy and paste attribute names from viewlet column headers right into the Select field of the query.

The column header names within the viewlet can be customized by using “as <ColumnTitle>” to change the column header names.  Attributes containing numeric values can also be added together using +.  As seen in the below examples, the put count fields were added together and display in one column titled “TOTALPUT.”

Adding Fields and Changing Display Names

Using Views

• FROM statement:  Defines the table name.
• WHERE statement:  contains query conditions.  It can have defined or dynamic parameters.  Dynamic parameter values are filled automatically according to the selected object.  Possible dynamic parameters:

{WGS_NAME} – workgroup server name

{NODE_NAME} – node name

{QMGR_NAME} – queue manager name

{OBJ_NAME} – object name

{INTERVAL_START} – interval start time Unix timestamp

{INTERVAL_END} – interval end time Unix timestamp

• GROUP BY or LIMIT statement:  defines the sorting and/or the limiting of the column values (limit of record rows), e.g., Group by MQNODE_NAME asc LIMIT 1000 (the definition depends on database vendor).
• Chart by field: specify a data field to display the results as a graph. See Viewing a Statistics Graph.

Getting Help (If you have no SQL experience)

As you can see, the Manage Statistics Schemas window is very advanced and requires knowledge of SQL.  Ask your administrator for assistance.  They can send you queries you can copy and paste into the Manage Statistics Schemas window.

An even easier method is to utilize Views.  Views are queried tables saved in the database created by your administrator.  Multiple views can be created.  The View name will need to be specified within the FROM statement, as seen below.  The WHERE statement remains the same.

Example of MySQL Query

Two queues from different nodes (MQM/NODE1/T1/LQ1) and (MQM/NODE2/T2/LQ2) were selected.  The local time specified in the statistics viewlet gets converted to a Unix timestamp:

start time (2019-08-21 10:00 AM) 1566370800,

end time   (2019-08-21 11:15 PM) 1566418500

The following schema was used for the queue:

Actual query:

SELECT * FROM statqueue WHERE
      (
            MANAGER_NAME = 'MQM' AND MQNODE_NAME = 'NODE1' AND MQMGR_NAME = 'T1' AND QUEUE_NAME = 'LQ1' AND STAT_TIME_STAMP >= 1566370800 AND STAT_TIME_STAMP <= 1566418500
      )
      OR
      (
            MANAGER_NAME = 'MQM' AND MQNODE_NAME = 'NODE2' AND MQMGR_NAME = 'T2' AND QUEUE_NAME = 'LQ2' AND STAT_TIME_STAMP >= 1566370800 AND STAT_TIME_STAMP <= 1566418500
      )
      LIMIT 1000

Queries are run using the 'nastel_apwmq' database.

The schemas are very flexible and different queries can be run. This includes queries not related to MQ statistics.  The syntax is dependent on your SQL database (i.e., queries for the MySQL database can be different from the Postgre database).

Viewing a Statistics Graph

Statistics viewlets can be displayed as a graph by specifying the data field within the Chart by field.  For example, to view message backout count (messages withdrawn from a queue due to transaction problems), enter the data field name, BACKOUT_COUNT within Chart by.

Your viewlet will now display as a graph, displaying the dynamics of the backed out messages during the specified time period.