Hi Keith,
So, it turns out you can just set the nextId column value in arschema to
the beginning of your missing sequence and you're good to go. While the
unique index violation that Rick mentions will happen, ARS seamlessly
handles the problem and will cycle until it finds an unused id. I'd
thought this was the case, but just tested it out to make sure...
I have a form "Test6" which had two entries in it using the first two ids
in the normal sequence "SCT-00000000001" and "SCT-00000000002" ("SCT-" is
of course the prefix on the request id). I then updated the nextId to set
it back to 1:
UPDATE arschema
SET nextId = 1
WHERE name = 'Test6'
I then submitted a new entry into the form, and here's the relevant portion
of the SQL log:
/* Mon Nov 03 2014 10:25:55.5090 */BEGIN TRANSACTION
/* Mon Nov 03 2014 10:25:55.5090 */OK
/* Mon Nov 03 2014 10:25:55.5090 */UPDATE arschema SET nextId = nextId + 1
WHERE schemaId = 531
/* Mon Nov 03 2014 10:25:55.5120 */OK
/* Mon Nov 03 2014 10:25:55.5120 */SELECT nextId FROM arschema WHERE
schemaId = 531
/* Mon Nov 03 2014 10:25:55.5140 */OK
/* Mon Nov 03 2014 10:25:55.5140 */INSERT INTO T531 (C2,C7,C8,C3,C5,C6,C1)
VALUES (N'Admin',0,N'aaa',1415039155,N'Admin',1415039155,N'SCT-00000000001')
/* Mon Nov 03 2014 10:25:55.6020 */* WARNING * The statement has been
terminated.
/* Mon Nov 03 2014 10:25:55.6020 */ROLLBACK TRANSACTION
/* Mon Nov 03 2014 10:25:55.6160 */OK
/* Mon Nov 03 2014 10:25:55.6160 */SELECT C1 FROM T531 WHERE C1 =
N'SCT-00000000001'
/* Mon Nov 03 2014 10:25:55.6170 */OK
/* Mon Nov 03 2014 10:25:55.6170 */BEGIN TRANSACTION
/* Mon Nov 03 2014 10:25:55.6180 */OK
/* Mon Nov 03 2014 10:25:55.6180 */UPDATE arschema SET nextId = nextId + 1
WHERE schemaId = 531
/* Mon Nov 03 2014 10:25:55.6180 */OK
/* Mon Nov 03 2014 10:25:55.6180 */SELECT nextId FROM arschema WHERE
schemaId = 531
/* Mon Nov 03 2014 10:25:55.6180 */OK
/* Mon Nov 03 2014 10:25:55.6180 */COMMIT TRANSACTION
/* Mon Nov 03 2014 10:25:55.6190 */OK
/* Mon Nov 03 2014 10:25:55.6190 */SELECT C1 FROM T531 WHERE C1 =
N'SCT-00000000001'
/* Mon Nov 03 2014 10:25:55.6200 */OK
/* Mon Nov 03 2014 10:25:55.6200 */BEGIN TRANSACTION
/* Mon Nov 03 2014 10:25:55.6200 */OK
/* Mon Nov 03 2014 10:25:55.6200 */UPDATE arschema SET nextId = nextId + 1
WHERE schemaId = 531
/* Mon Nov 03 2014 10:25:55.6200 */OK
/* Mon Nov 03 2014 10:25:55.6210 */SELECT nextId FROM arschema WHERE
schemaId = 531
/* Mon Nov 03 2014 10:25:55.6210 */OK
/* Mon Nov 03 2014 10:25:55.6210 */COMMIT TRANSACTION
/* Mon Nov 03 2014 10:25:55.6220 */OK
/* Mon Nov 03 2014 10:25:55.6220 */SELECT C1 FROM T531 WHERE C1 =
N'SCT-00000000002'
/* Mon Nov 03 2014 10:25:55.6220 */OK
/* Mon Nov 03 2014 10:25:55.6220 */BEGIN TRANSACTION
/* Mon Nov 03 2014 10:25:55.6220 */OK
/* Mon Nov 03 2014 10:25:55.6220 */UPDATE arschema SET nextId = nextId + 1
WHERE schemaId = 531
/* Mon Nov 03 2014 10:25:55.6230 */OK
/* Mon Nov 03 2014 10:25:55.6230 */SELECT nextId FROM arschema WHERE
schemaId = 531
/* Mon Nov 03 2014 10:25:55.6230 */OK
/* Mon Nov 03 2014 10:25:55.6230 */COMMIT TRANSACTION
/* Mon Nov 03 2014 10:25:55.6240 */OK
/* Mon Nov 03 2014 10:25:55.6240 */SELECT C1 FROM T531 WHERE C1 =
N'SCT-00000000003'
/* Mon Nov 03 2014 10:25:55.6240 */OK
/* Mon Nov 03 2014 10:25:55.6250 */BEGIN TRANSACTION
/* Mon Nov 03 2014 10:25:55.6250 */OK
/* Mon Nov 03 2014 10:25:55.6250 */INSERT INTO T531 (C2,C7,C8,C3,C5,C6,C1)
VALUES (N'Admin',0,N'aaa',1415039155,N'Admin',1415039155,N'SCT-00000000003')
/* Mon Nov 03 2014 10:25:55.6260 */OK
/* Mon Nov 03 2014 10:25:55.6480 */INSERT INTO H531 (entryId,T0,U0) VALUES
(N'SCT-00000000003',1415039155,N'Admin')
/* Mon Nov 03 2014 10:25:55.6500 */OK
/* Mon Nov 03 2014 10:25:55.6500 */COMMIT TRANSACTION
/* Mon Nov 03 2014 10:25:55.6510 */OK
You can see that it first attempts a "normal" operation: acquire the
nextId, bump it, then try to insert a record using it. But then you see
that this fails (no doubt due to that unique index violation, though that's
not evident in the log). Next it goes into an exception mode where it
cycles through each subsequent value of nextId, asking the system if
there's a record using it until it finds one that's unused, then inserts
the record without error.
Obviously, this exception mode is comparatively very expensive, and that
expense is proportional to the size of the block (of used ids) it'll need
to skip over. But it sounds like in your case this will happen only once
at some point in the future, so someone at some point will experience a
longer than usual delay when adding a record.
So, just issue this SQL:
UPDATE arschema
SET nextId = 6002
where name = '<your form name>'
NOTE THAT if you have the "Enable Next RequestID Block Size" option
enabled, you may need to bounce the server (but maybe not). I do NOT have
it enabled on my Test6 form and did not have to bounce the server for it to
work correctly (I actually tried this a few times, SQL logging it each
time, and it worked as shown above each time).
-charlie
On Mon, Nov 3, 2014 at 8:39 AM, Sinclair, Keith <[email protected]>
wrote:
> **
>
> Luckily, I don’t have a server group set up here in this environment. I
> have worked with them before in my previous life.
>
>
>
> So, to answer the question as to why in all that’s un/holy do I want to do
> this, it’s because the previous Admin used the request ID to generate a
> unique customer number. Problem is that the systems that integrate into
> Remedy that take advantage of the customer number can only take a number
> that is only 4 digits long. So, when someone who doesn’t understand Remedy
> uses these number and subsequently deletes, we lose that range of unique
> numbers.
>
>
>
> So what am I gaining? Really just buying time for the other systems teams
> to work out a new solution of handling customer’s and their equipment
> before we hit that wall.
>
>
>
> Like sticking my finger in the dam whilst waiting for the heavy moving
> equipment that’s already on its way to get here before the levee breaks.
>
>
>
>
>
> *From:* Action Request System discussion list(ARSList) [mailto:
> [email protected]] *On Behalf Of *William Rentfrow
> *Sent:* Monday, November 03, 2014 10:24 AM
>
> *To:* [email protected]
> *Subject:* Re: Request ID Numbering recovery
>
>
>
> **
>
> Agreed - you can do it, but it doesn't really make sense to do it.
>
>
>
> Many times people doing data analysis will assume that the order of the
> entries in the system indicates the order they were created in. This is
> mostly true on single server systems. You'll still get some gaps unless
> your NEXT-ID-BLOCK-SIZE is set to 1.
>
>
>
> It's completely different if you have a server group and multiple
> servers. The Next ID block size is almost always set to a higher number
> like 50 and it's very possible for server "A" to grab 50 ID's (e.g. 1-50)
> and then server "B" grabs 51-100. And then for whatever reason the users
> on server "B" work fast and create use up their 50 well before the users on
> server "A". So even though their requests are all newer they have a higher
> ID.
>
>
>
> You probably know all of that, but I said all of that so I can say this: I
> always tell users/managers/etc that the Request ID signifies only one thing
> - a unique number. You cannot draw any other conclusions from it, and
> missing requests are the norm and should be ignored.
>
>
>
> William Rentfrow
>
> [email protected]
>
> Office: 715-204-3061 or 701-232-5697x25
>
> Cell: 715-498-5056
>
>
>
> *From:* Action Request System discussion list(ARSList) [
> mailto:[email protected] <[email protected]>] *On Behalf Of *Rick Cook
> *Sent:* Monday, November 03, 2014 10:13 AM
> *To:* [email protected]
> *Subject:* Re: Request ID Numbering recovery
>
>
>
> **
>
> Technically, yes, you can. You can go into the DB and reset the NextID
> variable for that form. However, if it's a form that's being used for
> entry (vs a config form), there's a good chance that numbers already exist
> that exceed 7002. If so, you'll get a Unique Index error when the next
> incremented Entry ID see that a record already has that number.
>
>
>
> I would ask what you would gain from such an exercise vs. the effort it
> would take you to gain it. Decide which is worth more to you.
>
>
> Rick Cook
>
>
>
> On Mon, Nov 3, 2014 at 7:59 AM, Sinclair, Keith <[email protected]>
> wrote:
>
> **
>
> Okay, have an unusual request.
>
>
>
> If there’s a missing block of Request ID numbers, can these be recovered?
> Example, a request started at 6001. Then someone uploaded a CSV and then
> deleted the records – not me, but someone in another department did this.
> So the next legitimate request ID is 7002 or something like that.
>
>
>
> Can the numbers that were deleted, 6002 – 7001, be reused? When I first
> started out on Remedy, the answer was not really. Not sure if anything has
> changed in the years/versions since then.
>
>
>
> Specs:
>
> ARS 8.1
>
> Oracel12b DB
>
>
>
> *Keith Sinclair*
>
> *Remedy Development*
>
> *ShopperTrak Chicago USA*
>
> O: 312.676.8289 | M: 630.946.4744
>
> *[email protected] <[email protected]>* | @shoppertrak
>
> www.shoppertrak.com
>
>
>
> _ARSlist: "Where the Answers Are" and have been for 20 years_
>
>
>
> _ARSlist: "Where the Answers Are" and have been for 20 years_
> ------------------------------
>
> No virus found in this message.
> Checked by AVG - www.avg.com
> Version: 2014.0.4765 / Virus Database: 4040/8433 - Release Date: 10/22/14
> Internal Virus Database is out of date.
>
> _ARSlist: "Where the Answers Are" and have been for 20 years_
> _ARSlist: "Where the Answers Are" and have been for 20 years_
>
_______________________________________________________________________________
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org
"Where the Answers Are, and have been for 20 years"
