cancel
Showing results for 
Search instead for 
Did you mean: 

Need percentage of values used in an identity column

Former Member
0 Kudos

Hi All,

I am very thankful to all of you for the supprt provided at all times.

I am asked to provide the following details about an identity column for a table:

- Database name

- Table name

- Identity column

- Used number of identity values(max of identity values)

- Percentage of used values

- Next identity value to be generated

- Remaining identity value

I was able to get all these information from the global variables in sybase and with some calculations I was able to find all the identity related information as well but it takes time. For istance, the max of the column need to be found before calculating the percentage of used identity values. Since, these tables are huge(smallest table is 95 GB - bad design) the max computation itself takes time. Our Senior production DBA was able to get it with some system stored procedure which he doesn't want to disclose to us. We have requested him to at least guide us in the right direction but we are going nowhere.

I checked all the sybase documentation but did not get much help.

I sincerely request everyone to suggest me the most efficient and simple way to retrieve all this information.

Sybase ASE version is 12.5.3

Identity column

---------------------

precision - 12

scale - 0

Table size is 95 GB

Table has a  unique clustered index on five column and the identity column is one among them.

Please let me know if any information is required.

Thanks a lot in advance.

Regards

Nanda Kumar K

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi Bret/Kevin,

Both of you are right. I am aware that we can create stored procedures starting with sp_ and I have done it in the past myself. The onshore DBA's have convinced my manager that such a system procedure exists and my manager wants me to find it out because he doesn't want to rely on a new stuff developed in-house when sybase already has one to do this job.

I have begged them to tell me what it is but I managed to get only the name of the stored procedure(SP_USER_IDENTITY) which I couldn't find anywhere. I think they are just making fun of me and trying to test my knowledge since I am a new DBA here. As you both suggested I will ask for the source code of the stored procedure to find out the truth.

My intention was to confirm that such a stored procedure doesn't really exist in Sybase ASE. I hope this discussion thread would be enough to convince my manager but I will collect every proof possible from onshore DBAs before I can prove this.

Thanks for your valuable inputs.

Regards

ADNAN

kevin_sherlock
Contributor
0 Kudos

Your colleages and manager are being rediculous.  All of the "proof" you ever need is found in the installation directory of the sybase software.  Download a copy for yourself at:

http://www.sybase.com/linux/ase

and search if for this procedure name yourself if you don't have access to your production install.

# find $SYBASE -type f -exec grep -il "sp_user_identity" {} \;

Former Member
0 Kudos

Hi Kevin,

Our guess was right, They were just misleading me by giving wrong information. The stored procedure - SP_USER_IDENTITY is developed in-house and is part of an application.

Thanks to everyone who helped me in this search operation.

Regards

ADNAN

Answers (3)

Answers (3)

Former Member
0 Kudos

Hi Bret/Kevin,

Thank you for your inputs.

I had the same idea in my mind and already formatted a query which will yield all these results.

My team members are insisting me to find out the built-in system stored procedure which gives all these data. I am scoring through documentation and to my surprize some of the commonly used system stored procedure itself is not listed in the documentation.

However, I had a chat with another production DBA in London and he said they are are using stored procedure SP_USER_IDENTITY to get these data. I am not aware of this stored procedure and I tried it and ASE fired an error message that it does not exist. I am still not sure if the production DBAs at onsite are misleading me by giving a wrong information.

@Mark: Used value means the number of identity values that are consumed against the total available values. I can calculate this manually but I am trying to find out a system stored procedure which can do this job.

Please let me know if any such undocumented stored procedure exists which will generate all the information about an identity column in a table.

Regards

ADNAN

former_member188958
Active Contributor
0 Kudos

sp_user_identity is not a system stored procedure created by SAP/Sybase.   I don't find any mention of it on Google; most likely it is something your company has developed in-house.   I don't know of anything provided that would give you this identity information.

Dont think that just because the name starts with "sp_" that it necessarily comes from SAP.  You can create your own procedures in sybsystemprocs as well.  It isn't uncommon for people to create their own or make modified versions of the system procedures.  ASE normally only looks for a procedure in your current database, but if the name starts with "sp_", ASE will look in the current database, then look in sybsystemprocs, then finally look in master to try to find the procedure you are trying to execute.  (There are a small number of procedures created in master so they can be used even if sybsystemprocs has failed to recover).

There are some SAP provided stored procedures that aren't documented, but as far as I know these are all subprocedures for documented procedures. 

If you've found a DBA willing to talk to you, ask him for defncopy output for the procedure.

-bret

kevin_sherlock
Contributor
0 Kudos

there is no such stored procedure "sp_user_identity" installed in a standard ASE installation.  Most likely, this is a custom procedure installed by someone on your site.

System procedures are all contained in a file located at $SYBASE/$SYBASE_ASE/scripts/installmaster (on unix/linux systems).   Have your prod DBA's point out where in that file this proc exists.   It won't be there which means it was installed manually by one of your DBA's.

As Mark has pointed out well, most of the information you requested can be considered a bit nebulous (and subject to several assumptions) based on the characteristics of identity columns, so there wouldn't be any such Sybase/SAP supplied procedure to output this info in this manner.

Ask your DBA's to send you the source code for the procedure and see if it looks something like what has been posted in this thread.

kevin_sherlock
Contributor
0 Kudos

Try this bit of SQL code.  Basicallly it leverages the "next_identity" function to determine which value is next to occur for the column and computes the other derived values from that.  This also assumes that identity values are uniformly used (ie, no gaps).  That's a big assumption but it seems built-in to your question.

select "dbname"=db_name()

      ,"tablename"=so.name

      ,"identcol"=sc.name

      ,"usedvals"=convert(int,next_identity(so.name)) - 1

      ,"pctused"=str(100.00 *

                    ((convert(int,next_identity(so.name)) - 1))

                        / convert(numeric(32),power(convert(numeric(32),10),sc.prec)-1)

                    ,6,2)

      ,"nextident"=next_identity(so.name)

      ,"remainident"=convert(numeric(32),power(convert(numeric(32),10),sc.prec)-1) - convert(int,next_identity(so.name)) + 1

from  sysobjects so

    inner join

      syscolumns sc

      on so.id = sc.id

where so.sysstat2 & 64 = 64 -- table has identity column

and  sc.status & 128 = 128  -- column is an identity column

order by so.name 

former_member188958
Active Contributor
0 Kudos

For most practical purposes, the "max value used"  would be the value given by

   select next_identity(tablename) - 1.

So for percentage used you can use

    select next_identity(tablename) / power(10,precision)

-bret