key management
Moderators: chulett, rschirm, roy
key management
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.
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.
Re: key management
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
I belive they shld be unique at project level.jlegare wrote:
At what level must SequenceName be unique: job, project, instance?
IHTH
clarification
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.
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.
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.
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.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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:
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.
Code: Select all
UPDATE SDKSequences USING DICT VOC SET F1 = '1' WHERE @ID = 'dept_key';
- 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
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Not resetting
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
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.
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.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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?
Did you capture the output of the SQL statement so that you could check for errors? If so, what message was output?
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.