All Forums Database
SAP 73 posts Joined 08/14
28 Oct 2014
MLOAD error code need to be captured

Hi ,
I have a written  a stored procedure to release mload for the tables on which MLOAD is active . I have used
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
 
INSERT INTO ERRORTABLE .......;
But i am unable to capture MLOAD and FLOAD errors . Please help
Thanks !!!
 
 

SAP
dnoeth 4628 posts Joined 11/04
28 Oct 2014

What errors are you trying to capture?
When you RELEASE MLOAD there will be errors like 2580 MLoad not active, of course you can handle them.

Dieter

SAP 73 posts Joined 08/14
28 Oct 2014

Hi Dieter ,
But i was unable to capture them . Please find below the code which i have used to capture and help me to accomplish

REPLACE PROCEDURE EDW_DB.RELEASE_MLOAD()
BEGIN


DECLARE DBNAME VARCHAR(128);
DECLARE TBNAME VARCHAR(128);
DECLARE LOOP_NBR INTEGER;
DECLARE ROW_CNT INTEGER;
DECLARE SQLSTMT VARCHAR(1000);
DECLARE SQLSTMT1 VARCHAR(1000);

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
INSERT INTO EDW_DB.FLOAD_ON_TABLES
(
 DBNAME
,TBNAME
,ERROR_CODE
)
VALUES
(
:DBNAME
,:TBNAME
,SQLSTATE
)
;


DELETE FROM EDW_DB.FLOAD_ON_TABLES;


SET LOOP_NBR = 0;

SELECT COUNT(*) INTO ROW_CNT FROM EDW_DB.CHECK_FLOAD;


WHILE (LOOP_NBR < ROW_CNT) DO


SELECT DBNAME , TBNAME INTO DBNAME , TBNAME FROM EDW_DB.CHECK_FLOAD
WHERE RN_NUMBER = LOOP_NBR + 1
;

SET SQLSTMT = 'RELEASE MLOAD '||DBNAME||'.'||TBNAME||';';

SET SQLSTMT1 = 'RELEASE MLOAD '||DBNAME||'.'||TBNAME||' IN APPLY;';

CALL DBC.SYSEXECSQL(SQLSTMT);

CALL DBC.SYSEXECSQL(SQLSTMT1);


SET LOOP_NBR = LOOP_NBR + 1;


END WHILE;


END;

 

SAP

vikas_yadav 19 posts Joined 09/12
29 Oct 2014

You need to caputre value of SQLCODE variable not SQLSTATE variable in a procedure then you can join this table (FLOAD_ON_TABLES)  with DBC.ERRORMSGS to see the Errormsg for that particular SQLCODE.

REPLACE PROCEDURE RELEASE_MLOAD()

BEGIN
 
 
DECLARE OBJECTNAME VARCHAR(128);
DECLARE OBJECTNAME1 VARCHAR(128);
DECLARE DBNAME1 VARCHAR(128);
DECLARE TBNAME1 VARCHAR(128);
DECLARE ERROR_CODE VARCHAR(128);
DECLARE LOOP_NBR INTEGER;
DECLARE ROW_CNT INTEGER;
DECLARE SQLSTMT VARCHAR(100);
DECLARE SQLSTMT1 VARCHAR(1000);
 
DECLARE EXIT  HANDLER FOR SQLEXCEPTION
BEGIN
INSERT INTO FLOAD_ON_TABLES
(
 DBNAME
,TBNAME
,ERROR_CODE
)
VALUES
(
DBNAME1,
TBNAME1,
SQLCODE
)
;
 END;
 

 
SET LOOP_NBR = 0;
 SET ROW_CNT = 2;
 

DELETE FROM FLOAD_ON_TABLES;
 
WHILE (LOOP_NBR < ROW_CNT) DO
 

 SELECT TOP 1  DBNAME  ,TBNAME INTO  :DBNAME1,:TBNAME1  FROM CHECK_FLOAD WHERE DBNAME='dbc';
 
SET SQLSTMT = 'select count(1) from  '||CAST(DBNAME1 AS VARCHAR(30))||'.'||CAST(TBNAME1 AS VARCHAR(30));

CALL DBC.SYSEXECSQL(SQLSTMT);
 
 
SET LOOP_NBR = LOOP_NBR + 1;
 
 
END WHILE;
 
 
END;


CALL RELEASE_MLOAD();


SELECT * FROM DBC.ERRORMSGS,FLOAD_ON_TABLES WHERE ERRORCODE=ERROR_CODE;

SQLCODE.

You must sign in to leave a comment.