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 supports SAP ASE Database versions 16.0 and later.
Data Collection Method
vuSmartMaps collects SAP ASE metrics through a data collector deployed within the vuSmartMaps platform, which uses JDBC to retrieve performance and health metrics.
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: Provide the database connection details.
- IP Address: IP address needs to be a valid IP Address.
- Port: Enter a valid port
- Credential: This field is required
- Metrics Collection Queries: These queries are used to collect SAP ASE server metrics.
- Query: Select one or more predefined query packs for SAP ASE metrics collection.
- Polling Interval [seconds]: The interval must be between 180 and 86,400 seconds.
nan
Firewall Requirement
To collect data from this O11ySource, ensure the following ports are opened:
| Source IP | Destination IP | Destination Port | Protocol | Direction |
|---|---|---|---|---|
| vuSmartMaps Collection/Ingress node 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
Metrics from SAP ASE are collected by querying system monitoring tables using a read-only monitor_user user via VuNet’s remote collection probe.
Note: The following steps must be executed by a user with system administrator privileges (for example, 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
sp_configure 'object lockwait timing', 1
go
reconfigure with override
go
Step 2: Create a Read-Only User and Grant Role
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 Required 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 Accessible Log in as monitor_user and run:
select name from sysobjects where name like 'mon%' and type = 'U'
go
Configuration Steps
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 | Alias of CPUTimeMs (backward compatibility). | UInt64 |
| QueryState | Current state of the query (Running or Completed). | String |
| status | Status of the server process (e.g., sleeping, running). | String |
| CPUTimeMs | Total CPU time 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 |
