Managing Databases and the CSV Repository

Table Of Contents

Managing Databases and the CSV Repository

Common Database Management Tasks

Listing the Database Tables

Managing the Periodic Deletion of Old Records

Managing the Bundled Database

Deleting a Table

Deleting Old Records

Backing Up the Database

Restoring a Database

Updating Sybase with a Changed IP Address

Managing the CSV Repository

CSV Repository File Structure

Configuring the CSV File Repository

Configuring the Comma Escape

Configuring Escape of Nonprintable Characters

Managing Databases and the CSV Repository

This module explains how to use utility scripts to manage the Collection Manager database and the CSV repository.

Many of the database management tasks are applicable only to the bundled Sybase database.

Note For general instruction on using utility scripts, see Using Utility Scripts, page 4-1.

• Common Database Management Tasks

• Managing the Bundled Database

• Managing the CSV Repository

Common Database Management Tasks

The database management tasks that are applicable to all of the supported databases are:

•Generating a list of the database tables

•Defining and applying the schedule for the periodic deletion of old records

Every record stored in the database is given a timestamp indicating the time that the Cisco Service Control Management Suite (SCMS) Collection Manager (CM) received the Raw Data Record (RDR). This timestamp is used when various maintenance operations are performed on the database tables.

The following scripts are used to configure and maintain the database:

•~scmscm/scripts/dbtables.sh

•~scmscm/scripts/dbperiodic.py

•~scmscm/db_maint/create_periodic_del_procs.sh

Listing the Database Tables

To display a list all of the tables in the database, use the dbtables.sh script:

~scmscm/scripts/dbtables.sh
Where applicable, the number of lines in the table and the earliest and latest timestamps are displayed.

Actual content of the tables can be displayed using the Cisco Service Control Application (SCA) Reporter. For more information, see the Cisco Service Control Application Reporter User Guide .

The following table lists the dbtables.sh script options.

Table 5-1 dbtables.sh Options

Option

Description

-l

Lists the existing table names only (without statistics)

-a

Include the non-report tables in the listing

-f

Enable fast line counting, use the client rather than JDBC

Note Applicable only for the bundled Sybase database.

-t { sec_num }

The maximal waiting time, in seconds, for the response. The default is no timeout.

-h

Prints this help message and exits.

The following is a sample output from the dbtables.sh script:

>~scmscm/scripts/dbtables.shExecuting 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 Periodic Deletion of Old Records

In order to manage the periodic deletion of old records, it is necessary to perform the following general steps:

•Install the periodic delete procedures if they were not installed during the CM installation:

Log on as the scmscm user, start the CM, wait 1-2 minutes for the database tables to be created, and then run the script:

~scmscm/db_maint/create_periodic_del_procs.sh.
•Edit the periodic delete configuration file.

•Use the dbperiodic.py utility script to apply the new configuration.

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.

Note All periodic delete activity is recorded in the system log file ( /var/adm/messages ).

Note Periodic delete when using a MySQL database is supported only on version 5.

• Configuring Periodic Delete

• Applying the Periodic Delete Configuration File

Configuring Periodic Delete

The periodic delete configuration file ( dbperiodic.conf ) is, by default, located at ~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 The name of each section of the file is not used when the file is parsed; use whatever names you wish.

Each section begins with the section name in brackets, and should contain 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 (=). Examples of periodic delete configuration files are given following the table.

Table 5-2 Parameters in the Periodic Delete Configuration File

Parameter Name

Explanation

Values

Default

Example

active

Whether or not to use this section of the configuration file

true/false

true

false

tablenames

Names of the tables to which this section applies

Names of tables separated by commas, or * for all tables

* (all)

RPT_SUR,RPT_LUR

daystokeep

Number of days to keep records

Positive integers

14

30

minute

hour

day

month

When to perform the deletion in this section of the configuration file

0 ... 59, *

0 ... 23, *

1 ... 31, *

1 ... 12, *

0 *

(all) *

(all) *

(all)

0

0,4,8,12,16,20

1

1,3,5,7,9,11

Note Values for all parameters except active and daystokeep can be either a single value, a list of values separated by commas, a range of values (two values separated by a dash), or an asterisk (*) which signifies all possible values. A range is not possible for tablenames.

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, use the dbperiodic.py script:

~scmscm/scripts/dbperiodic.py[--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 dbperiodic.py Options

Option

Description

--load

Load the periodic delete configuration from /export/home/scmscm/db_maint/ dbperiodic.conf

--loadfile=path to periodic delete configuration file

Load the periodic delete configuration file from the specified directory

--dump

Print the periodic delete configuration

--h

Display these options

The following example prints the current periodic delete configuration:

~scmscm/scripts/dbperiodic.py --dump

Note This script prints the loaded periodic delete configuration. If the current periodic delete configuration file was not yet loaded, the actual configuration may vary from the script�s output.

The following example loads the periodic delete configuration file from ~scmscm/db_maint/dbperiodic.conf :

~scmscm/scripts/dbperiodic.py --load
The following example loads the periodic delete configuration file from a specified location:

~scmscm/scripts/dbperiodic.py --loadfile=path_to_periodic_delete_configuration_file

Managing the Bundled Database

Managing the bundled database includes:

•Deleting a table

•Manually deleting old records from a table

•Backing up and restoring a database

•Updating Sybase with a changed IP address

Every record stored in the database is given a timestamp indicating the time that the Cisco Service Control Management Suite (SCMS) Collection Manager (CM) received the Raw Data Record (RDR). This timestamp is used when various maintenance operations are performed on the database tables.

The following scripts are used to maintain the bundled Sybase database only:

•~scmscm/scripts/droptable.sh

•~scmscm/scripts/prunetable.sh

•~scmscm/scripts/sybback.sh

•~scmscm/scripts/sybrestore.sh

Deleting a Table

To delete a single table or all current tables from the database, use the droptable.sh script:

~scmscm/scripts/droptable.sh[-f] tableParameter
Table 5-4 droptable.sh Options Option Description table_name Drop table_name from the database ALLTABLES Drop all tables from the database -f Drop by force (no questions asked or errors reported) -h Display these options

The following example drops a table named RPT_SUR from the database with no request for confirmation:

~scmscm/scripts/droptable.sh -f RPT_SUR
The following example drops all tables from the database:

~scmscm/scripts/droptable.sh ALLTABLES
Deleting Old Records

To remove records from a database table based on the timestamps of the records, use the prunetable.sh script:

~scmscm/scripts/prunetable.sh[-f] num_days table_name
Table 5-5 prunetable.sh Options Option Description num_days The maximum age (in days) of records that will not be deleted. table_name The table whose records are to be deleted. -f Drop by force (no questions asked or errors reported). -h Display these options.

The following example shows how to delete all records that are more than seven days old from a table named RPT_SUR.

Since the -fflag is not specified, there may be requests for confirmation and errors will be reported.

>~scmscm/scripts/prunetable.sh 7 RPT_SUR
Backing Up the Database

To create text file backups of all the tables in the database, use the sybback.sh script:

~scmscm/scripts/sybback.sh -d path_to_backup_directory
The script converts all tables to ASCII files and copies the files to a backup directory.

Table 5-6 sybback.sh Options

Option

Description

-d path_to_backup_directory

Write backup text files to the specified directory

-h

Display these options

Restoring a Database

To restore a database from the backup file that was created by the sybback.sh script, use the sybrestore.sh script:

~scmscm/scripts/sybrestore.sh -d path_to_restore_directory
Table 5-7 sybrestore.sh Options Option Description -d path_to_restore_directory Restore the database using the text files in the specified directory -h Display these options

Note The scripts sybback.sh and sybrestore.sh are not a viable backup mechanism for Sybase. They are designed for backing up and restoring small amounts of data; for example, transferring small tables between machines.

Note If you require a viable backup mechanism, please consult the Sybase Backup Server product documentation.

Updating Sybase with a Changed IP Address

It is necessary to update the Sybase server when you change its IP addreass. As the root user run the following command:

~scmscm/setup/syb_interfaces.sh

Managing the CSV Repository

You can use a utility script to manage the repository of CSV files output by the CM. These files are written to the disk by the Comma-Separated Value (CSV) Adapter for use by a service provider's operational support system (OSS) or by a third-party billing system. The size of the CSV repository should be monitored to prevent disk overflow.

Note If the backup parameter is set to true, failure to delete CSV files may result in disk overflow (No CSV files will ever be deleted.)

Note The third-party application is responsible for managing the CSV files and deleting them as necessary.

To successfully invoke this script, the HTTP Adaptor of the CM must be running. If the adapter is down, an error message is printed.

• CSV Repository File Structure

• Configuring the CSV File Repository

• Configuring the Comma Escape

• Configuring Escape of Nonprintable Characters

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 the Cisco Service Control Application for Broadband Reference Guide .

The CSV files are (automatically) sequentially numbered, 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 csvconf.sh script, ~scmscm/scripts/csvconf.sh , to:

•List the number of RDRs currently stored in the repository.

•Configure the maximum number of CSV files and the maximum permissible number of reports (lines) in each file.

•Control whether a backup is made whenever an old CSV file is about to be overwritten.

•Control whether each line in a CSV file contains an indication of the IP of the Service Control Engine (SCE) that sent this RDR. (By default, this option is off.)

Note Instead of using this script, you can edit the file ~scmscm/cm/config/csvadapter.conf. Changes in this file require a CM restart to take effect.

Note The same configuration is applied to all subdirectories in the CSV Repository.

Note Setting these parameters does not change existing CSV files; it affects only files that are created subsequently.

Table 5-8 csvconf.sh Options

Option

Description

--list

Display the CSV repository contents (the number of RDRs currently stored in the repository).

--clear

Delete all files from the CSV repository. (This option deletes all CSV files, but not the directories in which they are contained.)

--maxlines=N

Set the maximum number of RDRs per CSV file to N(an integer between 1 and 20,000).

--maxfiles=M

Set the maximum number of CSV files in each subdirectory to M(an integer between 10 and 10,000.)

--backups={true|false}

Enable or disable backup of old CSV files.

--recordsource={true|false}

Enable or disable the inclusion of the record source in CSV files.

The following example shows how to set the maximum number of CSV files per subdirectory to 1000.

>~scmscm/scripts/csvconf.sh --maxfiles=1000
The following example shows how to set the maximum number records per CSV files to 10,000.

>~scmscm/scripts/csvconf.sh --maxlines=10000
The following example deletes all files from the CSV repository:

~scmscm/scripts/csvconf.sh --clear
The following example disables backing up of old CSV files in the repository:

~scmscm/scripts/csvconf.sh --backups=false
Configuring the Comma Escape

When a comma is contained within a field in a CSV file, an escape sequence is used to indicate that the comma does not mark the end of the field.

Three escape methods are supported:

•Single quotation marks—Single quotation marks surround any field that contains one or more commas. There is no special treatment of single quotation marks already present in existing RDRs.

•URL—Each comma contained within a field is replaced by %2C. There is no special treatment of such sequences already present in existing RDRs.

•Backslash—Each comma contained within a field is preceded by a backslash (\). There is no special treatment of backslashes already present in existing RDRs.

The first two escape methods are compatible with Microsoft� Excel. The Backslash method is not compatible with Excel, but is retained for backward compatibility.

By default, single quotation marks are used. You can change the escape method by modifying the value of the escapeMethod attribute. This attribute is located in the csvadapter.conf file in the CSVAdapter directory. The value must be one of: backslash , quote , or url.

Configuring Escape of Nonprintable Characters

Optionally, 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, each non-printable character, such as CR and LF, contained within a field is preceded by a backslash (\).

This option can be enabled in the csvadapter.conf file in the CSVAdapter directory. Changes in this file require a CM restart to take effect.

Option	Description
`-l`	Lists the existing table names only (without statistics)
`-a`	Include the non-report tables in the listing
`-f`	Enable fast line counting, use the client rather than JDBC Note Applicable only for the bundled Sybase database.
`-t { sec_num }`	The maximal waiting time, in seconds, for the response. The default is no timeout.
`-h`	Prints this help message and exits.

Parameter Name	Explanation	Values	Default	Example
active	Whether or not to use this section of the configuration file	true/false	true	false
tablenames	Names of the tables to which this section applies	Names of tables separated by commas, or * for all tables	* (all)	RPT_SUR,RPT_LUR
daystokeep	Number of days to keep records	Positive integers	14	30
minute hour day month	When to perform the deletion in this section of the configuration file	0 ... 59, * 0 ... 23, * 1 ... 31, * 1 ... 12, *	0 * (all) * (all) * (all)	0 0,4,8,12,16,20 1 1,3,5,7,9,11

Option	Description
`--load`	Load the periodic delete configuration from /export/home/scmscm/db_maint/ dbperiodic.conf
`--loadfile=path to periodic delete configuration file`	Load the periodic delete configuration file from the specified directory
`--dump`	Print the periodic delete configuration
`--h`	Display these options

Option	Description
table_name	Drop table_name from the database
ALLTABLES	Drop all tables from the database
-f	Drop by force (no questions asked or errors reported)
-h	Display these options

Option	Description
`num_days`	The maximum age (in days) of records that will not be deleted.
`table_name`	The table whose records are to be deleted.
`-f`	Drop by force (no questions asked or errors reported).
`-h`	Display these options.

Option	Description
`-d path_to_backup_directory`	Write backup text files to the specified directory
`-h`	Display these options

Option	Description
`-d path_to_restore_directory`	Restore the database using the text files in the specified directory
`-h`	Display these options

Option	Description
`--list`	Display the CSV repository contents (the number of RDRs currently stored in the repository).
`--clear`	Delete all files from the CSV repository. (This option deletes all CSV files, but not the directories in which they are contained.)
`--maxlines=N`	Set the maximum number of RDRs per CSV file to N(an integer between 1 and 20,000).
`--maxfiles=M`	Set the maximum number of CSV files in each subdirectory to M(an integer between 10 and 10,000.)
`--backups={true\|false}`	Enable or disable backup of old CSV files.
`--recordsource={true\|false}`	Enable or disable the inclusion of the record source in CSV files.