MLOAD error code need to be captured

Database
SAP
Enthusiast

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 !!!

3 REPLIES
Junior Contributor

Re: MLOAD error code need to be captured

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.

SAP
Enthusiast

Re: MLOAD error code need to be captured

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;
Enthusiast

Re: MLOAD error code need to be captured

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.