Regarding Identity numbers generation by PE/AMPS

Database
Enthusiast

Regarding Identity numbers generation by PE/AMPS

Hi all,

I have some doubts on Identity numbers generated by PE/AMPs.

Say my Identity column is a smallint and say i define the start and end number as 1 to 32000
and my Batch generation number is 100000 in DBS Control record.

I would like to know the following

- Are the numbers generated in batch by PE an distributed to AMPS or numbers are generated in Batch at AMP level? (If at AMP level then my batch size is so high that all numbers would be allocated one AMP and will i be able to insert records into other AMPS?)

- In the above case though i inserted very few records , im getting number exhausted message for newer insertions.

Any suggestions on what could be reason and how we can resolve this would be really useful.

Regards,
Annal T
2 REPLIES
Junior Contributor

Re: Regarding Identity numbers generation by PE/AMPS

Hi Annal,
each AMP/PE requests a bunch of values (by default 100000) and then assignes those values one after the other, just like a sequence in Oracle.
But sequence values are assigned by muliple AMPs and PEs in parallel.
- for INSERT VALUES through a single session the sequence numbers are assigned by a single PE "sequentially" without gaps.
- for INSERT VALUES through several sessions the sequence numbers are assigned by multiple PEs, thus there are huge gaps
- for INSERT SELECT the originating AMPs (not the PE) assignes the sequence numbers, thus there are huge gaps

If you want "sequential" values, better calculate it within your SELECT:
INSERT newtab
SELECT row_number() OVER (ORDER BY col), ...
FROM tab

Dieter
Enthusiast

Re: Regarding Identity numbers generation by PE/AMPS

Thanks for the info!!!

I'm fine with the gaps.

I wanted to know if at all there would be any scenario where we would run out of numbers
even if we have not inserted the allowable count of records due to the way Identity works.

Regards,
Annal T