SAS Enterprise Case Management to Reset data in incident_rk_seq database.

[imdickson]

Hi SAS Community,

I am a SAS Enterprise Case management(ECM) Specialist and recently i want to reset the RK counter for case and incident. Understand that it is controlled in this table called incident_rk_seq but it is empty inside. May i know if anyone has the script to reset the rk number?

[Patrick]

Hi @imdickson

I wouldn't call myself an ECM specialist but I do have some experience in working with ECM as part of a SAS AML implementation.

The following applies for the ECM version used in this AML implementation with Oracle as database.

With Oracle the RK's are created and maintained by calling an Oracle sequencer. Below a screenshot from Oracle Developer.

These sequencers are Oracle objects.

https://docs.oracle.com/cd/B28359_01/server.111/b28310/views002.htm#ADMIN11792 

Oracle doesn't provide a method to just re-set a sequencer (for good reasons). What you can do is to modify the increment to a minus value which is the current value minus 1, then call the sequencer once with a "nextval" method, then modify the increment back to 1. https://community.oracle.com/thread/1999691 

BUT:

Why would you ever want to reset these "counters"? You're only asking for trouble.

The RK's as such are generated database keys and they shouldn't have any meaning (like being "abused" for returning the number of cases and the like).

I do know about a rare case where someone run out of keys for Parties in a TEST environment after multiple full loads of large data volumes because Party_RK is defined as NUMBER(10,0) (and the multiple loads summed up to more than 10power10 parties).

For that reason I've changed all RK's to NUMBER(15,0) in the implementation I've been working on. SAS can store 15 digits at full precision and we didn't encounter any issues with this change.

[imdickson]

Hi Patrick. The reason why i want to reset/modify the RK number is because I am doing server migration and the new server with ECM should have the RK counter properly adjust to avoid problem when clicking on "Create New" in the new ECM environment as there are more than 100,000 reports stored. I checked on the sequencer part and yes appareantly there is no straight forward method to reset RK but I will take note on that.

thanks alot Patrick

[Patrick · Accepted solution]

Well, if you know what you're doing then it's not that complicated. Once you know how much you need to substract from the current value (which you can query) then below command sequence will do the job.

1. ALTER SEQUENCE TESTSEQ INCREMENT BY -3;
2. SELECT TESTSEQ.NEXTVAL FROM dual.
3. ALTER SEQUENCE TESTSEQ INCREMENT BY 1;
4. SELECT TESTSEQ.NEXTVAL FROM dual.

http://stackoverflow.com/questions/51470/how-do-i-reset-a-sequence-in-oracle