Document ID: 116112
Updated: Jul 15, 2013
Contributed by Cong Li, Glen Beach and Simon Chow, Cisco TAC Engineers.
Introduction
This document describes how to resolve the problem of a Cisco Service Control Application for Broadband (SCA BB) reporter that returns no new data after a Cisco Service Control Engine (SCE) has been upgraded.
Problem
After upgrade the SCE and Cisco Collection Manager (CM), you notice that the SCA BB reporter returns no new data.
Solution
This procedure describes the steps to resolve the problem that no new data is inserted.
The SCA BB reporter that is experiencing problems is based upon which raw data record (RDR) is in use. This example demonstrates how to troubleshoot a 'network usage per service' report, which is based on the Link Usage RDR (LUR) tag of 0xf0f0f005 / 4042321925. For more information, refer to Cisco Service Control Application for Broadband Reference Guide, Release 3.8.x, Raw Data Records: Formats and Field Contents.
- Verify the categories to which the RDR belongs. By default, LUR belongs to category 1.
The command to check the rdr-mapping is:
sce2#show rdr-formatter rdr-mapping all | in 0xf0f0f005
0xf0f0f005 1 - Ensure the SCE is connected to the correct CM and is sending category 1 RDRs without drops.
sce2#show rdr-formatter connection-status
Connection is: up
Forwarding mode: multicast
Connection Table:
-----------------------------------------------------------------
|Collector |Port |Status|Priority per Category: |
|IP-Address | | |---------------------------------------|
|Host-Name | | |Category1|Category2|Category3|Category4|
|-----------|-----|------|---------|---------|---------|---------|
|10.61.17.20|33000| up |100 | 100 | --- | --- |
-----------------------------------------------------------------
sce2#show rdr statistics
RDR-formatter statistics:
=========================
Category 1:
sent: 22597987
in-queue: 0
thrown: 0
format-mismatch: 0
unsupported-tags: 0
rate: 207 RDRs per second
max-rate: 335 RDRs per second
.... - Ensure the SCE time and timezone settings are correct. The RDR timestamp is based upon the SCE. Use 'clock timezone' under config mode in order to modify the timezone info if necessary.
- Verify the CM status.
bash-3.2$ ./alive.sh
STATUS OK. CM is functioning
-bash-3.2$ - Check the CM and queue configuration file, and ensure that category 1 RDRs are sent to the Java Database Connectivity Adapter (JDBCAdapter).
adapter.1=com.cisco.scmscm.adapters.jdbc.JDBCAdapter
adapter.2=com.cisco.scmscm.adapters.CSVAdapter
adapter.3=com.cisco.scmscm.adapters.topper.TAAdapter[dbase-hi]
adapter_id=1
priority=3
warning_size=40000
maximum_size=50000
tags=4042321936,4042321922,4042321925,4042321924,4042322000,4042323052,
4042321926,4042322064,4042321928,4042322048,4042321927 - Check the CM database type and version.
-bash-3.2$ ./cm dbversion
For more information, refer to Release Notes for Cisco Collection Manager, Release 3.8.x, Supported External Databases.
MySQL 5.0.45
or
-bash-3.2$./get_cm_info.sh
Collection Manager version:
CM CD Version 3.8.5 Build 14
Collection Manager DB type/version:
CM database:
MySQL 5.0.45 - Check the CM JDBCAdapter logs at opt\CM\cm\logs.
These example error logs are full of log files. The -bash-3.2$ ./dbtables.sh command returns similar errors.
2013-04-16 08:35:51,699 [main]
ERROR com.cisco.scmscm.db.JdbcConnection.RPT_SUR - JdbcConnection -
sleeping 30 sec, got SQLException again - JZ006: Caught IOException:
java.net.ConnectException: Connection refused - Re-run the CM database connection wizard, and restart the CM service.
-bash-3.2$ ./dbconf.sh
Enter the DB type:
1 - Oracle
2 - MySQL
3 - Sybase
4 - exit
Enter your choice: 2
Enter MySQL server host (current is localhost) :
Enter MySQL server listening port (current is 3306) :
Enter MySQL server instance id (current is apricot) :
Enter CM schema user name (current is pqb_admin) :
Enter CM schema user password (current is pqb_admin) :
Testing the DB connection... - Verify the database connectivity with ./dbtables.sh, and check that the table timestamp was updated. In this case, table RPT_LUR timestamp was not updated after the upgrade.
-bash-3.2$ ./dbtables.sh
Executing query for CM Tables...
name |min_time |max_time |
--------+---------------------+---------------------+
RPT_SUR | -| -|
RPT_LUR |2013-03-17 00:00:12.0|2013-04-16 01:15:24.0|
RPT_VLUR| -| -|
RPT_PUR |2013-03-17 00:00:16.0|2013-04-16 01:15:30.0|
RPT_TR |2013-03-16 23:59:52.0|2013-04-16 01:16:18.0|
RPT_FUR | -| -| - Check the JDBCAdapter logs again. Errors such as these indicate that the CM failed to perform the insert (RDR) operation into the database.
2013-04-16 10:42:47,356 [wrkr/JDBCAdapter]
ERROR com.cisco.scmscm.db.DbInserter.RPT_TR - insert err - Exception
while inserting the rdr values [27.110.229.154@TCB_Batch10, 26, 112,
1204, 9, 295166279, 443, , , 460252570, 49745, 0, 1366081430, 59670, 0,
2424, 4787, 0, 0, 23, 6, 251854848, 0, 0, 0, 0, , ], for the rdr
tag = 4042321936
java.lang.ArrayIndexOutOfBoundsException
2013-04-16 10:42:47,356 [wrkr/JDBCAdapter]
ERROR com.cisco.scmscm.db.DbInserter.RPT_TR - insert err (more details
in next line), tag=4042321936, fieldNo-1=25 curSqlIndex=28, i=27
java.lang.ArrayIndexOutOfBoundsException
2013-04-16 10:42:47,357 [wrkr/JDBCAdapter]
ERROR com.cisco.scmscm.adapters.jdbc.JDBCAdapter - processRDR - got
Throwable
java.lang.ArrayIndexOutOfBoundsException
2013-04-16 10:42:47,357 [wrkr/JDBCAdapter]
ERROR com.cisco.scmscm.db.DbInserter.RPT_TR - insert err - Exception
while inserting the rdr values [27.110.210.197@TCB_Batch21, 48, 84, 40,
18, 1120172119, 80, , , 460247749, 57178, 0, 1366081430, 620, 0, 1125,
759, 5, 9, 42, 6, 184681216, 0, 0, 0, 0, , ], for the rdr tag = 4042321936
java.lang.ArrayIndexOutOfBoundsException
2013-04-16 10:42:47,357 [wrkr/JDBCAdapter]
ERROR com.cisco.scmscm.db.DbInserter.RPT_TR - insert err (more details
in next line), tag=4042321936, fieldNo-1=25 curSqlIndex=28, i=27
java.lang.ArrayIndexOutOfBoundsException
2013-04-16 10:42:47,357 [wrkr/JDBCAdapter]
ERROR com.cisco.scmscm.adapters.jdbc.JDBCAdapter - processRDR - got
Throwable - Check that there is adequate space on the disk and in the database.
-bash-3.2$ df -h
Filesystem Size Used Avail Use% Mounted on
/dev/cciss/c0d0p3 14G 12G 1.1G 92% /
/dev/cciss/c0d0p7 15G 1.4G 13G 10% /var
/dev/cciss/c0d0p6 29G 4.9G 23G 19% /opt
/dev/cciss/c0d0p5 70G 56G 9.6G 86% /opt/MySQL
/dev/cciss/c0d0p1 99M 12M 82M 13% /boot
tmpfs 3.9G 0 3.9G 0% /dev/shmbash-3.2$ ./dbfree.sh
SCHEMA_NAME|TOTAL_TABLES|DATA_USED_IN_MB|MAX_DATA_LENGTH_IN_MB|
-----------+------------+---------------+---------------------+
apricot | 20 |44259.73 |118472377892863.98 | - Create a new MySQL database for test purposes in order to eliminate issues with the MySQL database, such as database corruption or a schema that was not updated. You will need a MySQL root login.
- Connect to MySQL.
bash-3.2$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 203
Server version: 5.0.45 Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer. - Display the current database.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| apricot |
| cacti |
| mysql |
| test |
+--------------------+
5 rows in set (0.01 sec) - Create a new database named 'apricot1.'
mysql> CREATE DATABASE apricot1;
- Verify the database was created.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| apricot |
| apricot1 |
| cacti |
| mysql |
| test |
+--------------------+
6 rows in set (0.01 sec) - Grant database permission to the pqb_admin user.
mysql> grant all privileges on *.* to 'pqb_admin'@'localhost'
identified by 'pqb_admin' with grant option; Query OK, 0 rows
affected (0.00 sec)
mysql> grant all privileges on *.* to 'pqb_admin'@'localhost'
identified by 'pqb_admin' with grant option; Query OK, 0 rows
affected (0.00 sec)
mysql> grant all privileges on *.* to 'pqb_admin'@'10.61.17.20'
identified by 'pqb_admin' with grant option; Query OK, 0 rows
affected (0.00 sec)
- Connect to MySQL.
- Re-run the CM database connection wizard, connect to the new database, and restart the CM service.
-bash-3.2$ ./dbconf.sh
Enter the DB type:
1 - Oracle
2 - MySQL
3 - Sybase
4 - exit
Enter your choice: 2
Enter MySQL server host (current is localhost) :
Enter MySQL server listening port (current is 3306) :
Enter MySQL server instance id (current is apricot) :apricot1
Enter CM schema user name (current is pqb_admin) :
Enter CM schema user password (current is pqb_admin) :
Testing the DB connection... - Ensure that the appropriate tables are created within the new database, apricot1.
mysql> user apricot1;
If you are familiar with SQL syntax, you can, for example, use SQL in order to check the content of a particular table and its timestamp.
Database changed
mysql> show tables;
+-----------------------------+
| Tables_in_apricot1 |
+-----------------------------+
| CONF_SE_TZ_OFFSET |
| IMEI_DEVICETYPE |
| RPT_FUR |
| RPT_GUR |
| RPT_LUR |
| RPT_MALUR |
| RPT_MEDIA |
| RPT_PUR |
| RPT_SPAM |
| RPT_SUR |
| RPT_TOPS_PEAK_CUMULATIVE |
| RPT_TOPS_PEAK_PERIOD |
| RPT_TOPS_PERIOD0 |
| RPT_TOPS_PERIOD0_CUMULATIVE |
| RPT_TOPS_PERIOD1 |
| RPT_TOPS_PERIOD1_CUMULATIVE |
| RPT_TR |
| RPT_VLUR |
| RPT_ZUR |
+-----------------------------+
19 rows in set (0.00 sec)
mysql> slect select * from RPT_LUR
- Check the CM to database connection again, and notice that the table max_time was updated.
-bash-3.2$ ./dbtables.sh
Executing query for CM Tables...
name |min_time |max_time |
--------+---------------------+---------------------+
RPT_SUR | -| -|
RPT_LUR |2013-04-16 12:15:12.0|2013-04-16 12:16:24.0|
RPT_VLUR| -| -|
RPT_PUR |2013-04-16 12:15:16.0|2013-04-16 12:16:30.0|
RPT_TR |2013-04-16 12:15:16.0|2013-04-16 12:16:18.0|
... - Navigate to Preferences > Reporter > Databases > Add > Advanced in order to configure the SCA BB reporter to the new database, apricot1. Use the same URL from the previous JDBC connection and change the database name from apricot to apricot1.
- Navigate to Activate > Test Active DB and ensure that all four tests passed.
The failure of the CM SQL insert operation is caused by the fact that, during the CM upgrade, there was a connectivity or credential issue and the MySQL database apricot shema was not upgraded appropriately.
Updating DB objects ...
...
The following problem occured during DB operation:
JZ006: Caught IOException: java.net.ConnectException: Connection refused
Error: DB schema update failed. Fix the problem and rerun the upgrade procedure
Done
Removing the backuped config ...
Done
Done upgrade
Open a Support Case 
(Requires a Cisco Service Contract.)
Related Cisco Support Community Discussions
The Cisco Support Community is a forum for you to ask and answer questions, share suggestions, and collaborate with your peers.
Refer to Cisco Technical Tips Conventions for information on conventions used in this document.
Updated: Jul 15, 2013
Document ID: 116112