Invalid column 'LastTranLogEndDumpTime' in AllDatabasesList on ASEmon v.3.2.3

[elovelo]

Hello everyone. Recently I deployed ASEmon v.3.2.3 to monitor my ASE instances in version 16.0 SP02 PL07. The archive server is also in the same version.

I have the archive server setup with asemon db, as well as the asemon logger. I can view metrics ok, but the problem is with the option AllDatabasesList. If I click on it I get:

ERROR
Module : refreshgrid()
Gridname : AllDatabasesList

Message :
Sybase error in msg_handler_GET : 207 severity : 16 state : 4 line : 1 Msg : Invalid column name 'LastTranLogEndDumpTime'.

And here is the query:

===============================

set nocount on
declare @tname varchar(255), @srvname varchar(255), @msg varchar(255)
create table #dbinfos (
     srvname           varchar(255) not null,
     dbid              smallint not null,
     dbname            sysname  not null,
     Total_pgs         bigint      not null,
     dbFree_pgs        bigint          null,
     isMixedLog        tinyint     not null,
     logTotal_pgs      bigint      not null,
     logUsed_pgs       bigint          null,
     logFree_pgs       bigint          null,
     logClr_pgs        bigint          null,
     logsegFree_pgs    bigint          null,
     PageSize          int             null,
     BckET             varchar(32)     null,  -- Backup start time
     Bck_s             int             null,  -- Backup elapsed time
     BtranET           varchar(32)     null,  -- Dump tran start time
     Btran_s           int             null,  -- Dump tran elapsed time
     LastChkpt         varchar(32)     null   -- Last checkpoint time
)


-- get all databases infos
open c1
fetch c1 into @tname 
while @@sqlstatus = 0
begin
    select @srvname=substring(@tname,1,datalength(@tname)-10)
    execute ('insert into #dbinfos (srvname,dbid,dbname,Total_pgs,dbFree_pgs,isMixedLog,logTotal_pgs,logUsed_pgs,logFree_pgs,logClr_pgs,logsegFree_pgs,PageSize) 
              select '''+@srvname+''',dbid,dbname,Total_pgs,dbFree_pgs,isMixedLog,logTotal_pgs,logUsed_pgs,logFree_pgs,logClr_pgs,logsegFree_pgs, null  
              from '+@tname+' 
              where Timestamp=(select max(Timestamp) from '+@tname+')
             ')
    fetch c1 into @tname
end
close c1

-- get all server's pagesize
open c2
fetch c2 into @tname 
while @@sqlstatus = 0
begin
    select @srvname=substring(@tname,1,datalength(@tname)-9)
    execute ('update #dbinfos set PageSize =  A.PageSize   from  '+@tname+' A where A.Timestamp=(select max(Timestamp) from '+@tname+') and #dbinfos.srvname='''+@srvname+'''')
--select @msg= 'update #dbinfos set PageSize =  A.PageSize   from  '+@tname+' A where A.Timestamp=(select max(Timestamp) from '+@tname+') and #dbinfos.srvname='''+@srvname+''''
--print @msg

  -- check if OpenDbs tab is at V16SP04 level
  if exists (select * from syscolumns where name='BackupStartTime' and id=object_id(+@srvname+'_OpenDbs'))
  begin
    execute ('update #dbinfos
              set
                BckET     = (select case when max(BackupEndTime) is null then null else convert (varchar, max(BackupEndTime), 111)+'' ''+convert (varchar, max(BackupEndTime), 108)+''.''+convert(varchar, datepart(ms,max(BackupEndTime))) end from '+@srvname+'_OpenDbs where DBID=I.dbid and LastBackupFailed=0),
                Bck_s     = (select datediff(ss,max(BackupStartTime),max(BackupEndTime)) from '+@srvname+'_OpenDbs where DBID=I.dbid and LastBackupFailed=0), 
                BtranET   = (select case when max(LastTranLogEndDumpTime) is null then null else convert (varchar, max(LastTranLogEndDumpTime), 111)+'' ''+convert (varchar, max(LastTranLogEndDumpTime), 108)+''.''+convert(varchar, datepart(ms,max(LastTranLogEndDumpTime))) end from '+@srvname+'_OpenDbs where DBID=I.dbid and LastBackupFailed=0),
                Btran_s   = (select datediff(ss,max(LastTranLogDumpTime),max(LastTranLogEndDumpTime)) from '+@srvname+'_OpenDbs where DBID=I.dbid and LastBackupFailed=0),
                LastChkpt = (select case when max(LastCheckpointTime) is null then null else convert (varchar, max(LastCheckpointTime), 111)+'' ''+convert (varchar, max(LastCheckpointTime), 108)+''.''+convert(varchar, datepart(ms,max(LastCheckpointTime))) end from '+@srvname+'_OpenDbs where DBID=I.dbid)
              from #dbinfos I
              where I.srvname='''+@srvname+'''
             ')
  end
    fetch c2 into @tname
end
close c2

set rowcount 0
select srvname,dbid,dbname,
  TotalSize_Mb=(Total_pgs*PageSize)/(1024*1024),
  DataSize_Mb=(case when isMixedLog=0 then Total_pgs-logTotal_pgs else Total_pgs end)*PageSize/(1024*1024),
  DataFree_Mb=(dbFree_pgs*PageSize)/(1024*1024),
  isMixedLog,
  LogSize_Mb=case when isMixedLog=0 then (logTotal_pgs*PageSize)/(1024*1024) else 0 end,
  logUsed_pgs,
  LogFree_Mb=(case when isMixedLog=0 then logFree_pgs else dbFree_pgs end)*PageSize/(1024*1024) ,
  logClr_pgs,
  logsegFree_pgs,
  PctDatUsed=str(100. - 100.*dbFree_pgs / case when isMixedLog=0 then  Total_pgs-logTotal_pgs else  Total_pgs end ,6,2),
  PctLogUsed= str(100.*(logUsed_pgs+logClr_pgs)/(case when isMixedLog=0 then logTotal_pgs else Total_pgs  end)  ,6,2),
  PageSize,
  BckET   , 
  Bck_s    ,
  BtranET  ,
  Btran_s  ,
  LastChkpt

from #dbinfos
where 1=1
and PageSize is not null



drop table  #dbinfos
deallocate cursor c1
deallocate cursor c2
set rowcount 0

==============================================

I checked the target table <server>_OpenDbs and indeed there's no such column as 'LastTranLogEndDumpTime'. If anything, there's only 'LastTranLogDumpTime'</server>

Here is the output of sp_help on this table:

Column_name         Type     Length Prec Scale Nulls Not_compressed Default_name Rule_name Access_Rule_name Computed_Column_object Identity

---

Timestamp datetime 8 NULL NULL 0 0 NULL NULL NULL NULL 0
Interval int 4 NULL NULL 0 0 NULL NULL NULL NULL 0
DBName varchar 30 NULL NULL 1 0 NULL NULL NULL NULL 0
AppendLogRequests int 4 NULL NULL 1 0 NULL NULL NULL NULL 0
AppendLogWaits int 4 NULL NULL 1 0 NULL NULL NULL NULL 0
DBID int 4 NULL NULL 0 0 NULL NULL NULL NULL 0
InstanceID tinyint 1 NULL NULL 0 0 NULL NULL NULL NULL 0
BackupInProgress int 4 NULL NULL 0 0 NULL NULL NULL NULL 0
LastBackupFailed int 4 NULL NULL 0 0 NULL NULL NULL NULL 0
TransactionLogFull int 4 NULL NULL 0 0 NULL NULL NULL NULL 0
SuspendedProcesses int 4 NULL NULL 0 0 NULL NULL NULL NULL 0
PRSUpdateCount_smp int 4 NULL NULL 0 0 NULL NULL NULL NULL 0
PRSSelectCount_smp int 4 NULL NULL 0 0 NULL NULL NULL NULL 0
PRSRewriteCount_smp int 4 NULL NULL 0 0 NULL NULL NULL NULL 0
BackupStartTime datetime 8 NULL NULL 1 0 NULL NULL NULL NULL 0
LastCheckpointTime datetime 8 NULL NULL 1 0 NULL NULL NULL NULL 0
LastTranLogDumpTime datetime 8 NULL NULL 1 0 NULL NULL NULL NULL 0
QuiesceTag varchar 30 NULL NULL 1 0 NULL NULL NULL NULL 0
Object has the following indexes

index_name index_keys index_description index_max_rows_per_page index_fillfactor index_reservepagegap index_created index_local

---

idx Timestamp, DBName, Interval nonclustered 0 0 0 Nov 6 2024 4:46PM Global Index
idx1 Timestamp, DBID nonclustered 0 0 0 Nov 6 2024 4:46PM Global Index
==========================

Also what is strange about this query is that it opens two cursors, c1 and c2, but there is no cursor declare statement in it. Can you help on this ?

[elovelo]

Ok, If found this query in file \AsemonReportSrv\Asemon_report\ASE\sql\sql_alldatabases.php

And I commented out this portion:

execute ('update #dbinfos
              set
               -- BckET     = (select case when max(BackupEndTime) is null then null else convert (varchar, max(BackupEndTime), 111)+'' ''+convert (varchar, max(BackupEndTime), 108)+''.''+convert(varchar, datepart(ms,max(BackupEndTime))) end from '+@srvname+'_OpenDbs where DBID=I.dbid and LastBackupFailed=0),
               -- Bck_s     = (select datediff(ss,max(BackupStartTime),max(BackupEndTime)) from '+@srvname+'_OpenDbs where DBID=I.dbid and LastBackupFailed=0), 
               -- BtranET   = (select case when max(LastTranLogEndDumpTime) is null then null else convert (varchar, max(LastTranLogEndDumpTime), 111)+'' ''+convert (varchar, max(LastTranLogEndDumpTime), 108)+''.''+convert(varchar, datepart(ms,max(LastTranLogEndDumpTime))) end from '+@srvname+'_OpenDbs where DBID=I.dbid and LastBackupFailed=0),
               -- Btran_s   = (select datediff(ss,max(LastTranLogDumpTime),max(LastTranLogEndDumpTime)) from '+@srvname+'_OpenDbs where DBID=I.dbid and LastBackupFailed=0),
                LastChkpt = (select case when max(LastCheckpointTime) is null then null else convert (varchar, max(LastCheckpointTime), 111)+'' ''+convert (varchar, max(LastCheckpointTime), 108)+''.''+convert(varchar, datepart(ms,max(LastCheckpointTime))) end from '+@srvname+'_OpenDbs where DBID=I.dbid)
              from #dbinfos I
              where I.srvname='''+@srvname+'''
             ')

It turned out that column 'BackupEndTime' this query references also does not exists in table <server>_OpenDbs. Now AllDatabasesList works. It just has the respective columns pertaining to db and tran dumps empty. See attached ss. </server>

Howerver, should it be like that ? Looks like the schema of table <server>Open_Dbs is icorrect and misses these two columns and it should have if it has columns 'BackupStartTime' and 'LastTranLogDumpTime'.</server>

[Jean-Paul Martin]

Hi Elovelo,
yes BackupEndTime and LastTranLogEndDumpTime cols exists starting V16SP04 but BackupStartTime did exist before V16 SP04
My query tests BackupStartTime and uses LastTranLogEndDumpTime : this is wrong when your server is at SP02 level
Now I test LastTranLogEndDumpTime
Will be available in next version
Thank you for reporting this problem
Jpm

[elovelo]

thanks Martin. in the meantime I found a comment in that query just before these columns' values set referring to SP04, and it got mw thinking that it's probably not compatible with SP02, but I didn't want to spam this thread until someone responds.

we are planning to upgrade to SP04 somehwere next year anyway, but until then I'll keep these columns commented out. Thanks!

P.S. by the way, SAP plans to release new ASE 16.1 in this quarter. Do you already have plans for releasing Asemon version complatible with this version of ASE?

[Jean-Paul Martin]

I'll test asemon with 16.1 when it is availlable. I'll see if new monitoring counters are interesting and will use them

[elovelo]

Hi Jean-Paul,

I discovered the same issue with 'SpaceUsed' collector. In file 'sql_SpaceUsed_statistics.php' I had to comment out the select of columns 'BckET', 'Bck_s', 'BtranET', 'Btran_s', 'LastChkpt' from tables ".$ServerName."_AseDbSpce' and ".$ServerName."_OpenDbs as these columns do not exist in these tables.

Also, this collector does not show the 'sybsecurity' database which I have set up on the monitored server whereas the 'Logs contention' collector in the 'Summary' shows this db.

[Jean-Paul Martin]

Hi,
yes I already saw that and fixed it
Thanks
Jpm

[elovelo]

Ok, and what about the missing sybsecurity database ?

[Jean-Paul Martin]

sybsecurity was excluded by default for security reasons.
But I agree we can monitor its size
You can use this attached modified collectort file
Regards
Jpm

[elovelo]

Thank you J.P., it's working now.