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.
Procedure Name | Type | Resolution | Filename |
PRC_DP_MCM_TTL_D | DataPower Message Count Monitor | day | prc_dp_mcm_ttl_d.(ora|db2).sql |
PRC_DP_MCM_TTL_H | DataPower Message Count Monitor | hour | prc_dp_mcm_ttl_h.(ora|db2).sql |
PRC_DP_MCM_TTL_M | DataPower Message Count Monitor | minute | prc_dp_mcm_ttl_m.(ora|db2).sql |
PRC_IIB_FLOW_TF_D | WebSphere Message Broker Message Flow | day | prc_iib_flow_tf_d.(ora|db2).sql |
PRC_IIB_FLOW_TF_H | WebSphere Message Broker Message Flow | hour | prc_iib_flow_tf_d.(ora|db2).sql |
PRC_IIB_FLOW_TF_M | WebSphere Message Broker Message Flow | minute | prc_iib_flow_tf_d.(ora|db2).sql |
PRC_WMQ_LQ_STAT_D | WebSphere MQ Local Queues | day | prc_wmq_lq_stat_d.(ora|db2).sql |
PRC_WMQ_LQ_STAT_H | WebSphere MQ Local Queues | hour | prc_wmq_lq_stat_d.(ora|db2).sql |
PRC_WMQ_LQ_STAT_M | WebSphere MQ Local Queues | minute | prc_wmq_lq_stat_d.(ora|db2).sql |
PRC_WMQ_LQ_TF_D | WebSphere MQ Local Queues | day | prc_wmq_lq_tf_d.(ora|db2).sql |
PRC_WMQ_LQ_TF_H | WebSphere MQ Local Queues | hour | prc_wmq_lq_tf_d.(ora|db2).sql |
PRC_WMQ_LQ_TF_M | WebSphere MQ Local Queues | minute | prc_wmq_lq_tf_d.(ora|db2).sql |
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.
SUFFIX | Purpose | On Commit | Comment |
_CSR_TMP | Used to store the list of objects to report on. | Preserve rows | Populated via a query to qp_obj_info. Once this table is populated, there is no cursor on any Middleware Monitor table. |
_RAW_TMP | Stores raw history data for processing. | Delete rows | Populated by a query of the qp_hdhr or qp_hdlr table. Once this table is populated, there is no cursor on any Middleware Monitor table. |
_FINAL_TMP | Result set. | Preserve rows | This is the table you query for results. |
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.
Name | Type | Description | Comment |
Start Date | Character | Start date | Must be in the form YYYY-MM-DD HH24:MI:SS |
End Date | Character | End date | Must be in the form YYYY-MM-DD HH24:MI:SS |
Input Mask | Character | Regular expression | Up to 500 characters |
Object Limit | Integer | Limit the number of objects processed. | Default is 80,000. |
Steady State? | Integer (0|1) | Persist values of no changes? | If you want a report that shows ONLY CHANGES, turn steady state off. Not recommended as it can make reports difficult to interpret. For queues only. |
System Objects? | Integer (0|1) | Process system objects? | For queues only. |
Debug? | Integer (0|1) | Whether debug info will be published | Enter 1 or 0. 1 is true. Debug causes a delay, expect the proc to be at least 10% slower. |
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
- Start date and time. Must be YYYY-MM-DD.
- End date and time. Must be YYYY-MM-DD.
- Regular expression filtering the list of queues. In this case, the SYSTEM.ADMIN.COMMAND.QUEUE from queue managers CRAIT or BESPIN.
- Object limit ( 0 = no limit )
- Steady state is ON (1)
- System queues are included (1)
- 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:
- Start date and time. Must be YYYY-MM-DD HH24:MI:SS.
- End date and time. Must be YYYY-MM-DD HH24:MI:SS.
- Regular expression filtering the list of queues. In this case, the SYSTEM.ADMIN.COMMAND.QUEUE from queue managers CRAIT or BESPIN.
- Object limit ( 0 = no limit )
- Steady state is ON (1)
- System queues are included (1)
- 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:
- Start date and time. Must be YYYY-MM-DD HH24:MI:SS.
- End date and time. Must be YYYY-MM-DD HH24:MI:SS.
- Regular expression filtering the list of queues. In this case, the SYSTEM.ADMIN.COMMAND.QUEUE from queue managers CRAIT or BESPIN.
- Object limit ( 0 = no limit )
- Steady state is ON (1)
- System queues are included (1)
- 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: