DB2 Nested Stored Procedures and labeled_begin_atomic error

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_CMD 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_CMD 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:

Expand|Select|Wrap|Line Numbers

 
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.

Expand|Select|Wrap|Line Numbers

 
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

$

Jan 23 '12 #1

Subscribe Post Reply

✓ answered by 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'',''LONGDESCRIPTION_ T'')
';
execute immediate stmt;
set stmt = '
CALL ARC.arc_disable_trig(''MAXIMO'',''LONGDESCRIPTION_ 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'',''PLUSPTKPRICETOT_ T'')
';
execute immediate stmt;
set stmt = '
CALL ARC.arc_disable_trig(''MAXIMO'',''PLUSPTKPRICETOT_ U'')
';
execute immediate stmt;
set stmt = '
CALL ARC.arc_disable_trig(''MAXIMO'',''PERSISTSTATUS_T' ')
';
execute immediate stmt;
set stmt = '
CALL ARC.arc_disable_trig(''MAXIMO'',''PERSISTSTATUS_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'',''LONGDESCRIPTION_T '')
';
execute immediate stmt;
set stmt = '
CALL ARC.arc_enable_trig(''MAXIMO'',''LONGDESCRIPTION_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'',''PLUSPTKPRICETOT_T '')
';
execute immediate stmt;
set stmt = '
CALL ARC.arc_enable_trig(''MAXIMO'',''PLUSPTKPRICETOT_U '')
';
execute immediate stmt;
set stmt = '
CALL ARC.arc_enable_trig(''MAXIMO'',''PERSISTSTATUS_T'' )
';
execute immediate stmt;
set stmt = '
CALL ARC.arc_enable_trig(''MAXIMO'',''PERSISTSTATUS_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]

2692

rjj1

Jan 25 '12 #2

Similar topics

Stored procedures and CTRL+ALT+DEL

by: Ryan | last post by:

We have a suppliers application that runs a stored procedure (one of many). This stored procedure then calls various other ones etc... and the final number of stored procedures run is 11. Now,...

Microsoft SQL Server

Stored Procedures

by: Jarrod Morrison | last post by:

Hi all Im relatively new to using stored procedures and im not sure if it is possible to do what I am trying to do so any help here is greatly appreciated. I am using the variable @MachineName...

Microsoft SQL Server

nested queries, stored procedures, temporary table

by: ahokdac-sql | last post by:

Hi, I'm adapting access queries to sql server and I have difficulties with the following pattern : query1 : SELECT * FROM Query2 WHERE A=@param1 query 2: SELECT * FROM Table2 WHERE B=@param2 ...

Microsoft SQL Server

Nested Stored Procedures and Packages

by: Anthony Robinson | last post by:

Have been encountering an odd issue. Every now and again, certain packages of stored procedures just become invalid. I'm aware that dropping or altering an underlying table would render a package...

DB2 Database

problem using java packages in jdbc stored procedures

by: Kent Lewandowski | last post by:

hi all, Recently I wrote some stored procedures using java jdbc code (admittedly my first stab) and then tried to implement the same within java packages (for code reuse). I encountered...

DB2 Database

Passing a resultset from a nested Stored Procedure

by: Twan Kennis | last post by:

Question: How do I pass a returning resultset from a nested Stored Procedure (which opens a cursor including option "WITH RETURN TO CALLER") as a returning resultset from it's own? When I...

DB2 Database

Views vs Stored Procedures, whats the difference?

by: mooreit | last post by:

The purpose for my questions is accessing these technologies from applications. I develop both applications and databases. Working with Microsoft C#.NET and Microsoft SQL Server 2000 Production and...

Microsoft SQL Server

Why the log file is growing too much upon running nested stored procedures (MS SQL 2K)

by: sasachi sachi sachi | last post by:

Hi there, I have a data manipulation process written in a Nested Stored procedure that have four levels deeper. When I run these individual procedures individually they all seems to be fine....

Microsoft SQL Server

Stored procedures and stored functions

by: svkreddy | last post by:

Dear All, Please provide the simple information about stored procedures and stored functions in microsoft sql server2005. Please give the information about Nested procedures and nested funtions....

Microsoft SQL Server

nested stored procedures and returning lots of rows

by: codefragment | last post by:

Hi I have a chunky bit of sql that I will want to call from a number of places. It will return a few thousand rows. Whats the best way of structuring this? 1) I initially thought of using...

Microsoft SQL Server

Batch import of multiple excel files into the database

by: ryjfgjl | last post by:

If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...

Data Management

Merging data from multiple Excel files

by: ryjfgjl | last post by:

In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...

Data Management

Migrating Website to Cloud - Emmanuel Katto

by: emmanuelkatto | last post by:

Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel

General

Navigating the Data Structures and Algorithms (DSA)

by: BarryA | last post by:

What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...

Algorithms / Advanced Math

How to build RAID in BIOS?

by: Hystou | last post by:

There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

Computer Hardware

Changing the language in Windows 10

by: Hystou | last post by:

Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...

Windows Server

Maximizing Business Potential: The Nexus of Website Design and Digital Marketing

by: jinu1996 | last post by:

In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...

Online Marketing

The easy way to turn off automatic updates for Windows 10/11

by: Hystou | last post by:

Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...

Windows Server

Discussion: How does Zigbee compare with other wireless protocols in smart home applications?

by: tracyyun | last post by:

Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...

General