Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2004
    Posts
    23

    Thumbs down Unanswered: URGENT!!! The WITH ROW MOVEMENT option for a VIEW

    Hi all! I am sorry for mine poor English.

    I have DB2 UDB 8.2 for Win2000 (after FixPack 7).

    I want at once to make the explanation,
    I use an option WITH ROW MOVEMENT
    with understanding for what purposes it serves.

    I have made, following:

    Code:
    DROP TABLE NODE_1
    
    CREATE TABLE NODE_1 (
    DOC_ID INTEGER NOT NULL,
    NODE_ID INTEGER NOT NULL,
    CONSTRAINT NODE1_CHK 
    CHECK (NODE_ID = 1)
    ENFORCED 
    ENABLE QUERY OPTIMIZATION
    )
    
    DROP TABLE NODE_2
    
    CREATE TABLE NODE_2 (
    DOC_ID INTEGER NOT NULL,
    NODE_ID INTEGER NOT NULL,
    CONSTRAINT NODE2_CHK 
    CHECK (NODE_ID = 2)
    ENFORCED 
    ENABLE QUERY OPTIMIZATION
    )
    
    DROP VIEW ALL_NODES_V
    
    CREATE VIEW ALL_NODES_V AS
    SELECT * FROM NODE_1
    UNION ALL
    SELECT * FROM NODE_2
    WITH ROW MOVEMENT
    Further:
    Code:
    INSERT INTO ALL_NODES_V VALUES (1,1)
    
    COMMIT
    
    SELECT * FROM ALL_NODES_V
    
    DOC_ID      NODE_ID
    ----------- -----------
              1           1
      1 records are selected.
    
    UPDATE ALL_NODES_V SET NODE_ID = 1
    
    COMMIT
    
    SELECT * FROM ALL_NODES_V
    
    DOC_ID      NODE_ID
    ----------- -----------
              1           1
              1           1
    
      2 records are selected.
    It how to understand?
    Why DB2 adds a new record the ambassador UPDATE???
    Someone collided with such problem and whether there is any workaround?And generally it a "BUG" or "QUIRKS IN SQL" (on Graeme Birchall)?

    Beforehand I thank for any explanations and excuse
    for a possible categoriality!

    Best regards, Dmitriy.
    Last edited by kdima71; 12-20-04 at 05:31.

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Appears to be a bug ... Raise it as a PMR with IBM

    Cheers
    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Nov 2004
    Posts
    23
    Hi all! I am sorry for my poor English.

    The workaround of a problem, with which I have met, was successfully by me found!!!

    Best regards, Dmitriy
    Last edited by kdima71; 12-20-04 at 10:44.

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Could you share it with us?

    Thanks

  5. #5
    Join Date
    Nov 2004
    Posts
    23
    Hi all. I am sorry for my poor English.

    The workaround consists that it is necessary forcedly to set
    NEW TRANSITION VARIABLE = OLD TRANSITION VARIABLE,
    If the new value for partition key does not fall outside the limits
    allowable significances (CHECK CONSTRAINT) for partition.

    For example:
    Code:
    CREATE TRIGGER NODE_1_B_UR 
    NO CASCADE BEFORE UPDATE OF NODE_ID ON NODE_1 
    REFERENCING  OLD AS old  NEW AS new  
    FOR EACH ROW MODE DB2SQL 
    BEGIN ATOMIC
     if old.node_id = new.node_id then
      set new.node_id = old.node_id;
    end if;
    END@
    Best regards, Dmitriy.

  6. #6
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    This does sound a good work-around ... IMHO, this is a very expensive way of doing this ...
    Assuming you have a support contract with IBM, make it a point to report it and get it fixed, so that your system does not underperform

    Cheers
    sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  7. #7
    Join Date
    Nov 2004
    Posts
    23
    Hi sathyaram! I am sorry for my poor English.

    I with you completely agree. It most likely not "work-around", and "patch"

    Infortunately, while my company does not have official support from IBM!

    But I do not despond and I shall wait for...

    Best regards, Dmitriy.

  8. #8
    Join Date
    Nov 2004
    Posts
    23
    Sorry, it just net. The repetitions are necessary for deleting.
    Last edited by kdima71; 12-21-04 at 06:27.

  9. #9
    Join Date
    Nov 2004
    Posts
    23
    Hi sathyaram! I am sorry for my poor English.

    I with you completely agree. It most likely not "work-around", and "patch"

    Infortunately, while my company does not have official support from IBM!

    But I do not despond and I shall wait for...

    Best regards, Dmitriy.

  10. #10
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Dimitriy .. Fixpak 8 for version 8 has a number of fixes for Union All views ... Your shop should upgrade

    Based on the fix descriptions, I guess it is very critical for IBM DB2 V8 shops who are FP4+ and using UNION ALL Views to apply Fixpak 8 ... (hoping Fixpak 8 doesn't introduce new bugs )

    Cheers
    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

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