How to check database statistics last update in SAP SYBASE ASE

Issue:

You would like to know how to check database statistics last updated date/time & percent rows modified in tables of your SAP Sybase ASE Environment

    SAP Adaptive Server Enterprise (ASE) 15.7 and 16.0 for Business Suite

Solution

    You can use t-code DBACockpit and go to those options menu:

        menu Space > Tables and Indexes > Tables , filter there the table name you would like to check. On the Details you'll have the "Data Change" which is the percentage of data changes (number of inserts, updates, and deletes that have occurred on the given object, partition, or column) since the last statistics update was performed on the object, and the "Last Statistics Flush".

        menu Space > Tables and Indexes > Single Table Analysis , and filter there the table name you would like to check. You'll see details for Statistics and ATM activities logs.

        menu Diagnostics > Automatic Tables Maintenance > ATM logs : you can filter on the Message Text box the table name you would like to check (using wildcard * , ie *tablename* ). The result of this consult will show you the datetime the update statistics ran for all tables or specific tables.

    If you don't have access to DBACockpit for some reason, you can manually consult the sysstatistics table (from your SID database) using isql tool as user 'SAPSR3'.

Here is a sample:

isql -USAPSR3 -S -X -w 2000

use
go
 
select id, , moddate, datachange ("", NULL, NULL) as datachange from ..sysstatistics where id=object_id("")
 
go

Note that the "datachange" column result is expressed in percentage, it is based on the number of rows remaining after a change.

For example, if a table has five rows and one row is deleted, datachange reports a value of 25 % since the current row count is 4 and the datachange counter is 1.