Question posted by: pompeyoc
(Guest)
on
August 8th, 2008 07:45 AM
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_ENTRIES 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_ENTRIES 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
|
|
August 8th, 2008 08:15 AM
# 2
|
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(without using the UPDATE statement)?
INSERT INTO SESSION.TEMP_ENTRIES SELECT fields/* change these by
expressions to replace blanks */ FROM table1, table2
WHERE condition
