Note: the solutions for dealing with identity gaps as described in this article, apply to ASE pre-12.5.1. When using ASE 12.5.1, new features are available to fix identity gaps more easily. Details are described here.
Contents
Introduction
This document describes a way of using
the undocumented command dbcc object_atts
to quickly fix identity gaps. Because dbcc
object_atts is rather difficult to use, a stored
procedure sp_identity has been written
which handles the difficult parts for you. This stored
procedure can be
downloaded from here
.
Warning: The methods
described below are undocumented and not supported by
Sybase. Use entirely at your own risk. Do not call Sybase
Technical Support for assistence !
Warning2: Do NOT use this method in ASE 12.5.1+ or in ASE 15.
dbcc object_atts
The command dbcc object_atts provides
"back-door" access to the OAM page on which the
identity counter is stored. Unfortunately, the command is
poorly documented and, due to some tricky issues, rather
difficult to use.
The syntax is as follows:
dbcc object_atts (table_name, 0, subcommand, [ new_value ] )
The value for the second argument
(0) is mandatory. The possible subcommands are
get and put: get
displays the identity value for the table as stored on
its OAM page; put modifies this value to the
new_value specified (note that there is also a
reserve subcommand, but it is unclear exactly
what this does).
The main problem with dbcc
object_atts is that it displays the identity value as
a 16-byte hexadecimal string, which you must decode
yourself. Unfortunately, how to do this depends on the
length of the identity column's underlying numeric
datatype. Similarly, setting the identity value using the
put subcommand requires that you specify the
new value as a 16-byte hex string again. Because of this,
dbcc object_atts is next to unusable in
practical situations.
Fortunately, there is a solution: I
wrote a stored procedure sp_identity which handles
the decoding/encoding for you; it also gives a quick
overview of all tables with an identity column in a
database, and their maximum possible gap sizes.
Download 'sp_identity'
Download
sp_identity from here. To install, ASE 11.0 or later is required.
The downloaded file contains two versions: one for ASE 11.9/11.5/11.0 (sp_identity.119.sql) and one for ASE 12.0+ (sp_identity.120.sql).
To install sp_identity in your ASE server, just run the sp_identity.xxx.sql script through isql (xxx= 119 or 120).
Displaying identity tables through sp_identity
sp_identity has a number of
different functions. Without parameters, it simply
displays all tables in the current database that contain
an identity column. It also displays the maximum possible
identity gap size for each table:
1> sp_identity
2> go
Tables with an identity column in database '':
Owner.Table.Column datatype Maximum Identity Gap
------------------------------- -------------------------------
dbo.invoices.inv_nr numeric(18) 500000000000000 (burn)
dbo.small_gap_tab.a numeric(30) 10 (identity_gap)
dbo.my_table.n numeric(6) 500 (burn)
(burn)
indicates that the identity gap
size is determined by the setting of "identity
burning set factor";
(identity_gap)
means that the identity_gap
attribute has been defined for this table.
Displaying the current identity value through sp_identity
To determine the current identity value stored on the
OAM page for a specific table, you must follow a
three-step process:
- First, enable trace
flag 3604 by running
dbcc traceon(3604)
- Run sp_identity
specifying the table name:
1> sp_identity invoices
2> go
Table = invoices (id=1804533462)
Identity column = a numeric(18)
Max. Identity Gap = 500000000000000
("identity burning set factor" = 5000 = 0.05%)
Reading identity value from OAM page...
object_atts:get:return value=1
0207E20C: 0001c6bf 52634001 00000000 00000000 ....Rc@.........
0207E21C: .
DBCC execution completed. If DBCC printed error messages,
contact a user with System Administrator (SA) role.
The 16-byte hex string printed
in bold is the identity value from the OAM page. If
you don't see this output, you forgot to switch on
trace flag 3604 (so go back to step 1).
- Now run sp_identity
again, and specify the 16-byte hex string that
was just printed as the second parameter (you can
leave the spaces in):
1> sp_identity invoices, "0001c6bf 52634001 00000000 0000000"
2> go
Table = invoices (id=1804533462)
Identity column = a numeric(18)
Max. Identity Gap = 500000000000000
("identity burning set factor" = 5000 = 0.05%)
Decoded identity value on OAM page = 500000000000001
(hex=0001c6bf52634001)
So now we've retrieved and decoded
this value from the OAM page, but what does it exactly
mean ? Well, what you're looking at is what the identity
column would jump to in case the server would be shutdown
"with nowait". It is not the next value
to be assigned to the next row inserted in the table;
instead, is the value the server has stored on disk on
the OAM page, based on the settings of the configuration
option identity burning set factor (or, in
ASE 12.0, the identity_gap option) for this
table.
BTW, when you're running ASE 12.0, you can see (but not
modify) this same value in numeric form through the
command dbcc listoam; this also shows
the in-memory version of this value (stored in the
object's DES structure (for "descriptor")).
Changing the identity value with sp_identity
The identity value on the OAM page can be set to an
arbitrary value using sp_identity. This requires the
following steps:
- Determine the value
you want to reset the identity column to, let's
say 10033.
- Shut down the server
normally (i.e. do not use with nowait)
- Restart the server so
that no users can access the database, for
example, start the server in standalone mode (-m
in the RUN_SERVER file) or change the port number
in the interfaces file temporarily.
- Run the command
sp_identity
table_name, null, new_value
; set
new_value
1 lower than you want the next
identity value to be assigned:
1> sp_identity invoices, null, 10032
2> go
Table = invoices (id=1804533462)
Identity column = a numeric(18)
Max. Identity Gap = 500000000000000
("identity burning set factor" = 5000 = 0.05%)
object_atts:put:return value=1
DBCC execution completed. If DBCC printed error messages,
contact a user with System Administrator (SA) role.
Identity value on OAM page has been set to 10032
(hex=0x00000000000027300000000000000000)
You should now do a 'shutdown with nowait' immediately.
After restarting the server, the value assigned to the
next row inserted into 'invoices' will be 10033.
- Immediately after
this, do a shutdown with nowait. This
is essential; if you do a normal shutdown, or if
you wait too long, the OAM page value may be
overwritten with the in-memory value kept in
memory.
- Note that this
shutdown with nowait is also the
reason for doing a normal shutdown first in step
2 above -- otherwise you'd be creating new
identity gaps here ! Therefore, do not insert
rows into any other tables containing identity
columns before doing this shutdown with nowait.
- Restart the server in
the normal way
- Next, when inserting
a row into my_table, the identity value assigned
will be 10033.
Even though these are
quite a number of steps, they can be performed relatively
fast; in any case, it's all much quicker than the
standard remedy of doing a BCP-out of the table,
recreating the table, BCP-in, and recreate the indexes.
For more information on the background of identity gaps,
and ways to prevent identity gaps from occurring, see
www.sypron.nl/idgaps.html
.
|