Working with stored procedures

Using stored procedures, you can query multiple objects by type. You can do so by day, hour or minute (d,h,m).

Currently supported types are:

• DataPower Message Count Monitors
• WebSphere Message Broker Message Flows
• WebSphere MQ Local Queues

There are three stored procedures per object type, one for each time resolution (day, hour or minute (d,h,m)). 

This topic includes the following:

Stored procedure filenames

The following table lists the procedure name, its type, the report resolution, and the filename.

Stored procedure dependent objects

Each procedure depends upon three global temporary tables.  Two of these tables are for internal work, and the third table contains the result set.
 

For each procedure, there is also a debug table (the table name is suffixed with “_DEBUG”).  This table is only populated when the procedure debug input parameter is set to true.  This table is a permanent table, and must be maintained manually.  To remove rows from it, use standard DML to truncate it or delete rows.

Installation

Use sqlplus to install the desired procedures.  For example:

sqlplus user/password@tns @prc_wmq_lq_tf_h.ora.sql

If you need to replace the stored procedures, run the drop SQL first:

sqlplus user/password@tns @drop.prc_wmq_lq_tf_h.ora.sql

Operation

Each procedure accepts input parameters.  They are the same for each resolution level.

Examples

The examples below use the queries for local queues.  The result is stored in the temporary table QP_WMQ_LQ_TF_D_FINAL_TMP.  Data from this table can be read by a program, copied to a permanent table, or just use sqlplus to produce a report...

Daily

To query a list of queues (by regular expression):

EXECUTE PRC_WMQ_LQ_TF_D('2018-08-04','2018-08-05','(CRAIT|BESPIN)!SYSTEM\.ADMIN\.COMMAND',0,1,1,0);

select inst_path, hddatetime, last_msgenqtotal, last_msgdeqtotal from QP_WMQ_LQ_TF_D_FINAL_TMP;

Parameter description

1. Start date and time.  Must be YYYY-MM-DD.
2. End date and time.  Must be YYYY-MM-DD.
3. Regular expression filtering the list of queues.  In this case, the SYSTEM.ADMIN.COMMAND.QUEUE from queue managers CRAIT or BESPIN.
4. Object limit ( 0 = no limit )
5. Steady state is ON (1)
6. System queues are included (1)
7. Debug is off (0)

Output

2 queues were found matching the regular expression, and the reporting range was 2 days, so 4 rows were returned:

Hourly

To query a list of queues (by regular expression):

EXECUTE PRC_WMQ_LQ_TF_H('2018-08-04 20:00:00','2018-08-04 23:00:00','(CRAIT|BESPIN)!SYSTEM\.ADMIN\.COMMAND',0,1,1,0);

select inst_path, hddatetime, last_msgenqtotal, last_msgdeqtotal from QP_WMQ_LQ_TF_H_FINAL_TMP;

Parameter description:

1. Start date and time.  Must be YYYY-MM-DD HH24:MI:SS.
2. End date and time.  Must be YYYY-MM-DD HH24:MI:SS.
3. Regular expression filtering the list of queues.  In this case, the SYSTEM.ADMIN.COMMAND.QUEUE from queue managers CRAIT or BESPIN.
4. Object limit ( 0 = no limit )
5. Steady state is ON (1)
6. System queues are included (1)
7. Debug is off (0)

Output

2 queues were found matching the regular expression, and the reporting range was 4 hours, so 8 rows were returned:

By Minute

To query a list of queues (by regular expression):

EXECUTE PRC_WMQ_LQ_TF_M('2018-08-04 23:55:00','2018-08-04 23:59:00','(CRAIT|BESPIN)!SYSTEM\.ADMIN\.COMMAND',0,1,1,0);

select inst_path, hddatetime, last_msgenqtotal, last_msgdeqtotal from QP_WMQ_LQ_TF_M_FINAL_TMP;

Parameter description:

1. Start date and time.  Must be YYYY-MM-DD HH24:MI:SS.
2. End date and time.  Must be YYYY-MM-DD HH24:MI:SS.
3. Regular expression filtering the list of queues.  In this case, the SYSTEM.ADMIN.COMMAND.QUEUE from queue managers CRAIT or BESPIN.
4. Object limit ( 0 = no limit )
5. Steady state is ON (1)
6. System queues are included (1)
7. Debug is off (0)

Output

2 queues were found matching the regular expression, and the reporting range was 5 minutes, so 10 rows were returned: