SAP ASE

Introduction

SAP ASE (Adaptive Server Enterprise) offers rich, real-time monitoring data that can be leveraged for observability to ensure database health, performance, and reliability. By collecting metrics on query execution, CPU and I/O usage, locks, deadlocks, wait events, and cache efficiency, teams can proactively detect anomalies, troubleshoot slowdowns, optimize resource utilization, and gain insights into transactional patterns. This observability data is essential for maintaining SLAs, enabling faster root cause analysis, and ensuring seamless operation of business-critical applications.

Getting Started

Compatibility

The SAP ASE O11ySource is compatible with all versions of SAP ASE DB greater than or equal to 16.0

Data Collection Method

vuSmartMaps collects health and performance data for SAP ASE Database by using an internal data collector. This data collector collects data based on the source configuration.

Prerequisites

Dependent Configuration

To configure this O11ySource, create a 'credential' of type 'user' under the 'Definition' tab.

Inputs for Configuring Data Source

• Instance Name: Please enter the name of the SAP ASE instance. This should be a unique identifier for the specific SAP ASE deployment you want to monitor.
• DB Details: Please provide the details of DB
• IP Address: IP address needs to be a valid IP Address.
• Port: Enter a valid port
• Credential: This field is required

Firewall Requirement

To collect data from this O11ySource, ensure the following ports are opened:

Source IP	Destination IP	Destination Port	Protocol	Direction
vuSmartMaps IP	IP address of the SAP ASE server	5000*	TCP	Outbound

*Before providing the firewall requirements, please update the port based on the customer environment.

Configuring the Target

Configure Metrics Collection from SAP ASE Server Metrics from SAP ASE are gathered by querying system monitoring tables using a read-only

• monitor_user* user via Telegraf SQL plugin.Note: The following configuration steps must be executed by a user with system administrator privileges (e.g., login with sa or a user granted sa_role). Step 1: Enable Monitoring and Statistics Collection

sp_configure 'enable monitoring', 1
go
sp_configure 'sql text pipe active', 1
go
sp_configure 'statement pipe active', 1
go
sp_configure 'statement statistics active',1
go
sp_configure 'statement cache size', 1000
go
sp_configure 'wait event timing', 1
go
sp_configure 'per object statistics active', 1
go
go sp_configure 'object lockwait timing', 1
go
reconfigure with override
go

Step 2: Create Read-Only User and Grant Access

sp_addlogin monitor_user, <password>
go
sp_adduser monitor_user
go
grant role mon_role to monitor_user
go

Step 3: Grant Read-only Access to System Tables

grant select on sysusages to monitor_user
go
grant select on sysdatabases to monitor_user
go
grant select on sysprocesses to monitor_user
go
grant select on sysconfigures to monitor_user
go
grant select on syscurconfigs to monitor_user
go

Step 4: (Optional) Grant Access to Replication Monitoring

grant select on rs_lastcommit to monitor_user
go

Step 5: (Optional) Verify Monitoring Tables Are Enabled Login as monitor_user and run the following query to ensure monitoring tables are accessible:

select name from sysobjects where name like 'mon%' and type = 'U'
go

Configuration Steps

• Enable the SAP ASE O11ySource.
• Select the Sources tab and press the + button to add SAP ASE instance to be monitored.
• Set up metrics collection configurations. The O11ySource already provides predefined metric collection queries. Afterwards, select Save and Continue

Metrics Collected

Name	Description	Data Type
@timestamp	The event timestamp in string format, likely ISO-8601.	String
timestamp	Precise timestamp of the event, with milliseconds precision.	DateTime64
database_name	Database Instance name of Sybase ASE Server	String
data_type	Type of data being collected	String
host	Hostname of the Sybase ASE Server	String
target	Hostname of the Sybase ASE Server	String
SPID	Session Process ID identifying a user process	UInt32
LoginUser	Username of the database user executing the query	String
Application	Name of the client application connecting to the database	String
SourceHostName	Hostname of the machine from which the session originated	String
TableAccesses	Total number of table accesses by the session	UInt64
TotalCPUTime	Cumulative CPU time used by the process or engine	UInt64
LogicalReads	Number of logical page reads (from cache)	Float64
PhysicalReads	Number of physical page reads (from disk)	Float64
DBName	Name of the database associated with the query or session	String
SQLText	SQL query text being executed	String
ExecutionTimeMs	Elapsed execution time in milliseconds	UInt64
CpuTimeMs	CPU time used by the SQL query in milliseconds	UInt64
QueryState	Current state of the query (Running or Completed)	String
status	Status of the server process (e.g., sleeping, running)	String
CPUTimeMs	CPU time consumed by the process in milliseconds	UInt64
physical_io	Number of physical I/O operations performed	UInt64
memusage	Amount of memory used by the process	UInt64
MaxConnections	Maximum configured concurrent user connections	UInt64
TxnType	Type of transaction (commit or rollback)	String
count	Total count of occurrences for the specified transaction type	Float64
SegmentType	Type of database segment (data, log, or mixed)	String
TotalBytes	Total allocated space in bytes for the segment	UInt64
UsedBytes	Used space in bytes within the segment	UInt64
FreeBytes	Free space in bytes within the segment	UInt64
UsedPct	Percentage of space used within the segment	Float64
UptimeInSeconds	Uptime of the Sybase ASE server in seconds	UInt64
DeadlockID	Unique identifier for the deadlock occurrence	String
HeldSPID	SPID holding the lock in a deadlock situation	String
HeldSQL	SQL statement held by the blocking process	String
WaitSPID	SPID that is waiting for the resource	String
WaitSQL	SQL statement waiting for the lock to be released	String
ObjectDBName	Name of the database where the locked object resides	String
HeldUserName	Username of the process holding the lock	String
WaitUserName	Username of the process waiting on the lock	String
WaiterSPID	SPID of the waiting session in a lock wait scenario	String
HolderSPID	SPID of the session holding the requested lock	String
LockType	Type of lock acquired or requested (e.g., page, row)	String
WaitTime	Time duration the lock has been waited for	UInt64
WaiterHost	Hostname of the client waiting for the lock	String
WaiterCommand	Command being executed by the waiting process	String
CacheName	Name of the memory cache pool	String
CacheHitRatioPct	Percentage of cache hits over total cache accesses	Float64
InstanceID	ID of the ASE instance (engine) reporting metrics	String
ProcedureCacheHitRatio	Hit ratio of the procedure cache	Float64
EngineNumber	Engine number of the ASE engine thread	String
UserCPUTime	CPU time consumed by user operations	UInt64
SystemCPUTime	CPU time spent on system/kernel operations	UInt64
IOCPUTime	CPU time spent on io operations	UInt64
IOPct	Percentage of CPU time spent on I/O operations	Float64
IdlePct	Percentage of CPU time the engine was idle	Float64
SpinlockName	Name of the spinlock structure	String
TotalGrabs	Number of successful grabs of the spinlock	UInt64
TotalSpins	Number of times the engine spun while waiting for the spinlock	UInt64
TotalWaits	Number of times the engine waited for the spinlock	UInt64
AvgContentionRatio	Average contention ratio of the spinlock	Float64
TotalSearches	Total number of searches performed in the statement cache	UInt64
TotalHits	Total number of successful statement cache hits	UInt64
HitRatioPct	Hit ratio percentage for statement cache usage	Float64
WaitEventID	Identifier of the wait event	String
Description	Text description of the wait event	String
TotalWaitTimeInMs	Total time spent waiting for the event in milliseconds	UInt64
TotalWaitCount	Total number of wait events of the specified type	Float64
DBID	Internal identifier of the database	UInt32
PagesRead	Total number of pages read from disk	UInt64
PagesWritten	Total number of pages written to disk	UInt64
LockRequests	Total number of lock requests	UInt64
LockWaits	Total number of times a lock request resulted in a wait	UInt64
TruncTimeStamp	Timestamp of the last replicated transaction truncation	UInt64
CurrentTimeStamp	Current timestamp at the time of metric collection	UInt64
QueueSize	Current size of the replication queue	Float64
ServerUserID	Internal user ID executing the query	String
StartTime	Timestamp when the SQL statement began execution	UInt64
EndTime	Timestamp when the SQL statement finished execution	UInt64
suid	Server user ID used for mapping to login name	String
segmap	Segment map indicating the purpose of database segment (data/log)	String
unreservedpgs	Number of unreserved pages in a segment	UInt64
crdate	Creation time of the database used to calculate uptime	String
ResolveTime	Time at which a deadlock was resolved	DateTime64
BlockedBy	SPID that is blocking the current process	String
Waits	Number of times a wait event occurred	String
PreparedStmtCount	Number of statements prepared	UInt64
SessionDuraionMs	Duration of the session in milliseconds	UInt64
LoggedInDateTime	Logged in time for the user	String
HostProcess	Host process through which the connection is established	String
TransactionName	Name of the transaction	String
BlockedDurationInTicks	Duration for which the current process is blocked measured in ticks	UInt64
Command	Command being executed	String
IPAddress	IP Address	String
ClientName	Name of the client program which is using the connection	String
HeldApplName	Application name of the session holding the lock	String
WaitApplName	Application name of the session waiting on the lock	String
HeldTranName	Transaction name of the lock holder	String
WaitTranName	Transaction name of the waiter for the lock	String
HeldLockType	Type of the lock held	String
WaitLockType	Type of the lock waited upon	String
HeldCommand	Command being executed by the lock holder	String
WaitCommand	Command being executed by the lock waiter	String
HeldHostName	Hostname of the lock holder	String
WaitHostName	Hostname of the lock waiter	String
ObjectName	Object Name	String
InstanceName	Instance Name	String