Reading rows from and adding rows to a hashfile in

[victorbos]

Hi all,

With the risk of asking a stupid question:
I am designing the surrogate-key mechanism for a dimensional model.
I did put my keyvalues in a hashfile with the idea to make it as fast a possible.
I use a transformer stage to determine the new keyvalues to be inserted into the hasfile with this hashfile and a sequentialfile as input.
This works all fine.
I am now stuck at the point where I want to add the rows that hold new keyvalues to the existing hasfile. I do this with a link-collector, and my job aborts when I run it (with nog error message btw!).
I guess it has something to do with file locking.

How should I do this? Or am I on the wrong path to implement keymapping?

tia from a DS beginner,

Victor.

---
Victor Bos - Datastage developer, the Netherlands

[WoMaWil]

Hi Victor,

why do you want to use the link-collector? A Hash-File as target is normaly enough for this task. This is something standard, what you are doing. I don't know of any project, which has not such feature as you describe them.



Wolfgang Huerter
=====================
Cologne, Germany

[kduke]

Victor

1. Create a job to:
select max(key) from MyTable
then output to HashSurrogateKeys, "MyTableName"

2. Create a routine to return the current max.

MyTableName = Arg1
Ans = 1
open "HashSurrogateKeys" to HashSurrogateKeysPtr then
read KeyRec from HashSurrogateKeysPtr, MyTableName then
Ans = KeyRec
end
end
return(Ans)

3. Create a stage variable called SurrogateKey.

Initial value:
SurrogateKey = GetMaxKey("MyTableName")

then increment:
SurrogateKey = SurrogateKey + @OUTROWNUM

Thanks Kim.

Kim Duke
DwNav - ETL Navigator
www.Duke-Consulting.com

[ray.wurlod]

You can even improve on that.
INITIALIZE the stage variable with a call to GetMaxKey.
The stage variable expression in the Transformer stage simply assigns its current value to itself.
The expression that generates the value for the surrogate key column on the output link has the form StageVariableName + @OUTROWNUM.
In this way to need call GetMaxKey only once.

Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518

[kduke]

Ray

You restated exactly what I said. "INITIALIZE" is the same as "Initial value". You call the routine only at the start.

Thanks Kim.

[Nat]

Hi,
I'm new in this tool. I have tried to do what Kim suggested (i'm not so familiar with routine and basic), so I was wondering a pair of things:
1- About first step, how and which fields the hashfile has?
2- About second step, who is KeyRec?
Thank you for attention. I noticed there is not rich documentation on DS, so I'm trying to improve my knowlegde of it thanking these suggestions.
Bye, Nat.

[roy]

Hi Victor,
to simplify the advice you already got...

step 1 : get a job that outputs the "select max(surrogate-key) ....) to a hash file with 2 columns (1 is a dummy key constant 1 for lookup, the second is the max(value) you got.
{ after this step you'll have a hash file with exactly 1 row i.e. dummy-col = 1; max(value)-col = 12345 }

step 2: in the job you generate the new data & surrogate-keys use a simple lookup to the file you generated in step1, use the preload file to memory option in the hash stage ( and the hard-coded lookup to the dummy column which is 1).

step 3: use the metod you were already sugested here (@OUTROWNUM+ max(value)) from the lookup, to get the new surrogate-keys in a transformer stage before you write it to your destination file or table .
{ i.e. asume you got the example max(value) hash file I gave in step 1.
for the 1st line, passing the transformer, you'll have @OUTROWNUM =1 by default + the lookup value from the max(value) hash file = 12345 totaling to a new key value of 12346.}

Good Luck,



Roy R.

[Nat]

Hi all,
please ignore my last questions because I finally resolve the problems, and I was able to generate further key for new value.
This has been a good exercise for me, since I'm trying to go further the tutorial to learn something more about this tools.
Thank you again for suggestions and help.
Bye.