analytics/configuration/schemas/content/db2/CSsize_profiling_db2.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 outputs",
SUM(cast(coalesce(LENGTH(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 LENGTH(DATAPROP) < 10240;

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

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

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

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

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

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


select c.NAME as "class name", count(*) as "Total number of specifications", SUM(cast(coalesce(LENGTH(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 LENGTH(SPEC) < 10240;

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

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

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



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

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

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

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


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


select c.NAME as "class name", count(*) as "Total number of specifications", SUM(cast(coalesce(LENGTH(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(LENGTH(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(LENGTH(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(LENGTH(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(LENGTH(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(LENGTH(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(LENGTH(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(LENGTH(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(LENGTH(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;

WITH CMPROFILE (CMID, CLASSID, NOBJECTS) AS 
(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;


WITH CMPROFILE (CMID, CLASSID, NOBJECTS) AS 
(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 LENGTH(POLICIES) = 0;

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

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

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

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

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

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

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

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

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

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

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