How to initialize the Spectrum Report Manager (SRM) database (RpmgrInitializeLandscape.sh )
search cancel

How to initialize the Spectrum Report Manager (SRM) database (RpmgrInitializeLandscape.sh )

book

Article ID: 22484

calendar_today

Updated On:

Products

CA Spectrum DX NetOps

Issue/Introduction

If you receive an alarm 0x10f62 SRM DISK SPACE USAGE [percentage of usage]  alarm, or have noticed SRM disk space nearing capacity from some other monitor or agent, you may need to clear disk space and initialize SRM. 

This technical document will walk you step by step through initializing the Report Manager database for CA Spectrum.

At the end of this article will be some further steps on Optimization and manually truncating data from MySQL. 

Environment

 All supported Spectrum releases

Cause

SRM requires sufficient disk space to write new data to the tables as well as properly run Optimization. If the disk that Report Manager is installed on runs out of disk space and no further disk space can be added to allow an optimization of the database, it may be necessary to initialize the Report Manager database.

Resolution

NOTE: Before proceeding with initializing SRM database, please be aware that the script will optimize the tables, and in order for optimize to be completed properly, MySQL requires 1.5x the amount of the largest table of disk space head room. For example, the Event table is commonly the largest table. If the Event table is 100GB large, then 150GB of additional space is required to run optimization. If there isn't sufficient disk space to run optimization, then the initialization script will fail. See the bottom of this article in reference to "Truncate tables" to see how to clear disk space to get necessary headroom to run the script. Alternatively, you can increase disk space size on the SRM participation temporarily in order to run the script, so long as the added disk space amounts to at least 1.5x the amount of the largest table. 

The data for SRM is stored in <SPECROOT>/mysql/data/reporting. You can review the table files in this directory to get a general idea of the table sizes. IMPORTANT! NEVER delete table files from the data directory! It will have serious negative impact on functionality of MySQL. 

The command to initialize the Report Manager database is RpmgrInitializeLandscape and it can be found in the $SPECROOT/bin directory on the OneClick/Report Manager server. The syntax for the command is as follows:

RpmgrInitializeLandscape <username> <password> [-skipInitialHistory] [-initHist <# of days>] [-all] [<landscape1> <landscape2> ...]

To better explain how this is working here is an example using root as <username> and <password>:

./RpmgrInitializeLandscape.sh root root -initHist 45 -all

 

.


Note: It might be necessary to manually type out the above command. In some instances, there are hidden characters that get picked up and the parameters do not get processed correctly when cut&pasting. 

 

Once initiated this command will do the following:

First the database is initialized with all data removed for specified landscapes (in above example as00901 and as00910). No data is retained in the landscape at all.

·        skipInitialHistory - If used Report Manager does not retrieve or store events during event processing that have occurred before the utility is run. This flag overrides -initHist <# of days> if it is also included in the command line.

·        initHist - After the database is initialized,  SRM then imports events from the Archive Manager database (ddmdb) up to the number of days specified with the "-initHist" parameter. In this example 45 days.

Please Note: By default, 45 days worth of events are stored in the DDM database. This can be modified by editing the MAX_EVENT_DAYS parameter in the $SPECROOT/SS/DDM/.configrc file. Check your .configrc file before running 
RpmgrInitializeLandscape.

·        initHist - If no number of days is specified SRM will clear all data and attempt to import 1 days’ worth of past events from ddmdb. If 0 (zero) days is declared, then SRM will not poll until the next polling cycle (24 hrs). 

·        all – Instead of specifying the SpectroSERVER(s) host name the –all option can be used to include all SpectroSERVER landscapes in this command.
 
Steps to run the Report Manager initialize is as follows:

1. Log into the SRM system as the user that owns the Spectrum installation

2. Stop the Spectrum Tomcat process. This will stop collection of events and prevent writing to the report manager database while it is being initialized.

3. If on Windows, start a bash shell by running "bash -login"

4. cd to the $SPECROOT/bin directory

5. Run the following command to initialize the reporting database with the most recent 45 days (or whatever is configured in your .configrc file) worth of data from the DDM database.

./RpmgrInitializeLandscape root root –initHist 45 –all

From 21.2.4 and newer the command will be

./RpmgrInitializeLandscape.sh root <pass> -initHist 45 -all

 

Note: It might be necessary to manually type out the above command. In some instances, there are hidden characters that get picked up and the parameters do not get processed correctly when cut&pasting. 

 

6. Once it completes please restart the Spectrum Tomcat process. 

The collection process will take some time depending on how much data is in the DDM database. You can monitor the most recent event time collected by logging into the SRM OneClick web page as an ADMIN user and going to the  Administration -> Report Manager -> Spectrum Status page. When the last event record time is within an hour of the current time, then event collection has caught up.

Additional Information

Please reference the "Initialize the Database for Specific Landscapes" section of the documentation for more information.

MySQL OPTIMIZATION

It is recommended to run optimization periodically on MySQL database. When data is written and deleted from tables, there is residual data blocks retained by MySQL (purpose to allow room in the table to re-insert of new data at a later period) but over time, this residual data can add up and cause disk bloat. Optimizing the table periodically helps clean up unused data blocks and reduce disk space usage. 

Commands to optimize SRM MySQL: 

  1. log into OneClick/SRM server as Spectrum Admin
  2. cd $SPECROOT/mysql/bin
  3. run;
    ./mysqlcheck -uroot -proot -o reporting

From 21.2.4 and newer the command will be

./mysqlcheck -uroot -pMySqlR00t -o reporting

 
Note: on Linux OS you must declare the mysql config file location: ./mysqlcheck --defaults-file=../my-spectrum.cnf -uroot -proot -o reporting
 
From 21.2.4 and newer the command will be

./mysqlcheck --defaults-file=../my-spectrum.cnf -uroot -pMySqlR00t -o reporting

Note
: As noted above, it is required to have 1.5x of the largest table disk space available to run optimization, Else the command may hang or fail. 


MySQL TRUNCATE

If SRM disk space is near capacity of the partition, and there is not enough disk space to account for 1.5x the largest table to allow Optimization, and disk space increase request is not an option, then it is likely necessary to TRUNCATE some tables to manually clear out data from MySQL to get the sufficient disk space head room to allow optimization and allow the RpmgrInitializeLandscape script to work properly. 

Note: As mention above, NEVER delete data manually from $SPECROOT/mysql/data to clear disk space. This will break MySQL. Instead, logging into MySQL and using TRUNCATE command is the recommended method to manually clear out table data and clear disk space. 

NOTE: Truncating tables is IRREVERSIBLE unless you already have a backup of the MySQL database. 

To TRUNCATE tables, use the following steps: 
  1. as Spectrum Admin, log into OC/SRM server
  2. cd $SPECROOT/mysql/bin
  3. log into MySQL:  

    Under Windows:
    ./mysql -uroot -proot reporting​

    Under Linux:      
    ./mysql --defaults-file=../my-spectrum.cnf -uroot -proot reporting​


  4. At the MySQL> prompt, run the following command. This example uses the "event" table but any table can be truncated so long as the proper name is used. You can see a list of tables by running "SHOW TABLES;"  command in MySQL (without quotes). You can reference the table sizes in the $SPECROOT/mysql/data/reporting directory.

    mysql> TRUNCATE TABLE event;


Note: commands in MySQL require semi-colon at the end

The above example will clear all data from the Event table. In many cases this is the largest table and clearing this should provide enough disk space headroom to go back and run the RpmgrInitializeLandscape script, which will TRUNCATE and clear out the rest of the tables, and optimize the tables. 




Checking Table Sizes via MySQL Query


cd $SPECROOT/mysql/bin
./mysql --defaults-file=../my-spectrum.cnf -uroot -proot


SELECT
  TABLE_NAME AS `Table`, TABLE_SCHEMA,
  ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024) AS `Size (MB)`
FROM
  information_schema.TABLES
WHERE
  TABLE_SCHEMA = "srmdbapi" or TABLE_SCHEMA = "reporting"
ORDER BY
  (DATA_LENGTH + INDEX_LENGTH)
DESC
LIMIT 10;


Example