Unanswered: INSERT statement with alternating rows
(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:
INSERT INTO SCHEME1.TABLE1
NEXTVAL FOR SCHEME9.SEQ_SK_TMP ID,
SCHEME2.CAMPAGIN CAMP --federated table
ON CAMP.INST_ID = T2.INST_ID
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!
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.
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:
Create Sequence ANOTHER_SCHEME.SEQ_TEST as BIGINT
Start With 1000000
Increment by 1
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?
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.