SAP Sybase ASE 15.7 sp_thresholdaction Example Stored Procedure




Output of Example sp_thresholdaction Stored Procedure


Printed output to the error log:
00:0006:00000:00031:2016/11/17 12:24:28.18 server  background task message: sybsystemprocs..sp_thresholdaction stored procedure output:
00:0006:00000:00031:2016/11/17 12:24:28.18 server  background task message: @dbname=tempdb20 @segmentname=logsegment @space_left=3304(logical pages) @status=1
00:0006:00000:00031:2016/11/17 12:24:28.18 server  background task message: (status 1 indicates the last-chance threshold has been reached)
00:0006:00000:00031:2016/11/17 12:24:28.18 server  background task message: LOG THRESHOLD REPORT FOR DATABASE tempdb20
00:0006:00000:00031:2016/11/17 12:24:28.19 server  background task message: dbname=tempdb20 spid=26 login=fbloggs SQLText=insert junk20 select * from raw_test..junk cmd=LOG SUSPEND
00:0006:00000:00031:2016/11/17 12:24:28.19 server  background task message: dbname=tempdb20 spid=33 login=psmith SQLText=insert junk select * from raw_test..junk cmd=LOG SUSPEND
00:0006:00000:00031:2016/11/17 12:24:28.19 server  background task message: END OF LOG THRESHOLD REPORT FOR DATABASE tempdb20

The stored procedure below shows the output of a query on tables master master..monProcessSQLText and master..sysprocesses for the database in which the threshold was reached along with the values of the parameters.

By using a cursor it is possible to print multiple rows from the query below into the dataserver error log.


sp_thresholdaction Stored Procedure Code


use sybsystemprocs
go
create procedure sp_thresholdaction (@dbname      varchar(30),
                                     @segmentname varchar(30),
                                     @space_left  int,
                                     @status      int)
as

-- Author: Alastair Cook, 16th Nov 2016
declare @cparms_message varchar(100)
declare @output_message varchar(300)
declare @cspace_left    varchar(10)
declare @cstatus        char(1)

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

print 'sybsystemprocs..sp_thresholdaction stored procedure output:'
print @cparms_message
print '(status 1 indicates the last-chance threshold has been reached)'
print 'LOG THRESHOLD REPORT FOR DATABASE %1!' ,  @dbname


set role mon_role on

/* declare a cursor */
declare curs cursor for
select "dbname=" + db_name(mp.dbid) + " spid=" + convert( varchar(3), mt.SPID)
+ " login=" + suser_name(mt.ServerUserID) + " SQLText=" + mt.SQLText + " cmd=" + mp.cmd
from master..monProcessSQLText mt, master..sysprocesses mp
where mt.SPID = mp.spid
and @dbname = db_name(mp.dbid)

/* open the cursor */
open curs

/* fetch the first row */
fetch curs into @output_message

/* now loop, processing all the rows
** @@sqlstatus = 0 means successful fetch
** @@sqlstatus = 1 means error on previous fetch
** @@sqlstatus = 2 means end of result set reached
*/
while (@@sqlstatus != 2)
begin
    /* check for errors */
    if (@@sqlstatus = 1)
    begin
        print "Error in result"
        return
    end

    print @output_message
    fetch curs into @output_message
end

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



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