clusters and preallocated/populated data

[Bastien]

Hi All,

I am doing some design work on a new system. The plan is to use clustered servers accessing the same backend instance. I want to prepoulate the table with our unique id (for say 5M rows for sake of discussion). Where I am puzzled is how do I guarantee unique ids to the application when both servers are looking for the same next available row? The sql could be something like

select min(id) from table where user_id is null

The trouble our current system runs into is that there are occasions where the users generate the same id which of course messes up the application.


Is this a case where a store procedure would do the insert and return the id to the user is a better solution?

Thanks


Bastien

I wish my computer would do what I want it to do,
instead of what I tell it to do...

 

[sathyarams]

With your current design, you are likely to hit on severe performance and locking issues .. In addition, your application handles the onerous task of unique id generation ...

Would it be possible to reconsider your design:

a) Define table (no pre-populating)
b) Generate unique id
c) Use the unique id to insert into the table(s)

OR

a) Define table with autogenerating column (no pre-populating)
b) Insert into table
c) Get the generated value and insert into other tables

For this you can use SEQUENCES or IDENTITY columns

For sequences, refer to

http://publib.boulder.ibm.com/infoc...topic=/com.ibm.db2.udb.doc/admin/r0004201.htm

In this case, db2 takes the responsibility of providing unique values for each request - irrespective of the client ..

Alternatively, you can use IDENTITY columns when creating a table (look for CREATE TABLE Statement) .. Here you don't even need to think about getting a unique value .. db2 autogenerates the value when you insert a new row (internally, this is also implemented using SEQUENCEs)

If you are using a sequece, you get the value from the database and use it like any other interger type variable in an application. It is also useful if you are inserting data into multiple tables in the application, and all inserts require the unique id ..

If you are using identify columns, your insert generates a value, which of course , you can retrieve and use it for subsequent processing ...

Exampleswith syntax errors ;-) )

A) Sequences :
CREATE TABLE USERS (id int,user_id char(100))
CREATE TABLE PROFILE(id int,name char(100))
CREATE SEQUENCE SEQ1 INT
MYID=VALUES(NEXTVAL for SEQ1)
INSERT INTO USERS VALUES(MYID,'Bastien100')
INSERT INTO PROFILE VALUES(MYID,'Bastien')

B) Identity columns

CREATE TABLE USERS (id int generated always as identity,user_id char(100))
CREATE TABLE PROFILE(id int,name char(100))
INSERT INTO USERS(user_id) VALUES('Bastien100')
MYID=VALUES(IDENTITY_VAL_LOCAL())
INSERT INTO PROFILE VALUES(MYID,'Bastien')



HTH

Sathyaram







For db2 resoruces visit

http://www.db2click.com

More DB2 questions answered at

http://www.dbforums.com/f8

&

http://www.idug.org/user/UserLogin.asp

 

[Bastien]

using identity was the original design, but some coders changed it here because they didn't get it. The result was that the application was creating duplicates since it was being managed with code (doing a select max(id) from). The hope with the is new structure/design is that the population would avoid the need to generate keys and just do an insert into the next available key

Bastien

I wish my computer would do what I want it to do,
instead of what I tell it to do...

 

[sathyarams]

because they didn't get it

do you mean they didn't understand ?







For db2 resoruces visit

http://www.db2click.com

More DB2 questions answered at

http://www.dbforums.com/f8

&

http://www.idug.org/user/UserLogin.asp

 

[Bastien]

sadly, yes

Bastien

I wish my computer would do what I want it to do,
instead of what I tell it to do...

 

[sathyarams]

I will agree the new design is remarkable and acceptable if these coders happen to be the owners of the company and their money is at stake if the application screws up .. ;-)

All I can do is wish you good luck

Have a great weekend

Sathyaram




For db2 resoruces visit

http://www.db2click.com

More DB2 questions answered at

http://www.dbforums.com/f8

&

http://www.idug.org/user/UserLogin.asp

 

[Bastien]

but the question remains, with the prepopulated table, how do i best access the table to guarantee that in a clustered server environment, that each access ges the next free id....instead of the situation where two users coming from different nodes in the cluster access the same id?

Bastien

I wish my computer would do what I want it to do,
instead of what I tell it to do...

 

[sathyarams]

Sorry, I did miss that, didn't I?:-(


Is SEQUENCE too out of question ?



If it is : then, the solution that does least damage:

Use a column to say allocated or not allocated instead of checking for (this comment holds good for any other solution)

create unique index prikey on users(id) include(allocated) [ index can be used for any other solution as well ]

alter table users add primary key (id)

In the appl:

select id from (update users set allocated='Y' where id=(select min(id) from users where allocated='N')) as x

This will give a id that can be used for further processing ...



Another option which uses tradiitonal approach ...

declare c1 cursor for select * from users where id=(select min(id) from users where allocated=) for update ;

open c1 ;

fetch c1 into v_id,....,v_allocated ;

update users set allocated='Y' where current of c1 ;

commit ;

close c1 ;

In the first approach, update and select are done in one statement and therefore it has minimal impact ... when this stmt is executing a lock is taken on the row and 'other stmts' will be serialized ...

In the second approach, when you open the cursor, a lock will be taken on the min row and will be released when you commit .. therefore here , 'other stmts' will be serialized .. Since there is more than one stmt to execute, it is less efficient than the first one ...

In this context, 'other stmts' mean, all stmts that need the locked record to generate a resultset ..

Let us say, the 'current id' is 1000 and the corresponding row is locked ..

Then,

select username from users where id=100

will execute without problems because id is part of unique index and therefore the database knows there is only one record and the locked record is not the one ..

select username from users where zipcode='19087'

will be serialized becuase the database cannot be sure that the locked row will not have zipcode 19087 ..


These are database solutions ..

Non-database solution may be to cache, say 5 userids at the client and use them ... so lockig multiple time can be avoided ..

select id from (update users set allocated='Y' where id in (select id from users where allocated='N' order by id asc fetch first 5 rows only)) as x ...

When the cache is used up , go back to the database to get 5 more

HTH

Sathyaram

For db2 resoruces visit

http://www.db2click.com

More DB2 questions answered at

http://www.dbforums.com/f8

&

http://www.idug.org/user/UserLogin.asp

 

[Bastien]

Thanks for that, I will see what the best fit is

Bastien

I wish my computer would do what I want it to do,
instead of what I tell it to do...