analytics/configuration/schemas/content/sqlserver/CSsize_profiling_mssqlserver.sql

-- Licensed Materials - Property of IBM
--
-- BI and PM: CM
--
-- (C) Copyright IBM Corp. 2008, 2014
--
-- US Government Users Restricted Rights - Use, duplication or disclosure
-- restricted by GSA ADP Schedule Contract with IBM Corp.

-- Copyright (C) 2008 Cognos ULC, an IBM Company. All rights reserved.
-- Cognos (R) is a trademark of Cognos ULC, (formerly Cognos Incorporated).


select count(*) as "total number of objects" from CMOBJECTS;

select  c.NAME as "class name", count(*) as "number of objects"
from CMOBJECTS o, CMCLASSES c  
group by o.CLASSID, c.CLASSID, C.NAME
having c.CLASSID=o.CLASSID
order by c.NAME;

select  c.NAME as "class name", count(*) as "number of secured objects"
from CMOBJECTS o, CMCLASSES c, CMPOLICIES p 
where  o.CMID=p.CMID and p.POLICIES is not null
group by o.CLASSID, c.CLASSID, C.NAME
having c.CLASSID=o.CLASSID
order by c.NAME;



select count(*) as "Total number of outputs",
SUM(cast(coalesce(DATALENGTH(DATAPROP),0) as bigint)) as "Total size of outputs" from CMDATA
where DATAPROP is not NULL;


select count(*) as "Outputs under 10KB" from CMDATA
where DATAPROP is not NULL and DATALENGTH(DATAPROP) < 10240;

select count(*) as "Outputs 10KB - 100KB" from CMDATA
where DATALENGTH(DATAPROP) >= 10240 and DATALENGTH(DATAPROP) < 102400;

select count(*) as "Outputs 100KB - 1MB" from CMDATA
where DATALENGTH(DATAPROP) >= 102400 and DATALENGTH(DATAPROP) < 1048576;

select count(*) as "Outputs 1MB - 10MB" from CMDATA
where DATALENGTH(DATAPROP) >= 1048576 and DATALENGTH(DATAPROP) < 10485760;

select count(*) as "Outputs 10MB-100MB" from CMDATA
where DATALENGTH(DATAPROP) >= 10485760 and DATALENGTH(DATAPROP) < 104857600;

select count(*) as "Outputs 100MB-1GB" from CMDATA
where DATALENGTH(DATAPROP) >= 104857600 and DATALENGTH(DATAPROP) < 1073741824;

select count(*) as "Outputs > 1GB" from CMDATA
where DATALENGTH(DATAPROP) >= 1073741824;


select c.NAME as "class name", count(*) as "Total number of specifications", SUM(cast(coalesce(DATALENGTH(p.SPEC),0) as bigint)) as "Total size of specifications" 
from CMOBJECTS o, CMCLASSES c, CMOBJPROPS7 p
where  o.CMID=p.CMID and p.SPEC is not NULL
group by o.CLASSID, c.CLASSID, C.NAME
having c.CLASSID=o.CLASSID
order by c.NAME;


select count(*) as "Specifications < 10KB" from CMOBJPROPS7
where SPEC is not NULL and DATALENGTH(SPEC) < 10240;

select count(*) as "Specifications 10KB - 100KB" from CMOBJPROPS7
where DATALENGTH(SPEC) >= 10240 and  DATALENGTH(SPEC) < 102400;

select count(*) as "Specifications 100KB - 1MB" from CMOBJPROPS7
where DATALENGTH(SPEC) >= 102400 and  DATALENGTH(SPEC) < 1048576;

select count(*) as "Specifications > 1MB" from CMOBJPROPS7
where DATALENGTH(SPEC) >= 1048576;



select c.NAME as "class name", count(*) as "Total number of specifications", SUM(cast(coalesce(DATALENGTH(p.SPEC),0) as bigint)) as "Total size of specifications" 
from CMOBJECTS o, CMCLASSES c, CMOBJPROPS34 p
where  o.CMID=p.CMID and p.SPEC is not NULL
group by o.CLASSID, c.CLASSID, C.NAME
having c.CLASSID=o.CLASSID
order by c.NAME;


select c.NAME as "class name", count(*) as "Total number of specifications", SUM(cast(coalesce(DATALENGTH(p.SPEC),0) as bigint)) as "Total size of specifications" 
from CMOBJECTS o, CMCLASSES c, CMOBJPROPS43 p
where  o.CMID=p.CMID and p.SPEC is not NULL
group by o.CLASSID, c.CLASSID, C.NAME
having c.CLASSID=o.CLASSID
order by c.NAME;


select c.NAME as "class name", count(*) as "Total number of specifications", SUM(cast(coalesce(DATALENGTH(p.SPEC),0) as bigint)) as "Total size of specifications" 
from CMOBJECTS o, CMCLASSES c, CMOBJPROPS66 p
where  o.CMID=p.CMID and p.SPEC is not NULL
group by o.CLASSID, c.CLASSID, C.NAME
having c.CLASSID=o.CLASSID
order by c.NAME;


select c.NAME as "class name", count(*) as "Total number of specifications", SUM(cast(coalesce(DATALENGTH(p.RELATED),0) as bigint)) as "Total size of specifications" 
from CMOBJECTS o, CMCLASSES c, CMOBJPROPS14 p
where  o.CMID=p.CMID and p.RELATED is not NULL
group by o.CLASSID, c.CLASSID, C.NAME
having c.CLASSID=o.CLASSID
order by c.NAME;


select c.NAME as "class name", count(*) as "Total number of specifications", SUM(cast(coalesce(DATALENGTH(p.SPEC),0) as bigint)) as "Total size of specifications" 
from CMOBJECTS o, CMCLASSES c, CMOBJPROPS63 p
where  o.CMID=p.CMID and p.SPEC is not NULL
group by o.CLASSID, c.CLASSID, C.NAME
having c.CLASSID=o.CLASSID
order by c.NAME;


select c.NAME as "class name", count(*) as "Total number of specifications", SUM(cast(coalesce(DATALENGTH(p.SPEC),0) as bigint)) as "Total size of specifications" 
from CMOBJECTS o, CMCLASSES c, CMOBJPROPS42 p
where  o.CMID=p.CMID and p.SPEC is not NULL
group by o.CLASSID, c.CLASSID, C.NAME
having c.CLASSID=o.CLASSID
order by c.NAME;


select c.NAME as "class name", count(*) as "Total number of specifications", SUM(cast(coalesce(DATALENGTH(p.SPEC),0) as bigint)) as "Total size of specifications" 
from CMOBJECTS o, CMCLASSES c, CMOBJPROPS68 p
where  o.CMID=p.CMID and p.SPEC is not NULL
group by o.CLASSID, c.CLASSID, C.NAME
having c.CLASSID=o.CLASSID
order by c.NAME;

select c.NAME as "class name", count(*) as "Total number of specifications", SUM(cast(coalesce(DATALENGTH(p.SPEC),0) as bigint)) as "Total size of specifications" 
from CMOBJECTS o, CMCLASSES c, CMOBJPROPS56 p
where  o.CMID=p.CMID and p.SPEC is not NULL
group by o.CLASSID, c.CLASSID, C.NAME
having c.CLASSID=o.CLASSID
order by c.NAME;

select c.NAME as "class name", count(*) as "Total number of specifications", SUM(cast(coalesce(DATALENGTH(p.SPEC),0) as bigint)) as "Total size of specifications" 
from CMOBJECTS o, CMCLASSES c, CMOBJPROPS57 p
where  o.CMID=p.CMID and p.SPEC is not NULL
group by o.CLASSID, c.CLASSID, C.NAME
having c.CLASSID=o.CLASSID
order by c.NAME;



select count(*) as "Total number of models" from CMOBJPROPS7
where CMODEL is not NULL;


select count(*) as "Models < 10KB" from CMOBJPROPS7
where DATALENGTH(CMODEL) < 10240;

select count(*) as "Models 10KB - 100KB" from CMOBJPROPS7
where DATALENGTH(CMODEL) > 10240 and  DATALENGTH(CMODEL) < 102400;

select count(*) as "Models 100KB - 1MB" from CMOBJPROPS7
where DATALENGTH(CMODEL) > 102400 and  DATALENGTH(CMODEL) < 1048576;

select count(*) as "Models > 1MB" from CMOBJPROPS7
where DATALENGTH(CMODEL) > 1048576;

create table #CMPROFILE (CMID int, CLASSID int, NOBJECTS bigint)

insert into #CMPROFILE (CMID, CLASSID, NOBJECTS)  
SELECT o.PCMID, p.CLASSID, count(*) from CMOBJECTS o left outer join CMOBJECTS p on o.PCMID=p.CMID
where p.CLASSID = 1 or p.CLASSID=17 or p.CLASSID=12 or p.CLASSID=9
group by o.PCMID, p.CLASSID

select c.NAME as "object class", pr.NOBJECTS as "number of children", count(*) as "number of objects"
from #CMPROFILE pr left outer join CMCLASSES c on pr.CLASSID=c.CLASSID
group by c.NAME,  pr.NOBJECTS
order by c.NAME, pr.NOBJECTS;

select TYPE as "schedule type", count(*) "number of schedules"
from CMOBJPROPS2 where ACTIVE=1 and TYPE is not NULL 
group by TYPE;

insert into #CMPROFILE (CMID, CLASSID, NOBJECTS) 
SELECT o.PCMID, p.CLASSID, count(*) from CMOBJECTS o left outer join CMOBJECTS p on o.PCMID=p.CMID
where p.CLASSID = 55 and o.CLASSID = 57 or o.CLASSID=17
group by o.PCMID, p.CLASSID

select c.NAME as "object class", pr.NOBJECTS as "number of children", count(*) as "number of objects"
from #CMPROFILE pr left outer join CMCLASSES c on pr.CLASSID=c.CLASSID
group by c.NAME,  pr.NOBJECTS
order by c.NAME, pr.NOBJECTS;




select count(*) as "Number of empty policies" from CMPOLICIES
where DATALENGTH(POLICIES) = 0;

select count(*) as "Policies 1-127 bytes" from CMPOLICIES
where DATALENGTH(POLICIES) >= 1 and DATALENGTH(POLICIES) <= 127;

select count(*) as "Policies 128-255 bytes" from CMPOLICIES
where DATALENGTH(POLICIES) >= 128 and DATALENGTH(POLICIES) <= 255;

select count(*) as "Policies 256-511 bytes" from CMPOLICIES
where DATALENGTH(POLICIES) >= 256 and DATALENGTH(POLICIES) <= 511;

select count(*) as "Policies 512-1023 bytes" from CMPOLICIES
where DATALENGTH(POLICIES) >= 512 and DATALENGTH(POLICIES) <= 1023;

select count(*) as "Policies 1K-2K" from CMPOLICIES
where DATALENGTH(POLICIES) >= 1023 and DATALENGTH(POLICIES) <= 2047;

select count(*) as "Policies 2K-4K" from CMPOLICIES
where DATALENGTH(POLICIES) >= 2048 and DATALENGTH(POLICIES) <= 4095;

select count(*) as "Policies 4K-6K" from CMPOLICIES
where DATALENGTH(POLICIES) >= 4096 and DATALENGTH(POLICIES) <= 6143;

select count(*) as "Policies 6K-8K" from CMPOLICIES
where DATALENGTH(POLICIES) >= 6144 and DATALENGTH(POLICIES) <= 8191;

select count(*) as "Policies > 8K" from CMPOLICIES
where DATALENGTH(POLICIES) > 8192;


select count(*) as "Archive queue size" from CMARCHIVEQUEUE;

select count(*) as "Delete queue size" from CMDELETEQUEUE;


drop table #CMPROFILE