What Queries are Used by the sybase Probe?

Products

• DX Unified Infrastructure Management (Nimsoft / UIM)

Issue/Introduction

What Queries are Used by the sybase probe?

Environment

Sybase 12.x

Any sybase probe version

Resolution

      QueryVers Sybase12

"database_count", "SELECT convert(varchar(3),count(*)) FROM master.dbo.sysdatabases"

"server_startup", "SELECT convert(varchar(20),DATEDIFF(dd,crdate,getdate()))+':'+ \
      CONVERT(varchar(20),crdate,104)+':'+CONVERT(varchar(20),crdate,108) FROM master.dbo.sysdatabases where name like 'tempdb'"
"database_size",  "SELECT db_name(u.dbid) +':'+ case " 
      "when u.segmap = 4 then 'log' "
      "when (((u.segmap & 4)=4) and ((u.segmap&~4)!=0)) then 'mixed' "
      "else 'data' "
    "end +':'+ "
    "convert(varchar(25),round(sum((convert(decimal(15,4),u.size))*(select convert(int, low) from master.dbo.spt_values where number = 1 and type = 'E')/1048576),4))+':'+ "
    "convert(varchar(25),round(sum((convert(decimal(15,4),curunreservedpgs (dbid, u.lstart, unreservedpgs)))*(select convert(int, low) from master.dbo.spt_values where number = 1 and type = 'E')/1048576),4))  "
    "FROM    master.dbo.sysusages u "
    "GROUP BY db_name(u.dbid),  "
      "case  "
      "when u.segmap = 4 then 'log' "
      "when (((u.segmap & 4)=4) and ((u.segmap&~4)!=0)) then 'mixed' "
      "else 'data' "
      "end "
    "ORDER BY db_name(u.dbid)"

"login_count", "select convert(varchar(5),count(*))from master..sysprocesses p,master..sysdatabases d where p.dbid = d.dbid"
   " and ((d.status & 32 != 32) and (d.status & 256 != 256) and (d.status2 & 16 != 16) and (d.status2 & 32 != 32)"
   " and (d.status2 & 64 != 64) and (d.status2 & 128 != 128)) and p.suid  > 0"

"active_users", "select convert(varchar(5),count(*))from master..sysprocesses p, master..sysdatabases d where p.dbid = d.dbid"
   " and ((d.status & 32 != 32) and (d.status & 256 != 256) and (d.status2 & 16 != 16) and (d.status2 & 32 != 32)"
   " and (d.status2 & 64 != 64) and (d.status2 & 128 != 128)) and p.suid  > 0 and p.status = 'running'"

"locked_users","select convert(varchar(25),l.spid)+':'+db_name(l.dbid)+':'+"
     "convert(varchar(25),l.time_blocked)+':'+rtrim(l.status)+':'+rtrim(l.cmd)+':'+"
     "convert(varchar(25),b.spid)+':'+rtrim(b.status)+':'+rtrim(b.cmd)+':'+rtrim(l.hostprocess)+':'+rtrim(b.hostprocess) " 
   "from master..sysprocesses l, master..sysprocesses b "
   "where l.blocked = b.spid"

"log_size","select db_name(u.dbid)+':'+"  
   "convert(varchar(25),sum(u.size))+':'+"
   "convert(varchar(25),max(lct_admin('logsegment_freepages', d.dbid)))+':'+"
   "convert(varchar(25),max(lct_admin('reserved_for_rollbacks', d.dbid)))+':'+"
   "convert(varchar(25),max((select low from master.dbo.spt_values where number = 1 and type = 'E')))"
   "from master.dbo.sysusages u, master.dbo.sysdatabases d"
   " where ((d.status & 32 != 32) and (d.status & 256 != 256)"
   " and (d.status2 & 16 != 16) and (d.status2 & 32 != 32) and (d.status2 & 64 != 64)"
   " and (d.status2 & 128 != 128)) and d.dbid = u.dbid and  u.segmap in(4,7)"
   " group by u.dbid order by u.dbid"

"db_device_size","select distinct db_name(u.dbid)+':'+v.name+':'+"
   "case "
   " when u.segmap = 4 then 'log' "
   " when (((u.segmap & 4)=4) and ((u.segmap&~4)!=0)) then 'mixed' " 
   " else 'data' " 
   "end+':'+"
   "convert(varchar(25),round(sum((convert(decimal(15,4),u.size))*(select convert(int, low) from master.dbo.spt_values where number = 1 and type = 'E')/1048576),4))+':'+ "
   "convert(varchar(25),round(sum((convert(decimal(15,4),curunreservedpgs (u.dbid, u.lstart, unreservedpgs)))*(select convert(int, low) from master.dbo.spt_values where number = 1 and type = 'E')/1048576),4)) "
   "FROM  master.dbo.sysusages u , master.dbo.sysdevices v, master.dbo.sysdatabases d "
   "where u.vstart between v.low and v.high "
   "and d.dbid = u.dbid "
   "and v.status & 2 = 2 "
   "and cntrltype = 0 "
   "and status3 & 256 != 256 "
   "and u.dbid != 2 "
   "GROUP BY db_name(u.dbid), v.name, "
   "case  "
   " when u.segmap = 4 then 'log' "
   " when (((u.segmap & 4)=4) and ((u.segmap&~4)!=0)) then 'mixed' "
   " else 'data' "
   "end order by u.dbid, v.name" 

"tempdb_device_size","select db_name(u.dbid)+':'+v.name+':'+"
   "case "
   " when u.segmap = 4 then 'log' "
   " when (((u.segmap & 4)=4) and ((u.segmap&~4)!=0)) then 'mixed' " 
   " else 'data' " 
   "end+':'+"
   "convert(varchar(25),round(sum((convert(decimal(15,4),u.size))*(select convert(int, low) from master.dbo.spt_values where number = 1 and type = 'E')/1048576),4))+':'+ "
   "convert(varchar(25),round(sum((convert(decimal(15,4),curunreservedpgs (u.dbid, u.lstart, unreservedpgs)))*(select convert(int, low) from master.dbo.spt_values where number = 1 and type = 'E')/1048576),4)) "
   "FROM    master.dbo.sysusages u , master.dbo.sysdevices v, master.dbo.sysdatabases d "
   "where u.vstart between v.low and v.high "
   "and d.dbid = u.dbid "
   "and v.status & 2 = 2 "
   "and cntrltype = 0 "
   "and (d.status3 & 256 = 256 or u.dbid = 2) "
   "and u.segmap != 4 "
   "GROUP BY db_name(u.dbid), v.name, "
   "case  "
   " when u.segmap = 4 then 'log' "
   " when (((u.segmap & 4)=4) and ((u.segmap&~4)!=0)) then 'mixed' "
   " else 'data' "
   "end order by u.dbid, v.name" 

"device_size","select sd.name+':'+"
   "convert(varchar(25),round((convert(decimal(15,4),(1+sd.high-sd.low))*(select convert(int, low) from master.dbo.spt_values where number = 1 and type = 'E')/1048576),4))+':'+"
   "convert(varchar(25),round(sum(convert(decimal(15,4),isnull(su.size,0))*(select convert(int, low) from master.dbo.spt_values where number = 1 and type = 'E')/1048576),4))+':'+"
   "convert(varchar(25),round((convert(decimal(15,4),(1+sd.high-sd.low) - sum(isnull(su.size,0)))*(select convert(int, low) from master.dbo.spt_values where number = 1 and type = 'E')/1048576),4))"
   " from master.dbo.sysdevices sd left join master.dbo.sysusages su on su.vstart between sd.low and sd.high"
   " where  sd.cntrltype=0 and (sd.status&2)=2"
   " group by sd.name order by sd.name" 

"old_format_end","select count(*) from pubs2..sales"

"monitor_config","sp_monitorconfig 'all'"

"server_cpu","select datediff(ss, '1970-01-01 00:00:00', getdate()) time, @@cpu_busy  cpu_busy, @@timeticks timeticks, (select count(*) from master.dbo.sysengines where status = 'online') no_engines"

"server_io","select datediff(ss, '1970-01-01 00:00:00', getdate()) time, @@io_busy  io_busy, @@timeticks timeticks, (select count(*) from master.dbo.sysengines where status = 'online') no_engines"

"buf_cachehit_ratio", "select CacheName as object, LogicalReads, PhysicalReads from master.dbo.monDataCache"

"lock_requests", "select datediff(ss, '1970-01-01 00:00:00', getdate()) time, 'granted' as object, (sum(convert(dec(15,0),LockRequests)) - sum(convert(dec(15,0),LockWaits))) as locks from master.dbo.monOpenObjectActivity "
   "union select datediff(ss, '1970-01-01 00:00:00', getdate()) time, 'waited' as object, sum(convert(dec(15,0),LockWaits)) as locks from master.dbo.monOpenObjectActivity"

"lock_requests_db", "select datediff(ss, '1970-01-01 00:00:00', getdate()) time, DBID, db_name(DBID) as object, sum(convert(dec(15,0),LockRequests)) as locks from master.dbo.monOpenObjectActivity group by DBID"

"lock_requests_granted_db", "select datediff(ss, '1970-01-01 00:00:00', getdate()) time, DBID, db_name(DBID) as object, (sum(convert(dec(15,0),LockRequests)) - sum(convert(dec(15,0),LockWaits))) as granted from master.dbo.monOpenObjectActivity group by DBID"

"lock_requests_waited_db", "select datediff(ss, '1970-01-01 00:00:00', getdate()) time, DBID, db_name(DBID) as object, sum(convert(dec(15,0),LockWaits)) as waited from master.dbo.monOpenObjectActivity group by DBID"

"dead_locks", "select datediff(ss, '1970-01-01 00:00:00', getdate()) time, NumDeadlocks deadlocks from master.dbo.monState"

"total_disk_io", "select datediff(ss, '1970-01-01 00:00:00', getdate()) time, LogicalName object, Reads reads, Writes writes, IOTime iotime, (select count(*) from master.dbo.sysengines where status = 'online') no_engines from master.dbo.monDeviceIO"

"stp_cachehit_ratio", "select datediff(ss, '1970-01-01 00:00:00', getdate()) time, Requests, Loads from master.dbo.monProcedureCache"

"q_locked_users", "select l.spid sid, rtrim(l.hostprocess) hostpid, db_name(l.dbid) dbname, l.time_blocked value, rtrim(l.status) status, rtrim(l.cmd) cmd, "
     "b.spid b_sid, rtrim(b.hostprocess) b_hostpid, rtrim(b.status) b_status, rtrim(b.cmd) b_cmd, substring(t.SQLText,1,256) text "
   "from master..sysprocesses l, master..sysprocesses b, master.dbo.monProcessSQLText t where l.blocked = b.spid and t.SPID = l.spid"

"check_bsalive", "select srvnetname as bsname from master.dbo.sysservers where srvname = 'SYB_BACKUP'"

"active_connection_ratio","select cur.value2 as total, (select count(*) from master..sysprocesses where hostprocess <> '') as active from master..syscurconfigs cur, master..sysconfigures def where def.name='number of user connections' and def.config = cur.config"

 

 

      QueryVers Sybase15

"database_count", "SELECT convert(varchar(3),count(*)) FROM master.dbo.sysdatabases"

"server_startup", "SELECT convert(varchar(20),DATEDIFF(dd,crdate,getdate()))+':'+ \
      CONVERT(varchar(20),crdate,104)+':'+CONVERT(varchar(20),crdate,108) FROM master.dbo.sysdatabases where name like 'tempdb'"

"database_size",  "SELECT db_name(u.dbid) +':'+ case " 
      "when u.segmap = 4 then 'log' "
      "when (((u.segmap & 4)=4) and ((u.segmap&~4)!=0)) then 'mixed' "
      "else 'data' "
    "end +':'+ "
    "convert(varchar(25),round(sum((convert(decimal(15,4),u.size))*(select convert(int, low) from master.dbo.spt_values where number = 1 and type = 'E')/1048576),4))+':'+ "
    "convert(varchar(25),round(sum((convert(decimal(15,4),curunreservedpgs (dbid, u.lstart, unreservedpgs)))*(select convert(int, low) from master.dbo.spt_values where number = 1 and type = 'E')/1048576),4))  "
    "FROM    master.dbo.sysusages u "
    "GROUP BY db_name(u.dbid),  "
      "case  "
      "when u.segmap = 4 then 'log' "
      "when (((u.segmap & 4)=4) and ((u.segmap&~4)!=0)) then 'mixed' "
      "else 'data' "
      "end "
    "ORDER BY db_name(u.dbid)"

"login_count", "select convert(varchar(5),count(*))from master..sysprocesses p,master..sysdatabases d where p.dbid = d.dbid"
   " and ((d.status & 32 != 32) and (d.status & 256 != 256) and (d.status2 & 16 != 16) and (d.status2 & 32 != 32)"
   " and (d.status2 & 64 != 64) and (d.status2 & 128 != 128)) and p.suid  > 0"

"active_users", "select convert(varchar(5),count(*))from master..sysprocesses p, master..sysdatabases d where p.dbid = d.dbid"
   " and ((d.status & 32 != 32) and (d.status & 256 != 256) and (d.status2 & 16 != 16) and (d.status2 & 32 != 32)"
   " and (d.status2 & 64 != 64) and (d.status2 & 128 != 128)) and p.suid  > 0 and p.status = 'running'"

"locked_users","select convert(varchar(25),l.spid)+':'+db_name(l.dbid)+':'+"
     "convert(varchar(25),l.time_blocked)+':'+rtrim(l.status)+':'+rtrim(l.cmd)+':'+"
     "convert(varchar(25),b.spid)+':'+rtrim(b.status)+':'+rtrim(b.cmd)+':'+rtrim(l.hostprocess)+':'+rtrim(b.hostprocess) " 
   "from master..sysprocesses l, master..sysprocesses b "
   "where l.blocked = b.spid"

"log_size","select db_name(u.dbid)+':'+"  
   "convert(varchar(25),sum(u.size))+':'+"
   "convert(varchar(25),max(lct_admin('logsegment_freepages', d.dbid)))+':'+"
   "convert(varchar(25),max(lct_admin('reserved_for_rollbacks', d.dbid)))+':'+"
   "convert(varchar(25),max((select low from master.dbo.spt_values where number = 1 and type = 'E')))"
   "from master.dbo.sysusages u, master.dbo.sysdatabases d"
   " where ((d.status & 32 != 32) and (d.status & 256 != 256)"
   " and (d.status2 & 16 != 16) and (d.status2 & 32 != 32) and (d.status2 & 64 != 64)"
   " and (d.status2 & 128 != 128)) and d.dbid = u.dbid and  u.segmap in(4,7)"
   " group by u.dbid order by u.dbid"

"db_device_size","select distinct db_name(u.dbid)+':'+v.name+':'+"
   "case "
   " when u.segmap = 4 then 'log' "
   " when (((u.segmap & 4)=4) and ((u.segmap&~4)!=0)) then 'mixed' " 
   " else 'data' " 
   "end+':'+"
   "convert(varchar(25),round(sum((convert(decimal(15,4),u.size))*(select convert(int, low) from master.dbo.spt_values where number = 1 and type = 'E')/1048576),4))+':'+ "
   "convert(varchar(25),round(sum((convert(decimal(15,4),curunreservedpgs (u.dbid, u.lstart, unreservedpgs)))*(select convert(int, low) from master.dbo.spt_values where number = 1 and type = 'E')/1048576),4)) "
   "FROM  master.dbo.sysusages u , master.dbo.sysdevices v, master.dbo.sysdatabases d "
   "where u.vdevno = v.vdevno "
   "and d.dbid = u.dbid "
   "and v.status & 2 = 2 "
   "and cntrltype = 0 "
   "and status3 & 256 != 256 "
   "and u.dbid != 2 "
   "GROUP BY db_name(u.dbid), v.name, "
   "case  "
   " when u.segmap = 4 then 'log' "
   " when (((u.segmap & 4)=4) and ((u.segmap&~4)!=0)) then 'mixed' "
   " else 'data' "
   "end order by u.dbid, v.name" 

"tempdb_device_size","select db_name(u.dbid)+':'+v.name+':'+"
   "case "
   " when u.segmap = 4 then 'log' "
   " when (((u.segmap & 4)=4) and ((u.segmap&~4)!=0)) then 'mixed' " 
   " else 'data' " 
   "end+':'+"
   "convert(varchar(25),round(sum((convert(decimal(15,4),u.size))*(select convert(int, low) from master.dbo.spt_values where number = 1 and type = 'E')/1048576),4))+':'+ "
   "convert(varchar(25),round(sum((convert(decimal(15,4),curunreservedpgs (u.dbid, u.lstart, unreservedpgs)))*(select convert(int, low) from master.dbo.spt_values where number = 1 and type = 'E')/1048576),4)) "
   "FROM    master.dbo.sysusages u , master.dbo.sysdevices v, master.dbo.sysdatabases d "
   "where u.vdevno = v.vdevno "
   "and d.dbid = u.dbid "
   "and v.status & 2 = 2 "
   "and cntrltype = 0 "
   "and (d.status3 & 256 = 256 or u.dbid = 2) "
   "and u.segmap != 4 "
   "GROUP BY db_name(u.dbid), v.name, "
   "case  "
   " when u.segmap = 4 then 'log' "
   " when (((u.segmap & 4)=4) and ((u.segmap&~4)!=0)) then 'mixed' "
   " else 'data' "
   "end order by u.dbid, v.name"

"device_size","select sd.name+':'+"
   "convert(varchar(25),round((convert(decimal(15,4),(1+sd.high-sd.low))*(select convert(int, low) from master.dbo.spt_values where number = 1 and type = 'E')/1048576),4))+':'+"
   "convert(varchar(25),round(sum(convert(decimal(15,4),isnull(su.size,0))*(select convert(int, low) from master.dbo.spt_values where number = 1 and type = 'E')/1048576),4))+':'+"
   "convert(varchar(25),round((convert(decimal(15,4),(1+sd.high-sd.low) - sum(isnull(su.size,0)))*(select convert(int, low) from master.dbo.spt_values where number = 1 and type = 'E')/1048576),4))"
   " from master.dbo.sysdevices sd, master.dbo.sysusages su where "  
    " sd.vdevno = su.vdevno "
   " and sd.cntrltype=0 and (sd.status&2)=2"
   " group by sd.name order by sd.name" 

"old_format_end", "select count(*) from pubs2..sales"

"monitor_config", "sp_monitorconfig 'all'"

"server_cpu","select datediff(ss, '1970-01-01 00:00:00', getdate()) time, @@cpu_busy  cpu_busy, @@timeticks timeticks, (select count(*) from master.dbo.sysengines where status = 'online') no_engines"

"server_io","select datediff(ss, '1970-01-01 00:00:00', getdate()) time, @@io_busy  io_busy, @@timeticks timeticks, (select count(*) from master.dbo.sysengines where status = 'online') no_engines"

"buf_cachehit_ratio", "select CacheName as object, LogicalReads, PhysicalReads from master.dbo.monDataCache"

"lock_requests", "select datediff(ss, '1970-01-01 00:00:00', getdate()) time, 'granted' as object, (sum(convert(dec(15,0),LockRequests)) - sum(convert(dec(15,0),LockWaits))) as locks from master.dbo.monOpenObjectActivity "
   "union select datediff(ss, '1970-01-01 00:00:00', getdate()) time, 'waited' as object, sum(convert(dec(15,0),LockWaits)) as locks from master.dbo.monOpenObjectActivity"

"lock_requests_db", "select datediff(ss, '1970-01-01 00:00:00', getdate()) time, DBID, db_name(DBID) as object, sum(convert(dec(15,0),LockRequests)) as locks from master.dbo.monOpenObjectActivity group by DBID"

"lock_requests_granted_db", "select datediff(ss, '1970-01-01 00:00:00', getdate()) time, DBID, db_name(DBID) as object, (sum(convert(dec(15,0),LockRequests)) - sum(convert(dec(15,0),LockWaits))) as granted from master.dbo.monOpenObjectActivity group by DBID"

"lock_requests_waited_db", "select datediff(ss, '1970-01-01 00:00:00', getdate()) time, DBID, db_name(DBID) as object, sum(convert(dec(15,0),LockWaits)) as waited from master.dbo.monOpenObjectActivity group by DBID"

"dead_locks", "select datediff(ss, '1970-01-01 00:00:00', getdate()) time, NumDeadlocks deadlocks from master.dbo.monState"

"total_disk_io", "select datediff(ss, '1970-01-01 00:00:00', getdate()) time, LogicalName object, Reads reads, Writes writes, IOTime iotime, (select count(*) from master.dbo.sysengines where status = 'online') no_engines from master.dbo.monDeviceIO"

"stp_cachehit_ratio", "select datediff(ss, '1970-01-01 00:00:00', getdate()) time, Requests, Loads from master.dbo.monProcedureCache"

"q_locked_users", "select l.spid sid, rtrim(l.hostprocess) hostpid, db_name(l.dbid) dbname, l.time_blocked value, rtrim(l.status) status, rtrim(l.cmd) cmd, "
     "b.spid b_sid, rtrim(b.hostprocess) b_hostpid, rtrim(b.status) b_status, rtrim(b.cmd) b_cmd, substring(t.SQLText,1,256) text "
   "from master..sysprocesses l, master..sysprocesses b, master.dbo.monProcessSQLText t where l.blocked = b.spid and t.SPID = l.spid"

"check_bsalive", "select srvnetname as bsname from master.dbo.sysservers where srvname = 'SYB_BACKUP'"

"active_connection_ratio","select cur.value2 as total, (select count(*) from master..sysprocesses where hostprocess <> '') as active from master..syscurconfigs cur, master..sysconfigures def where def.name='number of user connections' and def.config = cur.config"

 

Additional Information

The following checkpoints are not  directly using queries to check them, but are generated from Sybase API Requests programatically.

 

       "buf_cachehit_ratio"
      "lock_requests_db"
      "lock_requests_granted_db"
      "lock_requests_waited_db"
      "lock_requests"
      "page_locks"
      "table_locks"
      "dead_locks"
      "total_disk_io"
      "total_disk_reads"
      "total_disk_writes"
      "commited_transactions"
      "stp_cachehit_ratio"
      "log_contention"
      "total_memory"
      "buffer_memory"
      "stp_memory"
      "check_msalive"
      "device_io"