Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2008
    Posts
    9

    Unanswered: INSERT statement with alternating rows

    Hello there,

    (DB2/AIX64 9.1.4 on AIX Version 5.3)

    When trying to insert 2 rows of data into a table, the insert statement writes randomly from 0 to 6 rows instead of 2 rows.
    When I run the SELECT part only, there are always 2 rows displayed.

    Here's a glance at the stripped-down script:

    Code:
    INSERT INTO SCHEME1.TABLE1
    SELECT
      NEXTVAL FOR SCHEME9.SEQ_SK_TMP  ID,
    
    FROM
      SCHEME2.CAMPAGIN CAMP --federated table
    
    INNER JOIN
     SCHEME2.TABLE2	T2
    ON  CAMP.INST_ID = T2.INST_ID
    AND ...
    AND ...
    
    INNER JOIN 
      SCHEME3.TABLE3 T3
    ON T2.INST_ID = T3.INST_ID
    AND CAMP.INST_ID = T3.INST_ID;
    SCHEME3.TABLE3 is a simple lookup-table.
    It only contains 3 colums and one row. The INST_ID, a long INST_ID (which I need in the full statement) and a description.

    If I skip the last join with SCHEME3.TABLE3, the insert always writes 2 rows.

    However, the full INSERT statement randomly (?) inserts 0 to 6 rows when I run it repeatedly in the query tool.
    The SELECT without the INSERT but with the last JOIN always returns 2 rows.


    I tried to add some JOIN conditions and a WHERE clause, but this didn't help.

    My guess is that this could have something to do with where the tables are stored physically.

    Any suggestions are highly appreciated!

    Thanks
    Sebastian


    EDIT:

    Now I rearranged the whole thing and used the table of the last JOIN as the FROM table ...
    It's working now, but the mystery remains...
    Looks like another case for the X-Files.

    EDIT2:

    Now I had a similar situation but without any joins involved.
    However, a SEQUENCE was used, too.
    When I commented out this sequence and used constant numbers for testing, it worked.
    So maybe something's wrong with the sequence:
    Code:
    Create Sequence ANOTHER_SCHEME.SEQ_TEST as BIGINT 
    	Start With 1000000 
    	Increment by 1 
    	MinValue 1000000 
    	MaxValue 9223372036854775807  
    	Cache 20 ; -- tried no cache and ORDER, too
    In the manual of V8 it says
    "Sequences are not supported in a database with multiple partitions (SQLSTATE 42997). "

    We do have multiple partitions, but there were no errors / warnings when I created the sequence.

    The insert-script in question is used to write to tables in differrent schemes.
    The ID generated by the Sequence should be unique in all schemes.
    I thought a sequence would be suitable to do this. Are there any other options without a sequence?
    Rownumber() over wouldn't issue unique IDs throughout the different schemes, right?
    Last edited by sk-online; 09-03-08 at 08:21.

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    The first part of your query leads me to believe you are not joining the tables on the whole key. Randomly getting different numbers of rows back sounds like you have some type of cartesian product. Another good possibility is you do not have a unique index defined somewhere, so you have multiple rows in one table with the same values.

    Dave

Posting Permissions

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