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.
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