FAQs and tips for troubleshooting

Question #1: DWH tasks

How can I delete tasks from the DWH Tasks list in the Admin Tool when the tasks have been performed but are still visible in the list? How do I know if the tasks are really finished?

The task is deleted from the "current task" list automatically when the processing in CMRF is finished successfully.

You can monitor the status of processing for each task in the tasks view in the Admin Tool (navigation group Data Warehouse, navigation item Administration). It is finished when Status CMRF is SUCCESS or ERROR.

select cmas_dwh_task.id, cmas_dwh_task.status, cmas_dwh_synchronization.dwh_status, cmas_dwh_synchronization.cmrf_status from cmas_dwh_task left join cmas_dwh_synchronization on cmas_dwh_task.id=cmas_dwh_synchronization.reference_id;

There is a Delete button available in the Admin Tool. When should/can I use it?

Delete can be used if operation is not started yet (status: NEW). It is not available for active (ACTIVE), paused (PAUSED) or finished (SUCCESS, ERROR) operations.

For example, if someone accidentally started two transfers, the first one is processed and cannot be deleted, but the second one can be deleted.

Question #2: DWH update time

On large installations, a DWH update can take quite long. What can I do to reduce the time?

Prior to version 6.11, the runtime can be optimize via the CM system property cmas-dwh-server, batch-commit-interval. The default value is 10. We recommend to start with a value of 150. However, the optimum chunk size depends on the system performance. If the system has enough RAM, the value can be 1000 or even more. This will speed up the transfer considerably.

With version 6.11 the DWH transfer and DWH update options the CM package size and CMRF cache size are introduced. For recommendations see section Parameters for Transfer and Update Operations in chapter Data Warehouse (DWH) Management of the ConSol CM Administrator Manual.

When the SQL queries are especially show the following messages can be found in the log files:

Special case Oracle

Especially during large data imports when the tables size is significantly extended or during the transfer when the DWH tables are empty, there can be SQL performance problems in Oracle due to stale or missing table statistics. Also when the DWH update is not normally used, the DWH update time can also be significantly degraded.

It is likely that the runtimes of the actions are significantly reduced by forcing an update of the table statistics on the whole CM and CMRF schema. For example, you can use the following command for the schema CM6:

exec DBMS_STATS.GATHER_SCHEMA_STATS('CM6', OPTIONS => 'GATHER')

With the standard Oracles statistics setting, this creates histograms statistics which are needed for the specific DWH update SQLs, which can let the optimizer find a better execution plan. You may need to repeat several times, as the different objects have different SQLs. If the data volume is very large, this can still take several hours.

Question #3: Performance issues during fixing DWH live mode

The DWH update after an abort can take very long. Can it be speed up?

Aborts of the DWH live mode with the error message An error occurred. Update is needed. Live packages will be processed again after update. can be fixed by a DWH update. When the last DWH update was months ago or never this update can take days or even weeks.

To prevent such long DWH update times in case of unexpected errors, it is recommended to regularly execute the DWH update, e.g. every night or a least once a week. The DWH update always retransmits all changes since the last DWH update or transfer. This can be done as described in section DWH Synchronization During System Operation in the ConSol CM Administrator Manual.

You can see when the last change was transferred via the Admin Tool. Open the task view in the Admin Tool (navigation group Data Warehouse, navigation item Administration) and check the date in the End column of the newest task with type "UPDATE" or "TRANSFER". Alternatively you can use monitoring as described in section CMRF / DWH monitoring.

The advanced configuration can be used to speed up the update. But this can also lead to a corrupt DWH. If you want to use this feature, you must consult a ConSol representative to check if this is really applicable for your situation.

Question #4: Live mode specialties

When the DWH had to be recovered from a full back-up by a database administrator, how can I restart the automatic transfer in live mode without starting with reinitialization and full transfer?

Probably it will cause an ERROR in the log file: An error occurred. Update is needed. Live packages will be processed again after update. and many WARNINGs like this: Update is needed. Live packages will be processed again after update.('from' date must be equal or lesser than X, 'to' date must be equal or greater than Y.

Solution:

  1. Execute the following command:

    update cmas_dwh_synchronization set cmrf_status='ERROR' where to_date > X;

  2. Run the update (Update button in the Admin Tool).

Question #5: Manual submission of tasks in live mode

When the option Automatic commit of administrative changes is not active and I submit a task manually (using the GUI of the Admin Tool), which consequences does this have in live mode?

A task is created automatically when a configuration change has been submitted (e.g. set a ticket field from reportable = false to reportable = true). A new entry is created in the table cmas_dwh_task, and the task is displayed in the list of DWH tasks in the Admin Tool. When you click on Run task in the Admin Tool, a new entry is created in the table cmas_dwh_synchronization. The DWH transfer service reads the data from cmas_dwh_synchronization and writes chunks into INT_TRANSFER_QUEUE in the DWH. The CMRF service reads this data and also reads the live data from INT_LIVE_QUEUE. Since for CMRF, the TRANSFER data have a higher priority than the LIVE data, the TRANSFER data is processed first, i.e. the new data which results from the configuration change is processed first. Then the LIVE data processing is continued.

Question #6: RecoverableExceptions

How can I prevent the transfer to stop when an error occurs?

The property recoverable.exections basically takes a comma-separated list of Java classes as value. All exceptions which are mentioned in the list will not cause the abortion of the DWH transfer, but CM/CMRF will continue the transfer and ignore the respective exceptions. As one value (or even the only value) in the list, you can provide the Java class name of an exception and add a '+' at the end. Then all exceptions which inherit from this class will be ignored. Example: java.lang.Exception+ . In this case, all exceptions will be ignored, because java.lang.Exception is the parent class of all exceptions in Java. A regular expression can be provided as optional parameter. The RegEx is applied to the message of the exception as filter.

The following characters need to be escaped in RegEx:

  • , -> \,
  • : -> \:
  • \ -> \\

Example value: java.sql.SQLRecoverableException,java.lang.RuntimeException+:.*T.{1\,2}T.*

Please note that the value for recoverable exceptions has to be set at two locations:

You can also set a large number of retries.

Question #7: Debug level for CMRF

How can I increase the debug level for CMRF during troubleshooting?

The log level is determined in the CMRF configuration file, <JBOSS_CMRF_HOME>/standalone/configuration/cmrf-<DB>.xml:

<logger category="com.consol.cmrf">

<level name="${logger.com.consol.cmrf.level:INFO}"/>

<handlers>

<handler name="CMRF_FILE"/>

<handler name="ERROR_FILE"/>

</handlers>

</logger>

It can be changed in the CM/CMRF properties file cm6-cmrf-config.properties or cmrf-config.properties. For example, you can change the level from the default INFO to DEBUG to get more information. Add an entry logger.com.consol.cmrf.level=DEBUG to the properties file. Please see Configuring the log files in JBoss for details.

Question #8: DWH database size is increasing faster than CM database using Oracle databases

By initialize with the option delete existing data, the existing DWH tables are deleted and recreated. In Oracle by default the tables are not deleted, but kept in the recycle bin.

To free the data space you must purge the deleted tables from the recycle bin. For details consult the Oracle database documentation. You can also disable recycle bin for the whole Oracle database instance, but than you lose the ability to recover easily from tables dropped unintentionally.

Question #9: CMRF is not further updated after database connection failure

The live mode is not updating anymore, after a database connection error has been logged. How can I resume the live mode?

The live mode is broken. You need to restart the CMRF, either by restarting the application server or just restarting the service via the MBean, and perform a DWH update to re-enable the live mode.

A DWH task is not updating DWH table anymore, after a database connection error has been logged. How can I resume the live mode?

In the task view the CM Progress is still working, but the CMRF Progress is not changing anymore.

After restarting the CMRF, either by restarting the application or just restarting the service via the MBean, the task processing is resumed. The processing cannot be resumed by clicking the button Resume in the Admin Tool.

Question #10: Report SQLs on DWH which have been working before throw SQL errors or fields are empty

Users who receive or use reports based on the DWH database complain that fields are missing or empty in the reports. When can this happen?

After some changes in the customer data model the table structures can be changed in the DWH database. Details on the DWH database structure and how the data in the DWH can be configured is documented in the ConSol CM DWH Manual in chapter ConSol CM DWH Dynamic Tables / Custom Data Models.

Additionally DWH tables structures may change also due to CM core version updates. For this refer to the ConSol CM Release Notes.

Question #11: DWH live mode is active, but updates are missing in DWH

Why are the latest changes in a ticket are not visible in the DWH?

The live mode runs asynchronously. So it can take several minutes or longer depending on the update volume until the updates are applied to the DWH database. Even if there are only few changes there is delay, as the CMRF only checks for updates in certain intervals.

The thread which sends live data from CM to CMRF sleeps 1 minute if there is nothing to send. The thread which processes live data in CMRF sleeps 10 seconds if queues are empty.

It you need live reports you must implement them using the production database. Live reports can be implemented in CM by using the detailed search, tickets list and CM dashboard functionality.

What if after a scene update changes are missing in DWH?

During scene update not all changed are replicated by the live mode to the DWH database. Hence it is recommended to set the DWH mode to ADMIN before the Update and change it back to LIVE after the scene import was successful.

Especially you must not update CM tables directly, as in addition to potential problem in the CM, these changes are not seen by the live mode and can also be missed by the DWH update.

After as scene update with delete data the old tickets, units and resources are deleted in the CM but are still in DWH. How are they deleted in the DWH?

The delete data option in the scene does neither lead to a deletion of these objects in the DWH by DWH live mode nor does the task Update. You need to reinitialize the DWH via task Initialize with Delete existing data and create a task of type Transfer.