Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2011
    Posts
    36

    Unanswered: Set Integrity problem with generated fields

    I am trying to troubleshoot a massive upgrade for a DB2 DB sent to me by a software vendor (they made some mistakes). In particular, this series of statements is giving me trouble (ZONE being a pre-existing table having pre-existing fields ZONE_ID and SHORT_DESCRIPTION):

    SET INTEGRITY FOR ZONE OFF
    ALTER TABLE ZONE ADD COLUMN ZONE_ID_UP GENERATED ALWAYS AS (UPPER(ZONE_ID))
    ALTER TABLE ZONE ADD COLUMN SHORT_DESCRIPTION_UP GENERATED ALWAYS AS (UPPER(SHORT_DESCRIPTION))
    SET INTEGRITY FOR ZONE IMMEDIATE CHECKED FORCE GENERATED

    The first three present no problem, but the fourth (last) statement results in a SQL0668N error Operation not allowed for reason code 1 on table ZONE. SQLSTATE=57016

    All the documentation I can find indicates that reason code 1 means that the table is in Set Integrity Pending No Access state; however, what I think I am trying to do is to take it out of that state.

    If I run this instead: SET INTEGRITY FOR ZONE IMMEDIATE CHECKED (i.e. omit "FORCE GENERATED"), then I get an error indicating that the SET INTEGRITY has found an integrity violation...[on] SHORT_DESCRIPTION_UP. Well, naturally, since the FORCE GENERATED never took place.

    Admittedly not knowing quite what to do, I tried manually updating the field values to values that will satisfy the constraint by running this:

    UPDATE ZONE SET ZONE_ID_UP = UPPER(ZONE_ID)

    However, this (along with any attempt to SELECT records from this table) results in the same "Operation not allowed for reason code 1 on table ZONE" error as when trying SET INTEGRITY FOR ZONE IMMEDIATE CHECKED FORCE GENERATED. Nor can I drop either of the two newly-created columns, for the same reason.

    Then I tried creating an Exception table called ZONE_TEMP identical to my ZONE table but with all constraints removed, then running this:

    SET INTEGRITY FOR ZONE IMMEDIATE CHECKED FOR EXCEPTION IN ZONE USE ZONE_TEMP

    But, alas, now I get an SQL3604N error indicating that my Exception table "does not have the correct structure...blah, blah, blah...or is if invalid type" And this is after several iterations of dropping the exception table and recreating it, removing anything that even looks like a constraint (no PK, no defaults, no NOT NULLs).

    So I seem to be rather stuck: I cannot drop the columns, and I cannot SET INTEGRITY with them there.
    Last edited by Brian.Hart; 11-29-14 at 03:11. Reason: Extended

  2. #2
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi Brian,

    You can try this:
    Code:
    SET INTEGRITY FOR ZONE GENERATED COLUMN IMMEDIATE UNCHECKED;
    UPDATE ZONE SET (ZONE_ID_UP, SHORT_DESCRIPTION_UP)=(DEFAULT, DEFAULT);
    Regards,
    Mark.

  3. #3
    Join Date
    Feb 2011
    Posts
    36
    That ran correctly, but when I now select from the newly-created fields ZONE_ID_UP or SHORT_DESCRIPTION_UP, all I get is empty strings. That is, SELECT COUNT(*) FROM ZONE WHERE ZONE_ID_UP <> '' produces a count of 0. The same is true for SHORT_DESCRIPTION_UP.

    Also, I do not understand why I could not run this alone:

    SET INTEGRITY FOR ZONE GENERATED COLUMN IMMEDIATE UNCHECKED

    and then run the update statement.

    What is it about running them both together that make your statement work. Is it that the COMMIT takes place over both statements together?

  4. #4
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Brian,

    I'm not sure that I've got you correctly.

    Consider the following sequence of statements:

    SET INTEGRITY FOR ZONE OFF;
    ALTER TABLE ZONE ADD COLUMN ZONE_ID_UP GENERATED ALWAYS AS (UPPER(ZONE_ID));
    ALTER TABLE ZONE ADD COLUMN SHORT_DESCRIPTION_UP GENERATED ALWAYS AS (UPPER(SHORT_DESCRIPTION));
    SET INTEGRITY FOR ZONE GENERATED COLUMN IMMEDIATE UNCHECKED;


    The latest statement instruct DB2 not to generate values for your generated columns.
    If you run your "SELECT COUNT(*) FROM ZONE WHERE ZONE_ID_UP <> ''" statement immediately after the latest statement above, you really get the result you described.
    But if you run your select count(*) after the following statement:
    UPDATE ZONE SET (ZONE_ID_UP, SHORT_DESCRIPTION_UP)=(DEFAULT, DEFAULT)
    and get 0, then it's really strange and I can't understand why you get such a result.
    Regards,
    Mark.

  5. #5
    Join Date
    Feb 2011
    Posts
    36
    I see part of the problem now. The fourth line in my original sequence was this:
    SET INTEGRITY FOR ZONE IMMEDIATE CHECKED FORCE GENERATED
    This failed on the last line with reason code 1. I then tried this instead:
    SET INTEGRITY FOR ZONE IMMEDIATE UNCHECKED
    I did not notice that your syntax for the SET INTEGRITY was this instead (i.e. "GENERATED COLUMN" after "ZONE" and before "IMMEDIATE UNCHECKED"):
    SET INTEGRITY FOR ZONE GENERATED COLUMN IMMEDIATE UNCHECKED
    So I can only guess that I must have omitted the "GENERATED COLUMN" when I ran your two lines. I now dropped my two problematic columns and restarted the process, being careful to use your SET INTEGRITY statement verbatim, and now everything ran through without error, and the generated fields are correctly populated.

    I appreciate your help very much!

Posting Permissions

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