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?
dBforums
>
INSERT statement with alternating rows
09-03-08, 04:17
Linear Mode
