REPLACE PROCEDURE SYSDBA.PR_Move_Space (OUT msg VARCHAR(3000)) /********************************************************************************************************* * * Author : Vinay Bagare * Date : 11/08/2005 * Purpose : Adjust the PERM Space to have 15% free space at any time. * Modification History : * *********************************************************************************************************/ BEGIN DECLARE l_Parent VARCHAR(30); DECLARE l_Child VARCHAR(30); DECLARE l_PCT_Avail INT; DECLARE l_PCT_Used INT; DECLARE l_MaxPerm DECIMAL(18,0); DECLARE l_CurrentPerm DECIMAL(18,0); DECLARE SQL_Text VARCHAR(2300); DECLARE l_Fsp DECIMAL(18,0); DECLARE l_Threshold DECIMAL(18,0); DECLARE l_50mb DECIMAL(18,0); SET l_Fsp = 15.0; SET l_50mb = 52428800.00; L1: FOR Cur1 AS CurCompVal CURSOR FOR SELECT Parent ,Child ,PCT_Avail ,Pct_Used ,MaxPerm ,CurrentPerm ,Update_Date ,Update_Time FROM SYSDBA.MOVE_SPACE_META WHERE MaxPerm >10000 AND CurrentPerm > 0 DO IF Cur1.PCT_Avail > l_Fsp AND Cur1.Parent = 'TEST_DW' THEN -- If free space is greather then 1 Gb then run thru the loop SET l_Threshold = Cur1.MaxPerm - (Cur1.CurrentPerm* 100.0/(100.0 - l_Fsp)); IF l_Threshold > (1024*1024*1024) THEN SET SQL_Text = 'CREATE DATABASE DUMMY FROM ' || Cur1.Child || ' AS PERM = ' || CAST(Cur1.MaxPerm - l_50mb - (Cur1.CurrentPerm* 100.0/(100.0 - l_Fsp)) as CHAR(30)) || ';'; CALL DBC.SYSEXECSQL (:SQL_TEXT); SET SQL_Text = 'GIVE DUMMY TO DW_DiskSpace; ' ; CALL DBC.SYSEXECSQL (:SQL_TEXT); SET SQL_Text = 'DROP DATABASE DUMMY; ' ; CALL DBC.SYSEXECSQL (:SQL_TEXT); ELSE SET MSG = 'SKIPPED'; END IF; ELSEIF Cur1.PCT_Avail > l_Fsp AND Cur1.Parent = 'TEST_BI' THEN SET l_Threshold = Cur1.MaxPerm - (Cur1.CurrentPerm* 100.0/(100.0 - l_Fsp)); IF l_Threshold > (1024*1024*1024) THEN SET SQL_Text = 'CREATE DATABASE DUMMY FROM ' || Cur1.Child || ' AS PERM = ' || CAST(Cur1.MaxPerm - l_50mb - (Cur1.CurrentPerm* 100.0/(100.0 - l_Fsp)) as CHAR(30)) || ';'; CALL DBC.SYSEXECSQL (:SQL_TEXT); SET SQL_Text = 'GIVE DUMMY TO BI_DiskSpace; ' ; CALL DBC.SYSEXECSQL (:SQL_TEXT); SET SQL_Text = 'DROP DATABASE DUMMY; ' ; CALL DBC.SYSEXECSQL (:SQL_TEXT); ELSE SET MSG = 'SKIPPED'; END IF; ELSEIF Cur1.PCT_Avail < l_Fsp AND Cur1.Parent = 'TEST_DW' THEN SET SQL_Text = 'CREATE DATABASE DUMMY FROM DW_DiskSpace AS PERM = ' || CAST((Cur1.CurrentPerm * 100.0/(100.0 - l_Fsp)) - Cur1.MaxPerm AS DECIMAL(18,2)) || ';' ; CALL DBC.SYSEXECSQL (:SQL_TEXT); SET SQL_Text = 'GIVE DUMMY TO '|| Cur1.Child || ';' ; CALL DBC.SYSEXECSQL (:SQL_TEXT); SET SQL_Text = 'DROP DATABASE DUMMY ;' ; CALL DBC.SYSEXECSQL (:SQL_TEXT); ELSEIF Cur1.PCT_Avail < l_Fsp AND Cur1.Parent = 'TEST_BI' THEN SET SQL_Text = 'CREATE DATABASE DUMMY FROM BI_DiskSpace AS PERM = ' || CAST((Cur1.CurrentPerm * 100.0/(100.0 - l_Fsp)) - Cur1.MaxPerm AS DECIMAL(18,2)) || ';' ; CALL DBC.SYSEXECSQL (:SQL_TEXT); SET SQL_Text = 'GIVE DUMMY TO '|| Cur1.Child || ';' ; CALL DBC.SYSEXECSQL (:SQL_TEXT); SET SQL_Text = 'DROP DATABASE DUMMY ;' ; CALL DBC.SYSEXECSQL (:SQL_TEXT); ELSE SET SQL_TEXT = ' ;' ; END IF; END FOR L1; END;