{ ************************************************************************* }
{                                                                           }
{                          INFORMIX SOFTWARE, INC.                          }
{                                                                           }
{   Title:      arc_purge.sql                                               }
{   Sccsid:     @(#)arc_purge.sql	9.1 10/27/92  11:37:42                                                }
{   Description:                                                            }
{               cleanup archive tables by getting rid of inconsistent info  }
{                                                                           }
{ ************************************************************************* }
{ --------------------------------------------------------------------------}
{ FILE    : ARC_PURGE_INCONSISTENT.SQL                                      }
{ PRODUCT : ARCHIVE*SQL                                                     }
{ MODULE  : TOOLS PROCEDURES                                                }
{ VERSION : 2.3.1                                                           }
{ CONTENT : SQL statements to remove all inconsistent information from the  }
{           catalog and to then update the "VOL_NB_SVST" field of the       }
{           "ARC_VOLUME" table.                                             }
{                                                                           }
{ Computertime Network Corporation                                          }
{ Informix Version 6.0                                                      }
{ Copyright 1988, 1991, 1992.  All rights reserved.                         }
{                                                                           }
{ --------------------------------------------------------------------------}

database sysmaster;

begin work;

lock table arc_request in exclusive mode;
lock table arc_volume in exclusive mode;
lock table arc_file in exclusive mode;
lock table arc_save_set in exclusive mode;

{ --------------------------------------------------------------------------}
{ Remove "dangling" objects                                                 }
{ --------------------------------------------------------------------------}

DELETE FROM ARC_REQUEST WHERE (REQ_STATUS IN ('SU','UC','PA') AND
                               REQ_TYPE = 'AR'                AND
                               NOT EXISTS (select file_rid from arc_file
                                             where file_rid = req_rid));

DELETE FROM ARC_VOL_LOCK WHERE NOT EXISTS (SELECT req_rid from arc_request
                                             where req_rid = vlck_rid);

DELETE FROM ARC_VOLUME WHERE NOT EXISTS (SELECT vset_vid from arc_vset
                                           where vset_vid = vol_vid);

DELETE FROM ARC_REQ_VSET WHERE NOT EXISTS (SELECT req_rid from arc_request
                                           where req_rid = rv_rid and
                                             req_status in ('NE','EX','PA' ));

DELETE FROM ARC_REQ_VSET WHERE NOT EXISTS (SELECT vset_vid from arc_vset
                                             where vset_vid = rv_vid);

DELETE FROM ARC_VSET_USER WHERE NOT EXISTS (SELECT vset_vid from arc_vset
                                              where vset_vid = vu_vid);

DELETE FROM ARC_SAVE_SET WHERE NOT EXISTS (SELECT vset_vid from arc_vset
                                             where vset_vid = svst_vid);

DELETE FROM ARC_FILE WHERE NOT EXISTS (select req_rid from arc_request
                                   where req_rid = file_rid and
                                         req_status in ('SU','UC','EX','PA'));

DELETE FROM ARC_FILE WHERE FILE_TYPE = 'D' AND NOT EXISTS 
                                  (select dbf_rid from arc_db_file
                                           where dbf_rid = file_rid);

DELETE FROM ARC_FILE WHERE NOT EXISTS (select fc_rid from arc_file_copy
                                         where fc_rid = file_rid);

DELETE FROM ARC_FILE_COPY WHERE NOT EXISTS (select file_rid from arc_file
                                              where file_rid = fc_rid);

DELETE FROM ARC_REQUEST WHERE (REQ_STATUS IN ('SU','UC','PA') AND
                               REQ_TYPE = 'AR'                AND
                               NOT EXISTS (select file_rid from arc_file
                                             where file_rid = req_rid));

DELETE FROM ARC_DB_FILE WHERE NOT EXISTS (select file_rid from arc_file
                                            where file_rid = dbf_rid);

DELETE FROM ARC_DIRECTORY WHERE NOT EXISTS (select file_rid from arc_file
                                              where file_rid = dir_rid);

DELETE FROM ARC_REQUEST WHERE (REQ_STATUS = 'SU' OR REQ_STATUS = 'UC') AND
                               REQ_TYPE = 'AR'                AND
                               NOT EXISTS (select file_rid from arc_file
                                             where file_rid = req_rid);

DELETE FROM ARC_SAVE_SET WHERE NOT EXISTS (select fc_rid from arc_file_copy
                                           where fc_rid = svst_rid and
                                              fc_vid = svst_vid);

DELETE FROM ARC_SAVE_SET WHERE NOT EXISTS (select req_rid from arc_request
                                           where req_rid = svst_rid and
                                           req_status in ('SU','UC','EX','PA'));

DELETE FROM ARC_PENDING_REQ WHERE NOT EXISTS (select req_rid from arc_request
                                              where req_rid = pend_rid and
                                              req_status in ('NE','EX','PA'));

{ --------------------------------------------------------------------------}
{ Update the vol_nb_svst (nb of save-sets in a volume) field in ARC_VOLUME. }
{ --------------------------------------------------------------------------}

UPDATE ARC_VOLUME SET VOL_NB_SVST = (select count (distinct fc_rid)
                                       from arc_file_copy
                                       where fc_vid = vol_vid 
                                         and fc_vno = vol_vno)
                  WHERE VOL_VID IN  (select vset_vid
                                       from   arc_vset
                                       where  vset_dev_driver = 'DISK');

UPDATE ARC_VOLUME SET VOL_FULL = 'N' 
                      , VOL_SPACE_EXACT = 'N'
                  WHERE  VOL_NB_SVST = 0 
                      OR VOL_SPACE_EXACT = 'N';

{ --------------------------------------------------------------------------}
{ Update the number of file copy field in ARC_FILE.                         }
{ --------------------------------------------------------------------------}

UPDATE ARC_FILE SET FILE_NB_COPIES  = (select count (distinct fc_vid)
                                         from arc_file_copy  
                                         where fc_rid = file_rid
                                           and fc_fno = file_fno);

{ --------------------------------------------------------------------------}
{ Reset all parent request without child to UNCOMPLETED                     }
{ --------------------------------------------------------------------------}

UPDATE ARC_REQUEST_VIEW SET REQ_STATUS = 'UC' WHERE REQ_STATUS = 'PA' AND
                          NOT EXISTS (select pend_parent_rid 
                                             from arc_pending_req 
                                             where pend_parent_rid = req_rid);

commit work;

close database;