Generate Unique Number in DGTT

[situ]

Hi,

I'm using DB2 V9 for windows

I'm inserting records into DGTT from select statement; the problem is
how to insert unique value for every row inserted.
I tried using “generated always as” clause but its exhausting at some
point of time.
Say if 100000 records were needed to be processed, it’s only
processing up to 60000 rows.
Would you please tell me what I’m missing, and is there any workaround
to generate unique value for DGTT?


here is the syntax how i have created.

DECLARE GLOBAL TEMPORARY TABLE SESSION.TABLE_3 (
id INTEGER NOT NULL GENERATED ALWAYS AS
IDENTITY (START WITH 1, INCREMENT BY 1),
cd1 CHAR(5) NOT NULL,
id2 DECIMAL(16, 0) NOT NULL

)
IN USER8KSPACE
WITH REPLACE
PARTITIONING KEY (cd1,id2)
NOT LOGGED
ON COMMIT PRESERVE ROWS
;

[jefftyzzer]

Re: Generate Unique Number in DGTT

On May 29, 5:24 am, situ <SRIDHAR...@red iffmail.comwrot e:

Hi,
>
I'm using DB2 V9 for windows
>
I'm inserting records into DGTT from select statement; the problem is
how to insert unique value for every row inserted.
I tried using “generated always as” clause but its exhausting at some
point of time.
Say if 100000 records were needed to be processed, it’s only
processing up to 60000 rows.
Would you please tell me what I’m missing, and is there any workaround
to generate unique value for DGTT?
>
here is the syntax how i have created.
>
DECLARE GLOBAL TEMPORARY TABLE SESSION.TABLE_3 (
id INTEGER NOT NULL GENERATED ALWAYS AS
IDENTITY (START WITH 1, INCREMENT BY 1),
cd1 CHAR(5) NOT NULL,
id2 DECIMAL(16, 0) NOT NULL
>
)
IN USER8KSPACE
WITH REPLACE
PARTITIONING KEY (cd1,id2)
NOT LOGGED
ON COMMIT PRESERVE ROWS
;

I'm not sure what you mean by "exhausting ." The INTEGER data type
should get you to 2,147,483,647--a far cry from 60,000. Instead of
using an identity column (although, really, that should work just
fine), you could try assigning the value to "id" via the
GENERATE_UNIQUE () function.

Can you give us more detail on the issue--perhaps posting the error
message (or are you saying that there is no error per se but rather
that the INSERT rate just slows to a trickle)?

--Jeff