DB2/6000 CURSOR W HOLD / NOT NULL W DEFAULT PROBLEM
ITEM: RTA000051204
QUESTION:
My customer is placing their DB2 MVS/ESA DataBases in DB2/6000 on a
Risc/6000. We have run into two problems with porting the data and
application.
1) Application - A program issues a select with cursor hold. They
get an sql30090n error. One of the reason codes states:
. 03 - an SQL OPEN for a held cursor was issued while in a
XA/DTP environment. Held cursor is not supported.
Does this mean that this function is not supported at all in
DB2/6000? If this is true, how do we port applications that
make use of "hold with cursor"?
2) Data - The customer has defined columns in a DB2/6000 table with
the attribute of "not null with default". This table is loaded
and the customer is exporting and importing the data into another
DB2/6000 table. The export and import work but they get errors
on processing the second table. They have been told that
"Export/Import With Create does not support NOT NULL WITH DEFAULT"
is this true?
---------- ---------- ---------- --------- ---------- ----------
A: Please refere to the libraried Item CSWCN for 2) Data in your
question. The conclusion from this item are three possible workarounds:
1. Export the data specifying delimeters
2. Create the table with nullable fields
3. Export the data in IXF format
---------- ---------- ---------- --------- ---------- ----------
QUESTION:
Will there be answer for Question 1, on Cursor with Hold?
I have read your response to Question 2, Not Null With Default and
then read Item CSWCN. The three items that were described in this
were indicated as workarounds. Will Toronto development change
this behavior, so we will not have to use workarounds?
---------- ---------- ---------- --------- ---------- ----------
A: 1. HOLD with XA interface:
The Programming Guide (SC09-1572-00) section on XA
interface programming considerations:
" When a cursor is declared with the WITH HOLD option, the cursor
will be left open when a transaction commits. This allows the next
transaction within the same program to continue from the same cursor
position without re-opening and re-positioning the cursor. However,
since a server process is reused for transactions from different
user programs without any way for the RM to find out when they are
switched, a held cursor left open from a previous transaction may
be of no use to the next transaction unless it is guaranteed to be
from the same user in the same program. To avoid causing
unexpected logic errors in the transactions, any attempt to open a
WITH HOLD cursor in the CICS/6000 environment will be rejected with
a SQLSTATE 56026 (SQLCODE -30090).
I assume you are selecting many rows which will be processed
by a user on subsequent transactions, several rows per transaction.
You must either:
a. Select and fetch all the data and save in a temporary work
area outside the database. This can cause problems if the
data in the database gets modified from the value you have
saved outside the database.
b. Issue the Select for all the rows but only retrieve the primary
key values for the answer set; save the keys, and issue
additional Select's in each transaction for only the rows to
be used in that transaction.
c. Re-retrieve all the data on each transaction. Expensive if the
answer set is large.
2. When you export a table and its definition in IXF format, the
NOT NULL is retained, but the WITH DEFAULT is dropped. This
has been reported as a defect, and was returned by the developers
as "working as designed" untill a change can be made to all the
DB2 platforms so that they work in the same way. Eventually
the WITH DEFAULT will be retained. I reviewed the change
specifications for DB2/2 & DB2/6000 Version2, and I do not see
this change detailed in the specifications for that release.
---------- ---------- ---------- --------- ---------- ----------
This item was created from library item Q674730 FDTMP
Additional search words:
CURSOR DB2 DEFAULT FDTMP HOLD IX JAN95 NON NULL OZNEW PROBLEM
RISCDB2 RISCSYSTEM SOFTWARE 6000
WWQA: ITEM: RTA000051204 ITEM: RTA000051204
Dated: 01/1995 Category: RISCDB2
This HTML file was generated 99/06/24~12:43:19
Comments or suggestions?
Contact us