Fixing identity gaps using undocumented commands (转载)

Sybase ASE 11.0 11.5 11.9 12.0 12.5 Last updated: 07 April 2002 Fixing identity gaps using undocumented commands   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 dbcc object_atts Download 'sp_identity' Displaying identity tables through sp_identity Displaying the current identity value through sp_identity Changing the identity value through sp_identity 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 ! 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 .