DSXchange

I have a project which is doing in the multiple instance. When i get deep into it, i found out that i'm having on table serial issue. According my table serial in the project is generate primary key to the records before insert.

All the records are referring the same table serial. IF it is running on the multiple instance, it might have one key with 2 or 3 records are using at the same time... this is what i dun want this happen. Is there any idea on how to make them have no crash on this keys.?



thank you

If you read in the Parallel Job Developer's Guide about surrogate key management you will get some ideas. You need, perhaps, to allocate separate ranges of serial numbers that can be used by each instance, or work through a single-threaded process (such as the key management routines) to be able to guarantee uniqueness across all instances. In the latter case you are imposing a potential bottleneck on your design.

ray.wurlod wrote:If you read in the Parallel Job Developer's Guide about surrogate key management you will get some ideas. You need, perhaps, to allocate separate ranges of serial numbers that can be used by each ins ...

I'm sorry i cannot able to see whole of ur advice...but im using server edition. can i use the parallel job developer guide as well?

Thank you

What database? Are you saying you are using a SERIAL field to auto-generate surrogate keys and sometimes you get duplicates?

In any case, be more clear about what you are doing and how you are generating these 'serial' values. I've got no idea if you have a DataStage job design issue or a database problem at the moment.

In such a case use the database's ability to generate keys. Like in SQL Server, you make the key column as an identity column. This way the database generates a key everytime a record is inserted. One of the best ways to go about if multiple applications insert in the same table. If your concern is different, then please provide more details.

Thanx for everyone replies...

in table serial, it has 2 fields which are table name :ABC and curr_num =12345 . The curr num is the lates number whereby it use to put it in the the record before insert into target database.

For example there are 2 records going in the target db,
1st record - primary key : 12346 (logic: 12345+1)
2nd record -primary key : 12347 (logic: 12346+1)
..

After that, the latest num 12347 will be update the table serial and now table serial entity is curr_num = 12347

My issue happend is i have a job where extract the data


ascii file -> extract the data by parameter -> the data will store into
the sequential file

ascii file - all the record are in here
parameters - extract particular portion of data.
for eg:the parameter contains ctry_cd =us,
service_area_Cd = kul
sequential file - data which is similar with ctry cd and service area will be
store in the file.


i need to add a table serial somewhere here....but the issue is this job is a multiple instance... if 3 diff parameters sending into this job at the same time. that 3 parameters might be used the same curr_num = 12347 then problem will be arised.


Thank you

Ok... what mechanism are you using for getting the next value from the "table serial" and how is it being incremented? Any decent mechanism would support concurrency and it wouldn't be an issue for multiple processes to access it simultaneously without pulling the same key.

For example, that's why there's two versions in the sdk for the "Key Management" routines - one for serial access and one that supports concurrent access.

chulett wrote:Ok... what mechanism are you using for getting the next value from the "table serial" and how is it being incremented? Any decent mechanism would support concurrency and it wouldn't be an issue for multiple processes to access it simultaneously without pulling the same key.

For example, that's why there's two versions in the sdk for the "Key Management" routines - one for serial access and one that supports concurrent access.

Look up table from previouse number ---
Inside transformer,
Lnk_PK.ts_instance_id:(Lnk_PK.ts_curr_num+ PkInc)

In the stage variable :
PkInc+1

Let say the table serial curr_num = 150
after generate the key for those records.....then the table serial updated into 160
previouse value is the number of the table serial before updated.

table serial previous value
150 140
160 150
180 160