Guest

Cisco Service Control Application for Broadband

SCA BB Reporter Returns No New Data

Document ID: 116112

Updated: Jul 15, 2013

Contributed by Cong Li, Glen Beach and Simon Chow, Cisco TAC Engineers.

   Print

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.

  1. Verify the categories to which the RDR belongs. By default, LUR belongs to category 1.

    Note: While you can have a customized setting for rdr-mapping, the customized setting reverts back to the default after reload.

    The command to check the rdr-mapping is:

    sce2#show  rdr-formatter rdr-mapping  all | in 0xf0f0f005
    0xf0f0f005 1
  2. 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
    ....
  3. 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.

  4. Verify the CM status.

    bash-3.2$ ./alive.sh
    STATUS OK. CM is functioning
    -bash-3.2$
  5. 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
  6. Check the CM database type and version.

    -bash-3.2$ ./cm dbversion
    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
    For more information, refer to Release Notes for Cisco Collection Manager, Release 3.8.x, Supported External Databases.

  7. 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
  8. 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...
  9. 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 |                    -|                    -|
  10. 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
  11. 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/shm

    bash-3.2$ ./dbfree.sh 
    SCHEMA_NAME|TOTAL_TABLES|DATA_USED_IN_MB|MAX_DATA_LENGTH_IN_MB|

    -----------+------------+---------------+---------------------+

    apricot    | 20         |44259.73       |118472377892863.98   |
  12. 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.

    1. 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.
    2. Display the current database.

      mysql> show databases;
      +--------------------+
      | Database |
      +--------------------+
      | information_schema |
      | apricot |
      | cacti |
      | mysql |
      | test |
      +--------------------+
      5 rows in set (0.01 sec)
    3. Create a new database named 'apricot1.'

      mysql> CREATE DATABASE apricot1;
    4. Verify the database was created.

      mysql> show databases;
      +--------------------+
      | Database |
      +--------------------+
      | information_schema |
      | apricot |
      | apricot1 |
      | cacti |
      | mysql |
      | test |
      +--------------------+
      6 rows in set (0.01 sec)
    5. 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)
  13. 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...
  14. Ensure that the appropriate tables are created within the new database, apricot1.

    mysql> user  apricot1;
    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)
    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.

    mysql> slect       select * from RPT_LUR
  15. 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|

    ...
  16. 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.

  17. 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
Updated: Jul 15, 2013
Document ID: 116112