DB2 Nested Stored Procedures and labeled_begin_atomic error

[rjj1]

In DB2 UDB, I call a stored procedure, which in turn calls a number of other stored procedures. I am used Command Editor.

Here is the call which fails and the error message:
call arc.arc_restore ;
------------------------------------------------------------------------------
call arc.arc_restore
SQL0104N An unexpected token "CALL ARC.ARC_DISABLE _TRIG('MAXIMO', 'WORKLOG_T"
was found following " ". Expected tokens may include:
"<labeled_begin _atomic>". SQLSTATE=42601


Explanation:

A syntax error in the SQL statement or the input command string for the SYSPROC.ADMIN_C MD procedure was detected at the specified token following the text "<text>". The "<text>" field indicates the 20 characters of the SQL statement or the input command string for the SYSPROC.ADMIN_C MD procedure that preceded the token that is not valid.


Can someone explain what that means and how to fix it? Thanks in advance.

Here is the stored procedure called:

CONNECT RESET
$
CONNECT TO MAXDB71

$
DROP PROCEDURE ARC.ARC_RESTORE
$

CREATE PROCEDURE ARC.ARC_RESTORE ()
LANGUAGE SQL
BEGIN ATOMIC
DECLARE stmt varchar(32000) default ' ';

--Turn off all trigs on the tables being inserted in
--quiesce database immediate
set stmt = '
CALL ARC.arc_disable_trig(''MAXIMO'',''WORKLOG_T'');
CALL ARC.arc_disable_trig(''MAXIMO'',''WORKLOG_U'');
CALL ARC.arc_disable_trig(''MAXIMO'',''LONGDESCRIPTION_T'');
CALL ARC.arc_disable_trig(''MAXIMO'',''LONGDESCRIPTION_U'');
CALL ARC.arc_disable_trig(''MAXIMO'',''COMMLOG_T'');
CALL ARC.arc_disable_trig(''MAXIMO'',''COMMLOG_U'');
CALL ARC.arc_disable_trig(''MAXIMO'',''PLUSPTKPRICETOT_T'');
CALL ARC.arc_disable_trig(''MAXIMO'',''PLUSPTKPRICETOT_U'');
CALL ARC.arc_disable_trig(''MAXIMO'',''PERSISTSTATUS_T'');
CALL ARC.arc_disable_trig(''MAXIMO'',''PERSISTSTATUS_U'');
CALL ARC.arc_disable_trig(''MAXIMO'',''WORKVIEW_T'');
CALL ARC.arc_disable_trig(''MAXIMO'',''WORKVIEW_U'');
CALL ARC.arc_disable_trig(''MAXIMO'',''TKSTATUS_T'');
CALL ARC.arc_disable_trig(''MAXIMO'',''TKSTATUS_U'');
CALL ARC.arc_disable_trig(''MAXIMO'',''TICKET_T'');
CALL ARC.arc_disable_trig(''MAXIMO'',''TICKET_U'');

-- Call your routines here

CALL ARC.arc_enable_trig(''MAXIMO'',''WORKLOG_T'');
CALL ARC.arc_enable_trig(''MAXIMO'',''WORKLOG_U'');
CALL ARC.arc_enable_trig(''MAXIMO'',''LONGDESCRIPTION_T'');
CALL ARC.arc_enable_trig(''MAXIMO'',''LONGDESCRIPTION_U'');
CALL ARC.arc_enable_trig(''MAXIMO'',''COMMLOG_T'');
CALL ARC.arc_enable_trig(''MAXIMO'',''COMMLOG_U'');
CALL ARC.arc_enable_trig(''MAXIMO'',''PLUSPTKPRICETOTALS_T'');
CALL ARC.arc_enable_trig(''MAXIMO'',''PLUSPTKPRICETOTALS_U'');
CALL ARC.arc_enable_trig(''MAXIMO'',''PERSISTSTATUS_T'');
CALL ARC.arc_enable_trig(''MAXIMO'',''PERSISTSTATUS_U'');
CALL ARC.arc_enable_trig(''MAXIMO'',''WORKVIEW_T'');
CALL ARC.arc_enable_trig(''MAXIMO'',''WORKVIEW_U'');
CALL ARC.arc_enable_trig(''MAXIMO'',''TKSTATUS_T'');
CALL ARC.arc_enable_trig(''MAXIMO'',''TKSTATUS_U'');
CALL ARC.arc_enable_trig(''MAXIMO'',''TICKET_T'');
CALL ARC.arc_enable_trig(''MAXIMO'',''TICKET_U'');
';
execute immediate stmt;

--unquiesce db

END
$

And here are the stored procedures it calls. These work when I call them directly from the command processor.

CREATE PROCEDURE ARC.ARC_ENABLE_TRIG ( 
  		IN v_schema VARCHAR(128),  
   		IN v_name VARCHAR(128)) 
LANGUAGE SQL 
BEGIN 
DECLARE SQLCODE INT DEFAULT 0; 
DECLARE v_qualifier VARCHAR(128); 
DECLARE v_func_path VARCHAR(1000); 
DECLARE v_stmt VARCHAR(32672);
DECLARE v_stmt2 VARCHAR(32672); 
DECLARE v_curr_qualifier VARCHAR(128); 
DECLARE v_curr_funcpath VARCHAR(1000); 
DECLARE v_owner VARCHAR(128);
DECLARE EXIT HANDLER FOR SQLWARNING 
   		SIGNAL SQLSTATE '80000'  
SET MESSAGE_TEXT = 'Error. TRIG manual recreation required'; 
SET v_curr_qualifier = CURRENT SCHEMA; 
SET v_curr_funcpath = CURRENT FUNCTION PATH; 

SELECT qualifier, func_path, TEXT, owner into v_qualifier, v_func_path, v_stmt, v_owner
FROM ARC.ARC_DISABLED_TRIGS 
  		WHERE trigschema=v_schema and trigname=v_name;
 
SET v_func_path = 'SET CURRENT FUNCTION PATH = ' || v_func_path; 
EXECUTE IMMEDIATE v_func_path; 

SET v_qualifier = 'SET CURRENT SCHEMA = ' || v_qualifier; 
EXECUTE IMMEDIATE v_qualifier; 

EXECUTE IMMEDIATE v_stmt;
 
SET v_stmt2 = 'TRANSFER OWNERSHIP OF TRIG ' || v_schema || '.' || v_name || ' to USER ' || v_owner ||' 

PRESERVE PRIVILEGES';

EXECUTE IMMEDIATE v_stmt2;

DELETE FROM ARC.ARC_DISABLED_TRIGS 
  		WHERE trigschema=v_schema and trigname=v_name; 

SET v_curr_qualifier = 'SET SCHEMA = ' || v_curr_qualifier; 
SET v_curr_funcpath = 'SET CURRENT FUNCTION PATH = ' || v_curr_funcpath; 
EXECUTE IMMEDIATE v_curr_qualifier; 
EXECUTE IMMEDIATE v_curr_funcpath; 
END
$

CREATE PROCEDURE ARC.ARC_DISABLE_TRIG ( 
   		IN v_schema VARCHAR(128),  
   		IN v_name VARCHAR(128)) 
SPECIFIC ARC_DISABLE_TRIG 
LANGUAGE SQL 
BEGIN 
DECLARE SQLCODE INT DEFAULT 0; 
DECLARE v_stmt VARCHAR(250);  
DECLARE EXIT HANDLER FOR NOT FOUND 
   		SIGNAL SQLSTATE '80000'  
       		SET MESSAGE_TEXT='TRIG Not Found';  
DECLARE EXIT HANDLER FOR SQLWARNING 
   		SIGNAL SQLSTATE '80001'  
       		SET MESSAGE_TEXT='Unable to disable TRIG';  
INSERT INTO ARC.ARC_DISABLED_TRIGS 
SELECT 
	TRIGSCHEMA, TRIGNAME, OWNER, TABSCHEMA, TABNAME, 
	QUALIFIER, FUNC_PATH, CAST(TEXT as VARCHAR(31500))
FROM SYSCAT.TRIGGERS
WHERE TRIGSCHEMA = v_schema and TRIGNAME = v_name 
  		AND VALID='Y';  
SET v_stmt =  'DROP TRIGGER ' || v_schema || '.' ||v_name; 
EXECUTE IMMEDIATE v_stmt; 
END
$

[rjj1]

I called each stored procedure separately in my stored procedure, and it works now.

[code]
CONNECT RESET
$
CONNECT TO MAXDB71

$
DROP PROCEDURE ARC.ARC_RESTORE
$

CREATE PROCEDURE ARC.ARC_RESTORE ()
LANGUAGE SQL
BEGIN ATOMIC
DECLARE stmt varchar(32000) default ' ';

set stmt = '
CALL ARC.arc_disable _trig(''MAXIMO' ',''WORKLOG_T'' )
';
execute immediate stmt;
set stmt = '
CALL ARC.arc_disable _trig(''MAXIMO' ',''WORKLOG_U'' )
';
execute immediate stmt;
set stmt = '
CALL ARC.arc_disable _trig(''MAXIMO' ',''LONGDESCRIP TION_T'')
';
execute immediate stmt;
set stmt = '
CALL ARC.arc_disable _trig(''MAXIMO' ',''LONGDESCRIP TION_U'')
';
execute immediate stmt;
set stmt = '
CALL ARC.arc_disable _trig(''MAXIMO' ',''COMMLOG_T'' )
';
execute immediate stmt;
set stmt = '
CALL ARC.arc_disable _trig(''MAXIMO' ',''COMMLOG_U'' )
';
execute immediate stmt;
set stmt = '
CALL ARC.arc_disable _trig(''MAXIMO' ',''PLUSPTKPRIC ETOT_T'')
';
execute immediate stmt;
set stmt = '
CALL ARC.arc_disable _trig(''MAXIMO' ',''PLUSPTKPRIC ETOT_U'')
';
execute immediate stmt;
set stmt = '
CALL ARC.arc_disable _trig(''MAXIMO' ',''PERSISTSTAT US_T'')
';
execute immediate stmt;
set stmt = '
CALL ARC.arc_disable _trig(''MAXIMO' ',''PERSISTSTAT US_U'')
';
execute immediate stmt;
set stmt = '
CALL ARC.arc_disable _trig(''MAXIMO' ',''WORKVIEW_T' ')
';
execute immediate stmt;
set stmt = '
CALL ARC.arc_disable _trig(''MAXIMO' ',''WORKVIEW_U' ')
';
execute immediate stmt;
set stmt = '
CALL ARC.arc_disable _trig(''MAXIMO' ',''TKSTATUS_T' ')
';
execute immediate stmt;
set stmt = '
CALL ARC.arc_disable _trig(''MAXIMO' ',''TKSTATUS_U' ')
';
execute immediate stmt;
set stmt = '
CALL ARC.arc_disable _trig(''MAXIMO' ',''TICKET_T'')
';
execute immediate stmt;
set stmt = '
CALL ARC.arc_disable _trig(''MAXIMO' ',''TICKET_U'')
';
execute immediate stmt;


-- Call your routines here

set stmt = '
CALL ARC.arc_enable_ trig(''MAXIMO'' ,''WORKLOG_T'')
';
execute immediate stmt;
set stmt = '
CALL ARC.arc_enable_ trig(''MAXIMO'' ,''WORKLOG_U'')
';
execute immediate stmt;
set stmt = '
CALL ARC.arc_enable_ trig(''MAXIMO'' ,''LONGDESCRIPT ION_T'')
';
execute immediate stmt;
set stmt = '
CALL ARC.arc_enable_ trig(''MAXIMO'' ,''LONGDESCRIPT ION_U'')
';
execute immediate stmt;
set stmt = '
CALL ARC.arc_enable_ trig(''MAXIMO'' ,''COMMLOG_T'')
';
execute immediate stmt;
set stmt = '
CALL ARC.arc_enable_ trig(''MAXIMO'' ,''COMMLOG_U'')
';
execute immediate stmt;
set stmt = '
CALL ARC.arc_enable_ trig(''MAXIMO'' ,''PLUSPTKPRICE TOT_T'')
';
execute immediate stmt;
set stmt = '
CALL ARC.arc_enable_ trig(''MAXIMO'' ,''PLUSPTKPRICE TOT_U'')
';
execute immediate stmt;
set stmt = '
CALL ARC.arc_enable_ trig(''MAXIMO'' ,''PERSISTSTATU S_T'')
';
execute immediate stmt;
set stmt = '
CALL ARC.arc_enable_ trig(''MAXIMO'' ,''PERSISTSTATU S_U'')
';
execute immediate stmt;
set stmt = '
CALL ARC.arc_enable_ trig(''MAXIMO'' ,''WORKVIEW_T'' )
';
execute immediate stmt;
set stmt = '
CALL ARC.arc_enable_ trig(''MAXIMO'' ,''WORKVIEW_U'' )
';
execute immediate stmt;
set stmt = '
CALL ARC.arc_enable_ trig(''MAXIMO'' ,''TKSTATUS_T'' )
';
execute immediate stmt;
set stmt = '
CALL ARC.arc_enable_ trig(''MAXIMO'' ,''TKSTATUS_U'' )
';
execute immediate stmt;
set stmt = '
CALL ARC.arc_enable_ trig(''MAXIMO'' ,''TICKET_T'')
';
execute immediate stmt;
set stmt = '
CALL ARC.arc_enable_ trig(''MAXIMO'' ,''TICKET_U'')
';
execute immediate stmt;

END
$
[code]