KeyMgmt Function
Moderators: chulett, rschirm, roy
KeyMgmt Function
Can anyone tell me how to reset surrogate key generated using Keymgmt function in Datastage.
Thanks in advance
Thanks in advance
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
VOC is the DataStage vocabulary table (which is a hashed file). Because there is no definition for a column called F1 in the file dictionary of SDKSequences, a neat trick whereby a query against one hashed file can use the file dictionary of another hashed file (via the USING phrase) is employed in the INSERT statement.
The SQL statement could as easily have beenwhich is, in a sense, cleaner. DICT.DICT is the "dictionary of dictionaries", from which metadata for the LIST.DICT command are obtained.
Alternatively, a file dictionary entry called F1 could be created for the hashed file SDKSequences.Then you don't need a USING phrase at all.
The SQL statement could as easily have been
Code: Select all
UPDATE SDKSequences USING DICT DICT.DICT SET F1 = '1' WHERE @ID = 'Your_Sequence_Name';
Alternatively, a file dictionary entry called F1 could be created for the hashed file SDKSequences.
Code: Select all
INSERT INTO DICT SDKSequences (FIELD, CODE, LOC, NAME, FORMAT, SM)
VALUES ('F1', 'D', 1, 'Next Key', '8R', 'S');
Code: Select all
UPDATE SDKSequences SET F1 = 1 WHERE @ID = 'Your_Sequence_Name';
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.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
If you want to be able to see F1 by default, you need another entry in the file dictionary for SDKSequences.
This means that F1 will be displayed when is executed.
Code: Select all
INSERT INTO DICT SDKSequences (FIELD, CODE, EXP) VALUES ('@', 'PH', 'F1');
Code: Select all
SELECT * FROM SDKSequences;
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.
-
- Participant
- Posts: 197
- Joined: Mon Feb 17, 2003 11:20 pm
- Location: India
Ray a small clarification.
How do you know that file DICT SDKSequences has fileds FIELD, CODE, LOC, NAME, FORMAT, SM?
When I run the below query
SELECT * FROM DICT SDKSequences;
or
LIST.DICT SDKSequences
I see different names of columns like Filed Name, Filed Number, Field Definition, Conversion Code, etc.
Do all the DICT file have the same set of fields?
Regards,
Sumit
How do you know that file DICT SDKSequences has fileds FIELD, CODE, LOC, NAME, FORMAT, SM?
When I run the below query
SELECT * FROM DICT SDKSequences;
or
LIST.DICT SDKSequences
I see different names of columns like Filed Name, Filed Number, Field Definition, Conversion Code, etc.
Do all the DICT file have the same set of fields?
Regards,
Sumit
Ray has explained this before but when you SELECT * then it uses the @ phrase or the @SELECT phrase to get a default list of fields.
LIST.DICT HashFileName
Will describe all the fields available to use in a SQL SELECT statement or a Universe LIST or SORT statement.
If you do a search on @SELECT then you can find the Full Wurlod.
LIST.DICT HashFileName
Will describe all the fields available to use in a SQL SELECT statement or a Universe LIST or SORT statement.
If you do a search on @SELECT then you can find the Full Wurlod.
Mamu Kim
-
- Participant
- Posts: 197
- Joined: Mon Feb 17, 2003 11:20 pm
- Location: India
-
- Participant
- Posts: 197
- Joined: Mon Feb 17, 2003 11:20 pm
- Location: India
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
As well as specifying the location of data in the hashed file, the file dictionary specifies display format. One aspect of display format is a default column heading. This does not have to be the same as the column name.
That's what's happening here.
Field names in file dictionaries are specified in "meta meta data", in a file called DICT.DICT which is the "dictionary of dictionaries". That's how I knew which column names to use. There are lots of synonyms defined, because there are different record types in file dictionaries.
That's what's happening here.
Field names in file dictionaries are specified in "meta meta data", in a file called DICT.DICT which is the "dictionary of dictionaries". That's how I knew which column names to use. There are lots of synonyms defined, because there are different record types in file dictionaries.
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.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Yes. Many. All have the effect of opening the SDKSequences file, locking the appropriate record for update, reading that record (optional), writing the reset value, releasing the lock, and closing the file. Optionally you can build all of that into a transaction.
Is this not exactly what the SDK routine does? Well, no, but it does most of it. Here's how I'd write it, assuming that the hashed file SDKSequences exists.
Is this not exactly what the SDK routine does? Well, no, but it does most of it. Here's how I'd write it, assuming that the hashed file SDKSequences exists.
Code: Select all
FUNCTION ResetSDKSequences(SequenceName, ResetValue)
Ans = @NULL
* Simply return NULL if bad argument(s)
If Unassigned(SequenceName) Or IsNull(SequenceName)
Then
RETURN
End
If Unassigned(ResetValue) Or IsNull(ResetValue)
Then
RETURN
End
Open "SDKSequences" To SDKSequences.fvar
On Error
Msg = 'Error opening "SDKSequences" file. Error code = ' : Status()
Call DSLogWarn(Msg, "Reset SDK Sequences")
End
Then
Begin Transaction
* Lock for update, waiting if necessary for lock to be released.
RecordLockU SDKSequences.fvar, SequenceName
* Destructively overwrite the contents of field number 1.
WriteV ResetValue To SDKSequences.fvar, SequenceName, 1
Then
Commit ; * commits update and releases lock
Else
Msg = 'Unable to commit transaction.'
Call DSLogWarn(Msg, "Reset SDK Sequences")
End
Msg = 'Reset sequence "' : SequenceName : '" to ' : ResetValue : '.'
Call DSLogInfo(Msg, "Reset SDK Sequences")
Ans = ResetValue
End
Else
Rollback ; * discards update and releases lock
Msg = 'Unable to update "SDKSequences" file.'
Call DSLogWarn(Msg, "Reset SDK Sequences")
End
End Transaction
End
Else
Msg = 'Unable to open "SDKSequences" file. Error code = ' : Status()
Call DSLogWarn(Msg, "Reset SDK Sequences")
End
RETURN(Ans)
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.
-
- Premium Member
- Posts: 288
- Joined: Tue May 27, 2008 3:42 am
- Location: Luxembourg
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
You can't use the DataStage SQL directly. You could use an Execute Command activity to invoke the DataStage shell (dssh) to execute the command. If you don't have $DSHOME set, hard code the full path of dssh.
Code: Select all
$DSHOME/bin/dssh "UPDATE SDKSequences USING DICT VOC SET F1=1 WHERE F0 = 'MySequenceName';"
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.