Example SQL Queries to help manage TIBCO Spotfire library content size

Product: TIBCO Spotfire Server

Versions: All

Summary:

This article contains example SQL queries which provide insight into the TIBCO Spotfire library content

Details:

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.

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.