"This connection is part of a global transaction" error

Goal

This document describes how to resolve the error: “This connection is part of a global transaction” that may arise when querying a cached view in Denodo Virtual DataPort:

The most common reason for this error comes from an incorrect content of the  vdb_cache_sequences table in the cache database. We will explain how to detect if the error occurs due to this issue and how to resolve it.

Note: This solution applies only when Cache has been configured to use an external database, and not when using Denodo’s internal Apache Derby database.

Content

The cache database configured in Denodo Virtual DataPort contains a set of metadata tables. One of those tables is called “vdb_cache_sequences”. This table needs to contain at least one row in order to add new content into the cache database.

To check this, you can create a view on this table in Virtual DataPort using the cache data source (vdpcachedatasource in the admin database if the cache is enabled at server level, customvdpcachedatasource if the cache is enabled at database level) and query the view to check if it contains any rows. If no rows are returned, then the issue is in the cache database. It is highly likely that the cache tables have been modified directly in the cache database resulting in an inconsistency of this metadata table.  

To fix this error you will need to do a cleanup of the cache database by following these steps:

1. Deactivate the cache of your virtual database or at server level using Virtual DataPort Administration tool.  
2. Stop the Virtual DataPort server.
3. Drop all the cache tables from the cache database.
4. Start the Virtual DataPort server.
5. Activate the cache on your virtual database or at server level so that the new cache tables are created in the cache database.

After this, if you see rows created in the vdb_cache_sequences table then the issue has been resolved.

In conclusion if you see the error “This connection is part of a global transaction” always confirm if this is an error due to modifications in the cache database and resolve it by following the steps provided.