This document gives an overview of the Cisco Unified Customer Voice Portal (CVP) reporting server and offers troubleshooting steps.
CVP tables are categorized as:
Calls start at the Call table and are linked to the VXMLSession table by the CallGUID column.
The unified CVP reporting server includes a summary process that aggregates data from the Call and VXMLElement tables into new summary tables.
The reporting summary tables are:
The tables are created based upon this schedule:
See Cisco Bug ID CSCue65248, "CVP Reporting summary tables are not populated." In the CVP reporting server, the summary tables are not populated. The issue is caused by the script for monthly summary, which was introduced in CVP 9.0.
The unified CVP 9.0(1) reporting database is supported only on the Windows 2008 R2 server. Because the unified CVP 8.x reporting database is supported by Windows 2003, there is no direct update to the unified CVP 9.0(1) reporting database.
For migration instructions, see the installation guide. Note that:
Differences in post installation tasks include:
A key difference in users is that, with 9.x, there is no more Informix user. Instead, the cvp_dbadmin user is the owner of the database.
Cisco MCS-7845 reporting servers can handle 420 messages per second.
Use this equation in order to determine the number of reporting messages generated per second for each VoiceXML application:
A# = %CPS * CPS * MSG
where:
Use this equation in order to add the messages generated by each application:
A(total) = A1+ A2+?..+An
where A(total) is the total number of reporting messages generated per second by your VoiceXML applications.
The number of reporting messages per element or activity is in Table 17 in of the Cisco Unified Customer Voice Portal (CVP) Solution Reference Network Design (SRND) Release 9.0(1).
For simplicity, you can use this query in order to calculate the average number of messages written to the vxmlsession table for one second:
select count(*)/86400 from vxmlsession where dbdatetime between
'2012-12-12 00:00:00' and '2012-12-13 00:00:00'"
Run this query against these 14 tables:
Add the results in order to obtain the average number of messages per second received by the reporting server.
If the reporting server is overloaded, the reporting logs contain these alerts:
CVP_8_0_RPT-1-REPORTING_DB_ALERT_RAISE ALERT!!!!! The total JDBC messages queue
size has exceeded the critical limit 300000 .... All the JDBC messages will
be dropped. [id:4014]
CVP_8_0_RPT-1-REPORTING_DB_ALERT_RAISE ALERT!!!!! The total JDBC messages queue
size has exceeded the max limit 250000 .... Some of the JDBC messages may be
dropped. [id:4014]
There are several scenarios where the reporting server goes to Partial Service. However, Partial Service does not necessarily mean that there is a problem.
If the reporting server fails, messages destined for the reporting server are buffered by the Call Server, in memory, up to 200,000 messages. After that limit is reached, all new message detail information is dropped.
Take these steps in order to set the Number of Receive Buffers on the Reporting Server TCP settings to 4096 (max):
If the database connection fails, the reporting server sends out a Simple Network Management Protocol (SNMP) alert and starts to store messages to a persistent file (%CVP_HOME%\tmp\CVPReporting.tmp) up to a user-specified limit. During this time, the reporting server stays In Service.When 75% of the limit is reached, a warning is written to the log file. When 100% of the limit is reached, an SNMP alert is sent out, and the reporting server goes into Partial Service. Any new messages might be dropped.
When the database connection comes back up, the reporting server goes into recovery mode and changes its state to Partial Service (if it is not in that state already). It then starts to read messages from the %CVP_HOME%\tmp\CVPReporting.tmp file and to commit them to the database. Depending upon the size of the file, it may take hours to commit all of the data to the database. New messages that come in during recovery are buffered in memory.
There is, however, a limit to the number of messages that the reporting server can buffer, regardless of the mode or state of the server:
If a persistent file exists on startup, the reporting server stays in Partial Service and goes into recovery mode.
The reporting server can also go to Partial Service when it is recovering unfinished calls.
This message is seen in the reporting server logs:
%CVP_8_0_RPT-1-REPORTING_STATE_CHANGE: REPORTING Subsystem state changed to
RPT SS RPT1 changes its state to Partial Service cause Unfinished calls
recovery started [id:4001]
The logs also include information about recovery of these calls. Remember that the recovery process can take a long time!
%CVP_8_0_RPT-6-REPORTING_INFO: Recover Uncompleted call: 73
CallGUID:90DAAAC91000013C01075FC253EF37A4 Event Id: 11 CauseId: 0 [id:4000]
...
%CVP_8_0_RPT-6-REPORTING_INFO: Recover Uncompleted call:
129 CallGUID:673A58361000013C087A209E53EF37A5 Event Id: 0 CauseId: 0 [id:4000]
Once unfinished calls are completed, these messages are seen, and the reporting server goes back to In Service state:
%CVP_8_0_RPT-6-REPORTING_INFO: Recover CallRegistry finished [id:4000]
%CVP_8_0_RPT-6-REPORTING_INFO: initKeepAliver() -- processed unfinished calls
[id:4000]
%CVP_8_0_RPT-1-REPORTING_STATE_CHANGE: REPORTING Subsystem state changed to RPT
SS RPT1 changes its state to In Service cause Normal Operation [id:4001]
You can remove the %CVP_HOME%\tmp\CVPReporting.tmp file in order to avoid the recovery process and bring the reporting server back in service. This procedure describes how to bypass the recovery process:
See Cisco Bug ID CSCtu43570, "CVPReporting.tmp grows beyond size limit and is not timely recovered." New call reporting data was lost because the file could not be completely read in. The hard drive was filling up, which eventually caused an 'out of disk space' condition.
This issue was fixed in the Unified CVP 8.5(1)SR18 and 8.5(1)SR6 reporting database.
Edit the <install_dir>\Cisco\CVP\conf\reporting.properties file in order to set the trace level in the reporting server logs. This is an example:
RPT.traceMask = 0x810000
RPT.logLevel = DEBUG
The summaries use two tables in the ciscoadmin database: agg_schedule and agg_statements.
The <CVP_HOME>\logs\reporting.txt file shows whether aggregation has run.
This procedure describes how to enable additional tracing for the aggregator.bat job:
echo call sp_sched_agg(); | dbaccess ciscoadmin
to:
echo call sp_sched_agg('D'); | dbaccess ciscoadmin
Debug logs are written in the CVP_HOME>\logs\Agg_Debug.out file.
This procedure describes the troubleshooting process:
call upg_est(); UNLOAD to "c:/temp/upgvars.out" SELECT estimate1,estimate2,
retention,log_space_needed,minlog,maxlog FROM cvp_data:upg_estimate;
23:41:54 Wed Dec 19 2012 : dbaccess cvp_data
C:\Cisco\CVP\informix_frag\upg_est.sql
Database selected.
312: Cannot update system catalog (sysprocbody).
131: ISAM error: no free disk space
Error in line 26
Near character position 11
23:41:54 Wed Dec 19 2012 : dbaccess cvp_data c:/temp/cvpupg.sql 2>NUL
Database selected.
206: The specified table (upg_estimate) is not in the database.
SELECT COUNT(*)But, this table does not get created.
INTO tmp_int
FROM systables
WHERE tabname='upg_estimate';
IF tmp_int=0 THEN
CREATE TABLE upg_estimate (
estimate1 INTERVAL HOUR TO MINUTE,
estimate2 INTERVAL HOUR TO MINUTE,
retention SMALLINT,
log_space_needed INTEGER,
minlog INTEGER,
maxlog INTEGER
);
SELECT COUNT(*) FROM systables WHERE tabname='upg_estimate';The query returns 0, so the table should have been created.
CREATE TABLE upg_estimate (You receive the error message:
estimate1 INTERVAL HOUR TO MINUTE,
estimate2 INTERVAL HOUR TO MINUTE,
retention SMALLINT,
log_space_needed INTEGER,
minlog INTEGER,
maxlog INTEGER
);
261: Cannot Create file for table (informix.upg_estimate).
131: ISAM error: no free disk space
onspaces -a cvp_data_dbspace -
E:\ifmxdata\cvp_db_wp17cvprpt1a\cvp_data_dbspace\new_space -o 0 -s 10240
This command adds 100 MB of dbspace to the CVP Informix server.
This example shows how to connect to the database with DBAccess:
Revision | Publish Date | Comments |
---|---|---|
1.0 |
30-Sep-2013 |
Initial Release |