User connections check

Job details

Name:	User connections check
Platform:	Sybase
Category:	Performance
Description:	Checks the configuration setting for the ‘number of remote connections’ from the master.dbo.syscurconfigs table against the number of connections on the instance (processes with suid != 0 in from the master.dbo.sysprocesses table).
Long description:	Checks the configuration setting for the ‘number of remote connections’ from the master.dbo.syscurconfigs table against the number of connections on the instance (processes with suid != 0 in from the master.dbo.sysprocesses table).
Version:	1.2
Default schedule:	2,12,22,32,42,52 * * *
Requires engine install:	Yes
Compatibility tag:	.[type=‘instance’ & databasetype=‘sybase’]/instance[maj_version > ‘12′ & hasengine=‘YES’]

Parameters

Name	Default value	Description
warning threshold	90	The check will give a warning when the connection pool usage is above this ratio (in %).
alarm threshold	95	The check will give an alarm when the connection pool usage is above this ratio (in %).
keep data for	7	The number of days to keep records.

Job Summary

Purpose: The purpose of this job is to monitor and manage the user connections in a Sybase database system by checking the configuration settings and the actual number of active connections.
Why: This job is important because it helps in maintaining the efficiency and performance of the database by ensuring that the number of active connections does not exceed the configured maximum. If these thresholds are reached, it could result in degraded performance or even denial of service for new connection requests.
Manual checking: You can check this manually in the database by issuing these SQL commands:

SELECT config, value FROM master.dbo.syscurconfigs WHERE config=103;

SELECT count(*) FROM master.dbo.sysprocesses WHERE suid != 0;

Job Details

Category: Performance
Description: This task checks the “number of remote connections” configuration in the master.dbo.syscurconfigs table and compares it against the active connections (processes where suid != 0) from the master.dbo.sysprocesses table.
Company: dbwatch.no
Version: 1.2
Default Schedule: Every 10 minutes (at minutes 2, 12, 22, 32, 42, and 52 of each hour).

Thresholds Handling

Warning Threshold: The job triggers a warning if the usage ratio of the connection pool exceeds 90%.
Alarm Threshold: An alarm is triggered if the usage ratio exceeds 95%.

Cleanup Procedures

The cleanup involves dropping the procedure dbw_user_connections_check and the table user_connections_check_histr if the execution fails.

Implementation Details

Implementation involves creating a history table named user_connections_check_histr and a stored procedure named dbw_user_connections_check. The procedure calculates the number of free and used connections, inserting these values into the history table. It also parses thresholds and decides on the return value appropriate to the current state of the connection pool (0 for normal, 1 for warning, 2 for alarm). Historical records older than the specified "keep data for" period are deleted.

Report Generation

Report Template Version: 2
Report Title: User connections check
Description: The report provides insights into the historical data of user connections, categorizing data into “Active” (Used) connections and “Free” connections.
Presentation: The data is displayed in both table format and a chart, showing trends over time to help assess usage patterns and identify potential issues.

Attribute	Details
Title	User connections check
Data Parsed	Active connections, Free connections, History Date
Presentation Type	Table and Color-coded Category Chart

Compatibility Query

Checks for Sybase instances of major version greater than 12 which also have an engine installed.

Additional Operational Information

Express Install: Enabled
Force Install: Enabled

This monitoring job is crucial for Sybase database administrators to effectively manage resources and ensure optimal performance by monitoring user connections and reacting proactively when thresholds are approached or exceeded.