Help with CONTINUE HANDLER!!

Database
Teradata Employee

Help with CONTINUE HANDLER!!

Hi all.
I have a process that reads a table where the user defines wich stats should run each day of the week.
Once I have the list of stats to run in a table, I use a Sp to read each record, register the start of the collect stats in a log table, run the collect sentence using dbc.sysexecsql (the collect sentence is built into a field of the table).

My problem is, if the user defines a stat for a field that does not exists in a table. In this case the sysexecsql will fail and the sp will stop. But I need it to run for every stat defined, and if it fails just write the error.

According to what i have read i can use a CONTINUE HANDLER so that the Sp wont fail if the field orthe table does not exists or more general if the collect stats fails.

What im trying to do but im not sure ofthe syntax is this. This is working beacuse the DECLARE HANDLER line is commented, but as soon as I uncomment it it fails.

Thanks for any help in advance....

BEGIN
FOR C11 AS C_Stats CURSOR FOR
SELECT IdEstadistica, FechaIni, Qry
FROM DWH_STAGE.STATS_GRUPO_1
ORDER BY orden, IDESTADISTICA

DO

UPDATE DWH_LOG.LOG_STATS_TD
SET Fecha_Hora_Inicio = CURRENT_TIMESTAMP(0),
Estado = 'Running'
WHERE IdEstadistica = C11.IdEstadistica
AND Fecha_Inicio = C11.FechaIni
AND Fecha_Hora_Inicio IS NULL;

INS DWH_STAGE.SP_STATS_MSG ('RUNNING',);

--DECLARE CONTINUE HANDLER FOR NOT FOUND

CALL dbc.sysexecsql (C11.Qry);

INS DWH_STAGE.SP_STATS_MSG (C11.Qry,);

IF SQLSTATE <> '00000' THEN
UPDATE DWH_LOG.LOG_STATS_TD
SET Estado = 'Error'
WHERE IdEstadistica = C11.IdEstadistica
AND Fecha_Inicio = C11.FechaIni
AND Fecha_Hora_Fin IS NULL;

INS DWH_STAGE.SP_STATS_MSG ('FALLA',);

ELSE
UPDATE DWH_LOG.LOG_STATS_TD
SET Fecha_Hora_Fin = CURRENT_TIMESTAMP(0),
Estado = 'Finished Ok'
WHERE IdEstadistica = C11.IdEstadistica
AND Fecha_Inicio = C11.FechaIni
AND Fecha_Hora_Fin IS NULL
AND Fecha_Hora_Inicio IS NOT NULL;

INS DWH_STAGE.SP_STATS_MSG ('OK',);
END IF;

END FOR;

END;

7 REPLIES
Junior Contributor

Re: Help with CONTINUE HANDLER!!

A handler must be declared at the beginning of a "compound statement" after any variables and before any SQL statement:

BEGIN
DECLARE variables ...
DECLARE HANDLER ...
FOR ...
END;

Btw, a NOT FOUND handler is used for a select returning no rows.
But if you try to COLLECT STATS on a non-existing column you should check for a SQLSTATE 'T5628'.

Dieter
Teradata Employee

Re: Help with CONTINUE HANDLER!!

Dieter thank you for your quick response.
I changed the handler to DECLARE CONTINUE HANDLER FOR SQLSTATE 'T5628', and put it an the begining of the SP, but now when the SP is called, it wont do anything (compiled ok tough).
Any last recomendation? i feel so close so far...

REPLACE PROCEDURE DBA.SP_STATS_GRUPO2()
BEGIN
DECLARE CONTINUE HANDLER FOR SQLSTATE 'T5628'
BEGIN
FOR C11 AS C_Stats CURSOR FOR
SELECT IdEstadistica, FechaIni, Qry
FROM DWH_STAGE.STATS_GRUPO_1
ORDER BY orden, IDESTADISTICA
....
.
CALL DBC.sysexecsql(....
..

Junior Contributor

Re: Help with CONTINUE HANDLER!!

Hi Mauricio,
if this is your source code, then the cursor is part of the error handling action which isonly executed when there's an error :-)

REPLACE PROCEDURE DBA.SP_STATS_GRUPO2()
BEGIN
DECLARE CONTINUE HANDLER FOR SQLSTATE 'T5628'
BEGIN
-- error handlig code
END;
FOR C11 AS C_Stats CURSOR FOR
...

Dieter
Junior Supporter

Re: Help with CONTINUE HANDLER!!

Hi Diether,

I need some help in error handing in SP when using cursors as you mentioned in this post. Below is my current code that workds fine.

REPLACE PROCEDURE mysp (
IN SRC_DB_NM VARCHAR(30)
,IN SRC_TBL_NM VARCHAR(30)
,OUT MESSAGE VARCHAR(200)
)
DYNAMIC RESULT SETS 1
BEGIN
DECLARE query VARCHAR(200);
DECLARE rec_count INTEGER DEFAULT 0;
DECLARE Status CHAR(5) DEFAULT '00000';
DECLARE C1 CURSOR FOR S1;
BEGIN
SET QUERY = 'SELECT count(*) FROM ' || SRC_DB_NM || '.' || SRC_TBL_NM || ';';
PREPARE S1 FROM query;
OPEN C1 USING SRC_DB_NM,SRC_TBL_NM;
FETCH c1 INTO rec_count;
set MESSAGE = rec_count;
END ;
END;

I get an error maesage with sql code - 2652 that table is being loaded which is correct. I want to handle the return codes of this sql statement and populate the OUT message with different messages based on this and also leave the SP. But, how do we handle the sqlwarning/sqlexception when using cursors in SP. The normal process of handling this using :

  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION, SQLWARNING
BEGIN
SET Status = SQLSTATE;
END;

doesn't work with Cursors. I did check the TD SLQ manuals, but didnlt get a proper details/example. I have posted a question yesterday in this forum, but did get any response yet. you could refer to it for details of my requirement.

http://forums.teradata.com/forum/database/using-dynamic-databasename-and-tablename-in-stored-procedu...

Junior Supporter

Re: Help with CONTINUE HANDLER!!

REPLACE PROCEDURE dba_dwa.mysp (
IN SRC_DB_NM VARCHAR(30)
,IN SRC_TBL_NM VARCHAR(30)
,OUT MESSAGE VARCHAR(200)
)
DYNAMIC RESULT SETS 1
BEGIN
DECLARE query VARCHAR(200);
DECLARE rec_count INTEGER DEFAULT 0;
DECLARE Status CHAR(5) DEFAULT '00000';

DECLARE CONTINUE HANDLER FOR SQLSTATE '2652'
BEGIN
set message = 'in here';
END;

DECLARE C1 CURSOR FOR S1;
BEGIN
SET QUERY = 'SELECT count(*) FROM ' || SRC_DB_NM || '.' || SRC_TBL_NM || ';';
PREPARE S1 FROM query;
OPEN C1 USING SRC_DB_NM,SRC_TBL_NM;
if (sqlcode<>0) then
set MESSAGE = 'error';
end if;
FETCH c1 INTO rec_count;
set MESSAGE = rec_count;
END ;
END;

I think i am close, but missing the link somewhere. 

if (sqlcode<>0) doesn't handle this as i get this exceoption in ''Open'' stmnt.  When giving DECLARE CONTINUE HANDLER, it gives me error in DECLARE  C1 CURSOR FOR S1. As Diether mentioned above, i want to handle exception 2635 for this open/fetch of the cursor. I dont want to execute this cursor when this exception occurs.

Supporter

Re: Help with CONTINUE HANDLER!!

Hi teradatauser2,

The below code will do the required. You can now set a MESSAGE according to the SQLCODE you get using IF.. ELSE.

REPLACE PROCEDURE MYSP
(
IN SRC_DB_NM VARCHAR(30)
, IN SRC_TBL_NM VARCHAR(30)
, OUT MESSAGE VARCHAR(200)
)
DYNAMIC RESULT SETS 1

BEGIN

DECLARE QUERY VARCHAR(200);
DECLARE REC_COUNT INTEGER DEFAULT 0;
DECLARE STATUS CHAR(10) DEFAULT '00000';
DECLARE C1 CURSOR FOR S1;

DECLARE EXIT HANDLER FOR SQLEXCEPTION,SQLWARNING
BEGIN
SET STATUS = SQLCODE;
IF(TRIM(STATUS)) = '3807' THEN
SET MESSAGE = 'PASSED TABLE '||SRC_DB_NM||'.'||SRC_TBL_NM||' DOES NOT EXIST';
ELSE
SET MESSAGE = 'ERROR';
END IF;
END;

BEGIN
SET QUERY = 'SELECT COUNT(*) FROM ' || SRC_DB_NM || '.' || SRC_TBL_NM || ';';
PREPARE S1 FROM QUERY;
OPEN C1 USING SRC_DB_NM,SRC_TBL_NM;
FETCH C1 INTO REC_COUNT;
SET MESSAGE = REC_COUNT;
END;

END;

Thanks,

Rohan Sawant

Junior Supporter

Re: Help with CONTINUE HANDLER!!

Hi Roahan,

I believe, i have replied on your suggestion in my other post. As you mentioned, waiting for your reply on this as it doesn't seem to be working.

Thanks !

Samir