Handling KeyMgtGetNextValue() while moving machines - Post2

scottopizza · Posted: Thu Oct 12, 2006 1:47 pm

Greetings.

I'm trying to move KeyMgtGetNextValue() values from one server to another. So, for a start I would like to at least list all of the values. I found a thread titled, "Handling KeyMgtGetNextValue() while moving machines" that has the answer I'm looking for. But I think I need a little extra help. Here is the solution that Craig Hulett posted:
<< If you look at the source for the routine in question, you can see that it uses a Hash file called "SDKSequences". In the absense of some back-door Universe methodology for transferring the contents from one Project to another, I would think it would be fairly straight-forward to write two DataStage jobs - one to dump its contents to a flat file and then another to populate it from that flat file after you transfer it to the new server.
I'd run KeyMgtGetNextValue once on your new machine so that it can create it with the specs it wants, either that or make you create it using the same command as in the source code:
Code:
EXECUTE "CREATE.FILE SDKSequences 2 1 1"
FYI - This is from a Version 6 server.>>

I tried to find this hashed file and found the name at "E:\Ascential\DataStage\Projects\PROD\SDKSequences", but it doesn't look like a normal hashed file to me. Am I missing something. A little extra hint would be appreciated.

Thanks,

Scott

dsdev750 · Posted: Thu Oct 12, 2006 2:02 pm

As Craig pointed out:

1. Create first job ( on the existing server )

Hash --> Xfm --> Seq
(SDKSequences)
Have two columns defined in the hash file e.g.:
ID VARCHAR(10),
VALUE INTEGER(10)

Run this job. Transfer the seq file to the 2nd server.

2. Create a 2nd job ( on the new server )

Seq --> Xfm --> Hash

Read the Seq file created in step 1 and write to the SDK Sequences file.

The easiest way to create an SDKSequences file is to test the routine KeyMgtGetNextValue which will create the file, if it does not exist already.

ray.wurlod · Posted: Thu Oct 12, 2006 2:12 pm

I'd use a longer VarChar for the ID column; possibly VarChar(254) to be on the safe side.

chulett · Posted: Thu Oct 12, 2006 3:11 pm

scottopizza wrote: EXECUTE "CREATE.FILE SDKSequences 2 1 1" FYI - This is from a Version 6 server.>> I tried to find this hashed file and found the name at "E:\Ascential\DataStage\Pr ...

What's this?

ray.wurlod · Posted: Thu Oct 12, 2006 3:14 pm

It can be dynamic (Type 30), but is created as Type 2 by the function. Type 18 would probably be a better choice once modulo exceeds 1.

kduke · Posted: Thu Oct 12, 2006 3:29 pm

Hashed files should not be considered persistent meaning these values should be recreated every job run.

Select max(surrogate_key) from dimension_table -> SDKSequences

This is a job included in EtlStats called reseed_sequences. The surrogate_key is a parameter and so is the table name so the SQL looks a little different because of the # marks. Download EltStats and load his job it will save you some time. Most tables the surrogate key is a primary index so this is very fast to run.

ray.wurlod · Posted: Thu Oct 12, 2006 4:51 pm

There have been a number of posts on resetting sequences, this one for example provides a function that can be used.

scottopizza · Posted: Fri Oct 13, 2006 12:49 pm

Wonderful! Thanks for filling in the details I was missing. Thanks also to Kim for the code sample. - Scott

kduke · Posted: Fri Oct 13, 2006 1:52 pm

You are welcome.

kduke · Posted: Fri Oct 13, 2006 1:53 pm

You are welcome.

ray.wurlod · Posted: Fri Oct 13, 2006 3:30 pm

kduke wrote:

Hashed files should not be considered persistent meaning these values should be recreated every job run.

...except, of course, for the Repository tables and SDKSequences.

sylvan_rydes · Participant Joined: 12 Apr 2006 Posts: 47 Points: 349

Hi All,

I have something common with this. How can I update my oracle sequence number within datastage for a table following that I have used keymgtngetextvalue function.

Thanks in advance.

Sylvan.

ray.wurlod · Posted: Mon Jan 22, 2007 8:20 pm

The same way you'd reset a sequence from any other client application. Perhaps use the "after SQL" in a job that does something else or, better, in a job that does nothing else. Then you can cal ...

What's this?

sylvan_rydes · Participant Joined: 12 Apr 2006 Posts: 47 Points: 349

Hi Ray,

Thanks a lot for the reply. It worked well.

Thanks again.

Sylvan