SAP Sybase ASE 15.7 Data Threshold Stored Procedure Example




Example of Setting up Data Thresholds


This is a worked example of setting up data thresholds for SAP sybase databases.

Steps:

Calculate the number of free pages remaining for the threshold procedure to execute.

In the example below the threshold is set up to call the threshold procedure when there is 30% data free in the database.

Create a stored procedure to put into sybsystemprocs, example below.

Create a threshold in the database.

Test the threshold output to the error log with a test database.

Output to error log as a result of the setup and test of data threshold:

00:0006:00000:00001:2016/11/17 14:59:42.02 server  background task message: sybsystemprocs..sp_threshold_reached stored procedure output:
00:0006:00000:00001:2016/11/17 14:59:42.02 server  background task message: @dbname=raw_test @segmentname=default @space_left=38400(logical pages)
00:0006:00000:00001:2016/11/17 14:59:42.02 server  background task message: THRESHOLD REPORT FOR DATABASE raw_test
00:0006:00000:00001:2016/11/17 14:59:42.02 server  background task message: raw_test        |Data_Total_MB=500      |Free_MB=147.1         |Used_pct=70.56    |Free_Pages=37677         |30_pct_Free_Pages_calc_threshold=38400
00:0006:00000:00001:2016/11/17 14:59:42.02 server  background task message: END OF THRESHOLD REPORT FOR DATABASE raw_test


Output of query for threshold value calculation

 AL2016          |Data_Total_MB=100      |Free_MB=96.3          |Used=3.68%|Free_Pages=24656         |30%_Free_Pages_calc_threshold=7680                                                                                  
 MOVE_TEST       |Data_Total_MB=5        |Free_MB=1.6           |Used=66.32%|Free_Pages=431           |30%_Free_Pages_calc_threshold=384                                                                                  
 TEMPDB_2        |Data_Total_MB=50       |Free_MB=46.4          |Used=7.04%|Free_Pages=11898         |30%_Free_Pages_calc_threshold=3840                                                                                  
 altest_new      |Data_Total_MB=4        |Free_MB=0.6           |Used=83.59%|Free_Pages=168           |30%_Free_Pages_calc_threshold=308                                                                                  
 dbccdb          |Data_Total_MB=100      |Free_MB=69.5          |Used=30.40%|Free_Pages=17816         |30%_Free_Pages_calc_threshold=7680                                                                                 
 master          |Data_Total_MB=28       |Free_MB=15.2          |Used=45.49%|Free_Pages=3907          |30%_Free_Pages_calc_threshold=2151                                                                                 
 model           |Data_Total_MB=6        |Free_MB=2.6           |Used=55.85%|Free_Pages=678           |30%_Free_Pages_calc_threshold=461                                                                                  
 raw_test        |Data_Total_MB=500      |Free_MB=279.1         |Used=44.16%|Free_Pages=71472         |30%_Free_Pages_calc_threshold=38400                                                                                
 script_test     |Data_Total_MB=20       |Free_MB=12.9          |Used=35.39%|Free_Pages=3308          |30%_Free_Pages_calc_threshold=1536                                                                                 
 second_disk_fs  |Data_Total_MB=500      |Free_MB=364.8         |Used=27.03%|Free_Pages=93395         |30%_Free_Pages_calc_threshold=38400                                                                                
 sybmgmtdb       |Data_Total_MB=75       |Free_MB=49.7          |Used=33.70%|Free_Pages=12728         |30%_Free_Pages_calc_threshold=5760                                                                                 
 sybsecurity     |Data_Total_MB=300      |Free_MB=293.5         |Used=2.15%|Free_Pages=75148         |30%_Free_Pages_calc_threshold=23040                                                                                 
 sybsystemdb     |Data_Total_MB=12       |Free_MB=8.4           |Used=29.94%|Free_Pages=2152          |30%_Free_Pages_calc_threshold=922                                                                                  
 sybsystemprocs  |Data_Total_MB=172      |Free_MB=9.7           |Used=94.31%|Free_Pages=2505          |30%_Free_Pages_calc_threshold=13210                                                                                
 tempdb          |Data_Total_MB=906      |Free_MB=803.7         |Used=11.28%|Free_Pages=205771        |30%_Free_Pages_calc_threshold=69581                                                                                
 tempdb20        |Data_Total_MB=900      |Free_MB=166.8         |Used=81.46%|Free_Pages=42703         |30%_Free_Pages_calc_threshold=69120                                                                                


Query used for above output:
select convert(char(16), db_name(d.dbid))
+ "|Data_Total_MB=" + convert(char(9), ceiling (sum(case when u.segmap != 4 then u.size/1048576.*@@maxpagesize end )))
+ "|Free_MB=" + convert(char(14), (convert(numeric(8,1),
           ((sum(case when u.segmap != 4 then u.size/1048576.*@@maxpagesize end ))) - (sum(case when u.segmap != 4 then size
           - curunreservedpgs(u.dbid, u.lstart, u.unreservedpgs) end)/1048576.*@@maxpagesize))))
+ "|Used=" + rtrim(convert(char(9), (convert(numeric(12,2),
           100 * (1 - 1.0 * sum(case when u.segmap != 4 then curunreservedpgs(u.dbid, u.lstart, u.unreservedpgs) end) / sum(case when u.segmap != 4 then u.size end))))))
+ "%"
+ "|Free_Pages=" + convert(char(14), (convert(numeric(8,0),
           ((sum(case when u.segmap != 4 then u.size/1.0 end ))) - (sum(case when u.segmap != 4 then size
           - curunreservedpgs(u.dbid, u.lstart, u.unreservedpgs) end)/1.0))))

+ "|30%_Free_Pages_calc_threshold=" + convert(char(9), ceiling(0.30 * (sum(case when u.segmap != 4 then u.size/1.0 end ))))
from master..sysdatabases d, master..sysusages u
where u.dbid = d.dbid  and d.status not in (256,4096)
group by d.dbid
order by db_name(d.dbid)

Creating a threshold stored procedure


For the test this stored procedure was used:
use sybsystemprocs
go
create procedure sp_threshold_reached (@dbname      varchar(30),
                                       @segmentname varchar(30),
                                       @space_left  int,
                                       @status      int)
as

-- Author: Alastair Cook, 16th Nov 2016
-- @status not used for this procedure
declare @cparms_message varchar(100)
declare @output_message varchar(300)
declare @cspace_left    varchar(10)

select @cspace_left = convert(varchar(10), @space_left)
select @cparms_message="@dbname=" + @dbname + " @segmentname=" + @segmentname + " @space_left=" + @cspace_left + "(logical pages) "

print 'sybsystemprocs..sp_threshold_reached stored procedure output:'
print @cparms_message
print 'THRESHOLD REPORT FOR DATABASE %1!' ,  @dbname

select @output_message=convert(char(16), db_name(d.dbid))
+ "|Data_Total_MB=" + convert(char(9), ceiling (sum(case when u.segmap != 4 then u.size/1048576.*@@maxpagesize end )))
+ "|Free_MB=" + convert(char(14), (convert(numeric(8,1),
           ((sum(case when u.segmap != 4 then u.size/1048576.*@@maxpagesize end ))) - (sum(case when u.segmap != 4 then size
           - curunreservedpgs(u.dbid, u.lstart, u.unreservedpgs) end)/1048576.*@@maxpagesize))))
+ "|Used_pct=" + rtrim(convert(char(9), (convert(numeric(12,2),
           100 * (1 - 1.0 * sum(case when u.segmap != 4 then curunreservedpgs(u.dbid, u.lstart, u.unreservedpgs) end) / sum(case when u.segmap != 4 then u.size end))))))
+ " "
+ "   |Free_Pages=" + convert(char(14), (convert(numeric(8,0),
           ((sum(case when u.segmap != 4 then u.size/1.0 end ))) - (sum(case when u.segmap != 4 then size
           - curunreservedpgs(u.dbid, u.lstart, u.unreservedpgs) end)/1.0))))

+ "|30_pct_Free_Pages_calc_threshold=" + convert(char(9), ceiling(0.30 * (sum(case when u.segmap != 4 then u.size/1.0 end ))))
from master..sysdatabases d, master..sysusages u
where u.dbid = d.dbid  and d.status not in (256,4096)
group by d.dbid
having db_name(d.dbid) = @dbname

print @output_message
print 'END OF THRESHOLD REPORT FOR DATABASE %1!' ,  @dbname
go


Setting up the threshold in a database


Thresholds are set up as required within each database:
1> use raw_test
2> go
1> sp_helpsegment
2> go
 segment        name                 status
 -------------- -------------------- ------------
       0        system                    0
       1        default                   1
       2        logsegment                0
(return status = 0)

 raw_test        |Data_Total_MB=500      |Free_MB=279.1         |Used=44.16%|Free_Pages=71472         |30%_Free_Pages_calc_threshold=38400
The segment that will be used is the default segment.

The value of 384000 pages is taken from the list above.


syntax: sp_addthreshold dbname, segment_name, no_of_pages, sp_threshold_reached

sp_addthreshold raw_test, "default", 38400, sp_threshold_reached
1> sp_addthreshold raw_test, "default", 38400, sp_threshold_reached
2>
3> go
Adding threshold for segment 'default' at '38400' pages.
DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.
(return status = 0)

1> sp_helpthreshold
2> go
 segment name                 free pages               last chance?                 threshold procedure
 ---------------------------- ------------------------ ---------------------------- ------------------------------------------
 default                             26880                          0               sp_threshold_reached
 logsegment                           4408                          1               sp_thresholdaction

(2 rows affected)
(return status = 0)



Testing the threshold

First test at the command line:

1> sp_threshold_reached raw_test, "default", 38400, 0
2> go
sybsystemprocs..sp_threshold_reached stored procedure output:
@dbname=raw_test @segmentname=default @space_left=38400(logical pages)
THRESHOLD REPORT FOR DATABASE raw_test
raw_test        |Data_Total_MB=500      |Free_MB=179.4         |Used_pct=64.10    |Free_Pages=45949         |30_pct_Free_Pages_calc_threshold=38400
END OF THRESHOLD REPORT FOR DATABASE raw_test
(return status = 0)


Then test filling up a test database (printed output from error log):
00:0006:00000:00001:2016/11/17 14:59:42.02 server  background task message: sybsystemprocs..sp_threshold_reached stored procedure output:
00:0006:00000:00001:2016/11/17 14:59:42.02 server  background task message: @dbname=raw_test @segmentname=default @space_left=38400(logical pages)
00:0006:00000:00001:2016/11/17 14:59:42.02 server  background task message: THRESHOLD REPORT FOR DATABASE raw_test
00:0006:00000:00001:2016/11/17 14:59:42.02 server  background task message: raw_test        |Data_Total_MB=500      |Free_MB=147.1         |Used_pct=70.56    |Free_Pages=37677         |30_pct_Free_Pages_calc_threshold=38400
00:0006:00000:00001:2016/11/17 14:59:42.02 server  background task message: END OF THRESHOLD REPORT FOR DATABASE raw_test

Example of Dropping a Threshold

To drop an existing threshold the command has specify database, segment and no of pages syntax: sp_dropthreshold dbname, segment_name, no_of_pages
1> sp_dropthreshold raw_test, "default", 26880
2> go
Dropping threshold for segment 'default' at '26880' pages.
DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.
(return status = 0)

User defined segments


The example above does not cater for user defined segments ie segments created with sp_addsegment.

The assumption is made that all data in the example user database is on the "default" segment.

Disclaimer

No liability can be accepted for any loss due to any information found on this page.





Push Symbol. Push Symbol. Door logos



Alastair Cook email:
cookalastair @ btinternet.com

Page created - 16th Nov 2016


Push Pull Door