DSXchange

Want to use the KeyMgtGetNextValue(%SequenceName%) DS Transform

Have the following questions.

How can I determine what the next value assigned will be?

How can I reset values?

Where is the sequence stored?

At what level must SequenceName be unique: job, project, instance?

Thank You

J.

jlegare wrote: How can I determine what the next value assigned will be?

It will start from 1 and go on.

jlegare wrote: How can I reset values?

use the following in your before-job subroutine

UPDATE SDKSequences USING DICT VOC SET F1 = '1' WHERE @ID = 'yournumber';

jlegare wrote: Where is the sequence stored?

Stored in DS Universe Hash file

jlegare wrote:
At what level must SequenceName be unique: job, project, instance?

I belive they shld be unique at project level.

IHTH

Thanks for the info.

If the sequence has already been used how would I tell what the next number would be - or in otherwords the last issued number.


for reset the recommended syntax was:

UPDATE SDKSequences USING DICT VOC SET F1 = '1' WHERE @ID = 'yournumber';


Woudl this update all sequences?

Where do I specify the name of a specific sequence.?

What is yournumber?

Thanks

?J.

SDKSequences should have @ID set to the table name. So no it will not reset them all. Remove the WHERE clause and it will.

Hi,

I have a keymanagment routine and i am using the same routine for different jobs with different sequence names.

for ex:

keymgmt("emp_key").
keymgmt("dept_key")

if i wanted to reset the dept_key to a particular value, without resetting emp_key what should i do?

even i wanted to find out how it stores those sequences internally, if i am using same sequence for different jobs.

truely speaking i didn't really understand what has discussed on this.


nsm.

SDKSequences is a table.
It's key value is the argument for KeyMgtGetNextValue.
It has one other column; the next value to be used in the sequence.

Therefore, to reset just one "sequence", use a WHERE clause in the UPDATE statement. You can execute this from anywhere you can execute "UniVerse" commands, including:

• Administrator client Command window
  telnet connection into project
  before/after subroutine using ExecTCL
  calling DSExecute subroutine with "UV" as the Shell argument
  using EXECUTE or PERFORM statement in DataStage BASIC code

Don't forget to verify that the operation succeeded.

Hi,
I have routine ErrID(0) with 0 as argument.
The command that is mentioned is not resetting the value.
The value is getting incremented.

I gave the command mentioned in Before/after stage routine
using ExecTCL and inputvalue

UPDATE SDKSequences USING DICT VOC SET F1 = '1' WHERE @ID = '0';

Please let me know what went wrong in this.

regards

The surrogate key hash file contains a localized set of values to use for assignments. You need to keep these values in sync with the actual target table. Imagine if you manually loaded more rows into the target table, now your hash file has no awareness of the current maximum value assigned in the table.

You should write a simple job that selects the maximum surrogate key value on the target table. You then spool the output to the hash file used by your assignment routine. Anytime you want this value reset to the currently assigned max, just run the job. In fact, it probably would be a really good practice to put that job into your jobstream as the first job that runs. If you have multiple target tables getting surrogates, just write one job with a bunch of select max queries sending their output to the same hash file. Another trick is a single OCI/ODBC stage with a bunch of select max queries unioned to stream all of your values to the hash file in one operation.

We will need to see the code of ErrID routine before being able to diagnose.
Did you capture the output of the SQL statement so that you could check for errors? If so, what message was output?