8 Replies Latest reply: Mar 1, 2012 3:14 AM by 787689 RSS

    CCA Data Retention not working

    787689
      Hi all,

      I've a problem with "Data Retention" feature not working in CCA.
      Even if I set the "Number of Days to retain data before compressing / deleting" for "Interval Statistics", "Interaction Details" and "Recording", nothing happens... and "Last Day/Time Event Completed (GMT)" field shows no data...
      My "Data Retention" Policy is set to 180 Day, but I can find data of more two year old!

      Thanks
      Giuseppe.
        • 1. Re: CCA Data Retention not working
          rschepis
          There is a defect open on this in FP5 you can view in MyOracleSupport. What version are you on?
          • 2. Re: CCA Data Retention not working
            787689
            Hi Roman!

            I'm on FP6, but this features never worked, not even with other previous FP.
            Tnx
            • 3. Re: CCA Data Retention not working
              916570
              In the custom.xml file under the TAW tree (TAW/custom/custom.xml), add this:

              <application
              className="DataRetentionThread"
              package="com.taw.web.service.bean">
              </application>

              T
              • 4. Re: CCA Data Retention not working
                787689
                Hi,

                thanks for your suggestions, but my custom.xml file already contains that instructions...:


                <?xml version="1.0" encoding="ISO-8859-1"?>
                <!-- Insert the ASP user in the database -->

                <?xml-stylesheet type="text/xsl" href="custom.xsl" ?>

                <!DOCTYPE custom-data [
                <!-- DTD for "skills.xml". -->

                <!ELEMENT system-data (applications)>

                <!ELEMENT applications (application*)>

                <!ELEMENT application (parameter*)>
                <!ELEMENT parameter (#PCDATA)>

                <!ATTLIST application
                className CDATA #REQUIRED
                package CDATA #REQUIRED
                >

                <!ATTLIST parameter
                name CDATA #REQUIRED
                vaule CDATA #REQUIRED
                >
                ]>

                <custom-data>

                <applications>

                <!-- Use to re-deploy the web.xml file -->
                <application
                className="DeployWebXML"
                package="com.taw.custom"
                >
                </application>

                <!-- first custom application
                <application
                className="BluePumpkin"
                package="com.taw.custom"
                >
                <parameter
                name="periodDelay"
                value="4"
                />
                <parameter
                name="delayBetweenUpdate"
                value="1800"
                />
                </application>

                -->

                <!-- another custom application (example)
                <application
                className="SampleApplication"
                package="com.taw.custom"
                >
                <parameter
                name="someParameter"
                value="theParameterValue"
                />
                </application>

                -->

                <application
                className=“DataRetentionThread”
                package=“com.taw.web.service.bean”
                >
                </application>


                </applications>

                </custom-data>
                • 5. Re: CCA Data Retention not working
                  787689
                  Hi All,

                  I've found an error in the custom.xml !
                  As manual says I added the DataRetention lines to the custom.xml file. I edited it with notepad, inserting the new lines (or pasting them...).

                  The "Data Retention" web page (and db.dataretentionconfiguration table) never showed "Last Day/Time Event Completed", nor the CallCenterAnywhere.log file reported this event.

                  Today I opened the custom.xml file with VIM (a text editor like that for linux) and I noticed that the quotes (") were not displayed correctly... After pasting them from other lines, saving and restarting the web application DataRetention seems working !!! (the page displays the "Last Day/Time Event Completed", but I have yet to verify this).
                  • 6. Re: CCA Data Retention not working
                    787689
                    Hi,

                    It seems now that it's starting but not working as expected...:

                    the "Data Retention" web page now shows "Last Day/Time Event Completed" (ad dbo.dataretentionconfiguration table too...), but data is not purged and I can query for interactions prior to "the number of days to retain data before deleting"...

                    Here's an extraction of the CallCenterAnywhere.log where I can see some errors:
                    N.B.: I found this related knowledge:
                    https://support.oracle.com/CSP/ui/flash.html#tab=KBHome(page=KBHome&id=()),(page=KBNavigator&id=(bmDocDsrc=KB&bmDocID=1338559.1&from=BOOKMARK&viewingMode=1143&bmDocTitle=java.sql.SQLException:%20%5BFMWGEN%5D%5BSQLServer%20JDBC%20Driver%5D%5BSQLServer%5DServer%20failed%20to%20resume%20the%20transaction&bmDocType=PROBLEM))

                    ...
                    24 feb 2012 02:00:26,966 [DataRetentionThread] ERROR DataRetentionTest.processPurgeInteractionStats(DataRetentionTest.java:169) -
                    java.sql.SQLException: [BEA][SQLServer JDBC Driver][SQLServer]The server failed to resume the transaction. Desc:5700000001.
                         at weblogic.jdbc.base.BaseExceptions.createException(Unknown Source)
                         at weblogic.jdbc.base.BaseExceptions.getException(Unknown Source)
                         at weblogic.jdbc.sqlserver.tds.TDSRequest.processErrorToken(Unknown Source)
                         at weblogic.jdbc.sqlserver.tds.TDSRequest.processReplyToken(Unknown Source)
                         at weblogic.jdbc.sqlserver.tds.TDSRPCRequest.processReplyToken(Unknown Source)
                         at weblogic.jdbc.sqlserver.tds.TDSRequest.processReply(Unknown Source)
                         at weblogic.jdbc.sqlserver.SQLServerImplStatement.getNextResultType(Unknown Source)
                         at weblogic.jdbc.base.BaseStatement.commonTransitionToState(Unknown Source)
                         at weblogic.jdbc.base.BaseStatement.postImplExecute(Unknown Source)
                         at weblogic.jdbc.base.BasePreparedStatement.postImplExecute(Unknown Source)
                         at weblogic.jdbc.base.BaseStatement.commonExecute(Unknown Source)
                         at weblogic.jdbc.base.BaseStatement.executeInternal(Unknown Source)
                         at weblogic.jdbc.base.BasePreparedStatement.execute(Unknown Source)
                         at weblogic.jdbc.wrapper.PreparedStatement.execute(PreparedStatement.java:98)
                         at com.taw.web.service.bean.DataRetentionTest.processPurgeInteractionStats(DataRetentionTest.java:157)
                         at com.taw.web.service.bean.DataRetentionTest.processDataRetention(DataRetentionTest.java:73)
                         at com.taw.web.service.bean.DataRetentionTest.processRequest(DataRetentionTest.java:41)
                         at com.taw.web.service.bean.DataRetentionThread.processMessage(DataRetentionThread.java:41)
                         at com.taw.web.service.bean.DataRetentionThread.access$000(DataRetentionThread.java:11)
                         at com.taw.web.service.bean.DataRetentionThread$1.run(DataRetentionThread.java:32)
                         at java.lang.Thread.run(Thread.java:619)
                    ...
                    24 feb 2012 02:00:26,966 [DataRetentionThread] ERROR DataRetentionTest.processDataRetention(DataRetentionTest.java:91) -
                    java.lang.NullPointerException
                         at com.taw.web.service.bean.DataRetentionTest.processPurgeInteractionStats(DataRetentionTest.java:172)
                         at com.taw.web.service.bean.DataRetentionTest.processDataRetention(DataRetentionTest.java:73)
                         at com.taw.web.service.bean.DataRetentionTest.processRequest(DataRetentionTest.java:41)
                         at com.taw.web.service.bean.DataRetentionThread.processMessage(DataRetentionThread.java:41)
                         at com.taw.web.service.bean.DataRetentionThread.access$000(DataRetentionThread.java:11)
                         at com.taw.web.service.bean.DataRetentionThread$1.run(DataRetentionThread.java:32)
                         at java.lang.Thread.run(Thread.java:619)
                    24 feb 2012 02:00:26,966 [DataRetentionThread] DEBUG DataRetentionTest.processDataRetention(DataRetentionTest.java:62) - Processing DataRetention [command [] subCommand [38904] companies [[
                    ...
                    24 feb 2012 02:00:30,435 [DataRetentionThread] ERROR Revision: 14 .pingDB(DatabaseService.java:69) - pinging db: TAW_PRIVATE_DATASOURCE
                    24 feb 2012 02:00:30,528 [DataRetentionThread] DEBUG Revision: 14 .getDBConnection(DatabaseService.java:501) - got connection successfully
                    24 feb 2012 02:00:30,575 [DataRetentionThread] ERROR Revision: 10 .executeQuery(DatabaseConnections.java:231) - java.sql.SQLException: [BEA][SQLServer JDBC Driver][SQLServer]The server failed to resume the transaction. Desc:5500000002.
                    24 feb 2012 02:00:30,575 [DataRetentionThread] ERROR FtpRemoteRecordingsArchiver Revision: 15 .transferFilesByType(FtpRemoteRecordingsArchiver.java:86) - No remote ftp storage defined. Aborting media files transfer process!
                    24 feb 2012 02:00:30,575 [DataRetentionThread] INFO FtpRemoteRecordingsArchiver Revision: 15 .transferAllFiles(FtpRemoteRecordingsArchiver.java:69) - Historical media files transfer ended.
                    24 feb 2012 02:00:30,607 [DataRetentionThread] DEBUG Revision: 14 .getDBConnection(DatabaseService.java:501) - got connection successfully
                    24 feb 2012 02:00:30,638 [DataRetentionThread] ERROR Revision: 10 .executeQuery(DatabaseConnections.java:231) - java.sql.SQLException: [BEA][SQLServer JDBC Driver][SQLServer]The server failed to resume the transaction. Desc:5300000002.
                    24 feb 2012 02:00:30,638 [DataRetentionThread] ERROR FtpRemoteRecordingsArchiver Revision: 15 .transferFilesByType(FtpRemoteRecordingsArchiver.java:86) - No remote ftp storage defined. Aborting media files transfer process!
                    24 feb 2012 02:00:30,638 [DataRetentionThread] INFO FtpRemoteRecordingsArchiver Revision: 15 .transferAllFiles(FtpRemoteRecordingsArchiver.java:74) - Quality control media files transfer ended.
                    ...
                    • 7. Re: CCA Data Retention not working
                      787689
                      DataRetention now starting but not working...
                      • 8. Re: CCA Data Retention not working
                        787689
                        I found some articles in which other people have had a similar error with jdbc driver ("[SQLServer JDBC Driver][SQLServer]The server failed to resume the transaction.").

                        It seems that this version of JDBC driver doesn't allow a transaction within a stored procedure.
                        Indeed there are transactions within stored procedures used by dataretention functionality !

                        Which version of JDBC driver is present in CCAv8.1.3 FP6 / BEA Weblogic 10.3.0 ? Is there an update to try?

                        Here some useful links:

                        http://www-01.ibm.com/support/docview.wss?uid=swg1PK64012

                        http://www.ureader.com/msg/1148431.aspx

                        http://connect.microsoft.com/SQLServer/feedback/details/254121/setmaxrows-causes-server-failed-to-resume-the-transaction-on-sproc-with-nested-transaction

                        http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_24251766.html

                        http://social.msdn.microsoft.com/Forums/en/sqldataaccess/thread/ba79df62-3ac8-4c43-bfb4-c75793314ad4

                        http://blogs.msdn.com/b/jdbcteam/archive/2009/02/24/the-server-failed-to-resume-the-transaction-why.aspx