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

  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:

Was this page helpful? Yes No Submitting... Thank you

Comments