What is the real effect of begin/end statements on the scope of operations in a procedure?

I can only find discussions on compound SQL with begin atomic - which I can understand.

However, I keep getting errors like:
DB2ADMIN.LSH_ACCOUNTINFO: 252: [IBM][CLI Driver][DB2/NT] SQL0104N An unexpected token "<cursor declaration>" was found following "". Expected tokens may include: "<SQL statement>". LINE NUMBER=252. SQLSTATE=42601

Even though I can work around these by moving stuff around, I don't understand the principle in operation and it's taking too long to try and test each case, especially with complex procedures.

Sometimes if I try to block things out with begin/end statements, my temporary tables get filled two and three times because the begin/end scopes interfere with the 'with replace' statement on the temporary table.

For example, what's the proper order to handle a structure like the following

Create proc proctest
( ins and outs)
meta stuff...

1. variable declarations
2. temporary table declarations
3. cursor declarations
4. open and fetch cursors into variables
5. update the temporary tables
6. set the out parameters
7. create open and fetch the returned tables
8. get outa here

In general this seems to work, but not always.

THanks,

Kimball

As far as I know, the purpose of BEGIN..END is to enclose a group of statements.

From a syntax (grammar) point of view, it allows the grammar to say that one possible definition of an "<SQL statement>" is: BEGIN, followed by zero or more "<SQL statements>" (each one of which can, of course, be a nested BEGIN..END block), followed by END. With these two symbols clearly marking the beginning and end of the "group of zero or more statements," SQL can un-ambiguously parse and interpret what you have written.

Ok, I mean, I get that part.

But there's clearly some rules of scoping going on because some errors occur unless you have certain statements enclosed in a b/e scope, but only at certain times.

Please see my further questions about how to lay out a procedure in my first post.

For example, in 'Examples of SQL Procedures' from the DB2 docs, what's going on here?

DECLARE CONTINUE HANDLER FOR NOT FOUND, SQLEXCEPTION, SQLWARNING
BEGIN
SET result_set_end = 1;
END;

I've also seen the continue handler/set combination without a b/e scope block.

That's the kind of answer I need.

Thanks,

Kimball

This produces an error saying the result-set-locator is in the wrong place:

DECLARE rcr_ratio_2_text varchar(20);
DECLARE borrowing_base_amount decimal(19,2);
--
SET structure_code = txtStructureCode;
SET identifier_code = txtIdentifierCode;
--
DECLARE l_avail_stucts RESULT_SET_LOCATOR VARYING;
DECLARE l_group_desc RESULT_SET_LOCATOR VARYING;
--
DECLARE at_end INTEGER DEFAULT 0;
DECLARE not_found CONDITION FOR SQLSTATE '02000';
DECLARE CONTINUE HANDLER FOR not_found
SET at_end = 1;
--
DECLARE GLOBAL TEMPORARY TABLE g_availability
(
intRecordID integer,
) setting stuff...

But this does not produce an error:

DECLARE rcr_ratio_2_text varchar(20);
DECLARE borrowing_base_amount decimal(19,2);
--
DECLARE l_avail_stucts RESULT_SET_LOCATOR VARYING;
DECLARE l_group_desc RESULT_SET_LOCATOR VARYING;
--
DECLARE at_end INTEGER DEFAULT 0;
DECLARE not_found CONDITION FOR SQLSTATE '02000';
DECLARE CONTINUE HANDLER FOR not_found
SET at_end = 1;
--
SET structure_code = txtStructureCode;
SET identifier_code = txtIdentifierCode;
--
DECLARE GLOBAL TEMPORARY TABLE g_availability
(
intRecordID integer,
) setting stuff...

So why does the DECLARE RESULT_SET_LOCATOR care that some variables were set in between all the declarations, and the DECLARE GLOBAL TEMPORARY TABLE not care?

Thanks,

Kimball

BEGIN - END just identifies a compound statement. In terms of the condition handlers you describe, if you want your handler to perform multiple actions, you need to group them together as a compound statement.

e.g.

DECLARE CONTINUE HANDLER FOR NOT FOUND, SQLEXCEPTION, SQLWARNING
BEGIN
SET result_set_end = 1;
SET something_else = 0;
END;

Without the BEGIN-END, the handler declaration would assume that only the first action is associated with it.

As to why you have to declare certain types/objects in a particular order, you just do - it's syntax! Again, the syntax rules apply within a BEGIN-END block, so you can declare variables in a new block , even if you've previously declared a cursor.

The issue with cursors referencing a GTT having to be declared in a nested BEGIN-END block isn't entirely clear. There is no real reason why this should be the case (AFAIK) other than the compiler gets its knickers in a twist otherwise but is easily placated by this simple 'trick'.

If you're having 'scope' problems as you say with GTTs being declared WITH REPLACE, all I can think you really mean is that you need the GTT declaration (for the compiler) but don't really want to run the statement? If this is the case, employ another simple trick and wrap the declaration in a conditional statement that can never be true. The compiler will be happy and your GTT will not get replaced!


HTH

Thanks very much for the explanation.

It confirms what I was thinking but couldn't verify.

As to the 'with replace' for the temporary table. I put that in because it looked to me like the table was hanging about in subsequent calls and giving me some wierd data. I figured that mulitple users might get criss-crosssed.

Thanks again.

Kimball

To my mind, 'session' is synonymous with 'connection' (if that's not entirely true can someone please correct me?). The session GTT will therefore persist within the connection. Use ON COMMIT DELETE ROWS if you want to ensure that the table is 'fresh' within each unit of work.