|Last updated: 03 November 2003
Updates for 'Tips, Tricks & Recipes for Sybase ASE' - 1st edition
This page contains additions and updates for "Tips, Tricks & Recipes for Sybase ASE".
On 13 October 2003, ASE 12.5.1 was released. The following sections of "Tips, Tricks & Recipes for Sybase ASE" are affected by new or changed features in 12.5.1:
- page 7: 'MR' in ASE version string
While ASE 12.5.1 is a maintenance release according to Sybase's own definition, 12.5.1 does not appear to have 'MR' in its version string although such a convention was described earlier in a number of places. I guess this corresponds with various bits of text on page 9 about changing definitions of version-related terminology ...
- page 201-202: Changes to the newid() function
The newid() function has been significantly improved in 12.5.1. Most importantly, newid() now always generates a unique value for each row in a result set. This means that the tricks described on pages 201-202 where a parameter (like ascii(EmpName) on page 201) is passed to newid() to force it to generating unique values, have become redundant in 12.5.1.
The following query on page 201 now produces unique UUID values in 12.5.1, without the need for any further tricks:
1> select newid(1) GUID, EmpName from Employees
Also, when newid() is used in a column default (as shown on page 202), multi-row insert-select statements without a value for that column, will get unique UUID in 12.5.1.
In fact, the tricks described on pages 201-202 don't even work in anymore in 12.5.1: newid() now only accepts 0, 1 and 0x0 as parameters. Any other parameter value produces a NULL result. The tricks on pages 201-202 worked fine in 220.127.116.11, where there was no such restriction on the parameter values.
When you have used the abovementioned tricks with newid() in 18.104.22.168 and you're migrating to 12.5.1, you should review all T-SQL code calling the newid() function, and remove the tricks described on pages 201-202.
- page 275: New feature for resetting the identity counter
In 12.5.1, the identity counter can be reset to any value using the following new command:
sp_chgattribute table_name, 'identity_burn_max', 0,
This command sets the identity counter to the specified new value. To avoid interference by user access, sp_chgattribute first takes out an exclusive-table lock on the table.
While this command finally makes it easy to fix identity gaps, note the following: sp_chgattribute actually checks whether there is a row in the table with a higher identity column value than the new value for the identify counter. When such a higher value exists (which is likely when you're repairing an identity gap), sp_chgattribute refuses to change the identity counter in order to protect you against the risk of generating duplicate identity column value at some point in the future.
Should this check-for-a-higher-value be a problem, it can be bypassed by running the following dbcc command directly:
- page 297: New feature for automatically expanding full databases
As mentioned on page 297, ASE 12.5.1 contains a new feature to allow automatic expansion of a full database. This feature is managed with the new stored procedure sp_dbextend (which must be installed separately, by running the script installdbextend in the scripts subdirectory of the ASE installation tree).
See the 12.5.1 documentation for further details about this feature.
page 356: Traceflag 1212
As described, traceflag 1212 displays all lock requests. This can result in a large amount of output even for simple queries, since many lock requests are related to system tables. Traceflag 1217 may also be useful here: it is similar to traceflag 1212, but displays only lock requests for user tables.