Pervasive
Sign in | Join | Help
in

BIGIDENTITY Field?

Last post 07-17-2008 10:29 AM by BtrieveBill. 7 replies.
Page 1 of 1 (8 items)
Sort Posts: Previous Next
  • 04-30-2008 1:56 PM

    BIGIDENTITY Field?

    Does anyone know if there plans to offer an 8-byte IDENTITY field (BIGIDENTITY?) in the near future?

     

    Bill Bach
    Goldstar Software Inc.
    www.goldstarsoftware.com
  • 04-30-2008 3:42 PM In reply to

    Re: BIGIDENTITY Field?

    You've got tables with more than 4,294,967,295 records? Just curious :)

     

  • 04-30-2008 4:01 PM In reply to

    Re: BIGIDENTITY Field?

    Not yet. Cool 

    I'm actually answering a question for another who was asking about it.  Of course, if I only insert 10 records per second, I will be out of space in 12.6 years.  I can think of quite a few applications that have been running for at least that long by now!
     

    Bill Bach
    Goldstar Software Inc.
    www.goldstarsoftware.com
  • 05-05-2008 2:19 AM In reply to

    • Gordon
    • Top 100 Contributor
    • Joined on 08-30-2007
    • Delft, The Netherlands
    • Posts 51

    Re: BIGIDENTITY Field?

    Actually the IDENTITY fieldtype is a SIGNED integer.

    So it's only half that amount or 6.3 years Wink
     

  • 07-17-2008 5:20 AM In reply to

    Re: BIGIDENTITY Field?

    I tried inserting the first record with -2147483648 as the identity value, but the auto numbering gets stuck after a few records.

  • 07-17-2008 8:55 AM In reply to

    Re: BIGIDENTITY Field?

     Many people don't read the manual regarding these fields.  The BIGIDENTITY and IDENTITY data types are rooted in the Btrieve AutoInc field from many, many, many years ago.  It is actually a "special" data type in that it is indexed according to ABSOLUTE VALUE of the field, and not by SIGNED VALUE.  As such, if you insert a very large negative number, the next automatically-generated number will be the positive value of that number + 1.

    The idea was that the "negative" value could be used as a sign, perhaps to indicate data has been deleted, without actually deleting the row. 

    Bill Bach
    Goldstar Software Inc.
    www.goldstarsoftware.com
  • 07-17-2008 9:45 AM In reply to

    • wlau
    • Not Ranked
    • Joined on 06-11-2007
    • Posts 6

    Re: BIGIDENTITY Field?

    If the users get into 4,294,967,295 records by running for 12.6 years or other loads that get the table to a state with 4,294,967,295 records, should the solution be to back up the old data (from 10 years ago in the case of the 12.6 years usage scenario), hence freeing records instead of solving it by a BIGIDENTITY field?

  • 07-17-2008 10:29 AM In reply to

    Re: BIGIDENTITY Field?

    If you had an application that is that old, then the likelihood that you'd ever archive data is pretty limited. 

    First off, many applications of that age have no viable archive process to begin with.  They simply weren't written with longevity in mind.  As such, the end-user would need to be crafting this process himself.

    Second, even if you archive, you are likely to archive off the OLD data, not the NEW data.  This would leave you in a state where the records physically remaining in the file have all the highest numbers.  There is no way to change the AutoInc field to "roll over" back to 0 again.  Therefore, the only solution would be to manually "renumber" your records starting at 0.  While this can easily be done by dropping the key, setting all values to 0, and recreating the key, this change does NOT impact all of the other records that are pointing to that data.  So, if I have a Customer record where the ID is 2000000000, and I renumber that to 4, I have to find all of my invoices linked to that customer and change the values from 2000000000 to 4 at the same time.  Then, I have to find all sales reps and change from 2000000000 to 4 at the same time.  Then, I have to find all my sales rep commission history records and change them from 2000000000 to 4.  This continues for every record that  points to the customer record, ad nauseum.

    As you can see, the problem is not with the number of records, but with the record value.  It only takes ONE bad insert to mess with an AutoInc field.  (I knew of a user once who wanted to "test" his app with a fake Customer, so he used Xtrieve and manually put in a new record with an ID field of 999999999.  He forgot to remove the test record, and another customer was added the next day as 1000000000.  From that point forward, all of his Autoinc values were abnormally high.

    By the way, Pervasive has the exact same issue with their X$Field.Xe$Id column in the DDF's.  This field is NOT an AutoInc field, but rather is a regular two-byte integer field with values from 0 through 65535.  If you keep dropping and recreating tables, the database creates new field ID values with a simple logic: GetLast key value, Add one, Insert.  The problem comes in when the maximum of 65535 is exceeded.  Instead of rolling over, the engine enters another loop like this: Subtract one, try insert, Loop until successful.  The net result is that once you have reached the "last" Field ID number (because the DDF's have been around for many years), creating a new table can take minutes of processing time as the engine must search for an available Id number.  The only solution is to recreate new DDF's, move all of the records over, an drenumber the ID numbers as you go.  We actually wrote a tool to do this, for this very reason!
     

    Bill Bach
    Goldstar Software Inc.
    www.goldstarsoftware.com
Page 1 of 1 (8 items)
© 2008 Pervasive Software Inc. All Rights Reserved.