REPLACE PROCEDURE dbadmin.TableRowCount /*------------------------------------------------------ HDR_START
--
--  NAME:     			TableRowCount
--  DESCRIPTION:
--
--
--
--
--
--  INPUT PARAMETERS:	DBADMIN.ADWStats_Table - table names being tracked with Stats
--
--
--  OUTPUT PARAMETER:
--  					MESSAGE   - Text status message
--
--
--  INPUT TABLES:   	DBADMIN.v_ADWSTATS_Table
--      				DBADMIN.GT_TablesRowCount
--					DBC.Tables
--
--  TEMP TABLES:		DBADMIN.GT_TablesRowCount
--
--  OUTPUT TABLES:		DBADMIN.TableProperty
--
--  PREMODULE:    n/a
--  POSTMODULE:   n/a
--
--
-- ------------------------------------------------------------
--
--  AUTHOR:	JK Wight
--  CREATED:	2010-08-27
--  CHANGES:   Designed for Swift Transportation
--  VERSION:   20100827.1
--
-- -------------------------------------------------------------
-- Change History
--
-- ChangeDate/Version  	Author	Description
-- 20100827.1    		jkw  	Initial procedure for Swift
--
--
---------------------------------------------------- HDR_END */
(
 	 OUT MESSAGE   VARCHAR(200)
)


TableRowCount:
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 CountStart			TIMESTAMP(0);
DECLARE CountEnd			TIMESTAMP(0);
DECLARE CountLapse			INTERVAL MINUTE(4) TO SECOND;
DECLARE INS_Count_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;


--******************************************************************************
--
-- Step-02
--
--  Establish list of tables to be updated with row counts for this cycle
--		Capture in a CURSOR to process via dynamic SQL to capture counts
--		into a temp table (GT_TablesRowCount).
--

SET TableCount = 0;

SELECT	-- get current timestamp at start of cycle to measure lapse time
	CURRENT_TIMESTAMP(0)
	INTO CountStart;


DELETE FROM DBADMIN.GT_TablesRowCount ALL	-- Clear out or materialize the GT table
;

--
----------------- Cursor FOR Loop Start -----------------
FetchRowCount: FOR
	cur_table_row
	AS
	CountCursor CURSOR FOR
	SELECT
		 DatabaseName
		,TableName
	FROM
		dbadmin.v_ADWStats_Table
	WHERE
		(DatabaseName, TableName)
		IN
		(
		SELECT
			DatabaseName
			,TableName
		FROM "DBC"."Tables"
		)
		AND
		DatabaseName LIKE 'P%'	-- only get counts for Prod enviornment DBs
	ORDER BY 1,2
DO

	--
	--	Create the Col Stats statement

	SET INS_Count_Stmt = 'INSERT INTO DBADMIN.GT_TablesRowCount ( DatabaseName, TableName, RowCount ) SELECT '''
						|| TRIM(cur_table_row.DatabaseName)
						|| ''', '''
						|| TRIM(cur_table_row.TableName)
						|| ''','
						|| ' COUNT(*) FROM  '
						|| TRIM(cur_table_row.DatabaseName)
						|| '.'
						|| TRIM(cur_table_row.TableName)
						|| ' GROUP BY 1,2;';


	CALL dbc.SysExecSQL(INS_Count_Stmt);

	IF (Status <> '00000') THEN
		-- Set reject message an dleave
		SET MESSAGE = 'ABORT: Unable to collect statistics (Step-02) with '''
		|| INS_Count_Stmt
		|| ''' (SQLSTATE='
		|| Status
		|| ').';
		LEAVE TableRowCount;
	END IF;


	SET TableCount = TableCount + 1;


END FOR FetchRowCount;	-- End of Cursor Loop
----------------- Cursor FOR Loop End -----------------

SELECT	-- get current timestamp at start of cycle to measure lapse time
	CURRENT_TIMESTAMP(0)
	INTO CountEnd;


--******************************************************************************
--
-- Step-03
--
--	Rows counted for all qualifying tables and result inserted into the GT
--		table for final processing and posting to the target table.

INSERT INTO PMCPDATA.TableRowCount
(
	DatabaseName
	,TableName
	,EffectiveTrackDate
	,RowCountQuantity
)
SELECT
	DatabaseName
	,TableName
	,CURRENT_DATE
	,RowCount

FROM
	dbadmin.GT_TablesRowCount;

IF (Status <> '00000') THEN
	-- Set reject message and leave
	SET MESSAGE = 'ABORT: Unable to INSERT rows into the target table (SQLSTATE='
	|| Status
	|| ').';
	LEAVE TableRowCount;
END IF;


SET CountLapse = CountEnd - CountStart MINUTE (4) TO SECOND;

Set Message = 'Table Counts Finished for processing time: '
			|| CAST(CountLapse AS CHAR(9))
			|| ' for '
			|| CAST(TableCount AS CHAR(3))
			|| ' Qualifying tables.';


END TableRowCount;