Need help on SP with GTT

[pompeyoc]

Hi!

We are having problems with an SQL stored proc that uses Global
Temporary Tables. The SP creates the GTT as follows:
DECLARE GLOBAL TEMPORARY TABLE TEMP_ENTRIES (field1, field2, etc)
ON COMMIT PRESERVE ROWS NOT LOGGED IN TEMP_SPACE

The table is then filled with records from other permanent tables
using:
INSERT INTO SESSION.TEMP_EN TRIES SELECT fields FROM table1, table2
WHERE condition

Not all fields are filled.

A cursor is declared to read all the records in the table then enters
a loop to update the blank fields of each row as follows:
FETCH FROM cursorname INTO fieldx, fieldy, etc
[process data]
UPDATE SESSION.TEMP_EN TRIES SET fields WHERE keyfield = value

The loop is controlled by the at_end variable set by the command:
DECLARE CONTINUE HANDLER FOR NOT_FOUND SET at_end = 1

It's supposed to exit the loop when at_end = 1, i.e., there are no
more records in the GTT. Then it uses the contents of the GTT to
insert records into a permanent table.

Our test case involved inserting 2 records into the GTT. Everything
works fine when testing it using the Stored Procedure Builder or when
we have a VB.Net (in Windows XP) or COBOL (in AIX) program run it.
Both records are written to the permanent table. The problem comes out
when we invoke the SP using a COBOL program written as an SP. After
processing the first record, the at_end variable is immediately set to
1 and exits the loop. I couldn't find any bug in the COBOL SP that may
have caused it. I tried increasing the APP_CTL_HEAP_SZ and APPLHEAPSZ
from 128 to 512 (thinking that there wasn't enough memory) but still
nothing. I have absolutely no idea what to do next.

NOTE: The COBOL SP is called by a VB program in Windows or a COBOL
program in AIX. Neither of them works.

Please help!!!

Environment is as follows:
AIX 5.2
UDB 7.2
MFCOBOL OCDS 4.1

Thanks in advance.

Pompeyo C

[Tonkuma]

Re: Need help on SP with GTT

Although I felt that your description of processing logic of SP is too
general to say accurate things, I had some questions...
1) To what value was at_end initialized? And at where?
2-1) Is the loop to update the blank fields neccesary?
Is it possible to be replaced by an UPDATE statement with something
like CASE expressions and/or appropriate WHERE conditions?
2-2) or is it possible by using expressions in the SELECT list of
first INSERT statement(witho ut using the UPDATE statement)?
INSERT INTO SESSION.TEMP_EN TRIES SELECT fields/* change these by
expressions to replace blanks */ FROM table1, table2
WHERE condition

[pompeyoc]

Re: Need help on SP with GTT

On Aug 8, 4:13 pm, Tonkuma <tonk...@fiberb it.netwrote:

Although I felt that your description of processing logic of SP is too
general to say accurate things, I had some questions...
1) To what value was at_end initialized? And at where?
2-1) Is the loop to update the blank fields neccesary?
 Is it possible to be replaced by an UPDATE statement with something
like CASE expressions and/or appropriate WHERE conditions?
2-2) or is it possible by using expressions in the SELECT list of
first INSERT statement(witho ut using the UPDATE statement)?
INSERT INTO SESSION.TEMP_EN TRIES SELECT fields/* change these by
expressions to replace blanks */ FROM table1, table2
WHERE condition

Thanks for the immediate reply. To answer your questions:
1) The at_end variable was initialized to 0 before the DECLARE CURSOR
and DECLARE CONTINUE HANDLER statements with the command:
DECLARE at_end INT DEFAULT 0
2-1 and 2-2) Unfortunately, yes the loop is necessary because it needs
to call 2 other SP's to process the data in each row, which also means
I can't change the INSERT statement to get all the data

I'd be happy to send more info if you wish. I just took the major
parts of the program to make the message shorter.

Thanks!

Pompeyo C

[Serge Rielau]

Re: Need help on SP with GTT

pompeyoc wrote:

On Aug 8, 4:13 pm, Tonkuma <tonk...@fiberb it.netwrote:

>Although I felt that your description of processing logic of SP is too
>general to say accurate things, I had some questions...
>1) To what value was at_end initialized? And at where?
>2-1) Is the loop to update the blank fields neccesary?
> Is it possible to be replaced by an UPDATE statement with something
>like CASE expressions and/or appropriate WHERE conditions?
>2-2) or is it possible by using expressions in the SELECT list of
>first INSERT statement(witho ut using the UPDATE statement)?
>INSERT INTO SESSION.TEMP_EN TRIES SELECT fields/* change these by
>expressions to replace blanks */ FROM table1, table2
>WHERE condition

>
Thanks for the immediate reply. To answer your questions:
1) The at_end variable was initialized to 0 before the DECLARE CURSOR
and DECLARE CONTINUE HANDLER statements with the command:
DECLARE at_end INT DEFAULT 0
2-1 and 2-2) Unfortunately, yes the loop is necessary because it needs
to call 2 other SP's to process the data in each row, which also means
I can't change the INSERT statement to get all the data
>
I'd be happy to send more info if you wish. I just took the major
parts of the program to make the message shorter.

Ar eyou sure teh variable is actually set and you do not receive a
different error.
One way fro a caller to mess with what teh callee does is to mark the
caller as READS SQL DATA. In that case your INSERT should fail. Perhaps
that is what really happens...

Have you stepped it through the debugger?

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[pompeyoc]

Re: Need help on SP with GTT

On Aug 8, 7:15 pm, Serge Rielau <srie...@ca.ibm .comwrote:

pompeyoc wrote:

On Aug 8, 4:13 pm, Tonkuma <tonk...@fiberb it.netwrote:

Although I felt that your description of processing logic of SP is too
general to say accurate things, I had some questions...
1) To what value was at_end initialized? And at where?
2-1) Is the loop to update the blank fields neccesary?
 Is it possible to be replaced by an UPDATE statement with something
like CASE expressions and/or appropriate WHERE conditions?
2-2) or is it possible by using expressions in the SELECT list of
first INSERT statement(witho ut using the UPDATE statement)?
INSERT INTO SESSION.TEMP_EN TRIES SELECT fields/* change these by
expressions to replace blanks */ FROM table1, table2
WHERE condition

>

Thanks for the immediate reply. To answer your questions:
1) The at_end variable was initialized to 0 before the DECLARE CURSOR
and DECLARE CONTINUE HANDLER statements with the command:
DECLARE at_end INT DEFAULT 0
2-1 and 2-2) Unfortunately, yes the loop is necessary because it needs
to call 2 other SP's to process the data in each row, which also means
I can't change the INSERT statement to get all the data

>

I'd be happy to send more info if you wish. I just took the major
parts of the program to make the message shorter.

>
Ar eyou sure teh variable is actually set and you do not receive a
different error.
One way fro a caller to mess with what teh callee does is to mark the
caller as READS SQL DATA. In that case your INSERT should fail. Perhaps
that is what really happens...
>
Have you stepped it through the debugger?
>
Cheers
Serge
>
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab- Hide quoted text -
>
- Show quoted text -

Yes. I also added INSERT commands that write messsages to a table
(sort of a debug table) and it clearly shows that the variable was set
to 1 immediately after processing the first record.

It works fine if I run it in Stored Proc Builder. I also have no
problems when I call it from a VB program in Windows or from an
ordinary COBOL program in AIX. The problem only surfaces when its a
COBOL SP that invokes it. The COBOL SP was created with MODIFIES SQL
DATA.

[Tonkuma]

Re: Need help on SP with GTT

1) The at_end variable was initialized to 0 before the DECLARE CURSOR

and DECLARE CONTINUE HANDLER statements with the command:
DECLARE at_end INT DEFAULT 0

Just before entering the loop, is the value of at_end 0?
If yes, how did you confirmed that?

[pompeyoc]

Re: Need help on SP with GTT

On Aug 8, 8:29 pm, Tonkuma <tonk...@fiberb it.netwrote:

1) The at_end variable was initialized to 0 before the DECLARE CURSOR
and DECLARE CONTINUE HANDLER statements with the command:
DECLARE at_end INT DEFAULT 0

>
Just before entering the loop, is the value of at_end 0?
If yes, how did you confirmed that?

Yes. The DECLARE at_end INT DEFAULT 0 command is placed just before
entering the loop. Also, when I run it using the Stored Proc builder,
it is set to 0 after processing the first record then set to 1 after
processing the second record