Results 1 to 10 of 10
  1. #1
    Join Date
    Sep 2012
    Posts
    5

    Unanswered: Strange error in FETCH, Please help.

    Hi All,

    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

    -1822/-151"

    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
    SQLSTATE 09000.

    When I directly execute the declare cursor, open cursor statements in
    server, they are fine, but for fetch/close cursor I am getting the
    following error:

    " 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.

  2. #2
    Join Date
    Sep 2012
    Posts
    5

    Experts!!? Please help.

    Really does no one doesn't know the answer to my question??

  3. #3
    Join Date
    Mar 2003
    Posts
    280
    Is there a particular reason why you did not reveal your platform and version of DB2? It may also generate more answers if you provide ddl for a simplified scenario that demonstrate the error you are encountering.

  4. #4
    Join Date
    Oct 2009
    Location
    221B Baker St.
    Posts
    486
    Really does no one doesn't know the answer to my question??
    So far, not. Do keep in mind tghat you initially posted in the middle of Saturday afternoon in the US. Many people do other things for much of the weekend.

    From what i read, you provided lots of detail, but maybe not info that would help us help you. Said slightly differently from what lelle12 posted, try to create a much more simple situation that will still produce the failure.

    In the past i have seen several situations that caused a similar error. In some, a SQL statement failed, but was not detected. In some, the cursor was successfully closed, but another fetch was attempted.

  5. #5
    Join Date
    Sep 2012
    Posts
    5
    Hi lelle12 and papadi, Thanks for the reply.

    I am working on DB2 v9.5.0.4.Sorry I am not able provide the DDL.

    @papadi: I got your point papadi, I thought people will be checking forums even on weekends.
    As you said, after posting only I realized that I have provided lots of information. Really sorry for that, I could have simplified the problem, but don't know why I provided hell a lot of information.

    Below is the simplified version:

    I have a view called DATA and a table DATA_ENC, which is same as the view DATA with some columns encrypted.

    I have triggers for operations like Insert, Update and Delete performed on the view DATA, to perform the same on the table DATA_ENC.

    Example:

    create trigger d_enc_update
    instead of update on DATA
    .....
    .....
    update DATA_ENC set ...
    ....

    end

    View definition:

    create view DATA as Select * from DATA_ENC.

    In the program, I have a cursor as follows:

    declare datcur cursor as
    select * from DATA where ord_nbr = :ws-ord-nbr

    open datacur - this is successful.

    fetch datacur - below 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.

    For close statement it gives sqlcode -501,saying that the cursor is not open.

    When I directly execute the declare cursor, open cursor statements in
    server, they are fine, but for fetch/close cursor I am getting the
    following error:

    " DB21030E The cursor "cursor-name" has not been opened."

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    How does federation fit into that picture?
    ---
    "It does not work" is not a valid problem statement.

  7. #7
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    declare datcur cursor as <<---- typo or should it be datacur
    open datacur - this is successful.
    fetch datacur - below error
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  8. #8
    Join Date
    Sep 2012
    Posts
    5
    Quote Originally Posted by n_i View Post
    How does federation fit into that picture?
    Hi n_i,

    I am not getting you.

    Thanks.

  9. #9
    Join Date
    Sep 2012
    Posts
    5
    Quote Originally Posted by przytula_guy View Post
    declare datcur cursor as <<---- typo or should it be datacur
    open datacur - this is successful.
    fetch datacur - below error
    Hi,
    Thanks for your reply. It is a typo.

  10. #10
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by vive View Post
    I am not getting you.
    The error code suggests that you are querying a federated data source, which generates the underlying error (-151), so I'm wondering if you know what kind of a data source that is and what happens there that generates that error.

    You may want to look at the explain plan of the query defining the cursor.

    Since you're not providing the actual code or error messages, I doubt you can get more specific suggestions.
    ---
    "It does not work" is not a valid problem statement.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •