SAP ASE 16 Learning Part 9 Enabling Auto Expansion in Sybase ASE 16. To Learn or Teach Linux visit www.theskillpedia.com, A Marketplace for Learners and Trainers.
Session 9 Auto Expansion
Ram N Sangwan
Auto Expand Feature
We can configure databases to expand automatically when they run out of space.
This automatic expansion process runs as a background task and generates informational messages in the server's error log.
By default Sybase does not have this turned on, as a matter of fact, you have to run a script that is shipped with the product in order to create the stored procedures that will make it possible for you to use this feature.
Auto Expand Contd..
Use following steps to set up a database for automatic expansion.
Please log in with sa_role permission.
Install automatic database expansion procedures.
Installdbextend script installs the family of threshold action procedures and sp_dbextend in the sybsystemprocs database.
Expanding Databases Automatically
You can configure databases to expand automatically when they run out of space.
The stored procedure sp_dbextend allows you to install thresholds that identify those devices with available space, and then appropriately alter the database—and the segment where the threshold was fired—on these devices.
Automatic Expansion – How it Works
After you set up a database for automatic expansion, internal mechanisms fire when a database grows to its free space threshold, and increase the size of the database by the amount of space your expansion policies specify.
The automatic expansion process measures the amount of room left on all devices bound to the database.
If there is sufficient room on the devices, the database continues to grow.
By default, if the size of the device is greater than 40MB, the size of the database is increased by 10 percent.
How it Works
If your database is smaller than 40MB, the size of the database is increased by 4MB.
However, you can specify database resizing limits that match the needs of your site.
If any devices are configured for expansion, those devices expand next.
Finally, the database is expanded on those devices.
Validating current thresholds
sp_dbextend includes a powerful simulation mode that any user with sa_role permission can use to simulate the execution of the top-level threshold action procedure
Defining Expansion Policies
To define expansion policies for the logsegment in the pubs2 database:
sp_dbextend ’set’, ’database’, pubs2, logsegment,’3M’
sp_dbextend ’set’, ’threshold’, pubs2, logsegment, ’1M’
To simulate expansion for these policies:
sp_dbextend ’simulate’, pubs2, logsegment
The following examples show the series of database and disk expansions that would occur if the threshold on database pubs2 segment logsegment fired once:
sp_dbextend 'simulate', pubs2, logsegment
NO REAL WORK WILL BE DONE.
To actually expand the database manually for this threshold, issue:
sp_dbextend 'execute', 'pubs2','logsegment', '1'
To expand the database manually for this threshold, execute:
sp_dbextend ’execute’, ’pubs2’, ’logsegment’