ASE 16 max integer value for correlative reaching limit

[jmtorres]

Hello,

Running ASE 16 , we are reaching the max value for an integer datatype( 2,147,483,648) in a column used for unique identification, i.e a corraltive number .( each time a new row is inserted a new incremented value is added).

It's and old app , which does not use identity values for this matter.

So, which would be the best way to ovecame this matter , with the restiriction that the correlative numbers( int column) must be kept ?

We are thinking it to bcp out -in, create a new table with bigint. numeric(m,0) column.. ?

Any suggestions

Thank you!

Jose

[Mark_A_Parsons]

Are these values also stored in other tables (eg, as foreign keys)?

Do you know if there are any large gaps of 'unused' numbers?

Can older records be purged/archived thus allowing you to 'wrap around' the number (ie, start over from +1)?

How easy (hard?) would it be for the application to switch to using negative values (this would allow for an additional 2+ billion values)? Could it be as easy as setting the 'next' number to -2,147,483,648 and then allow the current auto-increment (+1) start generating numbers until you get up to -5, -4, -3, -2, -1, 0 ... at which point you'd need to address the exhaustion of 4+ billion numbers (though hopefully this will be after you retire/move-on! 🙂

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

One option would be to convert the current column to an unsigned int (thus allowing you to increment up to 4+ billion). Since the int and unsigned int datatypes require the same 4 bytes it's as 'easy' as updating syscolumns to reference the new datatype.

Updating syscolumns consists of updating the type and usertype columns, keeping in mind these values are based on the datatype and NULLability of the column:

datatype      NULLability  type  usertype
------------  -----------  ----  --------
         int  not NULL       56         7
unsigned int  not NULL       66        45

         int      NULL       38         7
unsigned int      NULL       68        45

The UPDATE statement would look like:

begin tran

update  syscolumns
set         type = case     type when 56 then 66 when 38 then 68 else     type end,
        usertype = case usertype when  7 then 45                 else usertype end
where   id   = object_id('table_name')
and     name = 'column_name'

-- verify results before issuing 'commit tran'

NOTES:

• this method is not addressed in the manuals so it's not officially supported by SAP
• verify the table does not currently contain any negative numbers in the int column otherwise follow-on operations will treat negative numbers as the associated 2's complement (ie, treated as positive numbers)
• this method only works when a) dealing with compatible datatypes and b) both datatypes require the same number of bytes of storage (in this case 4 bytes for both int and unsigned int)
• you'll need to configure ASE to allow system table updates (sp_configure 'allow updates',1)
• it's good practice is to disable system updates once completed (sp_configure 'allow updates',0)
• you'll need to insure all applications and SQL code can handle unsigned int values (ie, up to 4+ billion)
• incrementing an unsigned int will require adding an unsigned int value of '1' (eg, 'column = column + convert(unsigned int,1)' or 'declare @inc unsigned int; select @inc=1; column = column + @inc')
• this method has been verified in ASE 16.0 SP04 PL04 (ie, adding +1 to 2147483647 generates an overflow error before the update to syscolumns, but completes successfully after the update to syscolumns)
• this is a stop gap measure (increasing max value from 2 billion to 4 billion) and will require a different approach once you hit the 4 billion limit
• thoroughly test in a dev environment before performing this operation in production

[jmtorres]

Thank you Mark

Could you share the SQL for updating syscolumns in order to change form int to unsigned int info on the user table?

Regards

Jose

[Mark_A_Parsons]

Updated the answer with more details re: modifying syscolumns

[tilman_model-bosch]

Another warning regarding manual update of system catalogue tables:
This is not a supported method - so if something goes wrong your are on your own. SAP Support will probably not be able to help you. Sometimes manual update of system catalog tables is the method of choice - if there is no good alternative. But in this case I rather suggest to create a new table with a larger data type (e.g. bigint) - you can then load all rows from the original to the new table. This is then completely supported.

[jmtorres]

Thanks Mark!

Regards

Jose