/* ** ** This script should run as is to install the ADWStatsRefresh process. The 'admin' database name ** used is 'DBADMIN'. If you want to install into another admin DB, then repalce DBADMIN with the ** database name you desire. ** ** All tables, views and the SPL Proc go into the same DB. Again, if you have different ** DB's for views, etc. modify accordingly. ** ** If you are on Pre TD13, the SPL PROC is written with Dynamic Execution so the DB you use will ** have to have the right permissions to the target DB's/Table's that you are colllecting stats on. ** Usually DBA's have a 'master' ID they use to create all DB/USER accounts and assign privileges. ** In this case DBADMIN is that User. ** ** If there are any question, things I left out, etc. You can contact me at the following: ** ** JK Wight ** john_wight@swifttrans.com ** ** Good luck and if you make any additions, update functionality, etc. please be considerate ** to share with others and from whence this came;-) Thank you. ** ** JK - 2010-11-24 */ **------------------------------------------------------------------------------ ** Temporary table that will collect the pertinent information in the qualifying step ** of the AWDStats Process. Table is then used in the CURSOR loop logic to process each table ** for the generation and execution of the COLLECT STATS and meta data processing ** CREATE SET GLOBAL TEMPORARY TABLE dbadmin.GT_ADWStats, NO FALLBACK PROTECTION, LOG, CHECKSUM = DEFAULT ( DatabaseName CHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL FORMAT 'X(30)', TableName CHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL FORMAT 'X(30)', ADWStatsPerc BYTEINT FORMAT '-(3)9', CurrentDTTM TIMESTAMP(0) FORMAT 'YYYY-MM-DDBHH:MI:SS', OldestCollectDtTm TIMESTAMP(0) FORMAT 'YYYY-MM-DDBHH:MI:SS', PrevCurrentPerm FLOAT FORMAT '-9.99999999999999E-999', CurrCurrentPerm FLOAT FORMAT '-9.99999999999999E-999', SpaceChgPct FLOAT FORMAT '-9.99999999999999E-999', StatsReason CHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC FORMAT 'X(5)' ) UNIQUE PRIMARY INDEX UPI_GT_ADWStats ( DatabaseName, TableName ) ON COMMIT PRESERVE ROWS; **------------------------------------------------------------------------------ ** Table is a main control table that allows the process to check on the last time stats was collected ** If a table qualifies, the data in UpSerted at the end of the process to seed the info for the ** next run. There will be one and only one row for each table that qualifies for the process ** Table does NOT have to be seeded like the driver table - ASWStats_Table ** CREATE SET TABLE dbadmin.ADWStats_Control_Table, NO FALLBACK PROTECTION, NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM = DEFAULT ( DatabaseName CHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL FORMAT 'X(30)', TableName CHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL FORMAT 'X(30)', StatsRunDTTM TIMESTAMP(0) FORMAT 'YYYY-MM-DDBHH:MI:SS', CurrentPerm FLOAT FORMAT '-9.99999999999999E-999', CollectCount SMALLINT FORMAT '-(5)9' ) UNIQUE PRIMARY INDEX UPI_ADWStats_Control_Table ( DatabaseName, TableName ); COMMENT ON TABLE dbadmin.ADWStats_Control_Table IS 'Control table for the tables that have statitics collected on them during a DBADMIN cycle run periodically on selected base table databases. There will be only one row for table and it iwll be updated each time stats are collected on it.'; COMMENT ON COLUMN dbadmin.ADWStats_Control_Table.DatabaseName IS 'Database Name of a base table database that will be used to generate collect statistics in the DBADMINCollect process.'; COMMENT ON COLUMN dbadmin.ADWStats_Control_Table.TableName IS 'Table Name of a base table that will be used to generate collect statistics in the DBADMINCollect process.'; COMMENT ON COLUMN dbadmin.ADWStats_Control_Table.StatsRunDTTM IS 'The date/time the DBADMIN Collect process was executed'; COMMENT ON COLUMN dbadmin.ADWStats_Control_Table.CurrentPerm IS 'Perm space as of the last time Stats was run.'; COMMENT ON COLUMN dbadmin.ADWStats_Control_Table.CollectCount IS 'A count of the number of times stats were collected on the table during the period. This will be cleared at the end of each period - could be week, month, etc.'; CREATE VIEW dbadmin.v_ADWStats_Control_Table ( DatabaseName, TableName, StatsRunDTTM, CurrentPerm, CollectCount ) AS LOCKING ROW FOR ACCESS SELECT DatabaseName, TableName, StatsRunDTTM, CurrentPerm, CollectCount FROM DBADMIN.ADWStats_Control_Table WITH CHECK OPTION; **------------------------------------------------------------------------------ ** ** Table provides a summary of an ADW Stats Run of when it occured, the number of tables that qualified and time it took to run the collection process ** CREATE SET TABLE dbadmin.ADWStats_Summary, NO FALLBACK PROTECTION, NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM = DEFAULT ( StatsRunDTTM TIMESTAMP(0) NOT NULL FORMAT 'YYYY-MM-DDBHH:MI:SS', StatsRunEndDTTM TIMESTAMP(0) FORMAT 'YYYY-MM-DDBHH:MI:SS', TableCollectQty SMALLINT NOT NULL FORMAT '-(5)9', TableCollectTm INTERVAL MINUTE(2) TO SECOND(6) ) UNIQUE PRIMARY INDEX UPI_ADWStats_Summary ( StatsRunDTTM ); COMMENT ON TABLE dbadmin.ADWStats_Summary IS 'Identity column provides unique PK/PI for summare table'; COMMENT ON COLUMN dbadmin.ADWStats_Summary.StatsRunDTTM IS 'The date/time the DBADMIN Collect process was executed'; COMMENT ON COLUMN dbadmin.ADWStats_Summary.TableCollectQty IS 'Th elapse time (hours, minutes, seconds) it took for the process to run'; CREATE VIEW dbadmin.v_ADWStats_Summary ( StatsRunDTTM, StatsRunEndDTTM, TableCollectQty, TableCollectTm ) AS LOCKING ROW FOR ACCESS SELECT StatsRunDTTM, StatsRunEndDTTM, TableCollectQty, TableCollectTm FROM DBADMIN.ADWStats_Summary WITH CHECK OPTION; **------------------------------------------------------------------------------ ** ** This Table is the DRIVER to qualify what Databases and tables wouold apply to th eprocess. ** If not present in this table, then they will never qualify. ** CREATE SET TABLE dbadmin.ADWStats_Table, NO FALLBACK PROTECTION, NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM = DEFAULT ( DatabaseName CHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL FORMAT 'X(30)', TableName CHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL FORMAT 'X(30)', ADWStatsPerc BYTEINT DEFAULT 10 FORMAT '-(3)9' ) UNIQUE PRIMARY INDEX UPI_ADWStats_Table ( DatabaseName, TableName ); COMMENT ON TABLE dbadmin.ADWStats_Table IS 'Table used for selecting base table databases that need to be applied to the collect stats run scripts that are executed periodically during the day. Only Daatabases & tables that are present in table will qualify'; COMMENT ON COLUMN dbadmin.ADWStats_Table.DatabaseName IS 'Database Name of a base table database that will be used to generate collect statistics in the DBADMINCollect process.'; COMMENT ON COLUMN dbadmin.ADWStats_Table.TableName IS 'Table Name of a base table that will be used to generate collect statistics in the DBADMINCollect process.'; COMMENT ON COLUMN dbadmin.ADWStats_Table.ADWStatsPerc IS 'The Percentage of space change (+ or -) to check for during an ADWStats Cycle run to determine if stats is to be collected. Default is 10 - may be any number user requires for a specific table'; CREATE VIEW dbadmin.v_ADWStats_Table ( DatabaseName, TableName, ADWStatsPerc ) AS LOCKING ROW FOR ACCESS SELECT DatabaseName, TableName, ADWStatsPerc FROM DBADMIN.ADWStats_Table WITH CHECK OPTION; **------------------------------------------------------------------------------ ** ** Along with the summary table above, there will be a row inserted into the trace tabe for ** each table that collect stats qualifies and runs. This is the DBAs Detail Data! ** CREATE SET TABLE dbadmin.ADWStats_Trace, NO FALLBACK PROTECTION, NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM = DEFAULT ( DatabaseName CHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL FORMAT 'X(30)', TableName CHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL FORMAT 'X(30)', StatsRunDTTM TIMESTAMP(0) NOT NULL FORMAT 'YYYY-MM-DDBHH:MI:SS', ADWStatsPerc BYTEINT FORMAT '-(3)9', CollectStatsDTTM TIMESTAMP(0) NOT NULL FORMAT 'YYYY-MM-DDBHH:MI:SS', LastCollectDTTM TIMESTAMP(0) FORMAT 'YYYY-MM-DDBHH:MI:SS', PrevCurrentPerm FLOAT FORMAT '-9.99999999999999E-999', CurrCurrentPerm FLOAT FORMAT '-9.99999999999999E-999', SpaceChgPct FLOAT FORMAT '-9.99999999999999E-999', StatsReason CHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC FORMAT 'X(5)' COMPRESS ('Space','Time '), CollectStartDTTM TIMESTAMP(0) FORMAT 'YYYY-MM-DDBHH:MI:SS', CollectEndDTTM TIMESTAMP(0) FORMAT 'YYYY-MM-DDBHH:MI:SS' ) PRIMARY INDEX NUPI_ADWStats_Trace ( DatabaseName, TableName ); COMMENT ON TABLE dbadmin.ADWStats_Trace IS 'Collection table for the tables that have statitics collected on them during a DBADMINCollect cycle run periodically on selected ADW base table.'; COMMENT ON COLUMN dbadmin.ADWStats_Trace.DatabaseName IS 'Database Name of a base table database that will be used to generate collect statistics in the DBADMINCollect process.'; COMMENT ON COLUMN dbadmin.ADWStats_Trace.TableName IS 'Table Name of a base table that will be used to generate collect statistics in the DBADMINCollect process.'; COMMENT ON COLUMN dbadmin.ADWStats_Trace.StatsRunDTTM IS 'The date/time the DBADMIN Collect process was executed'; COMMENT ON COLUMN dbadmin.ADWStats_Trace.CollectStatsDTTM IS 'Timestamp of when the collect stats ddl was generated.'; COMMENT ON COLUMN dbadmin.ADWStats_Trace.PrevCurrentPerm IS 'Perm space as of the last time Stats was run.'; COMMENT ON COLUMN dbadmin.ADWStats_Trace.CurrCurrentPerm IS 'Current Perm space when stats were run at this time.'; COMMENT ON COLUMN dbadmin.ADWStats_Trace.SpaceChgPct IS 'Space % change from last stats collect to this run.'; COMMENT ON COLUMN dbadmin.ADWStats_Trace.StatsReason IS 'The reason that caused teh stats to be collected. Only values are: Time or Space.'; COMMENT ON COLUMN dbadmin.ADWStats_Trace.CollectStartDTTM IS 'The start timestamp when stats started to be collected on the table.'; COMMENT ON COLUMN dbadmin.ADWStats_Trace.CollectEndDTTM IS 'The end timestamp when stats started to be collected on the table.'; CREATE VIEW dbadmin.v_ADWStats_Trace ( DatabaseName, TableName, StatsRunDTTM, ADWStatsPerc, CollectStatsDTTM, LastCollectDTTM, PrevCurrentPerm, CurrCurrentPerm, SpaceChgPct, StatsReason, CollectStartDTTM, CollectEndDTTM ) AS LOCKING ROW FOR ACCESS SELECT DatabaseName, TableName, StatsRunDTTM, ADWStatsPerc, CollectStatsDTTM, LastCollectDTTM, PrevCurrentPerm, CurrCurrentPerm, SpaceChgPct, StatsReason, CollectStartDTTM, CollectEndDTTM FROM DBADMIN.ADWStats_Trace WITH CHECK OPTION; **------------------------------------------------------------------------------ ** ** View is used in the SPL process to qualify tables for stats collection. This ** view looks at all the individual stats on tables and prvides the oldest and ** latest dates when they were collected. ** CREATE VIEW dbadmin.v_ADWStatsDate -- View will retrieve the oldest and newest date(s) that statistics were collected for a table. ( DatabaseName ,TableName ,OldestCollectDate ,NewestCollectDate ) AS Locking Row For Access Select dt.DatabaseName ,dt.TableName ,Min(((HASHBUCKET(SUBSTR(Stats, 2, 1) || SUBSTR(Stats, 1, 1)(BYTE(4))) - 1900) * 10000 + (HASHBUCKET('00'xb || SUBSTR(Stats, 3, 1)(BYTE(4)))) * 100 + (HASHBUCKET('00'xb || SUBSTR(Stats, 4, 1)(BYTE(4)))))(DATE)) AS OldestCollectDate ,Max(((HASHBUCKET(SUBSTR(Stats, 2, 1) || SUBSTR(Stats, 1, 1)(BYTE(4))) - 1900) * 10000 + (HASHBUCKET('00'xb || SUBSTR(Stats, 3, 1)(BYTE(4)))) * 100 + (HASHBUCKET('00'xb || SUBSTR(Stats, 4, 1)(BYTE(4)))))(DATE)) AS NewestCollectDate From ( Select Databasename ,Tablename ,FieldStatistics As Stats From DBC.ColumnStats Where FieldStatistics Is Not Null Union Select DatabaseName ,TableName ,IndexStatistics As Stats From DBC.IndexStats Where IndexStatistics Is Not Null Union Select DatabaseName ,TableName ,ColumnsStatistics As Stats From DBC.MultiColumnStats Where ColumnsStatistics Is Not Null ) dt Group By dt.DatabaseName ,dt.TableName ; COMMENT ON VIEW dbadmin.v_ADWStatsDate IS 'View will calculate the oldest and newest date that statistics were collected for a table.'; **------------------------------------------------------------------------------ ** ** ** Stored Procedure to check and run Collect Stats on any tables qualifying for ** stats collection in the qualifying step (Step-03) ** ** ADWStatsRefresh SPL process collecets all the stats defined on a table and at the ** Table Level - not individual column or index level. So this supports stats ** that were defined USING SAMPLE. ** REPLACE PROCEDURE dbadmin.ADWStatsRefresh /*------------------------------------------------------ HDR_START -- -- NAME: p_collect_stats_all.spl -- DESCRIPTION: -- This procedure is called (by any means yo uwish) to identify -- what table(s) in their specified DBs under the ADW (Active DataWarehouse) -- that qualify for statistics collection. If they do, this procedure -- will dynamically generate a COLLECT STATS ON ; DDL, execute -- the DDL and update audit, trace & control tables -- -- -- INPUT PARAMETERS: InDBName - name of database to check for table stats refresh -- -- -- OUTPUT PARAMETER: -- MESSAGE - Text status message -- Error message if not valid -- Success message if OK -- -- INPUT TABLES: DBADMIN.v_ADWSTATS_Table -- DBADMIN.v_ADWStatsDate -- DBADMIN.v_ADWStats_Control_Table -- DBC.TableSize -- -- TEMP TABLES: DBADMIN.GT_ADWStats -- -- OUTPUT TABLES: DBADMIN.v_ADWStats_Control_Table -- DBADMIN.ADWStats_Trace -- DBADMIN.ADWStats_Summary -- DBC.TVFields -- DBC.Indexes -- -- PREMODULE: n/a -- POSTMODULE: n/a -- -- -- ------------------------------------------------------------ -- -- AUTHOR: JK Wight -- CREATED: 2008-03-11 -- CHANGES: Designed for Customer (Tempe, AZ) ADW ETL process -- VERSION: 20100315.1 -- -- ------------------------------------------------------------- -- Change History -- -- ChangeDate/Version Author Description -- 20080311.1 jkw Initial procedure for Teradata Customer -- 20080616.1 jkw Added check in GT population to only insert rows -- for tables from driver table that actually -- have at least one stat defined. if not, -- proc would abort with 3624 (no stats defined) -- and run would not apply stats. -- 20100215.1 jkw Add logic for a single table or ALL tables for a DB -- If ALL specified process all tables in the DB list -- If ALL 'NOT' specified, assume input is a table name -- And process that. -- 20100315.1 jkw Modified to process all tables for specified input database ONLY -- See p_collect_stats SPL in DBAMDIN library for the single table proc -- ---------------------------------------------------- HDR_END */ ( IN InDBName VARCHAR(30) ,OUT MESSAGE VARCHAR(200) ) ADWStatsRefresh: BEGIN -- Variables for Continue Handler DECLARE Status CHAR(5) DEFAULT '00000'; -- Variables for info/work/control of process DECLARE Start_DTTM TIMESTAMP(0); DECLARE End_DTTM TIMESTAMP(0); DECLARE ADWStatsStart TIMESTAMP(0); DECLARE ADWStatsEnd TIMESTAMP(0); DECLARE ADWStatsLapse CHAR(8); DECLARE Col_Stats_Stmt VARCHAR(512); DECLARE TableCount SMALLINT DEFAULT 0; DECLARE MaxSeqNumber INTEGER; DECLARE ProcessDB VARCHAR(30) DEFAULT ' '; DECLARE CurPerm FLOAT; DECLARE PrevPerm FLOAT DEFAULT NULL; --****************************************************************************** -- -- Step-01 -- -- Capture SQLSTATE in the event a database error occurs so SPL code can check for the completion status. -- DECLARE CONTINUE HANDLER FOR SQLEXCEPTION, SQLWARNING BEGIN SET Status = SQLSTATE; END; SELECT -- get current timestamp at start of cycle CURRENT_TIMESTAMP(0) INTO :ADWStatsStart; -- -- Purge the GT Table in case process is called multiple time within one session -- DELETE FROM dbadmin.GT_ADWStats ALL; --****************************************************************************** -- -- Step-02 -- -- Validate the Input Database name for existence in the ADWStats_Table driver table -- SET "MESSAGE" = 'Step-02 - Check valid DBname in driver table'; SELECT DatabaseName INTO :ProcessDB FROM dbadmin.v_ADWStats_Table vat WHERE vat.DatabaseName = :InDBName GROUP BY 1; IF (Status <> '00000') THEN SET MESSAGE = 'ABORT: Error in validating input DB Namee - step-02. (SQLSTATE=' || Status || ').'; END IF; IF (Activity_Count = 0) THEN SET MESSAGE = 'InDBName variable is not controlled by ADWStats - step-02. Input = ' || InDBName || '. No stats collection in this cycle.'; LEAVE ADWStatsRefresh; END IF; --****************************************************************************** -- -- Step-03 -- -- Validate the Input Database name for existence in the ADWStats_Table driver table -- to qualify tables within that database to check INSERT INTO DBADMIN.GT_ADWStats ( DatabaseName ,TableName ,ADWStatsPerc ,CurrentDTTM ,OldestCollectDtTm ,PrevCurrentPerm ,CurrCurrentPerm ,SpaceChgPct ,StatsReason ) SELECT -- those table that qualify for collecting stats DatabaseName AS DatabaseName ,TableName AS TableName ,ADWStatsPerc AS ADWStatsPerc ,CurrentDTTM AS CurrentDTTM ,OldestCollectDtTm AS OldestCollectDtTm ,PrevCurrentPerm AS PrevCurrentPerm ,CurrCurrentPerm AS CurrCurrentPerm ,SpaceChgPct AS SpaceChgPct , CASE WHEN SpaceChgPct > 10 -- More than 10% change, plus or minus THEN 'Space' ELSE 'Time' END AS StatsReason FROM ( Select curr.DatabaseName AS DatabaseName ,curr.TableName AS TableName ,curr.ADWStatsPerc AS ADWStatsPerc ,CURRENT_TIMESTAMP(0) AS CurrentDTTM ,prev.StatsRunDTTM AS OldestCollectDtTm ,COALESCE(Prev.CurrentPerm, 0) AS PrevCurrentPerm ,curr.CurrentPerm AS CurrCurrentPerm , CASE WHEN ( prev.CurrentPerm IS NULL OR prev.CurrentPerm = 0) THEN 100 -- To force collecting stats on new tables - i.e. never been involved in process before ELSE 100.00 * Abs(prev.CurrentPerm - curr.CurrentPerm) / prev.CurrentPerm END AS SpaceChgPct From ( SELECT -- Current size of the tables in the filtered databases adt.DatabaseName AS DatabaseName ,adt.TableName AS TableName ,adt.ADWStatsPerc AS ADWStatsPerc ,CURRENT_TIMESTAMP(0) AS CollectTimeStamp ,SUM(ts.CurrentPerm) AS CurrentPerm FROM DBC.TableSize ts -- Get current info on table size for qualifying INNER JOIN ( SELECT -- only tables that have column stats defined and in driver table cts.DatabaseName ,cts.TableName ,DT_tc.ADWStatsPerc FROM DBC.ColumnStats cts INNER JOIN ( SELECT DatabaseName ,TableName ,ADWStatsPerc FROM DBADMIN.v_ADWSTATS_Table -- A WHERE DatabaseName = :ProcessDB -- qualify tables from one of EDW's Databases - Input Parameter ) DT_tc ON cts.DatabaseName = DT_tc.DatabaseName AND cts.TableName = DT_tc.tablename where cts.fieldstatistics is not null group by 1 ,2 ,3 union SELECT -- only tables that have index stats defined and in driver table its.DatabaseName ,its.TableName ,DT_ti.ADWStatsPerc FROM DBC.IndexStats its INNER JOIN ( SELECT DatabaseName ,TableName ,ADWStatsPerc FROM DBADMIN.v_ADWSTATS_Table WHERE DatabaseName = :ProcessDB -- qualify tables from one of EDW's Databases - Input Parameter ) DT_ti ON its.DatabaseName = DT_ti.DatabaseName AND its.TableName = DT_ti.tablename where its.IndexStatistics is not null group by 1 ,2 ,3 ) AS adt -- filter only those tables qualifying for ADWStats and have stats ON ts.DatabaseName = adt.DatabaseName AND ts.TableName = adt.TableName GROUP BY adt.DatabaseName ,adt.TableName ,ADWStatsPerc ) AS curr LEFT OUTER JOIN DBADMIN.v_ADWStatsDate stat -- to get date of last stats collect for tables having stats on them On curr.DatabaseName = stat.DatabaseName And curr.TableName = stat.TableName LEFT OUTER JOIN DBADMIN.v_ADWStats_Control_Table prev -- to get the data from the last time stats were collected On curr.DatabaseName = prev.DatabaseName And curr.TableName = prev.TableName Where Cast(curr.CollectTimeStamp As Date) = Current_Date And ( SpaceChgPct > curr.ADWStatsPerc -- If space INCREASES more than ADWStatsPerc % OR (CURRENT_TIMESTAMP - prev.StatsRunDTTM DAY(4)) >= INTERVAL '28' DAY -- last collect > X days ) ) AS DT_stats; -- If no rows are returned, then there are no stats to be -- collected for this cycle call Put a default row into the -- summary table and exit procedure. IF (Activity_Count = 0) THEN INSERT INTO DBADMIN.ADWStats_Summary ( StatsRunDTTM ,StatsRunEndDTTM ,TableCollectQty ,TableCollectTm ) VALUES ( :ADWStatsStart ,:ADWStatsStart ,0 ,NULL ); SET MESSAGE = 'NO_ADWSTATS_COLLECT: No tables qualified for stats collection in this cycle - - step-03.'; LEAVE ADWStatsRefresh; END IF; IF (Status <> '00000') THEN SET MESSAGE = 'ABORT: Error in Seeding the Summary Table - step-03. (SQLSTATE=' || Status || ').'; LEAVE ADWStatsRefresh; END IF; --****************************************************************************** -- Step-04 -- -- We have table to process -- Seed Summary Table with initial Timestamp PK value -- Seed the Summary table with a new row for this cycle run. -- INSERT INTO DBADMIN.ADWStats_Summary ( StatsRunDTTM ,StatsRunEndDTTM ,TableCollectQty ,TableCollectTm ) VALUES ( :ADWStatsStart ,NULL ,0 ,NULL ); IF (Status <> '00000') THEN CALL dbc.SysExecSQL( 'select *;'); -- Dummy statement to set sql state to non zero value SET MESSAGE = 'ABORT: Error in Seeding the Summary Table - step-04. (SQLSTATE=' || Status || ').'; LEAVE ADWStatsRefresh; END IF; --****************************************************************************** -- Step-05 -- -- Initialize TableCount variable -- Establish CURSOR and process contents of GT_ADWStats -- create DDL -- execute Dynamic DDL -- update control table row -- insert trace row SET TableCount = 0; -- ----------------- Cursor FOR Loop Start ----------------- FetchADWRow: FOR cur_adwstats_proc AS adwcursor CURSOR FOR SELECT DatabaseName ,TableName ,ADWStatsPerc ,CurrentDTTM ,OldestCollectDtTm ,PrevCurrentPerm ,CurrCurrentPerm ,SpaceChgPct ,StatsReason FROM DBADMIN.GT_ADWStats DO -- -- Create the Col Stats statement SET Col_Stats_Stmt = 'COLLECT STATISTICS ON ' || TRIM(cur_adwstats_proc.DatabaseName) || '.' || TRIM(cur_adwstats_proc.TableName) ||';' ; -- capture start time for audit trace SELECT -- Get starting time timestamp CAST(CURRENT_TIMESTAMP(0) AS TIMESTAMP(0)) INTO Start_DTTM; -- Execute generated DDL COLECDT STATISTICS on table CALL dbc.SysExecSQL( :Col_Stats_Stmt ); IF (Status <> '00000') THEN -- Set reject message an dleave CALL dbc.SysExecSQL( 'select *;'); -- Dummy statement to set sql state to non zero value SET MESSAGE = 'ABORT: Unable to collect statistics (Step-05) with ''' || Col_Stats_Stmt || ''' (SQLSTATE=' || Status || ').'; LEAVE ADWStatsRefresh; END IF; -- Capture end time for audit trace SELECT -- Get ending time timestamp CAST(CURRENT_TIMESTAMP(0) AS TIMESTAMP(0)) INTO End_DTTM; -- -- -- Multi-Statement process to: -- 1. UpSert the control table DBADMIN.ADWStats_Control_Table with data -- on last collect stats info in readiness for next cycle -- 2. INSERT stats data just run to collect stats. -- Result data will then be used to analyze stats required cycle time reporting. UPDATE DBADMIN.ADWStats_Control_Table cts -- control table row where it already exists SET StatsRunDTTM = :ADWStatsStart ,CurrentPerm = cur_adwstats_proc.CurrCurrentPerm ,CollectCount = CollectCount + 1 WHERE cts.DataBaseName = cur_adwstats_proc.DataBaseName AND cts.TableName = cur_adwstats_proc.TableName ELSE INSERT INTO DBADMIN.ADWStats_Control_Table -- control table row where it does not exists ( DataBaseName ,TableName ,StatsRunDTTM ,CurrentPerm ,CollectCount ) VALUES ( cur_adwstats_proc.DataBaseName ,cur_adwstats_proc.TableName ,:ADWStatsStart ,cur_adwstats_proc.CurrCurrentPerm ,1 ) ;INSERT INTO DBADMIN.ADWStats_Trace -- insert stats info from table that had stats collected ( DataBaseName ,TableName ,StatsRunDTTM ,ADWStatsPerc ,CollectStatsDTTM ,LastCollectDTTM ,PrevCurrentPerm ,CurrCurrentPerm ,Spacechgpct ,StatsReason ,CollectStartDTTM ,CollectEndDTTM ) VALUES ( cur_adwstats_proc.DataBaseName ,cur_adwstats_proc.TableName ,:ADWStatsStart ,cur_adwstats_proc.ADWStatsPerc ,cur_adwstats_proc.CurrentDTTM ,cur_adwstats_proc.OldestCollectDtTm ,cur_adwstats_proc.PrevCurrentPerm ,cur_adwstats_proc.CurrCurrentPerm ,cur_adwstats_proc.Spacechgpct ,cur_adwstats_proc.StatsReason ,:Start_DTTM ,:End_DTTM ) ; IF (Status <> '00000') THEN CALL dbc.SysExecSQL( 'select *;'); -- Dummy statement to set sql state to non zero value SET MESSAGE = 'ABORT: Error in updating Control_Table_Stats or Collected_Table_Stats tables - Sep-05. (SQLSTATE=' || Status || ').'; LEAVE ADWStatsRefresh; END IF; -- Increment table count for summary Set TableCount = TableCount +1; END FOR FetchADWRow; -- End of Cursor Loop ----------------- Cursor FOR Loop End ----------------- --****************************************************************************** -- Step-08 -- -- Prepare return status message and exit Proc -- SELECT CURRENT_TIMESTAMP(0) INTO :ADWStatsEND; -- Update seeded rummary row with final summary stats UPDATE DBADMIN.ADWStats_Summary SET StatsRunEndDTTM = :ADWStatsEND ,TableCollectQty = :TableCount ,TableCollectTm = :ADWStatsEND - :ADWStatsStart HOUR(2) TO SECOND(0) WHERE StatsRunDTTM = :ADWStatsStart ; -- Set message for summary info Set Message = 'Stats Collection Finished for collection time: ' || CAST(ADWStatsEND AS CHAR(19)) || ' for ' || CAST(TableCount AS CHAR(3)) || ' Qualifying tables.'; END ADWStatsRefresh; **------------------------------------------------------------------------------ ** /* Install the tables and views, and create the SPL procedure into your administration User account - i.e. the User that you use to create your DBs and Users as this ID will have all the necessary privileges you need to collect stats and you won't have to have INDEX privileges or DROP TABLE privileges on other users that you don't need. To execute the process, you can use a number of methods. In the case where this process was initially created, it was an 'ACTIVE' environemnt where ETL was a 24X7 operation at a transaction level. It was initially set up to run in a BTEQ file initiated from a CRN job on the TD server every 2 hours. i.e. it would run and any table that reached the limits set for it (i.e. 10% or > 28 days), it would refresh the stats. In antoher case the proc was modified to be called by the ETL processes after an update. and if the table met the limits, it would collect the stats. */ ** **------------------------------------------------------------------------------