AdvisorAdvisorcollection script
import groovy.sql.Sql
//@Grab('org.hsqldb:hsqldb:2.7.1:jdk8')
//@GrabConfig(systemClassLoader=true)
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 databaseName = "${wildvalue}"
// Construct an SQL instance with a url and a driver
def url = "jdbc:sybase:Tds:${hostname}:${port}", username=user, password=pass
// Sybase SQL Driver
def driver = "com.sybase.jdbc4.jdbc.SybDriver"
// Create a connection to the SQL server
def sql = Sql.newInstance(url, user, pass, driver)
def wildvalue = instanceProps.get("wildvalue")
// Set the @showdev and @alloc variables
def showdev = true
def alloc = sql.firstRow("select low from master.dbo.spt_values where type='E' and number=1").low
// Execute the query
//def result = sql.rows("select s.name Segment, convert(numeric,sum((size*$alloc)/1048576)) 'size in MB', convert(numeric,sum((size*$alloc)/1048576))-convert(numeric,sum((curunreservedpgs(db_id('${wildvalue}'),lstart, unreservedpgs)* $alloc)/1048576)) 'Used in MB', convert(numeric,sum((curunreservedpgs(db_id('${wildvalue}'),lstart, unreservedpgs)* $alloc)/1048576)) 'Free in MB' from master..sysusages u, syssegments s where u.dbid=db_id('${wildvalue}') and (segmap & power(2, segment))=power(2,segment) group by s.name")
def result = sql.rows("select s.name Segment, cast(sum((size*$alloc)/1048576) as decimal(38,10)) 'size in MB', cast(sum((size*$alloc)/1048576)-sum((curunreservedpgs(db_id('${wildvalue}'),lstart, unreservedpgs)* $alloc)/1048576) as decimal(38,10)) 'Used in MB', cast(sum((curunreservedpgs(db_id('${wildvalue}'),lstart, unreservedpgs)* $alloc)/1048576) as decimal(38,10)) 'Free in MB' from master..sysusages u, syssegments s where u.dbid=db_id('${wildvalue}') and (segmap & power(2, segment))=power(2,segment) group by s.name")
// Print the results
result.each { row ->
println "Segment= ${row.Segment}\nsize in MB= ${row.'size in MB'}\nUsed in MB= ${row.'Used in MB'}\nFree in MB= ${row.'Free in MB'}"
}
// Close the connection
sql.close()
