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 6 2937
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
On Aug 8, 4:13*pm, Tonkuma <tonk...@fiberbit.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(without using the UPDATE statement)?
INSERT INTO SESSION.TEMP_ENTRIES 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
pompeyoc wrote:
On Aug 8, 4:13 pm, Tonkuma <tonk...@fiberbit.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(without using the UPDATE statement)? INSERT INTO SESSION.TEMP_ENTRIES 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
On Aug 8, 7:15*pm, Serge Rielau <srie...@ca.ibm.comwrote:
pompeyoc wrote:
On Aug 8, 4:13 pm, Tonkuma <tonk...@fiberbit.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(without using the UPDATE statement)?
INSERT INTO SESSION.TEMP_ENTRIES 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.
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?
On Aug 8, 8:29 pm, Tonkuma <tonk...@fiberbit.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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Sofia |
last post by:
My name is Sofia and I have for many years been running a personals site,
together with my partner, on a non-profit basis. The site is currently not
running due to us emigrating, but during its...
|
by: Robert Maas, see http://tinyurl.com/uh3t |
last post by:
System login message says PHP is available, so I tried this:
http://www.rawbw.com/~rem/HelloPlus/h.php
It doesn't work at all. Browser just shows the source.
What am I doing wrong?
|
by: Gregory Nans |
last post by:
hello,
i need some help to 'tree-ify' a string...
for example i have strings such as :
s = """A(here 's , B(A ) silly test) C(to show D(what kind) of stuff i
need))"""
and i need to...
|
by: Mike Kamermans |
last post by:
I hope someone can help me, because what I'm going through at the moment
trying to edit XML documents is enough to make me want to never edit XML
again.
I'm looking for an XML editor that has a...
|
by: JustSomeGuy |
last post by:
I need to write an new class derived from the list class.
This class stores data in the list to the disk if an object
that is added to the list is over 1K in size.
What methods of the std stl...
|
by: Bob.Henkel |
last post by:
I write this to tell you why we won't use postgresql even though we wish we
could at a large company. Don't get me wrong I love postgresql in many
ways and for many reasons , but fact is fact. If...
|
by: Michael R. Pierotti |
last post by:
Dim reg As New Regex("^\d{1,3}.\d{1,3}.\d{1,3}.\d{1,3}$")
Dim m As Match = reg.Match(txtIPAddress.Text)
If m.Success Then
'No need to do anything here
Else
MessageBox.Show("You need to enter a...
|
by: skumar434 |
last post by:
i need to store the data from a data base in to structure
.............the problem is like this ....suppose there is a data base
which stores the sequence no and item type etc ...but i need only...
|
by: Alan Mailer |
last post by:
A project I'm working on is going to use VB6 as a front end. The back
end is going to be pre-existing MS Access 2002 database tables which
already have records in them *but do not have any...
|
by: U S Contractors Offering Service A Non-profit |
last post by:
Brilliant technology helping those most in need Inbox
Reply
U S Contractors Offering Service A Non-profit
show details
10:37 pm (1 hour ago)
Brilliant technology helping those most in need ...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
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...
|
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...
|
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
|
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...
|
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...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
| |