

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

-- ----------------------------------------------------------------------
--
--           Copyright (c) 1998  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.
--	Author:      Durgesh Kumar Gupta
--	Date:        05/30/2018
--  Description: Computes the total saving when running synthetic full on the subclient and adds the subclient prop 'RunSFImmediately'
--				 with value as current time. The schedular triggers synthfull if not synthfull ran after the prop's time value.
-- ----------------------------------------------------------------------*/
-- dataServer_h_rcsid[]="@(#)$Source: /cvs/cvsrepro/GX/vaultcx/Source/CommServer/Db/Sp/SetFullConsolidationForAutomaticSchedule.sp,v $ $Id: SetFullConsolidationForAutomaticSchedule.sp,v 1.1.2.5.36.2 2021/07/17 01:41:16 dgupta Exp $";
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON


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

IF EXISTS (select * from GXDBVersions where aliasname='SetFullConsolidationForAutomaticSchedule')
	delete from GXDBVersions where aliasname = 'SetFullConsolidationForAutomaticSchedule'
GO
print '... Creating Procedure: SetFullConsolidationForAutomaticSchedule'
GO
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON
GO
create procedure SetFullConsolidationForAutomaticSchedule
AS
SET NOCOUNT ON
BEGIN
	DECLARE @attrName NVARCHAR(128) = N'RunSFImmediately'		-- Flag set in table APP_SubclienProps to denote that subclient need SF job immediately.
	DECLARE @created INT = dbo.GetUnixTime(GETUTCDATE())
	DECLARE @saveThresholdPercent FLOAT = ISNULL((SELECT CAST(value AS FLOAT) FROM GXGlobalParam WHERE name = 'FullConsolidationCutoffPercent' AND modified = 0), 49.5)	-- Only run Synthetic Full on subclients with data change greater than it
	DECLARE @minRunSFJobInterval INT =  7 * 24 * 60 * 60	-- Atleast wait for this duration before running SF to prevent back to back SF
	DECLARE @maxRunSFJobInterval INT =  90 * 24 * 60 * 60	-- Atmost wait for this duration before running SF to ensure atleast 1 SF runs in 90 days
	DECLARE @minSaveThresholdSize FLOAT = ISNULL((SELECT CAST(value AS FLOAT) FROM GXGlobalParam WITH (NOLOCK) WHERE name = 'FullConsolidationMinAppSizeGB' AND modified = 0), 1.0)	-- Only run Synthetic Full on subclients with application size of cycle & quota greater than it. Default size 1 GB
	SET @minSaveThresholdSize = @minSaveThresholdSize*1024*1024*1024
	IF object_id('tempdb.dbo.#BkpCycleStat') is not null
		DROP TABLE #BkpCycleStat
	CREATE TABLE #BkpCycleStat ( appId INT PRIMARY KEY, lastBkpCycle INT)
	INSERT INTO #BkpCycleStat
	SELECT appId, MAX(fullCycleNum)
	FROM JMBkpStats WITH (NOLOCK)
	GROUP BY appId
	IF object_id('tempdb.dbo.#savingPerBackupSetList') is not null
		DROP TABLE #savingPerBackupSetList
	CREATE TABLE #savingPerBackupSetList (BSId INT PRIMARY KEY)
	-- create a temp table to store the qualified subclients list
	IF object_id('tempdb.dbo.#qualifiedSubclients') is not null
		DROP TABLE #qualifiedSubclients
	CREATE TABLE #qualifiedSubclients (subclientID INT PRIMARY KEY)
	-- create a temp table to store the clients with subclients level index db enabled
	IF object_id('tempdb.dbo.#clientWithSCIdxDb') is not null
		DROP TABLE #clientWithSCIdxDb
	CREATE TABLE #clientWithSCIdxDb (clientId INT PRIMARY KEY)
	-- create a temp table to store the clients with backupset level index db enabled
	IF object_id('tempdb.dbo.#clientWithBSIdxDb') is not null
		DROP TABLE #clientWithBSIdxDb
	CREATE TABLE #clientWithBSIdxDb (clientId INT PRIMARY KEY)
	-- Determine which clients are on subclient level index db
	INSERT INTO #clientWithSCIdxDb
	SELECT DISTINCT CL2.id Id
	FROM APP_IDAName IDA2 WITH (NOLOCK)
	JOIN APP_IDAProp IDAP2 WITH (NOLOCK) ON IDAP2.componentNameId=IDA2.id
	JOIN APP_Client CL2  WITH (NOLOCK) ON CL2.id = IDA2.clientId
	WHERE IDA2.appTypeId in (1,11,12,17,18,19,20,21,29,33,34,36,38,42,43,63,74) -- Filesystem iDA types
	AND IDAP2.attrName = 'Subclient Index' AND IDAP2.attrVal=1
	-- Determine which clients are on backupset level index db
	INSERT INTO #clientWithBSIdxDb
	SELECT DISTINCT CL1.id Id
	FROM APP_IDAName IDA1 WITH (NOLOCK)
	JOIN APP_Client CL1  WITH (NOLOCK) ON CL1.id = IDA1.clientId
	WHERE IDA1.appTypeId in (1,11,12,17,18,19,20,21,29,33,34,36,38,42,43,63,74) -- Filesystem iDA types
	EXCEPT
	SELECT CL2.clientId Id
	FROM #clientWithSCIdxDb CL2
	-- Qualify backupsets with backup set level index db
	INSERT INTO #savingPerBackupSetList
	SELECT Subclient.backupSet BSId
	FROM APP_Application Subclient WITH (NOLOCK)
	JOIN #clientWithBSIdxDb Client WITH (NOLOCK) ON Client.clientId=Subclient.clientId
	JOIN JMBkpStats jobStats WITH (NOLOCK) ON jobStats.appId = Subclient.id
	JOIN #BkpCycleStat LastBkpTable ON LastBkpTable.appId=Subclient.id AND jobStats.fullCycleNum = LastBkpTable.lastBkpCycle
	JOIN APP_BackupSetProp BSProp WITH (NOLOCK) on Subclient.backupset = BSProp.componentnameid
	WHERE jobStats.status in (1,3,14) and BSProp.attrName = 'Application Size' AND BSProp.modified=0
	GROUP BY Subclient.backupSet
	having ((((SUM(jobStats.totalUncompBytes) - SUM(CAST(BSProp.attrVal AS FLOAT))/count(jobStats.appId) )))*100  > @saveThresholdPercent*CAST(SUM(jobStats.totalUncompBytes)AS FLOAT))
			AND (SUM(jobStats.totalUncompBytes) > @minSaveThresholdSize) AND ( (SUM(CAST(BSProp.attrVal AS FLOAT))/count(jobStats.appId)) > @minSaveThresholdSize)
	order by Subclient.backupSet
	DROP TABLE #clientWithBSIdxDb
	-- Qualify subclient with subclient level index db
	INSERT INTO #qualifiedSubclients
	SELECT Subclient.id SCId
	FROM APP_Application Subclient WITH (NOLOCK)
	JOIN #clientWithSCIdxDb Client WITH (NOLOCK) ON Client.clientId=Subclient.clientId
	JOIN JMBkpStats jobStats WITH (NOLOCK) ON jobStats.appId = Subclient.id
	JOIN #BkpCycleStat LastBkpTable ON LastBkpTable.appId=Subclient.id AND jobStats.fullCycleNum = LastBkpTable.lastBkpCycle
	JOIN APP_SubClientProp SCProp WITH (NOLOCK) on Subclient.id = SCProp.componentnameid
	WHERE jobStats.status in (1,3,14) and SCProp.attrName = 'Application Size' AND SCProp.modified=0 AND SCProp.cs_attrName = CHECKSUM(N'Application Size')
	GROUP BY Subclient.id
	HAVING (((SUM(jobStats.totalUncompBytes) - SUM(CAST(SCProp.attrVal AS FLOAT))/count(jobStats.appId) )))*100  > @saveThresholdPercent*CAST(SUM(jobStats.totalUncompBytes)AS FLOAT)
			AND (SUM(jobStats.totalUncompBytes) > @minSaveThresholdSize) AND ( (SUM(CAST(SCProp.attrVal AS FLOAT))/count(jobStats.appId)) > @minSaveThresholdSize)
	DROP TABLE #clientWithSCIdxDb
	-- Qualify vsa subclients
	INSERT INTO #qualifiedSubclients
	SELECT Subclient.id SCId
	FROM APP_Application Subclient WITH (NOLOCK)
	JOIN JMBkpStats jobStats WITH (NOLOCK) ON jobStats.appId = Subclient.id
	JOIN #BkpCycleStat LastBkpTable ON LastBkpTable.appId=Subclient.id AND jobStats.fullCycleNum = LastBkpTable.lastBkpCycle
	WHERE jobStats.status in (1,3,14)
	GROUP BY Subclient.id
	HAVING (((SUM(jobStats.totalUncompBytes) - (SELECT SUM(CAST(VMProp.attrVal AS FLOAT)) FROM APP_VMProp VMProp WITH (NOLOCK) WHERE VMProp.attrName='vmGuestSize' AND VMProp.jobId=MAX(jobStats.jobId)))))*100  > @saveThresholdPercent*CAST(SUM(jobStats.totalUncompBytes)AS FLOAT)
			AND (SUM(jobStats.totalUncompBytes) > @minSaveThresholdSize)
	DROP TABLE #BkpCycleStat
	-- remove subclient which hasn't waited for minRunSFJobInterval i.e. 7 weeks from last Full or SF job
	-- or subclient which hasn't had a single run of incremental / differential till now from last full or synthfull
	DELETE FROM #qualifiedSubclients
	WHERE subclientID in
		(
			SELECT SC.subclientID
			FROM #qualifiedSubclients SC
			JOIN JMBkpStats jobStatsFull WITH (NOLOCK) ON jobStatsFull.appId = SC.subclientID AND jobStatsFull.bkpLevel in (1, 64, 128, 16384) AND jobStatsFull.status IN (1, 3, 14) and jobStatsFull.dataStatus =0  --Full or SF or SF after & before with status as  JMSUCCESS, PARTIALSUCCESS, JMSUCCESSWITHWARNINGS
			JOIN JMBkpStats jobStatsInc WITH (NOLOCK) ON jobStatsInc.appId = SC.subclientID AND jobStatsInc.bkpLevel in (2,4) AND jobStatsInc.status IN (1, 3, 14) and jobStatsInc.dataStatus =0  --INCR , DIFFERENTIAL with status as  JMSUCCESS, PARTIALSUCCESS, JMSUCCESSWITHWARNINGS
			GROUP BY SC.subclientID
			HAVING ( MAX(jobStatsFull.servEndDate) + @minRunSFJobInterval) > @created
				OR (MAX(jobStatsFull.servEndDate) > MAX(jobStatsInc.servEndDate))
		)
	-- convert qualified backupsets into subclients
	INSERT INTO #qualifiedSubclients
	SELECT T.subClientId
	FROM (	SELECT DISTINCT SC.id subclientId
			FROM #savingPerBackupSetList List
			JOIN APP_Application SC WITH (NOLOCK) ON List.BSId=SC.backupSet
			JOIN JMBkpStats jobStatsFull WITH (NOLOCK) ON jobStatsFull.appId = SC.id AND jobStatsFull.bkpLevel in (1, 64, 128, 16384) AND jobStatsFull.status IN (1, 3, 14) and jobStatsFull.dataStatus =0  --Full or SF or SF after & before with status as  JMSUCCESS, PARTIALSUCCESS, JMSUCCESSWITHWARNINGS
			JOIN JMBkpStats jobStatsInc WITH (NOLOCK) ON jobStatsInc.appId = SC.id AND jobStatsInc.bkpLevel in (2,4) AND jobStatsInc.status IN (1, 3, 14) and jobStatsInc.dataStatus =0  --INCR , DIFFERENTIAL with status as  JMSUCCESS, PARTIALSUCCESS, JMSUCCESSWITHWARNINGS
			GROUP BY SC.id
			HAVING ( MAX(jobStatsFull.servEndDate) + @minRunSFJobInterval) <= @created
				AND (MAX(jobStatsFull.servEndDate) < MAX(jobStatsInc.servEndDate))
		UNION
		-- Set the subclient property also for subclient which didn't have any FULL or SYNTHETIC FULL job ran in past 90 days
			SELECT DISTINCT jobStats.appId subclientId
			FROM JMBkpStats jobStats WITH (NOLOCK)
			WHERE jobStats.bkpLevel in (1, 64, 128, 16384) AND status IN (1, 3, 14) and dataStatus =0  --Full or SF or SF after & before with status as  JMSUCCESS, PARTIALSUCCESS, JMSUCCESSWITHWARNINGS
			GROUP BY jobStats.appId
			HAVING (MAX(jobStats.servEndDate) + @maxRunSFJobInterval) <= @created
		EXCEPT
			SELECT subclientID
			FROM #qualifiedSubclients
		) T
	DROP TABLE #savingPerBackupSetList
	-- create a temp table to store the qualified subclients list
	IF object_id('tempdb.dbo.#updatedSCPropList') is not null
		DROP TABLE #updatedSCPropList
	CREATE TABLE #updatedSCPropList (subclientID INT PRIMARY KEY)
	-- Set SubClients Props to run SF immediately which are qualified
	UPDATE APP_SubClientProp SET attrVal=@created , modified = 0
	OUTPUT INSERTED.componentNameId INTO #updatedSCPropList
	WHERE attrName=@attrName AND cs_attrName = CHECKSUM(@attrName) AND componentNameId IN (SELECT subclientID FROM #qualifiedSubclients)
	INSERT INTO APP_SubClientProp(componentNameId,attrName,attrType,attrVal,created,modified,ccpId)
	SELECT QSC.subclientID,@attrName,1,@created,@created,0,0
	FROM #qualifiedSubclients QSC
	WHERE QSC.subclientID NOT IN (SELECT subclientID FROM #updatedSCPropList)
	DROP TABLE #qualifiedSubclients
	DROP TABLE #updatedSCPropList
END
SET NOCOUNT OFF
GO
-- Tell the AWK processor that there are no more input lines to scan

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

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

insert into GXDBVersions values(2, 'SetFullConsolidationForAutomaticSchedule',  'v1.1.2.5.36.2', 'SetFullConsolidationForAutomaticSchedule', 'v1.1.2.5.36.2')
GO

