CSCvb27878 :: UCCX/CUIC: Overflow occurred on a datetime or interval operation

[Saurabh Agnihotri]

Problem Description

When CUIC Agent Report is run, the agent report fails with the error that contains the following line:

Overflow occurred on a datetime or interval operation

 Complete Error will be similar to as as follows:

com.cisco.ccbu.cuic.businesslogic.datasource.CuicDbException: DbException: 
CuicDataSourceServiceManagerImpl.getDataSet() { Nested SQLException; 
SQLState: IX000 Vendor code: -1265 Message: Overflow occurred on a 
datetime or interval operation. } at 
com.cisco.ccbu.cuic.businesslogic.datasource.CuicDataSourceServiceManagerImpl.getDataSetBuilder(CuicDataSourceServiceManagerImpl.java:952) 
at 
com.cisco.ccbu.cuic.businesslogic.engine.CuicReportEngineWorker.executeQueryUsingDatasetBuilder(CuicReportEngineWorker.java:80) 
at 
com.cisco.ccbu.cuic.businesslogic.engine.CuicReportEngineWorker.runReport(CuicReportEngineWorker.java:37) 
at 
com.cisco.ccbu.cuic.businesslogic.enginebase.Worker.run(Worker.java:329) 
at 
com.cisco.ccbu.infra.threads.InstrumentedRunnable.run(InstrumentedRunnable.java:92) 
at 
java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) 
at 
java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) 
at java.lang.Thread.run(Thread.java:724) at 
com.cisco.ccbu.infra.threads.ThreadPoolThread.run(ThreadPoolThread.java:164) 
Caused by: java.sql.SQLException: Overflow occurred on a datetime or 
interval operation. at 
com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:413) at 
com.informix.jdbc.IfxSqli.a(IfxSqli.java:3647) at 
com.informix.jdbc.IfxSqli.E(IfxSqli.java:3974) at 
com.informix.jdbc.IfxSqli.dispatchMsg(IfxSqli.java:2695) at 
com.informix.jdbc.IfxSqli.receiveMessage(IfxSqli.java:2611) at 
com.informix.jdbc.IfxSqli.a(IfxSqli.java:1830) at 
com.informix.jdbc.IfxSqli.executeStatementQuery(IfxSqli.java:1768) at 
com.informix.jdbc.IfxSqli.executeStatementQuery(IfxSqli.java:1699) at 
com.informix.jdbc.IfxResultSet.a(IfxResultSet.java:210) at
com.informix.jdbc.IfxStatement.executeQueryImpl(IfxStatement.java:1237) at 
com.informix.jdbc.IfxPreparedStatement.executeQuery(IfxPreparedStatement.java:401) 
at 
com.informix.jdbc.IfxCallableStatement.executeQuery(IfxCallableStatement.java:241) 
at 
com.mchange.v2.c3p0.impl.NewProxyCallableStatement.executeQuery(NewProxyCallableStatement.java:2131) 
at 
com.cisco.ccbu.cuic.businesslogic.datasource.CuicDataSourceServiceManagerImpl.executeAndLoadDataset(CuicDataSourceServiceManagerImpl.java:1072) 
at 
com.cisco.ccbu.cuic.businesslogic.datasource.CuicDataSourceServiceManagerImpl.getDataSetBuilder(CuicDataSourceServiceManagerImpl.java:938) 
... 8 more Caused by: java.sql.SQLException at 
com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:413) at 
com.informix.jdbc.IfxSqli.E(IfxSqli.java:3979) ... 20 more 

So far, the issue has been reported on UCCX Versions 11.X, 10.X, 9.X, 8.X and can potentially impact HRC reporting as well.

The issue is filed as a defect already:

CSCvb27878   UCCX/CUIC: Overflow occurred on a datetime or interval operation

Since the defect is newly filed, it may not be visible externally at the moment. However, the fix for the defect is already available and can be installed on the affected UCCX servers without requiring any downtime.

The fix for the defect involves installing "ciscouccx.ReportFix.10.cop.sgn" file on both the UCCX Nodes. Installation of this file does not require any service restart and hence can be performed without requiring any reboot.

This should resolve your CUIC reporting issue mentioned above.

Frequently Asked Questions

Q. I have a UCCX HA environment. Do I need to install the cop file on both the nodes? 

Ans: Yes, you need to install the cop file on both the nodes.

Q. Does the installation of this cop file require any downtime or does it have any impact on service?

Ans: The cop file installation does not require any restart of service or reboot of server. Thus, it does not require any downtime and does not have any impact on service.

Q. Are there different cop files for different UCCX versions?

Ans: No, the same cop file work for any UCCX version equal to or higher than 8.x

Q. Can this cop file be installed on ES or SU version of UCCX?

Ans: Yes, it can be installed on ES or SU versions too.

Q. Can I install this cop file from GUI?

Ans: No. This cop file installation is only supported when done through the UCCX command line interface (CLI).

Q. Is there any UCCX Version available that has the fix for the defect CSCvb27878?

Ans: There is no UCCX version available at the moment that has the fix for the defect: CSCvb27878

Hope this helps!

Regards,

Saurabh

[virikh_av]

Hi Saurabh,

Thanks for the help!  After the update it fixed the reporting issue.

Many Thanks!

[Saurabh Agnihotri]

Glad to hear your issue is resolved. 

Please rate the blog if you found it useful.

Regards,

Saurabh

[aan.riyadi]

Hi Saurabh, thank you very much for publishing this article. Deepak thanks for informing the tool.

Please find attached file as complementary of Saurabh guidance. hope this help everyone.

Regards

Aan

[Kiewit Support Services]

We had this same error on our setup System version: 10.6.1.11002-15.  We uploaded the file though the UCCX OS Administration, on the GUI.  This was using the FreeFTPd software and setting it to transfer with FTP protocol from my desktop.  See screenshot for image of UCCX setup. Thank you!

[chandra_827]

The two tools that i always use is portable MiniSFTP or 3CDeamon. These two tools work like charm....I have done multiple upgrades and installs and never had issues. Did anyone saw other issues coming due to install of this COP file?? Is everything working fine post this COP file install?

[chandra_827]

Hi Saurabh,

Do you know if we miss any of the reports from the day we see this error till the date we apply the COP file? Did anyone had such occurrence ? I get this doubt due to the logs showing DBException error.

[Brian Houston]

Hi Sauraubh

I've had three customers running UCCX 8.5.1 report this issue in recent months and in each case I succesfully installed the COP file. In each case, this resolved the problem.

Now one of these customers is reporting that the identical problem has recurred. I only installed the COP file for this customer in January. He has provided a screenshot and its the same error as before - "A runtime error occurred while executing the query. Please check the log for details"

Are you aware of the problem recurring elsewhere or is it possible that we are looking at a new bug?

Regards

Brian Houston

[Saurabh Agnihotri]

Hi Brian,

We haven't heard of this issue recurring on its own. However, recently I have heard of a few instances where the upgrade to another uccx version may possibly require re-applying the same cop file.

Was there any upgrade performed on the UCCX server since you last applied the cop file on the UCCX server ? If yes, then you may want to reapply the cop file.

Also, before we assume that it is the same defect you are running into, please look into the error details and confirm that you are seeing this specific error:

Nested SQLException; 
SQLState: IX000 Vendor code: -1265 Message: Overflow occurred on a 
datetime or interval operation.

Hope this helps!

[chandra_827]

Hi Brian,

Please check ifor the cop file is listed in the installed files under Show version active. And may give a try with reboot if it is listed or you can reinstall.