How to grant UPDATE to DBC on DBC.IDCol Tables?

Database
Enthusiast

How to grant UPDATE to DBC on DBC.IDCol Tables?

I was reading an older post (http://forums.teradata.com/forum/database/reset-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.

3 REPLIES
Enthusiast

Re: How to grant UPDATE to DBC on DBC.IDCol Tables?

Kawish,

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

Khurram
Enthusiast

Re: How to grant UPDATE to DBC on DBC.IDCol Tables?

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.

Cheers,

Raja

Junior Contributor

Re: How to grant UPDATE to DBC on DBC.IDCol Tables?

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