SYBASE ASE修复identity 类型字段数据的跳跃（译）
内容提要1、引言
2、dbcc object_atts
3、下载'sp_identity' 存储过程
4、使用sp_identity显示使用identity 类型字段的表
5使用sp_identity显示当前表的 identity 数字
6、使用sp_identity 修改表的 identity数字
1引言
在sybase 中，有一个使用未公开的dbcc 命令 ，即 dbcc object_atts 快速修复identity 跳跃的方法，因为 dbcc
object_atts 比较难于使用,而存储过程 sp_identity 更容易完成你的问题
警告：本文的方法没有出现在sybase 发布的文档中，并且没有被 sybase 支持，使用与否取决定你自己，如果出现问题，不能从
sybase 技术支持那里得到帮助
2、dbcc object_atts
dbcc object_atts 命令提供了 一个访问 OAM 页的后门，在 OMA 中，保存了表的 identity 计数器。
不幸的是，这个命令在文档中几乎没有任何说明，并且很难使用，其语法如下：
dbcc object_atts (table_name, 0, subcommand, [ new_value ] )
第一个参数 ：表名，第二个参数 0，
第三个参数： get---显示 保存在 OMA 中的 identity 值
或put---设置 OMA 中的 identity 值，设置的具体数据为 第四个参数数据
第四个参数 ：新设置的数
主要的问题是 dbcc object_atts 显示的数据为 16字节的 16进制字符，你必须自己解释为10进制，并且，如何解释依赖于
identity 列的长度，同样，设置它也是。在实际环境中，很少作为首选。
幸运的是，有一个存储过程 sp_identity 能够处理编码与解码， 并且给出所有含 identity 列的表的情况
下载 'sp_identity'
sp_identity 只支持 11。0 以后版本，下载文件中包含两个版本，一个用于 ASE 11.9/11.5/11.0 的叫
,sp_identity.119.sql，另外一个用于ASE 12.0及以后版本(sp_identity.120.sql)
4、使用sp_identity显示使用identity 类型字段的表
sp_identity 有几个不同的功能，不带任何参数运行，只显示当前数据库中 包含 identity 列的表，
以及最大可能的 identity 跳跃
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) 表示 identity列的 一次取数由系统参数"identity burning set factor" 决定，而
(identity_gap) 表示 该表有自定义的identity_gap 属性
5使用sp_identity显示当前表的 identity 数字
确定当前特定表的OAM 页的identity 值，必须按照以下三步过程：
首先 打开跟踪标志 3604 (运行 dbcc traceon(3604) )
运行 指定特定表名运行 sp_identity
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.
这个 16字节的 16进制字符串是 OAM 中保存的 identity 值 （0001c6bf 52634001 00000000 00000000）
再次运行 sp_identity ,并且 指定这个字符串参数
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)
现在，我们得到并解码了 OAM 中的 identity 值，但它意味着什么呢？
首先，如果你用 "with nowait" 停止服务器，这个 identity 列将跳跃多少。而不是表中数据的下一个。
而是保存在 OAM 中的数据加上 identity 种子设顶数(在 ASE 12.0, 为identity_gap 选项)
另外，当你运行 ASE 12.0, 可以通过运行 dbcc listoam看到这个数据
6、使用sp_identity 修改表的 identity数字
使用 sp_identity ， OAM 中identity 的 数字可以被设置为任意数. 这个需要如下步骤：
1、确认你要设置的数据（查看表中identity 的数据应该到多少,比如 10033 ）
2、正常停止服务器
3重新启动服务器，以单用户模式，或者不让用户访问
4、 运行 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 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.
5然后立即运行 'shutdown with nowait' ，这样 表的下一个identity 数字是 10033
6重新正常启动sybase 服务
说明：如果你应用了 identity 数据类型，并且要求identity 列保持连续，并且数据量很大，那么，用这个方法需要的时间可能比较短
否则不推荐使用。
下面是SP_IDENTITY.120.SQL，用于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 should be enabled before running sp_identity for a
* 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.017-Dec-2000First version
* Version 1.1Oct-2002Handle identity_gap=NULL (thanks to Alan Cooper),
* and some small improvements for installation
* Version 1.2Apr-2003Improved 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 purposes.
* You are not allowed to sell or bundle this software or use it for any
* other commercial 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: 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
if object_id("sp_identity_help") <>; NULL
begin
drop proc sp_identity_help
end
go
create proc sp_identity_help
/* Copyright (c) 2000-2002 Rob Verschoor/Sypron B.V. */
as
begin
print " "
print " Usage: sp_identity table_name, ""hex-string-from-OAM-page"", new-identity-value "
print " Notes: "
print " - specifying only parameter 1 retrieves the current identity value for"
print " that table from the OAM page as a hexadecimal string."
print " - specifying this hexadecimal string as the second parameter (in quotes)"
print " will decode the hex value to a numeric value."
print " - specifying a numeric value for the third parameter will set that"
print " value as the new identity value for this table."
print " - parameters 2 and 3 cannot be specified together: one of them must be NULL."
print " - ""dbcc traceon(3604)"" must be run before using option 2 or 3"
print " "
print " Copyright (c) 2000-2002 Rob Verschoor/Sypron B.V."
print " See http://www.sypron.nl/idfix.html for background information & updates."
print " "
end
go
grant execute on sp_identity_help to public
dump tran sybsystemprocs with truncate_only
go
if object_id("sp_identity") <>; NULL
begin
drop proc sp_identity
end
go
create proc sp_identity
/* Copyright (c) 2000-2002 Rob Verschoor/Sypron B.V. */
@p0 varchar(50) = NULL, -- table name
@p1 varchar(50) = NULL, -- hex value to decode
@p2 numeric(38) = NULL-- new value to set
as
begin
set nocount on
declare @n numeric(38), @j numeric(38), @n256 numeric(3), @n10 numeric(38)
declare @i int, @max int, @idlen int, @idlenb int, @ib int, @len int
declare @p0_id int, @b int, @b1 binary(1), @lsb int
declare @idgap int, @idburn int, @maxidgap numeric(38), @max1 int, @max2 int
declare @idburnpct numeric(5,2), @v int, @ntab int
declare @c2 char(2), @doampg int, @indid int, @dbname varchar(32)
declare @vc50 varchar(50), @colname varchar(32), @vb16 varbinary(16)
select @dbname = db_name()
-- check version
select @v = 0
if exists (select * from sysobjects
where name = "sysqueryplans" and type = "S")
begin
select @v = 12
end
if @p0 = '?'
begin
exec sp_identity_help
return 0
end
-- numeric values
select @n256 = 256
select @n10 = 10
-- id burning set factor
select @idburn = value from master.dbo.syscurconfigs where config=141
select @idburnpct = convert(numeric(5,2), @idburn * 0.00001)
-- figure out msb/lsb
select @lsb = 0
if substring(convert(binary(4), 1), 1 ,1) = 0x01 select @lsb = 1
if substring(convert(binary(4), 1), 2 ,1) = 0x01 select @lsb = 2
if substring(convert(binary(4), 1), 3 ,1) = 0x01 select @lsb = 3
if substring(convert(binary(4), 1), 4 ,1) = 0x01 select @lsb = 4
-- get all tables containing identity columns in this database
select id = so.id, owner = su.name, uid = so.uid, so.name, si.doampg, si.indid, idgap =
isnull(si.identitygap,0),
colname = sc.name, sc.prec, maxgap = convert(numeric(38), ceiling((@idburn * 0.0000001) *
power(@n10,prec)))
into #id
from sysindexes si, syscolumns sc, sysobjects so, sysusers su
where si.indid < 2
and so.type = "U"
and sc.status & 128 = 128
and so.id = sc.id
and so.id = si.id
and so.uid = su.uid
select @ntab = @@rowcount
set arithabort numeric_truncation off
if @p0 = null
begin
-- display all tables with identity columns in the current DB
if @ntab = 0
begin
print " There are no tables with an identity column in database '%1!'", @dbname
return 0
end
print " Tables with an identity column in database '%1!':", @dbname
print " "
select @max1 = max(char_length(owner + "." + name + "." + colname)) + 12 from #id
select @max2 = max(char_length(convert(varchar(38), maxgap))) + 7 from #id
if @max1 <= 52 and @max2 <= 24
begin
select convert(varchar(52), owner + "." + name + "." + colname + " numeric(" +
convert(varchar(2),prec) + ")") "Owner.Table.Column datatype",
convert(varchar(24), case when idgap = 0 then convert(varchar(38),maxgap) + "
(burn)"
else convert(varchar, idgap) + " (identity_gap)"
end) "Maximum Identity Gap"
from #id
order by name
end
else
if @max1 <= 32 and @max2 <= 44
begin
select convert(varchar(32), owner + "." + name + "." + colname + " numeric(" +
convert(varchar(2),prec) + ")") "Owner.Table.Column datatype",
convert(varchar(44), case when idgap = 0 then convert(varchar(38),maxgap) + "
(burn)"
else convert(varchar, idgap) + " (identity_gap)"
end) "Maximum Identity Gap"
from #id
order by name
end
else
begin
select convert(varchar(78), owner + "." + name + "." + colname + " numeric(" +
convert(varchar(2),prec) + ")") "Owner.Table.Column datatype",
convert(varchar(50), case when idgap = 0 then convert(varchar(38),maxgap) + "
(burn)"
else convert(varchar, idgap) + " (identity_gap)"
end) "Maximum Identity Gap"
from #id
order by name
end
print " "
print " Legend:"
print " (burn) : gap size is determined by ""identity burning set factor"" "
if @v = 12
begin
print " (identity_gap) : gap size is determined by the ""identity_gap"" setting"
end
print " "
print " Current value for ""identity burning set factor"" = %1! (=%2!%%)", @idburn, @idburnpct
return 0
end
if charindex("sa_role", show_role()) = 0
begin
print "You must have 'sa_role' to run this procedure."
return -1
end
if charindex("sybase_ts_role", show_role()) = 0
begin
print "You must have 'sybase_ts_role' to run this procedure."
return -1
end
-- get some info on the object
select @p0_id = object_id(@p0)
if @p0_id = NULL
begin
print " Error: '%1!' is not a user table.", @p0
return -1
end
if @p0 not like "%.%"
begin
select @ntab = count(*) from sysobjects where name = @p0 and type = "U"
if @ntab >; 1
begin
print " %1! tables named '%2!' exist in this database:", @ntab, @p0
print " "
select owner + "." + name "owner.table_name"
from #id where name = @p0 order by uid
print " "
print " Specify 'owner.table_name' to identify the table."
return -1
end
end
select @doampg = doampg, @indid = indid, @idgap = idgap
from #id where id = @p0_id
if not exists (select 1 from syscolumns where id = @p0_id and status & 128 = 128)
begin
print " Error: Table '%1!' does not have an identity column", @p0
return -1
end
-- retrieve the length of the numeric identity column as declared when the
-- table was created
select @len = prec, @colname = name
from syscolumns where id = @p0_id and status & 128 = 128
if @@rowcount = 0
begin
print " Error: Could not retrieve length of identity column for table '%1!'", @p0
return -1
end
select @idlen = @len -- column precision (positions)
select @idlenb = (@idlen+1-((@idlen+5)/12)-(@idlen/12))/2 -- column length (bytes)
print " "
print " Table = %1! (id=%2!)", @p0, @p0_id
print " Identity column = %1! numeric(%2!)", @colname, @len
--print " OAM page = %1!", @doampg
--print " Bytes on OAM page = %1!", @idlenb
if @idgap >; 0
begin
print " Max. Identity Gap = %1! (""identity_gap"" = %2!)", @idgap, @idgap
end
else
begin
set arithabort numeric_truncation off
select @maxidgap = ceiling((convert(numeric(38),@idburn) * 0.0000001) * power(@n10,@len))
print " Max. Identity Gap = %1!", @maxidgap
print " (""identity burning set factor"" = %1! = %2!%%)", @idburn, @idburnpct
if @v = 12
begin
print " (Note: ""identity_gap"" has not been set for this table !)"
end
end
print " "
if @p1 = NULL and @p2 = NULL -- retrieve current ID value
begin
print " "
print " Reading identity value from OAM page... (traceflag 3604 should be enabled !)"
print " "
dbcc object_atts (@p0, 0, get)
print " "
print " To decode this hexadecimal value, re-run 'sp_identity' with the hexadecimal"
print " string as a quoted 2nd parameter (you can leave the spaces in). "
print " Example:sp_identity %1!, ""the-hex-string-from-the-above-output"" ", @p0
print " "
--
-- only for 12.0+, dbcc listoam will display the values in the OAM and the DES
--
--dbcc listoam(@dbname, @p0_id, @indid)
--
return 0
end
if upper(@p1) like "[ 0-9A-F]%" -- entered a hex string, decode it
begin
if @p2 != NULL
begin
print " Error: When decoding a hex value from the OAM page, parameter 3"
print " (the new identity value) must be NULL."
exec sp_identity_help
return -1
end
select @ib = @idlenb -- counts bytes
select @i= char_length(@p1) -- counts string
select @n = 0 -- holds result
while 1 = 1
begin
select @c2 = substring(@p1, (char_length(@p1)-@i)+1, 2)
--print "Step %1!, [%2!]", @ib, @c2
if upper(substring(@c2,1,1)) = " " -- ignore single space pasted in by user
begin
select @i = @i - 1
continue
end
if (upper(@c2) not like "")
begin
print " Error: Invalid characters (%1!) in hex string", @c2
break
end
-- add this byte to the result
select @vc50 = @vc50 + @c2
select @j = power(@n256, (@ib-1))
select @n = @n + @j * hextoint("0x" + @c2)
--print "Step %1!, exp= %2!, result = %3!", @ib, @j, @n
-- next byte
select @ib = @ib - 1
if @ib = 0 break-- ready
-- next 2 chars
select @i = @i - 2
if @i = 0
begin
-- we shouldn't have got here, error
print " Error: ID column length and specified hex string do not match !"
break
end
end
if @ib = 0
begin
print " Decoded identity value on OAM page = %1!", @n
print " (hex= %1!)", @vc50
print " "
return 0
end
else
begin
print " Error: Aborted due to error."
return -1
end
end
if @p2 != NULL -- entered a new ID value, set it
begin
if @p1 != NULL
begin
print " Error: When setting a new identity value, parameter 2"
print " (the hex string from the OAM page) must be NULL."
exec sp_identity_help
return -1
end
-- set the new ID value
if @p2 <= 0
begin
print " Error: The new identity value must be >;= 0."
print " "
exec sp_identity_help
return -1
end
if char_length(convert(varchar, @p2)) >; @idlen
begin
select @vc50 = replicate("9", @idlen)
print " Error: New identity value (%1!) is too large", @p2
print " for this identity column. The maximum possible value", @p2
print " is %1! (%2! positions).", @vc50, @idlen
return -1
end
if @lsb = 0
begin
print " "
print "Internal error in 'sp_identity':"
print "Cannot figure out byte order on this platform."
print "Please send a note to 'rob@sypron.nl' with the text of this"
print "error and your @@version string."
return -1
end
-- convert the numeric value to hex
select @ib = @idlenb -- counts bytes
select @i= 15
select @vc50 = NULL -- holds result
select @vb16 = NULL -- holds result
select @n256 = 256
select @n = @p2
set arithabort numeric_truncation off
while 1 = 1
begin
select @j = power(@n256, @i)
select @b = @n / @j
select @n = @n - (@b * @j)
select @c2 = right(inttohex(@b),2)
select @b1 = substring(convert(binary(4), @b), @lsb,1)
select @vb16 = @vb16 + @b1
--print "Step %1!, @b=%2!, hex(c2)=%3!, hex(b1)=%5!, result=%4!", @i, @b, @c2, @vb16, @b1
select @i = @i - 1
if @i < 0 break-- ready
end
if @i < 0
begin
select @vb16 = substring(@vb16, 16 - (@idlenb) + 1, (@idlenb) ) + substring(@vb16, 1, 16 -
(@idlenb) )
-- set the new ID value
dbcc object_atts(@p0, 0, "put", @vb16)
print " "
print " Identity value on OAM page has been set to %1!", @p2
print " (hex= %1!)", @vb16
print " "
select @p2 = @p2 + 1
print " You should now do a 'shutdown with nowait' immediately."
print " After restarting the server, the value assigned to the next row "
print " inserted into '%1!' will be %2!.", @p0, @p2
print " "
return 0
end
else
begin
print " Error: Aborted due to error."
return -1
end
end
-- we should never get here
print "Internal error in 'sp_identity': Reached invalid end of procedure."
return -1
end
go
grant execute on sp_identity to public
dump tran sybsystemprocs with truncate_only
go
print "Ready. For usage information, run 'sp_identity ""?"" '."
print ""
go
/*
** end
*/
页:
[1]