CUDB Application Counters

Contents

1Introduction
1.1Scope
1.2Revision Information
1.3Target Groups
1.4Prerequisites
1.5Typographic Conventions
1.6Related Terms

2

Overview
2.1Architecture
2.2Description
2.3Dependencies and Interactions

3

Operation and Maintenance
3.1Configuration
3.2Fault Management
3.3Performance Management
3.4Security
3.5Logging

4

Appendix: Examples
4.1Creating Application Counters Configuration File
4.2Creating Tables
4.3Stored Procedures
4.4ESA Configuration
4.5Configuring Cron File: appCounters.cron

Glossary

Reference List

1   Introduction

This document provides a description of the application counters function in the Ericsson Centralized User Database (CUDB) and describes how to create and configure application counters by using the Application Counters Framework.

1.1   Scope

The purpose of this document is to describe the application counters function. The application counters function is required by applications storing their data in CUDB to show statistics generated from the stored data.

1.2   Revision Information

This section contains the changes in the feature between the releases of this document.

Rev. A This document is based on 10/155 34-HDA 104 03/9 with revised content and structure.
Rev. B Editorial changes only.

1.3   Target Groups

This document is intended for system administrators.

1.4   Prerequisites

Users of this document must have knowledge and experience of the following:

1.5   Typographic Conventions

Typographic conventions can be found in the following document:

1.6   Related Terms

This section is not applicable to this feature.

2   Overview

The application counters function allows applications to create counters related to the application data stored in CUDB. Counter values are stored in CUDB tables, which are periodically consolidated and written to 3GPP standards-compliant EXtended Markup Language (XML) files, the officially supported format.

2.1   Architecture

Application counters are generated at system level, but are published by all CUDB nodes. This means that while each application counter shows the situation considering the whole CUDB system, they can be obtained from any of the CUDB nodes.

Note:  
Application Counters values obtained from nodes with slave PLDB may differ from the values in the node with the master PLDB replica as a consequence of the asynchronous replication.

Figure 1 shows interactions between application counters process and the rest of the components in the CUDB system.

Figure 1   Application Counters Overview

Application counters are generated by collecting information from replicas of all Data Store Unit Groups (DSGs) in the whole CUDB system (or from PLDBs in case of counters related to information stored in a PLDB), and published in XML files. The XML files can then be fetched through Secure File Transfer Protocol (SFTP).

2.2   Description

The Application Counters function supports two major tasks, counter generation and publishing.

2.3   Dependencies and Interactions

Stored procedures are lost and must be re-installed in that cluster in case of the following circumstances:

Because application counter values are fetched from the Application Counters DB and exposed in the Lightweight Directory Access Protocol (LDAP) Directory Information Tree (DIT) under a separate root entry, these values are not available in the DIT until the stored procedures are re-installed.

3   Operation and Maintenance

This section describes the operation and maintenance of the Application Counters function.

3.1   Configuration

To configure CUDB application counters, the following steps are followed:

3.1.1   Application Counters Configuration File

The application counters configuration file contains the information needed for generating counters and storing them in PLDB.

The following information is included in this configuration file:

The format of the configuration file, per counter group, is as follows:

[*PL_COUNTER*]
<procedure_name>
 <table_name>
 <counter_1_name>=<paramValue1>#<paramValue2>#...#<paramValueN>
 <counter_2_name>=<paramValue>
...
<counter_n_name>

The configuration file must be stored in /home/cudb/oam/performanceMgmt/appCounters/config/ with extension .conf.

Example 1 shows a sample application counters configuration file.

3.1.2   Create Tables in PLDB

Tables for the application counters must be created in the cudb_application_counters database, in PLDB. The SQL scripts that create these tables are stored under /home/cudb/oam/performanceMgmt/appCounters/schema/ and the files have .sql extension.

It is recommended to use each table for a group (collection) of counters.

Note:  
Defining a primary key for the Application Counters tables is mandatory. For example, it can be the first column of the table.

Note:  
Using the same counter and group names that are used in ESA is recommended as it is simpler to maintain all the configuration files for a counter (or a group of counters). For further information about the ESA collection of counters, refer to ESA Performance Management, Reference [8].

The tables must be filled with initial values and created in a single CUDB node on which PLDB runs as Master.

Before fetching the counter values, wait until these tables are replicated in all PLDBs in the CUDB system. See Example 2.

Once the .sql file is created, execute it on an SC as follows :

shell> mysql -h<pl0,pl1> -P15000 --user=<user_name> --password=<password> -e "source <complete_path_to_applicationCountersTable.sql>"

In the above command, <user_name> and <password> are obtained from CUDB Users and Passwords, Reference [1].

Note:  
All tables must be created only from a single PLDB (PL0 or PL1), as these tables depend on the database cluster and are visible from other PLDBs.

3.1.3   Create Stored Procedures

The scripts with the stored SQL procedures must be kept in the following location on all CUDB nodes:

/home/cudb/oam/performanceMgmt/appCounters/procedures/

The database where the procedures are executed has to be specified at the beginning of the script, as shown in Example 3.

Note:  
Application counters are defined on relational tables and rows, not on LDAP trees, so if the application uses LDAP Data Views, it adds an extra level of indirection, which must be considered during the definition of the counter (Views LDAP tree -> core DIT tree -> tables and rows). For detailed information on LDAP Views, refer to CUDB LDAP Data Views Management, Reference [7].

Once the stored procedure file is created, execute it on all access servers of the PLDB or DSG clusters in the CUDB system (depending on whether the counter is contained in PLDB or DSG), necessary so that all needed stored procedures receive the complete set of application counters.

The command to execute on the database cluster access servers of PLDB is the following:

shell> mysql -h<pl0,pl1> -P15000 --user=<user_name> --password=<password> -e "source <complete_path_to_applicationCountersProcedure.sql>"

In the above command, <user_name> and <password> are obtained from CUDB Users and Passwords, Reference [1].

Note:  
The stored procedures must be created from both PL0 and PL1, as stored procedures are created in the database cluster access server and are not propagated to other PLDB.

The command to execute on the database cluster access servers of the DS is the following:

shell> mysql -h<dsX_0,dsX_1> -P<15000+10*X> --user=<user_name> --password=<password> -e "source <complete_path_to_<applicationCountersProcedure.sql>"

In the above command, <user_name> and <password> are obtained from CUDB Users and Passwords, Reference [1].

Note:  
The stored procedures must be created from both DSX_0 and DSX_1, as stored procedures are created on the database cluster access server and are not propagated to other DS.

See Section 4.3.1 to learn about the best practices in writing stored procedures.

3.1.4   Configure ESA

Accounting for counters, configure ESA with the following three steps:

Detailed instructions follow below.

3.1.4.1   Create Scripts to Fetch Application Counters to ESA

The shell scripts required for a group of counters are called by ESA. To ease the retrieval and formatting of counter data, the following shell library is provided with functions that scripts can use:

/home/cudb/oam/performanceMgmt/appCounters/scripts/appCountersLib.sh

In each script, a different query must be defined to retrieve different sets of application counters. Example 7 shows a sample script.

Changing the QUERY and COUNTERID variables is enough for these scripts to work. QUERY must be an SQL SELECT sentence retrieving all the counters in a group located in the same table.

The scripts must be stored in the following location for all CUDB nodes of the CUDB system:

/home/cudb/oam/performanceMgmt/appCounters/scripts/

3.1.4.2   Configure ESA to Use Scripts

An XML file must be created to configure ESA to fetch the application counters in /home/cudb/oam/performanceMgmt/config/PmCounters on all CUDB nodes. The description of the XML file can be found in ESA Performance Management, Reference [8]. See Example 8.

Note:  
The default value for the interval attribute within tag <dataSource> is 900, representing the time interval, in seconds, between fetching the application counter values from PLDB.

3.1.4.3   Configure Jobs to Generate Output Files

Application counters output files are configured using XML-based job files placed in the following folder:

/home/cudb/oam/performanceMgmt/config/PmJobs

This folder is located on shared storage for all blades or VMs in CUDB. The job files within contain configuration parameters, such as the periodicity of generating counter files. Each counter group is configured with one job file.

The format of these files is described in ESA Performance Management, Reference [8]. The steps for configuring the output files are as follows:

  1. Create the corresponding job files and place them in the above folder through SFTP established directly from the Network Management Systems (NMS).
  2. To make the jobs configuration effective, restart the Performance Management Agent (pmagent) with the cudbPmJobReload command. Refer to CUDB Node Commands and Parameters, Reference [4] for further information on this command.
Note:  
If CUDB is integrated with an NMS that provides its own job definitions, keep only the appropriate job definitions and remove unnecessary ones to avoid duplication. For example, if CUDB is integrated with OSS-RC, keep the performance jobs created by OSS-RC and remove the default CUDB jobs.

For further information about output files location, naming, and format refer to CUDB Performance Guide, Reference [3].

3.1.5   Configure Cron Task

To generate application counters, tasks must be introduced in the cron of the SCs. This section describes the configuration file for this task and the steps required to configure cron.

The procedure to configure cron is as follows:

  1. Create, or if it is already created, modify the file at /home/cudb/oam/performanceMgmt/appCounters/scripts/appCounters.cron to include the new task, following this format:

    /opt/ericsson/cudb/OAM/bin/cudbApplicationCounters -C
    /cluster/home/cudb/oam/performanceMgmt/appCounters/config/<app_counter>.conf -U 1 -u LOCAL1

    The <app_counter>.conf parameter corresponds to the configuration file created in Section 3.1.1.

    See Example 9.

    Note:  
    Using the appCounter.cron file as a template (see Section 4.5), several cron files can be defined, so specific execution times or periodicity can be defined independently for different counter groups. This also allows the activation or deactivation of certain groups of counters.

    Note:  
    The heading of the file must be #!/bin/bash -l to enable the use of environment variables.

  2. Modify the root file at /var/spool/cron/tabs/root in each SC of each CUDB node to include the new task ending with >> /dev/null.

    For example, to execute the application counters every 15 minutes, insert the following line:

    0,15,30,45 * * * */bin/bash -l /home/cudb/oam/performanceMgmt/appCounters/procedures/appCounters.cron>> /dev/null

    Note:  
    For each cron file created, a line must be added to crontab, specifying the time or periodicity of the group of counters inside the cron file. To deactivate a given set of group counters, the corresponding line in crontab of the file that contains the set of group counters must be deleted or commented. Once a set of group counters is deactivated, active alarms related to these counters can be cleared manually from the system.

    Edit crontab file with the crontab -e command instead of a text editor.

  3. Activate the new scheduled tasks in the operating system in both SCs on all CUDB nodes of the CUDB system with the following command:

    # crontab /var/spool/cron/tabs/root

    Note:  
    This step is unnecessary if the previous step was completed using the crontab -e command on both SCs.

The cron in the SCs must be configured to call the cudbApplicationCounters executable every 15 minutes to retrieve counter values and store them in PLDB. For further information about the parameters of this executable, refer to CUDB Node Commands and Parameters, Reference [4].

3.2   Fault Management

During the execution of the application counters process, the following alarm can be raised:

3.3   Performance Management

This section is not applicable to this feature.

3.4   Security

This section is not applicable to this feature.

3.5   Logging

During the processing of application counters, a number of events can be logged. For further information, refer to CUDB Node Logging Events, Reference [5].

4   Appendix: Examples

This section provides examples for the procedures described in this document.

4.1   Creating Application Counters Configuration File

Example 1 shows a sample application counters configuration file.

Example 1   Application Counters Configuration File

*PL_COUNTER*
GET_AUCSUBS_COUNTERS
GRP_AUCSUBS
NSUBSCNT
NGSUBSCNT
NUSUBSCNT
NUMSUBSPARAMS=30

4.2   Creating Tables

Example 2 shows creating a table in PLDB.

Example 2   Creating a Table in PLDB

The stored procedures for each counter group will have 
the same name as the group.
Therefore the procedure name for this group will be 
GET_AUCSUBS_COUNTERS
*/
USE cudb_application_counters;
DROP TABLE IF EXISTS GRP_AUCSUBS;	
CREATE TABLE GRP_AUCSUBS(
NSUBSCNT integer PRIMARY KEY,
NGSUBSCNT integer,NUSUBSCNT integer) 
ENGINE=NDB;	

INSERT INTO GRP_AUCSUBS VALUES (0,0,0);

4.3   Stored Procedures

Example 3 shows stored procedures.

Example 3   Stored Procedures

USE cudb_user_data;
DELIMITER //

DROP PROCEDURE IF EXISTS GET_AUCSUBS_COUNTERS // 
CREATE PROCEDURE GET_AUCSUBS_COUNTERS (OUT NSUBSCNT INT, 
OUT NGSUBSCNT INT, OUT NUSUBSCNT INT)

BEGIN

	SELECT COUNT(*) INTO NSUBSCNT FROM AuthIMSIData 
WHERE IMSI IS NOT NULL;

	SELECT COUNT(*) INTO NGSUBSCNT FROM AuthIMSIData 
WHERE AKATYPE = 0;

	SELECT COUNT(*) INTO NUSUBSCNT FROM AuthIMSIData 
WHERE AKATYPE = 1;

END //

DELIMITER ;

4.3.1   Best Practices in Writing Stored Procedures

Example 4   Optimizing Stored Procedures - 13 Queries

 SELECT COUNT(*) FROM CP6 WHERE AOC = 1;
 SELECT COUNT(*) FROM CP6 WHERE AOC = 2;
 SELECT COUNT(*) FROM CP6 WHERE HOLD IS NOT NULL;
 SELECT COUNT(*) FROM CP6 WHERE ICI IS NOT NULL;
 SELECT COUNT(*) FROM CP6 WHERE OIN IS NOT NULL;
 SELECT COUNT(*) FROM CP6 WHERE TIN IS NOT NULL;
 SELECT COUNT(*) FROM CP6 WHERE MPTY IS NOT NULL;
 SELECT COUNT(*) FROM CP6 WHERE CLIP IS NOT NULL;
 SELECT COUNT(*) FROM CP6 WHERE CLIR IS NOT NULL;
 SELECT COUNT(*) FROM CP6 WHERE COLP IS NOT NULL;
 SELECT COUNT(*) FROM CP6 WHERE COLR IS NOT NULL;
 SELECT COUNT(*) FROM CP6 WHERE OICK IS NOT NULL;
 SELECT COUNT(*) FROM CP6 WHERE TICK IS NOT NULL; 

Example 5   Optimizing Stored Procedures - Single SELECT Statement

 SELECT
 SUM(IF(AOC=1, 1,0)) as value1,
 SUM(IF(AOC=2, 1,0)) as value2,
 SUM(IF(HOLD IS NOT NULL, 1,0)) as value3,
 SUM(IF(ICI IS NOT NULL, 1,0)) as value4,
 SUM(IF(OIN IS NOT NULL, 1,0)) as value5,
 SUM(IF(TIN IS NOT NULL, 1,0)) as value6,
 SUM(IF(MPTY IS NOT NULL, 1,0)) as value7,
 SUM(IF(CLIP IS NOT NULL, 1,0)) as value8,
 SUM(IF(CLIR IS NOT NULL, 1,0)) as value9,
 SUM(IF(COLP IS NOT NULL, 1,0)) as value10,
 SUM(IF(COLR IS NOT NULL, 1,0)) as value11,
 SUM(IF(OICK IS NOT NULL, 1,0)) as value12,
 SUM(IF(TICK IS NOT NULL, 1,0)) as value13
 FROM CP6 WHERE AOC=1 OR AOC=2 OR HOLD IS NOT NULL OR ICI IS NOT NULL OR OIN IS NOT NULL OR TIN IS NOT NULL OR MPTY IS NOT ⇒
 NULL OR CLIP IS NOT NULL OR CLIR IS NOT NULL OR COLP IS NOT NULL OR COLR IS NOT NULL OR OICK IS NOT NULL OR TICK IS NOT NULL;

Example 6   Optimizing Queries Using IN Operator

Instead of:
 
 SELECT COUNT(*) INTO NACTSUBSCNT FROM CP2 WHERE CSLOC = 0 OR
                 CSLOC = 3 OR
                 CSLOC = 4 OR
                 (CSLOC = 5 AND ( DATE_SUB(NOW(), INTERVAL INTERVAL_DAY DAY) < STR_TO_DATE(hexStr2decStr(HEX(PURGEDATECS)),\
'%y%m%d') ) ) OR
                 PSLOC = 0 OR
                 PSLOC = 3 OR
                 PSLOC = 4 OR
                 (PSLOC = 5 AND ( DATE_SUB(NOW(), INTERVAL INTERVAL_DAY DAY) < STR_TO_DATE(hexStr2decStr(HEX(PURGEDATEPS)),\
'%y%m%d') ) );
 
when possible use:
 
 SELECT COUNT(*) INTO NACTSUBSCNT FROM CP2
         WHERE CSLOC IN (0,3,4) OR
         (CSLOC = 5 AND ( DATE_SUB(NOW(), INTERVAL INTERVAL_DAY DAY) < STR_TO_DATE(hexStr2decStr(HEX(PURGEDATECS)),'%y%m%d')\
 ) ) OR
         PSLOC IN (0,3,4) OR
         (PSLOC = 5 AND ( DATE_SUB(NOW(), INTERVAL INTERVAL_DAY DAY) < STR_TO_DATE(hexStr2decStr(HEX(PURGEDATEPS)),'%y%m%d')\
 ) );

4.4   ESA Configuration

This section provide examples of ESA configuration.

4.4.1   Scripts to Fetch Counters

Example 7 shows a sample script to fetch counters.

Example 7   A Script to Fetch Counters

#!/bin/bash

. /home/cudb/oam/performanceMgmt/appCounters/scripts/appCountersLib.sh

QUERY="select NSUBSCNT,NGSUBSCNT,NUSUBSCNT from GRP_AUCSUBS;"

#DEBUG=1
mysql_query "$QUERY"

# Simple solution is to exit on failure
# More critical counters could follow a retry policy 
if [ $? != 0 ]
then
  logging_system 'error' "Query \"$QUERY\" failed with \"$ROWS\""
  exit 1
fi

GROUPID=AUCSUBS 
COUNTERID=AUCSUBS
format_counter $GROUPID $COUNTERID

exit $?

4.4.2   Configuring ESA to Use Scripts

Example 8 shows configuring ESA to use scripts.

Example 8   Configuring ESA to Use Scripts

<?xml version="1.0" encoding="UTF-8"?>
<pmCntGroup xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns="http://www.ericsson.com/esa"
xsi:schemaLocation="http://www.ericsson.com/esa pmCounter.xsd"
active="yes">
  <identification>
    <groupId>AUCSUBS</groupId>
  </identification>
  <description>
    <groupDescr>AUC application subscriber counters</groupDescr>
    <groupInfo>These counters permits evaluate the use of 
certain functions of the AUC application</groupInfo>
  </description>
  <!--Counter collection with data source SCRIPT-->
  <!-- G1-->
  
  <cntCollection active="yes" activeSnmp="yes">
    <identification>
      <collectionId>AUCSUBS</collectionId>
    </identification>
    <cntDefinition cntType="Gauge">
      <identification>
        <counterId>NSUBSCNT
		</counterId>
      </identification>
      <description>
        <counterDescr>Number of subscribers</counterDescr>
        <counterInfo/>
      </description>
    </cntDefinition>
    <cntDefinition cntType="Gauge">
      <identification>
        <counterId>NGSUBSCNT</counterId>
      </identification>
      <description>
        <counterDescr>Number of GSM subscribers</counterDescr>
        <counterInfo/>
      </description>
    </cntDefinition>
    <cntDefinition cntType="Gauge">
      <identification>
        <counterId>NUSUBSCNT</counterId>
      </identification>
      <description>
        <counterDescr>Number of UMTS subscribers</counterDescr>
        <counterInfo/>
      </description>
    </cntDefinition>
    <dataSource interval="900">
      <script>
	  <location>/cluster/home/cudb/oam/performanceMgmt/
appCounters/scripts/APCount_AUCSUBS.sh</location>
      </script>
    </dataSource>
  </cntCollection>
</pmCntGroup>

4.5   Configuring Cron File: appCounters.cron

Example 9 shows the configurable part of a sample appCounters.cron file.

Example 9   appCounters.cron File

#!/bin/bash -l
# -------------------------------------------------------------
# ----- ONLY THIS PART OF THE SOURCE HAS TO BE MODIFIED  -----
# -------------------------------------------------------------

LIST_APP_COUNTERS[0]="/opt/ericsson/cudb/OAM/bin/\
cudbApplicationCounters \
-C /cluster/home/cudb/oam/performanceMgmt/appCounters/config/\
<app_counter1>.conf -U 1 -u LOCAL1"

LIST_APP_COUNTERS[1]="/opt/ericsson/cudb/OAM/bin/\
cudbApplicationCounters \
-C /cluster/home/cudb/oam/performanceMgmt/appCounters/config/\
<app_counter2>.conf -U 2 -u LOCAL1"

LIST_APP_COUNTERS[2]="/opt/ericsson/cudb/OAM/bin/\
cudbApplicationCounters \
-C /cluster/home/cudb/oam/performanceMgmt/appCounters/config/\
<app_counter3>.conf -U 3 -u LOCAL1"

LIST_APP_COUNTERS[3]="/opt/ericsson/cudb/OAM/bin/\
cudbApplicationCounters \
-C /cluster/home/cudb/oam/performanceMgmt/appCounters/config/\
<app_counter4>.conf -U 4 -u LOCAL1"
# -------------------------------------------------------------

Glossary

For the terms, definitions, acronyms and abbreviations used in this document, refer to CUDB Glossary of Terms and Acronyms, Reference [6].


Reference List

CUDB Documents
[1] CUDB Users and Passwords, 3/00651-HDA 104 03/10
[2] Application Counters, Fault In Subscriber Statistic Application.
[3] CUDB Performance Guide.
[4] CUDB Node Commands and Parameters.
[5] CUDB Node Logging Events.
[6] CUDB Glossary of Terms and Acronyms.
[7] CUDB LDAP Data Views Management.
Other Ericsson Documents
[8] ESA Performance Management.


Copyright

© Ericsson AB 2016, 2017. All rights reserved. No part of this document may be reproduced in any form without the written permission of the copyright owner.

Disclaimer

The contents of this document are subject to revision without notice due to continued progress in methodology, design and manufacturing. Ericsson shall have no liability for any error or damage of any kind resulting from the use of this document.

Trademark List
All trademarks mentioned herein are the property of their respective owners. These are shown in the document Trademark Information.

    CUDB Application Counters