Need help on surrogate key genarator stage

[Manu1]

Hi,

Could any one help in generating a surrogate key value which should take the Initial value as 1 and increment by 1.

i.e,

My data should like as this:

1
2
3
4.........so on.

I have bulit a simple job which has 2 stages .......A surrogatorkeygen and a target sequential file.

Following are the Values to the Parameters.

Source Type = Flat file
File Initial Value = 1
Number of Records = 20

But,the o/p file formed has the below data

"4001"
"4002"
"4003"
"4004"
"4005"
"4006"
"4007"
"4008"
"4009"
"4010"
"4011"
"4012"
"4013"
"4014"
"4015"
"4016"
"4017"
"4018"
"4019"
"4020"
"5001"
"5002"
"5003"
"5004"
"5005"
"5006"
"5007"
"5008"
"5009"
"5010"
"5011"
"5012"
"5013"
"5014"
"5015"
"5016"
"5017"
"5018"
"5019"
"5020"


The total no of records that are generated are 40 but expected count is only 20.

Also,plz let me know the Datatype that I have to give for the Target filed.


My config file has 2 nodes.

[Sainath.Srinivasan]

Maybe you chose entire partition

[priyadarshikunal]

no its not .

surrogate key generator is being used as source and number of records is mentioned as 20. Hence its generating 20 records per node.

define a node map constraint or reduce the number of rows to 10.

[priyadarshikunal]

to increament by one search for block size and set it to 1.

viewtopic.php?t=127081&highlight=surrog ... +generator

above link should answer few questions you have about surrogate key generator.

[Manu1]

I ran the Job twice.
The O/P of first run is

"2"
"4"
"6"
"8"
"10"
"12"
"14"
"16"
"18"
"20"
"1"
"3"
"5"
"7"
"9"
"11"
"13"
"15"
"17"
"19"


And Second run O/P is

"21"
"23"
"25"
"27"
"29"
"31"
"33"
"35"
"37"
"39"
"22"
"24"
"26"
"28"
"30"
"32"
"34"
"36"
"38"
"40"


Is this Bcz I am specifying the same flat file for Both runs.

[chulett]

What did you want in the second run, something other than it continuing where it left off?

[Manu1]

Yes Craig............As per my reuirement on second run also it should start from 1 again as we load the data daily by removing Previous data.

[Sainath.Srinivasan]

The idea of surrogate key generation is to have unique numbers.

If you want same numbers, maybe you can use a column generator.

[chulett]

Right, or just a stage variable based on row and partition number if it should always start over at one. The stage is meant to begin where you left off last time.

[anil.kr06]

Hi,

I am binded to use Surrogate Key Generator stage to generate Surrogate Key in DataStage 8. But my problem is that I want to generate the key value from initial in each run. Is it possible in Surrogate Key Generator stage? If so please help me on this.

Regards,
Anil Kumar

[anil.kr06]

Hi,

I am binded to use Surrogate Key Generator stage to generate Surrogate Key in DataStage 8. But my problem is that I want to generate the key value from initial in each run. Is it possible in Surrogate Key Generator stage? If so please help me on this.

Regards,
Anil Kumar

[ray.wurlod]

You can initialize the stage by creating a job in which the stage has an input link.

[anil.kr06]

I think it is possible in 7.5 but datastage 8 we are creating one state file where last key value get stored and in next run it startes generating key value from next...

[Sainath.Srinivasan]

Just empty the state file before next run.

Something like

Code: Select all

echo '' > stateFileName

But really no point in working this way.

[anil.kr06]

One thing came in my mind that lets have Execute Command Stage after that particular job where we delete old state file and create a new file with the same name. If there is any other best option, where should not have any manual work in production ... then please....


Anil