Support

Contact Support

Technical Documents

Document Types

Partner Certification Report

Technote

White Paper-Technical

Technical Topics

Industry & Strategic Topics

Platforms

Products

ASE 15 Migration Resources

Platform Certification

Sybase IQ 15 Migration Resources

Education Services

Consulting

Managed Services

Downloads

Product Documentation/Manuals

Community Forums/Newsgroups

Case Management

Solved Cases

EBFs/Maintenance

Support Renewal Sales

Support > Technical Documents > Document Types > Technote > Best Practices Document for ASE Monitoring Best Practices Document for ASE Monitoring This document discusses some of the best practice ideas on what aspects of a production ASE environment should be monitored.

Best Practice

Subject: Sybase ASE Monitoring

Author(s): Tom Oorebeek, Staff DBA, Sybase IT

Reviewer(s): Hema Seshadri, Sr. DBA Manager, Sybase IT

Abstract:

In today's information driven world, high availability and optimal performance of database infrastructure is more important than ever. Many business aspects rely on being able to retrieve real-time data from their databases. Monitoring this critical infrastructure ensures maximum uptime. Monitoring is important not just from an overall availability and performance perspective, but also from the perspective of the end-user such that business productivity is not compromised. This document discusses some of the best practice ideas on what aspects of a production ASE environment should be monitored.

Introduction.

1 Best Practices: ASE Monitoring.

1.1 ASE versions.

1.2 Operating systems.

1.3 Tools.

1.4 Monitoring aspects.

1.5 What next ?

2 What to monitor ?

2.1 The ASE itself

2.2 Licenses

2.3 Database Availability.

2.4 Data Storage.

2.5 Disk Space.

2.6 Database & Transaction Log dumps.

2.7 User Activity

2.8 Error Logs

2.9 Blocking

2.10 Data Consistency

Appendix A

Template error checking script

Filename: STARTSERVER

Filename: admindump

Filename: cnt_sessions

Filename: dba.pl

Filename: db_spy

Filename: hostname.fs_spaceused.ini

Filename: fs_spaceused

Filename: logscan.ini

Filename: logscan

Filename: object_addition

Filename: run_sysmon

Filename: sp_block.sql

Filename: sp_echotime.sql

Filename: sp_devusage.sql

Filename: sp_hogs.sql

Filename: sp_lockname.sql

Filename: sp_w.sql

Introduction

- This Best Practices document is to help a Sybase DBA understand the various aspects of ASE monitoring and provide a quick start for setting up such monitoring.

1 Best Practices: ASE Monitoring

1.1 ASE versions

This document applies to all currently supported ASE versions. If certain features are specific to a particular version, it will be mentioned in the description at hand.

1.2 Operating systems

The monitoring aspects are generic across H/W platforms, although the sample scripts in Appendix A were written for the UNIX platform.

1.3 Tools

Various tools are available in the market for monitoring an ASE, e.g. Sybase SCC, Sybase Central, sysmon, MDA Tables, DB Artisan, DB Virtualizer, or Nimbus. This document focuses on what to monitor rather than recommending a tool or method to monitor. As necessary, references are made to automatic/unattended monitoring through scripts and cronjobs (Unix environments only).

A standard ASE server software installation includes software like Historical Server and Monitor Server, but these are separate products with their own manuals. As such these products are not described here either.

1.4 Monitoring aspects

What aspects of an ASE should be monitored? How often? Although the type, frequency and threshold levels for monitoring a system will vary from environment to environment, some aspects are essential to all ASE environments.

ASE Availability
Licenses
Database availability
Data storage
- Devices
- Database segments
- Log
User Activity
Error logs
Blocking
Data Consistency

1.5 What next ?

You followed the best practices and did setup extensive monitoring. Now, what?

Whether you choose to page a on call DBA, email the DBA or an entire alias, or fix the problem on a proactive basis, is up to you. This document does not spell out the actions to follow an alert from your monitoring tool, although certain recommendations may be made.

Typical Sybase environments have multiple ASEs that cater to a variety of business applications. It is recommended to buy or build a tool that provides an enterprise view of these systems, with the ability to drill down to issues for any given system.

A dashboard approach to the problem that is tied in to the monitoring system, allows for the monitoring script/feature to display the server status based on the alerts just received. For example, a server down status might mark the server icon red. An error 1105 situation might mark the server orange. A server with no monitoring alerts would stay green and so on. As alerts are attended to, the server status would change accordingly.

2 What to monitor ?

2.1 The ASE itself

Is the server up and running?

Ping the database server frequently to ensure it is alive and responding to commands. Check for the process at O/S level and also ping the database for a simple command. Ensure it returns the expect results. This confirms the basic availability of your server. Page the on call DBA if the server process does not show up or respond.

Do the same for your Backup Server process.

If it was just rebooted,
- Did it start up correctly with all options enabled?
- Is the license valid?
- Did it come up with async I/O?
- Did all the databases come up correctly?
- Check for anything abnormal

ASE startup messages are more or less standard server messages, like opening of the allocated devices and databases, but others, depending on configuration and/or traceflags, can show extra information about various aspects of the running ASE, like: connections (successful and failure logins), licenses etc.

See sample STARTSERVER script file in Appendix A for built-in error checking.

Once a server has started, the errorlog should be checked for startup behavior and non-standard messages, whereas a running server should have its errorlog automatically and periodically be scanned and non-standard messages be emailed to the concerned DBA(s).

To decide if a message is important enough for reporting, one could setup search strings per different type of messages, like errors (fatal or not), warnings and operational messages.
A separate category would be messages that could be picked up as errors or warnings, but can be skipped during normal processing. This type of messages would include objects names with the word error or msg in it.

Be sure to include keywords such as E(e)rror, stack, infected, warning, msg, instead, will shutdown, failed and any other keywords/phrases applicable to your environment. Filter on errors you know to be informational. See your ASE Error Messages Guide for details on informational errors. Error numbers of the pattern 6?? (6 followed by two digits) tend to be severe. Set up special alerts for this - for example, email messages with the word 'PANIC' or 'SEVERE ALERT' in them.

2.2 Licenses

Many Sybase products are offered with several editions and license types. Some features are not part of the core product and as such, require active licenses. Most customers use the SYSAM manager to manage licenses. Some may have a central licensing server.

Monitor your license server process. Ensure it is up and running. Look for errors. If you use stand alone licensing, check for keywords such as 'grace', 'will shutdown', 'Failed to obtain' and 'expire'.

You can specify the email address to send licensing warnings and errors during ASE installation or by using the sp_lmconfig command after installation.

2.3 Database Availability

The server itself may be up and running, but are the individual databases available to the users? For example, after a scheduled downtime where a particular database was made offline to users, setup monitoring that will alert a DBA if a database is unintentionally left in dbo or read-only mode. Or sql queries may be timing out on a particular user database.

Another example would be that the database is available to users, but is perhaps in log suspend or is refusing connections for some reason.

- Setup alerts for free log space per user database. Set up your alerts such that they escalate in priority if the situation does not improve.
- You may set your last chance thresholds to dump the log or abort the transaction, but there could be scenarios where the LCT action does not have a chance to kick in. Be sure to check for 1105 and log suspend errors.

In addition to alerts, one should check the general response time that will alert the DBA if a simple query such as 'sp_who' does not return in a reasonable amount of time. Be sure to use a non-privileged account when checking database availability as discussed in this section.

2.4 Data Storage

Device and database space is more or less static and pre-allocated. Usage information is provided by the standard procedures, sp_helpdevice (with ASE 15.x also showing free space) and sp_helpdb (also showing free space per fragment).

However, these standard procedures do not show the details required for continuous monitoring or they show too much detail, like per each fragment, when we are only interested in overall data, log and/or index segments.

Most important space monitoring is the current level of segment (data and/or log) space usage and the growth per day, week or month. Keeping historical space and growth information allows for better resource (disk space) capacity planning and allocation.

Devices

In general database devices are created on raw partitions and are therefore not monitored with O/S commands. However, in case an ASE also uses filesystems to store (some of) its database devices, these filesystems should also be monitored. Examples are the devices for tempdb and/or development and test ASEs.

Again, in general, these filesystem devices are more or less static, but since ASE 12.5 introduced the disk resize command, even these existing devices can grow and fill up the filesystem they are stored on. Growth and usage of these devices should therefore also be monitored.

See the fs_spaceused script and sp_devusage stored procedure code in Appendix A.

2.5 Disk Space

Depending on the Operating System being used, disk space usage can be monitored using the df (Solaris, AIX) or bdf (HP-UX) commands. Important directories to monitor at the O/S level are:

- The Sybase software installation tree ("Sybase directory")
- Errorlog directory
- Possible device directories
- Database dump directories (if dumping to disk)

The Sybase software directory is more or less static, apart from the ever growing errorlog and configuration files. For best practices, you may want to consider separating these files from the main installation directory for easier maintenance.

Each ASE has its own startup files like the RUNSERVER startup script, configuration files and its errorlog. Separating these files under there own folder structure enables better handling, monitoring and also allows easier upgrading of the ASE software in the future.
As an example one could store the software and startup files using the following tree setup: (using PROD_ASE as example server name)

Sample SYBASE software tree:

Directory:                 Contents:
--------------------       ---------------------------------------
/sybase/PROD_ASE           Startup script(s) and sub directories.

/sybase/PROD_ASE/cfg Configuration files

/sybase/PROD_ASE/log ASE error logs

/sybase/sybase_12.5.4 Version 12.5.4 software tree

/sybase/sybase_15.0.3 Version 15.0.3 software tree

/sybase Link to the actual version directory currently being used.

A tree setup of DBA script and log directories eases the way the monitoring environment can be maintained and copied to other hosts/ASE's.

Sample DBA software tree:

Directory:    Contents:
--------------       -----------------------------------------------------
/dba/jobs     DBA scripts for standard ASE tasks and monitoring.

/dba/input DBA input files, used by the DBA scripts

/dba/output DBA output files, like *.bcp etc.

/dba/log DBA script logfiles and central server logfile(s).

/dba/sysmon DBA script logfiles for a specific job, e.g.: run_sysmon

The above mentioned directories are used in the script samples in Appendix A.

2.6 Database & Transaction Log dumps

To provide for easy recovery, it is recommended that you backup your Sybase databases periodically. Backups may be done directly to tape or, first backed up to disk filesystems and later backed up at the O/S or network level. In this latter method, both the software being used (Sybase directory, startup directories, scripts and log files) as well as the database dumps can be stored on one tape or combination of tapes.

Directories used for database dumps have to be checked at least once a day, once the database dumps have been made, as once the data volume grows, also the size of the database dump files will grow. For performance reasons it is possible to dump databases to parallel stripes, meaning multiple dump directories (and/or filesystems) can be used for one database dump. Be sure to monitor space in each of the dump directories, so your nightly backups succeed.

Transactional log dumps are periodically taken to ensure point-in-time recovery. Keep these tran log dump files in a separate directory from your full backups and monitor this directory for free space as well.

Setup your monitoring to confirm daily successful backups. In addition to lack of disk space being a possible issue, there could be other failures, including disk failures or errors. Check the backup server errorlog for error keywords.

2.7 User Activity

User Activity is significant to system performance and health. It needs to be monitored from a historical perspective as well real-time to identify performance problems as they happen. Gather information on active user sessions, CPU cycles, cache hits, and disk I/O over a period of time. This will help you set up alert thresholds.

User connections

Monitor the total number of currently connected clients/users on a regular basis. This will help establish a baseline. Regular checking if a server (almost) hits its maximum number of user connections prevents users from being locked out (signaled with Error 1601 messages in the log). One way to check the number of current connections is the standard procedure sp_who, but more detailed and summary providing scripts and procedures are referenced in this document. See the sp_w procedure, db_spy and cnt_sessions scripts in Appendix A.

CPU, Disk I/O, cache hits

A standard ASE procedure that can be used to monitor for contention and bottlenecks in real-time is sp_sysmon. Running this procedure on a regular basis with a reasonable timeframe (e.g. 5-15 minutes per run) gives us information about various ASE counters and behavior, for that timeframe. When combined with information gathered on active processes (see sample db_spy script in Appendix A), one should be able to pinpoint server or process misbehavior to certain processes and/or configuration issues. Several third party monitoring tools use this method of gathering information in their monitoring.

For details on using and interpreting sp_sysmon, please see the standard documentation set: Performance and Tuning Guide, chapter: Monitoring Performance with sp_sysmon.

There are tools available on the O/S side of the house as well to check for disk I/O bottlenecks on devices that house the database and log files.

See sample script run_sysmon in Appendix A.

MDA tables / Problem SQL

ASE 12.x introduced new Monitoring and Diagnostic (MDA) tables, which store information about currently executing processes and statements and allows a DBA to get detailed information about ASE resources.

ASE 15.x has even more and better detailed MDA table information and as such version 15.X allows us to better monitor current behavior of the ASE.

In ASE 12.0, the MDA tables have to be installed separately using the installmontables scripts (supplied by Sybase).

In version 15.0 the MDA table installation has been incorporated in the standard installmaster script. This means every 15.x ASE already has these tables installed, but some configuration options have to be set, to be able to use them.

Typical problems faced by DBAs include sudden slow performance, hung queries, queries consuming 100% CPU and the like. Tools such as MDA Tables can be a big help here. Since problems don't always occur when a DBA is at their desk, it helps to collect historical information that can be used as a baseline. Some of the things MDA collects that can help you monitor as well as root cause a problem:

Process Activity: CPU usage, IO activity, resource usage
Resource usage: Data cache, procedure cache, engines
Object usage: Tables, partitions, indexes, stored procedures
Query history: SQL text, statement metrics, query plans, errors

The ASE documentation set has a separate chapter about how to use the MDA tables.
Please refer to the following manual for more information on this topic:

Adaptive Server Enterprise 15.0 > Performance and Tuning: Monitoring and Analyzing > Monitoring Tables

2.8 Error Logs

All logfiles associated with your ASE (ASE errorlog, backup server log, monitoring or maintenance jobs output logs) should be scanned for errors, warnings and other important messages. At a minimum this scanning should report the existence of any messages (found/not found or success/failure). One step further would be to also report the object (e.g. database or segment) the messages are related to.

Be sure to monitor space for your ASE errorlogs. In the default setup, ASE always appends to the current errorlog, so this file is always growing and can become too large to query or edit with an editor. In case of an ASE reboot, it is therefore advised to rename the old errorlog (adding date/timestamp) and have the ASE create a new errorlog dur ing every reboot.

See "Template error checking script" for a sample error checking framework in Appendix A.

2.9 Blocking

Most blocking conflicts are temporary in nature, and will resolve themselves eventually in a very short period of time. However, potentially bad application design or thoughtless adhoc user transactions can cause massive blocking impacting multiple users of the database. At such times, the server may be up and running, but for all practical purposes it is unavailable to the user.

Setup your monitoring to check for blocking that is non-transient. Any process (spid) that blocks for more than say, 2 minutes should be watched. Depending on the tolerance level of your user base, setup the alert to page the oncall DBA upon reaching a certain threshold. Often times, agreements with the business allow for spids blocking > 'x' minutes to be killed by the monitoring script.

See script sp_block in Appendix A for an example.

2.10 Data Consistency

Data consistency must be checked periodically. dbcc checkdb, dbcc checkcatalog and dbcc checkalloc are options that may take awhile to run on large databases, possibly blocking regular user access to the objects currently being checked.

An alternative is dbcc checkstorage which includes many dbcc checks, as well as an archive db setup, which provides the possibility for offline dbcc checks. Neither will however fix errors reported. Setup your monitoring to first check for successful running and completion of this job and two, to report on faults reported.

Appendix A

Each sample script below executes the tasks it is written for, stores output of the job in the script's logfile, writes status information to a central logfile (per server) and where possible also writes timing and status information to a central database.

Remark:

All scripts below use the here shown OS/Unix and SQL code setup, but some of them are shown as stripped versions to make it easier to understand the purpose of the script, instead of supplying a full blown, ready to run script.

Also OS specifics, like setting up a perl environment, and/or particular database/table layouts are not detailed, but just their sample usage is shown.

Template error checking script

. /sybase/SYBASE.sh # Set SYBASE environment

STATUS=SUCCESS # Initial script statu

DOMAIL=true # Send mail in case of error

FNDERR='ERROR|error|^Msg|^DB-LIB|^CT-LIB' # Find during error checking

SKPERR='_error|error_' # Skip during error checking

( this info can also be gathered from separately stored ini files, see e.g. logscan.ini )

LOGDIR=/dba/log # Set log directory

SVRLOG=$LOGDIR/$SERVER.LOG # Server central logfile

LOGFIL=$LOGDIR/$SERVER.$SCRIPT.log.'date +'%m%d.%H%M'' # Script logfile with timestamp

ERRFIL=$LOGDIR/$SERVER.$SCRIPT.log.errors # Error file

SYBUSR=dba # Get server login name

SYBPWD='cat /dba/input/.dba_pwd' # Get server password

MAILER="/usr/bin/mail" # Mail utility

RUNSQL="$SYBASE/$SYBASE_OCS/bin/isql -U$SYBUSR -S$SERVER -w300" # Define isql command

RB_INS="### Your own utility to insert monitoring data into a database, here called runbook"

>> $SVRLOG echo "'date '+%m%d.%H%M'' $SCRIPT started" # Add info to central logfile

> $LOGFIL echo "'date +'%Y.%m.%d %H:%M:%S'' Start $SCRIPT" # Open script logfile

## LOOP PER DATABASE OR OTHER OBJECT ...

BEGERR='egrep "$FNDERR" $LOGFIL | egrep -v "$SKPERR" | wc -l' # BEGIN error count

$RUNSQL <<-ENDSQL >> $LOGFIL

$SYBPWD -- Prevent password display in ps -ef

SQL_statements -- Do here whatever you want to do in this script
go

ENDSQL

ENDERR='egrep "$FNDERR" $LOGFIL | egrep -v "$SKPERR" | wc -l' # END error count

if [ $ENDERR -gt $BEGERR ] # Did errors occur?

then

STATUS="ERROR_Detected"

>> $LOGFIL echo "'date +'%Y.%m.%d %H:%M:%S'' $SCRIPT $STATUS."

else

>> $LOGFIL echo "'date +'%Y.%m.%d %H:%M:%S'' $SCRIPT completed successfully."

## END_LOOP

if [ "$STATUS" = "SUCCESS" ]

then
$RB_INS -s success # Record status for monitoring
else
$RB_INS -s error # Record status for monitoring

case $DOMAIL in

true)

> $ERRFIL echo "Logfile: $LOGFIL" # Select errors and send mail
>> $ERRFIL echo ""

egrep "$FNDERR" $LOGFIL | egrep -v "$SKPERR" >> $ERRFIL

$MAILER -s "*** $SERVER $SCRIPT: ERROR DETECTED" $MAILTO < $ERRFIL

>> $LOGFIL echo ">>> Mail sent to $MAILTO"
esac
fi

>> $LOGFIL echo "'date +'%Y.%m.%d %H:%M:%S'' Final Status: $STATUS"

>> $SVRLOG echo "'date '+%m%d.%H%M'' $SCRIPT $STATUS" # Different time info

Filename: STARTSERVER

Purpose: Starts server PROD_ASE

#/bin/sh
#------------------------------------------------------------------------
# Start dataserver: PROD_ASE
# -------------------------------------------------------------------------------

. /sybase/SYBASE.sh # Set Sybase environment

SERVER=PROD_ASE                                 # Name of the ASE
MASTER=/dev/rdsk/sybase/PROD_ASE.master         # master device

CFGDIR=/sybase/$SERVER/cfg                      # Configuration directory
LOGDIR=/sybase/$SERVER/log                      # Log directory
CFGFIL=$CFGDIR/$SERVER.cfg                      # Configuration file
LOGFIL=$LOGDIR/errorlog                         # Errorlog

TRACES="" # Traceflags
TRACES="$TRACES -T1204" # Print deadlock info in log

# TRACES="$TRACES -T4013" # Show login records in log
# (now config setting)

# -------------------------------------------------------------------------------
# Check is server is already running (prevent logfile rename)
# -------------------------------------------------------------------------------
if [ '/usr/bin/ps -ef | grep dataserver | grep $SERVER | wc -l' -gt 0 ]
then

echo "WHOA ... $SERVER is already running !!!"
exit 1

else

   # ------------------------------------------------------------
   # Check Async IO setting
   # ------------------------------------------------------------
   asyncERR='grep -c "allow sql server async i/o = 0" $CFGFIL'

   if [ $asyncERR -gt 0 ]
   then
       echo "------------------------------------------------------------"
       echo " ERROR: Async IO not configured !!!"
       echo "------------------------------------------------------------"
       exit 1
fi

[ -f $LOGFIL ] && { mv $LOGFIL $LOGFIL.'date +%y%m%d.%H%M' ; }    # Rename log

   # ------------------------------------------------------------
   # Start ASE, specifying special directories and options
   # ------------------------------------------------------------

$SYBASE/$SYBASE_ASE/bin/dataserver -s$SERVER \

-e$LOGFIL \

-d$MASTER \

-c$CFGFIL \

-i$SYBASE \

-M$CFGDIR \

$TRACES > /dev/null &
fi

Filename: admindump

Purpose: Save contents of important system tables in BCP (-c = text) format.

#!/bin/sh

# --------------------------------------------------------------------------------------

# admindump Dump various system tables in character format

# --------------------------------------------------------------------------------------

if [ ! -d $BCPDIR ]

then

>> $LOGFIL echo "ERROR bcp directory $BCPDIR does not exist!"

exit 1

else

cd $BCPDIR # Switch to output directory

PARAMS="-c -U$SERVERUSER -S$SERVER" # Set parameters

for SYSTAB in syslogins \

sysdatabases \

sysservers \

sysremotelogins \

syssrvroles \

syscharsets \

sysconfigures \

sysloginroles

OUTFIL="$SERVER.$SYSTAB.bcp.'date +'%m%d''" # Output filename

>> $LOGFIL echo "BCP out master..$SYSTAB ... 'date'"

$RUNBCP master..$SYSTAB out $OUTFIL $PARAMS < $PWDFILE >> $LOGFIL

done

Filename: cnt_sessions

Purpose: Counts and shows the top 30 nr of sessions per ASE account

#!/bin/sh
# ------------------------------------------------------------------------------
# Filename: cnt_sessions
#
# Counts the nr of sessions and shows config values
#
# Parameters: 1) servername, name of the server to connect to
#
# Expects: stored proc sp_echotime should exist.
# ------------------------------------------------------------------------------

SCRIPT='basename $0|sed 's/.sh//'' # This script

SERVER='echo $1 | tr '[a-z]' '[A-Z]'' # Server name

DISPLY=$2 # Any string to prevent display (during cron execution)

[ "$SERVER" = "" ] && { echo "Usage: $SCRIPT servername [ nodisplay ]"; exit 1; }

LOGFIL=./$SCRIPT.log.$SERVER.'date +%y%m%d.%H%M'

RUNSQL="isql -Udba -S$SERVER -w200"

# ------------------------------------------------------------------------------
# Generate report
# ------------------------------------------------------------------------------

$RUNSQL <<-ENDSQL | egrep -v 'Password:|return status = 0' > $LOGFIL

'cat /dba/input/.dba_pwd'

SET NOCOUNT ON
DECLARE @msg varchar(55)

SELECT @msg = 'Sessions in server: ' + upper(srvname)

FROM master..sysservers

WHERE srvid = 0

EXEC sp_echotime @msg

PRINT ' ---------------------------- ----------- ----------- ------------ -----------'

EXEC sp_configure 'user connections'

SELECT convert(char(11), getdate(), 102) + convert(char(8), getdate(), 108)

, 'Total nr of sessions: ' + convert(varchar, count(*))

FROM master..sysprocesses

PRINT ''

SET ROWCOUNT 30

SELECT TOP_30 = suser_name(suid)

, Sessions = count(suid)

FROM master..sysprocesses

GROUP BY suid

ORDER BY count(suid) DESC

ENDSQL

more $LOGFIL

Filename: dba.pl

Purpose: Standard perl module, used by all other DBA perl scripts

# ----------------------------------------------------------------------------------
# Name: dba.pl
# ----------------------------------------------------------------------------------
# Description/Purpose:     This perl module is the standard module for all dba perl jobs.
# It includes standard variable assignment as well as standard functions.
#
# Functions:
#       SetStandardVariables    - Sets up standard dba variables
#       ErrorOccurred           - Processes terminating errors
#       LogMessage              - Logs messages to standard log file
#       CentralLog              - Places a message in the "central log"
#       MailMessage             - Mails messages
#       SigTrapped              - Processes terminating signals
#       StartLog                - Puts a nicely formatted begin message in the log
#       EndLog                  - Puts a nicely formatted end message in the log
#       ExecSQL                 - Runs a SQL Statment using isql
#       ExecSql                 - Runs a SQL Statment using SybPerl DBlib
#       BaseAction              - Simple handler for ExecSql output
#       ObtainStatus            - Text to numeric converter for log/output files.
#       date                    - Returns numeric formatted date/time strings
#       DefineOS                - Sets up OS dependent variables
#       SetupAseOcsVersion      - Handles different release versions of OCS.
#
# Modification Log
# Login   Date         Action
# -------- ------------- -----------------------------------------------------------
# xxxxxx   yyy.mm.dd    Details ...
# ----------------------------------------------------------------------------------

sub SetStandardVariables
{

        # This function sets up standard variables for dba jobs. All variables
        # that end with "Dir" are based on the $MainDir variable that should be
        # passed in through an initialization file. The OcsBin directory is also
        # based on a passed in variable.

        &DefineOS;
        &SetupAseOcsVersion;

        $JobName = $0;
        $JobName =~ s,.*[\\/](.*),$1, ;
        $JobName =~ s/\.pl//;

        chomp($Prefix    = &date("md"));
        chomp($Suffix    = &date("HMS"));
        chomp($Suffix2 = &date("HM"));
        chomp($TimeStamp = &date("mdHM"));

        # Set File Location Variables

       $JobDir = "${MainDir}/jobs";
        $BinDir = "${MainDir}/bin";
        $DiagDir = "${MainDir}/diagnostics";
        $SqlDir = "${MainDir}/sql";
        $SqrDir = "${MainDir}/sqr";
        $InDir   = "${MainDir}/input";
        $OutDir = "${MainDir}/output";
        $WorkDir = "${MainDir}/work";
        $LogDir = "${MainDir}/log";
        $ReptDir = "${MainDir}/reports";

        $WorkFile1 = "${WorkDir}/${Prefix}${JobName}${Suffix}.wk1";
        $WorkFile2 = "${WorkDir}/${Prefix}${JobName}${Suffix}.wk2";
        $WorkFile3 = "${WorkDir}/${Prefix}${JobName}${Suffix}.wk3";
        $MailFile = "${WorkDir}/${Prefix}${JobName}${Suffix}.mail";

        $LogFile    = "${LogDir}/${SqlServer}.${JobName}.log.${Prefix}.${Suffix2}";
        $CentralLog = "${LogDir}/${SqlServer}.LOG";
}
sub ErrorOccured
{
        # This function simply removes any temporary files and exits
        # with a signal 99. This is called upon an unexpected exit.

        if (-r "$WorkDir/*$JobName*")

        {
                unlink "$WorkDir/*JobName*";
        }

exit (99);
}

sub LogMessage {

        # This function places an entry into the Log File
        # that reads as the supplied argument Message.

        if (!defined($_[0]))

{
$_[0] = "Log function called, no message supplied!";

}

open (LOGFILE, ">> $LogFile");

print LOGFILE "$_[0]\n";

close (LOGFILE);

}

sub CentralLog

{
# To be compatible with older scripts, this allow the basic function
# to enter a final status line to a central log for a given SQL Server

my $FinalStatus = $_[0];

if ($FinalStatus eq "")

        {
           $FinalStatus = "CentralLog function called, no message supplied!";
        };

open (CENTRALLOG, ">> $CentralLog");

print CENTRALLOG "$Prefix $Suffix: $JobName: $FinalStatus\n";

close (CENTRALLOG);

}

sub MailMessage

{
        # This file will send mail to the recipient with the given subject.
        # If the body is supplied, it will also be sent. If a sender is supplied,
        # the message will be marked with that sender in the "From" field.

        my $Recipient = $_[0];
        my $Subject   = $_[1];
        my $Body      = $_[2];
        my $Sender    = $_[3];

        $Recipient =~ s/\n+//;
        $Subject   =~ s/\n+//;
        $Sender    =~ s/\n+//;

        if (!defined $MailFile)
        {
                if ($OSFlag ne "Y")
                {
                   &DefineOS;
                }
                $MailFile = "${Temp}/dba_mail.$$";
        }

        if (-r $Body)

        {
                open (MAILFILE, "> $MailFile");
                open (BODY,     "$Body");
                while (<BODY>)
                {
                   print MAILFILE "$_";
                }
                close (BODY);
                close (MAILFILE);

} else {

                open (MAILFILE, "> $MailFile");
                print MAILFILE "$Body";
                close (MAILFILE);

        }

        if ($OS eq "MSWin32")
        {
        $Mail = qq! $Mail $MailFile -t "$Recipient" -s "$Subject" -I "$Sender" -f "$Sender" !;

open (MAIL, "| $Mail");

while (<MAIL>) { print $_; };

close (MAIL);

        } else {
                open (MAILFILE, "$MailFile");
                open (MAIL, "| $Mail $Recipient");
                print MAIL "To: $Recipient\n";
                print MAIL "From: $Sender\n";

print MAIL "Subject: $Subject\n\n";

                while (<MAILFILE>)
                {
                        print MAIL $_;
                }
                print MAIL "\n.\n";
                close (MAIL);
                close (MAILFILE);
        }

        $mesg = "Mail sent to $Recipient\n";

if (defined(&fLogMessage))

        {        &fLogMessage($mesg);
        } else {   LogMessage($mesg);
               }

unlink ("$MailFile");

}

sub SigTrapped

{

        # This works in conjuction with any standard script which sets
        # up an errorhandling routine to deal with abnormal signals.
        # These are signals such as kill, hup, etc. The routine makes
        # an entry into the Log File as to which signal was received
        # and then calls the erroroccurred cleanup subfunction which
        # then exits the program.

my ($sig) = @_;

&LogMessage("Error: Signal: $sig received: exiting program");

        &ErrorOccured;
}

sub StartLog
{
        # This subfunction begins the Log File with a nicely formatted
        # set of information about the particular script. The Info
        # String passed indicates what information to initially log
        # and is passed simply as a string of uppercase characters

        if (!defined($_[0]))

        {
                $_[0] = "JWIOLS"
        }

        uc($_[0]);

        @LogOptions = split(//, $_[0]);

        $Mesg = "Begin Processing: $JobName\n" . "Date: " . &date();
        $Mesg .= "Directories:\n";

        foreach $LogOption (@LogOptions)
        {
                if    ($LogOPtion eq "B")   { $Mesg .= "        Bin: $BinDir\n";
              } elsif ($LogOption eq "D") { $Mesg .= "Diagnostics: $DiagDir\n";
                } elsif ($LogOption eq "I") { $Mesg .= "      Input: $InDir\n";
                } elsif ($LogOption eq "J") { $Mesg .= "        Job: $JobDir\n";
               } elsif ($LogOption eq "L") { $Mesg .= "        Log: $LogDir\n";
                } elsif ($LogOption eq "O") { $Mesg .= "     Output: $OutDir\n";
                } elsif ($LogOption eq "Q") { $Mesg .= "        SQL: $SqlDir\n";
                } elsif ($LogOption eq "S") { $Mesg .= " Sybase Bin: $OcsBin\n";
                } elsif ($LogOption eq "W") { $Mesg .= "       Work: $WorkDir\n";
                }
        }
        $Mesg .= "-------------------------------------------\n";

        &LogMessage($Mesg);
}

sub EndLog
{
        # This procedure Places a nicely formatted finishing set of lines
        # to the Log File at the end of processing, includes a timestamp
        # and the jobname.

        $Mesg = "-------------------------------------------\n";
        $Mesg .= "End Processing: $JobName\n" . &date();

        &LogMessage($Mesg);
}

sub ExecSQL
{
        # Executes an SQL Statement against the given
        # server and places the output in the Log File.

        my $sql_code = $_[0];
        my $server   = $_[1];
        my $user     = $_[2];
        my $password = $_[3];
        my $TempFile = $_[4];
        my $OutFile;

        if ($OSFlag ne "Y")
        {
                &DefineOS;
        }

        if (defined($TempFile))
        {
                $OutFile = $TempFile;
        } else {
                $OutFile = $LogFile;
        }

        if ($sql_code eq "")
        {
                &LogMessage("Error: SQL Statement not defined.");
                return;
        }

        $Isql = "${OcsBin}/isql -S${server} -U${user}";

        if ($OS eq "MSWin32")
        {
                $Isql = $Isql . " -P${password}";
        }
        $Isql = $Isql . " >> $OutFile";

$ENV{SYBASE} = "$Sybase";

        open (ISQL, "| $Isql");
        if ($OS ne "MSWin32")
        {
                print ISQL "${password}\n";
        }
        print ISQL ${sql_code};
        close (ISQL);
}

sub ExecSql
{
        # Executes an SQL Statement against the given server using sybperl.
        # Output is returned on a row by row basis for action by a passed function name.

        my $sql_code = $_[0];
        my $Server   = $_[1];
        my $User     = $_[2];
        my $Database = $_[3];
        my $Password = $_[4];
        my $Handler = $_[5];
        my $Counter = 1;
        my $DBHandle;
        my $RetCode = "END";

        if (!defined $Handler)
        {
                $Handler = "BaseAction";
        }

        $ENV{SYBASE} = "$Sybase";

        use Sybase::DBlib;

        if ($LogAll == 1)
       {
                print "Activating: $Server, $User\n $sql_code\n";
        }
        $DBHandle = new Sybase::DBlib $User, $Password, $Server;
        $DBHandle->dbuse($Database) or $RetCode = "Could not use $Database\n";
        $DBHandle->dbcmd($sql_code) or $RetCode = "Could not send $sql_code\n";
        $DBHandle->dbsqlexec        or $RetCode = "Could not exec $sql_code\n";
        $DBHandle->dbresults        or $RetCode = "Could not obtain results\n";

        while (@CurrentResult = $DBHandle->dbnextrow)
        {
                &{$Handler}($Counter, @CurrentResult);
                $Counter += 1;
        }
        return $RetCode;
}

sub BaseAction

{

my @Results;

my $Counter;

($Counter, @Results) = @_;

print "Row $Counter: ";

foreach $Result (@Results)

{

print "$Result\t";

};

print "\n";

}

sub EmptyAction

{

# NOOP

}

sub ObtainStatus
{
        # This is used for searching back against the Log File of
        # the executing script and looking for known problem output.
        # If the problem output is found, the status of the job is
        # marked with the problem result. You can set up as many
        # problem results and search strings as you like. If more
        # than one of the problem search criteria are met, the one
        # specified first will take precedence. An ingore string
        # can also be specified.

        my @Result;
        my @Search;
        my $Status;
        my $StatusMax;
        my $Max;

        my $GoodResult         = shift(@_);
        my $Ignore             = shift(@_);

        if ($Ignore eq "")
        {
                $Ignore = "NOIGNORE";
        }

        while ((@_ + 0) > 0)
        {
                if ((@_ + 0) < 2)
                {
                        print "Invalid number of arguments specified to ObtainStatus!\n";

return "OBTAIN STATUS ERROR";

} else {

                        push (@Result, shift(@_));
                        push (@Search, shift(@_));
                }
        }

push (@Result, $GoodResult);

$Max = $StatusMax = $#Result;

        $Length = $#Result;

        open ( DETAILS, "$LogFile");
        while (<DETAILS>)

{

if (/$Ignore/)

{

} else {

for ($i = 0; $i < $Length; $i++)

{
if (/$Search[$i]/i)

{ $Max = $i;
}

}

if ($Max < $StatusMax)

{ $StatusMax = $Max

                        }
                }
        }

close (DETAILS);

$Status = $Result[$StatusMax];

return $Status;

}

sub EnterRunbook

{
        # This functions takes standard arguments for the rb_insert
        # program and builds them into a call to rb_insert.

        my $RbIniFile   = $_[0];
        my $RbDetails   = $_[1];
        my $Status      = $_[2];
        my $Activity    = $_[3];
        my $ForceKey    = $_[4];
        my $ForceServer = $_[5];|
        my $GrnLevel1   = $_[6];
        my $GrnLevel2   = $_[7];

$RbCmd = "${JobDir}/rb_insert ";

$RbArgs = "-i '${RbIniFile}' ";

$RbArgs .= "-D '${RbDetails}' ";

$RbArgs .= "-s '${Status}' ";

$RbArgs .= "-a '${Activity}' ";

if ($ForceKey ne "")

        {
                $RbArgs .= "-k '${ForceKey}' ";
        }

if ($ForceServer ne "")

{

$RbArgs .= "-S '${ForceServer}' ";

}

if ($GrnLevel1 ne "")

{

$RbArgs .= "-d '${GrnLevel1}' ";

}

if ($GrnLevel2 ne "")

{

$RbArgs .= "-j '${GrnLevel2}' ";

}

open (RBINS, "${RbCmd} ${RbArgs} |");

while (<RBINS>)

{

if (defined(&fLogMessage))

{

&fLogMessage($_);

} else {

&LogMessage($_);

}

close (RBINS);

}

sub date

{
        # This procedure is for returning numeric formatted strings
        # which indicate the date and/or time at the point the procedure
        # was executed. These are used for prefixes and suffixes and the
        # like and are not designed to be in traditional date formats.

        my ($options) = @_;
        my ($month, $day, $hour, $minute, $second, $year);
        my ($Date) = "";

($second, $minute, $hour, $day, $month, $year, $x, $y, $z) = localtime(time);

        $month    += 1;
        $month    = sprintf("%02d",$month);   # 2 digit Month
        $day      = sprintf("%02d",$day);     # 2 digit day of month
        $hour     = sprintf("%02d",$hour);    # 2 digit 24 hour
        $minute   = sprintf("%02d",$minute); # 2 digit minutes
        $second   = sprintf("%02d",$second); # 2 digit seconds
        $year     = (1900 + $year);           # 4 digit year

        if ($options eq "")

{ $options = "mdyHMS"; }

@opts = split (//, $options);

foreach $Option (@opts)
{

                if    ($Option eq "H") { $Date = $Date . "$hour";   }
                elsif ($Option eq "M") { $Date = $Date . "$minute"; }
                elsif ($Option eq "S") { $Date = $Date . "$second"; }
                elsif ($Option eq "m") { $Date = $Date . "$month"; }
                elsif ($Option eq "d") { $Date = $Date . "$day";    }
                elsif ($Option eq "y") { $Date = $Date . "$year";   }
        }
        $Date = "$Date\n";
}

sub DefineOS
{
        # This procedure sets OS dependent variables for commands which
        # require specialized use.

        $OS     = $^O;
        $OSFlag = "Y";

if ($OS eq "MSWin32")

{

$WinNTRoot = "$ENV{SystemRoot}";

$Mail = "$WinNTRoot\\system32\\blat";

$Temp = "$ENV{TEMP}";

} elsif ($OS eq "aix") {

$Mail = "/usr/lib/sendmail";

$Temp = "/tmp";

} elsif ($OS eq "solaris") {

$Mail = "/usr/lib/sendmail";

$Temp = "/tmp";

} elsif ($OS eq "sunos" ) {

$Mail = "/usr/lib/sendmail";

$Temp = "/tmp";

} elsif ($OS eq "linux" ) {

$Mail = "/usr/sbin/sendmail";

$Temp = "/tmp";

} elsif ($OS eq "hpux" ) {

$Mail = "/usr/sbin/sendmail";

$Temp = "/tmp";
}
}

sub SetupAseOcsVersion

{
        # This procedure sets up the directory structure and required
        # environment variables based on the ASE/OCS version.

        if (substr(${SybVer}, 0, 2) eq "11")

{

$OcsBin = "${Sybase}/bin";

} elsif (substr(${SybVer}, 0, 2) eq "12") {

$ENV{SYBASE_ASE} = $SybaseAse = "ASE-12_" . substr(${SybVer}, 3, 1);

$ENV{SYBASE_OCS} = $SybaseOcs = "OCS-12_" . substr(${SybVer}, 3, 1);

$OcsBin = "${Sybase}/${SybaseOcs}/bin";

} elsif (substr(${SybVer}, 0, 2) eq "15") {

$ENV{SYBASE_ASE} = $SybaseAse = "ASE-15_" . substr(${SybVer}, 3, 1);

$ENV{SYBASE_OCS} = $SybaseOcs = "OCS-15_" . substr(${SybVer}, 3, 1);

$OcsBin = "${Sybase}/${SybaseOcs}/bin";

}

sub SetBatonHolder
{
$BatonHolder = $_[1];
}

sub GetBatonHolder
{

        my $Type        = $_[0];
        my $Database    = $INI{BTdb};
        my $Server      = $INI{BTsvr};
        my $User        = $INI{BTusr};
        my $Passwd      = $INI{BTpwd};
        my $SqlCode;

        if (lc($Type) eq "pager")
        {
                $Type = 'emailpager';
        } else {

$Type = 'login';

}

        $SqlCode = "SELECT   ${Type} ";
        $SqlCode .= "FROM     contacts ";
        $SqlCode .= "WHERE    baton in ('T', 'O') ";
        $SqlCode .= "ORDER BY baton";

        &ExecSql($SqlCode, $Server, $User, $Database, $Passwd, 'SetBatonHolder');

        return($BatonHolder);
}

sub SendPage

{

        my $Recipient   = $_[0];
        my $Subject     = $_[1];
        my $Body        = $_[2];
        my $Sender      = $_[3];

        $Recipient      =~ s/\n+//;
        $Subject        =~ s/\n+//;
        $Sender         =~ s/\n+//;

        if (lc($Recipient) eq "baton")
        {
                $Recipient = &GetBatonHolder('PAGER');
        }
        &MailMessage($Recipient, $Subject, $Body, $Sender);
}

# End dba.pl

Filename: db_spy

Purpose: Save current processing information.

#!/bin/sh
# --------------------------------------------------------------------------------------
# db_spy Collect ASE information about currently running processes
# --------------------------------------------------------------------------------------

$RUNSQL <<-EOF | egrep -v "return status" >> $LOGFIL

$USRPWD

SET NOCOUNT ON