September 29th, 2012, 03:02 PM
Strange error in FETCH, Please help.
I have a error scenario in QA DB2 database, which I am not able to
resolve and truly helpless. Pls help me regarding this.
To briefly say:
"Fetch statement of a cursor defined for a VIEW fails with SQLCODE
To say in detail:
Previously I had a table called DATA.
For security purposes, table DATA was dropped, and converted into a VIEW.
Now it is a VIEW called DATA. A table DATA_ENC is created, which will
store all information which DATA stores with some columns encrypted.
I defined TRIGGERS for INSERT, DELETE and UPDATE operations that will be
performed on the previous TABLE DATA, so that the same data can be I/D/U
in the encrypted table DATA_ENC.
That is, as follows:
Trigger - Insert on view DATA = Insert on TABLE DATA_ENC.
Triggers are created and just SELECT statements alone in the application
programs are modified to use the encrypted table DATA_ENC.
Consider another TABLE BATA, which has gone through the same process as DATA.
The issue is as follows:
I have a program which uses both DATA and BATA. It has cursor for both
DATA and BATA, that will select some columns in them.
I have two versions of that program, the first one in which DATA is
replaced by DATA_ENC in select, the 2nd one in which both DATA, BATA
replaced by DATA_ENC, BATA_ENC respectively.
First DATA table goes thru the above stated process in QA, and the first
version is deployed. It works fine.
Now BATA table goes thru the above stated process in QA, now the first
program fails, but when second version which has changes for both tables
was deployed, it did not fail.
Ideally speaking the first program should not fail, even after BATA_ENC
is created, since there is still a view with the name BATA.
The first version failed,in the FETCH statement of the cursor defined for
BATA with the following error:
SQL1822N Unexpected error code "-151" received from data source "DB-name"
... The column "a-column-name" cannot be update". SQLSTATE=560BD?
The thing is that is a fetch statement and not a update statement, and
the column specified in the error is not even selected in the cursor.
When I check the sqlcode for open of that cursor, it is 0, but for close
statement it gives sqlcode -501,saying that the cursor is not open.
The DELETE statement for the view BATA, fails with SQL0723N and
When I directly execute the declare cursor, open cursor statements in
server, they are fine, but for fetch/close cursor I am getting the
" DB21030E The cursor "cursor-name" has not been opened."
Please help me regarding this issue, this looks really strange to me,
because when both tables are encrypted, first program fails, but
second one doesn't.
Thanks everyone in advance.