KeyMgt Function

rsunny · Post by **rsunny** » Tue May 24, 2011 1:49 pm

Hi All,

I am using one of the Datastage functions which is KeyMgtGetNextValue("abc") where abc is the table name in two jobs . when i run the 1st job its working fine , and then if i run the 2nd job , i am getting an error saying that 'unique constraint violated'. So can we use KeyMgtGetNextValue("abc") in two different jobs or should i need to change the 'abc' to 'def' in 2nd load. Can you please let me know what is the best way to generate a surrogate key value where i am loading Data from different Data in different jobs , so thats why i used KeyMgtGetNextValue("abc") in different jobs but still getting an error when i run the 2nd job after i am done with my 1st job.

Any help is really appreciated

Thanks in advance

ray.wurlod · Post by **ray.wurlod** » Tue May 24, 2011 4:53 pm

The argument supplied to this routine is the name of the sequence (a name internal to DataStage), not necessarily the name of a table. Looks like the second table already has the "next" key. You need to check for this, or initialize the sequence, or use independent sequences, or some combination thereof.

chulett · Post by **chulett** » Tue May 24, 2011 6:41 pm

Is this "abc" sequence name used for the same target table in your two jobs or two different targets?

chulett · Post by **chulett** » Wed May 25, 2011 8:57 am

Yes, "abc" is your sequence name in the Key Mgt routines as both Ray and I have pointed out to you. Now, it may match the name of the target table you are using the generated key for but that's hardly necessary. And if you have multiple jobs targeting the same table then they should all use a common sequence.

One consideration is concurrency, could the two jobs be loading the same table at the same time? That would change the routine you would need to use.

rsunny · Post by **rsunny** » Wed May 25, 2011 10:24 am

Hi,

Yes,The two jobs are loading the same table at same time. So can you tell me how should i change the routine . should i change from "abc" to some other value?

Thanks in advance

DSguru2B · Post by **DSguru2B** » Wed May 25, 2011 10:50 am

The routine will change. Look into KeyMgtGetNextValueConcurrent()

chulett · Post by **chulett** » Wed May 25, 2011 10:58 am

Was hoping that was obvious.

rsunny · Post by **rsunny** » Wed May 25, 2011 12:22 pm

Thanks every one .As of now its working fine with this KeyMgtGetNextValueConcurrent()