

--  ------------  Generated from [/big2/11.0/Build80_SP24/cxunix/source/CommServer/Db/Sp/ArchOnJobMaterialized.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.
-- ----------------------------------------------------------------------*/
-- dataServer_h_rcsid[]="@(#)$Source: /cvs/cvsrepro/GX/vaultcx/Source/CommServer/Db/Sp/ArchOnJobMaterialized.sp,v $ $Id: ArchOnJobMaterialized.sp,v 1.26.2.21.4.1 2021/06/16 23:43:08 bzhu Exp $";
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON


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

IF EXISTS (select * from GXDBVersions where aliasname='ArchOnJobMaterialized')
	delete from GXDBVersions where aliasname = 'ArchOnJobMaterialized'
GO
print '... Creating Procedure: ArchOnJobMaterialized'
GO
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON
GO
create procedure ArchOnJobMaterialized
  @i_commcellId int,
  @i_jobId int,
  @i_snapTapeJobId int
AS
  DECLARE @o_errorCode integer;
  DECLARE @o_sqlError integer;
--This will turn off message: "xxx rows affected".
SET NOCOUNT ON
DECLARE @l_Error int, @l_ErrorCode int, @l_MatJobId int
SELECT  @l_Error = 0, @l_ErrorCode = 0, @l_MatJobId = @i_snapTapeJobId
-- even if the commcellId of the job to materialize is from
-- a different commcell, all the snap to tape jobIds are
-- from the current commcell
--SELECT @l_MatJobId = max(WF.childJobId)
--FROM JMJobWF WF INNER JOIN JMJobInfo J ON WF.childJobId = J.jobId AND J.opType = 60
--WHERE WF.processedJobId = @i_jobId AND WF.commcellId = @i_commcellId
SET @l_Error = @@ERROR
IF (@l_Error <> 0)
BEGIN
	SET @l_ErrorCode  = 2
	GOTO FINISH
END
IF (@l_MatJobId = 0)
BEGIN
	SET @l_ErrorCode  = 1
	GOTO FINISH
END
DECLARE @l_AppId int
-- now get the distinct filetypes
DECLARE @lt_fileTypes TABLE (filetype int, hasStats int)
INSERT INTO @lt_fileTypes
SELECT DISTINCT fileType, 0
FROM ArchFile
WHERE jobId = @l_MatJobId AND commCellId = 2
-- note - please do not make the above statement read uncommitted data.
-- we will be locking all affected rows exclusively anyway in the next statement (update)
-- if anything, escalate the lock here in this select to an UPDLCK already rather than
-- reducing the lock in case this query is holding something (it shouldn't).
SET @l_Error = @@ERROR
IF (@l_Error <> 0)
BEGIN
	SET @l_ErrorCode  = 7
	GOTO FINISH
END
IF (0 = (SELECT COUNT(*) FROM @lt_fileTypes ))
BEGIN
	SET @l_ErrorCode  = 6
	GOTO FINISH
END
-- figure out the copy to create the JMJobDataStatsEntries in
DECLARE @l_AG int, @l_AGC int
SELECT TOP 1 @l_AG  = archGroupId
FROM ArchFile
WHERE CommcellId = @i_commcellId AND jobId = @i_jobId
IF (@l_AG IS NULL OR @l_AG = 0)
BEGIN
	SELECT TOP 1 @l_AG = archGroupId
	FROM archFile
	WHERE CommCellId = @i_commcellId AND jobId = @l_MatJobId
END
SELECT @l_AGC = defaultCopy
FROM ArchGroup
WHERE id = @l_AG
IF (@l_AGC is null)
BEGIN
	SET @l_ErrorCode  = 8
	GOTO FINISH
END
-- update totalWriteTime, totalCompBytes, totalUncompBytes, totalBackupSize, percentageChange and nwTransBytes of JMBkpStats table
DECLARE @l_appTypeId	int = 0
DECLARE @l_jobStatus	int = 0
DECLARE @l_writeTime	int = 0
DECLARE @l_compSize		bigint = 0
DECLARE @l_uncompSize	bigint = 0
DECLARE @l_backupSize	bigint = 0
DECLARE @l_nwTransBytes	bigint = 0
DECLARE @l_bkpLevel		int = 0
DECLARE @l_snapStartTime int = 0
DECLARE @l_snapEndTime int = 0
DECLARE @l_prevJobUncomp bigint = 0
DECLARE @l_percentageChange bigint = 0
DECLARE @l_jobDataStatsStatus		int = 100
DECLARE @l_disabled		int = 0
DECLARE @l_noOfObjects  int = 0
SELECT	@l_backupSize = SUM(AFC.physicalSize)
FROM	archFileCopy AFC, archFile AF
WHERE	AF.jobId = @l_MatJobId AND AF.commCellId = 2
	AND AFC.archFileId = AF.id AND AFC.commCellid = AF.commCellId AND AFC.archCopyId = @l_AGC
	AND AF.isValid = 1
SET @l_Error = @@ERROR
IF (@l_Error <> 0)
BEGIN
	SET @l_ErrorCode  = 11
	GOTO FINISH
END
SELECT	@l_jobStatus = status, @l_appTypeId = appType, @l_writeTime = totalWriteTime, @l_compSize = totalCompBytes,
		@l_uncompSize = totalUncompBytes, @l_nwTransBytes = nwTransBytes, @l_bkpLevel = bkpLevel, @l_noOfObjects = totalNumOfFiles
FROM	JMBkpStats
WHERE	jobId = @l_MatJobId AND commcellId = 2
SET @l_Error = @@ERROR
IF (@l_Error <> 0)
BEGIN
	SET @l_ErrorCode  = 12
	GOTO FINISH
END
IF	@l_backupSize IS NULL
	SET @l_backupSize = 0
IF	@l_backupSize > 0
BEGIN
	UPDATE	JMBkpStats
	SET		totalBackupSize = @l_backupSize
	WHERE	jobId = @l_MatJobId AND commcellId = @i_commcellId
	SET @l_Error = @@ERROR
	IF (@l_Error <> 0)
	BEGIN
		SET @l_ErrorCode  = 13
		GOTO FINISH
	END
END
SELECT  @l_snapStartTime = servStartDate, @l_snapEndTime = servEndDate, @l_AppId = appId
FROM	JMBkpStats
WHERE	jobId = @i_jobId AND commcellId = @i_commcellId
SELECT	TOP 1 @l_prevJobUncomp = totalUncompBytes
FROM	JMBkpStats
WHERE	appId = @l_AppId AND bkpLevel = @l_bkpLevel AND status = 1
	AND opType IN (59, 60) AND servStartDate < @l_snapStartTime
ORDER BY servStartDate DESC
/*
Do not update job stats for appaware if value is 0. But for other jobs update even if 0
so that incremental jobs that do not have any content to backup will show application size as 0
*/
DECLARE @l_isAppAwareJob BIT = 0
SELECT @l_isAppAwareJob = 1
FROM JMVSAAppJobLink JVSA WITH(NOLOCK) WHERE JVSA.childJobId = @i_jobId AND JVSA.commcellId = @i_commcellId
IF	(@l_prevJobUncomp > 0) AND ( (@l_uncompSize > 0) OR (@l_isAppAwareJob = 0) )
	SET	@l_percentageChange = ((@l_uncompSize - @l_prevJobUncomp)*100)/@l_prevJobUncomp
ELSE
	SET	@l_percentageChange = -200
IF	@l_percentageChange > 2000000000
	SET	@l_percentageChange = 2000000000
ELSE
IF	@l_percentageChange < -2000000000
	SET	@l_percentageChange = -2000000000
IF(@l_isAppAwareJob = 1)
	UPDATE	JMBkpStats SET totalWriteTime = CASE WHEN @l_writeTime > 0 THEN @l_writeTime ELSE totalWriteTime END,
			totalCompBytes = CASE WHEN @l_compSize > 0 THEN @l_compSize ELSE totalCompBytes END,
			totalUncompBytes = CASE WHEN @l_uncompSize > 0 THEN @l_uncompSize ELSE totalUncompBytes END,
			totalBackupSize = CASE WHEN @l_backupSize > 0 THEN @l_backupSize ELSE totalBackupSize END,
			percentageChange = @l_percentageChange,
			nwTransBytes = CASE WHEN @l_nwTransBytes > 0 THEN @l_nwTransBytes ELSE nwTransBytes END,
			totalNumOfFiles = CASE WHEN @l_noOfObjects > 0 THEN @l_noOfObjects ELSE totalNumOfFiles END
	WHERE	jobId = @i_jobId AND commcellId = @i_commcellId
ELSE
	UPDATE  JMBkpStats
	SET	totalWriteTime = @l_writeTime, totalCompBytes = @l_compSize,
       	-- for Oracle and Oracle RAC block level snap backup, use logical size from snap backup
totalUncompBytes = CASE WHEN (appType = 22 OR appType = 80) AND opType = 59 /* SNAPBACKUP */ AND bkpAttributesEx & 0x10 = 0x10 THEN totalUncompBytes ELSE @l_uncompSize END,
		totalBackupSize = @l_backupSize,
       	percentageChange = @l_percentageChange, nwTransBytes = @l_nwTransBytes, totalNumOfFiles = @l_noOfObjects
    WHERE   jobId = @i_jobId AND commcellId = @i_commcellId
SET @l_Error = @@ERROR
IF (@l_Error <> 0)
BEGIN
	SET @l_ErrorCode  = 14
	GOTO FINISH
END
-- change the jobIds
UPDATE	archFile
SET		jobId = @i_jobId
WHERE	jobId = @l_MatJobId AND commcellId = @i_commcellId
SET @l_Error = @@ERROR
IF (@l_Error <> 0)
BEGIN
	SET @l_ErrorCode  = 5
	GOTO FINISH
END
UPDATE	archChunkMapping
SET		jobId = @i_jobId
WHERE	jobId = @l_MatJobId AND commcellId = @i_commcellId
SET @l_Error = @@ERROR
IF (@l_Error <> 0)
BEGIN
	SET @l_ErrorCode  = 19
	GOTO FINISH
END
IF @l_appTypeId IN (22, 80)
	UPDATE	archFileOracle
	SET		jobId = @i_jobId
	WHERE	jobId = @l_MatJobId AND commcellId = @i_commcellId
ELSE
IF @l_appTypeId = 61
	UPDATE	archFileSAP
	SET		jobId = @i_jobId
	WHERE	jobId = @l_MatJobId AND commcellId = @i_commcellId
SET @l_Error = @@ERROR
IF (@l_Error <> 0)
BEGIN
	SET @l_ErrorCode  = 18
	GOTO FINISH
END
-- check if JMJobDataStats table exists for the fileTypes
UPDATE	@lt_fileTypes
SET		hasStats = 1
FROM	@lt_fileTypes FT INNER JOIN JMJobDataStats J WITH (READUNCOMMITTED)  ON J.jobId = @i_jobId AND J.commCellId = @i_commcellId AND J.dataType = FT.fileType INNER JOIN archGroupCopy AGC WITH (READUNCOMMITTED) ON J.archGrpCopyId = AGC.Id AND AGC.archGroupId = @l_AG  AND AGC.isSnapCopy = 0
SET @l_Error = @@ERROR
IF (@l_Error <> 0)
BEGIN
	SET @l_ErrorCode  = 17
	GOTO FINISH
END
-- if the job is oracle or oracle RAC and snap to tape job status is partially completed then set the job status as 102 in JMJobDataStats table
-- By doing this, job will not be considered for extended retention, aux copy etc
/*IF (@l_appTypeId IN (22, 80) AND @l_jobStatus = 3)
BEGIN
	SET @l_jobDataStatsStatus = 102
	SET @l_disabled	= 1
END
*/
-- now actually create the JMJobDataStats entries in the primary
DECLARE @l_curTimeUTC int
SET @l_curTimeUTC  = dbo.getunixtime(getutcdate())
-- if stats entry exists which might happen if the archFile invaliidation/ deletion might have failed for the snap job with inline bkp copy, delete those entries as they will be created from archJobDataStats create anyways
IF EXISTS (SELECT * FROM @lt_fileTypes WHERE hasStats = 1)
	DELETE JMJobDataStats
	FROM	JMJobDataStats J
	    INNER JOIN  @lt_fileTypes FT ON J.jobId = @i_jobId AND J.commCellId = @i_commcellId AND J.dataType = FT.fileType AND J.status IN (1000, 2, 3)
		AND FT.hasStats = 1
		INNER JOIN archGroupCopy AGC WITH (READUNCOMMITTED) ON J.archGrpCopyId = AGC.Id AND AGC.archGroupId = @l_AG  AND AGC.isSnapCopy = 0
SET @l_Error = @@ERROR
IF (@l_Error <> 0)
BEGIN
	SET @l_ErrorCode  = 16
	GOTO FINISH
END
-- now set the job to materialized
update JMJobSnapShotStats
SET materializationStatus = 100, materializationJobId = @l_MatJobId
WHERE CommcellId = @i_commcellId AND jobId = @i_jobId
SET @l_Error = @@ERROR
IF (@l_Error <> 0)
BEGIN
	SET @l_ErrorCode  = 10
	GOTO FINISH
END
UPDATE	JMJobSnapShotStats
SET		materializationStatus = 100, materializationJobId = @l_MatJobId
FROM	JMJobSnapShotStats SS, JMPreparedJob PJ
WHERE	SS.CommcellId = PJ.commCellId AND SS.JobId = PJ.JobId
	AND	PJ.preparedJobCCId = @i_commcellId AND PJ.preparedJobId = @i_jobId
--VSA - populate archfile link for Child backup copy job
DECLARE @parentJobId int = 0
SELECT @parentJobId=parentJobId FROM JMVSAAppJobLink WITH(NOLOCK) WHERE childJobId = @i_jobId AND commcellId = @i_commcellId
IF @parentJobId <> 0
BEGIN
	DECLARE @currentDataAF int
	DECLARE @ParentIndexAF int
	DECLARE @l_childClientId int = 0
	DECLARE @VMClientGUID NVARCHAR(2048)
	--Get the latest index AF for the parent job
	SELECT top 1 @ParentIndexAF = id
	FROM ArchFile AF WITH(NOLOCK) WHERE jobId = @parentJobId AND commcellId =  @i_commcellId AND isValid = 1 AND fileType = 2
	ORDER BY cTime desc
	SELECT @l_childClientId = clientId FROM App_Application WITH(NOLOCK) WHERE id = @l_AppId
	/*
	For VSA V1 @parentJobId we have will be the VSA pseudoclient jobId
	but for VSA V2 @parentJobId we got from JMVSAAppJobLink is the VM level job, so we gotta go up one level to find the VSA pseudoclient jobId.
	So assigning @parentJobId to @VSAPseudoClientParentJobId first. If it is VSA V2 it will get overwritten in the next query on JMJobDataLink
	*/
	DECLARE @VSAPseudoClientParentJobId INT = @parentJobId
	/*For V1 this won't find any row*/
	SELECT @VSAPseudoClientParentJobId = JDL.parentJobId
	FROM JMJobDataLink JDL WITH(NOLOCK)
	WHERE JDL.childJobId = @parentJobId AND JDL.commcellId = @i_commcellId
AND JDL.linkType IN (7, 8)
	SELECT @VMClientGUID = VMP.attrVal
	FROM App_VMProp VMP
	WHERE  VMP.jobId = @VSAPseudoClientParentJobId AND VMP.commcellId = @i_commcellId AND VMP.VMClientId = @l_childClientId AND VMP.attrName = N'vmGUID'
	IF @VMClientGUID IS NULL
	BEGIN
		--GUID gets updated for a VM client and so modified timestamp is set on previous GUID. Pick GUID based on the job time we have for the VM.
		SELECT @VMClientGUID = attrVal FROM App_ClientProp WITH(NOLOCK) WHERE attrName = N'Virtual Machine Instance UUID' AND componentNameId = @l_childClientId AND @l_snapEndTime >= created AND (@l_snapEndTime < modified OR modified = 0)
	END
	IF @VMClientGUID IS NULL
	BEGIN
		RAISERROR('Unable to find VMGUID for the Client Id %d', 16, 1, @l_childClientId)
		SET @l_ErrorCode  = 21
		GOTO FINISH
	END
	INSERT INTO archVSAAppAFLink (ParentIndexAFId, childAFId, commCellId, flags, VMGUID, mountVMGUID, CopyId)
	SELECT distinct @ParentIndexAF, AFC.archFileId,@i_commcellId,0,@VMClientGUID,NULL,@l_AGC
	from archFileCopy AFC WITH (NOLOCK)
WHERE AFC.archCopyId=@l_AGC AND AFC.archFileId in (select id from archfile where jobId= @i_jobId AND commcellId = @i_commcellId AND isValid = 1  AND (Flags & 2) = 2)
	SET @l_Error = @@ERROR
	IF (@l_Error <> 0)
	BEGIN
		SET @l_ErrorCode  = 20
		GOTO FINISH
	END
END
FINISH:
SELECT @l_ErrorCode, @l_Error
RETURN;
GO

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

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

insert into GXDBVersions values(2, 'ArchOnJobMaterialized',  'v1.26.2.21.4.1', 'ArchOnJobMaterialized', 'v1.26.2.21.4.1')
GO

