sybase相关tips

sybase相关tips和文摘

2006.04.20

SYBASE 序列异常跳转解决方法

发表人:feimei | 发表时间: 2006年四月19日, 21:50

identity burning set factor的问题
每次异常断电跳转的数目是:identity burning set factor*10的负7次方*表中identity列定义的长度,例如identity burning set factor=5000,identity列定义的长度numeric(10,0),则该数等于:5000*10的负7次方*1010的10次方,该数比较大

另外可以在建表时设置跳转的值,语法是 create table (...) with identity_gap = value. 修改当前表步长

EXEC sp_chgattribute 't_61e_tolltype','identity_gap',50
原文地址： http://blog.itpub.net/post/10248/70601

----

Managing identity gaps in tables

The IDENTITY column contains a unique ID number, generated by Adaptive Server, for each row in a table. Because of the way the server generates ID numbers by default, you may occasionally have large gaps in the ID numbers. The identity_gap parameter gives you control over ID numbers, and potential gaps in them, for a specific table.

By default, Adaptive Server allocates a block of ID numbers in memory instead of writing each ID number to disk as it is needed, which requires more processing time. The server writes the highest number of each block to the table's object allocation map (OAM) page. This number is used as the starting point for the next block after the currently allocated block of numbers is used or "burned". The other numbers of the block are held in memory, but are not saved to disk. Numbers are considered burned when they are allocated to memory, then deleted from memory either because they were assigned to a row, or because they were erased from memory due to some abnormal occurrence such as a system failure.

Allocating a block of ID numbers improves performance by reducing contention for the table. However, if the server fails or is shut down with no wait before all the ID numbers are assigned, the unused numbers are burned. When the server is running again, it starts numbering with the next block of numbers based on the highest number of the previous block that the server wrote to disk. Depending on how many allocated numbers were assigned to rows before the failure, you may have a large gap in the ID numbers.

Identity gaps can also result from dumping and loading an active database. When dumping, database objects are saved to the OAM page. If an object is currently being used, the 'maximum used identity value' is not be in the OAM page and, therefore, is not dumped.

Parameters for controlling identity gaps

Adaptive Server provides parameters that allow you to control gaps in identity numbers as described in Table 7-3.

Parameters for controlling identity gaps

Parameter Name	Scope	Used with	Description
identity_gap	table-specific	create table or select into	Creates ID number blocks of a specific size for a specific table. Overrides identity burning set factor for the table. Works with identity grab size.
identity burning set factor	server-wide	sp_configure	Indicates a percentage of the total available ID numbers you want to have allocated for each block. Works with identity grab size. If the identity_gap for a table is set to 1 or higher, identity burning set factor has no effect on that table. The burning set factor is used for all tables for which identity_gap is set to 0. When you set i dentity burning set factor, you express the number in decimal form, and then multiply it by 10,000,000 (107 ) to get the correct value to use with sp_configure. For example, to release 15 percent (.15) of the potential IDENTITY column values at one time, you specify a value of .15 times 107 (or 1,500,000): sp_configure "identity burning set factor", 1500000
identity grab size	server-wide	sp_configure	Reserves a block of contiguous ID numbers for each process. Works with identity burning set factor and identity_gap.

Comparison of identity burning set factor and identity_gap

The identity_gap parameter gives you control over the size of identity gaps for a particular table as illustrated in the following examples. In the examples, we have created a table named books to list all the books in our bookstore. We want each book to have a unique ID number, and we want Adaptive Server to automatically generate the ID numbers.

Example of using identity burning set factor

When defining the IDENTITY column for the books table, we used the default numeric value of (18, 0), which provides a total of 999,999,999,999,999,999 ID numbers. For the identity burning set factor configuration parameter, we are using the default setting of 5000 (.05 percent of 999,999,999,999,999,999), which means that Adaptive Server allocates blocks of 500,000,000,000,000 numbers.

The server allocates the first 500,000,000,000,000 numbers in memory and stores the highest number of the block (500,000,000,000,000) on the table's OAM page. When all the numbers are assigned to rows or burned, the server takes the next block of numbers (the next 500,000,000,000,000), starting with 500,000,000,000,001, and stores the number 1,000,000,000,000,000 as the highest number of the block.

Let's say, after row number 500,000,000,000,022 the server fails. Only numbers 1 through 500,000,000,000,022 were used as ID numbers for books. Numbers 500,000,000,000,023 through 1,000,000,000,000,000 are burned. When the server is running again, it creates ID numbers starting from the highest number stored on the table's OAM page plus one (1,000,000,000,000,001),which leaves a gap of 499,999,999,999,978 ID numbers.

To reduce this large gap in identity numbers for a specific table, you can set the identity gap as described in " Example of using identity_gap."

Example of using identity_gap

In this case, we create the books table with an identity_gap value of 1000. This overrides the server-wide identity burning set factor setting that resulted in blocks of 500,000,000,000,000 ID numbers. Instead, ID numbers are allocated in memory in blocks of 1000.

The server allocates the first 1000 numbers and stores the highest number of the block (1000) to disk. When all the numbers are used, the server takes the next 1000 numbers, starting with 1001, and stores the number 2000 as the highest number.

Now, let's say that after row number 1002 we lose power, which causes the server to fail. Only the numbers 1000 through 1002 were used, and numbers 1003 through 2000 are lost. When the server is running again, it creates ID numbers starting from the highest number stored on the table's OAM page plus one (2000), which leaves a gap of only 998 numbers.

You can significantly reduce the gap in ID numbers by setting the identity_gap for a table instead of using the server-wide table burning set factor. However, there may be a performance cost to setting this value too low. Each time the server must write the highest number of a block to disk, performance is affected. For example, if identity_gap is set to 1, which means you are allocating one ID number at a time, the server must write the new number every time a row is created, which may reduce performance because of page lock contention on the table. You must find the best setting to achieve the optimal performance with the lowest gap value acceptable for your situation.

Setting the table-specific identity gap

Set the table-specific identity gap when you create a table using either c reate table or select into.

Setting identity gap with create table

The syntax is:

create table table_name (column_name      datatype(constant_expression) identity)       with identity_gap = value 

For example:

create table mytable (IdNum numeric(12,0) identity) with identity_gap = 10

This statement creates a table named mytable with an identity column. The identity gap is set to 10, which means ID numbers will be allocated in memory in blocks of ten. If the server fails or is shut down with no wait, the maximum gap between the last ID number assigned to a row and the next ID number assigned to a row is ten numbers.

Setting identity gap with select into

If you are creating a table in a select into statement from a table that has a specific identity gap setting, the new table does not inherit the identity gap setting from the parent table. Instead, the new table uses the identity burning set factor setting. To give the new table a specific identity_gap setting, specify the identity gap in the select into statement. You can give the new table an identity gap that is the same as or different from the parent table.

For example, to create a new table ( newtable) from the existing table ( mytable) with an identity gap:

select identity into newtable  with identity_gap = 20 from mytable

Changing the table-specific identity gap

To change the identity gap for a specific table, use sp_chgattribute:

sp_chgattribute "table_name", "identity_gap", set_number

where:

• table_name is the name of the table for which you want to change the identity gap.
  
  
• identity_gap indicates that you want to change the identity gap.
  
  
• set_number is the new size of the identity gap.
  
  

For example:

sp_chgattribute "mytable", "identity_gap", 20

To change mytable to use the identity burning set factor setting instead of the identity_gap setting, set identity_gap to 0:

sp_chgattribute "mytable", "identity_gap", 0

Displaying table-specific identity gap information

To see the identity_gap setting for a table, use sp_help.

For example, the zero value in the identity_gap column (towards the end of the output) indicates that no table-specific identity gap is set. mytable uses the server-wide identity burning set factor value.

sp_help mytable

Name                           Owner      Type ------------------------------ ------------------------------     ---------------------- mytable                        dbo      user table  (1 row affected) Data_located_on_segment        When_created ------------------------------ -------------------------- default                               Jun  8 1999  5:35PM Column_name     Type            Length Prec Scale Nulls Default_name   

     Rule_name       Identity --------------- --------------- ------ ---- ----- ----- --------------- 

    --------------- -------- idnum           numeric              6   12     0     0 NULL      NULL                   1 Object does not have any indexes.

No defined keys for this object. Object is not partitioned. Lock scheme Allpages The attribute 'exp_row_size' is not applicable to tables with  allpages lock scheme. The attribute 'concurrency_opt_threshold' is not applicable to  tables with allpages lock scheme.  exp_row_size reservepagegap fillfactor max_rows_per_page identity_gap 

 ------------ -------------- ---------- ----------------- ------------ 

            1              0          0                 0            0 concurrency_opt_threshold ------------------------- 0

If you change the identity_gap of mytable to 20, the sp_help output for the table shows 20 in the identity_gap column. This setting overrides the server-wide identity burning set factor value.

sp_help mytable

Name                           Owner      Type ------------------------------ ------------------------------      ----------------------  mytable                        dbo       user table   (1 row affected) Data_located_on_segment        When_created   ------------------------------ --------------------------  default                               Jun  8 1999  5:35PM  Column_name     Type            Length Prec Scale Nulls Default_name       Rule_name       Identity  --------------- --------------- ------ ---- ----- ----- ---------------      --------------- --------  idnum           numeric              6   12     0     0 NULL       NULL                   1  Object does not have any indexes. No defined keys for this object. Object is not partitioned. Lock scheme Allpages The attribute 'exp_row_size' is not applicable to tables with  allpages lock scheme. The attribute 'concurrency_opt_threshold' is not applicable to  tables with allpages lock scheme.  exp_row_size reservepagegap fillfactor max_rows_per_page identity_gap ------------ -------------- ---------- ----------------- ------------ 1                         0          0                 0           20 concurrency_opt_threshold ------------------------- 0 

Gaps due to insertions, deletions, identity grab size, and rollbacks

Manual insertions into the IDENTITY column, deletion of rows, the value of the identity grab size configuration parameter, and transaction rollbacks can create gaps in IDENTITY column values. These gaps are not affected by the setting of the identity burning set factor configuration parameter.

For example, assume that you have an IDENTITY column with these values:

select syb_identity from stores_cal

 id_col    -------        1        2        3        4        5    (5 rows affected)

You can delete all rows for which the IDENTITY column falls between 2 and 4, leaving gaps in the column values:

delete stores_cal where syb_identity between 2 and 4

select syb_identity from stores_cal

 id_col    ------       1        5    (2 rows affected)

After setting identity_insert on for the table, the table owner, Database Owner, or System Administrator can manually insert any legal value greater than 5. For example, inserting a value of 55 would create a large gap in IDENTITY column values:

insert stores_cal  (syb_identity, stor_id, stor_name) values (55, "5025", "Good Reads")

select syb_identity from stores_cal

id_col    -------         1        5       55    (3 rows affected)

If identity_insert is then set to off, Adaptive Server assigns an IDENTITY column value of 55 + 1, or 56, for the next insertion. If the transaction that contains the insert statement is rolled back, Adaptive Server discards the value 56 and uses a value of 57 for the next insertion.

If table inserts reach the IDENTITY column's maximum value

The maximum number of rows you can insert into a table depends on the precision set for the IDENTITY column. If a table reaches that limit, you can either re-create the table with a larger precision or, if the table's IDENTITY column is not used for referential integrity, use the bcp utility to remove the gaps. See "Reaching the IDENTITY column's maximum value" for more information.

原文地址： http://manuals.sybase.com/onlinebooks/group-as/asg1250e/sqlug/@Generic__BookView