Retrieve information from CSDB - DDB Information


Userlevel 2
Badge +3

Similar to this article, I'd like to show simple queries to retrieve DDB information from CSDB.

Important note: do not modify CSDB data and modules, just use READ operations only.
Also please

To keep your activities safe you need to keep uncommitted read from any table as one of the following techniques:

use CommServ	-- just for convenience

-- place the following at the top of any query
set transaction isolation level read uncommitted;

-- place with(nolock) statement in each table reference
select * from APP_Application with(nolock)

Most of the DDB information is stored in tables started with Idx, for configuration of DDB is stored mainly the following 3 tables, first one is for DDB information, latter 2 for partitions:

select * from IdxSIDBStore
select * from IdxSIDBSubStore
select * from IdxAccessPath

To combine this, including which MA is in use for each partition, like the following:

select 
store.SIDBStoreName as 'DDB Name'
,apc.name as 'MediaAgent'
,ap.Path as 'Partition path'
from IdxSIDBStore store
inner join IdxSIDBSubStore subst on subst.SIDBStoreId = store.SIDBStoreId
inner join APP_Client apc on apc.id = subst.ClientId
inner join IdxAccessPath ap on ap.IdxAccessPathId = subst.IdxAccessPathId

Adding more information which is stored in IdxSIDBUsageHistory having history of DDB data statistics as follows:

select 
store.SIDBStoreName as 'DDB Name'
,apc.name as 'MediaAgent'
,ap.Path as 'Partition path'
,DateAdd(second, history.ModifiedTime+3600*0, '1970-01-01') as 'Last Updated (GMT)'
,history.PrimaryEntries as '# of Primary Entries'
from IdxSIDBStore store
inner join IdxSIDBSubStore subst on subst.SIDBStoreId = store.SIDBStoreId
inner join APP_Client apc on apc.id = subst.ClientId
inner join IdxAccessPath ap on ap.IdxAccessPathId = subst.IdxAccessPathId
inner join (
select * from (
select
row_number() over (partition by substoreid order by modifiedtime desc) as rownum
,*
from IdxSIDBUsageHistory) as A
where rownum = 1
)
as history on history.SubStoreId = subst.SubStoreId

Hope this helps a bit,


0 replies

Be the first to reply!

Reply