KeyMgtGetNextValue returning duplicate surrogate keys

[jreddy]

This is a strange one.
We are using KeyMgtGetNextValue routine to assign surrogate keys when inserting records to the warehouse tables. Load runs fine for days and months and out of the blue, the load aborts with 'cannot insert duplicate key' errors.

So, for example, if a table that is loaded everyday with a few rows, has the last ID used as 180 and today if the load starts pushing out id's of 130, 131 instead of values > 180, what are all the possible reasons and where should I be looking for some traceability on why these sequences got reset back to 130.

I can think of two possibilities:
1) The SDKSequences file has been compromised or corrupted or messed with
2) Someone or some process ran the UDATE SDKSequences on this one surrogate key to set it back to 130

Any other possibilities that you guys can think of? thanks in advance..

[ray.wurlod]

I'd be guessing option (2). If SDKSequences were corrupt it wouldn't be returning anything at all.

As for tracing, search for any job that has an input link on which the hashed file name is SDKSequences.

[chulett]

Another (remote) possibility might be a restore of files in the Project from backup. This hashed file might have been accidentally included.

[jreddy]

Thank you Ray and Craig

There is no job in the project that writes to the SDKSequence file directly and our admins have assured us that there was no restore to the project directory at all.

That leaves us with the only possible explanation to be - that someone had run the Update SKDSequences in the command prompt of Administrator (or any other *malicious* process that does the equivalent). If that is the case, how can we see the list of commands that were ever executed against that project from administrator? Command History shows me nothing ...

[chulett]

It's not stored as far as I know.

[ray.wurlod]

It's not stored unless it's explicitly saved.

[jreddy]

so, if out of the 30 odd sequences maintained by the SDKSequences, if only one starts having this problem occasionally, it has to be a malicious process/task updating it correct?How can we monitor and identify the root cause if something like this happens.
I wanted to make sure that there is no Datastage issue here when sequences seem to be off once in a while (and every time it is a different key)

[chulett]

So this isn't a one-time occurrence for you?

I'm not aware of any way to 'monitor' this, unfortunately. Perhaps post-cycle timestamped backups are in order?

[prasad.bodduluri]

clear SDK sequence using data stage administrator using CLEAR SDK.SEQUENCE

[ray.wurlod]

clear SDK sequence using data stage administrator using CLEAR SDK.SEQUENCE

1. This is never a good idea.
2. The command you gave is not valid anyway.

[ray.wurlod]

If you really do want to monitor changes to SDKSequences, you have the option of converting it to a UniVerse table and creating triggers that can audit these changes (including writing who and when information, and even the operation and before- and after-images, to a file).

[jreddy]

Thank you Ray, is it possible to get a code snippet on how to do what you suggested - thanks in advance

[ray.wurlod]

You'll need the UniVerse SQL Reference manual (downloadable from Rocket Software or from ADBK in Sweden).
There's a utility for converting a hashed file to a UniVerse table - called CONVERT.SQL - it ends up executing a CREATE EXISTING TABLE statement, which basically builds all the requisite system table entries. See also this post.

To create triggers the command is CREATE TRIGGER. And before you can use that you will need to create a DataStage/UniVerse BASIC trigger routine. All of the required information is in the UniVerse/SQL Reference Guide.