Feedback
Table Of Contents
Managing Databases and the Comma Separated Value Repository
Common Database Management Tasks
Managing the CM:RDR Periodic Deletion of Old Records
Applying the Periodic Delete Configuration File
UNICODE Support for Sybase Database
Managing the Bundled Sybase Database
Updating Sybase with a Changed IP Address
Updating Sybase Database Statistics
Managing the External MySQL Database
Partitioning the MySQL Database
Oracle Support with Minimum Privileges
Enabling Aggregation in Sybase
Enabling Aggregation in Oracle
Configuring the CSV File Repository
Configuring Escape of Nonprintable Characters
Managing Databases and the Comma Separated Value Repository
Introduction
This chapter explains how to use utility scripts to manage the Collection Manager (CM) database and the comma separated value (CSV) repository.
Note
For instructions on using utility scripts, see the "Using Utility Scripts" section.
•
•
•
•
Common Database Management Tasks
The following database management tasks apply to all the supported databases:
•
•
•
•
The Cisco Service Control Management Suite (SCMS) Collection Manager adds a timestamp to every raw data record (RDR) that it receives and stores in the database. This timestamp is used when various maintenance operations are performed on the database tables.
Use the following scripts to configure and maintain the database:
•
•
•
•
•
•
•
•
•
Listing Database Tables
To display a list of all of the tables in the database, run the dbtables.sh script:
~scmscm/scripts/dbtables.sh --rdrWhere applicable, the script displays the number of lines in the table and the earliest and latest timestamps.
Use the Cisco Service Control Application (SCA) Reporter to display the actual content of the tables. For more information, see Cisco Service Control Application Reporter User Guide.
Table 5-1 lists the dbtables.sh script options.
The dbtables.sh script generates the following sample output:
>~scmscm/scripts/dbtables.sh --rdr -c Executing query ...name| num_lines| min_time| max_time|----------------+--------+------------------------+------------------------+RPT_SUR| 131000| 2006-10-30 16:46:42.24| 2007-02-15 12:00:32.216|RPT_LUR| 170000| 2007-04-10 15:25:45.31| 2007-04-11 07:06:05.45|RPT_VLUR| 4694| 2007-04-11 13:12:39.683| 2007-04-11 13:18:07.396|RPT_PUR| 116000| 2007-04-09 04:45:55.566| 2007-04-11 07:44:09.186|RPT_TR| 57766| 2007-04-11 13:12:39.683| 2007-04-11 13:18:07.396|RPT_MALUR| 109000| 2007-04-09 04:46:35.903| 2007-04-09 13:32:18.42|RPT_MEDIA| 120000| 2007-04-05 17:14:24.443| 2007-04-11 13:16:29.436|RPT_TOPS_PERIOD0| 194250| 2007-03-18 20:00:00.01| 2007-04-23 06:00:00.16|RPT_TOPS_PERIOD1| 46940| 2007-03-19 00:00:00.05| 2007-04-23 00:00:00.1|Managing the CM:RDR Periodic Deletion of Old Records
To manage the periodic deletion of old records, perform the following steps:
1.
Log in as the scmscm user, start the Collection Manager, wait 1 - 2 minutes for the creation of the database tables, and then run the script:
~scmscm/db_maint/create_periodic_del_procs.sh. --rdr2.
3.
Caution
Periodic deletion of a table does not begin while a previous periodic deletion is still running. This prevents excessive load on the database, which would degrade insertion performance in the adapters.
When two or more tables are scheduled to be reduced at the same time, the tables are processed in the order in which they are listed in the periodic delete configuration file.
For ease of configuration, you can schedule periodic deletion for all tables consecutively on one schedule.
All periodic delete activity is recorded in the system log file. For Solaris, the system log file is located in the /var/adm/messages directory; for Linux, the system log file is located in the /var/log/messages directory. The following is an example of the system log file at the time when the periodic delete action occurs.
Feb 17 05:00:07 app-redhat79 logger: /opt/scmscm/db_maint/periodic_del.sh for hourly all - running single del for RPT_LUR to 14 daysFeb 17 05:00:09 app-redhat79 logger: Starting periodic delete for RPT_LUR keeping 14 daysFeb 17 05:00:09 app-redhat79 logger: Deleted rows: 0 from the table RPT_LUR
Note
Caution
•
Configuring Periodic Delete
By default, the periodic delete configuration file (dbperiodic.conf) is located in the directory ~scmscm/db_maint/. The file has a structure similar to an INI file, where each section describes a particular data reduction operation for a specific set of tables, to be performed according to a specified schedule.
Note
Each section begins with the section name in brackets, and contains the parameters shown in the following table. (Not all parameters are required in each section of the configuration file.) Separate the parameters and their values by an equal sign (=). Table 5-2 contains examples of periodic delete configuration files.
Note
In the following example, all fields are set to their default values.
# This dbperiodic.conf file emulates the legacy style for periodic # deletion. All tables are processed every hour on the hour, and # records are kept for 14 days. [hourly all] active = true tablenames = * daystokeep = 14 minute = 0 hour = *In this example, all tables are reduced at 4:30 A.M., leaving 10 days of data in each table. In addition, the real-time tables are reduced every hour, leaving three days of data in each table.
# This dbperiodic.conf file reduces all tables once a day and # real-time tables once an hour. [daily all] active = true tablenames = * daystokeep = 10 minute = 30 hour = 4 [hourly real-time] active = true tablenames = RPT_SUR,RPT_LUR,RPT_PUR daystokeep = 3 minute = 0 hour = *Applying the Periodic Delete Configuration File
To load and apply a new periodic delete configuration file or to view the current file, run the dbperiodic.sh script:
~scmscm/scripts/dbperiodic.sh --rdr [ --dump ] [ --load | --loadfile=path_to_dbperiodic.conf ]When the script is used to load a new configuration file, it parses the file, verifies its validity, and updates the scmscm user's crontab to reflect the changes. Table 5-3 lists the dbperiodic.sh options.
The following example shows how to print the current periodic delete configuration:
~scmscm/scripts/dbperiodic.sh --dump
Note
The following example shows how to load the periodic delete configuration file from ~scmscm/db_maint/dbperiodic.conf:
~scmscm/scripts/dbperiodic.sh --rdr --loadThe following example shows how to load the periodic delete configuration file from a specified location:
~scmscm/scripts/dbperiodic.sh --rdr --loadfile=path_to_periodic_delete_configuration_fileDeleting a Table
To delete one table or all current tables from the database, run the droptable.sh script:
~scmscm/scripts/droptable.sh --rdr[-f] tableParameterTable 5-4 lists the droptable.sh options.
The following example shows how to drop a table named RPT_SUR from the database with no request for confirmation:
~scmscm/scripts/droptable.sh --rdr -f RPT_SURThe following example shows how to drop all tables from the database:
~scmscm/scripts/droptable.sh --rdr ALLTABLESDeleting Old Records
To remove records from a database table based on the timestamps of the records, run the prunetable.sh script:
~scmscm/scripts/prunetable.sh --rdr [ -f ] num_days table_nameTable 5-5 lists the prunetable.sh options.
The following example shows how to delete all records that are more than seven days old from a table named RPT_SUR.
Because the -f flag is not specified, there can be requests for confirmation and errors are reported.
>~scmscm/scripts/prunetable.sh --rdr 7 RPT_SURUNICODE Support for Sybase Database
To add UTF support for the Sybase database, run the following script:
~scmscm/scripts/sybutfsupport.shManaging the Bundled Sybase Database
Managing the bundled database includes:
•
•
•
The Cisco Collection Manager adds a timestamp to each RDR it receives and stores in the database. This timestamp is used when various maintenance operations are performed on the database tables.
Use the following scripts to maintain the bundled Sybase database only:
•
•
•
Backing up the Database
In Release 3.7.0 and later, by default the backup database files are stored on the Cisco Collection Manager's home directory under the Sybase directory. An example of the home directory is /home/scmscm/sybase.
Note
Updating Sybase with a Changed IP Address
When you change the IP address, you must also update the Sybase server:
Step 1
Step 2
/etc/sysconfig/network-scripts/ifcfg-eth0Step 3
/etc/rc.d/init.d/network restartStep 4
~scmscm/setup/syb_interfaces.shStep 5
~scmscm/setup/sybase stop/startStep 6
~scmscm/cm/bin/cm restartUpdating Sybase Database Statistics
Run the update_statistics.sh script once a week at a time of low database load, for example, at night.
~scmscm/db_maint/update_statistics.shManaging the External MySQL Database
Managing the MySQL database includes:
•
The Cisco Collection Manager adds a timestamp to each RDR it receives and stores in the database. This timestamp is used when various maintenance operations are performed on the database tables.
Use the following scripts to maintain the MySQL database:
•
•
Backing up the MySQL Database
To create text file backups of all the tables in the database, use the mysqlback.sh script:
~scmscm/scripts/mysqlback.sh --rdrThe script converts all tables to ASCII files and copies the files to a backup directory. Table 5-6 lists the mysqlback.sh options.
Table 5-6 mysqlback.sh Options
Write backup CM:NF sql files to the specified directory
Displays these options
Note: The destination location path is defined *.properties file. Modify the path if you want to change the default location.
Restoring a MySQL Database
To restore a database from the backup file created by the mysqlback.sh script, run the mysqlrestore.sh script:
~scmscm/scripts/mysqlrestore.sh --rdrThe script converts all tables to ASCII files and copies the files to a backup directory. Table 5-7 lists the msqlrestore.sh options.
Table 5-7 mysqylrestore.sh Options
Restore the CM:RDR database using the sql files in the specified directory
Displays these options
Partitioning the MySQL Database
You can store records in an external MySQL database. When you configure MySQL as the database, partitioning is supported.
Prerequisites
The partitioning feature is supported only when the MySQL server meets the following conditions:
•
•
•
Restart the MySQL server to enable the configuration changes to take effect.
Configuring Partitioning
Caution
The partitioning configuration file (partitions.conf) is located in the ~scmscm/cm/config/dbpacks/mysql/4.0.20/partitions/ directory.
The following is a sample of the configuration file:
[partitions_config1]table_names = RPT_LUR,RPT_TOPS_PERIOD1,RPT_FUR,RPT_GURno_of_partition = 30period = 1scheduler_time = 23:30Each section begins with a unique section name starting with partitions_config in brackets and contains the parameters shown in Table 5-8. Separate the parameters and their values by an equal sign (=).
To create and view partitions, run the partitions.sh script:
~scmscm/scripts/partitions.sh --rdr [ --create ] [ --non_partitioned ] [ --partitioned ] [--partitions [table_name]] [events][ --help ]Table 5-9 lists the partitions.sh options.
Perform the following steps to enable the partitioning feature:
1.
2.
3.
4.
5.
Note
During the creation of the partitions, the partitions.sh script performs the following actions:
•
•
When the MySQL server is restarted after the partitioning, the event scheduler is disabled. To ensure the event scheduler remains enabled through a server restart, add "event_scheduler = 1" to the [mysqld] section of the my.cnf file.
Note
Note
Although the partitioning event scheduler runs every day, it to creates or deletes a partition based on the configuration parameters no_of_partition and period. Even if the server is restarted, the partitioning event scheduler is not disturbed.
Managing the Oracle Database
Oracle Support with Minimum Privileges
The following are the mandatory Privileges that are to be granted to the CM-DB user: Create Session , Create Table, Create Procedure, Create Table Space, Unlimited Table Space, Create Index, Execute Any Procedure
Syntax:
grant CONNECT ,CREATE TABLE ,CREATE TABLESPACE ,CREATE PROCEDURE ,UNLIMITED TABLESPACE,CREATE ANY INDEX, EXECUTE ANY PROCEDURE to pqb_admin
Note
Aggregating Database Data
You can aggregate database data according to varying time periods and store data in the same database tables. Both raw and aggregated data are stored in order to maintain compatibility with reporting tools that pull this data to generate analysis reports. All reports that process raw data for display also can process the aggregated data because the data structure is the same.
An input date parameter specifies the data records to process. These records are stored per time period. For xUR tables, hourly aggregation is performed starting from the input date value through the following fourteen days. Thereafter, aggregation occurs daily. For HTTP, video, and NUR VSA tables, aggregation occurs daily.
During the aggregation process, table rows are aggregated per time period. These values are placed in a temporary table. The source records are then removed to allow insertion of the aggregated records.
The aggregation feature is supported in the following tables:
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
Caution
Note
Configuring Aggregation
The aggregation configuration file ~scmscm/cm/config/aggregation.conf specifies the values used in the aggregation process.
The following is an example configuration file:
[agg_event1]procedure_names = P_RPT_MALUR_AGGREGATE, P_RPT_LUR_AGGREGATEno_of_days = 2scheduler_time = 23:30Each section in the aggregation configuration file begins with a unique section name in brackets
([]) starting with agg_event . The file contains the parameters shown in Table 5-10. Separate the parameters and their values with an equal sign (=).
To create a new aggregation event, run the createaggevents.sh script:
~scmscm/scripts/createaggevents.sh --rdr [ --create ] [ --help ]Table 5-11 lists the createaggevents.sh options.
Table 5-11 createaggevents.sh Options
Creates the corresponding aggregation events
Display these options
The Sybase, MySQL, and Oracle databases support aggregation. The following sections describe how to enable the aggregation feature:
•
•
•
Note
Enabling Aggregation in Sybase
Sybase versions 12 and 15 support data aggregation.
Complete the following steps to enable the aggregation feature:
1.
2.
3.
Cron job example:
crontab -eUpdate the path and expected time to schedule the job:
30 23 * * * /<path>/run_aggregation.sh4.
readme_run_aggregation_procedure.txt for more information.Enabling Aggregation in MySQL
MySQL versions 5.1.06 and later support data aggregation.
Complete the following steps to enable the aggregation feature:
1.
2.
Enabling Aggregation in Oracle
Oracle versions 9, 10, and 11 support data aggregation.
Complete the following steps to enable the aggregation feature:
1.
2.
Note
Managing the CSV Repository
You can run a utility script to manage the repository of CSV files output by the Collection Manager. the CSV adapter writes these files to disk for use by a service provider's operational support system (OSS) or by a third-party billing system. To prevent disk overflow, monitor the size of the CSV repository.
Note
Note
To successfully invoke this script, the HTTP adaptor of the Collection Manager must be running. If the adapter is down, an error message is printed.
•
•
•
CSV Repository File Structure
CSV files are stored in several subdirectories. Each subdirectory is given the number of a Raw Data Record (RDR) tag. RDR tags define the type of the RDR. Each RDR is stored in the subdirectory whose name matches its RDR tag number. For more information on RDR tags, see tCisco Service Control Application for Broadband Reference Guide.
The CSV files are numbered sequentially (automatically), with separate numbering in each directory. You can change the location of the parent directory by editing the cm.conf file located in the cm/config directory.
Configuring the CSV File Repository
Use the ~scmscm/scripts/csvconf.sh script to:
•
•
•
•
Table 5-12 lists the csvconf.sh options.
Note
Note
Note
The following example shows how to set the maximum number of CSV files per subdirectory to 1000.
>~scmscm/scripts/csvconf.sh --maxfiles=1000The following example shows how to set the maximum number records per CSV files to 10,000.
>~scmscm/scripts/csvconf.sh --maxlines=10000The following example shows how to delete all files from the CSV repository:
~scmscm/scripts/csvconf.sh --clearThe following example shows how to disable backing up old CSV files in the repository:
~scmscm/scripts/csvconf.sh --backups=falseConfiguring the Comma Escape
When a comma is contained within a field in a CSV file, an escape sequence indicates that the comma does not mark the end of the field.
Three escape methods are supported:
•
•
•
The first two escape methods are compatible with Microsoft Excel. The backslash method is not compatible with Excel, but is retained for backward compatibility.
Single quotation marks are used by default. You can change the escape method by modifying the value of the escapeMethod attribute. This attribute is located in the ~/scmscm/cm/config/csvadapter.conf file. You must use one of the following values: backslash, quote, or url.
Configuring Escape of Nonprintable Characters
The CSV adapter can escape nonprintable characters. Enabling this option incurs a performance penalty for the adapter. By default, the option is disabled.
When the option is enabled, a backslash (\) precedes each character that cannot be printed , such as CR and LF, contained within a field.
Enable this option in the ~/scmscm/cm/config/csvadapter.conf file. For changes to this configuration file to take effect, you must restart the Collection Manager.
