SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software
Data Virtualization Tools and Software Denodo Platform and Data Virtuality Tutorials and Downloads for SQL Database Developer

Data Virtuality: Cannot add batch to invalidated cache group

Data Virtuality enables developers to access different data sources using a standard SQL interface. While executing SQL queries on different data sources with data types that are not stored in memory but stored on disks, some times following Data Virtuality error might occur: Cannot add batch to invalidated cache group.
This error is related with temporary storage disk size when it is not enough to store such data. The solution is either cleaning the temp space area by a reboot of the Data Virtuality server or increasing the disk size reserved for temporary usage by changing the Data Virtuality buffer-service-max-buffer-space server attribute

After a long running query on Data Virtuality server, following error is reported by users.

TEIID31138 Cannot add batch to invalidated cache group "7,790,770"

Cannot add batch to invalidated cache group

Fortunately, a detailed information about the error message is given on Data Virtuality support portal

As the article implies, the disk space for temporarily storage requirements was 50 GB which is the default size.
Administrators can check their Data Virtuality server's temp space amount by executing following command on a new SQL editor screen.

SELECT "a.reply"
"script" => '/subsystem=teiid:read-attribute(name=buffer-service-max-buffer-space)')) as a;;

If you did not change the buffer-service-max-buffer-space or temp space and keep the default setting, the output will be pointing to an amount around 53000 MB or so which is 50 GB actually.

If you have enough space, you can increase the temp space size from 50 GB to 100 GB by executing below code. This will provide a long term solution to the problem.

EXEC "SYSADMIN.executeCli"(
"script" => '/subsystem=teiid:write-attribute(name=buffer-service-max-buffer-space,value=102400)');;

increase temporary disk size for Data Virtuality

Please note; after modifying the disk space reserved for Data Virtuality to use for temporary data storage, a reboot of the Data Virtuality server is requried.

If you again try to read the same attribute before you reboot the AWS EC2 instance by executing the "read-attribute" command, you can realize the "reload-required" message in the response

check temp space size on Data Virtuality server

If the Data Virtuality server is restarted, and then the "read-attribute" command is executed on SQL editor, the message will be different in this case displaying the actual amount reserved for temp data storage.

Data Virtuality buffer-service-max-buffer-space attribute value after reboot

To summarize, to modify the Data Virtuality server attribute buffer-service-max-buffer-space enables usage of more disk space for temporary disk storage of data of type XML, CLOB, BLOB and OBJECT which should not be stored in memory. A restart of the Data Virtuality server is required after modification of server attributes.

In this article, we concantrated on long term solution of the problem. A reboot of the Data Virtuality server will clean the reserved 50 GB disk storage area and until more disk space is requried SQL users can query without a problem. But if more than 50 GB temporary disk size is requried the same error will occur again.

Data Virtualization

Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.