Example SQL Queries to help manage TIBCO Spotfire library content size
book
Article ID: KB0072647
calendar_today
Updated On:
Products
Versions
Spotfire Server
All
Description
Over time the content in the TIBCO Spotfire library can grow as data (dxp files with data stored/embedded, sbdf files, etc) is saved to the library. The following are some sample SQL queries that can be run against the TIBCO Spotfire database to help a Spotfire administrator get visibility into the library contents, specifically highlighting the largest objects and largest folders in the library, users who are associated with the most content, and large content which has not been accessed in recent history or ever.
Also, see Searching the Library for ways to find similar content with the official Spofire library search functionality (for example: content_size:>100MB).
Note: Management of the database itself is the responsibility of the database administrator, but the Spotfire administrator can manage the content saved in the library.
Environment
All
Resolution
Here are example queries that can be run against a TIBCO Spotfire database which is running on a Microsoft SQL Server. For other database types the queries may have to be modified:
------------------------------------ --Largest library items ------------------------------------ select li.CONTENT_SIZE/(1024*1024) as [CONTENT_SIZE (MB)], li.ITEM_ID, li.TITLE, li.DESCRIPTION, lit.DISPLAY_NAME, created.DISPLAY_NAME as [CREATED BY], li.CREATED as [CREATED DATE], modified.DISPLAY_NAME as [MODIFIED BY], li.MODIFIED as [MODIFIED DATE], li.ACCESSED as [ACCESSED DATE], li.PARENT_ID, parent.TITLE as [PARENT TITLE] from lib_items li join LIB_ITEMS parent on li.PARENT_ID=parent.ITEM_ID join LIB_ITEM_TYPES lit on li.ITEM_Type=lit.TYPE_ID join USERS created on li.CREATED_BY=created.USER_ID join USERS modified on li.MODIFIED_BY=modified.USER_ID order by li.CONTENT_SIZE desc
------------------------------------ --Largest library folders ------------------------------------ select Sum(li.CONTENT_SIZE)/(1024*1024) as [CONTENT_SIZE (MB)], li.PARENT_ID as [FOLDER ITEM_ID], parent.TITLE as [FOLDER TITLE], created.DISPLAY_NAME as [CREATED BY], parent.CREATED as [CREATED DATE], modified.DISPLAY_NAME as [MODIFIED BY], parent.MODIFIED as [MODIFIED DATE], parent.ACCESSED as [ACCESSED DATE] from lib_items li join LIB_ITEMS parent on li.PARENT_ID=parent.ITEM_ID join USERS created on parent.CREATED_BY=created.USER_ID join USERS modified on parent.MODIFIED_BY=modified.USER_ID group by li.PARENT_ID,parent.TITLE,created.DISPLAY_NAME,parent.CREATED,modified.DISPLAY_NAME,parent.MODIFIED,parent.ACCESSED order by Sum(li.CONTENT_SIZE)/(1024*1024) desc
------------------------------------ --Users consuming the most space - by Creator ------------------------------------ select top 1 * from lib_items select * from lib_item_types select Sum(li.CONTENT_SIZE)/(1024*1024) as [TOTAL CONTENT_SIZE (MB)], Sum(case when lit.FILE_SUFFIX='dxp' then 1 else 0 end) as [# DXP FILES], Sum(case when lit.FILE_SUFFIX='dxp' then li.CONTENT_SIZE else 0 end)/(1024*1024) as [DXP FILE SIZE (MB)], Sum(case when lit.FILE_SUFFIX='sbdf' then 1 else 0 end) as [# SBDF FILES], Sum(case when lit.FILE_SUFFIX='sbdf' then li.CONTENT_SIZE else 0 end)/(1024*1024) as [SBDF FILE SIZE (MB)], created.DISPLAY_NAME as [CREATED BY] from lib_items li join LIB_ITEM_TYPES lit on li.ITEM_TYPE=lit.TYPE_ID join USERS created on li.CREATED_BY=created.USER_ID group by created.DISPLAY_NAME order by Sum(li.CONTENT_SIZE)/(1024*1024) desc
------------------------------------ --Users consuming the most space - by Last Modifier ------------------------------------ select Sum(li.CONTENT_SIZE)/(1024*1024) as [TOTAL CONTENT_SIZE (MB)], Sum(case when lit.FILE_SUFFIX='dxp' then 1 else 0 end) as [# DXP FILES], Sum(case when lit.FILE_SUFFIX='dxp' then li.CONTENT_SIZE else 0 end)/(1024*1024) as [DXP FILE SIZE (MB)], Sum(case when lit.FILE_SUFFIX='sbdf' then 1 else 0 end) as [# SBDF FILES], Sum(case when lit.FILE_SUFFIX='sbdf' then li.CONTENT_SIZE else 0 end)/(1024*1024) as [SBDF FILE SIZE (MB)], modified.DISPLAY_NAME as [LAST MODIFIED BY] from lib_items li join LIB_ITEM_TYPES lit on li.ITEM_TYPE=lit.TYPE_ID join USERS modified on li.MODIFIED_BY=modified.USER_ID group by modified.DISPLAY_NAME order by Sum(li.CONTENT_SIZE)/(1024*1024) desc ------------------------------------
------------------------------------ --Largest content (>10 MB) not accessed recently (never accessed or accessed greater than 1 year ago) ------------------------------------ select li.CONTENT_SIZE/(1024*1024) as [CONTENT_SIZE (MB)], li.ITEM_ID, li.TITLE, li.DESCRIPTION, lit.DISPLAY_NAME as [ITEM TYPE], created.DISPLAY_NAME as [CREATED BY], li.CREATED as [CREATED DATE], modified.DISPLAY_NAME as [MODIFIED BY], li.MODIFIED as [MODIFIED DATE], li.ACCESSED as [ACCESSED DATE], li.PARENT_ID, parent.TITLE as [PARENT TITLE],li.CONTENT_SIZE from lib_items li join LIB_ITEMS parent on li.PARENT_ID=parent.ITEM_ID join LIB_ITEM_TYPES lit on li.ITEM_Type=lit.TYPE_ID join USERS created on li.CREATED_BY=created.USER_ID join USERS modified on li.MODIFIED_BY=modified.USER_ID where (li.ACCESSED is NULL or li.ACCESSED < DATEADD(year,-1,GETDATE())) and li.CONTENT_SIZE/(1024*1024) > 10 order by li.CONTENT_SIZE desc
Disclaimer: The content of this article is for informational purposes only. The subject material may change in any new versions with no notice and there is no responsibility by TIBCO to maintain or support future access to this internal application content. Modification of any internal application content is not recommended and can lead to an unsupported configuration. It is not intended to be used "As Is" in a Production environment. Always test in a Development environment.
Issue/Introduction
This article contains example SQL queries which provide insight into the TIBCO Spotfire library content