/*
**
** 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.
*/
**
**------------------------------------------------------------------------------