Instructions & Usage:

because of Pruner stopped truncating data for some table, Unwanted partitions creating in db and DB size is growing.

We can delete stale partitions entry from partition_cntl and dropping
related partition table from DB using procedure  multiple_stale_partitions_removal.



NOTE: Please take backup before performing this activity.

      Also Note that, Dropping Table may not shrink the size of DB, Manual rebuild  can use to shrink Sybase DB size
     
NOTE: Make sure that Output of below query for VALUE must be  24
      select NAME ,VALUE from system_const where name = 'RW_PARTITION_PERIOD';
 

STOP BPPM SERVER with pw sys stop command and Start Only dbsrv process


1. Create multiple_stale_partitions_removal procedure in sybase database using dbisql with dba login
   using multiple_stale_partitions_removal.sql file.

2. Get the output of below query  by re-directing in any file

   select t1.tablename prunetbl,
       t2.tablename as partitiontbl,
       (t1.deltahours / 24 ) as  prunedays,
       max(t2.part_num) as  maxpart,
       count(t2.part_num) as cntpart
  from prune_cntl t1, partition_cntl t2
 where t1.Tablename = t2.tablename || '_STATS'
   and t2.tabletype = 1
 GROUP by t1.tablename, t2.tablename, (t1.deltahours / 24)
 ORDER BY max(t2.part_num) desc;

 If cntpart > prunedays + 2 it means there is issue with prunner and you need to
 run procedure to clean these stale entries 

3. STOP BPPM SERVER with pw sys stop command and let dbsrv process running
   IF you want to delete stale data from all listed monitors table you can use below command using dbisql editor with dba login)
  
   call "DBA"."multiple_stale_partitions_removal"('DBA')
 
   Cross verify the output using below query

       select t1.tablename prunetbl,
       t2.tablename as partitiontbl,
       (t1.deltahours / 24 ) as  prunedays,
       max(t2.part_num) as  maxpart,
       count(t2.part_num) as cntpart
  from prune_cntl t1, partition_cntl t2
 where t1.Tablename = t2.tablename || '_STATS'
   and t2.tabletype = 1
 GROUP by t1.tablename, t2.tablename, (t1.deltahours / 24)
 ORDER BY max(t2.part_num) desc;

 
 start BPPM SERVER with pw sys start command
 Run pw dataview drop -all followed by pw dataview create -all. 
 Run pw dataview update




