Stored Procedure

Database
KN
Enthusiast

Stored Procedure

Hello Gurus,

 

while executing the below procedure that i created i get the o/p of the SQL statement:

 

Sel DISTINCT A.DatabaseName as D_NM ,A.TableName as T_NM ,A.ColumnName as C_NM,B.IntervalDays as INT_DAYS,B.LastCollectDate from TDDB_PROD_DB.STATS7_METADATA_LOG_TMP A
JOIN TDDB_PROD_DB.STATS7_METADATA1 B
on ( A.DatabaseName=b.DatabaseName AND A.Tablename=B.Tablename )
where STATUS='N'
order by 1,2,3,4 ==>  15 rows  ( i tjust took one table to test )

 

These rows are not further processed into the DO conditon. I am not sure what i am missing .  Any inputs?

 

REPLACE PROCEDURE TDDB_PROD_DB.CollectStatistics(OUT RESULTMSG VARCHAR(512))
--DYNAMIC RESULT SETS 1
BEGIN
/* WORK VARIABLES */
DECLARE DB_NM,TB_NM VARCHAR(50);
DECLARE COL_NM VARCHAR(500);
DECLARE LC_DT DATE;
DECLARE INT_DAYS INTEGER;
DECLARE SQLSTATEMENT_TX VARCHAR(512);

DECLARE EXIT HANDLER FOR SqlException
BEGIN
SET ResultMsg = 'CollectStatistics: SQL Exception, SQL State = '
|| SqlState
|| ', SQL Code = '
|| SqlCode;
END;


LABEL1: BEGIN

Del from TDDB_PROD_DB.STATS7_METADATA_LOG_TMP ;

--OPEN CONTROL_DETAILS;
/* Insert into the Collect stats Logtable with flag set to N */
INSERT INTO TDDB_PROD_DB.STATS7_METADATA_LOG_TMP
(Starttime,DatabaseName, TableName,ColumnName,Endtime,Status)
Sel NULL,DatabaseName,TableName,ColumnName,NULL,'N' from TDDB_PROD_DB.STATS7_METADATA1;

/* Now traverse through each object from STATS7_METADATA_LOG_TMP where Status=N */
FOR vFor --AS vFOR1 CURSOR FOR
Sel DISTINCT A.DatabaseName as D_NM ,A.TableName as T_NM ,A.ColumnName as C_NM,B.IntervalDays as INT_DAYS,B.LastCollectDate from TDDB_PROD_DB.STATS7_METADATA_LOG_TMP A
JOIN TDDB_PROD_DB.STATS7_METADATA1 B
on ( A.DatabaseName=b.DatabaseName AND A.Tablename=B.Tablename )
where STATUS='N'
order by 1,2,3,4

DO

IF (DATE - vFor.INT_DAYS) >= vFor.LastCollectDate /* checking based on Interval of 21 days for this table */

THEN
/* First Insert into the Log Table to indicate that the column has been considered for stats collection - S - "Started" */
Insert into TDDB_PROD_DB.STATS7_METADATA_LOG Values (Current_TimeStamp(0),trim(vFor.D_NM),trim(vFor.T_NM),trim(vFor.C_NM),trim(vFor.INT_DAYS),,'S');

/* COLLECT THE ACTUAL STATS */
SET SQLStatement_TX = 'COLLECT STATISTICS ON '
|| Trim(vFor.D_NM)
|| '."'
|| Trim(vFor.T_NM)
|| '" COLUMN ('
|| Trim(vFor.C_NM)
|| ');';

CALL DBC.SysExecSQL(:SQLStatement_TX);

/* update the STATS7_METADATA_LOG to show the right status */
update TDDB_PROD_DB.STATS7_METADATA_LOG set Status='C' , EndTime=current_timestamp(0)
where DataBaseName= trim(vFor.D_NM )
and TableName=trim(vFor.T_NM)
and ColumnName=trim(vFor.C_NM);

/* update the Control Table for that column as completed 'Y' */

update TDDB_PROD_DB.STATS7_METADATA_LOG_TMP
set STATUS='Y'
where
DataBaseName= trim(vFor.D_NM )
and TableName=trim(vFor.T_NM)
and ColumnName=trim(vFor.C_NM);

END IF;
END FOR;


SET ResultMsg = 'Stats process Completed Successfully';
END LABEL1;
END;


Accepted Solutions
Highlighted
KN
Enthusiast

Re: Stored Procedure

Because the data is not matched based of the if condition that why it bypassed.

 

 

1 ACCEPTED SOLUTION
1 REPLY 1
Highlighted
KN
Enthusiast

Re: Stored Procedure

Because the data is not matched based of the if condition that why it bypassed.