Issue with Surrogate Key Generator

ankursaxena.2003 · Post by **ankursaxena.2003** » Mon May 14, 2012 2:09 pm

Hi,

I am facing issue with Surrogate Key Generator Stage.
Let me first describe my goal before showing the problem.
Goal: Get the Max value from Database and write it to Surrogate Key file.

Scenario:
When I run it for the first time I should get value 1 in the Surrogate Key file as the table is empty in DB. And I am getting 1 in Surrogate Key file. Lets say that today I load 35 records in table. So, my Surrogate Key will start tomorrow from 36.
Tomorrow I have 40 records to be loaded into table. So, the Surrogate Key will point to 76.
On the Third day I have to load 90 records but for some reason after loading 50 records, the job aborts. So, I need to delete all the 50 records from the table and I need to change my Surrogate Key to 76 because if I don't do a roll back then I will miss Surrogate Keys from 76 to 125. So, I need to delete the 50 records in the table and get the max value from table.

Problem:
So, when I run the job to get the max Surrogate Key in table for the 1st time I read it as below:

$ od X.sk
0000000 000000 000000 000000 000001 000000 000000 000000 000001
0000020

But, when I try to see the Surrogate File after couple of runs. I see 2 records as shown below:

$ od X.sk
0000000 000000 000000 000000 000001 000000 000000 000000 000603
0000020 000000 000000 000000 000605 000000 000000 000000 000605
0000040

And I read other forums, so I am running the job in Sequential Mode.

ray.wurlod · Post by **ray.wurlod** » Mon May 14, 2012 4:39 pm

Don't worry about what's in the state file. Just use the Surrogate Key generator stage. It knows what's in the state file and how to use and maintain it.

ankursaxena.2003 · Post by **ankursaxena.2003** » Tue May 15, 2012 7:55 am

Ray - Thanks for the reply. State File is not the Primary Problem.
The primary problem is that when I run the job 3rd time and it aborted after 50 inserts. So, I went and deleted those 50 records from table. And while trying to reset the Surrogate Key to 76, it doesn't start from 76 instead it starts from 126(But, I checked that it reads 76 as MAX value from on table). So, I am loosing 50 counts from 76 to 125.

And lets say that again while running the job it aborts after 40 inserts into table. And I again try to update the Surrogate Key. It won't reset it to 76, instead the next time it starts from 166.

sreewin7 · Post by **sreewin7** » Tue May 15, 2012 10:26 am

I worked same sort of senario in server ,but i never used in parellel as per my knowledge on parellel, Try to check the table option, without commiting the table the records will not insert into table and i hope the below may be helpful to you.

viewtopic.php?p=246588&sid=10b93f18d9e9 ... 838a372fe7

Thanks
Sree

[quote="ankursaxena.2003"]Ray - Thanks for the reply. State File is not the Primary Problem.
The primary problem is that when I run the job 3rd time and it aborted after 50 inserts. So, I went and deleted those 50 records from table. And while trying to reset the Surrogate Key to 76, it doesn't start from 76 instead it starts from 126(But, I checked that it reads 76 as MAX value from on table). So, I am loosing 50 counts from 76 to 125.

And lets say that again while running the job it aborts after 40 inserts into table. And I again try to update the Surrogate Key. It won't reset it to 76, instead the next time it starts from 166.[/quote]

rameshrr3 · Post by **rameshrr3** » Tue May 15, 2012 10:56 am

Are you setting the FileInitialValue property of the surrogate key file using max key value from database ( can be passed as a job parameter from the formatted output of an Command activity stage in Sequencer ) ?

Whats is the block size of the surrogate key sequence ? User defined or system selected ? If you set it to 1 using user defined , you will get the next value as 76 or 77 , and i see no reason to run the job or stage in sequential mode exclusively for the surrogate key stage to generate sequential values ( unless its a problem with the final target stage )

ankursaxena.2003 · Post by **ankursaxena.2003** » Tue May 15, 2012 2:10 pm

@Sreewin7 - Thanks for your reply. But, my concern is not about loading into target table. My concern is that I should be able to reset the value in the Surrogate Key file. At the 5th attempt the Surrogate Key reads from 166 instead of starting from 76.

ankursaxena.2003 · Post by **ankursaxena.2003** » Tue May 15, 2012 2:16 pm

@Rameshrr3 - Let me show you my job structure.
Step1:
The below structure is in a separate job for resetting the Surrogate Key.
Reading from Oracle Table ------------------> Surrogate Key Generator

Options in Surrogate Key Generator
Input Column Name - XYZ
Key Source update Action - Create and Update
Source Name - File_Path/File_Name
Source Type - Flat File

Step2:
And when I am using the Surrogate Key File in the actual job. It has the below structure.
------------> Surrogate Key Generator ------------> Writing to Oracle Table

Options in Surrogate Key Generator
Generated Output Column Name - XYZ
Source Name - File_Path/File_Name
Source Type - Flat File
File Block Size - User Specified
User Specified Block Size=1
Generate Key from Last Highest=Yes

when ever the job aborts while loading into Table, I delete all the records loaded in Table and run Step-1 to reset the Surrogate Key to previous day. And Step-2 is the actual logic which is used for assigning Surrogate Key to each record.

rameshrr3 · Post by **rameshrr3** » Tue May 15, 2012 2:27 pm

You don't need 2 jobs ( even though some recommend it ) . If you can reset the file Initial value in your dimension job ( using the max value from database key column ) , it will work as good. ( Im suggesting a better approach with fewer jobs ) . I think you may be having an issue updating your surrogate key state file in Step#1 . Does Job 1 run in sequential mode when it creates/updates the surrogate key state file ?

Also Check if the setting

Code: Select all

 Generate Key from Last Highest=Yes

is really necessary. It may be the source of your problem to start with

ankursaxena.2003 · Post by **ankursaxena.2003** » Tue May 15, 2012 3:01 pm

I have choosen 2 Jobs but I am not going to run Step-1 daily. I will run Step-1 only if there is some issue while loading into table.

And yes the Job-1 runs in sequential mode. I am not creating the Surrogate Key file. I am just updating it whenever I want to reset the Surrogate Key. I checked my Job-1 and it has 'Generate Key from Last Highest' set to 'Yes'. And I also checked that when I pull the data from Table it is coming correct. But, for some reason it is not able to update Surrogate Key file.

rameshrr3 · Post by **rameshrr3** » Tue May 15, 2012 3:13 pm

Using DOS Commands can you delete and recreate the state file everytime if you need to use job #1 ? and then try to update its value from database ?

You can use ExecDOS to delete the old state file. I guess the surrogate Key generator stage itself can create the file . I hope you are using the same state file in both jobs.

ankursaxena.2003 · Post by **ankursaxena.2003** » Tue May 15, 2012 3:26 pm

This is the back-up plan which I have. According to that whenever I want to reset the Surrogate Key file, I delete it and re-create it. And at that time it works fine.
But, I am sure there should be some way of updating the Surrogate Key file without deleting.

And I checked that I am using the same state file in Job-1 and Job-2.