New Topic
CommunityTopicQuestionAdministration
Following
photo
David Registro
APAC SD Team
Assigned
Properties
Category Administration

What query can I use to manually clear cached reports from the Document Data table

David Registro shared this question 8 years ago
Answered

My Yellowfin DB is huge, and it seems it's primarily because of the DocumentData table.

I've let the cleanup schedule run overnight, but it's just not shrinking. I think my table is too large for the mass cleanup process to work.

Is there a query I can run to manually clear these cached reports by age?

1 The same question

Replies (2)

photo
1
David Registro ● 8 years ago

Please note, and changes made to the Yellowfin DB should only be done after backups have been made.


You can use the query below for SQL Server.


  1. Stop Yellowfin and backup the DB.
  2. Work out your max age of the cached reports you want to keep, the query below will remove everything older than the DATETIME specified. The format of the DATETIME should be yyyymmddhhss and is in GMT time .
    E.g. 20161109000000 . This is 9th Nov 2016 at 00:00.
  3. Run the query below against your Yellowfin DB:


  1. SELECT ri.ReportInstanceId, ri.DocumentId
  2. INTO #temp_table
  3. FROM ContentManagement cm, ReportHeader rh, ReportInstance ri
  4. WHERE cm.ContentEntityCode = 'REPORT'
  5. AND cm.ContentCode = rh.SubCategoryCode
  6. AND rh.ReportId = ri.ReportId
  7. AND ri.DocumentId IS NOT NULL
  8. AND ri.DocumentId != 0
  9. AND ri.RequestTypeCode != 'COMMENT'
  10. AND ri.ReportGMTDateTime < 'DATETIMEHERE';
  11. DELETE FROM ReportInstance
  12. WHERE ReportStatusCode = 'SNAPSHOT'
  13. AND ReportId IN (
  14. SELECT ReportInstanceId
  15. FROM #temp_table
  16. );
  17. UPDATE ReportInstance
  18. SET DocumentId = null
  19. WHERE ReportInstanceId IN (
  20. SELECT ReportInstanceId
  21. FROM #temp_table
  22. );
  23. DELETE FROM DocumentData
  24. WHERE RevisionId IN (
  25. SELECT RevisionId
  26. FROM DocumentRevision
  27. WHERE DocumentId IN(
  28. SELECT DocumentId
  29. FROM #temp_table
  30. )
  31. );
  32. DELETE FROM DocumentRevision
  33. WHERE DocumentId IN(
  34. SELECT DocumentId
  35. FROM #temp_table
  36. );
  37. DELETE FROM DocumentItem
  38. WHERE DocumentId IN (
  39. SELECT DocumentId
  40. FROM #temp_table
  41. );
  42. DROP TABLE #temp_table;


4. Test Test Test your content to ensure you have only removed the expected cached reports.

photo
1
David Registro ● 8 years ago

You can use the below query for MySQL


  1. Stop Yellowfin and backup the DB.
  2. Work out your max age of the cached reports you want to keep, the query below will remove everything older than the DATETIME specified. The format of the DATETIME should be yyyymmddhhss and is in GMT time .
    E.g. 20161109000000 . This is 9th Nov 2016 at 00:00.
  3. Run the query below against your Yellowfin DB:


  1. CREATE TABLE temp_table AS (
  2. SELECT ri.ReportInstanceId, ri.DocumentId
  3. FROM ContentManagement cm, ReportHeader rh, ReportInstance ri
  4. WHERE cm.ContentEntityCode = 'REPORT'
  5. AND cm.ContentCode = rh.SubCategoryCode
  6. AND rh.ReportId = ri.ReportId
  7. AND ri.DocumentId IS NOT NULL
  8. AND ri.DocumentId != 0
  9. AND ri.RequestTypeCode != 'COMMENT'
  10. AND ri.ReportGMTDateTime < 'DATETIME'
  11. );
  12. DELETE FROM ReportInstance
  13. WHERE ReportStatusCode = 'SNAPSHOT'
  14. AND ReportId IN (
  15. SELECT ReportInstanceId
  16. FROM temp_table
  17. );
  18. UPDATE ReportInstance
  19. SET DocumentId = null
  20. WHERE ReportInstanceId IN (
  21. SELECT ReportInstanceId
  22. FROM temp_table
  23. );
  24. DELETE FROM DocumentData
  25. WHERE RevisionId IN (
  26. SELECT RevisionId
  27. FROM DocumentRevision
  28. WHERE DocumentId IN(
  29. SELECT DocumentId
  30. FROM temp_table
  31. )
  32. );
  33. DELETE FROM DocumentRevision
  34. WHERE DocumentId IN(
  35. SELECT DocumentId
  36. FROM temp_table
  37. );
  38. DELETE FROM DocumentItem
  39. WHERE DocumentId IN (
  40. SELECT DocumentId
  41. FROM temp_table
  42. );
  43. DROP TABLE temp_table;

4. Test Test Test your content to ensure you have only removed the expected cached reports.

Replies have been locked on this page!