TNPM/SQL

SQL that might be handy

To find out the total Number of Elements in an on state:

SELECT COUNT(*) "Total Elmt"
FROM   elmt_desc
WHERE  str_state = 'on';

To find out the total Number of subElements in an on state:

SELECT COUNT(*) "Total SE"
FROM   se_desc s,
       elmt_desc e
WHERE  s.str_state = 'on'
       AND e.str_state = 'on'
       AND s.idx_host = e.idx_ind;

To find out the Grouping tree information:

SELECT grpcnt       "Group Count",
       parentgrpcnt "Parent Group Count",
       childgrpcnt  "Child Group Count"
FROM   (SELECT COUNT(*) grpcnt
        FROM   se_grp_desc),
       (SELECT COUNT(DISTINCT idx_group_parent) parentgrpcnt,
               COUNT(DISTINCT idx_group_child)  childgrpcnt
        FROM   se_grp_group);

To find out the SNMP Collector information:

SELECT COUNT(*)        "Collector Count",
       MAX(cnt)        "Max SE Per Collector",
       Round(Avg(cnt)) "Ave SE Per Collector"
FROM   (SELECT COUNT(*) cnt
        FROM   elmt_desc e,
               se_desc s
        WHERE  e.idx_ind = s.idx_host
        GROUP  BY e.int_collector);

To find out the Grouping information:

SELECT cntleafgrp                                "Leaf Grps With SE",
       cntse                                     "Total SE",
       cntgrpedse                                "Total Grped SE",
       Round(cntgrpedse / cntse, 1)              "Grp Density",
       distinctcntgrpedse                        "Distinct Count Of Grped SE",
       Round(cntgrpedse / distinctcntgrpedse, 1) "Grp Density Of Grped SE"
FROM   (SELECT COUNT(*) cntgrpedse
        FROM   se_grp_member),
       (SELECT COUNT(DISTINCT idx_alias_inst) distinctcntgrpedse
        FROM   se_grp_member),
       (SELECT COUNT(*) cntse
        FROM   se_desc),
       (SELECT COUNT(DISTINCT idx_group) cntleafgrp
        FROM   se_grp_member);

Version Control details

begin
pvm_version.Delete_Current_Version('INST',' <Proviso_component>,' <hostname>');
end;
/

begin pvm_version.Delete_Current_Version('INST','Database','my_dbdm');end;/
begin pvm_version.Delete_Current_Version('INST','Datachannel','my_dc');end;/

begin pvm_version.Delete_Current_Version('INST','Dataload','my_dl');end;/

begin pvm_version.Delete_Current_Version('INST','Datamart','my_dbdm');end;/

begin pvm_version.Delete_Current_Version('INST','Dataview','my_dv');end;/