AdvisorAdvisorimport groovy.sql.Sql
Class.forName("com.sybase.jdbc4.jdbc.SybDriver")
// Get basic info to connect
def hostname = hostProps.get("system.hostname")
def user = hostProps.get("sybase.user")
def pass = hostProps.get("sybase.pass")
def port = 21000
def wildvalue = instanceProps.get("wildvalue")
// Construct an SQL instance with a URL and a driver
def url = "jdbc:sybase:Tds:${hostname}:${port}"
def sql = Sql.newInstance(url, user, pass, "com.sybase.jdbc4.jdbc.SybDriver")
try {
// Drop the table WNLsegments_logsegment if it exists
sql.execute("if exists (select 1 from tempdb..sysobjects where name = 'WNLsegments_logsegment') drop table tempdb..WNLsegments_logsegment")
// sql.execute("if exists (select 1 from tempdb..sysobjects where name = 'WNLdblist_logsegment') drop table tempdb..WNLdblist_logsegment")
def query1 = "select dbid, name as dbname, (case when status2 & 32768 = 32768 then 'MIX' else 'SEP' end) as logtype into tempdb..WNLdblist_logsegment from master..sysdatabases where status2 & 16 <> 16"
sql.execute(query1)
def query = """
set nocount on
declare @dbid int,
@maxdbid int,
@dbname varchar(50),
@logtype char(3)
select @dbid=min(dbid),@maxdbid=max(dbid) from tempdb..WNLdblist_logsegment
select @dbname=dbname,@logtype = logtype from tempdb..WNLdblist_logsegment where dbid=@dbid
while @dbid <= @maxdbid
begin
select @dbname=dbname,@logtype = logtype from tempdb..WNLdblist_logsegment where dbid=@dbid
use @dbname
declare @dbsize integer
select db_name() dbname, s.name segname, s.segment, u.segmap,u.size, curunreservedpgs(db_id(), u.lstart, u.unreservedpgs) unreservedpgs
into tempdb..WNLsegments_logsegment
from master..sysusages u, syssegments s
where u.dbid=db_id() and power(2,s.segment) = u.segmap & power(2,s.segment)
order by s.segment
declare @pgsize integer
select @pgsize=low/1024 from master.dbo.spt_values where number = 1 and type = 'E'
select @dbsize=((sum(size)*@pgsize)/1024) from master..sysusages where dbid=@dbid
select top 1 db_id(dbname),dbname as DBNAME,
convert(integer,(sum(size)*@pgsize)/1024) as TOT_SEG_SIZE_MB,
convert(integer,((sum(size)-sum(unreservedpgs))*@pgsize)/1024) as SEG_USED_MB,
convert(integer,(sum(unreservedpgs)*@pgsize)/1024) as SEG_FREE_MB,
convert(integer,100*(sum(size)-sum(unreservedpgs))/sum(size)) as PERCENT_USED_SEG_MB
from tempdb..WNLsegments_logsegment
where segname='logsegment'
group by dbname,segment
order by dbname,segment
set @dbid=@dbid+1
end
"""
def resultSet = sql.rows(query)
// Process the resultSet (e.g., print or do something with the data)
resultSet.each { row ->
//println("DBID: ${row.DBID}")
// println("SYB_INSTANCE: ${row.SYB_INSTANCE}")
println("DBNAME: ${row.DBNAME}")
// println("SEGMENT_NAME: ${row.SEGMENT_NAME}")
// println("LOGTYPE: ${row.LOGTYPE}")
println("TOT_SEG_SIZE_MB: ${row.TOT_SEG_SIZE_MB}")
println("SEG_USED_MB: ${row.SEG_USED_MB}")
println("SEG_FREE_MB: ${row.SEG_FREE_MB}")
println("PERCENT_USED_SEG_MB: ${row.PERCENT_USED_SEG_MB}")
println()
}
} catch (Exception e) {
// Handle any exceptions that occurred during execution
println("An error occurred: ${e.message}")
e.printStackTrace()
}
// Drop the table WNLsegments_logsegment if it exists
sql.execute("if exists (select 1 from tempdb..sysobjects where name = 'WNLsegments_logsegment') drop table tempdb..WNLsegments_logsegment")
// Drop the table WNLdblist_logsegment if it exists
sql.execute("if exists (select 1 from tempdb..sysobjects where name = 'WNLdblist_logsegment') drop table tempdb..WNLdblist_logsegment")
// Ensure the SQL connection is closed regardless of success or failure
sql.close()