Can a user in TIBCO Data Virtualization delete the temp files under TDV_INSTALL/tmp directory?
Article ID: KB0072637
Updated On:
| Products | Versions |
|---|---|
| TIBCO Data Virtualization | All supported versions |
Description
The tmp tables are created when the results of a query are too large to hold in memory and are eventually "spilled to disk”. Generally restarting TIBCO Data Virtualization would clear the Temp files.
Issue/Introduction
This article answers some important questions regarding the temp files that get created under TDV_INSTALL/tmp.
Environment
All supported versions
Resolution
* These tmp files can be deleted to reclaim the space, however, it's important to note that no other query must be in execution and must be writing on the disk because it can impact another execution if the file is still being used.
* It is not possible to find a correlation between the tmp file being generated and the query that is writing on the disk. The temp file names are randomly created and are guaranteed to be unique within the same directory. Therefore, the numeric ID you see in the name of a tmp file e.g. in tmp10281829865662712107.tmp, the numeral 10281829865662712107 has no relation with either the Request ID of the query or the space that query has consumed on the disk.
* However, a user can run the below query to figure out the list of requests that are writing on the DISK:
-------------------------
SELECT * FROM /services/databases/system/SYS_REQUESTS ORDER BY MAX_DISK DESC
-------------------------
* User may make use of the cs_server_status.log to trace down the query that might be still in execution and writing on the DISK. If the files are being created by a query, the query would be a script, and so the user might be able to find the queries using the below-mentioned approach:
- Look at the timestamp for the files under TDV_INSTALL/tmp/pipecursors.
- See whether the cs_server_status.log contains requests having a timestamp close to this. For a script, the requests will contain "SQL Script", for example:
-------------------------
dir C:\apps\tdv85a\tmp\pipecursors\
-------------------------
---> 01/11/2022 04:01 PM 4,500,001 somefile.tmp
grep "2022-01-11 16:01" cs_server_status.log*
-------------------------
cs_server_status.log:
477150 | [NULL] | 501698 | 509711 | -1973 | admin | SQL Script | TOP_MEMORY | PROCEDURE p_insert (OUT result PIPE (rev_name VARCHAR)) BEGIN DECLARE c CURSOR FOR SELECT city FROM /shared/examples/ds_orders/tutorial/customers; DECLARE name VARCHAR; OPEN c; REPEAT FETCH c INTO name; INSERT INTO result (rev_name) VALUES (name); UNTIL NOT c.FOUND END REPEAT; CLOSE c; END | 2022-01-11 15:59:15.559 | 2022-01-11 15:59:15.674 | 115 | 115 | -1 | 2524660 | 0 | 0 | 0 | [NULL] | 1024660 | 0 | 0
-------------------------
NOTE: A temp file for a pipe cursor can store information for all resources that are using that pipe.
If a user is facing issues with large temp files on disk, they need to either increase the disk size allotted to TIBCO Data Virtualization or increase the memory so that more processing is done in memory. However, they can increase both so that they don't have to rely on manual intervention.
* It is not possible to find a correlation between the tmp file being generated and the query that is writing on the disk. The temp file names are randomly created and are guaranteed to be unique within the same directory. Therefore, the numeric ID you see in the name of a tmp file e.g. in tmp10281829865662712107.tmp, the numeral 10281829865662712107 has no relation with either the Request ID of the query or the space that query has consumed on the disk.
* However, a user can run the below query to figure out the list of requests that are writing on the DISK:
-------------------------
SELECT * FROM /services/databases/system/SYS_REQUESTS ORDER BY MAX_DISK DESC
-------------------------
* User may make use of the cs_server_status.log to trace down the query that might be still in execution and writing on the DISK. If the files are being created by a query, the query would be a script, and so the user might be able to find the queries using the below-mentioned approach:
- Look at the timestamp for the files under TDV_INSTALL/tmp/pipecursors.
- See whether the cs_server_status.log contains requests having a timestamp close to this. For a script, the requests will contain "SQL Script", for example:
-------------------------
dir C:\apps\tdv85a\tmp\pipecursors\
-------------------------
---> 01/11/2022 04:01 PM 4,500,001 somefile.tmp
grep "2022-01-11 16:01" cs_server_status.log*
-------------------------
cs_server_status.log:
477150 | [NULL] | 501698 | 509711 | -1973 | admin | SQL Script | TOP_MEMORY | PROCEDURE p_insert (OUT result PIPE (rev_name VARCHAR)) BEGIN DECLARE c CURSOR FOR SELECT city FROM /shared/examples/ds_orders/tutorial/customers; DECLARE name VARCHAR; OPEN c; REPEAT FETCH c INTO name; INSERT INTO result (rev_name) VALUES (name); UNTIL NOT c.FOUND END REPEAT; CLOSE c; END | 2022-01-11 15:59:15.559 | 2022-01-11 15:59:15.674 | 115 | 115 | -1 | 2524660 | 0 | 0 | 0 | [NULL] | 1024660 | 0 | 0
-------------------------
NOTE: A temp file for a pipe cursor can store information for all resources that are using that pipe.
If a user is facing issues with large temp files on disk, they need to either increase the disk size allotted to TIBCO Data Virtualization or increase the memory so that more processing is done in memory. However, they can increase both so that they don't have to rely on manual intervention.
Feedback
Yes
No
Powered by
