

--  ------------  Generated from [/big2/11.0/Build80_SP24/cxunix/source/CommServer/Db/Sp/MMGetSIDBsToReconstruct.sp] ---------- 

-- ----------------------------------------------------------------------
--
--           Copyright (c) 2007  CommVault Systems, Inc.
--                  All rights reserved.
--
--
--        This is unpublished proprietary source code of CommVault
--        Systems, Inc. The copyright notice above does not evidence
--        any actual or intended publication of such source code.
-- ----------------------------------------------------------------------*/
-- dataServer_h_rcsid[]="@(#)$Source: /cvs/cvsrepro/GX/vaultcx/Source/CommServer/Db/Sp/MMGetSIDBsToReconstruct.sp,v $ $Id: MMGetSIDBsToReconstruct.sp,v 1.14.88.5.56.1 2021/07/07 23:59:08 anarulkar Exp $";
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON


IF EXISTS (select * from sysobjects where name='MMGetSIDBsToReconstruct')
BEGIN
	print '>>> Drop Stored Procedure: MMGetSIDBsToReconstruct <<<'
	drop procedure MMGetSIDBsToReconstruct
END
IF EXISTS (select * from GxQscripts where name='MMGetSIDBsToReconstruct')
	delete from GxQscripts where name = 'MMGetSIDBsToReconstruct'
GO

IF EXISTS (select * from GXDBVersions where aliasname='MMGetSIDBsToReconstruct')
	delete from GXDBVersions where aliasname = 'MMGetSIDBsToReconstruct'
GO
print '... Creating Procedure: MMGetSIDBsToReconstruct'
GO
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON
GO
create procedure MMGetSIDBsToReconstruct
  @i_reserveParam integer
AS
-- Following are the "columns" returned, in the order in which they are returned
  DECLARE @o_SIDBStoreId integer
  DECLARE @o_SubStoreId integer
  DECLARE @o_CommCellId integer
  DECLARE @o_ClientId integer
  DECLARE @o_StoreFlags integer
  DECLARE @o_StoreStatus integer
  DECLARE @o_ArchGroupId integer
  DECLARE @o_CopyId integer
  DECLARE @o_CopyDeDupFlags integer
  DECLARE @o_CopyFlags integer
	declare @CorruptedSIDBs		TABLE (sidbStoreId int,
										SubStoreId int,
										CCId int,
										clientId int,
										storeFlags int,
										storeStatus int,
										archGroupId int,
										copyId int,
										copyDeDupeFlags int,
										copyFlags int)
	declare @tblRunningSubStores	TABLE (SIDBStoreId int, SubStoreId int)
	declare @tblRunningDDBSyncJobs	TABLE (JobId	int)
	DECLARE @handle INT;
	DECLARE @xmlText XML;
	DECLARE @allowReconstructForSealedStore	INTEGER
	SET @allowReconstructForSealedStore = 0
	DECLARE @disableSeperateReconsForSubstore INTEGER = 0
	SELECT @disableSeperateReconsForSubStore = value
	FROM	MMConfigs WITH (READUNCOMMITTED)
	WHERE	name = 'MMCONFIG_SEPERATE_RECON_FOR_SUBSTORE_DISABLED'
	SELECT	@allowReconstructForSealedStore = value
	FROM	MMConfigs WITH (READUNCOMMITTED)
	WHERE	name = 'MMCONFIG_ALLOW_SIDB_RECONSTRUCT_FOR_SEALEDSTORES'
	INSERT	INTO @CorruptedSIDBs
    SELECT	DISTINCT STORE.SIDBStoreId, STORE.SubStoreId, STORE.CommCellId, STORE.ClientId, STORE.flags, STORE.Status, 0, 0, 0, 0
	FROM 	IdxSIDBSubStore STORE WITH (READUNCOMMITTED),
			ArchCopySIDBStore CS WITH (READUNCOMMITTED),
			ArchGroupCopy AC WITH (READUNCOMMITTED),
			IdxSIDBStore ddb WITH (READUNCOMMITTED)
WHERE	(STORE.Version >= 2 AND STORE.Status = 1 AND (STORE.SealedTime = 0 OR (STORE.SealedTime > 0 AND @allowReconstructForSealedStore = 1)))
		AND STORE.SIDBStoreId = CS.SIDBStoreId AND STORE.CommCellId = CS.CommCellId AND CS.CopyId = AC.id AND STORE.SIDBStoreId = ddb.SIDBStoreId
AND ((AC.dedupeFlags & 67108864) > 0) AND ddb.flags & 1024 = 0
AND NOT EXISTS (SELECT * FROM IdxSIDBSubStore WITH (READUNCOMMITTED) WHERE SIDBStoreId = ddb.SIDBStoreId AND flags & 1024 = 1024)
		-- Check this flag outside this store procedure and log it correctly
		-- AND ((STORE.flags & IDX_SIDBSTORE_FLAGS_DDB_VALIDATION_FAILED) = 0)  --Ignore the DDBs which are currently failed in DDB validation phase
		AND STORE.ClientId > 1
    UPDATE	@CorruptedSIDBs
    SET		archGroupId = AC.archGroupId, copyId = AC.id, copyDeDupeFlags = AC.dedupeFlags, copyFlags = AC.flags
    FROM 	@CorruptedSIDBs SIDB,
		    ArchCopySIDBStore CS WITH (READUNCOMMITTED),
		    ArchGroupCopy AC WITH (READUNCOMMITTED)
    WHERE	SIDB.SIDBStoreId = CS.SIDBStoreId AND SIDB.CCId = CS.CommCellId AND CS.CopyId = AC.id
AND ((AC.dedupeFlags & 67108864) > 0)
	--
	-- Remove those rows whose reconstruction is happening right now.
	--
	IF @@ROWCOUNT > 0
	BEGIN
		INSERT INTO @tblRunningDDBSyncJobs
		SELECT jobId
		FROM JMJobInfo JMINFO WITH (READUNCOMMITTED)
		WHERE JMINFO.OpType = 80 /* DEDUPDBSYNC */
		DECLARE @jobId int
		DECLARE	jobList CURSOR FOR
		SELECT jobId FROM @tblRunningDDBSyncJobs
		OPEN jobList
		FETCH NEXT FROM jobList INTO @jobId
		WHILE @@FETCH_STATUS = 0
		BEGIN
			INSERT INTO @tblRunningSubStores
			SELECT	I.SIDBStoreId, T.t_substoreId
			FROM IdxSIDBSubStore I WITH (NOLOCK),
				(
					SELECT sb.value('@val', 'INT') AS t_substoreId
					FROM
					(
						select CAST(value AS XML) AS xmlstr
						from dbo.GetAllJobOptions(@jobId)
						where optionId=34449693 -- DEDUPDBSYNCOPTION__SUB_STORE_ID_LIST
					) AS O
					CROSS APPLY O.xmlstr.nodes ('SubStoreIdList') R(sb)
				) T
			WHERE I.Status = 1
			AND T.t_substoreId = I.SubStoreId
		FETCH NEXT FROM jobList INTO @jobId
		END
		CLOSE jobList
		DEALLOCATE jobList
		-- If seperate recons are disabled, then do not consider the store till first recon is completed.
		IF (@disableSeperateReconsForSubStore > 0)
		BEGIN
			DELETE @CorruptedSIDBs
			FROM @CorruptedSIDBs SIDBs,@tblRunningSubStores T
			WHERE SIDBs.sidbStoreId = T.SIDBStoreId
		END
		ELSE
		BEGIN
			DELETE @CorruptedSIDBs
			FROM @CorruptedSIDBs SIDBs,@tblRunningSubStores T
			WHERE SIDBs.sidbStoreId = T.SIDBStoreId
			AND SIDBs.SubStoreId = T.SubStoreId
		END
	END
	SELECT	TMP.SIDBStoreId,
			TMP.SubStoreId,
			TMP.CCId,
			TMP.ClientId,
			TMP.storeFlags,
			TMP.storeStatus,
			TMP.archGroupId,
			TMP.copyId,
			TMP.copyDeDupeFlags,
			TMP.copyFlags
	FROM 	@CorruptedSIDBs TMP
	Order by TMP.SIDBStoreId asc
GO

IF EXISTS (select * from GxQscripts where name = 'MMGetSIDBsToReconstruct')
	delete from GxQscripts where name = 'MMGetSIDBsToReconstruct'
GO

IF EXISTS (select * from GXDBVersions where aliasname='MMGetSIDBsToReconstruct')
	delete from GXDBVersions where aliasname = 'MMGetSIDBsToReconstruct'
GO

insert into GXDBVersions values(2, 'MMGetSIDBsToReconstruct',  'v1.14.88.5.56.1', 'MMGetSIDBsToReconstruct', 'v1.14.88.5.56.1')
GO

