Using the BW Queue Job Configuration and Status Program

E2E Monitoring and Alerting Infrastructure (MAI) metrics and events can be written to the Business Warehouse (BW) and aggregated there, for long-term storage. There are various applications available there, such as Interactive Reporting and various Dashboards.

From SAP Solution Manager 7.1 SP12, there is a new way of transferring this data to BW, the job SMREP_PROCESS_BW_DATA_QUEUE (Data Queue Processor, or just Processor) writes metric and event values from MAI to the Business Warehouse, depending on how it is configured. The processor runs typically every 3-5 minutes, depending on the technical environment. By default, this processor only processes the MAI events, not the metrics.

In the previous technical realization, a lot of processes had change access to the database, in parallel, which sometimes caused performance problems or gaps in BW data, in large technical system landscapes. These problems were sometimes also noticeable in a lot of dialog work processes of the user SM_EFWK, SM_EXT_WS or the BW background user. By contrast, the data queue processor serializes this procedure.

TheConfiguration and Status of BW Data Queue program can check the status and performance of the processor, and make configuration settings. Use it if there are data gaps in Interactive reporting or other applications which display the history data in BW.

Activities

Call Program

To see the BW data queue configuration and status display program, call the transaction MAI_TOOLS. Go to Expert Mode, and in the Analysis group box, choose BW Data Queue Configuration and Status.

Technical Background

The MAI data queue comprises two analogous tables SMREP_MAI_QUEUE1 (table 1) and SMREP_MAI_QUEUE2 (table 2). MAI writes the data which is to be written to BW, into one of the two tables alternately, while the processor writes data records from the other table into BW. The flow of this cycle is as follows:

MAI writes current values into table 1.
The processor writes data from table 2 into BW.
When all the data from table 2 has been written into BW, table 2 is deleted.
MAI then writes current values into table 2.
The processor writes data from table 1 into BW.
When all the data from table 1 has been written into BW, table 1 is deleted.
MAI then writes current values into table 1, and the cycle restarts from the beginning.

This concept has two main advantages:

MAI and the processor never use the same table, which improves performance.
There is no resource-intensive selective deletion of obsolete data records. Deleting all the data in a table with DROP TABLE is usually much quicker than selective deletion.

The BW Data Queue Configuration and Status programm displays the following values about this, under Type and Parameters:

Type	Parameters	Description
`SMREP_CFG_STATIC`	`QUEUE_TYPE`	The technical realization of the data queue. This is currently as a database table (`DB`).
`SMREP_MAI_QUEUE1` `SMREP_MAI_QUEUE2`	Data records in queue 1 Data records in queue 2	How many data records the two data queues currently contain. Note Double-click on a queue to display the contents of its data queue table. SAP Support uses this function to check the data written to BW. End of the note.
`SMREP_QUEUE_CONF`	`CUR_TARGET`	Into which queue MAI is currently writing.
`SMREP_QUEUE_CONF`	`LAST_REC`	Timestamp of the last data record written from the data queue to BW.

Note Note

Do not change these parameters. They are only for information, and are mostly used by SAP Support.

The parameter PAUSE is also reserved for use by SAP Support. If it has the value X, queue processing stops. In this case, the parameter PAUSE_END_COUNTDOWN is automatically set to 100, and incremented by -1 with every job run. If the value reaches 0, the pause ends. If the parameter PAUSE is set, the jobs end with an error message.

End of the note.

Analyze job for errors

If an error occurred in the queue job, today or yesterday, it is output in type JOBFAIL. To analyze the error, proceed as follows:

Double-click on the row of type JOBFAIL. Job selection (transaction SM37) is called with SMREP_PROCESS_BW_DATA_QUEUE as default Job Name.
Confirm the default with (Execute).
Select the job run in which an error occurred, and choose Job Log. The job log of the selected run is displayed.
The most common reason for job cancellation is a run-time error. Search the job log for this error, and if you find it, double-click on it.
Check the cause of the error. If you cannot find the cause, report an incident in the application component shown in the header of the run-time error.

Check job performance

There is a tool under the type JOBLOG to check the performance of the queue job. The following values are output for job runs today and yesterday:

Number of job runs. This depends on the technical environment and your parameter settings, but the system should run the job at least 100 times a day, with default settings.
Aggregate run time of all job runs on one day
The percentage of the time of day in which the data queue processor runs. This value should be less than 80%, otherwise reduce the value of the parameter JOB_DELAY (see Improving Job Performance).

For further information about the performance and load of the processor, double-click on a row of type JOBLOG. In the Analyzing the BW Data Queue Processor Job Log screen, you can specify the time interval and granularity in which to display the information.

Choose Execute (Execute). The most important performance indicators of the processor are output in the specified granularity:

Processor run-time
Time to delete queue tables
Number of MAI data packages read, and the time required to do so (such a package contains values for all metrics and events of a managed object collected by MAI at a point in time)
Number of data records read, and the time taken to do so
Time to enhance and map the data records
Number of BW calls, and the time taken to make them
Number of job runs

Note Note

Information at this level of detail is above all useful to SAP Support for the analysis of performance problems.

End of the note.

Improving job performance

If the data queue processor performance is inadequate (if the load is greater than 80%), you have the following alternatives (in descending order of importance):

Reduce the number of metrics and events saved in BW.
In Support Package 12 of SAP Solution Manager 7.1, various MAI content settings have been optimized so that only those MAI values are saved in BW which are needed by its applications. To ensure that you have this content, start the SAP Solution Manager Configuration (transaction SOLMAN_SETUP) and go to Technical Monitoring System Monitoring Configure Infrastructure Update Content , and ensure that you have the newest content.

Note
If the load incurred by saving the data in BW is still too high, proceed as described in SAP Note 1999511.

End of the note.
Reduce the value of the parameter JOB_DELAY.
The value of this parameter is the minimum time, in seconds, between two runs of the processor. This minimum time prevents the processor running when there is only a small amount of data to be processed, and the administrative overhead would be disproportionate to the data processed. If the load is very high, such a waiting time is unimportant, or perhaps even harmful.

The current value of parameter JOB_DELAY is displayed. To reduce its value, proceed as follows:
1. Double-click on a row of type SMREP_CFG_STATIC (the value of the parameter JOB_DELAY is specified in this table).
2. Confirm the default values with (Table Contents), and in the selection screen, choose (Execute).
3. Create a new row with (Create).
4. In the PARAM NAME field, enter the parameter name JOB_DELAY, and in the PARAM VALUE, enter a smaller value than the current one.
  
  Note
  If there is already a row for the parameter JOB_DELAY, change its PARAM VALUE value.
  
  End of the note.
5. Save your changes.
Change the queue table deletion method.
The queue tables are (as explained under Technical Background) deleted entirely with the DROP TABLE command. This is normally more effective than selectively deleting entries. Depending on the DB type and data quantity, it can be quicker to delete entries (SQL DELETE). An indication that this may be the case would be, for example, if the time taken to delete the queue tables is a large proportion of the total processor time (see Check Job Performance).

Note
Selective deletion of entries can create Re-Do log files, which can be large, depending on the amount of data to be saved in BW, and the database type.

End of the note.

To delete entries from the queue tables, proceed as follows:
1. Double-click on a row of type SMREP_CFG_STATIC (the value of the parameter NO_TABDROP is specified in this table).
2. Confirm the default values with (Table Contents), and in the selection screen, choose (Execute).
3. Create a new row with (Create).
4. In the PARAM NAME field, enter the parameter name NO_TABDROP, and in the PARAM VALUE field, the value X.
5. Save your changes.
Change the number of data records whose calculation is passed consecutively by the data queue processor to BW, for saving.
You can configure the processor package size, the number of data records which are read, calculated and written to BW, consecutively. The default package size is 500. The optimum size for the run depends on a lot of technical factors in your system landscape, so it can be a good idea to slightly vary the PACKSIZE parameter. Change the parameter value, analogously to changing the NO_TABDROP parameter, and check whether the load has reduced, as described under Check Job Performance.

If the performance is still unsatisfactory after you have taken all these measures, the performance of your database is inadequate.

Processing metrics with the data queue processor

By default, the data queue processor only writes events to the BW. You can also process metrics in this way, by setting the configuration parameter METR_QUEUE to X. If you do so, observe the performance of the processor closely, and optimize it. If the performance is unsatisfactory after such a change, or the load is permanently more than 80%, change it back.

To make this change, proceed as follows:

Double-click on a row of type SMREP_CFG_STATIC (the value of the parameter METR_QUEUE is specified in this table).
Confirm the default values with (Table Contents), and in the selection screen, choose (Execute).
Create a new row with (Create).
In the PARAM NAME field, enter the parameter name METR_QUEUE, and in the PARAM VALUE field, the value X.
Save your changes.