All Forums Database
Kawish_Siddiqui 37 posts Joined 03/07
12 Dec 2013
How to grant UPDATE to DBC on DBC.IDCol Tables?

I was reading an older post ( identity-column-counter) and concluded that somehow Update is possible for DBC tables (DBC.IdCol). 
I've come-up with  situation where I need to perform similar experiment (resetting ID column values).
Now question is, how can I update a table in DBC (how to grant update rights to DBC)?
I am connected using DBC user and trying to update a value but getting error:
Failed.  [5315] The user does not have UPDATE access to DBC.IdCol.AvailValue.

Kawish Siddiqui -

M.Saeed Khurram 544 posts Joined 09/12
12 Dec 2013

Can you please explain what do you want to achieve, as discussed in the post you mentioned, you can either recycle the Identity, Or you can use START WITH, INCREMENT BY, MINMUM VALUE,  options in the identity.
I beleive the DBC.ColID can not be updated manually. But you can change the range in DBS, using IDCOLBATCHSIZE, valid range is 1-1000000


Raja_KT 1246 posts Joined 07/09
13 Dec 2013

Hi Kawish,
It is a riddle now for me since you connect with DBC and you get an error. I am not sure, however when I run this query:
SELECT * FROM DBC.AllRightsV where tablename='IDCol';, I got the username as SYSDBA. I do not have admin right to perform the experiments as you can.
My past experiences with IDCol is always bad, including other DBs. It is better to have your own Identity column where you can have full control. As and when business and requirement expand, it creates a mess. When we make a call, we have to answer :), no one saves us.

Raja K Thaw
My wiki:
Street Children suffer not by their fault. We can help them if we want.

dnoeth 4628 posts Joined 11/04
15 Dec 2013

Hi Kawish,
all important system tables are secured, nobody including DBC has rights to INSERT/UPDATE/DELETE/DROP them. This is done to protect the system from getting damaged.
There's no built-in process to reset an identity (of course you might do an enhancement request), thus when you do an illegal update the internal process to assign the sequence doesn't know about it and will keep it's old range until it needs a new one (i just tried it).


You must sign in to leave a comment.