[Post] fixed the data skip of the identity Field

Fixed the conflict of data in the identity field.
[Post, the content has not been tested]

Contents

1. Introduction
2. DBCC object_atts
3. Download The 'SP _ identity 'Stored Procedure
4. Use sp_identity to display tables that use the identity Field
 
5. Use sp_identity to display the identity numbers of the current table
6. Use sp_identity to modify the table's identity number

1 Introduction
In Sybase, an undisclosed DBCC command is used, that is, DBCC object_atts, to quickly fix the identity Hop because DBCC

Object_atts is difficult to use, while sp_identity is easier to solve your problem.
Warning the methods in this article are not found in the documents published by Sybase and are not supported by Sybase. Whether or not they are used depends on your own. If a problem occurs, you cannot
Sybase technical support for help

2. DBCC object_atts
The DBCC object_atts command provides a backdoor for accessing the OAM page. In Oma, the table's identity counter is saved.
Unfortunately, this command is hardly described in the document and is difficult to use. Its syntax is as follows:
DBCC object_atts (table_name, 0, subcommand, [new_value])
First parameter: Table Name, second parameter 0,
Third parameter: Get --- display the identity value stored in OMA
Or put --- set the identity value in OMA. The specific data set is the fourth parameter data.
Fourth parameter: Number of new settings

The main problem is that DBCC object_atts displays 16-byte hexadecimal characters, which must be interpreted as a hexadecimal character by yourself.

The length of the identity column. In the actual environment, it is rarely the first choice.
Fortunately, there is a stored procedure sp_identity that can process encoding and decoding and give information about all tables with identity columns.

Download 'SP _ identity'
Sp_identity only supports 11. Versions later than 0. The downloaded file contains two versions.

, Sp_identity.119. SQL. The other one is used for ASE 12.0 and later versions (sp_identity.120. SQL)

4. Use sp_identity to display tables that use the identity Field
Sp_identity has several different functions and does not contain any parameters. It only displays tables containing the identity column in the current database,
And the largest possible identity jump

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.nnumeric (6) 500 (burn)

(Burn) indicates that the number of retrieved values in the identity column is determined by the system parameter "Identity burning set factor ".
(Identity_gap) indicates that the table has a custom identity_gap attribute.

5. Use sp_identity to display the identity numbers of the current table
To determine the identity value of the OAM page of a specific table, follow these three steps:
First enable tracking flag 3604 (run DBCC traceon (3604 ))
Run sp_identity with the specified 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 hexadecimal string is the identity value stored in OAM (0001c6bf 52634001 00000000 00000000)

Run sp_identity again and specify this string parameter

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)

Now we get and decode the identity value in OAM, but what does it mean?
First, if you stop the server with "with Nowait", the number of hops in this identity column. Instead of the next data in the table.
Instead, add the ID seed number to the data stored in OAM (in ASE 12.0, It is the identity_gap option)
In addition, when you run ASE 12.0, you can view the data by running DBCC listoam.

6. Use sp_identity to modify the table's identity number
Using sp_identity, the number of identity in OAM can be set to any number. The following steps are required:
1. Confirm the data you want to set (view the number of identity data in the table, such as 10033)
2. Stop the server normally
3. Restart the server in single-user mode or prevent user access.
4. Run sp_identity table_name, null, new_value;
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 shoshould now do a 'shutdown with nowait' immediately.
After restarting the server, the value assigned to
Next row inserted into 'invoices' will be 10033.

5. Run 'shutdown with nowait' immediately, so that the next identity number in the table is 10033.
6. Restart the Sybase service.

Note: If you have applied the identity data type and require that the identity column be continuous with a large amount of data, this method may take a short time.
Otherwise, it is not recommended.

The following is sp_identity.120. SQL, which is used for Versions later than 12.0.
/*
* Sp_identity.120. SQL-version for ASE 12.0 or later
* (When running ASE 11.9/11.5/11.0, install sp_identity.119. SQL instead)
*
* Description
* ==============
* This file contains the Stored Procedure 'SP _ identity ', which performs some
* Useful functions W. R. T. Identity columns:
*
*-Displays all tables with an identity column in the current database
*-Displays the identity value stored on a table's OAM page
*-Resets the identity value stored on a table's OAM page
*
* For details and background, see http://www.sypron.nl/idfix.html.
*
*
* Installation
* ================
* Execute this script using "iSQL", using a login having both "sa_role"
* And "sso_role ".
* The stored procedure will be created in the sybsystemprocs database.
*
*
* Notes
* ====
*-Traceflag 3604 shocould be enabled before running sp_identity for
* Specific table ("DBCC traceon (3604)"); If omitted, you won't see
* Any output.
*
*-To run sp_identity for a specific table, sa_role and sybase_ts_role
* Are required.
*
*-Sp_identity was successfully tested on ASE versions 11.0.3, 11.5,
* 11.9.2 and 12.0 on various platforms.
*
*-Sp_identity will not work on ASE 11.0 because this version
* Does not support the 'case' expression.
*
*-Sp_identity was not tested on a 64-bit ASE version.
*
*
* Revision history
* ========================
* Version 1.0 17-dec-2000 first version
* Version 1.1 Oct-2002 handle identity_gap = NULL (thanks to Alan Cooper ),
* And some small improvements for Installation
* Version 1.2 Apr-2003 improved Installation
*
*
* Copyright Note & Disclaimer:
* ====================================
* This software is provided "as is"; there is no warranty of any kind.
* While this software is believed to work accurately, it may not work
* Correctly and/or reliably in a production environment. In no event shall
* Rob Verschoor and/or sypron B. V. be liable for any damages resulting
* From The use of this software.
* You are allowed to use this software free of charge for your own
* Professional, non-commercial cial purposes.
* You are not allowed to allocate or bundle this software or use it for any
* Other extends cial purpose without prior written permission from
* Rob Verschoor/sypron B .v.
* You may (re) distribute only unaltered copies of this software, which
* Must include this copyright note, as well as the copyright note in
* The header of each stored procedure.
*
* Note: All trademarks are acknowledged.
*
* Please send any comments, bugs, suggestions etc. To the below email
* Address.
*
* Copyright (c) 2000-2002 Rob Verschoor/sypron B. V.
* P. O. Box 10695
* 2501 HR Den Haag
* The Netherlands
*
* Email: rob@sypron.nl
* Www: The http://www.sypron.nl/
*----------------------------------------------------------------------------
*/

Set nocount on
Go
Set flushmessage on
Go

Use sybsystemprocs
Go

-- We need to be at ASE 12.0 or later; if not, abort this script
If isnull (object_id ("Master. DBO. sysqueryplans"), 99)> = 99
Begin
Print ""
Print ""
Print "************************************** ******"
Print "************************************** ******"
Print "this script is for ASE 12.0 or later ."
Print "Please install sp_identity.119. SQL instead ."
Print "************************************** ******"
Print "************************************** ******"
Print ""
Print ""
Print ""
Set background on -- terminate this script now
End
Go

Print ""
Print "installing 'SP _ identity '..."
Print ""
Go