KeyMgmt Function

[amsh76]

Can anyone tell me how to reset surrogate key generated using Keymgmt function in Datastage.

Thanks in advance

[rasi]

You have to update the table which has this information.

Use the following command

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

Thanks
Siva

[amsh76]

Hi Siva,

Can you throw more light on this. Whats VOC? and where should i write this command ?

Thanks Again

[ray.wurlod]

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 been

Code: Select all

UPDATE SDKSequences USING DICT DICT.DICT SET F1 = '1' WHERE @ID = 'Your_Sequence_Name'; 

which 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.

Code: Select all

INSERT INTO DICT SDKSequences (FIELD, CODE, LOC, NAME, FORMAT, SM) 
VALUES ('F1', 'D', 1, 'Next Key', '8R', 'S');

Then you don't need a USING phrase at all.

Code: Select all

UPDATE SDKSequences SET F1 = 1 WHERE @ID = 'Your_Sequence_Name';

[ray.wurlod]

If you want to be able to see F1 by default, you need another entry in the file dictionary for SDKSequences.

Code: Select all

INSERT INTO DICT SDKSequences (FIELD, CODE, EXP) VALUES ('@', 'PH', 'F1');

This means that F1 will be displayed when

Code: Select all

SELECT * FROM SDKSequences;

is executed.

[KeithM]

where should i write this command ?

You can enter these statements through the command window in Administrator.

[sumitgulati]

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

[kduke]

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.

[sumitgulati]

Kim, I am not using SELECT * from the file. I am using SELECT * from DICT of the file. I guess this is same as LIST.DICT <HashFileName>.

What I wanted to ask is when I run this query I do see similar column names but they are not exactly the same what Ray gave in his insert query.

Regards,
Sumit

[sumitgulati]

Kim I guess I got it after looking at the other query you have posted. The DICT files have the following columns:

FIELD, CODE, EXP, NAME, FORMAT, SM, ASSOC

Thanks
-Sumit

[ray.wurlod]

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.

[amsh76]

Is there any way by which one can reset the hash file using routines?

[ray.wurlod]

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.

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)

[TonyInFrance]

You have to update the table which has this information.

Use the following command

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

Thanks
Siva

Where in my Job sequence can I put this?

[ray.wurlod]

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.

Code: Select all

$DSHOME/bin/dssh "UPDATE SDKSequences USING DICT VOC SET F1=1 WHERE F0 = 'MySequenceName';"

If you don't have $DSHOME set, hard code the full path of dssh.