highest surrogate key

rsunny · Post by **rsunny** » Sat Oct 02, 2010 10:40 am

Hi everyone ,

my source is a database which will be loaded into oracle tables. Suppose for example, if I get 10 records today, I need to assign a Surrogate Key only to these records which are new records and i have to use the same surrogate key for the updated records and load it into the oracle table. So next day, when I get new set of records I need to track the highest Surrogate Value in the target table and then assign surrgate key values to these new records starting from the highest value. I have to assign a maximum value for the surrogate key and increment by 1 everytime i insert a new record but can anyone tell me how to implement as i couldn't able to get a maximum value of the surrogate as there is no function to get a maximum value in transformer. I tried taking MAXIMUM(SURROGATE KEY) in stage variable and assigned it by incrementing by 1 but it didnt work. Please can one tell me how to implement.

thanks in advance

ray.wurlod · Post by **ray.wurlod** » Sat Oct 02, 2010 3:53 pm

Tell is what "didn't work" actually means.

Usually this is implemented either through a job parameter or through a reference link to which the returned value has @INROWNUM added in a Transformer stage. However, this involves doing the query for every row processed, which is why performing the query once in an upstream job and supplying that value as a job parameter is to be preferred.

rsunny · Post by **rsunny** » Sat Oct 02, 2010 4:22 pm

hi ,

can you tell me how to implement it through a job parameter oor through a reference link.i am new to the datastage and i had taken a stage variable and used it i.e. stagevar=MAXIMUM(inputlink.surrogate id)+1.
It didn't work.i think my function is wrong.i dont have any idea of how to find it through job parameter or through reference link.Please can you tell me the code or how to implement it.

thanks in advance

chulett · Post by **chulett** » Sat Oct 02, 2010 6:16 pm

You need to capture the MAX() value outside the job and pass it in, then increment the job parameter for each record. That or use one of the 'KeyMgmt' routines that come with the tool.

rsunny · Post by **rsunny** » Sat Oct 02, 2010 6:50 pm

Hi craig ,

can u please explain me how to capture the MAX() value outside the job and pass it to increment the job parameter for each record.so you mean to say i have to get the max value through aggregator only and i have to use a different job to get a maximum value and then use that value in the job o parameter to increment for each record.But how can i use that value in my job parameter .please can you explain me in detail of how to insert that value in job parameter .and do i need to do a different job to get a maximum value and then insert that value into job parameter.please can you explain me in detail as i am new to datastage. And i tried using 'KeyMgmt' but there might be a chance of getting the same surrogate key value as already in the target.So thats why i havent used it.

thanks in advance

chulett · Post by **chulett** » Sat Oct 02, 2010 9:41 pm

You're wrong about the KeyMgmt routines. If you've used them exclusively to populate the target, there's no issue. If late to the game, just start it off high enough to avoid any existing keys.

rsunny · Post by **rsunny** » Sat Oct 02, 2010 11:12 pm

hi ,

i havent used a routine anytime but i used the keymgmt function.can you please explain how to use because i used that function as keymgmt(constant value) in my transformer and i used it .other than that i dont have any idea of how to use in any other way.can you please tell me in detail of how to use.

thanks in advance

chulett · Post by **chulett** » Sun Oct 03, 2010 5:57 am

You use KeyMgtGetNextValue('X') and for every discrete value of 'X' it will track an incremented surrogate value. We used the target table name for 'X', including owner if needed to make it unique.

rsunny · Post by **rsunny** » Sun Oct 03, 2010 9:29 am

chulett wrote:You use KeyMgtGetNextValue('X') and for every discrete value of 'X' it will track an incremented surrogate value. We used the target table name for 'X', including owner if needed to make it uni ...

hi ,

So you mean to say X value should be target.Surrogateid i.e. target.linkname. but itried in that way also but it didnt work for me.can you please tell me what my assumption is right or wrong.If not can you please explain me in detail.

thanks in advance

rsunny · Post by **rsunny** » Sun Oct 03, 2010 10:08 am

hi ,

when i use KeyMgtGetNextValue(linknamei.e. surrogateid) .i got this error
DataStage Job 38 Phantom 5556
Program "DSX.KEYMGTGETNEXTVALUE": Line 69, Improper data type.
Attempting to Cleanup after ABORT raised in stage CopyOfscd2with_SK..xfm1
DataStage Phantom Aborting with @ABORT.CODE = 3

can any one tell me why igot this error.i used the datatype as varcahr and i also used Real also but same error.

tahnks in advance

chulett · Post by **chulett** » Sun Oct 03, 2010 10:32 am

It needs to be a string, a hard-coded value. For example, if your target table is named TEST.FRED then use KeyMgtGetNextValue("TEST.FRED") as the call.

rsunny · Post by **rsunny** » Sun Oct 03, 2010 10:52 am

Hi craig,

thanks for your valuable suggestions .it is working fine but i am having a doubt what if we are loading millions of records every time .does the KeyMgtGetNextValue('target table name') provides a unique value every time we load.And when i am loading for the first time every thing is working fine .i mean to say updates and inserts are working fine.when i try to load the new data second time ,only i can able to load inserts only but not able to update for any record which i loaded for the second time.Can you please tell me what might be the problem of not getting the updates for the second time when but able to get the updates for the first time when i load the data.i also used trim function in order to trim spaces and tabs but still the same problem.can you please suggest me what might be the problem.

thanks in advance

chulett · Post by **chulett** » Sun Oct 03, 2010 11:40 am

Those routines provide a unique value every time they are called. They track the last value used and increment it by one, simple as that. You would need to detail your job design and how you are determining inserts v. updates before anyone could help with your other issue.

rsunny · Post by **rsunny** » Sun Oct 03, 2010 3:57 pm

Hi ,

my source and the target is a database. i am trying to implement scd type2 .so i have given a seperate links for inserts and updates.first time when i load the data inserts and updates are working fine.when i load the data for second time , tha records which are new are inserting but the records which has to be updated are showing 0 rows not updating.so i try to use trim function and try to load it but still facing the same problem.so can anyone tell me what might be the problem.and if i try to implement sccd type 1 its working fine both inserts and updates for any times of load whether it is 3rd or 4th load.but not working for scd type2.can anyone provide me va;luable suggestions for this problem.

thanks in advance

chulett · Post by **chulett** » Sun Oct 03, 2010 4:19 pm

More details needed. How, exactly, are you checking for existence? Business key hashed lookup for the surrogate to update?