Case study 12: Changing locking scheme for one table or for all tables in database
Platform: HP-UNIX 11, Tru64, Windows
Sybase ASE version: 12.5.x
Background story: One useful modification of script for listing all tables in database is script for switching locking scheme for one table or for all tables in database.
Task: Write a script which locks all tables in database to some of possible locking schemes.
Solution: All necesary data for this script can be found in sysobjects table in selected database. So, SQL script for locking all tables in database to desired locking level is given below:
select 'alter table ' + name + ' lock datarows' from sysobjects where type = 'U' order by name
select 'alter table ' + name + ' lock datapages' from sysobjects where type = 'U' order by name
select 'alter table ' + name + ' lock allpages' from sysobjects where type = 'U' order by name
First script makes a script for changing locking scheme to datarows, second to datapages and third to allpages.
User which runs this script must be the owner of tables for which locking schemes switching is issued.
Remark: More about Sybase locking limitations can be found on this site.
