One of the core features of GEM is its monitoring and maintenance scripts. These scripts perform the systems monitoring and alarming, perform the system administration operations you define in your maintenance plans, and provide useful routine maintenance activities. The scripts are customized to your environment by the GEM configuration manager. For version 1, some of these scripts may involve hand definition of a configuration file.

The configuration manager will create scheduled for some of these jobs on your Windows scheduler and will create a Unix crontab file for jobs you indicate you wish to run on Unix. These tasks fundamentally entail a call to one of the command line scripts located in the ADMIN_SCRIPTS directory, but the GEM hides this from you. GEM creates a set of .bat and .ksh driver scripts that have obvious names to be the scripts that you actually run or schedule.

These scripts are stored in the win32_batch_scripts and Unix_batch_scritpts directories. Within those directories are 4 subdirectories as follows:

Simply put, each script is created as a .ksh on Unix and as both a .bat and .ksh script on Windows. Copies of the script exist in an interactive and batch directory (or a plan_interactive/plan_batch directory). The batch scripts redirect output to locations in the data tree (so you cant see it) but are identical to the interactive scripts. When you run a scheduled task, use the batch version, and when you run interactively, use the interactive version (you want to see output if you are running interactively). The plan_* directories store pregenerated maintenance plan scritps. The plan directories were separated from the regular directories because the tree becomes hard to navigate when you get large numbers of servers (and a large number of maintenance plans).

The GEM Enterprise Manager is the graphical user interface for GEM. It is a modular system that uses plugins for all functionality. Plugins use single console for all your systems. It uses perl DBI - a database independant api - and provides you with a seamless api. The trick here is the RosettaStone.pm perl module, which basically maps database independant requests (get me locks/logins/space) to database specific ddl. This gives you consistency. The GEM. is designed to use plugins, which are basically small perl modules that conform to an api. Most of your services are provided by the other GEM libraries, so these modules basically define what user interface elements they want (menus, how they want their explorer to look, right click functionality) and then manage a Tk frame however they want. The goal here is to have third parties develop plugins that add functionality. And the nice thing here, is that this can be business specific functionality (ie. a trade or order volume report). The GEM plugins are managed from a central store on the internet which is available to you to distribute your plugins in the same manner that CPAN does for perl modules.

INSTALLATION GUIDE

The following guide describes how to install GEM.

If you are planning to using GEM, please contact Sql Technologies by email. We are interested in making your GEM Installation a success, and will provide installation support. We are aiming for full customer satisfaction and will work with you to make this product a success.

Pre-install check list

• PICK 1 or 2 SYSTEM(s) TO USE AS GEM MONITORING SERVER(s)

  GEM manages your systems from central monitoring systems. A windows systems is used to manage your windows databases. A unix/linux server is used for your unix servers. If you have both (a "Hybrid Environment") windows and unix based databases, you will need 2 monitoring systems.

  These systems run a lot of perl batch jobs. It is recommended that you use dedicated systems for your production GEM monitoring servers. For your initial trial setup, use a development system.

  On windows monitoring systems, we suggest you have access to a windows login OTHER than the login you normally use for login. On windows, scheduled GEM batch jobs might result in the pop up of dos cmd boxes (which is annoying). Scheduling the jobs using a 'batch' windows account prevents this.

  You will need appropriate SQL Server and/or Sybase/Oracle client software to be installed on your monitoring servers.

• PICK A HOME FOR THE GEM CODE

  Identify the directory you are going to install GEM into. This is only tricky if you have a Hybrid Environment, in which case your admins need to create a samba share such that a single directory appear in both unix/linux and windows. When GEM is installed on a samba share, the same code works unchanged to monitor both your Unix and Windows environments seamlessly.

• INSTALL & VERIFY perl 5.8.1 OR LATER

  Each monitoring station requires perl to be installed at version 5.8.1 or later. This can be verified by running the command perl -v (prints the version number).

  On windows, we recommend the latest Activestate Perl from http://www.activestate.com. Use the default installation options.

  On UNIX, binary releases can be downloaded from http://www.cpan.org. You can have your unix admins install/provide perl for you or can install your own version of perl in your own home directory. The second approach has the advantage that you have control, and will not need permission of the unix administrators to add/update modules.

• ADD PERL MODULES

  Verify availability of the perl DBI module and associated DBD perl modules. DBI and DBD::ODBC are required on windows servers. DBI and Native DBD Drivers (DBD::Sybase, DBD::Oracle) are required for the perl on your unix monitoring servers.

  On Windows, install these modules via :

  [StartKey] -> [Run] and then type ppm install DBI
  and
  [StartKey] -> [Run] and then type ppm install DBD::ODem/em.

  If your company has a firewall/proxy set up such that the ppm commands fail we suggest:

  Bring up a browser and go to http://ppm.activestate.com/PPMPackages/zips/ - select your version and go to the Windows subdirectoy and download the latest zip file. The file will contain 3 files when extracted - a .ppd file, a readme, and a .tar.gz file. To install this ActiveState PPM package, run the following command in the current directory:

  ppm install DBD-ODBC.ppd

  You should download both the DBD::ODBC and DBI libraries. The latest versions (as of October 2007 are 1.13 and 1.58 respectively.

  On Unix, the DBI and DBD::Sybase modules must be installed by hand. You may also wish to install DBD::Oracle, although oracle support is currently limited with full support scheduled for 3Q08. Module installation on unix may require administrator help (the make install step requires write access to the perl library directories which are write protected unless you are using a local version of perl.

• IDENTIFY SCRIPT CAPABLE DIRECTORY ON YOUR WEB SERVER

  While GEM does not "technically" require running scripts on an web server, several of the interfaces were designed primarly for web access. To run these scripts, you must create a 'script enabled' directory on your web server. GEM provides perl scripts which, when deployed to this directory will provide web based features. The primary interfaces provided at this time are a monitoring and reporting tool for your database servers. You should secure this web directory as you see fit for information of that type.

• CREATE A MONITORING DATABASE

  GEM requires a small monitoring database. Place this database on Sybase if you have a Hybrid or Unix Only environment and on either Sybase or Microsoft SQL Server in windows only environments. This database will store enterprise monitoring information and must be accessible from all your monitoring servers. If you have Unix Systems yet wish (for some reason) to use a SQL Server as your repository, you can install FreeTds and the unix verison of DBD::ODBC.

  • Create A Database Named gemalarms with size=200MB
  • Turn on select into and truncate log on checkpoint database options
  • Create login named gemalarms with password gemalarms
  • Add login gemalarms to database gemalarms in group public (not a dbo account)

  use master
  go
  create database gemalarms on data_device_6=200
  go
  use master
  go
  sp_dboption gemalarms,"select",true
  go
  sp_dboption gemalarms,"trunc",true
  go
  use gemalarms
  go
  checkpoint
  go
  sp_addlogin "gemalarms","gemalarms","gemalarms"
  go
  sp_adduser gemalarms
  go
  

• IDENTIFY THE DATABASE SERVERS YOU WANT TO MONITOR

  If you are trialing GEM, we recommend installing on 4-6 database servers on 4-6 separate sytems and that the trial be either 'all unix' or 'all windows' databases. You will require the following rights on these database servers:

  You need windows administrator access your windows database servers

  • needed to monitor disk space, server processes, and event logs
  You will require sa_role access for all your Sybase database servers
  • for installation of the stored procedure library
  • you can get away with non-sa access for monitoring
  You will require "no-password" access via ssh or rsh for your unix systems
  • for access to log files
  You will require sybase unix account access for your Sybase database servers.
  • this is used to read sybase event logs

• SET UP YOUR ODBC DATA SOURCES OR interfaces FILE

  Ensure that all the database servers identified in the last step have span>ODBC entries (Windows Monitoring Box), and have appropriate Sybase interfaces file (and Oracle tnsnames.ora) entries. The configuration process (configure.pl) will automatically discover your servers based on these values.

• SET UP SECURE INTRA SYSTEM COMMUNICATIONS (UNIX)

  All UNIX systems involved with GEM must be accessible from the unix monitoring system by ssh/scp (preferred method), rsh/rcp, or standard ftp. The account you will be using to schedule the batch jobs and to run gem must be able to get into these systems without passwords (except in the case of ftp where a password is ok).

  monitor ebarlow: ssh sybase@sybprod
  Last login: Wed Apr 2 11:37:29 2008 from monitor
  $
  

Suggestions on your first implementation

Database administrators can be, and should be, a cautious lot. We obviously roll out a tool like GEM to a single test box and install the client on our personal workstation. Lowest risk configuration and all that. An installation like that totally and completely buys you nothing. GEM will be of no value. That is totally and completely the wrong way to roll out GEM.

The correct way to roll out GEM is to implement it on between 4 and 6 test and dev servers at once using a 'server' class box as a monitoring station (it does NOT need to be a dedicated box but it runs a bunch of perl scripts). Obviously dont trial GEM on anything production (that includes your workstation). Your Trial needs this many systems to let you to see how the GEM software works. Plus, GEM is a LOT of effort to implement, so you might as well get some use out of your GEM trial.

Finally, ensure that you have defined ALL items on the GEM pre-install check list. For example, GEM loses half its value in a unix environment if you dont have 'no password' access via either ssh or rsh connectivity to your unix systems. Simarly, "ADMINSITRATOR GROUP" rights are required for direct access to your Windows servers log files. You need these. Additionally, it is highly strongly very suggested that you have a web server with a script capable directory into which you can place our cgi perl interface. While we do have a TK version of the interface, the web interface is the one you should use.

Presuming you have a reasonable network configuration, GEM has a pretty low footprint on everything but the monitoring host system. Network impact is mostly semi hourly fetches of your database error logs. GEM does install our system stored procedures on all your database servers. They are a nice package and you will like them. On the monitoring system, GEM runs a bunch of perl batch jobs - and frequently multiple jobs will run at the same time. We recommend multi processor systems as your monitoring systems, but most 2-4 year old server class systems will be fine (find some depricated system). Linux is a good choice for a monitoring system to monitor unix database.

As far as your database servers go, GEM has the experience to be a good citizen. GEM is designed by active dbas, not a lab by developers. Your developers and users should have not have any noticable degredation in performance as a result of GEM.

A good solid burn in environment for GEM should have between 4 and 6 database servers and 4 to 6 different host systems (of course nothing production should be touched). It would be slightly simpler to implement the trial if running all windows or all unix databases.

Download & initial setup

• DOWNLOAD AND EXTRACT GEM

  You should now download and extract the GEM software from http://www.edbarlow.com/download.pl.

  You will need a passcode to download the software which can be recieved by mail from SQL Technolgies.

  Extract into the directory identified in the pre-install checklist. You should need to install into only one location regardless of whether you have a hybrid or non-hybrid environment.

  The GEM download file is a standard compressed tar file and can be extracted with winzip (windows), gnu tar (tar xvzf gem.tgz), or with gzip -d gem.tgz; tar xvf gem.tar. If you install GEM into a subdirectory, do not use a directory that includes space characters (ie. Program Files). White space in the directory name is not a tested option.

• SET YOUR DISPLAY ENVIRONMENT VARIABLE IF UNIX

  Many of the GEM tools use perl/Tk for a user interface. On windows this works without support, but on unix this requires some type of X terminal manager (like exceed). On UNIX, you must set the environment variable DISPLAY correctly.

  EXPORT DISPLAY=your_ip_address:0.0.

  Unix users will also require an X terminal manager installed on their workstation. Your infrastructure support can help you install this if you are not allready set up.

• VERIFY YOUR PERL INSTALLATION

  perl troubleshoot.pl

  Use the program troubleshoot.pl in the root of the installation tree to test for the presence of necessary perl libraries. troubleshoot.pl is the GEM installation trouble shooting program and it can be run to diagnose any GEM issues - a successful run indicates that your configuration is stable.

• RUN configure.pl

  See the section on configure.pl for details on what this program does.

configure.pl GEM Configuration Tool

configure.pl is the GEM configuration utility. This utility will build the configuration files (stored in the conf subdirectory) and runs some basic setup tasks for GEM. The GEM Configuration Tool is quite intelligent, and will survey your systems to give you a good baseline that should require minimal tuning to work within your environment.

Some of the things configure.pl does include:

• Create Configuration Files

  One primary output of configure.pl is the configuration files stored in the conf directory. These files can be hand edited once GEM is set up.

• Rebuild of all the code

  Changing the hashbangs (the first line of the file on unix is known as a hashbang and defines the command line interpreter to use), removing Control-M (dos newline) characters from the files, and modifying library paths.

• Creation of the GEM Batch Jobs

  configure.pl also creates the GEM Batch Jobs. On a win32 monitoring station, GEM can schedule these jobs. On a unix environment, we create a crontab.txt file they can be schedule via cron.

• Monitoring Setup

  The initial database installation is done via configure.pl

• Initial Build

  This program also will generate an initial version of the GEM console and prepopulate the alarm system with alerts and reports relevant to your environment.

  Important notes

  • Configure.pl creates a file logs/configure.log which includes both console messages AND diagnostic messages. If you have a problem please look at this file. It contains all output including output that should be only produced with configure.pl --DEBUG.

  • Work through the tabs from left to right.

  • "Save Configuration Changes" writes your configuration files. While some operations (adding stored procedures to servers) are permanent once completed, the majority are not. It is important to save before you quit.

  • Leave the backups tab alone for your first pass

  How to run configure.pl

  perl configure.pl

  If you wish to see diagnostic messages, you may run

  perl configure.pl --DEBUG

  A log file of the run will be placed in the logs subdirectory.

  FOLLOW THE FOLLOWING STEP BY STEP INSTRUCTIONS TO COMPLETE YOUR INSTALLATION

  The welcome tab

  PURPOSE: License Details and Acceptance

  This tab gives licensing options for GEM. Select "I AGREE WITH THE LICENSE" to continue after you have read all the information provided when you click on the various buttons on this page.

  The GEM Help button will help guide you through your install.

  All GEM code is copyright (c) 1995-2007 by SQL Technologies=head1 The Register Tab

  PURPOSE: Basic configuration setup plus register your product.

  • Enter Administrator Info. This information is never shared so make it correct.

  • Select Environment, Product, and License. Running on a samba share is appropriate in Hybrid Windows/Unix environment. Select Free GEM for product trials. Contact barlowedward@hotmail.com for a product key for "Full Gem".

  • Select Preferred Connect Options to define the legal system connectivity options.

  • Click "REGISTER YOUR VERSION" to register your product.

  The paths tab

  [This tab is trivial if you have an All Windows or All Unix environment.]

  • Enter FULL Path(s) to your version of perl. eg. /usr/local/bin/perl or C:/perl/bin/perl.

  • Enter All legal paths to the GEM top level directory to define paths to required libraries. On a samba / hybrid installation where /apps/gem maps to c:/gem on win32 host win2, you would have '/apps/gem' (the Unix Code Location) and '//win2/c$/gem' as the NT Code Location, and Alternate #1 would be 'C:/gem'. Alternate #2 will be blank.

  • Enter Unix/Linux Web Browser Its probably /usr/local/bin/firefox. Skip if you dont know it.

  • Enter Sybase Home Directories in a list so GEM knows what paths to search while surveying remote systems.

  PURPOSE: Ensure Mail Connectivity

  This tab is only required on Windows servers. For these servers, you need to provide your SMTP mail server name. If "Test Mail Install" should send a test message to your GEM Administrator Mail Address (entered earlier).

  The servers tab

  PURPOSE: Register your Databases & Systems with GEM

  • Access To Databases must be via 'sa' or an account with similar roles/permissions. GEM does not support blank/empty administrator passwords.

  • Access To Unix Systems must be via ssh, rsh, or ftp using an account with reads permissions on the DBMS log files in $SYBASE/$ORA_HOME. The ssh or rsh connection should not require a password.

  • Access To Win32 Systems is via native authentication - the account must read Event Logs and Administrative File Shares (ie. requires administrator).

  • The Full GEM Add Server Wizard creates a best guess at your environment - prompting for logins and passwords appropriately. To save time, it guesses passwords by reusing previously entered ones. This wizard only registers NEW servers and is re-runnable.

  • The Free GEM Add Server Wizard For FREE Gem provides a simple data entry screen to enter up to 4 DATABASEs.

  • The Server Type Buttons allow you to define category (only PRODUCTION/CRITICAL systems are page-able), change passwords, and deregister servers

  • The Quick Add Button allows you to register a single Database or System.

  • The UnixComm & WinComm columns allow you to specify preferred communication methods from Unix/Windows Monitoring stations respectively.

  The files tab

  PURPOSE: Identify Database Home Directories & Important FIles

  This tab contains rows for each UNIX/Linux system, listing survey results (collected earlier) in blue and configuration directives in black. If survey results look incorrect, correct the configuration directives (with the add/delete buttons) and rerun the surveys.

  • Sybase ASE Server $SYBASE Directory is a valid sybase server directory and is searched for Error Logs, RUN_* files, and other config information.

  • Application Log File (unused in v1)

  • Ignore This RUN_* File directives that the Sybase server RUN_* file is unused and should be "ignored" to prevent confusion about what runs where.

  • Operating System Log File (unused in v1)

  • Sybase Open Client $SYBASE Directory are surveyed only for interface file consistency checks.

  • Oracle ORAHOME Directory (unused in v1)

  • SQL Server Maintenance Plan Logs (unused in v1)

  When completed, you consider clicking "SAVE CONFIGURATION CHANGES"=head1 The Connect Tab

  PURPOSE: Test connectivity and survey your systems.

  • Page-Top Buttons allow you to can see servers by type.

  • Connect To All to test connectivity. Windows GEM requires odbc settings for all databases and native administrator access for systems. Unix GEM requires normal database access (interfaces file for sybase) plus host access via ssh (preferred), rsh, or ftp. Fix up any servers you cant connect to.

  • Survey All to run a survey of all your servers. Normally, surveys are scheduled but we need some initial data. Click "Dont Skip" to not skip servers which have been recently surveyed.

  The backups tab

  When you create your servers, it will also create two backup plans for you: one that has the same name as the server and the other which has _WEEKEND appended to the server name. Data on your plans is stored in the configure.cfg file and is hand editable. Please see documentation on the GEM Maintenance And Backup System.

  • For initial setup, you can ignore this section

  The console tab

  PURPOSE: Define Console Special Behaviors

  • For an initial setup or trial, you can ignore this section

  • This page allows you to copy the console to another location. You may be interested in doing this for security reasons. By default, the CONSOLE Web Pages are created the data/CONSOLE_REPORTS directory but these reports can be published to another location (perhaps on your web server). Most users will NOT wish to do this.

    You may copy the files via unix copies (ssh, rsh, ftp - select the Copy using FTP button) or to a local directory (like //NEWSERVER/c$/web/gem). Some users may want to do thsi copy for security reasons - to allow non administrators to use the GEM console. This keeps those same users away from the GEM directory tree which stores other highly sensitive data.

  The alarms tab

  PURPOSE: Set Up Alarms & Monitoring Systems

  • Fill in the Alarm Database Server Info with information about the 200MB (approximate size) alarm database you set up before running configure.pl (see pre-requesites). The email address is arbitrary and all alarms will be mailed using that as a reply-to address. It is reasonable to set this to an INVALID mail address (alarms@yourcompany.com or Gem@yourcompany.com) because you wont ever reply to the messages.

  • Install/Upgrade Alarm Database will create alarm database tables & procedures

  • Validate Alarm Database checks the database for sanity and version.

  • Fill in Web Server information including the map of hostname/filename to alarming url. Once this is done click Populate Local cgi-bin to copy formatted scripts to .../cgi-bin and Copy to Web Server to copy those files to the web server.

  The install software tab

  Now would be a good time to Save Configuration Changes!

  Click the "Install Software" button to run the numbered steps listed below that button.

  • Reformat Code Removes Control-M's and Sets Hashbangs

  • Create GEM Batch Scripts creates your batch scripts. In a hybrid environment run this on UNIX last - so none of the files have control-M characters embedded

  • Backup Manager sets up default directories for the backup system

  • Modify Console Scripts

  The post install tasks tab

  • Create a crontab file for your unix installation and schedule it.

  • Snapshot your windows servers services to create conf/pc_service.dat. This basically creates a list of all the running services on all your windows boxes for future monitoring. You will be notified if any services go down. Assuming your environement is currently stable, a current snapshot will give you a pretty good listing of what needs to be monitored.

  The scheduler tab

  PURPOSE: Define Scheduling and Actually Schedule Batch Jobs

  • Look over the jobs as defined to ensure that they are correct - you can use the default Frequencies by default

  • Schedule on this server (Win32 Monitoring Only) provides a native interface to the windows task scheduler. Provide an account & password which has access to windows event logs, windows disk administrative shares, and windows services (probably requires adminstrator).

  • Create Unix Crontab will create a crontab.txt file in the gem home directory that is appropriate for unix cron. To install into cron:

       export EDITOR=vi
    
       crontab -e
    
       <vi window pops up>
    
       :r crontab.txt
    
    

    Be sure that the account you schedule this on has a .forward file so any errors will not be routed! A .forward file is a file in your HOME directory that contains mail addresses.

  
  

  POST INSTALLATION STEPS

  Other things to do

  Once you are done with your GEM installation, there are a variety of post-installation tasks . This checklist is designed to help you with these tasks.

  CLEAN UP UNIX SCRIPTS DIRECTORY

  There are outstanding issues with chmod() and dos newline removal that impact Hybrid environments. Specifically, you must run configure.pl on unix AFTER you run configure.pl on windows. This is because there is no way to get windows to use just a standard newline instead of CR/newline (so all files will have a ^M in them if you run on windows last).

  You should also run the Batch File creation on UNIX last. If you ran your Win32 install after your Unix install, go to your unix server and run perl upgrade.pl to fix these issues.

  Modify your Password Files

  The password files are located in conf/*_password.dat and are hand editable.

     sybase_passwords.dat
     oracle_password.dat
     pc_password.dat
     unix_passwords.dat
     sqlsvr_password.dat
  
  

  All configuration files are self documented. An archive of your old configuration files is kept in conf/saved_files

  If you add new databases or systems, recreate & reschedule your Batch Scripts. Most scripts work against all servers in the configuration files, but some scripts work on a specific database (specifically the ones with the Database name in the file name).

  Windows Directory Cleaner

  One of the most horrific tasks of the windows DBA is disk space cleanup. Something is *always* running out of space... GEM automates this task for you.

  The file conf/Win32_Cleanup.dat contains a list of disk space Cleanup instructions for the windows cleanup batch job. It is suggested that you customize this file to include your tran/archive log directories, your application log directories, and any other directories that fill up on your systems. A best practice is to add rows to this file over time, every time the windows disk space monitor spots a directory that is filling up. The lines in this file conform to the arguments of ADMIN_SCRIPTS/bin/purge_old_files.pl, and include --hours and --days to identify the keep times, --dir to identify the directory to purge, and either --compress= or --del (to identify what to do with the old files). See the syntax of purge_old_files.pl for other options.

     # FILENAME: Win32_Cleanup.dat
     #
     # USED BY:  Batch job Win32FileCleanup
     #
     # SYNOPSIS: Directives for cleaning up win32 drives.  This file is
     #    identical to Unix_Cleanup.dat but for the --compress (Win32 only)
     #    argument which is missing and the required --host argument (Unix Only).
     #
     #
     # FORMAT  : HOSTNAME drive drive drive.
     #
     # DETAILS : The arguments are the arguments to purge_old_win32_files.pl
     #
     #  --host=<host>
     #  --hours=<hours>
     #  --days=<days>
     #  --dir=<dir>
     #  [--del]                - do you wish to delete the file?
     #  [--done]             - only work on files with .done extension
     #
     # EXAMPLE FORMATS:
     #
     # Delete 14+ day old files on host h1
     # --days=14 --dir=//adsrv012/c$/oracle/admin/SCPROD/udump --del
     #
     # Delete files with .done extension older than 2 days old
     # Note: this will delete .done.gz files and .done files
     # --days=2 --dir=//SRVR001/d$/logshipbuffer --del --done
     #
     #######################################################################
     #
     # START RECOMMENDED PURGES
     # --days=7 --dir=C:/gem/data/logs -del
     # --days=7 --dir=C:/gem/data/batchjob_errors -del
     # --days=7 --dir=C:/gem/data/GEM_BATCHJOB_LOGS -del
     # --days=7 --dir=C:/gem/data/CONSOLE_REPORTS -del
     # --days=7 --dir=C:/gem/data/cronlogs -del
     # --days=7 --dir=C:/gem/data/errors -del
     # --days=7 --dir=C:/gem/data/backup_reports -del
     # --days=7 --dir=C:/gem/data/html_output -del
     # --days=30 --dir=C:/gem/data/BACKUP_LOGS\*\sessionlog -del
     # --days=30 --dir=C:/gem/data/BACKUP_LOGS\*\errors -del
     # END RECOMMENDED PURGES
  

  Unix Directory Cleaner

  The Unix_Cleanup.dat file will control unix file/directory cleanups. It is similar to the Windows cleanup file but for the addition of the --hostname argument.

  port_monitor.dat

  Port Monitoring is a quick availability test - and a good way to find out if a database or system is down. The file conf/port_monitor.dat contains a list of Hosts and Ports to Monitor.

  This file is autogenerated by configure.pl but will probably require hand editing.

  Simple format:

    # hostname                      PORT SERVER   DESCRIPTION
    SRVR001                        1433 SRVR001   Microsoft SQL Server
    SRVR007                        1433 SRVR007   Microsoft SQL Server
    brokersql                      1433 BROKERSQL Microsoft SQL Server
    mail1                            25   Sendmail on mail1
  

  Sybase Replication Monitoring

  The File conf/Replication.dat is used by the Sybase replication server monitoring scripts and will need to be popluated with your Rep Servers and Routes.

     # FILENAME: replication.dat
     #
     # SYNOPSIS: Lists your sybase replication server information
     #
     # FORMAT:
     #
     # REPSERVER $NAME $LOGIN $PASSWORD $THRESHOLD_MB
     # REPROUTE  $SOURCE_SVR $SOURCE_DB $DEST_SVR $DEST_DB $REPSERVER
     #
     # EXAMPLES (350MB Thresholds):
     #
     #  REPSERVER REP1DR_RS sa <pass> 350
     #  REPSERVER REP2_RS   sa <pass> 350
     #  REPSERVER REP1_RS   sa <pass> 350
     #
     #  REPROUTE  SYB1 my_db SYB2   my_db REP2_RS
     #  REPROUTE  SYB1 my_db SYB1DR my_db REP2_RS
     #
     # ONCE YOU SET THIS FILE UP - PLEASE RUN
     #      ADMIN_SCRIPTS/monitoring/SybRepMonitor.pl --ACTION=INSTALL
     # MONITORING IS HANDLED AUTOMATICALLY BY THE GEM BATCH SybRepMonAgent
     ##############################
  

  There are two programs that check replication. The batch SybRepServerChecker will check all your rep servers to be sure that no routes are suspended, that disk space is reasonable, and that there are no other serious errors. There are also three batches, SybRepMonAgent, SybRepMonCheck, and SybRepMonReport that compute latencies of your replication, and will warn you when replication falls behind. These three batch programs call SybRepMonitor.pl. This program takes an argument --ACTION.

  To initialize this system, you need to set up your configuration file and then run myperl SybRepMonitor.pl --ACTION=INSTALL

  After the system is INSTALLED, the above mentioned batches will perform your monitoring automatically. Below is the syntax for SybRepMonitor.pl:

     Usage: SybRepMonitor.pl --ACTION=INSTALL|UNINSTALL|MONITOR|REPORT|CHECK
          [--SERVER=xxx] [--DEBUG] [--OUTFILE=file]
  

          if --ACTION=INSTALL will install
                  --REBUILD redoes table creates
          if --ACTION=CHECK will check latency vs --ALARM_MINS
                  - mail to --ALARM_MAILTO as needed
          if --ACTION=REPORT will create a report
                  - will print data for --REPORT_HOURS hours
                  - will only show rows > --REPORT_THRESH seconds
                  - will aggregate based on --BUCKETSIZE
                  - will print HTML output if --HTML is passed
          if --ACTION=MONITOR will insert rows into all source databases
                  - will insert --ITERATIONS rows
                  - sleeps --FREQUENCY secs between each insert
  

  Adjust Alarming Thresholds

  You may not wish all your alarms to work at the same thresholds - preferring to set some thresholds individually. Generally speaking, this is most needed when some resourse is nearing "full" and generating alarms, but you can not fix the problem immediately - intending to fix the problem later. The conf/threshold_overrides.dat file allows you that control.

     # FILENAME: threshold_overrides.dat
     #
     # SYNOPSIS: Monitoring Thresholds
     #
     # DETAILS : This file permits you to turn off alarms and to control their
     # severity.  This is a Comma Separated File
     #
     # SAMPLE FORMAT:
     # <system> <monitor_program> <level> <threshold> <optional subsystem>
     #
     # monitor_program=UnixDiskspace|PcDiskspace|SybSpaceMonitor
     #                         |SpaceMonitorSqlsvr|ThresholdManager
     # level=CRITICAL|ERROR|WARNING
     #
     # remember to reset higher priority thresholds so there is no overlap - ie if you
     # set ERROR to 98%, then you should set critical to something larger (say 99%).
     # The programs may act wierd if the CRITICAL threshold was less than the ERROR threshold.
     #
     # By default - SybSpaceMonitor & SpaceMonitorSqlsvr will use 90/92/95% as
     # their default thresholds
     #
     # Replication thresholds are in minutes.  For replication,  you can put a
     # blank target name in if you want but not a blank source name
     #
     # EXAMPLES
     # eg.  directfeed,UnixDiskspace,WARNING,80,Disk: /var
     # eg.  direct1,UnixDiskspace,WARNING,80,Disk: /var
     # eg.  direct2 ,UnixDiskspace,WARNING,80,Disk: /var
     #
     # eg.  SYBSVR1,SybSpaceMonitor,WARNING,80,db1
     # eg.  SYB1,SybSpaceMonitor,ERROR,95
     # eg.  SYB1,SybSpaceMonitor,ERROR,95,clientdb
     #
     # eg.  ADSRV057,PcDiskspace,ERROR,98,d$
     # eg.  ADSRV057,PcDiskspace,CRITICAL,99,d$
     #
     # eg.  SERVERNM,ThresholdManager,CRITICAL,99,logsegment|default
     #
     # EXAMPLE REPLICATION STUFF
     # eg.  SOURCESVR,SybRepMonCheck,CRITICAL,30,TARGETNM
     # eg.  SOURCESVR,SybRepMonCheck,ERROR,15,TARGETNM
     # eg.  SYB1,SybRepMonCheck,CRITICAL,80,IMAGSYB2
     # eg.  SYB1,SybRepMonCheck,CRITICAL,80,
     # eg.  SYB1,SybRepMonCheck,ERROR,60
     #
     #########################################
  

  The sybase threshold manager

  Another separately installed GEM component is the Sybase threshold manager. This program (ADMIN_SCRIPTS/bin/threshold_manager.pl) installs customized sp_thresholdaction procedures into your databases and allows you to find peak usage (most important for log file sizes). This proactive utility will hopefully prevent you from running out of log space - because you will have been warned in advance in such a way that you can expand capacity before there is a production alert.

  The sp_thresholdaction procedures we install are simple reporting versions that insert peak usage data into a small table in sybsystemprocs. You can run a report showing peak usage of data and log segments in your databases which is the Threshold Report you can view in the console.

  This procedure needs to be installed manually by running

  ADMIN_SCRIPTS/bin/threshold_manager.pl --ACTION=INSTALL

  after you have registered your servers. One warning. If you increase the *size* of any of your databases, you will need to rerun the above installation command. Sybase thresholds are based on free pages and the percentages are therefore set when you run the installation. So, if you double the size of a database, your 60% threshold will be firing at 30% and recording it as 60% full! Just reinstall and you will be ok!.

  Other commands of interest:

  ADMIN_SCRIPTS/bin/threshold_manager.pl --ACTION=CHECK

  ADMIN_SCRIPTS/bin/threshold_manager.pl --ACTION=REPORT

  This feature only works with Sybase

  Snapshot Win32 Services

  This information is stored in conf/pc_service.dat and can be created from configure.pl using "Snapshot Win32 Services" on the Post install tasklist. The file can also be created by the unscheduled batch file PcServiceCheckerInit.ksh.

  You will probably need to hand edit this file - the snapshot is at a point in time and some services may get turned off or just dont stay up. After you have created this file, simply remove lines which get flagged by the monitors yet are not important.

    cd win32_batch_scripts/interactive
    PcServiceCheckerInit.ksh
  

  Install web server scripts

  The alarms tab will populate the directory cgi-bin with runnable web scripts. The button to actually distribute those scripts to your web server is currently unimplemented. You will need to copy the scripts by hand.

  This is made easier by the fact that the scripts have been modified to run in your environments, but it is possible that the scripts do not execute.

    a) copy the contents of cgi-bin to the web server scripts directory you identified for your use
    b) chmod 755 these scripts to make them executable (unix only)
    c) try to run mimi.pl when in that directory.  Mimi is a cgi-bin script - so it will hang asking for parameters if it runs successfully.  It might also give you a command not found or library not found message.
    d) you might need to change the hashbang (first line) to point to your perl
    e) try running printenv.pl from your web browser... this is a very simple script that should always work.
    f) try running mimi.pl from your web browser
  

  It is beyond the scope of this document to discuss web server setup. All the directory requires is script access.

  The top of these scripts should look like:

     #!/usr/local/bin/perl-5.8.1
  

     use lib qw(/apps/sybmon/dev/lib)
     my($COMPANYNAME)="YOUR COMPANY";
     BEGIN {
        if( ! defined $ENV{SYBASE} or ! -d $ENV{SYBASE} ) {
           if( 1==2 ) { print 'doh'; }
           } elsif( -d "/export/home/sybase" ) {
              $ENV{SYBASE} = "/export/home/sybase";
           } elsif( -d "/apps/sybase" ) {
              $ENV{SYBASE} = "/apps/sybase";
           }
        }
     }
  

  The paths, company name, sybase directories, and perl version have all been customized. Make sure they all look ok.

  Test this script through your web browser.

  RUN monitor.pl and eventviewer.pl

  perl monitor.pl
  perl eventviewer.pl

  Two modules are currently shipped with GEM - an event viewer and a monitor. Both are located in the root directory. To run, use /monitor.pl. Under windows, you should be able to just double click on these programs to run them and can create desktop shortcuts as needed.

  Run fix_db.pl to for initial db config errors

  perl ADMIN_SCRIPTS/bin/fix_db.pl -Ax

  This is a cute little database configuration checker. You might as well run it. It shows dboptions you might wish to correct and identifies common configuration problems. To correct these problems remove the -x (noexec) option.

  ADDITIONAL CONSOLE REPORTS

  You can add customized reports to the console easily using conf/console.dat. These reports can be pretty much any command or sql statement. Check out the file for the syntax.

  SCHEMA GENERATION

  It is recommended that you create object level backups of your server DDL once per week. GEM provides the excellent script dbschema.pl (written by Michael Peppler and David Owen) in the ADMIN_SCRIPTS/bin directory to facilitate this process. Because this script is written in SybPerl (not DBI::Sybase), we do not provide an integrated approach to creating these audits. I recommend you back up your ddl once per week on your production servers.

  DNS Montitoring

  You can set up conf/dns.dat and then do dns monitoring with

  0,20,40 * * * * /usr/bin/nice /usr/local/bin/perl-5.8.1 /apps/sybmon/dev/ADMIN_SCRIPTS/monitoring/dnsmonitor.pl --OUTFILE=/apps/sybmon/dev/data/html_output/dnsmonitor.pl.txt

  Scheduling the batch jobs

  Win32 Scheduling

  The gem configuration utility will schedule your jobs on win32 monitoring stations.

  Unix Scheduling

  At 2 points in configure.pl, you can create a unix crontab file. This file is named crontab.txt and is placed in the GEM root directory. You should schedule this file using yoru crontab on unix. To do this run

     export EDITOR=vi   # presuming u like vi
     crontab -e
     <vi window>
     :r crontab.txt
     :wq
  

  You should also place a .forward file in your home directory to redirect mails to your account.

  Your own scheduled tasks

  Put the output of any of your routine scripts into data/html_output or data/cronlogs to have the reports show in the GEM console. This is a convenient spot to redirect output in any case. The filename itself is used to generate the console report.

     # the following job compare a server to its dr copy
     1 22 * * * /perl/bin/perl /apps/sybmon/dev/ADMIN_SCRIPTS/bin/datacompare.pl -Usa -SSYB1 -Pxxx -sSYB1DR -pxxx -uxxx -h > /apps/sybmon/dev/data/html_output/Rowcount_Compare_Prod_DR.html 2>&1
  

  Schedule additional alarming reports. These need to be set up in the cgi-bin mimi.pl reports screen before they can be run. But you can snapshot anything you can see on those screens and mail the results as appropriate

     0 10,12,14,16,18 * * 1-5 /usr/local/bin/perl-5.8.1 /apps/sybmon/dev/ADMIN_SCRIPTS/monitoring/RunMimiReport.pl --SUBJECT="2 Hour Unix Status Report" --MAILTO=def@bac.com,abc@abc.com --REPORT=Unix2Hour --TIME="2 Hours" > /apps/sybmon/dev/data/GEM_BATCHJOB_LOGS/RunMimiReport_Unix2Hour 2> /apps/sybmon/dev/data/batchjob_errors/RunMimiReport_Unix2Hour
  

  I sync logins between my replicated servers with

     38 9 * * 0 /usr/local/bin/perl-5.8.1 /apps/sybmon/dev/ADMIN_SCRIPTS/bin/copy_syslogins.pl -FSYB1 -Usa -Pxxx -tSYB2 -usa -pyyy > /apps/sybmon/dev/data/GEM_BATCHJOB_LOGS/copy_syslogins_SYB1.log 2>/apps/sybmon/dev/data/batchjob_errors/copy_syslogins_SYB1.log
  

  I monitor cisco logs with

     18,48 * * * * /usr/bin/nice /usr/local/bin/perl-5.8.1 /apps/sybmon/dev/ADMIN_SCRIPTS/monitoring/cisco_logmon.pl --INFILE=/var/log/cisco.log --TODAYONLY >/apps/sybmon/dev/data/GEM_BATCHJOB_LOGS/cisco_logmon.log 2>/apps/sybmon/dev/data/batchjob_errors/cisco_logmon.log
  

  I monitor centrally collected (via syslogsd) unix logs with

     5,15,25,35,45,55 * * * * /usr/local/bin/perl-5.8.1 /apps/sybmon/dev/ADMIN_SCRIPTS/monitoring/unix_monitor.pl --INFILE=/var/log/messages --TODAYONLY --EXCLUDEFILE=/apps/sybmon/dev/ADMIN_SCRIPTS/bin/solaris.excl --MAXLINES=100000000 --HTML --OUTFILE=/apps/sybmon/dev/data/html_output/unix_monitor.html >/apps/sybmon/dev/data/GEM_BATCHJOB_LOGS/unix_monitor.log 2>/apps/sybmon/dev/data/batchjob_errors/unix_monitor.log
  

  Directory security

  GEM is an administrative tool, designed for system administrators. As such, the conf directory contains all your passwords. You need to protect this data via operating system permissions.

  We strongly recommend that all other directories in the tree, with the exception of the data/CONSOLE_REPORTS tree, be also made secure. Technically, you can allow read/execute access to these files, but it would be easier to only allow owner/group access - much of the data contained may be sensitive.

  
  

  How To Upgrade GEM

  GEM is designed for simple upgrades. The steps are as follows

  • Download the latest codeline

  • Extract on top of your existing distribution!!!

    No configuration files are shipped with the installation so there should be no problems. All configuration files are shipped with a .sample extension.

    On your windows monitoring server, you may not be able to extract the code because the task scheduler is running a batch job that calls one of the perl scripts. Windows will not let you overwrite a program that is running. This does not happen under Unix. You can either go into the task scheduler, stop all running jobs before rerunning the extract, or kill the jobs and then stop the task scheduler process. I normally just stop the jobs, but sometimes (because end task does not work cleanly) i am forced to stop the scheduler process for the duration of the extract.

  • Run perl upgrade.pl

    After you download, your GEM scripts will have *OUR* hashbangs, library paths, and sybase environment. This is probably not what you want.

    Run perl upgrade.pl to finalize the installation. This step is MANDATORY and will perform a variety of steps like updating your configuration files and re-installing your alarm database. During the period you are upgrading - your GEM batch jobs will probably fail and send you alarm mail. Ignore these failures notifications.

    The alarm database reinstall will save table data, drop all tables, reinstall tables, and finally copy back data. If there is a problem, it will leave a tables with the extension _BAK containing the original data. So, under the hood, the upgrade process for the table Event is "select into Event_BAK from Event", "drop table Event", "create table Event", "insert Event select * from Event_BAK", and finally "drop table Event_BAK". Upgrades can not start if any tables with the extension _BAK exists - you must remove them before the upgrade.

    If there is a problem, you can probably just drop the BAK table - but it would be better to recover by following a process akin to "truncate table Event", "insert Event select * from Event_BAK", "Drop table Event" (you have to do these by hand). Got it? Any questions on a failed install please contact SQL Technologies tech support.

    The final install step redoes your hashbangs, perl library paths, and removes all control M's from the code line. A log of the upgrade is saved in the logs directory.

    ONCE YOU HAVE SUCCESSFULLY RUN upgrade.pl YOU ARE DONE

  
  

  GUIDE TO GEM BATCH JOBS

  DESCRIPTION

  Batch jobs are created by the GEM configuration program and are used to monitor your servers and to create the GEM Console. These scripts are organized into 8 directories that contain duplicate scripts. The batch jobs name is the "monitor program" shown by the monitoring system.

  The directory structure is

     win32_batch_scripts or
         batch
         interactive
         plan_batch
         plan_interactive
     unix_batch_scripts
         batch
         interactive
         plan_batch
         plan_interactive
  

  Files are created in these directories as needed by configure.pl. For example, if you tell configure.pl that you have only windows based systems, the subdirectories under unix_batch_scripts will not be populated. Similarly, if you are in an all-sybase environment, the oracle and sql server scripts will not be created. Batch jobs are created with a .ksh extension. Additionally, the win32_batch_scripts tree will contain duplicates with a .bat extension. This simplfies scheduling.

  The major difference between the "*_batch" and "*_interactive" directories is the redirection of standard output. Scheduled jobs use the "batch" directories which differ from the "interactive" directories only in that they redirect standard output and standard error into the GEM_HOME/data/GEM_BATCHJOB_LOGS directory. These files can be viewed through the Console.

  You are responsible for scheduling these jobs (sort of). If you are running on windows, configure.pl (the GEM Configuration utility) can auto schedule your jobs in the windows task scheduler. If unix, configure.pl can create a customized GEM_HOME/crontab.txt file for your inclusion in your unix crontab (via crontab -e; set the variable 'EDITOR=vi' if 'crontab -e' leaves you in 'ed' mode. On Win32, GEM provides both .ksh and .bat versions of all scripts to support those who use a unix emulator like cygwin or MKS toolkit.

  TO RUN THE JOBS INTERACTIVELY

  When running batch jobs by hand (for example to test), always use the files in the 'interactive' or 'plan_interactive' subdirectory trees. If you use the scripts in batch or plan_batch, you wont be able to view the output because it is redirected to files!!!

  
  

  SUMMARY OF BATCH JOBS

  The following table is a summary of the jobs that can be created by GEM's configuration utility.

  Scripts In The 'plan' Subdirectories
  Job Name	Win32 or Unix	Description	Recommended Frequency
  Backup_<PLANNAME>	Win32	Main Nightly PLANNAME Backup Run (and possibly transfer & load them)	daily
  FixLogship_<PLANNAME>	Win32	Guarantees Transfer of logshiped files for plan PLANNAME	never
  LoadTranlogs_<PLANNAME>	Win32	Loads Transfered but Unapplied Transaction Logs	never
  Trandump_<PLANNAME>	Win32	Run a Transaction Log Dump (and possibly transfer & load them)	Hourly

  Scripts In The 'interactive' Subdirectories
  Job Name	Win32 or Unix	Description	Recommended Frequency
  AlarmCleanupDB	Both	Internal cleanup of the gemalarms database	daily
  AlarmRoutingAgent	Both	Read Alerts & Route Them	Frequently
  CheckServer_<SERVER>	Win32	Is SERVER ok?	Hourly
  CleanupUnixGEM	Unix	Cleans up unix files on GEM	Daily
  ConsoleArchiver.ksh	Both	Once a month make a datestamped copy of the console	Monthly
  ConsoleBuildAndFtp	either	Assembles the Console. Optionally publish the Console to external web server	Hourly
  ConsoleUnixHourly	Unix	Builds frequently changing pages in the Console - scheduled 1ce/hour	Hourly
  ConsoleUnixNightly	Unix	Build 1ce/day pages in the Console	daily
  ConsoleUnixWeekly	Unix	Build 1ce/week pages in the Console	Weekly
  ConsoleWin32Hourly	Win32	Builds frequently changing pages in the Console - scheduled 1ce/hour	Hourly
  ConsoleWin32Nightly	Win32	Build 1ce/day pages in the Console	Daily
  ConsoleWin32Weekly	Win32	Build 1ce/week pages in the Console	Weekly
  DnsMonitor	Both	Monitors your DNS Servers	Hourly
  GemRpt_AgentReport	Both	Create Backup Reports	Hourly
  GemRpt_BackupReport	Both	Create Backup Reports	Hourly
  GemRpt_Backups	Both	Create Backup Reports	Hourly
  GemRpt_BlackoutReport	Both	Creates the Blackout Report	Daily
  GemRpt_PCDiskspace	Both	Win32 Disk Space Monitor	Hourly
  GemRpt_PCEventlog	Both	Win32 Event Log Monitor	Hourly
  GemRpt_ProductionErrors	Both	Create Production Overnight Report	Hourly
  GemRpt_ProductionList.ksh	Both	Creates the Production List Report	Daily
  GemRpt_ProductionWarnings	Both	Create Full List Of All Alerts	Hourly
  GemRpt_Sybase_ASE	Both	Create Sybase Event Log Reports	Hourly
  GemRpt_Sybase_BackupServer	Both	Create Sybase Backup Reports	Hourly
  GemRpt_Sybase_RepServer	Both	Create Sybase Replication Report	hourly
  GemRpt_Unix_Errors.ksh	Unix	Create a report with current unix errors	Daily
  GemRpt_Unix_Warnings.ksh	Unix	Create a report with current unix warnings	Daily
  GetUnixDiskSpace	Unix	Collect Unix Disk Space Alerts	Frequently
  LargeLogFileArchive.ksh		Archive database error logs that have grown too large	Weekly
  OptdiagAnalyze.ksh		Analyze your servers based on optdiag output	Weekly
  OraSurvey	Both	Survey all oracle servers	daily
  PortMonitor	Both	Test Services (sybase servers, win32 services...)	Frequently
  SybAnalyzeDepends	Both	Create Dependency Analysis Files Of All Your Databases	Weekly
  SybAuditor	Both	Reverse Engineer an Audit of your Sybase Servers	Weekly
  SybBkpSvrConfig	Both	Create Configuration Report on Backup File Fetch	Daily
  SybBkpSvrLogFetch	Both	Fetch All Sybase Backup Files	Hourly
  SybBkpSvrLogRpt	Both	Process Sybase Backup Files	Hourly
  SybCheck_Development	Both	Check availability of your Development Servers	Hourly
  SybDailyCheck	Both	Daily Sybase Server Check	Daily
  SybErrLogAllRpt	Both	Create complete Sybase Error Log REport	Hourly
  SybErrLogSaveAlarms	Both	Parse Sybase Error Logs & Save Any Alerts	Hourly
  SybErrLogConfig	Both	Create Sybase Error Log Configuration Report	daily
  SybErrLogFetch	Both	Fetch Sybase Error Logs	Hourly
  SybErrLogRpt	Both	Analyze & Report on Sybase Error Logs	Hourly
  SybHistSvrConfig	Both	Report Historical Server Configuration	daily
  SybHistSvrLogFetch	Both	Fetch Historical Server Log Files	Hourly
  SybHistSvrLogRpt	Both	Analyze & Create Historical Server Report	Hourly
  SybMonSvrConfig	Both	Report Monitor Server Configuration	daily
  SybMonSvrLogFetch	Both	Fetch Historical Server Log Files	Hourly
  SybMonSvrLogRpt	Both	Analyze & Create Monitor Server Report	Hourly
  SybPing	Both	Monitor Your Sybase Servers via ping	Frequently
  SybSurvey	Both	survey	daily
  SybRepCompare		Compare Rowcounts between servers that are replicated	Daily
  SybRepServerChecker	Both	Check Replication	Hourly
  SybRepSvrConfig	Both	Report On Your Replication Configuration	daily
  SybRepSvrLogFetch	Both	Fetch Replication Server Log Files	Frequently
  SybRepSvrLogRpt	Both	Report on Repliation Server Logs	Hourly
  SybRepSvrSaveAlarms	Both	Parse & Save Replication Server Errors	Frequently
  SybaseBackupFileReport	Both	Report on Backups	daily
  SybRepMonCheck		Checks that your replication monitoring is working	Daily
  SybRepMonReport	Both	Create Report of Replication Latencieis	Frequently
  SybSpaceMonitor	Both	Sybase Space Monitor	Hourly
  SybStoredProcUpdater		Ensure that all stored procedure libraries are at the latest revision	Weekly
  SybThresholdManager	Both	Run Threshold Manager	Daily
  SybThresholdManagerReinstall	Both	Reinstall Threshold Manager for databases that have changed in size	Daily
  SybTruncateMaster	Both	dump tran master with nolog	Daily
  SybTruncateSybsystemdb	Both	dump tran sybsystemdb with nolog	Weekly
  SybTruncateSybsystemprocs	Both	dump tran sybsystemprocs with nolog	Weekly
  MssqlAnalyzeDepends	Win32	Optionally analyze dependencies on Sql Server	Daily
  MssqlAuditor	Win32	Audit your Mssql configurations	Daily
  MssqlBackupCrosscheck_AM	Win32	Morning batch to check Mssql Backups	Daily
  MssqlBackupCrosscheck_PM	Win32	Evening batch to check Mssql Backups	Hourly
  MssqlBackupHistory	Win32	Check your backup history	Daily
  MssqlCheck_Development	Win32	Check your development servers	Hourly
  GetBackupStateMssql	Win32	Have All Your SQL Server Databases Been Backed Up?	Hourly
  GetBackupStateSybase	Both	Have All Your Sybase Server Databases Been Backed Up?	Hourly
  MssqlCycleEventlogs	Win32	Cycle Sql Server Event Logs	daily
  MssqlDailyCheck	Win32	Daily Check of your Mssql Servers
  MssqlJobStatusRpt	Win32	Check Sql Server Scheduled Jobs	daily
  MssqlLogMaintPlan	Win32	Check Sql Server Transaction Logs Plans	Hourly
  MssqlPing	Win32	Monitor Your Sql Servers via ping	Frequently
  MssqlScheduledJobRpt	Win32	Check SQL Server Scheduled Jobs	daily
  MssqlShrinkDatabase	Win32	Shrink Database	Weekly
  MssqlShrinklogs	Win32	Shrink Transaction Logs	Hourly
  MssqlStoredProcUpdater	Win32	Ensure latest revision of Stored Procedure Library
  SpaceMonitorMssql	Win32	Monitor Space on Mssql Servers	Hourly
  MssqlSurvey	Win32	survey your Win32 Servers	daily
  MssqlTruncateMaster	Win32	Truncate Master database log
  Win32FileCleanup	Win32	Cleanup Windows Directories	daily
  Win32Diskspace	Win32	Monitors Windows Diskspace	Frequently
  Win32Eventlog	Win32	Monitors Windows Event Logs	Frequently
  Win32GetHosts	Win32	Fetch Win32 Host Information	daily
  Win32Ping	Win32	Pings your Win32 Hosts	Frequently
  Win32HostsRpt	Win32	Create Win32 Hosts Report	daily
  Win32ServiceChecker	Win32	Monitors Windows Services	Frequently
  Win32ServiceCheckerUpdate	Win32	Append to Services List for any new servers	never
  Win32Survey	Win32	Survey Your Win32 Servers	daily
  Win32TaskScheduler	Win32	Monitors Windows Task Scheduler	Hourly
  TestDfltConnectivity	Both	Tests Connectivity to your Hosts	Hourly
  TraceRoute	Both	Run & Analyze Trace Route Info To Your Servers	Hourly
  UnixPing	Both	Monitor Your unix systems via ping	Frequently
  UnixSurvey	Unix	Survey Your Unix Servers	Daily
  UnixBackupStateStaticInfo	Both	Sanity Check Backup Configuration Static Info (lists of databases...)	Daily
  UnixFileCleanup	Unix	Cleanup Unix Directories	Daily

  
  

  Survey Scripts

  The survey scripts discover information about your servers and store the results in the gem.xml data file. The scritps are broken up by type for scheduling convenience. The Sql Server and Windows surveys must be run from a Win32 scheduler, but the Oracle and Sybase jobs can be run on either unix or windows. I personally prefer to run these jobs on unix.

  OraSurvey

    Package:  Gem Console
    Synopsis: Surveys your oracle servers - collects configuration information
    Executes: <CODEDIR>/server_documenter/discover.pl -Toracle
  

  SurveySqlServer

    Package:  Gem Console
    Synopsis: Surveys your sql servers - collects configuration information
    Executes: <CODEDIR>/server_documenter/discover.pl -Tsqlsvr
  

  SybSurvey

    Package:  Gem Console
    Synopsis: Surveys your sybase servers - collects configuration information
    Executes: <CODEDIR>/server_documenter/discover.pl -Tsybase
  

  Win32Survey

    Package:  Gem Console
    Synopsis: Surveys your windows servers - collects configuration information
    Executes: <CODEDIR>/server_documenter/discover.pl -Tpcdisks
  

  
  

  MAINTENANCE PLAN JOBS

  These jobs, stored in plan_batch and plan_interactive are used for your nightly maintenance. Each plan you created will have its own associated set of scripts. There will either 2 or 4 batches created for each plan, depending on whether log shipping is enabled.

  Backup_{PLANNAME}

    Package:  Gem Backup Jobs
    Synopsis: Backs up your databases
    Executes: <CODEDIR>/dbi_backup_scripts/backup.pl -J<JOB>
  

  FixLogship_{PLANNAME}

    Package:  Gem Backup Jobs
    Synopsis: Copy transaction logs for log shipping
    Executes: <CODEDIR>/dbi_backup_scripts/fix_logship.pl -J<JOB>
  

  LoadTranlogs_{PLANNAME}

    Package:  Gem Backup Jobs
    Synopsis: Cleans up old events and heartbeats from your GEM Alarms Database
    Executes: <CODEDIR>/dbi_backup_scripts/load_all_tranlogs.pl -JOB=<JOB>
  

  Trandump_{PLANNAME}

    Package:  Gem Backup Jobs
    Synopsis: Cleans up old events and heartbeats from your GEM Alarms Database
    Executes: <CODEDIR>/dbi_backup_scripts/backup.pl -t -J<JOB>
  

  
  

  CONSOLE BATCH JOBS

  These jobs create and manage the Console

  ConsoleUnixHourly, ConsoleWin32Hourly

    Package:  GEM Console
    Synopsis: builds the Console documentation tree with recently changed stuff
    Executes: <CODEDIR>/server_documenter/consoler_hourly.pl
  

  ConsoleUnixNightly, ConsoleWin32Nightly

    Package:  GEM Console
    Synopsis: builds the Console documentation tree - overnight rebuild
    Executes: <CODEDIR>/server_documenter/console_nightly.pl
  

  ConsoleUnixWeekly, ConsoleWin32Weekly

    Package:  GEM Console
    Synopsis: builds the Console documentation tree - complete rebuild (slow)
    Executes: <CODEDIR>/server_documenter/console_weekly.pl
  

  
  

  CONSOLE BATCH REPORTS

  These reports use the alarm viewer to create pre-designed reports about your servers. These reports are stored in /html_output. When any of the Console build utilities run, they search for these reports and copy them in to the GEM menu heirarchy. These reports are, in other words, batched. As you will probably be rebuilding the Console hourly, they may be up to 1 hour delayed (it depends on how often you schedule the job). Note that the same command executable (RunMimiReport.pl) takes other arguments including email addresses for direct mailing of reports to specific email addresses.

  GemRpt_Backups

    Package:  GEM Console
    Synopsis: Creates Custom Report GemRpt_Backups.html
    Executes: <CODEDIR>/monitoring/RunMimiReport.pl --REPORT=GemRpt_Backups
       --OUTFILE=<DATADIR>/html_output/GemRpt_Backups.html
  

  GemRpt_PCDiskspace

    Package:  GEM Console
    Synopsis: Creates Custom Report GemRpt_PCDiskspace.html
    Executes: <CODEDIR>/monitoring/RunMimiReport.pl --REPORT=GemRpt_PCDiskspace
       --OUTFILE=<DATADIR>/html_output/GemRpt_PCDiskspace.html
  

  GemRpt_PCEventlog

    Package:  GEM Console
    Synopsis: Creates Custom Report GemRpt_PCEventlog.html
    Executes: <CODEDIR>/monitoring/RunMimiReport.pl --REPORT=GemRpt_PCEventlog
       --OUTFILE=<DATADIR>/html_output/GemRpt_PCEventlog.html
  

  GemRpt_ProductionOvernight

     Package:  GEM Console
     Synopsis: Creates Custom Report GemRpt_ProductionOvernight.html
     Executes: <CODEDIR>/monitoring/RunMimiReport.pl --BATCHID=GemRpt_ProductionOvernight
        --REPORT=GemRpt_ProductionOvernight
        --OUTFILE=<DATADIR>/html_output/GemRpt_ProductionOvernight.html
  

  GemRpt_ProductionWarnings

     Package:  GEM Console
     Synopsis: Creates Custom Report GemRpt_ProductionWarnings.html
     Executes: <CODEDIR>/monitoring/RunMimiReport.pl --BATCHID=GemRpt_ProductionWarnings
        --REPORT=GemRpt_ProductionWarnings
        --OUTFILE=<DATADIR>/html_output/GemRpt_ProductionWarnings.html
  

  GemRpt_SybaseASE

     Package:  GEM Console
     Synopsis: Creates Custom Report GemRpt_SybaseASE.html
     Executes: <CODEDIR>/monitoring/RunMimiReport.pl --BATCHID=GemRpt_SybaseASE
        --REPORT=GemRpt_SybaseASE
        --OUTFILE=<DATADIR>/html_output/GemRpt_SybaseASE.html
  

  GemRpt_SybaseBackupServer

     Package:  GEM Console
     Synopsis: Creates Custom Report GemRpt_SybaseBackupServer.html
     Executes: <CODEDIR>/monitoring/RunMimiReport.pl --BATCHID=GemRpt_SybaseBackupServer
        --REPORT=GemRpt_SybaseBackupServer
        --OUTFILE=<DATADIR>/html_output/GemRpt_SybaseBackupServer.html
  

  GemRpt_SybaseRepServer

     Package:  GEM Console
     Synopsis: Creates Custom Report GemRpt_SybaseRepServer.html
     Executes: <CODEDIR>/monitoring/RunMimiReport.pl --BATCHID=GemRpt_SybaseRepServer
        --REPORT=GemRpt_SybaseRepServer
        --OUTFILE=<DATADIR>/html_output/GemRpt_SybaseRepServer.html
  

  
  

  NORMAL JOBS

  These jobs, stored in the batch and interactive subdirectories are used for normal maintenance.

  AlarmCleanup

    Package:  Gem Maintenance
    Synopsis: Cleans up old events and heartbeats from your GEM Alarms Database
    Executes: <CODEDIR>/monitoring/MlpAlarmCleanupDB.pl --DAYS=7
  

  AlarmRoutingAgent

    Package:  Gem Maintenance
    Synopsis: Reads GEM Alarms and routes them via pager and email as appropriate
    Executes: <CODEDIR>/monitoring/MlpAlarmRoutingAgent.pl --FROM=MonitorAdm@hotmail.com --MAXHOURS=24
  

  AnalyzeDepends

    Package:  Gem Console / Utilities
    Synopsis: Builds a real ddl dependency tree for your servers
    Executes: <CODEDIR>/bin/depends_analyze.pl
  

  CheckServer_{SERVER}

    Package:  GEM Console / Monitoring
    Monitor:  CheckForBlocks
    Synopsis: Checks a single server for blocks.  Will have --MAILTO if it is a production server.
    Optional Arguments: --TIME=secs and --MAILTO=a@b,c@d  can be used for higher granualarity monitoring.k
    Executes: <CODEDIR>/monitoring/CheckServer.pl -TYPE=sqlsvr -TIME=300 --SERVER={SERVER}
       -MICROSOFT
    Executes: <CODEDIR>/monitoring/CheckServer.pl -TYPE=sqlsvr -TIME=60 --SERVER={SERVER}
       -MICROSOFT --MAILTO="barlowedward@hotmail.com"
  

  Win32GetHosts

    Package:  GEM Console
    Synopsis: Collects hosts files from your windows boxes
    Executes: <CODEDIR>/bin/win32_get_file.pl --BATCH_ID=Win32GetHosts
       --INDIR=/winnt/system32/drivers/etc,/windows/system32/drivers/etc,/windows,/winnt
       --INFILE=hosts -OUTDIR=<DATADIR>/system_information_data/hosts
  

  Win32TaskScheduler

    Package:  GEM Console
    Synopsis: Collects windows task scheduler log files from your windows boxes
    Executes: <CODEDIR>/bin/win32_get_file.pl --BATCH_ID=Win32TaskScheduler --INDIR=/winnt,/windows
       --INFILE=Schedlgu.txt --NEWLABEL=Win32_Job_Scheduler_Log
       -OUTDIR=<DATADIR>/system_information_data/schedlgu
  

  MssqlJobStatusRpt

    Executes: <CODEDIR>/bin/mssql_jobstatus_rpt.pl --HTML --OUTFILE=<DATADIR>/html_output/mssql_jobstatus_rpt.html
  

  This creates the file html_output/mssql_jobstatus_rpt.html which lists failed sql server jobs that are enabled. To remove rows from this report you should disable the failed job.

  MssqlLogMaintPlan

    Monitor: MssqlLogMaintPlan
    Executes: <CODEDIR>/bin/mssql_logmaintplan.pl -CONFIGFILE=<DATADIR>/logmaintplan.dat
  

  This saves maintenance plan information as heartbeats with the monitor_key=LogMaintPlan. This filters only system maintenance plans for SQL Server.

  Win32FileCleanup

  This is the most important of the maintenance scripts. The only issue is that you need to set it up by hand. Every directory you want purged should have lines added to it. Maintain this program and you wont ever need to run out of disk space again.

    Package: Normal Maintenance
    Synopsis: Cleans up directories on windows.  You should edit your conf/Win32_Cleanup.dat file
    Monitor: Win32FileCleanup
    Executes: <CODEDIR>/bin/purge_old_files.pl --BATCH_ID=Win32FileCleanup --ctlfile=//samba/sybmon/dev/conf/Win32_Cleanup.dat
  

  Win32Diskspace

    Package:  Gem Console
    Monitor:  Win32Diskspace
    Synopsis: Monitor and Report on Windows Diskspace
    Executes: <CODEDIR>/bin/win32_diskspace.pl --ALARMTHRESH=95 --WARNTHRESH=90
       --LOGALARM --OUTFILE=<DATADIR>/html_output/win32_diskspace.txt
  

  win32_diskspace.bat will save space usage information for your servers with embedded alarm thresholds of 90 for warning and 95% for errors. The output is placed in html_output/win32_diskspace.txt to be picked up later.

  PcEventlog

    Package:  Gem Console
    Monitor:  PcEventlog
    Synopsis: Monitor and Report on Windows System Event Logs
    Executes: <CODEDIR>/bin/win32_eventlog.pl --HOURS=2 --LOGEVENTS --CONFIGFILE=<DATADIR>/win32_eventlog.dat --SHOWINFOMSGS
  

  win32_eventlog.bat will save slightly reduced list of event log messages into the alarm manager. Several specific services are filtered out - the type can be adjusted by editing the win32_eventlog.pl script (which gives directions).

  win32_eventlog.bat uses the file data/win32_eventlog.dat for its base configuration information. It should run frequently (every 1-2 hours). Messages are saved in the alarm system with monitor_program = PcEventlog

  Win32ScheduledJobRpt

    Executes: <CODEDIR>/bin/win32_scheduled_job_rpt.pl --HTMLFILE=<DATADIR>/html_output/win32_scheduled_job.html
  

  Job scheduler reports will be generated. The mechanism for this is win32_scheduled_job_rpt.*. and sqlsvr_backupreport.ksh. These call the primitive routines win32_backupreport.pl and win32_scheduled_job_rpt.pl and result in html_output of win32_scheduled_job.html.

  The primitive for this is win32_scheduled_job_rpt.pl and win32_backupreprot.pl

  Win32ServiceChecker

    Package:  Gem Console
    Synopsis: Monitor and Report on Windows Services
    Monitor:  Win32ServiceChecker
    Executes: <CODEDIR>/bin/win32_service.pl --ACTION=VALIDATE
  

  Checks service availability on all your windows servers and compares results to a snapshot of services that should be running. The results are saved in the alarms database. This snapshot is not created by default (yet). The snapshot is stored in the conf/win32_service.dat file. The first thing to do is to run the following which creates a snapshot file of all running services on all your defined Win32 servers

    Win32_service_checker_init.bat
  

  The above command should probably not be scheduled, or scheduled only once per week. The init routine writes the win32_service.dat configuration file and will clear your alarm database. If you wish to reset the services for a particular system (perhaps it was rebuilt) you should add --SYSTEMS=server. In other words you should run either

     Win32_service_checker_init --SYSTEMS=winhost1
  

  which is the same as running the primitive

     win32_service.pl --SYSTEMS=winhost1 --ACTION=SNAPSHOT
  

  win32_service_checker.bat should probably be run fairly frequently. The primitive for this is win32_service.pl

  Win32ServiceCheckerInit

    Package:  Gem Console
    Synopsis: One off batch - takes a snapshot of services on all your windows boxes for use by Win32ServiceChecker
    Executes: <CODEDIR>/bin/win32_service.pl --ACTION=SNAPSHOT
  

  Win32ShrinkLogs

    Package:  Maintenance
    Synopsis: Shrink the transactionlogs on all your windows servers
    Executes: <CODEDIR>/bin/mssql_shrinklogs.pl --DOSHRINK --SYSTEMS=ALL
  

  The win32_shrinklogs.bat script will shrink all logs in all your sql servers using the mssql_shrinklogs.pl primitive.

  PingSqlsvr

    Monitor: PingSqlsvr
    Executes: <CODEDIR>/bin/ping_server.pl -TYPE=SQLSVR
  

  The Ping* scripts ping your database servers and save the results in the alarms database

  PingSybase

    Monitor: PingSybase
    Executes: <CODEDIR>/bin/ping_server.pl -TYPE=SYBASE
  

  The Ping* scripts ping your database servers and save the results in the alarms database

  PingSystems

    Monitor: PingSystems
    Executes: <CODEDIR>/bin/ping_systems.pl -TYPE=SYBASE
  

  The Ping* scripts ping your database servers and save the results in the alarms database

  PortMonitor

    Monitor: PortMonitor
    Executes: <CODEDIR>/monitoring/port_monitor.pl --LOGALARM
  

  The port monitor uses the file conf/port_monitor.dat for data. It does a "ping" on ports of your servers for a response. To initialize this file from known port data, run

     PortMonitor.ksh --GENERATE
  

  Additionally, the first time you run PortMonitor.ksh, it will generate the file based on your sybase interfaces file and GEM configuration data. Since your interface file may contain junk servers, it will comment out servers that are not up at the time of the run by placing a # in front of the line.

  You should add other servers/services to the config file. The initialization only monitors databases, but the pgogram can monitor everything that is based on sockets. Note windows NT services are allready handled.

  To remove a service that generates a warning or error, simply comment it out or remove it from the configuraiton file.

  PcHostsRpt

    Monitor: PcHostsRpt
    Executes: <CODEDIR>/bin/parse_hosts.pl -BPcHostsRpt -h -O<DATADIR>/html_output/Win32_Hosts_Report.html
       -D<DATADIR>/system_information_data/hosts
  

  SpaceMonitorMssql

    Monitor: SpaceMonitorMssql
    Executes: <CODEDIR>/server_documenter/space_monitor.pl -AR -Tsqlsvr
  

  SybSpaceMonitor

    Monitor: SpaceMonitorMssql
    Executes: <CODEDIR>/server_documenter/space_monitor.pl -AR -Tsybase
  

  TraceRoute

    Monitor: TraceRoute
    Executes: <CODEDIR>/ADMIN_SCRIPTS\monitoring\tracert.pl -BATCH_ID=TraceRoute
  

  SybRepMonAgent

     Executes: <CODEDIR>/ADMIN_SCRIPTS/monitoring/SybaseRepMonitor.pl --ACTION=MONITOR
       --ITERATIONS=5 --FREQUENCY=30
       --OUTFILE=<DATADIR>/html_output/SybRepMonAgent.html $*
  

  SybRepMonReport

     Executes: <CODEDIR>/ADMIN_SCRIPTS/monitoring/SybaseRepMonitor.pl --ACTION=REPORT
       --HTML --OUTFILE=<DATADIR>/html_output/SybRepMonReport.html $*
  

  MssqlCycleEventLogs

     Cycles the sql server event logs
  

  MssqlShrinklogs

     Shrinks the sql server log files on all your servers
  

  
  

  Log File Monitors

  These files will collect error logs for your servers. There are multiple reports for each log type. These reports are a Configuration Report (what it should do), a Fetch Report (how the file fetch went), a Report (usually giving 48 hours of log messages), and a SaveAlarms report, which uses a patern file and saves recent alarms into the alarm database. The SaveAlarms report will ensure that duplicates are not stored.

  SybErrLogAllRpt

     Executes: <CODEDIR>/ADMIN_SCRIPTS/console/gem_file_manager.pl -BSybErrLogAllRpt -TASE_SERVER -hrtF
       -O<DATADIR>/html_output/GemRpt_SybErrLogAllRpt.html $*
  

  SybErrLogConfig

     Executes: <CODEDIR>/ADMIN_SCRIPTS/console/gem_file_manager.pl -BSybErrLogConfig -TASE_SERVER -hm
       -O<DATADIR>/html_output/GemRpt_SybErrLogConfig.html $*
  

  SybErrLogFetch

     Executes: <CODEDIR>/ADMIN_SCRIPTS/console/gem_file_manager.pl -BSybErrLogFetch -TASE_SERVER -hf
       -O<DATADIR>/html_output/GemRpt_SybErrLogFetch.html $*
  

  SybErrLogRpt

     Executes: <CODEDIR>/ADMIN_SCRIPTS/console/gem_file_manager.pl -BSybErrLogRpt -TASE_SERVER -hrt
       -O<DATADIR>/html_output/GemRpt_SybErrLogRpt.html $*
  

  SybErrLogSaveAlarms

     Executes: <CODEDIR>/ADMIN_SCRIPTS/console/gem_file_manager.pl -BSybErrLogSaveAlarms -TASE_SERVER -hrpA
       -O<DATADIR>/html_output/GemRpt_SybErrLogSaveAlarms.html $*
  

  SybHistSvrConfig

     Executes: <CODEDIR>/ADMIN_SCRIPTS/console/gem_file_manager.pl -BSybHistSvrConfig -THISTORICAL_SERVER -hm
       -O<DATADIR>/html_output/GemRpt_SybHistSvrConfig.html $*
  

  SybHistSvrLogFetch

     Executes: <CODEDIR>/ADMIN_SCRIPTS/console/gem_file_manager.pl -BSybHistSvrLogFetch -THISTORICAL_SERVER -hf
       -O<DATADIR>/html_output/GemRpt_SybHistSvrLogFetch.html $*
  

  SybHistSvrLogRpt

     Executes: <CODEDIR>/ADMIN_SCRIPTS/console/gem_file_manager.pl -BSybHistSvrLogRpt -THISTORICAL_SERVER -htr
       -O<DATADIR>/html_output/GemRpt_SybHistSvrLogRpt.html $*
  

  SybMonSvrConfig

     Executes: <CODEDIR>/ADMIN_SCRIPTS/console/gem_file_manager.pl -BSybMonSvrConfig -TMONITOR_SERVER -hm
       -O<DATADIR>/html_output/GemRpt_SybMonSvrConfig.html $*
  

  SybMonSvrLogFetch

     Executes: <CODEDIR>/ADMIN_SCRIPTS/console/gem_file_manager.pl -BSybMonSvrLogFetch -TMONITOR_SERVER -hf
       -O<DATADIR>/html_output/GemRpt_SybMonSvrLogFetch.html $*
  

  SybMonSvrLogRpt

     Executes: <CODEDIR>/ADMIN_SCRIPTS/console/gem_file_manager.pl -BSybMonSvrLogRpt -TMONITOR_SERVER -htr
       -O<DATADIR>/html_output/GemRpt_SybMonSvrLogRpt.html $*
  

  SybRepSvrConfig

     Executes: <CODEDIR>/ADMIN_SCRIPTS/console/gem_file_manager.pl -BSybRepSvrConfig -TREP_SERVER -hm
       -O<DATADIR>/html_output/GemRpt_SybRepSvrConfig.html $*
  

  SybRepSvrLogFetch

     Executes: <CODEDIR>/ADMIN_SCRIPTS/console/gem_file_manager.pl -BSybRepSvrLogFetch -TREP_SERVER -hf
       -O<DATADIR>/html_output/GemRpt_SybRepSvrLogFetch.html $*
  

  SybRepSvrLogRpt

     Executes: <CODEDIR>/ADMIN_SCRIPTS/console/gem_file_manager.pl -BSybRepSvrLogRpt -TREP_SERVER -htr
       -O<DATADIR>/html_output/GemRpt_SybRepSvrLogRpt.html $*
  

  SybRepSvrSaveAlarms

     Executes: <CODEDIR>/ADMIN_SCRIPTS/console/gem_file_manager.pl -BSybRepSvrSaveAlarms -TREP_SERVER -hrpA
       -O<DATADIR>/html_output/GemRpt_SybRepSvrAlarm.html $*
  

  SybaseBackupFileReport

     Executes: <CODEDIR>/ADMIN_SCRIPTS/console/gem_file_manager.pl -BSybaseBackupFileReport -TBACKUP_SERVER -hm
       -O<DATADIR>/html_output/SybaseBackupFileReport.html $*
  

  OTHER

    check_sybase_repserver.pl     SybRepServerChecker
    documenter report 14          UnixDiskspace
  

  
  

  THE WIN32 BACKUP CROSSCHECK PROCESS

  Process

  The win32 backup crosscheck process is an extensive monitor within GEM that determines whether your SQL Server databases are appropriately backed up. This process consists of the following batch processes

  Batch Jobs Involved

        MssqlBackupCrosscheck
        MssqlBackupCrosscheckWeekly
        UpdateBackupStateStaticInfo
  
  

  What Happens

  Event log records are read on all your sql servers and stored in a table in the gemalarms database named BackupState. The MssqlBackupCrosscheckWeekly batch reads 1 full week of records while the MssqlBackupCrosscheck just reads a few hours worth of records. This table contains the last time any backup events happened - the last time the db was dumped, loaded, tran dumped etc... The table can, of course be non-representative of what your actual environment is. For example, you could drop a database named AA. Your event logs would show a backup of AA from last week - which would trigger an alarm because the db has not been backed up in a week! The UpdateBackupStateStaticInfo should run nightly to remove (actually mark deleted) information that is not current.

  The final part of this process is the crosscheck reporting. The --REPORT arg (normally set in the batches) will produce reports and alarm based on the info collected in the table. The reporting process uses crosscheck.dat config file to determine thresholds for what and how to alarm.

  Diagnostics Procedure

  MssqlBackupCrosscheck.ksh --DEBUG or --DIAGNOSTICS select * from BackupState perl Backup_Crosscheck_Report.pl --SQLSVRONLY --TRANHOURS=2 --NOALARM # use noalarm to avoid a flood of emails
  

  GEM SCREENSHOTS

  Document purpose

  This document will give screen shots of GEM.

  Installing dbi & dbd libs on win32

  Osql / Isql / Ping not in your path Not having any of these commands will cause an error on configure.pl startup. This is a non fatal error, but only if you are intentionally not using these features (ie. you are on win32 but do not want sql server support).

  Initial Run of troubleshoot.pl

  Initial Run of configure.pl

  Normal configure.pl Startup

  Configure.pl is the main program for installing the G.E.M.  It allows you to register your servers and provides a full interface to configuring the system.

  $ perl configure.pl Generic Enterprise Manager Setup Driver

          Use 'perl configure.pl --DEBUG' to see diagnostic messages.
          Messages will be logged to configure.log
  

   Generic Enterprise Manager Configuration Utility
   Copyright (c) 1995-2005 by SQL Technologies
   Root Directory: W:/dev
   Installation Log File: W:/dev/logs/gamecomp_configure.log
   >> [configure] | Reading GEM Function Library
   >> [func]      | Reading Configuration Files - FULL
   >> [func]      | Reading XML Data - gem.xml
   >> [func]      | Parsing Key Values from xml
   >> [configure] | Application Version: Build 634 (Apr 10 2006)
   >> [configure] | Start Time: Mon Apr 10 22:29:34 2006
   >> [configure] | Configuration Files Loaded - initializing
   >> [connectmgr]| Initializing Connection Manager
   >> [connectmgr]| Loading DBI Drivers
   >> [connectmgr]| Done Initializing Connection Manager
   >> Initialization Completed - Starting Main TK Loop
  

  

  

  

  

  

  

  

  

  

  

  

  

  

  

  
  

  Eventviewer.pl

  The event viewer allows you to see the state of your systems all at once.  In normal operation, it provides you with timely information about problems in your environment.  It allows you to review errors and state that they are ok (for 24 hours – if the alarm is found after 24 hours, the message will reappear) or delete the message.  You can also filter the messages by source monitoring program, server, and severity.  You can set up groups of servers (like all your databases or all your windows boxes) and see messages just from them.

  

  The examples on the following pagges show recent messages from a single production system.  Note that there are numerous messages but the system has filtered them down to a manageable number of alerst.

  

  This information above is a subset of the messages stored for that system, which are more numerous and include a variety of event logs and monitoring messages. Here are two sample screens showing All the events and heartbeats for a production sql server.

  

  

  
  

  
  

  Monitor.pl

  Monitor.pl is your system monitor.  It provides easy navigation between databases and allows you to see a variety of performance reports at your fingertips.  The main power here is to see exactly what is going on in your servers.  Monitor.pl is a G.E.M. plugin and will be included in the final G.E.M. release.  

  

  In addition

  

  

  
  
  

  FREQUENTLY ASKED QUESTIONS

  DESCRIPTION

  q) i just want to install your stored procedure library... what should i do.

  a) just go to the ADMIN_SCRIPTS/procs subdirectory and run /configure.pl.

     The main issue you will in counter with this perl script is your perl setup and
     your perl library path.  If you get missing module messages it is likely that this
     is the problem.  The script uses the GEM library DBIFunc.pm, which is located in the
     lib directory.  The configure.pl script should find it automatically, if it does not,
     please contact SQL Technologies.  configure.pl will prompt you for Server, Login (an
     sa login) and Password - it will then autodetect if you are on Microsoft SQL Server
     or Sybase and install the procedures as appropriate.
  

  q) i cant run "perl configure.pl" or "perl gem.pl" to start the application.

  a) Well... thats pretty general... i would love some more information. The first thing

     to do is run "perl troubleshoot.pl".
  

  q) i want my data subdirectory to exist somewhere besides data...

  a) use symbolic links. There are currently dependencies on the path to this directory

     that are not completely flexible - hence the lack of a GemRpt_DATA_DIR configuration
     variable in configure.cfg.  This may change in future versions.
  

  q) i wish to run from unix

  a) this is an x windows application from windows - you need to set your display and have an x emulator like exceed running on your pc.

     export DISPLAY=10.5.103.51:0.0
     /usr/local/bin/perl-5.8.1 gem.pl
  

  q) the screen freezes when running some commands

  a) check the background screen, the screen you called the GEM or configure.pl programs from, for output.

     the lack of fork() functionality on Windows implies that sometimes there are issues like this.  It is
     being worked.  The solution to this is often to just hit return a few times in the background screen.
  

  q) i get an error that looks like:

   >> [gem.pl]    | Reading XML Configuration File - conf/
   >> [gem.pl]    | Reading Gem Function Library
   >> [gem.pl]    | Reading Configuration Files
   no element found at line 1, column 0, byte 0 at G:/dev/lib/XML/Parser.pm line 168
   <Hit Enter Key To Continue>
  

  a) well your conf/gem.xml file is incorrect - i would just go to an archive. The archive files are all listed with date extensions in the same directory. The xml configuration file is very touchy about syntax.

  q) i get the following message at startup

    junk after document element at line 6050, column 0, byte 933251 at C:/Perl/site/
    lib/XML/Parser.pm line 168
    <Hit Enter Key To Continue>
  

  a) you have a screwed up xml configuration file. The above file is screwed up at line 6050. You will need to edit the file and fix it or to roll back to one of the dated backups.

  q) why dont some of the perl scripts run stand alone???

   for example i run <i>perl fix_db.pl -AX</i> and i get
  

    Can't locate Repository.pm in @INC (@INC contains:
       /apps/sybmon/perl/lib/5.8.4/sun4-solaris-thread-multi
       /apps/sybmon/perl/lib/5.8.4
       /apps/sybmon/perl/lib/site_perl/5.8.4/sun4-solaris-thread-multi
       /apps/sybmon/perl/lib/site_perl/5.8.4
       /apps/sybmon/perl/lib/site_perl .) at fix_db.pl line 21.
  

  BEGIN failed--compilation aborted at fix_db.pl line 21.

  a) GEM libraries in perl are NOT stored in the normal perl library locations. They are stored in the lib subdirectory of the GEM install. If you installed into /apps/sybmon, you need to run perl -I/apps/sybmon/lib fix_db.pl -AX

  To get around this problem, we use lines like

     use lib qw(/apps/gem/lib);
  

  Which are touched / updated by the GEM Configuration Utility when you run the reformatter option.

  q) I keep getting these pop up screens from my scheduled tasks

  a) run your windows scheduled tasks using a different account from your interactive account.

  q) My windows graphical application gets stuck/frozen

  a) this is one of those silly windows'isms. If you have tabbed into the console window and scrolled, the application can get frozen. Of course, all the information (plus more) that you see in this window is actually saved into the log file and can be viewed from there. HIT RETURN IN YOUR CONSOLE WINDOW TO CONTINUE.

  q) I have a problem installing... help!!!

  a) well... im sorry to hear that... but the right thing to do is report it to sql technologies and to attach the log file to your error report. This file is the application name with a .log extension - so configure.log is the configure.pl log file. This log file does contain lots of details on your install and should hopefully help us solve your problem. Thanks!

  q) Rep Server Error Log Contains:

  E. 2006/04/25 12:04:04. ERROR #14024 USER(sa) - /execint.c(1038) Executor does not allow the entered command in the current mode.

  a) Sadly, this appears to be intrinsic to the DBD::Sybase library. It must do some under the covers sql commands to verify the connection. This message means a "normal" sql command was sent to the rep server - which of course will not handle it because the rep server has no sql language handler. Ignore the messages.

  q) i keep getting messages "no statement executing at ...."

  a) this comes from older versions of DBD::Sybase and has been fixed in the latest version. Ignore the messages or upgrade

  q) ok i scheduled all these jobs - what if something goes wrong?

  a) the console contains the contents of all your log files! Basically the jobs in your crontab all use a similar syntax to :

    $ cat TraceRoute.ksh
    umask 000
    /apps/sybmon/perl/bin/perl /apps/sybmon/dev/ADMIN_SCRIPTS/monitoring/tracert.pl -BATCH_ID=TraceRoute $* > /apps/sybmon/dev/data/GEM_BATCHJOB_LOGS/Unix_TraceRoute.log 2> /apps/sybmon/dev/data/batchjob_errors/Unix_TraceRoute.log
  

  Of course this is all customized ... the perl, the file locations etc...

  So how do you see what happened!!! just look at the console under "GEM Logs" -> "BATCH FILE EXECUTION LOGS". Do this every few days to make sure things are working (or whenever you notice a problem.o

  q) when i install the stored procedure library, i get language mismatch messages like :

     Msg 2401, Level 11, State 2:
     Server 'CALYPSODB':
     Character set conversion is not available between client character set 'utf8'
     and server character set 'iso_1'.
     No conversions will be done.
  
  

  a) set the LANG environment variable as appropriate

     export LANG=C
  
  

  q) I run GEM under LINUX and get expat errors:

     Generic Enterprise Manager Configuration Utility
     Copyright (c) 1995-2006 by SQL Technologies
     Root Directory: /apps/sybmon/gem
     Installation Log File: logs/upgrade_part1.log
     >> [configure] | Reading GEM Function Library
     >> [func]      | Reading Configuration Files - FULL
     >> [func]      | creating conf/configure.cfg from sample file
     ...
     >> [func]      | creating conf/port_monitor.dat from sample file
     >> [func]      | creating conf/replication.dat from sample file
     >> [func]      | Reading XML Data - gem.xml
     Can't load '/apps/sybmon/lib/auto/XML/Parser/Expat/Expat.so' for module XML::Parser::Expat: libexpat.so.1: cannot open shared object file: No such file or directory at /apps/perl/linux/perl-5.8.2/lib/5.8.2/i686-linux/DynaLoader.pm line 229.
      at /apps/sybmon/lib/XML/Parser.pm line 14
     Compilation failed in require at /apps/sybmon/lib/XML/Parser.pm line 14.
     BEGIN failed--compilation aborted at /apps/sybmon/lib/XML/Parser.pm line 18.
     Compilation failed in require at /apps/sybmon/gem/lib/XML/Simple.pm line 224.
     /apps/perl/linux/perl-5.8.2/bin/perl  -I/apps/sybmon/gem/lib -I/apps/sybmon/lib  /apps/sybmon/gem/bin/configure.pl --LOGFILE=logs/upgrade_part2.log --UPGRADE
  

  a) Apparently the default linux /usr/local/lib/libexpat.a is an old version & is missing a symbol resulting in

  Can't load '/apps/sybmon/DOWNLOADS/XML-Parser-2.34/blib/arch/auto/XML/Parser/Expat/Expat.so' for module XML::Parser::Expat: /apps/sybmon/DOWNLOADS/XML-Parser-2.34/blib/arch/auto/XML/Parser/Expat/Expat.so: undefined symbol: XML_SetEntityDeclHandler at /apps/perl/linux/perl-5.8.2/lib/5.8.2/i686-linux/DynaLoader.pm line 229.

  You will need to build by hand. Download and install the newest libexpat.a which comes from sourceforge at http://sourceforge.net/projects/expat => I downloaded it into /apps/sybmon/DOWNLOADS/expat-2.0.0

  You will want to build an all 32 bit solution. I have had no luck making it work if any part is 64 bit.

    -- DO THIS ON A 32 BIT MACHINE IF YOU CAN!
         mkdir /apps/sybmon/mystuff
           cd /apps/sybmon/DOWNLOADS/expat-2.0.0
         configure --prefix=/apps/sybmon/mystuff
         make
         make install
  

  The above will install to /apps/sybmo/mystuff instead of /usr/local/lib. It creates some .a/.so and some .h's.

  XML::Parser was also downloaded in /apps/sybmon/DOWNLOADS. The out of the box code does NOT work if you had to rebuild libexpat. It will find the wrong libexpat.so. The bad code does something like:

     LD_RUN_PATH=/usr/local/lib; cc -shared -L/usr/local/lib Expat.o -o ... -L /a/b/lib -lexpat
  

  This causes the build to use the original Expat.a in /usr/local/lib

        /usr/local/bin/perl-5.8.2 Makefile.PL EXPATLIBPATH=/apps/sybmon/mystuff/lib EXPATINCPATH=/apps/sybmon/mystuff/include  PREFIX=/apps/sybmon/lib
        vi Makefile */Makefile
           - LDDLFLAGS = -shared -L/usr/local/lib
           - LDFLAGS =  -L/usr/local/lib
           + LDDLFLAGS = -shared
           + LDFLAGS =
        make
        make test
  

  Note that you need to actually edit two Makefiles. I shortened LD_LIBRARY_PATH too - adding /apps/sybmon/mystuff/lib to it too.

  Finally... i needed to move all the stuff in the target directory up to a top level and set

     export perlargs=-I/apps/sybmon/lib
  
  

  q) I have an Installation failure on some kind of _BAK table

     > perl upgrade.pl
  

     [ snip ]
      TABLE      ContainerMap                   SAVE  drop  Install Restore
      TABLE      ContainerOverride              SAVE  drop  Install Restore
      TABLE      Container_full                 SAVE  drop  Install Restore
  

     *****************************************************
     Cant Backup Table Event.  Event_BAK allready exists! This indicates that a prior release failed.  Event_BAK is a backup of Event prior to the upgrade - make sure that the data is ok, dr
     op Event_BAK, and rerun this command
     *****************************************************
     /apps/sybmon/perl/bin/perl  -I/apps/sybmon/dev/lib  /apps/sybmon/dev/bin/configure.pl --LOGFILE=logs/upgrade_part2.log --UPGRADE
  
  

  a) You have a prior failed upgrade and a table archive (with the _BAK extension) exists. You are probably ok to just drop the

     _BAK table but you would be better served to
  
        insert Event select * from Event_BAK
        drop table Event_BAK
  
  

  q) While installing GEM on a windows system I receive the followingn error messages during the ppm install DBI :

  C:\Workfiles\GEM\gem>ppm PPM - Programmer's Package Manager version 3.2. Copyright (c) 2001 ActiveState Corp. All Rights Reserved. ActiveState is a division of Sophos.

  Entering interactive shell. Using Term::ReadLine::Perl as readline library.

  Type 'help' to get started.

  ppm> install DBI Error: No valid repositories: Error: 500 Can't connect to localhost:80 (connect: Unknown error) Error: 500 Can't connect to localhost:80 (connect: Unknown error) ppm> install DBI Error: No valid repositories: Error: 500 Can't connect to localhost:80 (connect: Unknown error) Error: 500 Can't connect to localhost:80 (connect: Unknown error) ppm>

  Any suggestions?

  a) I have re-installed active perl and added the DBD::ODBC manually and now it works fine.

  q) I have reinstalled a system and PcServiceChecker is complaining about services being down.

  a) You need to reinitialize conf/pc_service.dat. This is a good example of a procedure. We know what complains. Its the Batch PcServiceChecker.ksh. So we run it : Here is my session

     [G:/dev/win32_batch_scripts/interactive] Win32ServiceCheckerInit.ksh --HELP
     Unknown option: help
     Bad Parameter List Bad file descriptor
     Usage: pc_service.pl  - show all nt services
      --OUTFILE=outfile - save results to outfile
      --DEBUG
      --SYSTEMS=[system,system]
               if system=ALL will work on all systems in cfg file
               if system=Local will work on local system
      --ERRORLOG=errorlog - errorlog
      --SERVICE=service - show services matching
      --PATERN=patern - show services matching patern
      --EXCLUDE_PAT=patern - exclude services matching patern
      --RUNSTATE=runstate - show services with state matching patern
      --ACTION=action  - START|STOP|SNAPSHOT|VALIDATE
               if action is SNAPSHOT then a snapshot is taken
               if action is VALIDATE then a validate vs snapshot
  
     many of the above can take csv list as an arg
  
     [G:/dev/win32_batch_scripts/interactive] cat PcServiceCheckerInit.ksh
     C:/perl/bin/perl.exe //samba666/sybmon/dev/ADMIN_SCRIPTS/bin/pc_service.pl --ACTION=SNAPSHOT $*
  
     [G:/dev/win32_batch_scripts/interactive] PcServiceCheckerInit.ksh --SYSTEMS=WIN32_SERVER1
     Saving Snapshot into //samba666/sybmon/dev/conf/pc_service.dat
     Deleting Data For WIN32_SERVER1
     Delete Heartbeat where monitor_program='PcServiceChecker' and system='WIN32_SERVER1'
     44 services saved for WIN32_SERVER1
  
     [G:/dev/win32_batch_scripts/interactive]
  
  

  If we now compare pc_service.dat with a saved version, we see only the services for WIN32_SERVER1 have been replaced.

  
  

  PROCESS NOTES

  SYNOPSIS

  The following describes the major processes involved within the GEM system

  SYBASE BACKUP CROSSCHECK

  SQL SVR BACKUP CROSSCHECK

  The following batches read event log info and store the results in the database. After storing the results, they run the script UpdateBackupStateStaticInfo.pl to fix up static info (like the list of working databases in the system) and then will run a reporting tool Backup_Crosscheck_Report.pl which reads crosscheck.dat and compares the results to the saved values in the BackupState table.

     MssqlBackupCrosscheck.ksh
     MssqlBackupCrosscheckWeekly.ksh
  
  

  To collect data initially, we suggest you run the above with --NOALARM so you dont get totally spammed with alarms about databases you dont care about. Once you do the initial run (with --NOALARM) look at the standard output for the job and appropriately edit crosscheck.dat to remove any databases for which you dont care about backups and to appropriately have the system handle/manage your log shipping. By setting up the directives in this config file, you can inform GEM that systems should be log-shipped and it will intelligently understand that means that tran dumps must happen on primary and loads happen on secondary and that the database names must match between the systems.

  SYBASE LOG FILE CHECKS

  The following Jobs Relate To Fetching Sybase Log Files

  SybErrLogAllRpt.ksh Generates "Sybase Error Log - UnFiltered" SybErrLogFetch.ksh Fetch The Logs & Generate Console Sybase Error Log Fetch Report SybErrLogSaveAlarms.ksh Save Alarms From Prefetched Files - Report in "Sybase Error Log - Recent" SybErrLogConfig.ksh Generates The Console Sybase Error Log Configuration Report SybErrLogRpt.ksh Generates "Sybase Error Log - Filtered"

  The best mechanism to approach this process is to look at the Configuration Report and to remove any log files that are irrelevant -> the way we do this is to actually mark the RUN files associated with the log files as bad.

  SQL SVR LOG FILE CHECKS

  CONFIGURATION CHECKER

  allsrv_query_long.pl --QUERY=select @@SERVERNAME,value,comment from sysconfigures where comment="procedure cache size" --TYPE=SYBASE

  REPLICATION MONITOR

  CLEANING UP FILES

  
  

  gem.pl Generic Enterprise Manager

  DESCRIPTION

  This is the front end to your enterprise.

  USAGE

  perl gem.pl

  ENVIRONMENT VBLS

  if the environement variable perlargs is set - that will be added to the perl options of called programs

  
  

  configure.pl GEM Configuration Tool

  configure.pl is the GEM configuration utility. This utility will build the configuration files (stored in the conf subdirectory) and runs some basic setup tasks for GEM. The GEM Configuration Tool is quite intelligent, and will survey your systems to give you a good baseline that should require minimal tuning to work within your environment.

  Some of the things configure.pl does include:

  • Create Configuration Files

    One primary output of configure.pl is the configuration files stored in the conf directory. These files can be hand edited once GEM is set up.

  • Rebuild of all the code

    Changing the hashbangs (the first line of the file on unix is known as a hashbang and defines the command line interpreter to use), removing Control-M (dos newline) characters from the files, and modifying library paths.

  • Creation of the GEM Batch Jobs

    configure.pl also creates the GEM Batch Jobs. On a win32 monitoring station, GEM can schedule these jobs. On a unix environment, we create a crontab.txt file they can be schedule via cron.

  • Monitoring Setup

    The initial database installation is done via configure.pl

  • Initial Build

    This program also will generate an initial version of the GEM console and prepopulate the alarm system with alerts and reports relevant to your environment.

    Important notes

    • Configure.pl creates a file logs/configure.log which includes both console messages AND diagnostic messages. If you have a problem please look at this file. It contains all output including output that should be only produced with configure.pl --DEBUG.

    • Work through the tabs from left to right.

    • "Save Configuration Changes" writes your configuration files. While some operations (adding stored procedures to servers) are permanent once completed, the majority are not. It is important to save before you quit.

    • Leave the backups tab alone for your first pass

    How to run configure.pl

    perl configure.pl

    If you wish to see diagnostic messages, you may run

    perl configure.pl --DEBUG

    A log file of the run will be placed in the logs subdirectory.

    FOLLOW THE FOLLOWING STEP BY STEP INSTRUCTIONS TO COMPLETE YOUR INSTALLATION

    The welcome tab

    PURPOSE: License Details and Acceptance

    This tab gives licensing options for GEM. Select "I AGREE WITH THE LICENSE" to continue after you have read all the information provided when you click on the various buttons on this page.

    The GEM Help button will help guide you through your install.

    All GEM code is copyright (c) 1995-2007 by SQL Technologies=head1 The Register Tab

    PURPOSE: Basic configuration setup plus register your product.

    • Enter Administrator Info. This information is never shared so make it correct.

    • Select Environment, Product, and License. Running on a samba share is appropriate in Hybrid Windows/Unix environment. Select Free GEM for product trials. Contact barlowedward@hotmail.com for a product key for "Full Gem".

    • Select Preferred Connect Options to define the legal system connectivity options.

    • Click "REGISTER YOUR VERSION" to register your product.

    The paths tab

    [This tab is trivial if you have an All Windows or All Unix environment.]

    • Enter FULL Path(s) to your version of perl. eg. /usr/local/bin/perl or C:/perl/bin/perl.

    • Enter All legal paths to the GEM top level directory to define paths to required libraries. On a samba / hybrid installation where /apps/gem maps to c:/gem on win32 host win2, you would have '/apps/gem' (the Unix Code Location) and '//win2/c$/gem' as the NT Code Location, and Alternate #1 would be 'C:/gem'. Alternate #2 will be blank.

    • Enter Unix/Linux Web Browser Its probably /usr/local/bin/firefox. Skip if you dont know it.

    • Enter Sybase Home Directories in a list so GEM knows what paths to search while surveying remote systems.

    PURPOSE: Ensure Mail Connectivity

    This tab is only required on Windows servers. For these servers, you need to provide your SMTP mail server name. If "Test Mail Install" should send a test message to your GEM Administrator Mail Address (entered earlier).

    The servers tab

    PURPOSE: Register your Databases & Systems with GEM

    • Access To Databases must be via 'sa' or an account with similar roles/permissions. GEM does not support blank/empty administrator passwords.

    • Access To Unix Systems must be via ssh, rsh, or ftp using an account with reads permissions on the DBMS log files in $SYBASE/$ORA_HOME. The ssh or rsh connection should not require a password.

    • Access To Win32 Systems is via native authentication - the account must read Event Logs and Administrative File Shares (ie. requires administrator).

    • The Full GEM Add Server Wizard creates a best guess at your environment - prompting for logins and passwords appropriately. To save time, it guesses passwords by reusing previously entered ones. This wizard only registers NEW servers and is re-runnable.

    • The Free GEM Add Server Wizard For FREE Gem provides a simple data entry screen to enter up to 4 DATABASEs.

    • The Server Type Buttons allow you to define category (only PRODUCTION/CRITICAL systems are page-able), change passwords, and deregister servers

    • The Quick Add Button allows you to register a single Database or System.

    • The UnixComm & WinComm columns allow you to specify preferred communication methods from Unix/Windows Monitoring stations respectively.

    The files tab

    PURPOSE: Identify Database Home Directories & Important FIles

    This tab contains rows for each UNIX/Linux system, listing survey results (collected earlier) in blue and configuration directives in black. If survey results look incorrect, correct the configuration directives (with the add/delete buttons) and rerun the surveys.

    • Sybase ASE Server $SYBASE Directory is a valid sybase server directory and is searched for Error Logs, RUN_* files, and other config information.

    • Application Log File (unused in v1)

    • Ignore This RUN_* File directives that the Sybase server RUN_* file is unused and should be "ignored" to prevent confusion about what runs where.

    • Operating System Log File (unused in v1)

    • Sybase Open Client $SYBASE Directory are surveyed only for interface file consistency checks.

    • Oracle ORAHOME Directory (unused in v1)

    • SQL Server Maintenance Plan Logs (unused in v1)

    When completed, you consider clicking "SAVE CONFIGURATION CHANGES"=head1 The Connect Tab

    PURPOSE: Test connectivity and survey your systems.

    • Page-Top Buttons allow you to can see servers by type.

    • Connect To All to test connectivity. Windows GEM requires odbc settings for all databases and native administrator access for systems. Unix GEM requires normal database access (interfaces file for sybase) plus host access via ssh (preferred), rsh, or ftp. Fix up any servers you cant connect to.

    • Survey All to run a survey of all your servers. Normally, surveys are scheduled but we need some initial data. Click "Dont Skip" to not skip servers which have been recently surveyed.

    The backups tab

    When you create your servers, it will also create two backup plans for you: one that has the same name as the server and the other which has _WEEKEND appended to the server name. Data on your plans is stored in the configure.cfg file and is hand editable. Please see documentation on the GEM Maintenance And Backup System.

    • For initial setup, you can ignore this section

    The console tab

    PURPOSE: Define Console Special Behaviors

    • For an initial setup or trial, you can ignore this section

    • This page allows you to copy the console to another location. You may be interested in doing this for security reasons. By default, the CONSOLE Web Pages are created the data/CONSOLE_REPORTS directory but these reports can be published to another location (perhaps on your web server). Most users will NOT wish to do this.

      You may copy the files via unix copies (ssh, rsh, ftp - select the Copy using FTP button) or to a local directory (like //NEWSERVER/c$/web/gem). Some users may want to do thsi copy for security reasons - to allow non administrators to use the GEM console. This keeps those same users away from the GEM directory tree which stores other highly sensitive data.

    The alarms tab

    PURPOSE: Set Up Alarms & Monitoring Systems

    • Fill in the Alarm Database Server Info with information about the 200MB (approximate size) alarm database you set up before running configure.pl (see pre-requesites). The email address is arbitrary and all alarms will be mailed using that as a reply-to address. It is reasonable to set this to an INVALID mail address (alarms@yourcompany.com or Gem@yourcompany.com) because you wont ever reply to the messages.

    • Install/Upgrade Alarm Database will create alarm database tables & procedures

    • Validate Alarm Database checks the database for sanity and version.

    • Fill in Web Server information including the map of hostname/filename to alarming url. Once this is done click Populate Local cgi-bin to copy formatted scripts to .../cgi-bin and Copy to Web Server to copy those files to the web server.

    The install software tab

    Now would be a good time to Save Configuration Changes!

    Click the "Install Software" button to run the numbered steps listed below that button.

    • Reformat Code Removes Control-M's and Sets Hashbangs

    • Create GEM Batch Scripts creates your batch scripts. In a hybrid environment run this on UNIX last - so none of the files have control-M characters embedded

    • Backup Manager sets up default directories for the backup system

    • Modify Console Scripts

    The post install tasks tab

    • Create a crontab file for your unix installation and schedule it.

    • Snapshot your windows servers services to create conf/pc_service.dat. This basically creates a list of all the running services on all your windows boxes for future monitoring. You will be notified if any services go down. Assuming your environement is currently stable, a current snapshot will give you a pretty good listing of what needs to be monitored.

    The scheduler tab

    PURPOSE: Define Scheduling and Actually Schedule Batch Jobs

    • Look over the jobs as defined to ensure that they are correct - you can use the default Frequencies by default

    • Schedule on this server (Win32 Monitoring Only) provides a native interface to the windows task scheduler. Provide an account & password which has access to windows event logs, windows disk administrative shares, and windows services (probably requires adminstrator).

    • Create Unix Crontab will create a crontab.txt file in the gem home directory that is appropriate for unix cron. To install into cron:

         export EDITOR=vi
      
         crontab -e
      
         <vi window pops up>
      
         :r crontab.txt
      
      

      Be sure that the account you schedule this on has a .forward file so any errors will not be routed! A .forward file is a file in your HOME directory that contains mail addresses.

    
    

    troubleshoot.pl Rules based troubleshooter

    USAGE

    perl troubleshoot.pl

    DESCRIPTION

    Troubleshoot.pl is a command line program designed to troubleshoot your installation of the GEM. For now all it does is verify and validate your installation of perl. Run this prior to running any of the other scripts.

    TROUBLESHOOT OUTPUT

    /apps/sybmon/perl/bin/perl -I/apps/sybmon/dev/lib -I/apps/sybmon/dev/plugins /apps/sybmon/dev/bin/troubleshoot.pl troubleshoot.pl - perl package troubleshooter

       Your perl version is 5.008004
       Your Perl Library Include Path is
            /apps/sybmon/dev/lib
            /apps/sybmon/dev/plugins
            /apps/sybmon/perl/lib/5.8.4/sun4-solaris-thread-multi
            /apps/sybmon/perl/lib/5.8.4
            /apps/sybmon/perl/lib/site_perl/5.8.4/sun4-solaris-thread-multi
            /apps/sybmon/perl/lib/site_perl/5.8.4
            /apps/sybmon/perl/lib/site_perl
            .
       Testing Perl Builtin Package Integrity
             OK: All Required Builtin Perl Packages Found!!
       Testing Perl Database Connectivity Package Integrity
             OK: All Required Perl/DBI Packages Found!!
             OK: DBI Version is (1.46)
       OK: DBI Version is (1.04)
       Testing Perl/Tk Package Integrity
             OK: All Required Perl/Tk Packages Found!!
             OK: Tk Version is (804.027)
       Testing Gem Add On Module Integrity
             OK: All Gem Packages Found!!
       Testing Toolkit Built In Module Integrity
             OK: All Builtin Packages Found!!
    

    
    

    monitor.pl GEM Detailed Server Monitor

    DESCRIPTION

    monitor.pl provides a single interface for process monitoring on your servers. Currently the plugin works for sybase and for sql server. It Permits detailed monitoring of locks, blocks and running processes via the standard sp_who, sp_lock etc. procedures. Data is color coded for readability. It also provides MDA table monitoring for sybase servers.

    The program uses the stanadard GEM Enterprise Manager paradigm. You have a left most frame which contains your servers and a button labeled "Sql Server" and another labeled "Sybase". Select the button to change your context from Sql Server to and from Sybase. The right side provides you with your color coded process explorer.

    YOu can select what you wish to monitor in a drop down box. The program automatically refreshes using the Refresh Rate (in seconds) parameter). You can also manually refresh the screen using Refresh, Can Freeze the monitor using the appropriate (Freeze/UnFreeze) button. The System checkbox will allow you to see system processes or not.

    USAGE

    perl monitor.pl

    COLORS

    The following styles are used

       'admin'      => [ -background => 'white', -foreground=>'grey'    ],
       'running'   => [ -background => 'white', -foreground=>'green'   ],
       'error'       => [ -background => 'red',   -foreground=>'white' ],
       'sleeping'   => [ -background => 'white', -foreground=>'blue' ],
    

    According to the rules:

     if $bk>0 $style=error
     elsif( $cmd =~ /AWAITING COMMAND/i ) {
       $style ='sleeping';
     elsif( $cmd =~ /SUSPEND/i ) {
        $style ='error';
     elsif( $status =~ /lock sleep/i ) {
        $style ='error';
     elsif( $status =~ /run/ ) {
        $style ='running';
     elsif( $status eq "sleeping" ) {
        $style ='sleeping';
     elsif( $status eq "background" ) {
        $style ='sleeping';
     elsif( $cmd =~ /^HK/ ) {   $style ='admin';
     elsif( defined $login and  $login eq "" ) {
        $style ='admin';
     elsif( defined $"lock type" ) {
        $style ='error' if $"lock type" =~ /blk/;
     else    $style ='sleeping';
    

    
    

    eventviewer.pl Generic Enterprise Manager Module To Manage Events

    DESCRIPTION

    This is the front end to your enterprise.

    USAGE

    perl eventviewer.pl

    
    

    mimi.pl web browser based event/heartbeat monitor

    DESCRIPTION

    mimi.pl is THE MAIN PROGRAM FOR MONITORING YOUR ALARMS!!!

    INSTALLATION

    This program is a cgi web script that must be installed into a script mimi.pl is a full featured monitor that includes embedded data management capabilities. You can, for example, set up operators and define what alarms these operators will recieve from this web interface. This program should be bookmarked after it is run, and can be left up all day.

    mimi.pl will poll the server every thirty seconds for updated heartbeat and event data.

    SET UP

    The scripts in ADMIN_SCRIPTS/cgi-bin must be copied to a script executable web server directory as per the CGI installation instructions. These installation instructions can be found in the post-install tasks section of the installation guide.

    
    

    webgem.pl Secure GEM Interface For Web Servers

    SYNOPSIS

    webgem.pl is a program that runs from a web server script directory and provides secure access to the primary GEM executables. The GEM programs are X Windows based and the output screen will be sent back to your workstation. It is required that this program be set up by hand.

    SET UP

    The scripts in ADMIN_SCRIPTS/cgi-bin must be copied to a script executable web server directory as per the CGI installation instructions. These installation instructions can be found in the post-install tasks section of the installation guide.

    Server maintenance scripts

    The following is a guide to a FREE plan-based Data Server Backup and Maintenance Package for Sybase ASE and Microsoft SQL Server. 

    The package is written in perl,  runs on WinNT, Linux, and UNIX platforms, and include all the facilities you will require to perform basic data server management.  The individual scripts are small and easy to understand and, as with all perl code, are released as source code. The primary interface to these scripts is command line based for flexibility and power. Electronic mail is used for notification as appropriate.

    This package has been tested on the following databases

        SQL SERVER 2000     SQL SERVER 2005     Sybase 4.9     Sybase 10     Sybase 11     Sybase 12.5     Sybase 15

    This package is released as Free software from the GEM web site under a standard GPL license. The license will be updated to GPL v3 when that draft is finished.

    Backup Manager Package Links. Home Page   Standalone Download   GEM Download

    Package overview

    The GEM Server Maintenance Scripts are a complete solution for routine maintenance of "normal" Sybase and Microsoft SQL Server installations. This package uses Maintenance Plans to perform normal maintenance for your database systems like dbcc, update statistics, reorgs etc...

    These scripts work with both Sybase and Microsoft SQL Server. While we recommend the Microsoft Enterprise Manager solution for your smaller SQL Servers, their solution does not work well for all environments. Our solution is designed to complement the Microsoft solution, and is engineered to provide a flexibile mechanism that works well in complicated SQL server environments.

    This package has been around for about 15 years, although it was originally written as a set of Unix bourne shell scripts.  It was rewritten in perl in the late 90s as perl scripts, modified in early 2002 for Microsoft Sql Server compatibility, and is considered production quality and stable. 

    Features

    The following are some of the features of these scripts:

    • Free
    • Open Source Perl
    • Maintenance Plan Based
    • Command line driven
    • Complete backup solution for normal (ie. not data warehouses) Sybase and SQL Servers.
      • full backups
      • transaction log dumps
      • update statistics (with sp_recompile)
      • standard DBCC's
      • db reorgs
      • log shipping
    • Reports provide audit information necessary for you to rebuild your server from scratch.
    • Table Backups via Optional configurable BCP Backups of critical databases and tables
    • Extensible error handler that interfaces with your error management system
    • Universal Solution that works on UNIX, Win32, Sybase, and SQL Server
    • Compression of backup files using either internal (sybase) or external compression utilities (sql server)
    • Graphical menu system that both operators and administrators can use to load backups
    • Extensive Logging allows you to understand what occurred and when occurred
    • Self Cleaning architecture removes old backups and log files based on configurable parameters
    • Clean Layout provided in an easily understood clean directory structure
    • Centralized Solution running all maintenance from a single system
    • Log Shipping solution for disaster recovery

    How it works

    These scripts are designed to be simple to understand and work with. The scripts are delivered in three directories. The first is dbi_backup_scripts, which contains the code. The second, named lib, contains perl modules/libraries. The third, named conf, contains your configuration files.

    This package is installed and run from ONE centralized system. This central system runs all your backups, which you are responsible for scheduling using cron (on unix) or the windows task scheduler (on win32). You will identify an 'output' directory (BASE_BACKUP_DIR) which will contain your log files (dbcc output, session logs etc). The full and incremental backup files, of course, stay on the system that is running your RDBMS. The success of your backups is checked via RSH, SSH, or FTP (if running on UNIX) or via Windows Native File Access (if running on win32).

    The following stand alone perl scripts are provided. These open source programs are small and self documenting - you can see syntax using perl program_name -? and can see full documentation either at the end of this help section or by typing perldoc program_name:

    PROGRAM	DESCRIPTION
    backup.pl	This is the main script to run backup plans - calling the other scripts appropriately.
    config_report.pl	Create An Audit Report
    dbcc_checktable.pl	Runs DBCC Checktable on a single table
    dbcc_db.pl	Runs Standard Database Level Dbcc Commands
    dump_database.pl	Backup Database (complete or incremental)
    fix_logship.pl	Resync two directories of log shipping files - ie copy from source to target
    kill_users_in_db.pl	Kill the users in a particular database
    load_all_tranlogs.pl	Load All Unapplied Transaction Logs into another database
    load_database.pl	Load a Database from Dump or Incremental DUmp
    rebuild_index.pl	Complete Index Rebuilds (not used much)
    reorg_sybase.pl	Reorg Rebuild or Compact for Sybase
    set_dboption.pl	Sets A Database option like truncate log on checkpoint
    show_configvars.pl	Diagnostic - see the configuration values
    update_stats.pl	Update Statistics and Recompile

    Most of the time the only program you will use/schedule is backup.pl with appropriate arguments. backup.pl reads maintenance plan information from the master configuration file, configure.cfg, and runs the associated sub-commands as needed. Backup.pl is normally scheduled using one of two formats: backup.pl -JPLAN_NAME (full plan) or backup.pl -t -JPLAN_NAME (just dump & ship the tran log). backup.pl can take additional arguments like -d (runs in verbose/diagnostic mode), -R (control which steps are run), and -D (specifiy the databases to work on). Full documentation for any of these scripts can be found on this page or via 'perldoc backup.pl'. A short command syntax will be printed when you run 'backup.pl -?'. When installing, i suggest 'testing' your backups using -Dmaster - backing up a small database like master is an effective way of identifying all setup issues because the scripts will work fast.

    The files conf/unix_passwords.dat, conf/sybase_passwords.dat, and conf/sqlsvr_passwords.dat contain passwords.

    Installation

    These backup scripts are shipped as an integrated part of GEM, our commercial enterprise management solution for DBA's. They are also shipped as a standalone package and can be downloaded in compressed tar format (.tgz). 

    There are minor differences between the two layouts. The main difference is that the standalone release creates the three directories (conf, lib, and dbi_backup_scripts) under the directory "backup_scripts", while the GEM release puts the actual scripts in <GEM ROOT>/ADMIN_SCRIPTS/dbi_backup_scripts. Other than that, the releases are identical.

    We distribute the software as .tgz files (compressed tarbals). A compressed tar file is a native unix format that is also the format used by Windows utilities like WinZip.

    On unix, uncompress the distribution using tar xvzf backup_scripts.gtz. Older versions of tar may not have the 'z' uncompress option - you can also uncompress via gunzip backup_scripts.gtz or gzip -d backup_scripts.gtz and then extract with tar xvf backup_scripts.tar. Note the change in file endings - the uncompression programs will change the .tgz extension to a .tar one.

    After you download the package and uncompress, you can see the three main directories. The configuration files (in the conf subdirectory of course) are distributed with .sample extensions. This means that you can overwrite a working distribution with a code update and it will not clobber your configuration settings. You will need to copy these files, removing the .sample extension, and hand edit them to define your passwords and Maintenance Plans.  These files are completely self documenting. 

    Finally, our standalone release ships with a file configure.ksh which you must run before contiuning. All this simple file does is reformat the codeline by removing windows newlines and changing the internal include paths (so the code can find the perl libraries that are shipped in lib). The GEM configuration process does much the same thing.

    Note that when you download, some older browsers strip off multiple extension names.  If the file does not open once it is downloaded, check that the file contains the extension .tgz (compressed tar file) and rename the file if it does not. After you have downloaded, follow instructions in the installation section. If you have any comments, please contact SQL Technologies.

    Where to install

    These scripts normally run on a central 'scheduling' system.  You do not need to schedule backups on your database servers.

    If you have both Windows and Unix Database Servers, you will want to schedule your Unix Database Backups on a unix system and your Windows Database Backups on a windows server. The reasons for this are technical - TDS protocol changes by microsoft make connections from Unix to SQL Server impossible without implementing Free TDS / UnixODBC (non-trivial). The lack of native ssh/rsh on Windows makes checking Unix backups from windows hard - and scheduling Unix Database backups from the Windows Task Scheduler can not be recommended. Backups and server maintenance are a core and critical part of the dba's job - and reliability is important. Schedule your Unix/Sybase backups from a central Unix system and schedule your Win32/SQL Server or Windows/Sybase backups from a central Windows server. It is normal in mixed environments to store your logs (BASE_BACKUP_DIR) in a samba share so all logs are readily available from both unix or windows.

    The scripts validate backups by doing a file listing on the backup files. We use SSH, RSH, FTP and Windows Networking to implement this. You are responsible for setting up no-password access via SSH, RSH and Windows Networking, or for providing unix credentials that work with FTP.

    There is occasionally a need for additional installations of the backup scripts. This might be done due to communication firewalls or log shipping for High Volume installations. High Volume log shipping may require backup script installation on one of the database servers because log shipping requires copying the log dumps (and sometimes full dumps) from the Primary to the Secondary server. When scheduled from a third system, this is done by copying the files from Source to that third system and then copying to the target - double copying. For large (100+GB) dump files this is problematic. In this case, simply install and run the standalone version of the scripts on either the primary or secondary server - and your log shipping solution will work faster because there is no 'middle' system.

    Restrictions

    This solution is a general solution for backing up and maintaining Sybase and Microsoft SQL Server. No provision is made for very large databases (ie ones that will not fit on disk). This package routinely handles servers containing several hundred gigabytes and hundreds of databases. Very large RDBMS systems have particular needs which must be engineered by your database administration staff. For example, many data warehouses have uptime requirements that preclude your ever running update statistics and dbcc! These servers may require specially designed maintenance using facilities like table level dbcc or the running of update statistics on a separate servers. We provide consulting if you have questions on how to implement backups (but theoretically the scripts are designed so they are easy to use & you wont need that).

    Disaster recovery

    Our scripts provide a robust and complete log shipping solution that can assist you if you are concerned about the length of time that restoring your databases from backups might take in emergency situations. We use simple transaction log shipping, which has been a normal part of these RDBMS systems for many years.

    Source directory layout

    The code is released in three directories. These directories are laid out differently depending on if you are using GEM or the stand alone release.

    GEM Release Directory Structure

    	gem	Where You Installed
    	conf	configuration files
    	lib	Perl Libraries
    	ADMIN_SCRIPTS	Where all the tools are
    	dbi_backup_scripts	The code that is run

    Standalone Release Directory Structure

    	backup_scripts	Where You Installed
    	conf	configuration files
    	lib	Perl Libraries
    	dbi_backup_scripts	The code that is run

    Target directory layout

    Output is placed in a simple neat directory structure that exists on all your systems (both the scheduling system and the target database). These directories will not necessarily be populated. The scheduling server populates all directories except dbdumps and logdumps - which contain the actual backups. dbdumps and logdumps are the only directories populated on the systems running your RDBMS's. The directory structure is composed of the following:

    	Base Backup Directory	e.g. C:/backups (on NT) or /dbdumps (on UNIX) per config file
    	.../$SERVER	Sub directory for your Server
    	.../$SERVER/audits	Directory to put audit results (optional)
    	.../$SERVER/bcp	Directory to put bcp level backups (optional)
    	.../$SERVER/dbcc	Directory for raw dbcc output
    	.../$SERVER/dbdumps	Directory for local server dumps
    	.../$SERVER/errors	Directory for error messages
    	.../$SERVER/logdumps	Directory for local server tran log dumps
    	.../$SERVER/sessionlog	Directory for run logs

    This is a very functional and practical layout.  Because sessionlog and error messages are written to files on the central backup server, you have ready access to this information. If you wish to track down what happened to last nights backups of system X, you do not need to actually go to system X - you have the data in the same location as is all your other data. A full log of all your Maintenance operations stored in the sessionlog sub directory. A full log of raw dbcc output is stored in dbcc.

    All files in these directories are appropriately timestamped using yyyymmdd.hhmmss format so they list in time order when you do a directory listing. Log files are kept small yet clear, so you can keep several weeks worth of information readily available. The system will self-purge these log files based on a configuration setting that you control (ie. how many days do you want to keep of either your backups or your logs). You most likely will never use these historical logs, but they are useful if you have a problem.

    Similarly, 'audits' contains the output of a server configuration audit. This can be used to rebuild your system from scratch and contains system procedure output (sp__helpdb) plus a bcp in both native and character mode of important system tables. The audits require that you install our free extended stored procedure library.

    Rights and redistribution

    This package and related programs is copyright © 1996-2008 by SQL Technologies and Edward Barlow. All Rights to this program are reserved. The software is released under standard Gnu Public License (GPL) license. The license will be updated to GPL v3 when that draft is finished.

    You may redistribute the package at will (see below). Tell your friends.  Tell me about bugs. Be nice - I am making no money off this.

    You are allowed to use this software so long as all copyright notices, README, and other documentation are not altered and so long as no money is made by the sale of this software (i.e. you cant include it in a commercial package without permission). If you would like to "make money" or include the code in a commercial package, I ask that you decide on a "fair" price and create some form of "fair" agreement. Make two copies, sign them both, and send them to the package author (Edward Barlow). If the agreement seems fair, I will sign both and send one copy back to you, and we will have a deal. I have put significant effort into this code and, while my primary purpose is to create software for people to use, I expect a fair shake from anybody who can profit from my endeavors.

    Security

    Keep these scripts secure using file permissioning - files (especially your configuration files which are in the conf subdirectory) should have restricted access.  They contain your server administrative passwords. Keep them secure.

    As an added level of security, we block the unix 'ps' command from showing your passwords.

    The password files

    The password files in the conf directory contain all passwords for system access. On unix Sybase DBMS's you will need to edit unix_password.dat and sybase_password.dat. The unix information is necessary for ftp/rsh/ssh to work to remote unix systems. On windows databases, you only need to edit the information in sqlsvr_password.dat and/or sybase_password.dat as windows networking is used to manage database backup verificiaton and file copies. You will, of course, need windows networking access from your central backup server to your remote backup directories.

    The password files are pretty simple. They use the following format:

     EDSDB sa edsdbsapass
            SERVER_TYPE=PRODUCTION
     BOBSDB sa bobsdbsapass
            SERVER_TYPE=PRODUCTION
    

    Entries starting at the beginning of the line are servers and have the format servername login password. The login of course must be able to perform the necessary operations (ie be an oper role account). The lines that are indented contain server variables like SERVER_TYPE - which indicates the system is DEVELOPMENT, PRODUCTION etc...

    Configure.cfg

    The master configuration file is named configure.cfg.  This file contains your plan information. This file exists in the 'conf' directory. The configuration file configure.cfg is designed to store maintenance information keyed by maintenance plan. The file is composed of key-value pairs of the form VARIABLE=VALUE. More specifically, information is stored in either the form

     [VARIABLE]=VALUE
     or
     [PLAN_NAME]_[VARIABLE]=VALUE.
    

    When you run a task that specifies a PLAN_NAME, the [PLAN_NAME]_[VARIABLE] key will override the default [VARIABLE].  If no plan specific variable [PLAN_NAME]_[VARIABLE] exists, the default is used. 

    Each of your RDBMS systems will have 1 or more Maintenance Plans. Y The majority of your smaller RDBMS systems can have a single Maintenance Plan per DBMS. For a larger system, you might create a plan for normal everyday backups and a separate plan for weekend maintenance. It is normal to name the Maintenance Plan for SERVER1 as "SERVER1" - keeping the PLAN_NAME matching the server name keeps things readable and simple. 

    If you wish to only run particular steps (like DBCC) on weekends, we recommend you set up a Maintenance Plan named <SERVER_NAME> with DO_DBCC=n and a second Plan named <SERVER_NAME>WEEKEND or <SERVER_NAME>DBCC  with DO_DBCC=y turned on.   There are no limits on PLAN_NAMES other than that they should do a single set of tasks on a single database and the name can not contain non-printable characters like spaces (underscores are ok). 

    Each Maintenance Plan is composed of a variety of PLAN VARIABLES (listed later).

    BASE_BACKUP_DIR is a special global variable pointing to the directory structure in which you want to keep your sessionlogs and other output. 

    SYBASE is also a special global variable defining the SYBASE home directory on your scheduling server. BASE_BACKUP_DIR and SYBASE can not be overridden on a per plan basis. The SYBASE variable is only requred for backing up Sybase Database servers.

    The third 'GLOBAL' variable is IGNORE_SERVER, which contains a comma or pipe separated list of servers to not backup, an alternative to changing your scheduler if you wish to disable a backup.

    The configure.cfg ships as configure.cfg.sample - which is renamed when building the system. This file contains complete documentation on the plan variables and should be read. The below code 'sample' shows a section of configure.cfg that overrides the two number of files to keep parameters for the SYBPROD2 backup plan.

       # NUM_BACKUPS_TO_KEEP=[ A Number >=1 ]
       NUM_BACKUPS_TO_KEEP=1
       SYBPROD2_NUM_BACKUPS_TO_KEEP=2
    

       # NUM_DAYS_TO_KEEP
       #  For other backup files (log files etc), this is the number of days
       #  to keep stuff for prior to removing it)
       NUM_DAYS_TO_KEEP=7
       SYBPROD2_NUM_DAYS_TO_KEEP=10
    
    

    Plan variables

    GLOBAL BACKUP VARIABLES

    VARIABLE		EXPLANATION
    BASE_BACKUP_DIR		[ A directory for plan output files that exists on server you will schedule your plans from ]
    IGNORE_SERVER		[ pipe separated list of servers to ignore ]. Why would you do this? Well consider the situtation where you have multiple plans all running on the same server which you need to offline for a few hours. This allows you to do this. It will not be used often (The author has never used it).
    SYBASE		[ Sybase Directory On This Machine (Sybase Only) ]

    This is used by some cron jobs (which do not source an environment) and is the SYBASE environment variable for the system which will run the cron jobs. This can NOT be overridden on a per job basis (you shouldnt need to ever)

       example:
           SYBASE=/opt/sybase
    

    PLAN VARIABLES

    VARIABLE		EXPLANATION
    SERVER_NAME		The Server/Connection Name for this plan. This is the DSQUERY variable for unix/sybase or ODBC DSN on windows for the server. By convention, you should name the main plan for your server such that PLAN_NAME=SERVER_NAME. This makes things less confusing (ie. MYRDBMS_SERVER_NAME=MYRDBMS).
    SERVER_DIRECTORY		Directory on the server for your backups. e.g. D:/backups or /export/backups.. Backups files are placed in subdirectory off SERVER_DIRECTORY (see the earlier directory tree info) named SERVER_DIRECTORY/SERVER_NAME/dbdumps and SERVER_DIRECTORY/SERVER_NAME/logdumps. These may be overridden using the two optional variables below.
    	SERVER_DUMP_DIRECTORY	Override database full backup directory (SERVER_DIRECTORY/SERVER_NAME/dbdumps). Not normally set. If you set this, you must set CLIENT_PATH_TO_DUMP_DIR.
    	SERVER_LOG_DIRECTORY	Override the db tran log directory (SERVER_DIRECTORY/SERVER_NAME/logdumps). Not normally set. If you set this, you must set CLIENT_PATH_TO_LOG_DIR.
    NUM_BACKUPS_TO_KEEP		[ A Number >= 1 ] Number of full backups to keep
    DO_PURGE		[y|n] - Plan Includes Purging old files as part of normal backup
    	NUM_DAYS_TO_KEEP	[ A Number >= 1 ] The Number of Days to Keep Other Files (like session logs)
    DATABASE_IGNORE_LIST		Pipe separated list of databases ignored By Backups [db1|db2|db3]. Should include non-backupable databases - e.g. DATABASE_IGNORE_LIST=model|tempdb|pubs|sybsyntax
    MAIL_FROM		From address for mails
    MAIL_HOST		Your mail host. Not exactly sure what environments its needed in but i think only win32
    MAIL_TO		Comma Separated List of EMail addresses to send failure notices. No spaces should exist in the line.
    SUCCESS_MAIL_TO		Comma Separated List of EMail addresses to send success notices. No spaces should exist in the line.
    IS_REMOTE		[y|n] - Is The SERVER_DIRECTORY remote from the Backup Client software. Set this to 'y' if the SERVER_DIRECTORY can NOT be reached by direct file access (NFS or NT Networking) from the Client. If Y, we will/must use FTP/SSH/RSH to access the server. If 'N', the backup scripts will use copy(). (Always 'n' on Windows)
    	CLIENT_PATH_TO_DIRECTORY	If IS_REMOTE=N, then this represents the full path name the client uses to get to the SERVER_DIRECTORY. For example if SERVER_NAME=cluster4 and SERVER_DIRECTORY is e:/backups then you should set this to //cluster4/e$/backups.
    	CLIENT_PATH_TO_DUMP_DIR	Similarly, this is the full path to SERVER_DUMP_DIRECTORY. Only use if you have set SERVER_DUMP_DIRECTORY
    	CLIENT_PATH_TO_LOG_DIR	Similarly, this is the full path to SERVER_LOG_DIRECTORY. Only use if you have set SERVER_LOG_DIRECTORY
    	SERVER_HOSTNAME	This is the Host Name the DBMS resides on and is used if IS_REMOTE=Y. Used in conjunction with SERVER_DIRECTORY. (UNIX ONLY as IS_REMOTE=N for Win32)
    	SERVER_HOST_LOGIN	IF IS_REMOTE=Y this can override the Unix Account for SERVER_HOSTNAME that is identified in the password files. If not defined, will use entries from server registration. (UNIX ONLY)
    	SERVER_HOST_PASSWORD	IF IS_REMOTE=Y this can override the Unix Password for SERVER_HOSTNAME that is identified in the password files. You should set it to something random if you are using rsh/ssh (it probably needs to find a password even if it wont use it - we havent tested use of a SERVER_HOST_LOGIN without a SERVER_HOST_PASSWORD). If not defined, will use entries from server registration. (UNIX ONLY)
    SYBASE_COMPRESSION_LEVEL		Internal Compression Level (Sybase 12.5+ Only). Sybase Internal Compression Level. Set to 0 to not use internal compression (ie server version < 12.5). If non-zero, external compression utilities will NOT be used.
    DO_EXTERNAL_COMPRESS		[y|n] - Use External Compression on Backups When Completed. Use the SYBASE_COMPRESSION_LEVEL variable if you wish to use sybase internal compression.
    	COMPRESS	Full path to external compression program. Only used if not using sybase internal compression. e.g. /usr/local/bin/gzip.
    	UNCOMPRESS	Path to External UnCompression Program use if DO_EXTERNAL_COMPRESS=Y. Only used if not using sybase internal compression (SYBASE_COMPRESSION_LEVEL). e.g. /usr/bin/gzip -d.
    	COMPRESS_LATEST	[y|n] - should you do external compression on the LATEST backup files Requires DO_EXTERNAL_COMPRESS=Y. Not setting means the latest will not be compressed but the older backups will be.
    DO_UPDSTATS		[y|n] - Plan Includes Running Update Statistics (update_stats.pl)
    	UPD_STATS_FLAGS	flags for update_stats.pl. try -iv60 (for 60 buckets / index stats ) or -i (for sybase index stats)
    DO_AUDIT		[y|n] - Plan Includes Running A Configuration Audit with config_report.pl
    	AUDIT_PURGE_DAYS	Days of audit results to keep if DO_AUDIT=Y. Default=30.
    DO_DBCC		[y|n] - Plan Includes Running DBCC Checks (dbcc_db.pl)
    	DBCC_IGNORE_DB	Requires DO_DBCC=Y. In addition to DATABASE_IGNORE_LIST these databases are ignored by DBCC. You would set this if you had large databases that you want backed up but which can not be DBCC'ed every night due to time constraints. [ db1|db2|db3 ]
    DO_BCP		[y|n] - should you do bcp's as part of normal backup. This will normally be N but perhaps you wish to bcp out critical tables. These will be identified by BCP_TABLES.
    	BCP_COMMAND	Requires DO_BCP=Y. Full path to your bcp command if its not in the path
    	BCP_TABLES	Requires DO_BCP=Y. A pipe separated list of tables to copy out using bcp.
    DO_REORG		[y|n] - should you do reorg rebuild/reorg compact of normal backup (Sybase Only)
    	REORG_ARGS	Requires DO_REORG=Y - The arguments that reorg_sybase.pl will take. See command docs. Default is --REORG_COMPACT.
    DO_DUMP		[y|n] - Plan Includes Full or Incremental Backup
    	DUMP_FILES_PER_SUBDIR	A concession to data warehouses - allows you to put striped sybase dumps into multiple subdirectories. (Sybase Only).
    	NUMBER_OF_STRIPES	[ A Number >=1 ] Number Of Stripes (Sybase Only). This applies to both full and incremental backups. It is recommended that you set this to 4 and leave it at 4 for all your servers/plans.
    	DO_TRUNCLOG_BEFOREDUMP	[y|n] - clear transaction logs before full backups
    DO_INDEXES		[y|n] - should you perform our indexes matching as part of normal backup. This step is not a rebuild index step - it is a index doublecheck step that uses a configuration file to ensure that your databases have at least the indexes you want. You create the list of indexes and then this step will recreate them if they are missing.
    	REBUILD_INDEX_DB	[db|db|db] Databases To Rebuild Indexes For
    	REBUILD_INDEX_FILE	[file|file|file] Index File To Use For Rebuilding OF Indexes

    VARIABLES FOR LOG SHIPPING

    These varialbes are used for log shipping.  Log shipping will be invoked every time you run backup.pl.

    VARIABLE		EXPLANATION
    DO_LOAD		[y|n] - Plan includes incremental tran loads which you will ship and load Into Another Server.
    DO_ONLINEDB		[y|n] Run online db after done with loads (Sybase Only). This is normally set to N because you will be in a log shipping situation - so you will not online your sybase dbs. But if you wanted the db to be live after you ship it - for example you are syncing a development copy o.f the db from production on a daily basis
    XFER_TO_SERVER		[ pipe separated list ] server SERVER_NAME or ODBC names you want to copy to
    XFER_TO_DB		[ pipe separated list ]  databases to copy into in XFER_TO_SERVER. use '*' for all databases
    XFER_FROM_DB		[ pipe separated list ]  database on primary to copy from
    XFER_BY_FTP		[ Y|N ftp the files to the remote system? ] If 'N' will use direct file copy
    XFER_TO_DIR		Directory on Target (full pathspec) as seen by Caller (ie //mysrv/D$/logship). This directory will have the standard dbdumps/logdumps subdirs  that the dumps go into as seen by the loading host (XFER_TO_HOST).
    XFER_SCRATCH_DIR		[ directory on local system for tmp space ] this is an intermediate directory we copy to on the server running the backups. This should not be in /tmp. It will contain 1 of the stripes of your backups.
    XFER_TO_HOST		[ hostname to transfer files to ]
    XFER_TO_DIR_BY_TARGET		Directory on Target as seen by Target (ie D:/logship)

    Remote purging and compression

    Because our architecture involves scheduling your dumps out of a central location, we must have a solution for purging old backup files and for compressing them. Sybase 12.5 and later servers support internal backup compression, but Sybase 11.9.2 and SQL Server do not. Our solution to this is seamless. One caveat. It takes time to compress backups. It takes SIGNIFICANTLY longer to compress a backup file than it does to create it. You can, however, get up to 90% compression.

    Our scripts support purging and compression in two modes. The first mode, direct file access, assumes that the monitoring / scheduling server can directly access the backup files. This is true if you are backing up to an NFS share or when running under Windows Networking. Plans using this solution will set IS_LOCAL=y and setup SERVER_DIRECTORY to the appropriate directory path. This system uses full path urls on windows (ie. SERVER_DIRECTORY=//system/c$/backups).  Be careful to use the UNIX file structure (separate directories with the / character not the \ character). The '\' character is often used in perl to escape metacharacters. As always, test your backups.

    On UNIX, the system supports file purging & compression using and FTP, RSH, and SSH. You are responsible for creating the communication path (ie setting up the .rhost file if you want to use RSH). 

    If you are not using sybase internal compression (either because you have an 11.9.2 or earlier sybase server or because you are working with a microsoft sql server), be aware of some facts with the optional compression.  Firstly, to compress you need double the disk space of the dump files (compress will make a copy that it compresses).  This can cause all  kinds of out of space disk errors if you are not careful.  Secondly, the compression will fail on UNIX due to permission violations when run by any account except Sybase or root (not recommended).  The error will be : permission violation (cant modify to file).  Finally, your compression on remote systems will be done over the network - which can be slow - it is not recommended once your databases exceed 20GB or so in size. The plus of this compression scheme is that database backups can be greatly shrunk with compress.  It is normal to see a 1GB dump file shrink to 100MB!  This can alleviate space concerns on your systems.  If you have no space issues, you do not need to compress.

    Setting up your crontab

    These scripts can be scheduled using the windows task scheduler or unix cron.  On unix this is done with crontab -e.  If your editor doesn't work here, set the environment variable EDITOR (e.g. export EDITOR=/bin/vi).  If you get a permission violation when running crontab -e, contact your unix support person and have them set you up to run cron jobs.  As the output of backup.pl already goes to the session file, you can redirect cron output to /dev/null so you dont get voluminous mail. (ie. /home/programs/bin/backup.pl -JMYSERVER >/dev/null). You probably want to dump tran logs every ten-twenty minutes on production systems.  Transaction log backups are done by passing -t to backup.pl.  You can also pass -n to backup.pl to copy your transaction logs but not load them - and can pass -s to skip the transfer  and load step alltogether.  The fix_logship.pl script will identify differences between your production and dr site and copy missing files and load all unapplied files as appropriate. 

    Error handling

    These scripts provide a default error handler. This will basically an email that is sent on failure and on successful full backup.  You an also easily configure the error handler to integrate with your own error handler.  The scripts run the perl module Log::Dispatcher out of the file on_error.pl.  If you wish to modify the default behavior of this error handler (which is to put errors in the errors sub directory and session logs in the sessionlog sub directory, and then to mail to a specified set of users), you simply need modify the on_error.pl.  Log::Dispatcher is easy to use and supports a variety of log and error handling options.

    Frequently asked questions

    The package does not work from your scheduler, but runs fine from the command line: There could well be some environmental difference that is breaking things.  Specifically, check that the environment variable SYBASE is defined.

    I get permission violations when running: The scheduler should run out of the same account that runs the Sybase server.  On UNIX it should not run as root (if you run out of root, you should do a "su - Sybase -c" but this is not recommended).  In fact to make this a little more clear, almost every real problem I have ever encountered with the scripts has been by people using "su - Sybase -c..." instead of running out of sybase's crontab (so just dont do it).

    How can i find if there were problems last night: A session log AND a separate error log are created for each run.  The error log is removed after the run if it is empty.  So, to tell if there were problems just look in the errors subdirectories in the output file tree.  If there are any files there, there were problems.  Remove these files each day and you will know if there are any events that need attention.  Additionally, the default error handler sends you mail with any problems.

    I need to set up paging for problems:   You will need a script that sends pages and to add it to the on_error.pl file.  See the section on error handling.

    The code looks funny (tabbing is wierd): All scripts can be viewed and edited best with tabstop=3.

    Are there any bad table names? If you have tables with the string 'corrupt' in it, you may have trouble with the dbcc program. corrupt is a keyword that i search for to find dbcc errors.  There is no current work around for this but it would not be hard to implement. 

    How can i use BCP to copy out tables

     Here are details on what happens when you specify BCP options
    

     a) config file read - specifically DO_BCP, BCP_TABLES and BCP_COMMAND directives.
     b) @tables = split(/[\|\,]/,$BCP_TABLES
           - tables are the BCP_TABLES directive split by pipes and commas.
     c) PSEUDOCODE:
        foreach $table in (@tables) {
             print "copying out table $table"
             $cmd= $BCP_COMMAND || bcp
                     - command is the BCP_COMMAND directive or just straight bcp if that is not defined
              execute : $cmd out $base_bk_dir/bcp/$table.bcp -Uxxx -Pxxx -Sxxx -n
        }
    

        the execute: statement should print the command to be executed (with password as xxx of
        course so nobody reading the log file can get the  sa password!)  You should see a line saying
        copying out table <nm> followed by something that looks and smells like a bcp command line.
      d) to test you can just run your backup with ONLY the bcp turned on.  to do this run the command:
          backup.pl -JJOBNAME -Rb
    

         to see diagnostic messages you can also add the -d flag.
    

    Integration with gem

    These scripts are an integral part of the Generic Enterprise Manager (GEM). GEM provides a full featured graphical user interface to these scripts, and provides an additional layer of reporting and alarming for your backups. GEM effectively reports on the *state* of your maintenance scripts, cross checking backup file sizes, server log files, and script output so you can guarantee that your systems were effectively backed up. GEM also provides real time Alarming and Monitoring of your maintenance scripts.

    These scripts are free, but if you like them, give GEM a try.

    Menu.pl / cgi.pl

    In addition to a command line interface, the package provides a simple user interface through an ascii menu (menu.pl) and from a web page (cgi.pl).

    The basic flow of menu.pl is as follows:

    IF THERE ARE MULTIPLE SERVERS DEFINED

    WELCOME SCREEN

       1. SELECT SERVER XXX
       2. SELECT SERVER YYY
    

    IF ONLY ONE SERVER IS DEFINED OR YOU HAVE SELECTED A SERVER FROM THE ABOVE LIST

    SERVER XXX BACKUP MENU

       1. Test Connectivity
       2. Test RSH Access
       3. Check Configuration
       4. Perform Full Server Backups
       5. Backup A Single Database
       6. Restore A Database
       7. Start Database Server
       8. Stop Database Server
       9. Start Backup Server
       10. Stop Backup Server
       11. View Backup Error Logs
       12. Cleanup Backup Log Files
       13. Show Database Server Error Log (Filtered)
       14. Show Database Server Error Log (Filtered, Last 2 Days Only)
       15. Show Backup Server Errorlog
       16. Debug This Server
       17. QUIT  
    

    TO DO ITEMS

    There really is not much to do on the packages. The scripts will need an update for any new features in SQL Server 2005 and will need to be updated to run dbcc checkstorage (if anyone needs that - contact us). Also, some of the scripts use the -U/-S/-P/-J format and others use --USER=/--SERVER=/--PASSWORD= format. I prefer the latter, and will probably eventually migrate all the scripts to that format. Its easier to read

    Sample configuration directives

    The following are examples of configure.cfg settings for a variety of systems. They are intended for illustration only.

    You must test your setup. I recommend backup.pl -JJOBNAME -Dmaster. I use master because it is fast and a good db to try stuff on while setting things up. If your backup completes, then you can schedule the job. If you are using GEM, .ksh and .bat files are automatically created for each of your backup server jobs in the GEM Batch Jobs directory. You should run these.

    A LOCAL SERVER

    The following is an example of the simple case of backing up a local unix database server to /export/home/sybase-dump.

     MYRDBMS_SERVER_NAME=MYRDBMS
     MYRDBMS_CLIENT_PATH_TO_DIRECTORY=/export/home/sybase-dump
     MYRDBMS_DATABASE_IGNORE_LIST=model|tempdb|sybsyntax
     MYRDBMS_SYBASE_COMPRESSION_LEVEL=1
     MYRDBMS_COMPRESS_LATEST=n
     MYRDBMS_IS_REMOTE=n
     MYRDBMS_UPD_STATS_FLAGS=-i
    
    

    Now let us modify the above example to copy database emb_test to emb_test2 on the same server (it could be another server but its the simple case). Remember to set DO_ONLINEDB=Y (actually MYRDBMS_DO_ONLINDEDB) if you are using sybase and want the database onlined (ie. you will not be applying tran logs). To do this, we copy the above settings and append

     MYRDBMS_DO_ONLINEDB=y
     MYRDBMS_XFER_TO_SERVER=MYRDBMS
     MYRDBMS_XFER_FROM_DB=emb_test
     MYRDBMS_XFER_TO_DB=emb_test2
     MYRDBMS_XFER_BY_FTP=N
     MYRDBMS_XFER_TO_DIR=
     MYRDBMS_XFER_TO_DIR_BY_TARGET=
    
    

    The XFER_BY_FTP=N means the server is local (no copy needed) and the empty TO_DIR variables at the end also indicate that we need not copy the files. By the way, we can test this plan with "backup.pl -JMYRDBMS -Demb_test -Rdlp". The -R option specifies that we only want to runs the dump, load, and purge steps. This command will backup our database emb_test and load it into emb_test2.

    A REMOTE SERVER

    The next example of backing up a simple non-local server that resides on the hostname myrdbms to the directory /export/home/sybase-dump on that system. Note that when this plan runs, it will run an AUDIT of your configuration (ie. reverse engineer setup stuff & bcp out core system tables so you can recover), and will DBCC, Backup, and Update Statistics on all databases not in the DATABASE_IGNORE_LIST.

     MYRDBMS_SYBASE_COMPRESSION_LEVEL=1
     MYRDBMS_DO_AUDIT=y
     MYRDBMS_DO_DBCC=y
     MYRDBMS_DO_LOAD=n
     MYRDBMS_DO_DUMP=y
     MYRDBMS_DO_PURGE=y
     MYRDBMS_DO_REORG=n
     MYRDBMS_DO_INDEXES=n
     MYRDBMS_DO_UPDSTATS=y
     MYRDBMS_DO_BCP=n
     MYRDBMS_SERVER_NAME=MYRDBMS
     MYRDBMS_DATABASE_IGNORE_LIST=sybsystemdb|model|junkdb1|junkdb2|text_db|tempdb
     MYRDBMS_IS_REMOTE=y
     MYRDBMS_MAIL_TO=ebarlow@myco.com,mydba@myco.com
     MYRDBMS_NUM_DAYS_TO_KEEP=2
     MYRDBMS_SERVER_DIRECTORY=/export/home/sybase-dump
     MYRDBMS_SERVER_HOSTNAME=myrdbms
    
    

    Sybase Server Copying Single Database Within The Server

     MYRDBMS_COPY_SERVER_NAME=MAGIDDB
     MYRDBMS_COPY_SERVER_DIRECTORY=/export/home/sybase-dump
     MYRDBMS_COPY_NUM_DAYS_TO_KEEP=2
     MYRDBMS_COPY_SYBASE_COMPRESSION_LEVEL=1
     MYRDBMS_COPY_DATABASE_IGNORE_LIST=sybsystemdb|testing
     MYRDBMS_COPY_MAIL_TO=me@x.com
     MYRDBMS_COPY_DO_LOAD=y
     MYRDBMS_COPY_IS_REMOTE=y
     MYRDBMS_COPY_SERVER_HOSTNAME=mydb
     MYRDBMS_COPY_DBCC_IGNORE_DB=testing
     MYRDBMS_COPY_DO_ONLINEDB=Y
     MYRDBMS_COPY_XFER_TO_SERVER=MAGIDDB
     MYRDBMS_COPY_XFER_FROM_DB=ettrddb
     MYRDBMS_COPY_XFER_TO_DB=testing
     MYRDBMS_COPY_XFER_BY_FTP=N
     MYRDBMS_COPY_XFER_TO_DIR=
     MYRDBMS_COPY_XFER_TO_DIR_BY_TARGET=
    
    

    SQL Server With Tran Log Shipping

    The SQL SERVER ABCD has large and small databases, which are backed up separately and are transferred to the remote log shipping dr server DRSERVER001. We would scheule these jobs like

     # FULL DUMP SCRIPTS
     backup.pl -JMYRDBMS_SMALL
     backup.pl -JMYRDBMS_BIG -DMPdb
     backup.pl -JMYRDBMS_BIG -DWebReports
     backup.pl -JMYRDBMS_BIG -DMPdb2
     # TRAN DUMP SCRIPTS
     backup.pl -JMYRDBMS_SMALL -t
     backup.pl -JMYRDBMS_BIG -DMPdb -t
     backup.pl -JMYRDBMS_BIG -DWebReports -t
     backup.pl -JMYRDBMS_BIG -DMPdb2 -t
    
    

    Note that we do not clear the logs before dumping.

     MYRDBMS_SMALL_SYBASE_COMPRESSION_LEVEL=0
     MYRDBMS_SMALL_DO_CLEARLOGSBEFOREDUMP=n
     MYRDBMS_SMALL_DO_EXTERNAL_COMPRESS=y
     MYRDBMS_SMALL_DO_LOAD=y
     MYRDBMS_SMALL_DO_UPDSTATS=n
     MYRDBMS_SMALL_SERVER_NAME=ABCD
     MYRDBMS_SMALL_DATABASE_IGNORE_LIST=model|tempdb|sybsyntax|master|msdb|pubs|pubs2|Northwind|MPdb|WebReports|MPdb2
     MYRDBMS_SMALL_IS_REMOTE=n
     MYRDBMS_SMALL_CLIENT_PATH_TO_DIRECTORY=E:/backups
     MYRDBMS_SMALL_NUMBER_OF_STRIPES=0
     MYRDBMS_SMALL_NUM_BACKUPS_TO_KEEP=1
     MYRDBMS_SMALL_SERVER_DIRECTORY=E:/backups
     MYRDBMS_SMALL_UNCOMPRESS=
     MYRDBMS_SMALL_XFER_BY_FTP=N
     MYRDBMS_SMALL_XFER_FROM_DB=*
     MYRDBMS_SMALL_XFER_TO_DB=*
     MYRDBMS_SMALL_XFER_TO_DIR=//drserver/d$/logshipbuffer
     MYRDBMS_SMALL_XFER_TO_DIR_BY_TARGET=D:/logshipbuffer
     MYRDBMS_SMALL_XFER_TO_HOST=
     MYRDBMS_SMALL_XFER_TO_SERVER=DRSERVER001
     MYRDBMS_BIG_SYBASE_COMPRESSION_LEVEL=0
     MYRDBMS_BIG_DO_CLEARLOGSBEFOREDUMP=n
     MYRDBMS_BIG_DO_EXTERNAL_COMPRESS=n
     MYRDBMS_BIG_DO_DUMP=y
     MYRDBMS_BIG_DO_LOAD=y
     MYRDBMS_BIG_DO_UPDSTATS=n
     MYRDBMS_BIG_SERVER_NAME=ABCD
     MYRDBMS_BIG_DATABASE_IGNORE_LIST=model|tempdb|sybsyntax|master|msdb|pubs|pubs2|Northwind
     MYRDBMS_BIG_IS_REMOTE=n
     MYRDBMS_BIG_CLIENT_PATH_TO_DIRECTORY=//abcd/e$/backups
     MYRDBMS_BIG_CLIENT_PATH_TO_DIRECTORY=//abcd/f$/backups
     MYRDBMS_BIG_CLIENT_PATH_TO_DIRECTORY=E:/backups
     MYRDBMS_BIG_NUMBER_OF_STRIPES=0
     MYRDBMS_BIG_NUM_BACKUPS_TO_KEEP=1
     MYRDBMS_BIG_SERVER_DIRECTORY=E:/backups
     MYRDBMS_BIG_SERVER_DIRECTORY=F:/backups
     MYRDBMS_BIG_UNCOMPRESS=
     MYRDBMS_BIG_XFER_BY_FTP=N
     MYRDBMS_BIG_XFER_FROM_DB=*
     MYRDBMS_BIG_XFER_TO_DB=*
     MYRDBMS_BIG_XFER_TO_DIR=//drserver/d$/logshipbuffer
     MYRDBMS_BIG_XFER_TO_DIR_BY_TARGET=D:/logshipbuffer
     MYRDBMS_BIG_XFER_TO_HOST=
     MYRDBMS_BIG_XFER_TO_SERVER=DRSERVER001
    
    

    Large Sybase Unix Db

    Here we set up two plans that only differ in their options and schedule them. I have called them MYRDBMS (backup) and MYRDBMSDBCC (once per week for dbcc & reorg only). Note that these scripts basically only differ in the DO_* flags

     MYRDBMS_DBCC_SYBASE_COMPRESSION_LEVEL=1
     MYRDBMS_DBCC_DO_DBCC=y
     MYRDBMS_DBCC_DO_LOAD=n
     MYRDBMS_DBCC_DO_DUMP=n
     MYRDBMS_DBCC_DO_REORG=y
     MYRDBMS_DBCC_DO_UPDSTATS=y
     MYRDBMS_DBCC_SERVER_NAME=MYRDBMS
     MYRDBMS_DBCC_IGNORE_DB=sybsystemdb|model|junkdb1|junkdb2|text_db|tempdb
     MYRDBMS_DBCC_IS_REMOTE=y
     MYRDBMS_DBCC_MAIL_TO=ebarlow@myco.com,mydba@myco.com
     MYRDBMS_DBCC_NUM_DAYS_TO_KEEP=2
     MYRDBMS_DBCC_SERVER_DIRECTORY=/export/home/sybase-dump
     MYRDBMS_DBCC_SERVER_HOSTNAME=myrdbms
    
     MYRDBMS_SYBASE_COMPRESSION_LEVEL=1
     MYRDBMS_DO_DBCC=n
     MYRDBMS_DO_LOAD=n
     MYRDBMS_DO_UPDSTATS=n
     MYRDBMS_SERVER_NAME=MYRDBMS
     MYRDBMS_DATABASE_IGNORE_LIST=sybsystemdb|model|junkdb1|junkdb2|text_db|tempdb
     MYRDBMS_IS_REMOTE=y
     MYRDBMS_MAIL_TO=ebarlow@myco.com,mydba@myco.com
     MYRDBMS_NUM_DAYS_TO_KEEP=2
     MYRDBMS_SERVER_DIRECTORY=/export/home/sybase-dump
     MYRDBMS_SERVER_HOSTNAME=myrdbms
     MYRDBMS_UPD_STATS_FLAGS=-i
    
    

    SQL Server With All the Bells And Whistles

    Logshipping, Compression,etc...

     MYRDBMS_COMPRESS=D:/PROGRA~1/MKSToo~1/mksnt/mkszip.exe -l1
     MYRDBMS_SYBASE_COMPRESSION_LEVEL=0
     MYRDBMS_DO_CLEARLOGSBEFOREDUMP=n
     MYRDBMS_DO_EXTERNAL_COMPRESS=n
     MYRDBMS_DO_DUMP=n
     MYRDBMS_DO_LOAD=y
     MYRDBMS_DO_UPDSTATS=n
     MYRDBMS_SERVER_NAME=PLATINUM
     MYRDBMS_DATABASE_IGNORE_LIST=model|tempdb|sybsyntax|master|msdb|pubs|pubs2|Northwind
     MYRDBMS_IS_REMOTE=n
     MYRDBMS_CLIENT_PATH_TO_DIRECTORY=//adcluster400/e$/logdump
     MYRDBMS_CLIENT_PATH_TO_DIRECTORY=//platinum/e$/logdump
     MYRDBMS_NUMBER_OF_STRIPES=0
     MYRDBMS_NUM_BACKUPS_TO_KEEP=1
     MYRDBMS_SERVER_DIRECTORY=D:/backups
     MYRDBMS_XFER_BY_FTP=N
     MYRDBMS_XFER_FROM_DB=*
     MYRDBMS_XFER_TO_DB=*
     MYRDBMS_XFER_TO_DIR=//adsdr012/f$/logshipbuffer
     MYRDBMS_XFER_TO_DIR_BY_TARGET=f:/logshipbuffer
     MYRDBMS_XFER_TO_SERVER=DRSERVER012
    
    

    Another Sybase Server

    note we are using index stats and 60 buckets

     MYSYBASE_SYBASE_COMPRESSION_LEVEL=1
     MYSYBASE_COMPRESS_LATEST=y
     MYSYBASE_DO_EXTERNAL_COMPRESS=y
     MYSYBASE_DO_DBCC=n
     MYSYBASE_DO_DUMP=y
     MYSYBASE_DO_LOAD=n
     MYSYBASE_DO_PURGE=y
     MYSYBASE_DO_UPDSTATS=n
     MYSYBASE_DO_UPDSTATS=y
     MYSYBASE_SERVER_NAME=MYSYBASE
     MYSYBASE_IS_REMOTE=y
     MYSYBASE_NUM_DAYS_TO_KEEP=1
     MYSYBASE_NUM_DAYS_TO_KEEP=2
     MYSYBASE_SERVER_DIRECTORY=/export/home/sybase-dump
     MYSYBASE_SERVER_HOSTNAME=ssdb
     MYSYBASE_SERVER_HOSTNAME=ssdb2
     MYSYBASE_UPD_STATS_FLAGS=-i
     MYSYBASE_UPD_STATS_FLAGS=-iv60
     MYSYBASE_XFER_BY_FTP=Y
     MYSYBASE_XFER_FROM_DB=tlm|mlp_tlm
     MYSYBASE_XFER_SCRATCH_DIR=/apps/sybmon/tmp
     MYSYBASE_XFER_TO_DB=tlm|mlp_tlm
     MYSYBASE_XFER_TO_DIR=/export/home/sybase-dump
     MYSYBASE_XFER_TO_HOST=elmssdb
     MYSYBASE_XFER_TO_HOST=elmssdb2
     MYSYBASE_XFER_TO_SERVER=MYSYBASEDR
    
    

    Another Sql Server

     SQLSVR1_COMPRESS=D:/PROGRA~1/MKSToo~1/mksnt/mkszip.exe -l1
     SQLSVR1_SYBASE_COMPRESSION_LEVEL=0
     SQLSVR1_DO_CLEARLOGSBEFOREDUMP=n
     SQLSVR1_DO_EXTERNAL_COMPRESS=n
     SQLSVR1_DO_DUMP=n
     SQLSVR1_DO_LOAD=y
     SQLSVR1_DO_UPDSTATS=n
     SQLSVR1_SERVER_NAME=SUNGARD
     SQLSVR1_DATABASE_IGNORE_LIST=model|tempdb|sybsyntax|master|msdb|pubs|pubs2|Northwind
     SQLSVR1_IS_REMOTE=n
     SQLSVR1_CLIENT_PATH_TO_DIRECTORY=//sqlsvr1/f$/logshipbuffer
     SQLSVR1_NUMBER_OF_STRIPES=0
     SQLSVR1_NUM_BACKUPS_TO_KEEP=1
     SQLSVR1_SERVER_DIRECTORY=D:/backups
     SQLSVR1_XFER_BY_FTP=N
     SQLSVR1_XFER_FROM_DB=*
     SQLSVR1_XFER_TO_DB=*
     SQLSVR1_XFER_TO_DIR=//adsdr007/e$/logshipbuffer
     SQLSVR1_XFER_TO_DIR_BY_TARGET=E:/logshipbuffer
     SQLSVR1_XFER_TO_SERVER=DRSERVER007
    
    

    Some databases are copied and loaded once a night

     SYB1_TO_SYB2_SERVER_NAME=SYB1
     SYB1_TO_SYB2_SERVER_DIRECTORY=/export/home/sybase-dump
     SYB1_TO_SYB2_DATABASE_IGNORE_LIST=model|tempdb|sybsyntax|clientdata|shareddata|archivedata|xREP1_RS_RSSD|master|syblogins|sybsystemdb|sybsystemprocs|xvue|xvue_test|xvue_test2|xvue_new|xvue3|xvue2
     SYB1_TO_SYB2_DO_REORG=n
     SYB1_TO_SYB2_DO_EXTERNAL_COMPRESS=n
     SYB1_TO_SYB2_COMPRESS_LATEST=n
     SYB1_TO_SYB2_DO_UPDSTATS=n
     SYB1_TO_SYB2_DO_DBCC=n
     SYB1_TO_SYB2_DO_DUMP=y
     SYB1_TO_SYB2_DO_LOAD=y
     SYB1_TO_SYB2_DO_ONLINEDB=y
     SYB1_TO_SYB2_DO_PURGE=y
     SYB1_TO_SYB2_IS_REMOTE=y
     SYB1_TO_SYB2_UPD_STATS_FLAGS=-iv60
     SYB1_TO_SYB2_SERVER_HOSTNAME=syb1
     SYB1_TO_SYB2_XFER_TO_HOST=syb2
     SYB1_TO_SYB2_XFER_TO_SERVER=SYB2
     SYB1_TO_SYB2_XFER_FROM_DB=blahsys|blahmgmt|blahdata|blahbatch|consolidation|mlparchive|dbvue
     SYB1_TO_SYB2_XFER_TO_DB=blahsys|blahmgmt|blahdata|blahbatch|consolidation|mlparchive|dbvue
     SYB1_TO_SYB2_XFER_BY_FTP=Y
     SYB1_TO_SYB2_XFER_SCRATCH_DIR=/export/home/tmp
     SYB1_TO_SYB2_XFER_TO_DIR=/export/home/sybase-dump
    
    

    Another Log Shipping Example

    Existing DB MXDBDR..repproddb goes to MXMYRDBMS_..dr_repproddb

       {Normal Backup Params)
       MYRDBMS_XFER_TO_SERVER=MXMYRDBMS_
       MYRDBMS_XFER_TO_DB=dr_repproddb
       MYRDBMS_XFER_FROM_DB=repproddb
       MYRDBMS_XFER_TO_HOST=mxproddb
       MYRDBMS_XFER_TO_DIR=/export/home/sybase-dump
       MYRDBMS_XFER_SCRATCH_DIR=/export/home/sybase/tmp
       MYRDBMS_XFER_BY_FTP=Y
       MYRDBMS_DO_AUDIT=N
    
    

    initial sync via

       perl backup.pl -JMYRDBMS -Drepproddb
    
    

    with the following scheduled tasks:

       nightly: perl backup.pl -JMYRDBMS -n -Drepproddb
       hourly:  perl backup.pl -JMYRDBMS -t -Drepproddb
    
    

    
    

    backup.pl master driver for backup scripts

    USAGE

    backup.pl -S SERVER -l|-f

    DESCRIPTION

    backup.pl is the main driver script for your operations and will appropriately call the other scripts in this package to get its work done. It is probably the only program you will add to your scheduler. It reads the configuration file and process servers based on the options you have specified. Operations are performed in the following order:

            Purge Old Dumps (if necessary use rsh)
            Dbcc Databases
            Dump Tran Log
            Full Dump
            Check for Required Database Loads
            Update Statistics
            Run Audit
            Optional Table Level Bcps
            Compress The Dumps (if necesary use rsh)
            Rebuild Indexes
    

    USAGE

    Basic Usage:

     Usage: backup.pl -JJOB -t|-f [-dh]
    

    Advanced Usage:

    Usage: backup.pl -JJOB -t|-f [-dh] -mmode

    This is THE Master backup script. This script performs appropriate tasks as defined by maintenance plans (specified with -JJOB) which are defined in configure.cfg (located in backup scripts directory or one level above it).

     -x noexec mode                         (purges will exec but other stuff wont)
     -n copy but dont load for logshipping
     -t for transaction log dumps only
     -f for full backups    (default).
     -m mode = SSH|FTP|RSH  (override communication mode)
     -d is debug mode
     -b batchid (for alarming)
     -h is html output.
     -s skip transfer and load step
     -DDB_LIST (pipe separated) restriction on databases
     -RlpdDuabCir will run only the (l)oad, (p)urge, (d)ump, (D)bcc, (r)eorg,
          (u)pdate stats, (a)udit, (b)cp, (C)ompress, and (i)ndex steps.
     -p dont purge tranlogs
    

    SYNOPSIS

    This is the master driver for the backup scripts. It relies on the Job being defined in the configure.cfg (located in backup scripts directory or one level above it). It follows instructions and performs the appropriate tasks as needed. -t for transaction log dumps only, -f for full backups. -d is debug mode and -h is html output.

    You can also specify -DDB_LIST to restrict databases operations are performed on further.

    Notes

    A transaction log dump (with no_log) is always done prior to the full dump.

    tempdb and model are NEVER dumped

    Optionally Compress the dumps

    Update Statistics and Recompile will never work on tempdb/model

    Audit Reports require extended procs to be installed

    On production servers, you will also wish to run backup.pl -l. This does a transaction log dump on SOME of your databases. The databases that are dumped are those that are "production", which means that "trunc. log on checkpoint" is off and "select into/bcp" is also off. Of course, master, model, and tempdb are never transaction log dumped. Log dumps are placed in dated files in the .../logdumps sub directory.

    
    

    config_report.pl Server Configuration Report

    SYNOPSIS

    Creates a configuration report for a server that can be used to recreate that server from scratch.

    AUTHOR

    By Ed Barlow

    USAGE

    config_report.pl -USER=SA_USER -SERVER=SERVER -PASSWORD=SA_PASS [-OUTFILE=FILE]

            -or-
    

    config_report.pl -TYPE=sybase|sqlsvr|all [-OUTDIR]

       -BATCHID=BatchDesignator
       -OPTDIAG=path_to_optdiag # if set run optdiag output
       -OUTDIR=dir              # directory for output - otherwise stdlocation
       -STDLOCATION             # make the output file be in standard location ~gem/data/...
       -OPTDIAG=path_to_optdiag
       -HTML                    # output in html format
       -NOBCP                   # Do not bcp out key files
       -ERRORLOG=errorlog   (optional)
       -LOGFILE=sessionlog (optional)
       -KEEPVERSIONS={versions to keep (default=7)}
    

    Creates a configuration report for a server that can be used to recreate that server from scratch.

    DESCRIPTION

    This program creates a configuration report for a server that can be used to recreate that server from scratch. This is intended as documentation for use by the system administrator in case your server crashes or has a problem. This program should produce virtually everything about your server that you might care about except user object DDL.

    Requires: Requires the extended stored procedure library

    WHAT IT PRINTS

       SERVER:  srvname
       @@version
       helpdb
       configure
       helpmirror
       vdevno
       helpdevice
       helplogin
       helpuser by db
       all the reverse engineering routines
    

    
    

    dbcc_checktable.pl dbcc for larger databases

    AUTHOR

    By Ed Barlow

    DESCRIPTION

    An inteligent dbcc that works on table sizes and tries to keep running only for a specified amount of minutes. Note that DBCC does not stop if it might go over that limit, so we run until the total time taken exceeds the MINUTES passed in.

    If you pass a database list it will create the file listing in FILE. This consists of database, object, and pages. This list is run through sequentially. The system calculates an estimate of the number of seconds that the table will take. If time_taken + estimate > minutes_parameter, then the dbcc checktable will not run.

    If you pass in an ESTIMATEFILE as an optional parameter - better statistics are automatically created and managed. Default statistics are based on total pages processed and total time taken *in this run*. Better statistics (from the ESTIMATEFILE) are based on the historical average of rows per second for a single table and grouped into categories based on table size. The default estimate will be conservative because there is overhead factored in. The better statistics are required if you wish to work on files other than in straight linear order - smallest to fastest.

    Say you did the first 500 tables (smallest) on one saturday. The use of estimated statistics could allow you to do the next 40 (largest) on the following saturday. You could not do this safely with default stats.

    USAGE

    USAGE: dbcc_checktable.pl -UUSER -SSERVER -PPASS -FFILE -TMINUTES -DDATABASE_LIST -EESTIMATEFILE

    If you pass a database list it will create the file listing in FILE. MINUTES is the number of minutes to run before stopping (estimate). ESTIMATEFILE is optional parameter - to keep track of time estimates.

    
    

    dbcc_db.pl utility to dbcc databases

    AUTHOR

    By Ed Barlow

    USAGE

    dbcc_db.pl -USA_USER -SSERVER -PSA_PASS -DDB_LIST

       -p          print all output from dbcc
       -T Type       Sybase or Odbc
       -D DB_LIST    pipe separated list with wildcards of databases
       -d       debug mode
       -h       html output
       -o outputdir  for raw output (file is server.db.dbcc.datestamp)
       -e errorlog   optional
       -l sessionlog optional
    

       Runs standard dbcc on the list of databases
    

    DESCRIPTION

    Runs dbcc checkdb, checkalloc, and checkcatalog on your databases. Only prints output if there is a problem.

    
    

    dump_database.pl utility to backup databases

    DESCRIPTION

    Backs up database or does a transaction log dump. Can be set for tran log dumps with -t or will, by default, perform full database dumps. Places database dumps in DIRECTORY/$SERVER.$(database)_dbdump.yyyymmdd.hhmmss and tran log dumps into DIRECTORY/$SERVER.$(database).logdump.yyyymmdd.hhmmss It will ignore databases as appropriate, following the following rules:

                 1) It will not perfore a transaction log dump if "trunc. log on chkpt" set
    

                 2) model,tempdb, and sybsyntax are not dumped unless specifically identified with -d option
    

                 3) It will not dump transaction logs if data and log on the same device
    

    It will also warn if user databases have select into set but do not have trunc. log on chkpt set. The dump files will have a .tmp in them until the dump is completed, at which point they will be renamed with the .dbdump or .logdump extension if the -r option is specified.

    USAGE

    dump_database.pl -nnum_stripes -USA_USER -SSERVER -PSA_PASS -DDB_LIST -oDIRECTORY -tdhfc -X0-10 -MType

       -n Num stripes (.SX appendended to dump name)
       -v Num         use devices: Num dumps per subdirectory (device_#)
       -D DB_LIST     (pipe separated list with wildcards of databases)
       -o DIR         (output directory)
       -t             (Dump Transaction Log only)
       -f             (Full Dump - default)
       -M             Server Type for DBI connection String (ODBC, Sybase...)
       -d             (debug mode - for odbc errors use -z)
       -h             (html output)
       -e errorlog    (optional)
       -l sessionlog  (optional)
       -s time        (set time on dump files)
       -c             (clear tran log prior to dumping)
       -X             Compression Level On The Dumps (Sybase only)
       -Y             force std output prints (wont print to stdout if cron job)
    

       Dump a database to the specified directory in with the name
          {SERVER}.{DATABASE}.dbdump.yyyymmdd.hhmmss
       or (if striped)
          {SERVER}.{DATABASE}.dbdump.yyyymmdd.hhmmss.S?
    

    MORE DESCRIPTION

    Backs Up Databases. Can be set for tran log dumps with -t or will, by default, back up full database dumps. Places databases in

       DIRECTORY/$SERVER.$(database)_dbdump.yyyymmdd.hhmmss
    

    and tran log dumps into

       DIRECTORY/$SERVER.$(database).logdump.yyyymmdd.hhmmss
    

    It will ignore databases as appropriate. Specifically the ruls are as follows:

       Cant Tran Dump if "trunc. log on chkpt" set
       model,tempdb, and sybsyntax are not dumped unless specifically set
          with -d option
       Cant Tran Dump if data and log on the same device
    

    It will also warn if user databases have select into set but do not have trunc. log on chkpt set.

    
    

    fix_logship.pl sync two directories

    USAGE

    fix_logship.pl -Jjob -Ssourcedir -hsourceUnixHostname -Ttargetdir -ttargetUnixHostname -Ddatabase -UN -u/usr/bin/gunzip

     -f full dumps resync (will load dumps too)
     -y since yesterday (24 hours)
     -s since time - yyyymmdd.hhmmss
     -p purge target only files
     -N nocopy - just rename .done extensions (must pass -U)
     -U is used to move .done to unnamed extensions\n";
     -P print differences
     -u uncompress program for source side
     -D database name - comma separated list
    

     -F filespec - match patern for files.  Otherwise the following will be used
       .$db.logdump.\d+.\d+   if database name is passed
       logdump.\d+.\d+      if no database is passed
    

    where \d+ is the perl string for a set of digits

    SYNOPSIS

    Works on both unix and windows. Will sync two directories. By default it only copies from the source to the target (no removes on target) but with full dumps or if -p is called it will purge the target too.

    The only caveat of this program is that the way it copies files is to use a temporary local directory. This means that if you running from machine a to copy SYBA to SYBA_DR, it copies SYBA->a and then a->SYBA_DR. Two copies. Might be slow with large databases. Recommend that you run locally (on either SYBA or SYBA_DR) if your databases are larger than a 100 gigs or so.

    
    

    get_latest_filedate.pl get the latest file date from a directory

    EXAMPLE

    IMDBDATE=`/usr/local/bin/perl /apps/sybmon/dev/ADMIN_SCRIPTS/dbi_backup_scripts/get_latest_filedate.pl -C"ssh sybase@sybhost ls /export/home/sybase-dump" -Dmydb` echo loading db for date=$IMDBDATE echo ---- DB=mydb DATE=$IMDBDATE >> $FILE echo set nocount on >> $FILE echo 'select getdate()' >> $FILE echo go >> $FILE echo load database mydb >> $FILE echo from \"compress::/export/home/sybase-dump/sybhost.mydb.dbdump.$IMDBDATE.S1\" >> $FILE echo stripe on \"compress::/export/home/sybase-dump/sybhost.mydb.dbdump.$IMDBDATE.S2\" >> $FILE echo stripe on \"compress::/export/home/sybase-dump/sybhost.mydb.dbdump.$IMDBDATE.S3\" >> $FILE echo stripe on \"compress::/export/home/sybase-dump/sybhost.mydb.dbdump.$IMDBDATE.S4\" >> $FILE echo go >> $FILE echo online database mydb >> $FILE echo go >> $FILE echo 'select getdate()' >> $FILE echo go >> $FILE

    
    

    kill_users_db.pl utility to load databases

    USAGE

     kill_users_db.pl -USA_USER -SSERVER -PSA_PASS -Ddb -d
    

    DESCRIPTION

    Kills all users in a database.

    
    

    load_all_tranlogs.pl Load Transaction Logs In Mass

    SYNOPSIS

    This is the primary driver to load more than one transaction log. It applies file filters and runs load_database.pl on each file that survives the filtering in order. It renames the files to append .done to the ones applied.

    USAGE

    load_all_tranlogs.pl -JJOBNAME

    or

    load_all_tranlogs.pl

       -USER=SA_USER      -SERVER=SERVER      -PASSWORD=SA_PASS
       -DATABASE=db       -INDIR=file_root    -TYPE=Type
       -DESTDIR=dest_dir  -JOB=job            -HOSTNAME --SKIPPRIOR
       -UNIXLOGIN         -DELETE             -UNIXPASSWORD -STRIPES
       -SOURCESERVER - (for reporting only - this is the source server)
       -FULL   - full database loads (only on microsoft for now)
       -DEBUG debug mode  --FILEPATERN=pat --COMPRESSED (sybase compression)
       -SKIPDONE skip files with .done extension
       -MICROSOFT microsoft sql svr format (see below)
       -RENAME rename files to .done extension
       -INDIR identifies a directory / files as seen from this program
       -DESTDIR identifies the same directory/files as seen by remote server
          (or pass HOSTNAME)
    

       Will Load, in order, tranlogs that have the following name format
          {SERVER}.{DATABASE}.dbdump.yyyymmdd-hhmmss
       or {DB}_tlog_yyyymmddhhmm.TRN (sql server format - if --MICROSOFTpassed)
    

    DESCRIPTION

    Works on standard SQL server and My standard file naming conventions. If your files are not using standard naming (yyyymmdd.hhmmss) then that needs to be looked at.

    OTHER NOTES

    If you get in a situation where your files are jumbled, just run with --SKIPPRIOR. This option will stop the check that skips logs once an error is found. All logs will be applied not in order! This can be used to unjumble tran logs when there are ordering issues.

    
    

    load_database.pl utility to load databases

    USAGE

    load_database.pl -nnum_stripes -USA_USER -SSERVER

                               -PSA_PASS -Ddb -ifile_root -tdhk -MType
    

       -n Num stripes (.SX appendended to dump name)
       -v Num         use devices: Num dumps per subdirectory (device_#)
       -D DB_NAME     (may NOT contain wildcards)
       -i DIR         (input file root directory)
       -k             dont kill users
       -M servertype  Sybase or ODBC
       -t             (Transaction Log only)
       -d             (debug mode - for dbi debugging use -z)
       -h             (html output)
       -r extension   (rename file extension (dbdump part of name) when done)
       -e errorlog    (optional)
       -l sessionlog  (optional)
       -s silent mode (only print summary)
       -X             dumps are compressed using internal sybase compression
       -Y             force std output prints (wont print to stdout if cron job)
       -O             no online database needed
    

       Load a database from the specified file in with the name
          {SERVER}.{DATABASE}.dbdump.yyyymmdd-hhmmss[.S?]
       where .S? represents optional stripe numbers if the backups are striped.
       The Root of the file (specified by -i) is the full name (excluding the
       .S? stripe name).
    

    If -t and no file exactly matches the file_root, ALL files that start with the root are loaded in order. If full load, and no file matches the -i root, the LATEST file starting with the root will be loaded.

    EXAMPLE

    Load 4 striped compressed dumps into dbname (files have .S1 .. .S4 suffixes)

    load_database.pl -n4 -Usa -SSVRNAME -Ppass -Ddbname -i/export/home/dumps/SRVNAME/dbdumps/SRVNAME.dbname.dbdump.20061231.210404 -X

    DESCRIPTION

    Loads Databases. Can be set for tran log dumps with -t or will, by default, back up full database dumps.

    Loads a database from either a full backup or a transaction log dump. The file root of the command is the path name to the dump file. It should exclude the .S[num] extension for striped dumps. The program attempts to be intelligent about what file to load. If a tran log is implied and no file exactly matches the file_root, the -i option identifies a file root and ALL files that match the root are loaded in order of timestamp (from the file name). If there is no file with exactly the correct root on a full load, the LATEST file matching the root of the command will be loaded. The -r prefix can be used to rename files when they are done loading. For example, you may use -rloaded to rename SRVR.MYDB.dbdump.20001109.022233 to SRVR.MYDB.loaded.20001109.022233. The database name may NOT contain wildcards.

    Standard naming for dump files should be

          {SERVER}.{DATABASE}.dbdump.yyyymmdd-hhmmss
       or
          {SERVER}.{DATABASE}.dbdump.yyyymmdd.hhmmss.S1
         through
          {SERVER}.{DATABASE}.dbdump.yyyymmdd.hhmmss.SN
    

    if the backups are striped.

    
    

    rebuild.pl

    SYNOPSIS

    rebuild.pl - Rebuild Hashbang And use lib statements

    COPYRIGHT

     Copyright (c) 2002-3 By Edward Barlow
     All Rights Reserved
     Explicit right to use can be found at www.edbarlow.com
     This software is released as free software and should be shared and enjoyed
    

    USAGE

    Usage: G:\dev\AHMR52~F\bin\rebuild.pl [--UNSET] --OVERRIDE --LIBDIR=DIRECTORY,DI RECTORY --FILENAME=FILENAME,FILENAME --DIRECTORY=DIRECTORY,DIRECTORY [--DEBUG] - -PERL=PERL

       USE --UNSET to reset your perl to default hashbangs
    

    DESCRIPTION

    Resets the perl hashbang lines at the top of the file. If the first line starts with a hashbang #! then the next part is a command to reformat_file. This can either be of the format

        #!/usr/local/bin/perl
    

    or if you pass --UNSET

       : # use perl
           eval 'exec perl -S $0 "$@"'
           if $running_under_some_shell;
    

    These lines are reformated correctly based on the perl in your path or based on the perl you pass

    USAGE

       rebuild.pl file file file...
    

    or

       rebuild.pl *
    

    or

       rebuild.pl -D.
    

    or

       rebuild.pl -u
    

          to undo changes to generic format
    

     --OVERRIDE will work on restricted file types
    

    NOTES

    Reset perl on first or first 3 lines as necessary. If the first line starts with a hashbang #! then that word is reformatted as necessary. If the first 3 lines looks like:

       : # use perl
       eval 'exec perl -S $0 "$@"'
       if $running_under_some_shell;
    

    skips files named configure.pl or rebuild.pl. Those should always be full path name to the perl executable using the above syntax.

    
    

    rebuild_index.pl Rebuild Indexes

    DESCRIPTION

    This procedure builds missing indexes in a server. It relies on a file extract from another server to understand what indexes to rebuild. The magic of this proc is in its error handler, which understands the errors that can be generated by index generation and in its duplicate row processing. You can rely on the fact that when this program is done, your indexes will be correct. Use it to maintain indexes between identical servers.

    Note: this is not a script to recreate existing indexes. This is a script that corrects indexes in a server that might be incorrect by making them like the indexes in a file (which can be extracted from another server).

    The extract file is is just index create scripts, 1 per line. For syntactical clarity, it is recommended that the extract file be based on the extended stored procedure sp__revindex, which is found in Ed Barlows extended procedure library. The program can be used to extract output into a file (using -o) which can later be run into another server (using -i). Duplicate rows are cleaned up if the -c option is used

    USAGE

    Usage: rebuild_index.pl -UUSER -PPASS -SSERVER -DDB [-oOUTFILE|-iINFILE] -c

    Index Comparator. Extracts indexes for a DB and then allows you to compare the saved indexes to the indexes on your servers.

     -o : if outfile specified then creates a file with the indexes from the
          server.  This file can be used as an infile for further comparisons.
     -c : correct duplicate keys in indexes.  Deletes duplicate data.  One random
          row in unique indexes will be kept.
     -r : report only - this will report missing indexes
          as well as duplicate data but will not rebuild any indexes.
    

    note that this program requires sp__revindex (extended stored proc library)

    
    

    reorg_sybase.pl sybase reorg manager

    USAGE

    reorg_sybase.pl -USER=SA_USER -DATABASE=db -SERVER=SERVER -PASSWORD=SA_PASS

    or

    reorg_sybase.pl -RUNONALLSERVERS

    Additional Arguments

       --OUTFILE   : Output File (all output also goes here)
       --ASNEEDEDONLY
       --MAXTIMEMINS
       --CONFIG_FILE
       --HTML
       --DEBUG
       --REORG_COMPACT
       --REORG_REBUILD
       --NOEXEC
    

    ARGUMENTS

    You can run on all your servers with --RUNONALLSERVERS or you can specify a server with --SERVER/--USER/--PASSWORD. If no --DATABASE is specified, it will run on all user databases.

    The command run will be reorg REORG_COMPACT (--REORG_COMPACT) or reorg REORG_REBUILD (--REORG_REBUILD).

    If you specify --ASNEEDEDONLY, it will only reorg tables that need to be reorged. The default behavior is to reorg all tables. If not, it will get the info from systabstats looking for tables with forwarded/deleted rows.

    You can also pass in --NOEXEC to print what will happen but not run the commands

    Output can be directed to --OUTFILE and can be saved in HTML format if --HTML is passed.

    The MAXTIMEMINS argument will specify the max time in minutes that the entire operation can run (all servers all databases). You can specify --CONFIG_FILE to store the order and time of this run for the future...

    
    

    set_dboption.pl utility to set database options in multiple databases

    USAGE

    set_dboption.pl -USA_USER -SSERVER -PSA_PASS -DDB_LIST -Ooption -Vvalue -k

      multi database option setting with checkpoint and kills if those r needed
      (ie... issue kills if set single user and user in the db)
    

       -O OptionName : option to set
       -D DB_LIST: may include wildcards or pipe separated list of databases
       -d debug mode
       -h html output
       -l logfile
       -e errorfile
       -k do kills (otherwise just prints it)
       -V value true or false
    

    DESCRIPTION

    Sets Db Options As Appropriate. DB_LIST can contain wildcards. Kills users in database if read only, single user or dbo use only. Runs chekcpoint to close out the functionality.

    
    

    show_configvars.pl print out the job configuration variables as they would be used

    USAGE

    show_configvars.pl

    SYNOPSIS

    This is a diagnostic utility

    show_configvars.pl

    CURRENTLY DEFINED PLANS ARE :

    SYB1 PLATINUM MMMDBDB_COPY SYBDEV1 SYBDEV2 SYBDEV2_UPDSTATS MLPSYBDBCC CALYDB SYB2_UPDSTATS IMAGREP2SYB ADS060 ADS088 SYB1_TO_SYB2

    or

    show_configvars.pl -JJOB

    show variables for the plan

     debug: CONFIG{AUDIT_PURGE_DAYS}=30
     debug: CONFIG{BASE_BACKUP_DIR}=/apps/sybmon/dev/data/BACKUP_LOGS
     debug: CONFIG{BCP_COMMAND}=
     debug: CONFIG{BCP_TABLES}=
     debug: CONFIG{CODE_LOCATION_ALT1}=
     debug: CONFIG{CODE_LOCATION_ALT2}=
     debug: CONFIG{COMPRESS}=/usr/local/bin/gzip
     debug: CONFIG{SYBASE_COMPRESSION_LEVEL}=0
     debug: CONFIG{COMPRESS_LATEST}=n
     debug: CONFIG{DBCC_IGNORE_DB}=
     debug: CONFIG{DO_AUDIT}=y
     debug: CONFIG{DO_BCP}=n
     debug: CONFIG{DO_CLEARLOGSBEFOREDUMP}=y
     debug: CONFIG{DO_EXTERNAL_COMPRESS}=y
     debug: CONFIG{DO_DBCC}=y
     debug: CONFIG{DO_DUMP}=y
     debug: CONFIG{DO_INDEXES}=n
     debug: CONFIG{DO_LOAD}=n
     debug: CONFIG{DO_ONLINEDB}=n
     debug: CONFIG{DO_PURGE}=y
     debug: CONFIG{DO_REORG}=y
     debug: CONFIG{DO_UPDSTATS}=y
     debug: CONFIG{SERVER_NAME}=DACSSERVER
     debug: CONFIG{DUMP_DIR_BY_BKSVR}=/export/home/sybase-dump/DACSSERVER/dbdumps
     debug: CONFIG{DUMP_FILES_PER_SUBDIR}=
     debug: CONFIG{DATABASE_IGNORE_LIST}=model|tempdb|sybsyntax
     debug: CONFIG{IGNORE_SERVER}=
     debug: CONFIG{IS_REMOTE}=y
     debug: CONFIG{JOBNAME}=DACSSERVER
     debug: CONFIG{CLIENT_PATH_TO_DIRECTORY}=//samba/sybmon/dev/data/BACKUP_LOGS
     debug: CONFIG{CLIENT_PATH_TO_DUMP_DIR}=
     debug: CONFIG{CLIENT_PATH_TO_LOG_DIR}=
     debug: CONFIG{MAIL_HOST}=mail1.mlp.com
     debug: CONFIG{MAIL_TO}=ebarlow@mlp.com,ryi@mlp.com
     debug: CONFIG{NT_CODE_LOCATION}=//samba/sybmon/dev
     debug: CONFIG{NT_PERL_LOCATION}=C:/perl/bin/perl.exe
     debug: CONFIG{NUMBER_OF_STRIPES}=4
     debug: CONFIG{NUM_BACKUPS_TO_KEEP}=2
     debug: CONFIG{NUM_DAYS_TO_KEEP}=4
     debug: CONFIG{SERVER_HOST_LOGIN}=sybase
     debug: CONFIG{SERVER_DIRECTORY}=/export/home/sybase-dump
     debug: CONFIG{SERVER_DUMP_DIRECTORY}=
     debug: CONFIG{SERVER_HOSTNAME}=dacs2
     debug: CONFIG{SERVER_LOG_DIRECTORY}=
     debug: CONFIG{SERVER_HOST_PASSWORD}=
     debug: CONFIG{REORG_ARGS}=--maxtimemins=60 --reorg_compact
     debug: CONFIG{SRVR_DIR_BY_BKSVR}=/export/home/sybase-dump/DACSSERVER
     debug: CONFIG{SUCCESS_MAIL_TO}=ebarlow@mlp.com,ryi@mlp.com
     debug: CONFIG{SYBASE}=/apps/sybase
     debug: CONFIG{TRAN_DIR_BY_BKSVR}=/export/home/sybase-dump/DACSSERVER/logdumps
     debug: CONFIG{UNCOMPRESS}=/usr/bin/gzip -d
     debug: CONFIG{UNIX_CODE_LOCATION}=/apps/sybmon/dev
     debug: CONFIG{UNIX_PERL_LOCATION}=/apps/sybmon/perl/bin/perl
     debug: CONFIG{UPD_STATS_FLAGS}=-i
     debug: CONFIG{XFER_BY_FTP}=
     debug: CONFIG{XFER_FROM_DB}=
     debug: CONFIG{XFER_SCRATCH_DIR}=
     debug: CONFIG{XFER_TO_DB}=
     debug: CONFIG{XFER_TO_DIR}=
     debug: CONFIG{XFER_TO_DIR_BY_TARGET}=
     debug: CONFIG{XFER_TO_HOST}=
     debug: CONFIG{XFER_TO_SERVER}=
    

    
    

    Stop_Sybase.pl

    DESCRIPTION

    Stops your sybase server cleanly

    USAGE

    Stop_Sybase.pl $opt_S $SAPASSWORD [$SYBASEDIR]

    note server must be running on the unix system in question and be using an errorlog named errorlog_$opt_S in $SYBASE/install and a runserver file of RUN_$opt_S in the same directory unless it is called SYBASE (will use defaults of RUN_opt_S and errorlog

    COPYRIGHT

    Copyright (c) 1996 by Edward Barlow All Rights Reserved

    You are welcome to use and redistribute this software, without charge, provided you make no money from it. To the best of my knowledge this program works as specified, but I make no waranty about it (ie. you get what you pay for). I recommend you browse the scripts to see what i have done prior to using them in any production environments.

    
    

    update_stats.pl update statistics on a database

    AUTHOR

    By Ed Barlow

    DESCRIPTION

    update statistics on all tables in db. Also recompiles. If Db Not Passed, will work on all user databases in the server. Pass -s if you want no output.

    USAGE

    USAGE: update_stats.pl -UUSER -SSERVER -PPASS [-DDB]

       -p samplepct   adds 'with sampling = samplepct' (sybase 12.5.0.3+)
       -D DB_LIST     (pipe separated list with wildcards of databases)
       -E TABLES      (comma separated list of tables to EXCLUDE)
       -I TABLES      (comma separated list of tables to INCLUDE)
       -s             (silent mode)
       -k             keep old stats (ie no delete stats)
       -d             (debug mode)
       -vVAL                                number of steps/values to use (suggest 60 - dflt 20)
       -h             (html output)
       -i             update index stats
       -a                                   update all stats
       -n                           noexec
       -e errorlog    (optional)
       -l sessionlog  (optional)
    
    

    ARGUMENTS

    You can control pretty much everything here. Will do a delete stats, update stats, and then an sp_recompile on the object in question.

    
    

    CheckServer.pl utility to check databases

    USAGE

       CheckServer.pl -BATCH_ID=id -USER=SA_USER -SERVER=SERVER -PASSWORD=SA_PASS -TIME=secs [-debug] -OUTFILE=Outfile --DOALL=sybase|sqlsvr --MAILTO=a,b,c
    

    DOALL=run on all servers in the repository TIME is the time threshold for blocks (ie they must be older than -TIME arg) before a CRITICAL event is generated

    DESCRIPTION

     Checks for blocks > time seconds old
     Checks for status in in ('infected','bad status', 'log suspend','stopped')
     Checks for circular deadlocks
    

    
    

    CheckServerDaily.pl daily check servers

    USAGE

    CheckServerDaily.pl -BATCH_ID=id

          [-debug]
          -OUTFILE=Outfile
          -ERRFILE=Outputs error messages appropriate for the configfile
    

    Server Selection via:

       -USER=SA_USER -SERVER=SERVER -PASSWORD=SA_PASS
       --PRODUCTION|--NOPRODUCTION
       --DOALL=sybase|sqlsvr
    

    CONFIGFILE

    Reads the config file CheckServerDaily.dat. This file takes excludes in the format generated by --ERRFILE which happens to be:

    SERVER,ERRORNO,DATABASE,MESSAGE

    DESCRIPTION

    Checks everything i could think of to check on a daily basis.

       * Checks stored procedure library
       * run sp__auditsecurity
       * check wierd select_into/trunc option definition on databases
       * foreach database
       ->  space used (data&log) from sp__qspace
       ->  sp__auditdb
       ->  database options
    

    
    

    CleanupFiles.pl Basic Cleanup

    USAGE

       CleanupFiles.pl
    

    DESCRIPTION

    Removes GEM_BATCHJOB_LOGS files that are over a minute old and have 0 size. chmod 666 the others. Useful because we dont want these 0 sized files out there.

    
    

    MlpEvent.pl save an event

    SYNOPSIS

    a simple utility that saves a event using the monitoring system

    USAGE

    UUsage: MlpEvent.pl --MONITOR_PROGRAM=file --SEVERITY=val --SYSTEM=system --SUBSYSTEM=subsystem --MESSAGE_VALUE=msg --EVENT_ID=id --DEBUG=1 --EVENT_TIME=time --MESSAGE_TEXT=text --DOCUMENT_URL=url --TEST

    Save an EVENT using common Event Management

       --TEST                  use test data
       --MONITOR_PROGRAM= Unique Name Of the Program Name
       --SEVERITY= Severity Of the Event
            * EMERGENCY = system down / critical failure
            * CRITICAL = serious problem.
            * ALERT = non fatal error needing attention
            * ERROR = non fatal error possibly requiring administrator attention
            * WARNING = non fatal warning.
            * INFORMATION = a simple message.  Synonym for OK.
            * DEBUG = messages only of interest to developers
       --SYSTEM=system
       --SUBSYSTEM=subsystem
       --DEBUG=1    - print diagnostics
       --EVENT_TIME=time
       --MESSAGE_TEXT=string tehxt
       --DOCUMENT_URL=attachment
    

    =cu
    

    Heartbeat.pl save a heartbeat

    SYNOPSIS

    A simple front end utility to the GEM Alarm system heartbeats. This program can be used in two modes. Mode 1 is as a regular GEM Heartbeat - which represents a system/state message where you do not care about history. Examples of this is system up/down (you dont care whether it was up yesterday - just that it is up/down now). The other use is as a timer. In this "HEARTBEAT" case, you identify an alarm time - and the heartbeat is saved as an error heartbeat that has been "REVIEWED" until a specific time in the future. In other words, the system stores (for example) a CRITICAL message that "BATCH JOB AAA HAS NOT RUN IN 2 HOURS" and then says that this message is "OK" until 2 hours in the future. In two hours, the alarm routing agent and any alarm viewers will start seeing that message - which is hidden until them as it is marked as reviewed. To prevent your getting a page - run the same program within 2 hours, which will push the "REVIEWED UNTIL TIME" out for another 2 hours - which is the point - alarm unless another heartbeat comes in within a specified time.

    USAGE

    Usage: MlpHeartbeat.pl --MONITOR_PROGRAM=file --STATE=val --SYSTEM=system --SUBSYSTEM=subsystem --DEBUG=1 --MESSAGE_TEXT=text --DOCUMENT_URL=url

       --MONITOR_PROGRAM= Unique Name Of the Program Name
       --STATE= Severity Of the Event
            * EMERGENCY = system down / critical failure
            * CRITICAL = serious problem.
            * ALERT = non fatal error needing attention
            * ERROR = non fatal error possibly requiring administrator attention
            * WARNING = non fatal warning.
            * OK = a simple message.  Synonym for INFORMATION.
            * DEBUG = messages only of interest to developers
       --SYSTEM=system
       --HEARTBEAT=minutes ( the HEARTBEAT Special Case )
       --SUBSYSTEM=subsystem
       --DEBUG=1    - print diagnostics
       --MESSAGE_TEXT=string tehxt
       --BATCHJOB=1
       --DOCUMENT_URL=attachment
    

    EXAMPLE1

     # In this example, we save a normal heartbeat
     #  - these messages appear in the alarm system immediately
     Heartbeat.pl --SYSTEM=DEF --MONITOR_PROGRAM=XYZ   --STATE=OK --MESSAGE_TEXT="SYSTEM DEF PASSED THE XYZ TEST"
     Heartbeat.pl --SYSTEM=DEF --MONITOR_PROGRAM=XYZ   --STATE=ERROR --MESSAGE_TEXT="SYSTEM DEF FAILED THE XYZ TEST"
    

     # In this example, we save a HEARTBEAT - an error that is reviewed until <currenttime>+$HEARTBEAT
     #  - these messages appear in the alarm system after 60 minutes (unless time is extended by rerunning
     #  - for the same key value (monitor_program,system,subsystem)
     Heartbeat.pl --SYSTEM=ABC   --HEARTBEAT=60
       translates into
          --MONITOR_PROGRAM=HEARTBEAT
          --SYSTEN=ABC
          --STATE=ERROR
          --SUBSYSTEM=""
          --MESSAGE_TEXT="A HEARTBEAT has not occurred for at least $HEARTBEAT Minutes"
          --heartbeat=60
    

     # This is similar to the above but with a custom message
     Heartbeat.pl --SYSTEM=ABC   --HEARTBEAT=10  --MESSAGE_TEXT="ERROR ABC HAS NOT RUN IN 10 MINUTES - SEEK HELP"
    

    Notes

    If you want to be alarmed for a regular heartbeat - best choose a system that is production
    

    MlpAlarmCleanupDB.pl perform alarm system maintenance

    DESCRIPTION

    This batch should be used to perform necessary maintenance on your alarm system. You can pass in --DAYS or --HBDAYS and --EVDAYS. In addition to cleaning up the database, it performs necessary maintenance.

    This jobs should be scheduled daily.

    USAGE

    Usage: MlpAlarmCleanupDB.pl --DAYS=xxx --HBDAYS=days --EVDAYS=days

    
    

    MlpAlarmRoutingAgent.pl monitoring system alarm router

    DESCRIPTION

    This is the alarm router. It sends alarms to operators based on routing data.

    USAGE

    Usage: $0 --SLEEPTIME=secs --MAXTIME=minutes --FROM=from --DEBUG

    If sleeptime is defined, will check to see if allready running by looking for a lock file.

       --ROUTING displays the routing table
       --SLEEPTIME is the number of seconds to sleep between runs
       --FROM      email from
       --MAXTIME  max minutes this program is to stay alive (it quits then)
       --RESTART
       --TEST      work as normal but dont actually alarm
       --DEBUG"         wont alarm
    

    NOTES

    The subject is based on the message. Everything after >> will be ignored in the subject line.

    
    

    RunMimiReport.pl run a report on alarms database

    SYNTAX

    Usage: RunMimiReport.pl --REPORT=rpt --OUTFILE=file --FILENAME=file --MAILTO=xxx --SUBJECT=xxx --DEBUG [--TIME=time]

            if --OUTFILE thats where it ends up
            if No --MAILTO then stdoutput is used.  If --FILENAME it will mail the identified file.
    

    DESCRIPTION

     Runs a report on the alarms database and distribute the output. The general heirarchy of this is
    

       exec mimi.pl --REPORTNAME=$REPORT
          set args based on: select * from ReportArgs where reportname=$REPORT
    

    The Report requires data to be inserted in the ReportArgs table within the monitoring DB. THis has the format

       reportname
       keyname
       value
    

    Where keyname=value are the arguments you might pass into the cgi-bin program mimi.pl. These can include

       admin
       progname
       subsystem
       ProdChkBox
       ReportTitle
       ShowProgram
       filter_time
       radio_screen
       filter_severity
       filter_container
    

    and might, for example, include the following:

       admin   Production
       admin   Ignore List
       admin   Blackout Report
       filter_container   All
       filter_container   FEED
       filter_container   UNIX
       filter_container   Unix
       filter_container   Feeds
       filter_container   WIN32
       filter_container   BACKUP
       filter_container   SYBASE
       filter_container   SQL_SERVER
       filter_container   ExchangeFiles
       filter_container   IMAGINE SYSTEM
       filter_severity   All
       filter_severity   Errors
       filter_severity   Warnings
       filter_time   1 Day
       filter_time   5 Days
       filter_time   2 Hours
       filter_time   Since 4PM
       progname   PcEventlog
       progname   UnixLogmon
       progname   PcDiskspace
       progname   SybaseErrorlog
       progname   Sybase_ASE_Log
       progname   Sybase_Backup_Log
       progname   Sybase_Replication_Log
       radio_screen   Agents
       radio_screen   Events
       radio_screen   Backups
       subsystem   Security
    

    
    

    SybRepMonitor.pl Complete REplication Monitoring SYstem

    DESCRIPTION

    The sybase replication monitor is a stand alone system that permits replication monitoring. Data is inserted into your replication sources (one row per minute) and is replicated to your replication destinations. That latency is timed and stored for later usage.

    SYNTAX

    Usage: SybRepMonitor.pl --ACTION=INSTALL|UNINSTALL|MONITOR|REPORT|CHECK

       [--SERVER=xxx] [--DEBUG] [--OUTFILE=file]
    

       if --ACTION=INSTALL, it will install the monitoring
          --REBUILD - rebuild tables (loses data). By default table is preserved.
                      this option is primarily used if there is a table ddl change.
                      if the table does *not* exist, it will always be built
    

       if --ACTION=CHECK will check latency vs --ALARM_MINS
          - mail to --ALARM_MAILTO as needed
    

       if --ACTION=REPORT will create a report
          - will print data for --REPORT_HOURS hours
          - will only show rows > --REPORT_THRESH seconds
          - will aggregate based on --BUCKETSIZE
          - will print HTML output if --HTML is passed
    

       if --ACTION=MONITOR will insert rows into all source databases
          - will insert --ITERATIONS rows
          - sleeps --FREQUENCY secs between each insert
    

    DETAILS

    A table called gem_rep_status is created in each of your replication db's - both the source and target. A procedure is created in sybsystemprocs named sp__repstatus_$dbname. This procedure will tell you the replication status of the dbname in question. When you run with --ACTION=MONITOR it will insert rows into primary db's. The --ACTION=REPORT function creates a report on the latencies of the rows you inserted with --ACTION=MONITOR.

    
    

    check_sybase_num_users.pl Count Number Of Users on sybase or sql server and alarm if error

    USAGE

       check_num_users.pl --USER=USER --SERVER=SERVER --PASSWORD=PASS  --MAXRUNNING=num
    

       or
    

       check_num_users.pl --USER=USER --SERVER=SERVER --PASSWORD=PASS --BATCH_ID=ID -TYPE=Sybase|ODBC [--DEBUG] --MAXRUNNING=num --MAXUSERS=num --OUTFILE=OUTPUTFILE --MAILTO=a,b --DOALL=sybase|sqlsvr
    

       if --MAXUSERS not passed - defaults to 80% of the maximum value
    

    DESCRIPTION

    Checks for number of user connections > max_num_users

    
    

    CheckSybaseReplication.pl utility to check databases

    USAGE

       check_sybase_repserver.pl -UUSER -SSERVER -PPASS -ttime
    

    DESCRIPTION

     Checks for blocks > time seconds old
     Checks for status in in ('infected','bad status', 'log suspend','stopped')
     Checks for circular deadlocks
    

    
    

    cisco_logmon.pl Read Cisco Logs And Alarm Appropriately

    DESCRIPTION

    This perl module parses and reads the cisco logs.

    FILTER RULES

    There are a number of paterns that are excluded. The main interest of this program is whether lines are up or down. Consequently, we search on the following mesages:

    • UPDOWN:

    • STATECHANGE:

    • ALARM: or INFO:

    • RANDOM Less Used Stuff

    
    

    dfstats.pl utility to check disk space

    SYNOPSIS

    Output to syslog via logger on a configurable channel. Program is designed to w ork with any perl anywhere. It can run in a loop if you pass --SLEEPTIME and wi ll single thread itself so that multiple instances will not run on a single syst em in loop mode.

    USAGE

            dfstats.pl --DEBUG --EXCEPTFILE=file --SLEEPTIME=secs --WARNTHRESH=pct --ERRTHRESH=pct -NOLOGGER --SHOWSTDOUT
    

    ARGUMENTS

            --DEBUG       diagnostic mode
       --EXCEPTFILE=file    exception file
       --SLEEPTIME=secs    run in loop - sleeping --SLEEPTIME seconds per loop
       --WARNTHRESH=pct    warning percentage threshold
       --ERRTHRESH=pct    error percentage threshold
       --NOLOGGER       dont output to syslogd
       --SHOWSTDOUT       show errors and warnings on standard output
    

    DESCRIPTION

    Works on Linux or Solaris (althought this can be easily expanded. When run with no arguments it will produce no output. Run with --DEBUG flag to see whats is going on. This is a feature. You may also run with --SHOWSTDOUT to see output. Put stuff in EXCEPTFILE to have it ignored.

    OUTPUT

    In monitor host will produce logger lines as

     Jul 21 13:18:32 kickstart1 dfstats.pl: FS=/ Pct=30 (Sz=985M Usd=273M Avl=662M)
     Jul 21 13:18:33 kickstart1 dfstats.pl: FS=/boot Pct=10 (Sz=193M Usd=17M Avl=167
    

    M)

     Jul 21 13:18:33 kickstart1 dfstats.pl: FS=/export/home Pct=81 (Sz=6.5G Usd=4.9G
     Avl=1.2G)
     Jul 21 13:18:33 kickstart1 dfstats.pl: FS=/usr Pct=79 (Sz=5.3G Usd=4.0G Avl=1.1
    

    G)

     Jul 21 13:18:33 kickstart1 dfstats.pl: FS=/var Pct=28 (Sz=2.0G Usd=508M Avl=1.4
    

    G)

    or

     Jul 21 13:20:03 kdb2 dfstats.pl: [ID 702911 local1.info] FS=/var Pct=26 (Sz=963543 Usd=229538 Avl=676193)
     Jul 21 13:20:03 kdb2 dfstats.pl: [ID 702911 local1.info] FS=/export/home Pct=8 ( Sz=30113974 Usd=2326863 Avl=27485972)
    

    
    

    cisco_logmon.pl Read Cisco Logs And Alarm Appropriately

    DESCRIPTION

    This perl module parses and reads the cisco logs.

    FILTER RULES

    There are a number of paterns that are excluded. The main interest of this program is whether lines are up or down. Consequently, we search on the following mesages:

    • UPDOWN:

    • STATECHANGE:

    • ALARM: or INFO:

    • RANDOM Less Used Stuff

    
    

    port_monitor.pl utility to ping ports

    USAGE

       port_monitor.pl -USA_USER -SSERVER -PSA_PASS -ttime
    

    SYNTAX

    Usage: port_monitor.pl [--LOGALARM] [--DEBUG] --SLEEPTIME=secs

            --CFGFILE=FILENAME
            --SYSTEM=SYS    [ returns all responses just for this system ]
            -DEBUG
            -LOGALARM
            -ITERATIONS=num (loop num times slepeing SLEEPTIME seconds betwen loops)
    

            -GENERATE       - Generate/Update config file
            -INTERFACE_FILE=FILENAME
            -SLEEPTIME=SECONDS
    

    Only loops if SLEEPTIME defined > 0 The system sends an event when state changes

    OTHER OPTIONS

       --GENERATE
    

    Generate a data file if one does not exist using your sybase interface file. The optional --INTEFACE_FILE parameter can help here

       --SYSTEM
    

    Generates a listing of all ports that respond between 1 and 2000. This has some usefulness.

    DESCRIPTION

     Checks ports as needed
    

     By default reads configuration file port_monitor.dat in the conf directory
    

    
    

    tracert.pl trace route utility

    USAGE

       Usage: tracert.pl --MAXHOPS=hops --MAXTIME=secs --SYSTEM=server --BATCH_ID=key --DEBUG
    

    DESCRIPTION

    Loops through your servers and prints the number of hops and the total time it takes to get to them. Handles unix and nt traceroute output.

    Will alarm if there are problems.

    It takes 5 seconds between attempts. The trace aborts if it does not get a response within 20 seconds.

    
    

    unix_monitor.pl Read Unix Logs And Alarm Appropriately

    DESCRIPTION

    This perl module parses and reads the Unix logs. It needs some thought to set up - it is recommended that you use a common syslogd host and then just schedule this job once on the combined file.

    USAGE

    Usage: unix_monitor.pl --NOSAVEPATFILE --TODAYONLY --HTML --OUTFILE=file --EXCLUDEFILE=file --DEBUG --NOLOG [--PATFILE=file] --INFILE=file

     --HTML : html output
     --NOSAVEPATFILE : dont save the patern file when u are done
     --OUTFILE : do you want to save output to a file.  I would put this file into
       data/html_output so the console will automatically include it
     --EXCLUDEFILE : paterns to exclude.  A few .excl files are saved in the bin directory
     --NOLOG : dont save messages through the standard error log
     --TODAYONLY : a misnomer - it filters for today and yesterday
    

    SAMPLE SCHEDULE

    You should be sure to include the appropriate options when you schedule this job.

    5,15,25,35,45,55 * * * * /usr/local/bin/perl-5.8.1 /apps/sybmon/dev/ADMIN_SCRIPTS/monitoring/unix_monitor.pl --INFILE=/var/log/messages --TODAYONLY --EXCLUDEFILE=/apps/sybmon/dev/ADMIN_SCRIPTS/bin/solaris.excl --MAXLINES=100000000 --HTML --OUTFILE=/apps/sybmon/dev/data/html_output/unix_monitor.html >/apps/sybmon/dev/data/GEM_BATCHJOB_LOGS/unix_monitor.log 2>/apps/sybmon/dev/data/GEM_BATCHJOB_LOGS/unix_monitor.err

    
    

    The GEM Console Guide

    The GEM CONSOLE is a set web pages that allow you to see a consolidated set of system reports. These reports range from fairly static configuration reports to very dynamic reports (system monitoring, batch job state, performance reports). The console can be viewed with a standard web browser. A web server is not needed.

    The GEM Console is assembled and managed by the GEM batch scripts. This is a batch reporting system - designed intentionally that way to minimize production system impact. This means that some of the console reports will be assembled in the middle of the night and some will be reassembled every few minutes using the data collected by the monitoring system. The console is designed with several goals in mind.

    1. Completely document your environment
    2. Provide configuration auditing & security reports
    3. Historical reporting
    4. Assist Disaster Recovery
    5. System sanity check for misconfigurations, space problems etc...
    6. Advanced Reporting on the state of your backups
    7. Provide extensive morning review reports
    8. Monitor GEM and specifically monitor GEM Batch Jobs
    9. Provide trivial customization to YOUR specific needs.

    The GEM Console also had a design goal. It was designed over time, as a freely available open architecture system. It was specifically designed to be easily extendible into my consulting clients site specific needs. The customization features of the console are simple, elegant, and do pretty much whatever you might need to monitor or report on specific things with your systems. It will work very well with your existing monitoring, auditing, and reporting.

    This document explains the Console with the aim of permiting you to install, use, and maintain it.

    Documented separately are the GEM batch jobs. These batch jobs are integral to the workings of the Console. To synopsize, the configuration utility creates a set of batch jobs. Jobs for your unix systems exist in the unix_batch_scripts directory and win32 jobs exist in the win32_batch_scripts directory. You are responsible for scheduling these jobs (although we do provide help on this). If you have a hybrid win32/unix environment, each batch job should be scheduled in one and only one spot. The batch jobs perform system monitoring and collect information into the data subdirectory of the GEM installation. There are a few batch jobs that can be considered "Console" batch jobs. These jobs either put together and manage the console or create specific console reports. One final job takes all the "pre-prepared" console parts and assembles them into a final set of web pages. The GEM configuration utility will build an initial set of Console pages, but a working console requires you to schedule your batch jobs. While installing, you will end up with a minimal console, but that console will be fleshed out with more information as the batch jobs you sheduled run. The console is built in the data/CONSOLE_REPORTS directory of the GEM root. The console can optionally be published to another system or directory. This feature allows you and your co-administrators to view the console over the company intranet. Be aware - the console publishes detailed configuration reports about your systems that you may not want revealed for security reasons. It is normal to publish the console on a shared drive (not a web server) that can be controlled with standard file access permissions. The batch jobs also produce output. Job standard output and standard error is stored in data/GEM_BATCHJOB_LOGS and and data/batchjob_errors. As mentioned earlier, data/CONSOLE_REPORTS is where the console is assembled. The data/BACKUP_LOGS directory is read for output of your backup scripts. The data/html_output directory is a place you can put your own reports for inclusion in the Console.

    This package needs to be installed through the master configuration utility of GEM. It is not supported as a standalone utility.

    
    

    CONSOLE ARCHITECTURE

    The following table summarizes the locations mentioned in the introduction.

    DIRECTORY	USAGE
    ADMIN_SCRIPTS/console	Perl Code Location
    data/CONSOLE_REPORTS	Final Web Browsable Output
    data/GEM_BATCHJOB_LOGS	Source Of Batch Job Log Files
    data/batchjob_errors	Source Of Batch Job Error Logs
    data/html_output	Where you put user assembled log files/reports
    data/system_information_data	Spot GEM places collected server logs and configuration data
    unix_batch_scripts/batch	The Unix Batch Scripts That Create The Console
    win32_batch_scripts/batch	The Win32 Batch Scripts That Create The Console

    The above table reveals a lot about the order of activities. Scheduled batch scritps in the unix_batch_scripts and win32_batch_scripts call perl programs that reside in ADMIN_SCRIPTS/console. These programs perform a variety of tasks but many of them collect data into data/system_information_data. All Batch jobs create log files in data. A few of the batch jobs collect data and create the actual console.

    The console also gives you additional protection by crosschecking your backup files to ensure that your backups were really created. Backups are perhaps the most critical database administration task. The critical nature of the success of your backup strategy has not been considered by the major vendors - it is left as an exercise for the DBA to validate their backups. GEM changes this. It checks that your backups actually happened and that everything looks reasonable. Of course, we can not KNOW what you intend in terms of backups. Should the database "test" on your production server be backed up? Do you have log shipping? The console creates reports describing your backup state and uses heuristic rules to figure out if everything is ok. A human must read these reports to ascertain that everything is working and to investigate any failures. This will prevent you from running with inadequate backups or inadequate transaction log loads (in hot backup systems). Surprisingly, while you may think that your backups are reliable, stuff will happen... This system protects you.

    The GEM console specific batch jobs are as follows.

    BATCH JOB NAME	DESCRIPTION
    ConsoleArchiver.ksh	Saves a copy of the console. Recommended that this is run once per month to give you a historical configuration audit for your servers.
    ConsoleBuildAndFtp.ksh	Assembles Console And Copies/Ftp's Final Output Directory To Final Destination Calls ADMIN_SCRIPTS/console/console_build_and_ftp.pl
    ConsoleUnixWeekly.ksh ConsoleWin32Weekly.ksh	Performs Task That Should Be Done 1ce Per Week Calls ADMIN_SCRIPTS/console/console_weekly.pl
    ConsoleUnixNightly.ksh ConsoleWin32Nightly.ksh	Performs Task That Should Be Done 1ce Per Night Calls ADMIN_SCRIPTS/console/console_nightly.pl
    ConsoleUnixHourly.ksh ConsoleWin32Hourly.ksh	Performs Task That Should Be Done Hourly Calls ADMIN_SCRIPTS/console/console_hourly.pl

    
    

    CUSTOMIZING REPORTS

    Gem allows you to create your own reports that will be directly incorporated into the console. These reports are defined in the file conf/console.dat. The following is the online help for this file. This file is used to easily incorporate custom business logic into your console.

       # THIS FILE CONTROLS THE REPORTS FOUND IN THE CUSTOM SITE REPORTS
       #
       # NOTE: anything found in the log directory AUTOMATICALLY gets reported
       #  under the log files section of the web page.
       #
       # MAIN PURPOSE: Custom Reports section of the server documenter
       #
       # You can create custom reports here and set up a list of messages to
       # ignore.  Reports can be queries run on one server, commands that
       # return html, and external commands that return txt.
       #
       # Sometimes you will explicity wish to ignore some messages from the
       # reports (like user with no login messages from databases that
       # have been copied from an other server).  Do that here as well.
       #
       # The format for this file is (one row per report)
       #   REPORT_NAME SERVER Database QUERY
       #   REPORT_NAME html <command to run returning html results>
       #   REPORT_NAME cmd <command to run returning ascii/txt results>
       #   REPORT_NAME sql server login pass <sql command>
       #   REPORT_NAME file host <filename>
       #    IGNORE server database <message ids (space separated)>
       #
       # Queries can be run on individual servers with the sql and null keywords.
       #
       # Use the sql keyword to run on non ASE servers (like HISTORICAL and BACKUP
       # servers) that are not in the password file for whatever reason.
       #
       # For file reports - host may be the word null in which case the file is local.
       #
       # External commands can be called using the cmd (ascii output) and html
       # (formatted html output) keywords.
    

       # The IGNORE line ignores particular messages on the Server Analysis For
       # Problems report by server/db.  The message ids should be numbers (eg. 31008)
       # from that report.
       #
       # ALL underscores in REPORT_NAME will be replaced with spaces when printing
       #
       # sql is a special query for HIST/BACKUP/REPORT servers that are not
       # in the password file for whatever reason
       #
       # Database name can be the word 'null' if that is needed (no quotes)
       #
       # ALL underscores in REPORT_NAME will be replaced with spaces on output
       #
       # STORED PROCEDURES
       # ------
       # When you run a stored procedure that returns some stuff it is embedded
       # into the html report as is
       #
       # example to run 2 queries on BPSAPROD database bpsadata:
       #
       # Bpsa_Status BPSAPROD bpsadata exec bpsadata..show_bpsa_load_status
       # Bpsa_Transaction_Row_Count BPSAPROD bpsadata exec bpsadata..show_trans_row_counts
       #
       # RUN SYSTEM COMMAND (PERL MUST BE IN PATH or PREPENDED)
       # ------
       # If you are running a perl command and you are on NT, you will need to
       # have C:/program_files/perl (or whatever your perl is) prepended
       # to your program path so it can interpret it ok.  ie.
       #    Rsh_Program_Type cmd C:/program_files/perl /optapp2/syb_admin/x.pl -cx
       #
       # this code checks for core dumps on 3 systems
       #
       # examples:
       # Core_Dump_Checker_On_Prod html /optapp2/syb_admin/development/ADMIN_SCRIPTS/console/core_dump_checker.pl  2>/dev/null
       # Core_Dump_Checker_On_Sybprod html rsh -l sybase sybprod /opt2/sybdump/MONITORING_SCRIPTS/core_dump_checker.pl -o.  2>/dev/null
       #
       # run file command on rsh and ftp on all hosts in password file so that i can
       # see if they have been appropriately modified for my current site (we use
       # shell wrappers on development/qa so these systems cant go to our production.
       #
       # Rsh_Program_Type cmd /optapp2/syb_admin/development/ADMIN_SCRIPTS/rdist/run_cmd_on_all_hosts.pl -c"file /usr/bin/rsh"
       # Ftp_Program_Type cmd /optapp2/syb_admin/development/ADMIN_SCRIPTS/rdist/run_cmd_on_all_hosts.pl -c"file /usr/bin/ftp"
       #
       # we also have a program that identifies recently modified files and their
       # differences within a set of servers
       #
       # Recently_Changed_Files html /scripts/chksum_compare.pl  -hx  -pappprod
       # Oracle_Backups cmd rsh -l oracle sybint1 'ls -l /optapp1/oracle/tools/*'
    

    
    

    EXTERNALLY ASSEMBLED REPORTS

    GEM automatically includes external reports based on information in conf/custom_reports.dat. This configuration file contains information regarding This file is self documenting. The header for console_reports.dat currently reads.

       #
       # CONSOLE CONFIGURATION FILE
       #
       # This file lists console reports and sources in a format that allows customization and stuff.  Basically, reports
       # are either assembled by the console manager (document_all.pl) or by external programs.  The page header, however,
       # needs to be assembled by document_all.pl.  To make this work,   reports that are assembled by batch scritps are
       # placed in the html_output directory.  They are then read by the console manager, converted from .txt or .html format,
       # incorporated into the javascript navigation bar, and moved into the CONSOLE_REPORTS directory.  This file guides
       # that process.  The format is a standard GEM csv file.
       #
       # FILE,   MENU,   TITLE,   IS_OPTIONAL
       #
       # note.  this means that you should not put commas in your report titles...
       #
       # The following are legal MENU items
       #   BYAMREVIEW
       #   SECURITY
       #   BYGEMLOG
       #   BYCUSTOM
       #   BACKUP
       #   BYHOST
       #   INDEXPAGE
    

    
    

    A LIST OF CONSOLE REPORTS

    Html File	Menu Item	Report Name	Batch Name
    Win32_Hosts_Report.html	BYHOST	Windows Servers Hosts Files	PROGNAME
    MssqlScheduledJobRpt.html	BYAMREVIEW	Sql Server Jobs With Errors	PROGNAME
    Win32_Scheduler.html	BYAMREVIEW	Windows Task Scheduler	PROGNAME
    mssql_jobstatus_rpt.html	BYAMREVIEW	Sql Server Job Scheduler	PROGNAME
    PcDiskspace.txt	BYAMREVIEW	Windows Disk Space Report	PROGNAME
    SybRepMonReport.html	BYAMREVIEW	Sybase Replication Latency	PROGNAME
    GemRpt_SybRepSvrLogRpt.html	BYAMREVIEW	Sybase Replication Log Report	PROGNAME
    GemRpt_SybMonSvrLogRpt.html	BYAMREVIEW	Sybase Mon Server Log Report	PROGNAME
    GemRpt_SybHistSvrLogRpt.html	BYAMREVIEW	Sybase Hist Server Log Report	PROGNAME
    GemRpt_SybErrLogRpt.html	BYAMREVIEW	Sybase Error Log - Filtered	PROGNAME
    GemRpt_SybErrLogAllRpt.html	BYAMREVIEW	Sybase Error Log - UnFiltered	PROGNAME
    GemRpt_SybErrLogAlarm.html	BYAMREVIEW	Sybase Error Log - Recent	PROGNAME
    OptdiagAnalyze.html	BYAMREVIEW	Sybase Fragmentation Analysis	PROGNAME
    GemRpt_SybaseRepServer.html	BYAMREVIEW	GEM Sybase Replication Logs	PROGNAME
    GemRpt_SybaseBackupServer.html	BYAMREVIEW	GEM Sybase Backup Logs	PROGNAME
    GemRpt_SybaseASE.html	BYAMREVIEW	GEM Sybase ASE Error Logs	PROGNAME
    GemRpt_ProductionOvernight.html	BYAMREVIEW	GEM Production Errors	PROGNAME
    GemRpt_ProductionWarnings.html	BYAMREVIEW	GEM Production Warnings	PROGNAME
    GemRpt_PCEventlog.html	BYAMREVIEW	GEM Windows Event Log Report	PROGNAME
    GemRpt_PCDiskspace.html	BYAMREVIEW	GEM Windows Disk Space Report	PROGNAME
    GemRpt_UnixWarnings.html	BYAMREVIEW	GEM Unix Warnings Report	PROGNAME
    GemRpt_UnixErrors.html	BYAMREVIEW	GEM Unix Errors Report	PROGNAME
    GetBackupStateSybase.html	BACKUP	Get Backup Info - Sybase	PROGNAME
    GetBackupStateMssql.txt	BACKUP	Get Backup Info - MS SQL	PROGNAME
    BackupErrorReport.html	BACKUP	Backup CrossCheck - Possible Errors	PROGNAME
    BackupReport.html	BACKUP	Backup CrossCheck - All Systems	PROGNAME
    SybaseBackupFileReport.html	BACKUP	Sybase Backup Configuration	PROGNAME
    GemRpt_BackupReport.html	BACKUP	GEM Backup Manager Summary	PROGNAME
    GemRpt_Backups.html	BACKUP	GEM Backup Manager Job State	PROGNAME
    SybRepMonAgent.html	BYGEMLOG	Sybase Replication Latency Monitor	PROGNAME
    GemRpt_SybRepSvrLogFetch.html	BYGEMLOG	Sybase Replication Log Fetch	PROGNAME
    GemRpt_SybRepSvrConfig.html	BYGEMLOG	Sybase Replication Log Configuration	PROGNAME
    GemRpt_SybRepSvrSaveAlarms.html	BYGEMLOG	Sybase Replication Log Save	PROGNAME
    GemRpt_SybMonSvrLogFetch.html	BYGEMLOG	Sybase Mon Server Log Fetch	PROGNAME
    GemRpt_SybMonSvrConfig.html	BYGEMLOG	Sybase Mon Server Log Configuration	PROGNAME
    GemRpt_SybHistSvrLogFetch.html	BYGEMLOG	Sybase Hist Server Log Fetch	PROGNAME
    GemRpt_SybHistSvrConfig.html	BYGEMLOG	Sybase Hist Server Log Configuration	PROGNAME
    GemRpt_SybErrLogFetch.html	BYGEMLOG	Sybase Error Log Fetch	PROGNAME
    GemRpt_SybErrLogConfig.html	BYGEMLOG	Sybase Error Log Configuration	PROGNAME
    GemRpt_BlackoutReport.html	BYGEMLOG	GEM Ignore / Blackout Report	PROGNAME
    GemRpt_ProductionList.html	BYGEMLOG	GEM Production Servers Report	PROGNAME
    GemRpt_AgentReport.html	BYGEMLOG	GEM Agents Run At Report	PROGNAME
    GemRpt_WinSecurityRpt.html	SECURITY	GEM Windows Security Report	PROGNAME

    
    

    CONSOLE ASSEMBLED REPORTS

    The console will generate reports on its own when it runs. The console can run (by default) in Hourly, Nightly, and Weekly flavors, with SYBASE and SQL Server options.

    Page	Report Name	File Name	Assembled?	Notes On Creation
    MAIN PAGE	Server Names & Ports	interfaces.html	Y	Internally assembled by document_all.pl if DO_SYBASE=1
    MAIN PAGE	Interface File Conflicts	interfaces_conflicts.html	Y1	console/interfaces_file_rpt.pl
    MAIN PAGE	Database Use Report	dbuserpt.html	ALL	Assembled by document_all.pl From Stored XML Data
    MAIN PAGE	Server Overview	server_summary.html	ALL	Assembled by document_all.pl From Stored XML Data
    MAIN PAGE	Server Version Report	server_version.html	ALL	Assembled by document_all.pl From Stored XML Data
    MAIN PAGE	Server Sysconfig Values	server_configures.html	2	BROKEN! console/sysconfigures_rpt.pl
    MAIN PAGE	Space By Database (Sybase)	space_by_db_syb.html	DO_SYBASE	Assembled by document_all.pl From Stored XML Data
    MAIN PAGE	Space By Database (SQL Server)	space_by_db_sql.html	DO_SQLSVR	Assembled by document_all.pl From Stored XML Data
    MAIN PAGE	Server by database (Sybase)	server_by_db_syb.html	All	Assembled by document_all.pl From Stored XML Data
    MAIN PAGE	Server by database (SQL Server)	server_by_db_sql.html	All	Assembled by document_all.pl From Stored XML Data
    MAIN PAGE	Devices By Servers	server_devices.html	2	console/devices_html_rpt.pl
    MAIN PAGE	Space Used By Database (Sybase)	space_used_syb.html	All	Assembled by document_all.pl From Stored XML Data
    MAIN PAGE	Space Used By Database (SQL Server)	space_used_sql.html	All	Assembled by document_all.pl From Stored XML Data
    MAIN PAGE	Windows Version/Restart Report	win32_getversion.html	W5	Assembled by bin/win32_getversion.pl --DAYS=7
    MAIN PAGE	SQL Server Data Paths	sql_path_report.html	S5	bin/mssql_datapath_report.pl --HTML
    MAIN PAGE	Space Used History	space_report.html	9	bin/space_report.pl
    ByServer	[Click ]	by_server.html	Always	Assembled by document_all.pl - This page is a container
    ByServer	Overview	byserver/$srvr.html	ALL	Assembled by document_all.pl From Stored XML Data
    ByServer	Config	$srvr.html	Server Type	config_report.pl
    ByServer	Login Report	$svr_logins.html	6 + Server Type	custom_rpt.pl -Ssvr -h -R6
    ByServer	Users	$svr_users.html	6 + Server Type	custom_rpt.pl -Ssvr -h -R6
    ByServer	Largest 20 Tables	$svr_largetables.html	22	biggest_table.pl -h -S$srvr -U$login -P$password -n20 -s
    ByServer	Debug	$svr_debug.html	4	debug_one_server -h -Ssvr
    ByServer	$srvr_dbschema.txt	$svr_dbschema.txt	(NEVER?)	dbschema.pl -Ssvr -km -Dmaster
    By Host	Bad Symbolic Links	bad_symbolic_links.html	20 and !nt	core_dump_checker.pl
    ByHost	Database Disk Usage	disk_report.html	6	Assembled by document_all.pl From Stored XML Data
    By Host	Host Network Setup	ip_info.txt	U13	run_cmd_on_all_hosts.pl -c"/usr/sbin/ifconfig -a" -C"/sbin/ifconfig -a" -n
    By Host	Host Operating System	sys_info.html	U11	run_cmd_on_all_hosts.pl -c"uname -a" -f1,3,4,5,6,7,8 -o
    By Host	Host Uptime	rup.html	12	/usr/bin/rup -t
    By Host	Hosts File	host_info.html	U13	bin/build_hosts.pl
    By Host	Windows IP Configuration Report	win32_ipconfig.htm	W11	bin/win32_ipconfig_report.pl
    By Host	Windows Hardware Report	win32_hardware_report.htm	W11	bin/win32_system_report.pl -SYSRPT
    By Host	Windows Server Hosts File	Win32_Hosts_Report.html	N.A.	EXTERNAL REPORT (See ABOVE)
    By Host	Windows Software Config Report	win32_software_report.htm	W11	bin/win32_system_report.pl -SWRPT
    By Host	Core Dumps Report	you_have_mail.txt	U15	run_cmd_on_all_hosts.pl -cls -l /var/mail -n
    By Host	Large File Report	large_files.txt	15	core_dump_checker.pl
    By Host	Users With Crontabs	core_dump_checker.html	15	core_dump_checker.pl
    By Host	Users With Mail	you_have_crontab.txt	U15	./rdist/run_cmd_on_all_hosts.pl -c'ls -l /var/spool/cron/crontabs' -n
    Security	Access By Logins - SQL Server	sql_suid_by_server.html	W5	crosstab.pl -C0 -R2 -O3...
    Security	Access By Logins - Sybase	syb_suid_by_server.html	Y3	crosstab.pl -C0 -R2 -O3...
    Security	GEM Windows Security Report	GemRpt_WinSecurityRpt.html	N.A.	EXTERNAL REPORT (See ABOVE)
    Security	Object Creation Time - SQL Server	sql_crdate.html	S5	crdate_report.pl -PRODONLY --HTML --TYPE=sqlsvr
    Security	Object Creation Time - Sybase	syb_crdate.html	Y5	crdate_report.pl -PRODONLY --HTML --TYPE=sybase
    Security	Role Report - Sql Server	sql_role.txt	S5	passwd_rpt.pl -h -Tsqlsvr
    Security	Role Report - Sybase	syb_role.txt	Y5	passwd_rpt.pl -h -Tsybase
    Morning Review	Dbcc Results Summary	check_dbcc_output.html	16	check_dbcc_output.pl -D14
    Morning Review	GEM Production Errors	GemRpt_ProductionOvernight.html	N.A.	EXTERNAL REPORT (See ABOVE)
    Morning Review	GEM Production Warnings	GemRpt_ProductionWarnings.html	N.A.	EXTERNAL REPORT (See ABOVE)
    Morning Review	GEM Sybase ASE Error Logs	GemRpt_SybaseASE.html	N.A.	EXTERNAL REPORT (See ABOVE)
    Morning Review	GEM Sybase Backup Logs	GemRpt_SybaseBackupServer.html	N.A.	EXTERNAL REPORT (See ABOVE)
    Morning Review	GEM Sybase Replication Logs	GemRpt_SybaseRepServer.html	N.A.	EXTERNAL REPORT (See ABOVE)
    Morning Review	GEM Unix Errors Report	GemRpt_UnixErrors.html	N.A.	EXTERNAL REPORT (See ABOVE)
    Morning Review	GEM Unix Warnings Report	GemRpt_UnixWarnings.html	N.A.	EXTERNAL REPORT (See ABOVE)
    Morning Review	Server Analysis for Problems	error.html	4	Internally Generated From Stored Data
    Morning Review	Sql Server Job Scheduler	mssql_jobstatus_rpt.html	N.A.	EXTERNAL REPORT (See ABOVE)
    Morning Review	Sql Server Jobs With Errors	MssqlScheduledJobRpt.html	N.A.	EXTERNAL REPORT (See ABOVE)
    Morning Review	SqlServer Db Configuration Fixer	fix_db_sqlsvr.html	S4	fix_db.pl -x -Tsqlsvr
    Morning Review	Sybase Db Configuration Fixer	fix_db_sybase.html	Y4	fix_db.pl -x -Tsybase
    Morning Review	Sybase Error Log Filtered	GemRpt_SybErrLogRpt.html	N.A.	EXTERNAL REPORT (See ABOVE)
    Morning Review	Sybase Error Log - Recent	GemRpt_SybErrLogAlarm.html	N.A.	EXTERNAL REPORT (See ABOVE)
    Morning Review	Sybase Error Log - UnFiltered	GemRpt_SybErrLogAllRpt.html	N.A.	EXTERNAL REPORT (See ABOVE)
    Morning Review	Sybase Hist Server Log	GemRpt_SybHistSvrLogRpt.html	N.A.	EXTERNAL REPORT (See ABOVE)
    Morning Review	Sybase Mon Server Log Report	GemRpt_SybMonSvrLogRpt.html	N.A.	EXTERNAL REPORT (See ABOVE)
    Morning Review	Sybase Replication Latency	SybRepMonReport.html	N.A.	EXTERNAL REPORT (See ABOVE)
    Morning Review	Sybase Replication Log Report	GemRpt_SybRepSvrLogRpt.html	N.A.	EXTERNAL REPORT (See ABOVE)
    Morning Review	Sybase Space Errors Report	space_errors.html	9	space_errors.pl -H
    Morning Review	Sybase Thresholds Crossed Report	Sybase_Threshold_Report.html	Y9	threshold_manager.pl --DEBUG --HTML --ACTION=REPORT
    Morning Review	Unix Disk Space	unix_df_info.html	U14	unix_space_monitor_via_rsh.pl -A
    Morning Review	Windows Disk Space Report	PcDiskspace.html	N.A.	EXTERNAL REPORT (See ABOVE)
    Morning Review	Windows Task Scheduler	Win32_Scheduler.html	N.A.	EXTERNAL REPORT (See ABOVE)
    Morning Review	GEM Windows Disk Space Report
    Morning Review	GEM Windows Event Log Report
    Morning Review	Sybase Fragmentation Analysis
    GEM Logs	BATCH FILE EXECUTION LOGS	by_logs.html		Internally Assembled
    GEM Logs	GEM Agents Run At Report	GemRpt_AgentReport.html	N.A.	EXTERNAL REPORT (See ABOVE)
    GEM Logs	GEM File Sources Report	gem_file_manager.html	21	gem_file_manger.pl -mh
    GEM Logs	GEM Ignore / Blackout Report	GemRpt_BlackoutReport.html	N.A.	EXTERNAL REPORT (See ABOVE)
    GEM Logs	GEM Production Servers Report	GemRpt_ProductionList.html	N.A.	EXTERNAL REPORT (See ABOVE)
    GEM Logs	Sybase Error Log Configuration	GemRpt_SybErrLogConfig.html	N.A.	EXTERNAL REPORT (See ABOVE)
    GEM Logs	Sybase Error Log Fetch	GemRpt_SybErrLogFetch.html	N.A.	EXTERNAL REPORT (See ABOVE)
    GEM Logs	Sybase Hist Server Log Configuration	GemRpt_SybHistSvrConfig.html	N.A.	EXTERNAL REPORT (See ABOVE)
    GEM Logs	Sybase Hist Server Log Fetch	GemRpt_SybHistSvrLogFetch.html	N.A.	EXTERNAL REPORT (See ABOVE)
    GEM Logs	Sybase Mon Server Log Configuration	GemRpt_SybMonSvrConfig.html
    GEM Logs	Sybase Mon Server Log Fetch	GemRpt_SybMonSvrLogFetch.html	N.A.	EXTERNAL REPORT (See ABOVE)
    GEM Logs	Sybase Replication Latency Monitor	SybRepMonAgent.html	N.A.	EXTERNAL REPORT (See ABOVE)
    GEM Logs	Sybase Replication Log Configuration	GemRpt_SybRepSvrConfig.html	N.A.	EXTERNAL REPORT (See ABOVE)
    GEM Logs	Sybase Replication Log Fetch	GemRpt_SybRepSvrLogFetch.html	N.A.	EXTERNAL REPORT (See ABOVE)
    GEM Logs	Sybase Replication Log Save	GemRpt_SybRepSvrSaveAlarms.html	N.A.	EXTERNAL REPORT (See ABOVE)
    Custom Reports	ALL	$custom_report_name{$count}.*	17	$custom_report_query{$count}
    Backup Logs	Backup Batch Logs: Errors	by_backuperrs.html		Internally Assembled
    Backup Logs	Backup Batch Logs: Logs	by_backuplogs.html		Internally Assembled
    Backup Logs	Backup CrossCheck - All Systems	BackupReport.html	N.A.	EXTERNAL REPORT (See ABOVE)
    Backup Logs	Backup CrossCheck - Possible Errors	BackupErrorReport.html	N.A.	EXTERNAL REPORT (See ABOVE)
    Backup Logs	GEM Backup Manager Job State	GemRpt_Backups.html	N.A.	EXTERNAL REPORT (See ABOVE)
    Backup Logs	GEM Backup Manager Summary	GemRpt_BackupReport.html	N.A.	EXTERNAL REPORT (See ABOVE)
    Backup Logs	Get Backup Info - MS SQL	GetBackupStateMssql.html	N.A.	EXTERNAL REPORT (See ABOVE)
    Backup Logs	Get Backup Info - Sybase	GetBackupStateSybase.html	N.A.	EXTERNAL REPORT (See ABOVE)
    Backup Logs	Sybase Backup Configuration	SybaseBackupFileReport.html	N.A.	EXTERNAL REPORT (See ABOVE)

    
    

    The gem.xml Database

    The system uses a variety of configuration information including the conf/gem.xml library which is managed by the GemData.pm perl module. This file stores, in xml, static configuration data about your environment - data often collected using the internal RosettaStone.pm module. This seems a superior approach to creating flat files for everything or to the approach of discovering all configuration informaton in real time. The main program that loads this database is called discover.pl. The actual file gem.xml is stored in the conf subdirectory of your appliation.

    
    

    Information Collected

    The tool kind of gets everything i could think of. It collects:

    1. Sybase Error Logs
    2. Sybase Backup Server Logs
    3. Sybase Run Files
    4. Sybase Configuration Information From the server
    The specific set of files collected is governed by the toolkit configuration files ( sybase_passwords.dat and unix_passwords.dat ) which identify the sybase directories on your remote systems, the servers you own, etc...

    
    

    Cron

    You will need to schedule your batch jobs. Luckily, the GEM configuration utility will create the crontab file that you will need to schedule. Here are some samples of a crontab file.
    
    10 1 * * 0       /apps/sybmon/dev/unix_batch_scripts/batch/console_weekly.ksh
    37 5 * * 1-6      /apps/sybmon/dev/unix_batch_scripts/batch/console_nightly.ksh
    24,32,48,4 8-16 * * 1-5  /apps/sybmon/dev/unix_batch_scripts/batch/console_hourly.ksh
    41 7-19 * * 1-6   /apps/sybmon/dev/unix_batch_scripts/batch/space_monitor.ksh
    33 * * * *        /apps/sybmon/dev/unix_batch_scripts/batch/get_unix_diskspace.ksh
    

    
    

    PRE BUILT MONITORING SYSTEM REPORTS

    Gem contains numerous pre built reports that are derived from the monitoring system data. These reports are named with the string “GEM” at the beginning of them. For example, a report named $REPORTNAME is created by the GemRpt_${REPORTNAME}.ksh script (or GemRpt_${REPORTNAME}.bat on Win32) and appears in the Console as “GEM $REPORTNAME Report”.

    Under the covers, the batch file calls the perl script RunMimiReport.pl – which in turn calls mimi.pl the CGI version of the monitoring interface. In other words – the prebuilt reports are a static (point in time) snapshot of the dynamic alarm system monitor. The exact syntax under the covers would be :

    RunMimiReport.pl –REPORT=$REPORTNAME --OUTFILE=$GemRpt_INSTALL_DIR/data/html_output/$REPORTNAME.html --BATCHID=$REPORTNAME

    The above command generates a report based on dynamic data stored in the alarming database. RunMimiReport.pl calls mimi.pl with a set of arguments associated to $REPORTNAME that are fetched from the ReportArgs table. You can, therefore create your own reports! RunMimiReport.pl has many associated features (like mailing the output to someone!) allowing you to say – mail an hourly snapshot of just the systems zen_app_1, zen_app_3, and zen_manager to bob, the zen guy every hour! This report can include EVERY thing monitored on any set of systems, servers, processes, or whatever… Send the pre built Unix_Errors and Unix_Warnings reports by mail to your harried unix guy. Send the Win32_Errors report to your frantic windows guy.

    So… Backtracking… Let us assume you want a report… First you would create it in the monitoring page, which allows you to set all kinds of filters - then you note the URL – which (due to CGI conventions) contains the report arguments. The next thing you do is enter them into the ReportArgs table using a syntax like:

       delete ReportArgs where reportname = 'GemRpt_Backups'
       insert ReportArgs values  ('GemRpt_Backups','filter_severity','All')
       insert ReportArgs values   ('GemRpt_Backups','filter_container','All')
       insert ReportArgs values   ('GemRpt_Backups','filter_time','1 Day')
       insert ReportArgs values   ('GemRpt_Backups','ShowProgram','NO')
       insert ReportArgs values   ('GemRpt_Backups','ReportTitle','All Backup    Messages')
       insert ReportArgs values   ('GemRpt_Backups','progname','BACKUPS')
    

    Then create/run your batchfile. I recommend using the –DEBUG argument to RunMimiReport.pl and saving the results into a .html file that you can view in brilliant color and magnificence in your web browser. When I run the above report with –DEBUG I see within the output the fact that the essential query was:

       select {stuff}
       from Heartbeat
       where monitor_program
       in ('backup.pl', 'BackupFull', 'BackupTran', 'LoadAllTranlogs','FixLogship')
       and ( reviewed_time is null or reviewed_until and internal_state!='D'
       order by t.system,subsystem}
    

    To debug a report (say the example GemRpt_Backups report which comes prepackaged with the system) you can try something like:

       /usr/local/bin/perl /apps/sybmon/dev/ADMIN_SCRIPTS/monitoring/RunMimiReport.pl --BATCHID=GemRpt_Backups --REPORT=GemRpt_Backups --OUTFILE=/apps/sybmon/dev/data/html_output/GemRpt_Backups.html --DEBUG --MAILTO=ebarlow@home.com
    

    Which will send you a mail report with the output of the report! The --DEBUG optional arg will give additional diagnostics.

    
    

    STANDARD REPORTS PROVDIED WITH GEM

    The following report is generated by RunMimiReport.pl --GENREPORT and lists the arguments that are passed for each of the pre built reports.

    Reportname	ProdChkBox	ReportTitle	ShowProgram	admin	filter_container	filter_severity	filter_time	progname	radio_screen	subsystem
    Backups	NO	Backup Error Report	NO		BACKUP	Warnings	2 Hours
    ExchangeFiles	YES	Exchange Files	NO		ExchangeFiles	All	2 Hours
    ExchangeFiles2	YES	Exchange Files	NO		ExchangeFiles	Errors	2 Hours
    FeedDf	NO	Market Data Disk Space	NO		FEED	Warnings	5 Days
    Feeds	NO	Feed Error Report	NO		Feeds	Warnings	2 Hours
    GemRpt_AgentReport						All			Agents
    GemRpt_BackupReport						All			Backups
    GemRpt_BlackoutReport		GEM Blackout Report		Blackout Report
    GemRpt_Ignorelist				Ignore List
    GemRpt_ProductionErrors	YES	Production Overnight Report	NO		All	Errors	Since 4PM
    GemRpt_ProductionList				Production
    GemRpt_ProductionWarnings	YES	Production Warnings	NO		All	Warnings	Since 4PM
    GemRpt_Sql_Server_Errors	NO	Sql Server Errors	NO		SQL_SERVER	Errors	2 Hours
    GemRpt_Sybase_ASE		Sybase ASE Error Logs	NO		All	Warnings	1 Day	Sybase_ASE_Log
    GemRpt_Sybase_BackupServer		Sybase Backup Logs	NO		All	Warnings	1 Day	Sybase_Backup_Log
    GemRpt_Sybase_Errorlog		Sybase Error Logs	NO		All	Warnings	1 Day	SybaseErrorlog
    GemRpt_Sybase_Errors	NO	Sybase Errors	NO		SYBASE	Errors	2 Hours
    GemRpt_Sybase_RepServer		Sybase Replication Logs	NO		All	Warnings	1 Day	Sybase_Replication_Log
    GemRpt_Unix_2Hour	NO	GemRpt_Unix_2Hour	NO		UNIX	Warnings	2 Hours
    GemRpt_Unix_Errors	NO	Unix Error Report	NO		All	Errors	1 Day	UnixLogmon	Events
    GemRpt_Unix_Warnings		Unix Warning Report	NO		All	Warnings	1 Day	UnixLogmon	Events
    GemRpt_Win32_Diskspace		Windows Disk Space	NO		All	Warnings	1 Day	PcDiskspace
    GemRpt_Win32_Errors	NO	Win32 Errors	NO		WIN32	Errors	2 Hours
    GemRpt_Win32_Eventlog		Windows Event Logs	NO		All	Warnings	1 Day	PcEventlog
    GemRpt_Win32_SecurityRpt		Windows Security Events	NO		All	Errors	1 Day	PcEventlog	Events	Security
    Imagine	NO	Imagine Error Report	NO		IMAGINE SYSTEM	Warnings	2 Hours
    ProdOvernight	YES	ProdOvernight	NO		All	Errors	Since 4PM
    ProdWarnings	YES	Production Warnings	NO		All	Warnings	2 Hours
    Unix2Hour	NO	Unix2Hour	NO		Unix	Warnings	2 Hours

    
    

    GEM ALARM CONTAINERS

    A container is a set of servers or programs that can be used to filter stuff for particular users. This system is not totally flexible - you can choose a container to include a set of other containers, systems, or monitoring programs - but you can not restrict it further. For most users this is acceptable.

    Users will probably be interested either in a set of servers (APP1, APP2, DB1) or a set of monitor programs (ie all the unix monitoring programs for your unix admin).

    As a note: we should probably should rename ContainerMap to ContainerProgram and ContainerOverride to ContainerSystem

    Container Definition via mimi.pl

       Program -> Container Mapping (Program=Sybase_ASE_Log) Container=DELETEME
          Note: Must add container by hand or it wont show
          Insert ContainerMap Values ('Sybase_ASE_Log','DELETEME')
       System -> Container Mapping (CONTAINER=DELETEME, SYSTEM=mlpsyb)
          if exists ( select * from Container where name='mlpsyb' )
                Insert ContainerOverride Values ('DELETEME','c','mlpsyb',null)
          else    Insert ContainerOverride Values ('DELETEME','s','mlpsyb',null)
    

    TABLE: ContainerMap

                fields monitor_program,container
    

    TABLE: Container_full

                fields name,element
    

    TABLE: ContainerOverride

                fields name, type, element, owner
    

    TABLE: Container

                fields name (container), type (s=system, c=container, p=program), element, owner (unused)
    

    PROCEDURE: build_container

       delete Container
    

       -- First map containers from ContainerMap
       INSERT Container
          select cm.container,'s',e.system,null
          from ContainerMap cm, Event e
          where e.monitor_program = cm.monitor_program
       union
          select cm.container,'s',e.system,null
          from ContainerMap cm, Heartbeat e
          where e.monitor_program = cm.monitor_program
          and e.batchjob is null
    

       delete Container
       FROM Container c1,  ContainerOverride c2
       where c1.name=c2.name
       and   c1.type=c2.type
       and   c1.element=c2.element
    

       INSERT Container
       select * from ContainerOverride
    

       delete Container_full
    

       INSERT Container_full
          select 'All',e.system from Event e
          where monitor_program!='Monitor'
       union
          select 'All',e.system from Heartbeat e
          where monitor_program!='Monitor'
          and e.batchjob is null
    

       INSERT  Container_full
       select  distinct name, element
       from    Container
       where     type = 's'
     union
       select  c1.name, c2.element
       from    Container c1, Container c2
       where   c1.type = 'c'
       and     c2.type = 's'
       and     c1.element = c2.name
    

    union

       select  c1.name, c3.element
       from    Container c1, Container c2, Container c3
       where   c1.type = 'c'
       and   c2.type = 'c'
       and   c3.type = 's'
       and   c1.element = c2.name
       and   c2.element = c3.name
    

    union

       select  c1.name, c4.element
       from    Container c1, Container c2, Container c3, Container c4
       where   c1.type = 'c'
       and   c2.type = 'c'
       and   c3.type = 'c'
       and   c4.type = 's'
       and   c1.element = c2.name
       and   c2.element = c3.name
       and   c3.element = c4.name
    

       INSERT    Container_full
       select    distinct 'All',e.system
       from    IgnoreList e
       where    e.system not in ( select element from Container_full )
    

       INSERT ProductionServers
       select distinct element,0
       from   Container_full
       where  element not in ( select system from ProductionServers )
    

    REPORTS: Containers

       select    name,
             Type=case when type='c' then 'Container'   when type='s' then 'Server'   else 'unknown'      end,
             element
       from Container order by name,type"
    

    REPORTS: System->Container

          select    name,   element from ContainerOverride order by name,type
    

    REPORTS: Program->Container

          select    monitor_program, container from ContainerMap order by monitor_progra
    

    
    

    OneQueryPerServerReport.pl report on system configurations

    USAGE

    OneQueryPerServerReport.pl

    Generic procedure runner. Will run a single query on each of your servers, formatting output correctly.

    REQUIRED ARGUMENTS

            -QUERY=QUERY
            -PROGRAMNAME=PROGRAMNAME        (default to BATCHID)
            -TITLE=TITLE
    

    OPTIONAL ARGUMENTS

            -HTML
            -NOLOCK                 (ignore lock file to prevent repeat runs)
            -BATCHID=batchid
            -NOHEADER               (ignore header rows from results)
            -PREREQ                 (prerequisite query - only continue if this returns something)
            -NOPRINTMSGS    (ignore messages from the server)
            -DEBUG                  (debug)
            -NOLOCK
            -SYSTEMS=systems
            -SERVER=Server
            -PROGRAMNAME
            -TITLE
            -PRINTSERVERLABEL       (pretty print a nice label for each server)
            -TYPE=(DB Type - sqlsvr / sybase / both)
    

    
    

    console_build_and_ftp.pl Rebuild the console and copy it

    SYNTAX

    perl console_build_and_ftp.pl $SYBASE

    SCHEDULING

    This program should be scheduled frequenly. It rebuilds the console from saved pages and can ftp it to remote sites.

    
    

    console_hourly.pl Run GEM console routine hourly tasks

    SYNTAX

    perl console_hourly.pl $SYBASE

    SCHEDULING

    This program should be scheduled about once an hour. It does needed maintenance on the web pages.

    
    

    console_nightly.pl GEM Console Nightly Batch

    SYNTAX

    perl console_nightly.pl $SYBASE

    SCHEDULING

    This program should be scheduled about once a night. It does needed maintenance on the web pages for static stuff. Specifically it does the following

      1.  Log itself with the alarming system.
      2.  Run document_all.pl -I10,15,18
    

         Rebuild all your web pages - heck its the middle of the night.  For a variety of reasons, the
         following 3 reports are NOT run (thats what the -I means = IGNORE)
    

         Report #10 - schema report
         Report #15 - File system checks
         Report #18- hourly server logs - format a recent error logs report
    

      3.  Run ftp_to_website.pl to copy the resulting web pages if you have decided that you want
          them to be copied to another web site.
    

    
    

    console_weekly.pl Resurvey the environment

    SYNTAX

    perl console_weekly.pl $SYBASE

    SCHEDULING

    This program should be scheduled once a week. It does a survey on the whole world and rebuilds the whole web site. This is basically discover.pl plus all the functionality of the console_nightly.pl.

    
    

    console_hourly.pl Run routine hourly tasks

    SYNTAX

    perl console_hourly.pl $SYBASE

    SCHEDULING

    This program should be scheduled about once an hour. It does needed maintenance on the web pages.

    
    

    console_nightly.pl GEM Console Nightly Batch

    SYNTAX

    perl console_nightly.pl $SYBASE

    SCHEDULING

    This program should be scheduled about once a night. It does needed maintenance on the web pages for static stuff. Specifically it does the following

      1.  Log itself with the alarming system.
      2.  Run document_all.pl -I10,15,18
    

         Rebuild all your web pages - heck its the middle of the night.  For a variety of reasons, the
         following 3 reports are NOT run (thats what the -I means = IGNORE)
    

         Report #10 - schema report
         Report #15 - File system checks
         Report #18- hourly server logs - format a recent error logs report
    

      3.  Run ftp_to_website.pl to copy the resulting web pages if you have decided that you want
          them to be copied to another web site.
    

    
    

    console_weekly.pl Resurvey the environment

    SYNTAX

    perl console_weekly.pl $SYBASE

    SCHEDULING

    This program should be scheduled once a week. It does a survey on the whole world and rebuilds the whole web site. This is basically discover.pl plus all the functionality of the console_nightly.pl.

    
    

    core_dump_checker.pl utility to find core dumps on unix systems

    USAGE

    core_dump_checker.pl -o outdir

    
    

    discover.pl Master Survey Program

    DESCRIPTION

    Survey your servers and store the results in gem.xml.

    USAGE

      discover.pl -Ssys1,sys2,sys3 -Tunix,oracle,sqlsvr -d
    

    where sys1..3 are systems and -T represents the 5 types currently supported (unix,oracle,sqlsvr,win32servers,sybase).

    -O to print diagnostic Output dump

    
    

    document_all.pl GEM Console Generator

    DESCRIPTION

    The GEM COnsole is a web system with html documentation on all your servers. The input (server names, passwords, report definitions) comes from your configuration files.

    document_all.pl is the main script called by the GEM batch jobs in order to create your console. The scheduled batch scripts are simply drivers around this script. document_all.pl is responsible for creating html output reports.

    This program integrates with the Alarming library to save alarms as approriate.

    USAGE

    document_all.pl -OUTDIR=outdir -REPORT=1,2,3... -IGNOREREPORT=1,2,3

            -OUTDIR dir       : output directory
            -QUICK                          : dont do the glue stuff
            -IGNOREREPORT=list: list of reports to ignore (comma separated)
            -REPORT=list      : list of reports to run (comma separated)
            -PRINTTIMING      : print timing information for reports
            -NOREPORTS        : no rebuild of pages
            -ALARM            : use mlp alarms
            -DO_SQLSVR        : run sql servers
            -DO_SYBASE        : run sybase servers
            -DO_ORACLE                      : run oracle servers
            -BATCHID          : label of which batch this was run by
            -CONSOLE_REBUILD  : quick run - just reformat main web site
    

    Report numbers are identified as follows

            1)  Interfaces file report
            4)  Create Debug/error report (by reading _debug.html files)
            5)  Weekly Security Reports
            6)  Disk usage report
            7)  Get backup server logs
            8)  Sybase server error logs
            9)  Space report
            10) Schema report
            11) Host os info (some reports require rsh setup)
            12) Host uptime info (requires rsh setup for rup command)
            13) ifconfig -a (requires rsh setup for rup command)
            14) Unix disk space (requires rsh for df -k)
            15) File checks (cores, crontabs, failed links, mail -> unix only)
            16) Dbcc & Backup reports
            17) User defined reports (see console.dat)
            18) Unused
            19) Unused
            20) Core Dump Checker (local, long)
            21) Gem Reports
            22) By Server Pages
            23) Weekly Updates (need this on windows or unix)
            24) Nighly Updates (need this on windows or unix)
            25) Database Debugger - Calls CheckServerDaily.pl
            26) Standard Internal Reports
    

    The program rebuilds your main web page automatically based on whatever output files exist in the output directory, so you do not need to run all reports every night.

    This program should be totally config file driven.

    FILES

    Console_Info.dat = identify console report layout

       these reports are found in $gem_root_dir."/data/html_output"
       when found they are reformatted and moved
       file format is comma separated ($FILENAME,$MENU,$TITLE,$IS_OPTIONAL)
       These reports are placed in $MENU/$TITLE with link referring to $FILENAME
    

    CONSOLE.dat

       These are custom reports processed by run_custom_defined and read_custom_defined_reports
    

    OUTPUT DIRECTORY

    The output directory is set via the driver scripts but is normaly data/CONSOLE_REPORTS. This directory contains a variety of subdirectories as needed.

    RELATED BATCH JOBS

    ConsoleArchiver.ksh

       /usr/local/bin/perl /apps/sybmon/dev/ADMIN_SCRIPTS/bin/ConsoleArchiver.pl --BATCHID=ConsoleArchiver $*
    

    ConsoleBuildAndFtp.ksh

       /usr/local/bin/perl  /apps/sybmon/dev/ADMIN_SCRIPTS/console/console_build_and_ftp.pl $*
    

    ConsoleUnixHourly.ksh

       /usr/local/bin/perl  /apps/sybmon/dev/ADMIN_SCRIPTS/console/console_hourly.pl $*
    

    ConsoleUnixNightly.ksh

       /usr/local/bin/perl  /apps/sybmon/dev/ADMIN_SCRIPTS/console/console_nightly.pl $*
    

    ConsoleUnixWeekly.ksh

       /usr/local/bin/perl  /apps/sybmon/dev/ADMIN_SCRIPTS/console/console_weekly.pl $*
    

    ConsoleWin32Hourly.ksh

       C:/perl/bin/perl.exe  //samba666/sybmon/dev/ADMIN_SCRIPTS/console/console_win32_hourly.pl $*
    

    ConsoleWin32Nightly.ksh

       C:/perl/bin/perl.exe  //samba666/sybmon/dev/ADMIN_SCRIPTS/console/console_win32_nightly.pl $*
    

    ConsoleWin32Weekly.ksh

       C:/perl/bin/perl.exe  //samba666/sybmon/dev/ADMIN_SCRIPTS/console/console_win32_weekly.pl $*
    

    UNDERLYING FUNCTIONALITY OF THESE BATCH JOBS

    The following convenience batche jobs are provided. They map 1:1 to batch jobs set up by the scheduler. The win32 jobs should be run on windows, the others only on unix. console_build_and_ftp.pl should be run either on windows or unix (but not both).

    console_build_and_ftp.pl => document_all.pl --BATCHID=ConsoleBuildAndFtp --PRINTTIMING -CONSOLE_REBUILD console_hourly.pl => document_all.pl --BATCHID=ConsoleUnixHourly -PRINTTIMING -REPORT=7,8,9 -ALARM -NATIVE console_nightly.pl => document_all.pl -BATCHID=ConsoleUnixNightly -IGNOREREPORT=10,15,18,20,22 -PRINTTIMING -NATIVE -ALARM console_weekly.pl => discover.pl -Tunix,oracle,sybase

                               => space_monitor.pl --BATCHID=WeeklyTablespaceWrite --REWRITE --NOGEMXMLWRITE --DOHELPTABLE
                               => document_all.pl -BATCHID=ConsoleUnixWeekly -IGNOREREPORT=100 -PRINTTIMING -ALARM -NATIVE
    

    console_win32_hourly.pl => document_all.pl -BATCHID=ConsoleWin32Hourly -PRINTTIMING -REPORT=7,8,9 -ALARM -NATIVE console_win32_nightly.pl => document_all.pl -BATCHID=ConsoleWin32Nightly -IGNOREREPORT=10,15,18,20,22 -PRINTTIMING -ALARM -NATIVE console_win32_weekly.pl => discover.pl -Twin32servers,sqlsvr

                               => space_monitor.pl --BATCHID=WeeklyTablespaceWrite --REWRITE--NOGEMXMLWRITE
                               => document_all.pl --BATCHID=ConsoleWin32Weekly -IGNOREREPORT=100 -PRINTTIMING -ALARM  -NATIVE
    

    NATIVE SETTINGS

    The GEM Console is designed to identify your environment and set the standard variables as appropriate when --NATIVE is passed in. Specifically --NATIVE will set -DO_SQLSVR, -DO_SYBASE, -DO_ORACLE and --PRIMARY.

       if( $CONFIG{INSTALLTYPE} == WINDOWS and is_nt() ) {
             $DO_SQLSVR=1 if $CONFIG{USE_SQLSERVER} eq "Y";
             $DO_SYBASE=1 if $CONFIG{USE_SYBASE_ASE} eq "Y";
             $DO_ORACLE=1 if $CONFIG{USE_ORACLE} eq "Y";
       } elsif( $CONFIG{INSTALLTYPE} == UNIX & ! is_nt() ) {
             $DO_SYBASE=1 if $CONFIG{USE_SYBASE_ASE} eq "Y";
             $DO_ORACLE=1 if $CONFIG{USE_ORACLE} eq "Y";
       } elsif( $CONFIG{INSTALLTYPE} == SAMBA and is_nt() ) {
             $DO_SQLSVR=1 if $CONFIG{USE_SQLSERVER} eq "Y";
             $DO_SYBASE=0;
             $DO_ORACLE=1 if $CONFIG{USE_ORACLE} eq "Y";
       } elsif( $CONFIG{INSTALLTYPE} == SAMBA and ! is_nt() ) {
             $DO_SYBASE=1 if $CONFIG{USE_SYBASE_ASE} eq "Y";
       }
    

    
    

    findid.pl report on space

    USAGE

    findid.pl -i sourcedir -A -N

       -d debug mode
       -N dont use html
    

    
    

    ftp_to_website.pl copy reports to target directory

    SYNOPSIS

    This program copys the files to your web site - as specified in the GEM configuration utility. Sometimes, the local directory is not where you wish to publish your console. This program does the copy, or ftp if needed, to your actual web site. This modified version of ftp_to_website.dist is customized during the configuration process to include your server information.

    
    

    gem_file_manager.pl print file saved information

    DESCRIPTION

    Optionally Fetch files or Print File Information From gem.xml.

    USAGE

    gem_file_manager.pl -SERVERNAME=sys1,sys2,sys3 -DEBUG -DOREPORT -DOANALYSIS -DOF ETCH -OUTFILE=file -FILETYPE=FILE_TYPE

    This is the main program Retrieve, Parse, and Handle Files From Your Systems

    Required: -FILETYPE=FILE TYPE

          BACKUP_SERVER or ASE_SERVER or HISTORICAL_SERVER or MONITOR_SERVER
             or REP_SERVER or Envronment File, Errorlog, Interfaces File, Run File
    

    Required: One of these 3 operations

       -DOREPORT      print a report of files to fetch
       -DOANALYSIS    analyze the files you have collected
       -DOFETCH       fetch files
    

    Optional:

        -HTML               html output
        -SAVE_TO_ALARMDB    save to alarm database
        -OUTFILE=outputfile
        -BATCHID=BATCHID    key for the alarms system (internal)
        -PATFILE=paternfile patern files allows restart for logging
        -SERVERNAME=Svrs    to work on (default is all of appropriate type)
        -TODAYYESTERDAYONLY filter for today/yesterday errors (10000 messages)
        -NOERRORFILTER      dont filter non errors out (show all)
        -PURGESIZE=<size>   purge source files larger than <size> bytes (after fetch
    

    )

        The Default Filter will filter to a maximum 1000 messages per file
    

    
    

    interfaces_file_rpt.pl report on interfaces files

    USAGE

    interfaces_file_rpt.pl [--PORTLIST] [--DEBUG] [--HTML]

    
    

    space_errors.pl report on space

    USAGE

    space_errors.pl -i sourcedir -A -N

       -d debug mode
       -N dont use html
    

    
    

    space_monitor.pl space monitor for sybase and sql server

    USED BY

    SybSpaceMonitor SpaceMonitorSqlsvr

    DESCRIPTION

    This utility monitors database space

    It also monitors space history into ascii files. This utility will save table space information the first time it is run every day. These files are named helptable.SERVER.DB.DATE.TIME. It will also save database level space information so that can be tracked. Data is saved in the gem/data directory in ascii file format.

    You may need to run two batches of this job - one for sqlserver (only works from windows) and one for sybase (works both windows and sql).

    USAGE

    space_monitor.pl -outdir=outdir

    MONITOR SPACE USAGE ON YOUR SYBASE/SQL SERVER DATABASES

    -BATCHID=batchid -DEBUG (debug) -SERVER=Server -DATABASE=Database -TYPE=(DB Type - sqlsvr / sybase / both) -DOHELPTABLE ( table space ) -REWRITE (Rewrite) -ALARM (Alarm appropriately) -NOGEMXMLWRITE (no gem.xml write)

    With --DOHELPTABLE saves sp_helptable into output directory in files named

       helptable.SERVER.DB.DATE.TIME
    

    This directory output is useful for the program plot_tablespace.pl This program works with space_report.pl

    OVERRIDES

    The program uses threshold_overrides.dat for override information.

    
    

    space_report.pl report on space

    USAGE

    space_report.pl -i outdir -d

    NOTES

    looks through the system_information_data tree and gets files starting with "dbspace_". These files contain space used information - one line per day. This data is parsed and put into a reasonable report.

    The files are created by space_monitor.pl
    

    unix_space_monitor_via_rsh.pl monitor space on your unix systems

    DESCRIPTION

    Monitor Unix Disk Space on all servers tagged COMM_METHOD=RSH or SSH and then store results using the alarming subsystem. Should be run frequently. When run from GEM console, the report created is put onto web pages.

    ARGUMENTS

    Use -A to save heartbeats.

    SYNTAX

    unix_space_monitor_via_rsh.pl -o outdir -A

                            -A use mlp alarms
                            -t test mode - additional prints
                            -S hostname (comma separated)
    

    Monitor Unix Disk Space on all servers tagged with COMM_METHOD=SSH or RSH and STORE results using the alarming subsystem. Should be run frequently. When run from GEM console, the report created is put onto web pages.

    Stored procedure library

    The following is a guide to a FREE set of stored procedures that extend the Sybase and Microsoft SQL Server provided system procedures. These procedures provide additional functionality and new ways to look at the data provided in the System Tables. They also provide new formats that allows users, developers, and administrators to see the data they want in the format they want. This collection has been developed by several authors, and is maintained by Edward Barlow. This package is intended to be a professional tool suitable for general use on all your servers.

    These procedures are installed into sybsystemprocs on Sybase and into master on Microsoft Sql Server. All procedure names start with "sp__" (two underscores). This naming convention ensures that the procedures, when run, will be available from any database and that, when they are run, the database context will be the current database. This naming convention also ensures that there are no naming conflicts with Sybase's internal procedures. Thus, if you want to see space in the statsdb database, you can run:

      use statsdb
      exec sp__dbspace
    

    Most of these procedures can be passed the parameter @dont_format='Y' to produce unformatted output. One goal was to produce output that is readable in 80 column mode for convenience in isql/osql. This is done by truncating some fields (dbname, username etc), which can result in truncated output. If you need more detail or are using a graphical query analyzer you can pass the @dont_format='y' argument and it will show all fields at full length (no column truncation).

       sp__who @dont_format='Y'
    

    Extended Stored Procedure Library Links. Home Page   Standalone Download   GEM Download

    These stored procedures are released as Free software under a standard GPL license.

    These procedures have been tested under

      SQL SERVER 2000
      SQL SERVER 2005
      SYBASE 4.9
      SYBASE 10
      SYBASE 11
      SYBASE 12
      SYBASE 15
    

    Installation

    The library is distributed in two ways. The Generic Enterprise Manager software incldues a copy and a full featured user interface to install the procedures. GEM also uses these procedures extensively - the console creates numerous reports based on these procedures that can be viewed using your web browser. You can also download these procedures as a standalone package.

    The primary interface to install this library is to use the configure.pl program. configure.pl is a perl script that requires DBI and either DBD::Sybase (unix) or DBD::ODBC (windows). You can also use the older - unsupported .bat/.sh files that are shipped with the distribution but let me repeat - they are not supported. The .bat files require two parameters SERVERNAME and PASSWORD (the installSQLSVR.bat script only requires SERVERNEAME - it uses native authentication).

    To run configure.pl type

      perl configure.pl -?
    

    which should respond with

       Unknown option: ?
       usage: configure.pl --DEBUG --USER=sa --PASSWORD=pass --SERVER=svr --MINVERSION=ver --UPGRADE_TO_LATEST --ALLSERVERS|--SQLSERVERS|--SYBSERVERS [-FILE=File]
    

    If it does not produce the above output on windows - and the cause is missing libraries (the above mentioned DBI and DBD::ODBC), you can install them by running

      ppm install DBI
      ppm install DBD::ODBC
    

    If the libaries are missing on unix, contact your unix administrator.

    If the above command works (ie it displays the command syntax) - your perl has all necessary modules installed - you can just run

       perl configure.pl
    

    ignoring the command line arguments and it will ask you for SERVER/LOGIN/PASSWORD. The login you use must have sa_role (sybase) or sysadmin (sql server) role.

    As system databases will be modified by these procedures, it is suggested that you dump that database before you load the procedure.

    If you are not able to get the perl installer working - you can install via the following unspported scripts:

     configure.sh - the original shell script installer
     installSQLSVR.bat
     installSYBASE12.bat
     installSYBASE15.bat
     installSYBASE_MDA.bat - must change to mda procedure directory
    

    You are, of course, required to know the sa password to the server to install. Well thats not quite true - sql server, if you enter an incorrect password, will attempt to use native windows authentication (so the install will work if you have admin role on the server). Note that, on sybase, the default size for sybsystemprocs is a bit small - it is recommended you extend the default size to use the full sysprocsdev device that gets allocated during installation. You could always just try an install and if you get out of space messages - to extend the space and reinstall.

    Special thanks

    Special thanks to Simon Walker of the SQL Workshop Ltd, who authored many of them and who greatly assisted in this project. Special thanks to Desiree Johnson for her help. The menu.sh program is compliments of Lars Karlsson of Sweden. The permanent home for this distribution is http://www.edbarlow.com

    Right to use, resale and copyright

    All procedures in this package are copyright (c) 1994-2007 by Edward Barlow. They are released under a standard GPL license agreement. The license will be changed to GPL v3 when the GPL v3 draft is formalized.

    You may redistribute the package at will (see below). Tell your friends. Give me access to procedures that you have written for future versions. Tell me about bugs. Be nice - I am making no money off this.

    You are allowed to use this software so long as all copyright notices, README, and other documentation are not altered and so long as no money is made by the sale of this software (i.e. you cant include it in a commercial package without permission). If you would like to "make money" or include the code in a commercial package, I ask that you decide on a "fair" price and create some form of "fair" agreement. Make two copies, sign them both, and send them to the package author (Edward Barlow). If the agreement seems fair, I will sign both and send one copy back to you, and we will have a deal. I have put significant effort into this code and, while my primary purpose is to create software for people to use, I expect a fair shake from anybody who can profit from my endeavors.

    Procedures submitted by outside authors retain the authors copyright (which can be found in the code files and which should be noted in this documentation) but I (Edward Barlow and any companies I create to distribute software) get the right to redistribute them as i see fit (this is to protect me).

    Uninstall

    SQL statements to uninstall this package are contained in the file cleanup.SQL.

    Warranty

    (Are you kidding...) This software is provided as-is. No warranties or guarantees are made. To the best of my knowledge, any bugs or outstanding issues are documented in the file "BUGS" that comes with the source code.

    That file is probably NOT shipped in this distribution because there are no no bugs - although there is generally a bunch of changes that get made whenever the Database vendor releases a new version of the database. If you notify me of any bugs, i will fix them as i can.

    List of procedures

    A menu program that provides a shell level interface is available, thanks to Lars Karlsson of Sweden, in the program menu.sh. Run it and see!

    GENERAL HELP PROCEDURES

    sp__depends	Better version of sp_depends
    sp__help	Better sp_help
    sp__helpdb	Database Information
    sp__helpdevice	Break down database devices into a nice report
    sp__helpgroup	List groups in database by access level
    sp__helpindex	Shows indexes by table
    sp__helpsegment	Segment Information
    sp__helprotect	Simple Protection Info for the database
    sp__helptext	Show comments with line splits ok
    sp__helpuser	Lists users in current database by group (includes aliases)
    sp__lock	Lock information
    sp__syntax	Works on any procedure to give you syntax
    sp__who	sp_who that fits on a page

    SYSTEM ADMINISTRATOR PROCEDURES

    sp__block	Blocking processes.
    sp__dbspace	Summary of current database space information.
    sp__dumpdevice	Listing of Dump devices
    sp__diskdevice	Listing of Disk devices
    sp__helpdbdev	Show how Databases use Devices
    sp__helplogin	Show logins and remote logins to server
    sp__helpmirror	Shows mirror information, discover broken mirrors
    sp__segment	Segment Information
    sp__server	Server summary report (very useful)
    sp__stat	Give basic server performance information (loops)
    sp__vdevno	Who's who in the device world

    DBA PROCEDURES

    sp__badindex	list badly formed indexes (allow nulls) or those needing statistics
    sp__collist	list all columns in database
    sp__find_missing_index	Finds keys that do not have associated index
    sp__flowchart	Makes a flowchart of procedure nesting
    sp__groupprotect	Permission info by group
    sp__indexspace	Space used by indexes in database
    sp__id	Gives information on who you are and which db you are in
    sp__noindex	list of tables without indexes.
    sp__helpcolumn	show columns for given table
    sp__helpdefault	list defaults (part of objectlist)
    sp__helpobject	list objects
    sp__helpproc	list procs (part of objectlist)
    sp__helprule	list rules (part of objectlist)
    sp__helptable	list tables (part of objectlist)
    sp__helptrigger	list triggers (part of objectlist)
    sp__helpview	list views (part of objectlist)
    sp__objprotect	Permission info by object
    sp__read_write	list tables by # procs that read, # that write, # that do both
    sp__trigger	Useful synopsis report of current database trigger schema
    sp__whodo	sp__who - filtered for only active processes

    AUDIT PROCEDURES

    sp__auditsecurity	Security Audit On Server
    sp__auditdb	Audit Current Database For Potential Problems
    sp__checkkey	Generate script for referential integrity problems (uses key info from sp_foreignkey)

    REVERSE ENGINEERING PROCEDURES

    sp__revalias	get alias generation script for current database
    sp__revdb	get database generation script for server
    sp__revdevice	get device generation script for server
    sp__revgroup	get group generation script for current database
    sp__revindex	get index generation script for current database
    sp__revlogin	get login generation script for server
    sp__revmirror	get mirror generation script for current database
    sp__revsegment	get segment generation script for current database
    sp__revtable	get table generation script for current database
    sp__revuser	get user generation script for current database

    OTHER PROCEDURES

    sp__bcp	Create unix script to bcp in/out database
    sp__date	Who can remember all the date styles?
    sp__iostat	Loops n times showing active processes only
    sp__grep	Search for patern
    sp__isactive	Shows info about a single active process
    sp__ls	Lists specific objects
    sp__quickstats	Quick dump of server summary information
    sp__whoactive	Show info about who is active

    Auditing error codes

    The following is a list of errors generated by the auditing procedures

       31001 User +name+ Is Locked
       31002 Login +name+ Is Expired
       31003 User +name+ Has Null Password
       31003 User +name+ Has Null/Short Password
       31003 User +name+ Has Short (<=4 character) Password
       31004 User +name+ Has +dbname+ Database As Default
       31005 Allow Updates is Set
       31006 ERROR: Num Open Devices Parameter Set Too Low
       31007 ERROR: Num Open Databases Parameter Set Too Low
       31008 Allow Updates is Set
       31009 User +name+ Has Pasword=Id
       31010 Allow Updates is Set
       31011 Tempdb is only +@tempdb)+MB
       31012 User sa is trusted from +srvname
       31013 Database +name+ Created For Load
       31014 Database +name+ Suspect
       31015 Database +name+ Offline
       31016 Database +name+ Offline until recovery completes
       31017 Database +name+ Is Being Recovered
       31018 Database +name+ Has Suspect Pages
       31019 Database +name+ Is Being Upgraded
       31020 Database +name+ -> No Log Device and No TL on Chkpt
       31021 ERROR: MIRROR BROKEN: +name
       31022 ERROR: CHECK MIRRORING: +name
       31025 x.name+ Config Has Been Reset To +c.value2+ (default=+c.defvalue)
       31030 ERROR: Login +name+ Has an invalid default db (dbname)
       31031 Device +rtrim(dev.name)+ Is Mapped But Unused (no segments). Size (MB) = usg.size/512.
       31032 ERROR: Sybsystemprocs should be > 60 MB (@c)
       31033 ERROR: tempdb should be >= 100 MB (@c)
       31034 Database +name+ Is not auto-shrink
       31035 Database +name+ Has No Torn Page Detection
       31036 Database +name+ Is Unusable
       31037 User +name+ Has Stupid Password
       31038 user +u.name+ can use db but lacks login
       31040 File +name+ Is Not Autogrowth
       31101 User +user_name(uid)+ Owns Objects
       32000 Login +m.name+ is aliased to +u.name
       32007 suid + a.suid+ lacks login and is aliased to +u.name
       32008 user +u.name+ can use db but lacks login suid=suid
       32009 database +db_name()+ has not been tran dumped in
       32010 Object +name+ Has No Comments
       32010 count(*)+ Objects Have No Comments
       32011 Comments for id + id + have no object
       32013 v.name + Permission granted to + u.name
       32014 Table +object_name(id)+ Index + name + is suspect
       32016 Object: +object_name(s1.id)+ Has Statistics From +s1.moddate 0)+ and +s2.moddate 0)
       32017 Object: +object_name(s1.id)+ Has OLD Statistics From +s1.moddate 0)
       32018 name + permissions granted to public on +object_name(id) from sysprotects master.dbo.spt_values c
       32019 db_name()+ has +@dat+MB data and + @log+MB log
       32100 Login + m.name + is aliased to + u.name
       32101 User +user_name(uid)+ Has + count(*)+ Objects
       32101 User +user_name(uid)+ Owns Objects
       32102 Object +name+ exists in the db and in sybsystemprocs - This is a possible Trojan Horse
       32102 Object +name+ in master - Possible Trojan Horse
       32102 Object +o.name+ Exists in master and the database - Possible Trojan Horse
       32102 Object +o.name+ Possible Trojan Horse (Exists in master)
       32103 Object +object_name(id)+ has access to syslogins
       32104 No Groups Exist In Database +db_name()
       32105 User +n1.name+ is a member of group public
       32106 Group Public +rtrim(v.name)+ access to + count(*) + objects
       32106 Group Public access to object +o.name+ type=+o.type
       32110 DB Collation +collation+ != Server Collation + serverproperty(collation)
    

    
    

    sp__auditdb Audit current database

    AUTHOR

    Edward Barlow ( SQL Technologies, inc. )

    DESCRIPTION

       Checks Common Database Problems
       Lists users in group public if groups are used.
       Warn about lack of groups if no groups exist besides public.
       List users aliased to another non dbo user.
       List aliases without logins (login previously dropped).
       List users without logins (login previously dropped).
       List objects owned by non-dbo (maybe poor code control?).
       Find objects with access to syslogins in them. This
          procedure excludes normal objects like sp__addlogin. Use
          of this procedure will identify potential Trojan horses.
       Find any objects with public access.
       If not master db, list any objects starting with "sp_" that
          are also in master (Trojan horses).
       Database has not had transaction log dump in 24 hours.
       Checks Object / Comment mismatch (hand deleted, or rename)
       Create object permissions granted to users
    

    USAGE

    sp__auditdb [@srvname, @hostname ]

    External programs that collect errors can pass the parameters parameters (@srvname & @hostname) to the procedure. If these are passed, the procedure will print a slightly different return set that includes error numbers and other information.

    SEE ALSO

    sp__auditsecurity

    ACCESS

    This procedure can be only runable by sa because it may reveal information that can help an intruder..

    SAMPLE OUTPUT

    1> sp__auditdb

       Error
       ---------------------------------------------
       Object get_comn_syslogins has access to syslogins
       Object get_comn_sysusers has access to syslogins
       Object get_comn_sysusers has access to syslogins
       User sa is a member of group public
       Group Public access to object pb_catcol type=P
       Group Public access to object pb_catedt type=P
       Group Public access to object pbcatfmt type=U
       Group Public access to object pbcattbl type=U
    

    
    

    sp__auditsecurity Audit system security

    AUTHOR

    Edward Barlow ( SQL Technologies, inc. )

    DESCRIPTION

      Reports Users With Passwords like the Username
      Reports Users With Null Passwords
      Reports Users With Short (<=4 character) Passwords
      Reports Users With Master/Model/Tempdb Database As Default (except sa)
      Reports allow updates is set
      Reports Users with stupid passwords like "sybase"....
    

    USAGE

    sp__auditsecurity [@print_only_errors,] [@srvname, @hostname ]

    if @print_only_errors is not null then prints only errors. Otherwise it will print statements about successes

    Programs that collect errors can pass the parameters parameters (@srvname & @hostname) to the procedure. If these are passed, the procedure will print a slightly different return set that includes error numbers and other information.

    SEE ALSO

    sp__auditdb

    ACCESS

    This procedure is only runable by sa because it reveals users with weak passwords.

    SAMPLE OUTPUT

      1> sp__auditsecurity
    

      Security Violations
      ------------------------------------------------------
      (No Users With Null Passwords)
      User monitor Has master Database As Default
      User mon6 Has master Database As Default
      User a Has master Database As Default
      Allow Updates is Set
      (Allow Updates is Not Set)
      (No Trusted Remote Logins)
    

    
    

    sp__badindex List badly formed indexes or those needing statistics

    AUTHOR

    Edward Barlow ( SQL Technologies, inc. )

    DESCRIPTION

    Identifies bad indexes according to the following rules. Finds indexes containing null, vbl lth, text, or image columns. Find indexes over 30 bytes long or indexes that have never had statistics updated on them. List NC indexes on small tables.

    USAGE

    sp__badindex [ @tablename ]

    SAMPLE OUTPUT

    1> exec sp__badindex

     Table/Index Name               Description            Problem Found
     ------------------------------ ---------------------- --------------
     alerts.XPKalerts               Length = 60            >30 Byte Index
     alerts.XPKalerts               srvname char(30)       Allows Null
     audit_trail.XPKaudit_trail     Length = 38            >30 Byte Index
     comn_database.XPKdatabase      Length = 60            >30 Byte Index
     comn_dumpdevices.XPKcomn_dumpd Length = 60            >30 Byte Index
     comn_syscolumns.XPKcomn_syscol Length = 94            >30 Byte Index
    

    
    

    sp__bcp Produce object

    AUTHOR

    Simon Walker, The SQL Workshop LTD.

    DESCRIPTION

    Creates bcp in / out shell script that can be used to extract info from the database.

    USAGE

    sp__bcp {server}, [database], [user], [password], [direction], [extension], [commands]

    where...

            {@server} Server name (should really be entered since
                  @@servername is rarely defined)
            [@database] Defaults to database procedure is run in
            [@user] Defaults to current username
            [@password] Defaults to current password
            [@direction] "out" or "in". Defaults to out
            [@extension] File extension will default to .dat
            [@commands] Allows you to enter further switching commands
                  (-c option to bcp)
    

    BUGS

    It is advisable to run the stored procedure through isql in at least 132 column mode (-w132) to stop the crummy (isql) program from inserting linefeeds.

    SAMPLE OUTPUT

            1> use master
            1> sp__bcp SYBASE,master,sa,xxx
            echo ""
            echo ""
            echo BCP out table master..spt_committab
            bcp master..spt_committab out spt_committab.dat -Usa -Pxxx
            -SSYBASE -c
            echo ""
            echo ""
            echo BCP out table master..spt_values
            bcp master..spt_values out spt_values.dat -Usa -Pxxx -SSYBASE
    

    
    

    sp__block Show blocked processes details

    AUTHOR

    4.9 version: Simon Walker, The SQL Workshop LTD. System 10 version: Ed Barlow

    DESCRIPTION

    Monitor Blocked Processes

    SEE ALSO

    sp__block sp__lockt

    SAMPLE OUTPUT

      1> sp__block
      SPID User       Host   Program    Blocking on Table    Lock Type
      ---- ---------- ------ ---------- -------------------- ---------------
      7    giraffe                      AARDVARKS_R_US       Update_page-blk
      7    giraffe                      AARDVARKS_R_US       Ex_page-blk
    

      Blocked SPID Blocked User Host Program Blocked By SPID
      ------------ ------------ ---------- ---------- ---------------
      26           edisking                           7
      26           ediswise                           7
      45           iamrich                            7
      45           iampoor                            7
    

    
    

    sp__checkkey Creates script you can use to check db referential integrity.

    AUTHOR

    Ed Barlow

    DESCRIPTION

    Create script to check foreign key relationships. For example if you have a field stor_id in table sales that indicates the store the sales are for, the script output lists stor_id's in sales that do not have rows in store. The script uses foreign keys that you have set up.

    SAMPLE OUTPUT

        1> sp__checkkey
    

        [ for each foreign key in database ]
        declare @cnt int
        set nocount on
        select title_id into #tmp from roysched
        delete #tmp from #tmp p,titles d where p.title_id=d.title_id
        if exists ( select * from #tmp )
        begin
         select @cnt=count(*) from #tmp
         if @cnt>=100
         print 'first 100 keys in roysched w/o data in titles'
         else
         print 'distinct keys in roysched w/o data in titles'
         set rowcount 100
         select distinct * from #tmp
         set rowcount 0
        end
        drop table #tmp
        go
        [ WHEN RUN THIS SCRIPT PRODUCES ]
    

        1> [ execute above ]
        distinct keys in roysched w/o data in titles
    

        title_id
        ----------
        A12224
    

    
    

    sp__colconflict Analyze conflicting columns definitions in current database

    AUTHOR

    Edward Barlow ( SQL Technologies, inc. )

    DESCRIPTION

    Reports column with multiple definitions (for example, one. defined in table A as an int and in B as a smallint).

    USAGE

    sp__colconflict [ @objectname ]

    @objname specifies objects to select (query like "%@objname%")

    SEE ALSO

    sp__helpcolumn, sp__collist

    sp__helpnull

    SAMPLE OUTPUT

     1> exec sp__colconflict
     Column               Table                Defn            Null
     -------------------- -------------------- --------------- --------
     attribute            schedule_history     char(30)        null
     attribute            schedule_attributes  varchar(127)    null
     description          error_severity       char(18)        null
     description          system               char(255)       null
     description          disks                char(30)        null
     description          user_view            char(30)        null
     description          remarks              varchar(127)    null
     description          hardware             varchar(127)    null
     group_name           schedule_defn        char(18)        not null
     group_name           schedule_groups      char(18)        not null
     group_name           comn_sysusers        char(30)        not null
     group_name           model                char(30)        null
    

    
    

    sp__collist Analyze columns in current database

    AUTHOR

    Edward Barlow ( SQL Technologies, inc. )

    DESCRIPTION

    Reports column definitionsand reports columns with multiple definitions (for example, one. defined in table A as an int and in B as a smallint) and columns that have conflicting "allow null" definitions.

    USAGE

    sp__collist [ @objectname ]

    @objname specifies objects to select (query like "%@objname%")

    SEE ALSO

    sp__helpcolumn

    SAMPLE OUTPUT

      1> exec sp__collist
      column          type            Nulls    Ident    Num Tables
      --------------- --------------- -------- -------- ----------
      action          char(18)        null              1 Tables
      allow_null      tinyint         null              1 Tables
      allow_updates   smallint        null              1 Tables
      attribute       char(30)        null              1 Tables
      attribute       varchar(127)    null              1 Tables
      audit_trail     tinyint         not null          1 Tables
      benchmark       int             null              4 Tables
    

    
    

    sp__colnull columns with conflicting nullity

    AUTHOR

    Edward Barlow ( SQL Technologies, inc. )

    DESCRIPTION

    Reports column with same name but conflicting nullity (for example, one. defined in table A as allow null and in B as not allow null).

    USAGE

    sp__colnull [ @objectname ]

    @objname specifies objects to select (query like "%@objname%")

    SEE ALSO

    sp__helpcolumn, sp__collist,

    sp__helpconflict

    SAMPLE OUTPUT

     1> exec sp__colnull
     Column               Table                Defn            Null
     -------------------- -------------------- --------------- --------
     controller           disk_usg             char(10)        not null
     controller           controllers          char(10)        not null
     controller           disks                char(10)        null
     crdate               audit_trail          datetime        not null
     crdate               db_space_save        datetime        not null
     crdate               comn_sysobjects      datetime        not null
     crdate               server_syslocks      datetime        not null
     crdate               db_space_history     datetime        not null
     crdate               password_history     datetime        not null
     crdate               table_space_save     datetime        not null
     crdate               table_space_history  datetime        not null
     crdate               alerts               datetime        null
     crdate               comn_database        datetime        null
    

    
    

    sp__configure a better system configuration viewer

    AUTHOR

    Edward Barlow ( SQL Technologies, inc. )

    DESCRIPTION

    This, unlike sp_configure, gives straight result set viewing, sorted into categories. It also shows any defaults if available.

    SEE ALSO

    USAGE

     Proc_name                      Order Parameter
     ------------------------------ ----- ------------------------------
     sp__configure                      1 @dont_format char(1) NOT NULL
    

    SAMPLE OUTPUT

     Category                     Option Name               Value   Default
     ---------------------------- ------------------------- ------- -------
     default data cache           2K I/O Buffer Pool        7800    NULL
     SQL Server Administration    Upgrade version           11510   1100
     default data cache           User Defined Cache        0       0
     default data cache           User Defined Cache        7800    NULL
     Physical Memory              additional netmem         0
     Languages                    default character set ID  2       1
     Languages                    default language          0
    

    
    

    sp__date Show date conversion formats for the server

    AUTHOR

    Simon Walker, The SQL Workshop LTD.

    DESCRIPTION

    show date conversion formats for server. you could look it up but...

    USAGE

    sp__date [ @datestring ]

    @datestring is date string to convert - if not given uses getdate()

    SAMPLE OUTPUT

    1> exec sp__date

      0   Oct 31 1997 12:21AM           100  Oct 31 1997 12:21AM
      1   10/31/97                      101  10/31/1997
      2   97.10.31                      102  1997.10.31
      3   31/10/97                      103  31/10/1997
      4   31.10.97                      104  31.10.1997
      5   31-10-97                      105  31-10-1997
      6   31 Oct 97                     106  31 Oct 1997
      7   Oct 31, 97                    107  Oct 31, 1997
      8   00:21:35                      108  00:21:35
      9   Oct 31 1997 12:21:35:150AM    109  Oct 31 1997 12:21:35:150AM
      10  10-31-97                      110  10-31-1997
      11  97/10/31                      111  1997/10/31
      12  971031                        112  19971031
    

    
    

    sp__datediff Gives real datediff between time and now

    AUTHOR

    Edward Barlow ( SQL Technologies, inc. )

    ARGUMENTS

            @starttime datetime
            @scale char(1)
            @outp float     output
    

    DESCRIPTION

    Returns time differences in FLOATING format. So difference between 3:20AM and 4:40AM in hours is 1.333 and in days is 1.333/24 or about .055.

     if     @scale='h'
            select @outp= convert(float,datediff(mi,@startdate,getdate()))/60
     else   if @scale='d'
            select @outp= convert(float,datediff(hh,@startdate,getdate()))/24
     else   if @scale='m'
            select @outp= convert(float,datediff(ss,@startdate,getdate()))/60
     else   if @scale='s'
            select @outp= convert(float,datediff(ss,@startdate,getdate()))
    

    SAMPLE OUTPUT

     1> declare @x float
     2> exec sp__datediff "Jan 20 1999","h",@x
    

    
    

    sp__dbspace Show current db space

    AUTHOR

    Unknown

    DESCRIPTION

    calculates out amounts reserved and used for current database

    SEE ALSO

    sp__qspace

    SAMPLE OUTPUT

     1> sp__dbspace
     1> exec sp__dbspace
     Name      Data MB    Used MB     Percent Log MB  Log Used  Log Pct
     --------- ---------- ----------- ------- ------- --------- -------
     statsdb           10         6.6   66.29       0      0.00    0.00
    

    
    

    sp__dbuse Show system information

    AUTHOR

    Philippe Wathelet (Flexible Consulting) philipew@hotmail.com )

    VERSION

    5.2

    DESCRIPTION

    This procedure gives a complete outlook of a database relating to its various components, including the server it runs on. It is designed to operate in a System 11 environment only.

    USAGE

     ......:1>sp__dbuse <database name>
     ......:2>go
    

    From any database including . If only the beginning of the DB name is given then the full name is found if identifiable.

    OR

     ......:1>sp__dbuse
     ......:2>go
    

    defaults to the current DB. This usage will return the most information.

    OR

     master:1>sp__dbuse
     master:2>go
    

    If the current DB is master, defaults to the DB with the log full else to the first DB with a blocking lock on else to the DB with the largest data percentage full.

    SEE ALSO

    SAMPLE OUTPUT

     ----------------------------------------------
     You are 'SYS_INSTALL' as 'dbo' under SPID 10
    

    on with role(s): sa, sso, oper

     -DATABASE:  Monday Oct 11 1999 11:18AM
     ========
    

     Database: xrm_db          dbid:5
     DB owner: xrm_mgr         suid:6
     Creation: Apr  1 1999  9:42AM     MB:      52
    

     -PLACEMENT:
     =========
    

     id Database    id Database
     -- ------------------- -- -------------------
     4  sybsystemprocs
     5  xrm_db <<<<<<<<<<<<
     6  xrm_sod01_db
     7  xrm_security_db
     8  sim_db
    

     -OPTIONS:
     =======
    

     - Select into/bulkcopy/pllsort
     - Trunc log on chkpt
     - Abort tran on log full
    

     -IN USE BY:
     =========
    

     All DBs  # # # # # # # # # # # # # # # # # #
     This DB  xrm_db is NOT in use
    

     -ROLES:
     =====
    

     sa sso        oper       replication
     ---------- ---------- ---------- ------------
     sa
     SYS_OPER
     SYS_INSTAL
     SCRIPT_SVR
    

     -SERVER:
     ======
    

     Running  ####################################
     Idle     ##################################
    

     SQL      #############
     I/O      ####################################
    

     Received ####################################
     Sent     ###############################
     Errors: 2
    

     Read     #######
     Write    ####################################
     Errors: 0
    

     -ENGINES:
     =======
    

     Nr     Status  # Pr    Online since
     0      online  0       Oct  6 1999 11:48AM
    

     -DATA:
     ====
     Size MB    Used MB    Full %          Free MB
     40         30.8       77.1            9.2
    

     -LOG:
     ===
    

     Size MB    Used MB    Full %          Free MB
     12         0.1        0.8             11.9
    

     -ALLOCATED:
     =========
    

     Device      Usage                           Size MB
     datadev1       -           Data        -       40
     logdev1        -           Log         -       10
     logdev1        -           Log         -       2
    

     -AVAILABLE: (max used Virtual Device Nr = 3  )
     =========
    

     Device VDN    Total MB    Free MB
     datadev1       2            28
     logdev1        3            13
     master         0            1
     sysprocsdev    1            0
                 + ------     + -----
                    387          42
     ----------------------------------------------
     see also 'sp__dbuse usage'
    

    
    

    sp__depends It's a superset of sp_depends.

    AUTHOR

    Q Vincent Yin (umyin@mctrf.mb.ca), Sep 1995

    DESCRIPTION

    This proc can handle usertypes, defaults and rules that are not covered by the original proc sp_depends. For tables, procs, etc, that are covered by sp_depends, this proc will simply call sp_depends. It prints usage and quits if invoked without arguments. Otherwise:

    For each line printed by this proc:

      If @format=null, output is in tabular format similar to sp_depends.
      If @format='drop', output is in isql format.
    

    For example,

      exec sp__depends 'my_rule', 'drop'
    

    will print (not execute) isql scripts that would unbind my_rule from all attached columns and usertypes, and then drop my_rule. By running the generated isql script, you won't encounter this frustrating error:

      Msg 3716, Level 16, State 1:
      The rule 'my_rule' cannot be dropped because it is
          bound to one or more column.
    

    BUGS

    @format='drop' doesn't guarentee the successful dropping of usertype because the usertype may have been used by some tables and procs.

    I didn't pay much attention to the owners of objects since all objects at our site are owned by dbo.

    USAGE

     Proc_name                 Order Parameter
     ------------------------- ----- ------------------------------
     sp__depends                   1 @objname varchar(30) NOT NULL
     sp__depends                   2 @format varchar(30) NOT NULL
     sp__depends                   3 @dont_format char(1) NOT NULL
    

    SAMPLE OUTPUT

     object                         dependant
     ------------------------------ ------------------------------
     sp_procxmode                   sysobjects
     sp_validlang                   syslanguages
     sp_getmessage                  sysusermessages
     sp_getmessage                  sysmessages
     sp_getmessage                  syslanguages
     sp_getmessage                  sp_validlang
     sp_configure                   sysattributes
     sp_configure                   sysdevices
     sp_configure                   sysconfigures
     sp_configure                   syscurconfigs
    

    
    

    sp__diskdevice List disk devices and their basic information

    AUTHOR

    Edward Barlow ( SQL Technologies, inc. )

    DESCRIPTION

    Basic information about disk devices

    USAGE

    sp__diskdevice [@devname]

    @devname: device name you are interested in - defaults to printing all dump devices

    SEE ALSO

    sp__helpdevice, sp__dumpdevice

    SAMPLE OUTPUT

     1> exec sp__diskdevice
     ****** PHYSICAL DISK DEVICES (Mirror info after device name) ******
     Device Name   Physical Name                   size   alloc    free
     ------------- ------------------------------- ------ -------- --------
     datadev       /disk1/sybase10/datadev.dat     20.0MB   20.0MB    0.0MB
     datadev2      /disk1/sybase10/datadev2.dat    19.5MB   17.0MB    2.5MB
     datadev3      /disk1/sybase10/datadev3.dat    10.0MB    4.0MB    6.0MB
     master        d_master                        17.0MB   16.5MB    0.5MB
     sybsecurity   /disk1/sybase10/sybsecurity.da   5.0MB    5.0MB    0.0MB
     sysprocsdev   /disk1/sybase10/sysprocsdev.da  10.0MB   10.0MB    0.0MB
    

    
    

    sp__dumpdevice List dump devices and their basic information

    AUTHOR

    Edward Barlow ( SQL Technologies, inc. )

    DESCRIPTION

    Basic information about dump devices

    USAGE

    sp__dumpdevice [@devname]

    @devname: device name you are inetested in - defaults to printing all dump devices

    SEE ALSO

    sp__helpdevice, sp__diskdevice

    SAMPLE OUTPUT

     1> sp__dumpdevice
     1> exec sp__dumpdevice
     Device Name          Physical Name
     -------------------- --------------------------------------------------
     tapedump1            /dev/rmt4
     tapedump2            /dev/rst0
    

    
    

    sp__find_missing_index Lists potentially missing indexes

    AUTHOR

    Edward Barlow ( SQL Technologies, inc. )

    DESCRIPTION

    Lists potentially missing indexes. Assumes that your foreign key relationships are potential joins and compares keys with actual indexes, listing any keys that do not have associated indexes. This proc will list a key relationship based on fields a,b,c & d if there were no index on a, a&b, a&b&c and so on, assuming that the dba has done his work and would not have an index that was not sufficiently selective (ie. if you decide a is good enough for an index your relationship a,b,c,d should use it in its joins). If this proc does not find the missing index, your model is not sufficiently defined.

    USAGE

    sp__find_missing_index [ @objname ] [ @p1]

    @objectname gives you missing indexes for that object

    @p1 if passed will give full output suitable for programs, but too long for humans to read.

    SAMPLE OUTPUT

     1> exec sp__find_missing_index
     No Indexes Found in Current Database
    

    
    

    sp__flowchart List execution flow of procedures

    AUTHOR

    Edward Barlow ( SQL Technologies, inc. )

    DESCRIPTION

    List flow of procedures in current db. Goes 6 levels. Does this from sysdepends. See also sp__read_write for another good optimization tool. I think the output is readable but...

    USAGE

    sp__flowchart [ @objname ] [ @p1]

    @objectname gives you flow only from 1 object

    @p1 if passed will give full 30 character by 6 level output (otherwise the output is

    parsed to 15 characters wide strings x 4 levels to fit on the screen).

    SAMPLE OUTPUT

     1> exec sp__flowchart
     level 1            level 2            level 3            level 4
     ------------------ ------------------ ------------------ -----------
     ap_insert_server   ap_insert_system
    

    
    

    sp__grep grep for Sybase SQL Server System 10

    AUTHOR

    Andrew Zanevsky, AZ Databases, Inc.

    DESCRIPTION: Searches syscomments table in the current database for occurences of a combination of strings. Correctly handles cases when a substring begins in one row of syscomments and continues in the next.

    USAGE

    sp__grep @parameter [,@case]

    @parameter describes the search:

    string1 {operation1 string2} {operation2 string 3} ... where - stringN is a string of characters enclosed in curly brackets not longer than 80 characters. Brackets may be omitted if stringN does not

    contain spaces or characters: +,-,&; operationN is one of the characters: +,-,&.

    Parameter is interpreted as follows:

    1.Combine the list of all objects where string1 occurs.

    2.If there is no more operations in the parameter, then display the list and stop. Otherwise continue.

    3.If the next operation is + then add to the list all objects where the next string occurs; else if the next operation is - then delete from the list all objects where the next string occurs; else if the next operation is & then delete from the list all objects where the next string does not occur (leave in the list only those objects where the next string occurs);

    4.Goto step 2.

    Parameter may be up to 255 characters long & may not contain <Line Feed> characters. Please note that operations are applied in the order they are used in the parameter string (left to right). There is no other priority of executing them. Every operation is applied to the list combined as a result of all previous operations.

    Number of spaces between words of a string matters in a search (e.g. "select *" is not equal to "select *"). Short or frequently used strings (such as "select") may produce a long result set.

    - @case: i = insensitive / s = sensitive (default).

    SAMPLE OUTPUT

    list all objects where string 'employee' occurs;

    sp__grep employee

    list all objects where string 'employee' occurs in any case (upper, lower, or mixed), such as 'EMPLOYEE', 'Employee', 'employee', etc.;

    sp__grep employee, i

    list all objects where either both strings 'employee' and 'salary' occur or string 'department' occurs, and string 'trigger' does not occur;

    sp__grep 'employee&salary+department-trigger'

    list all objects where string "select FirstName + LastName" occurs;

    sp__grep '{select FirstName + LastName}'
    

    sp__groupprotect Synopsis of protection stuff.

    AUTHOR

    Edward Barlow ( SQL Technologies, inc. )

    DESCRIPTION

    Gives number of select / update /delete /insert / revoke / and execute grants for each group and type of object. Useful to summarize what groups have priviliges to do what.

    SEE ALSO

    sp__helprotect, sp__objprotect

    SAMPLE OUTPUT

      1> sp__groupprotect
      2> go
      type grp             tot  sel    upd  del  ins  rev  exe
      ---- --------------- ---- ------ ---- ---- ---- ---- ----
      P    g_mon6          27   0      0    0    0    0    0
      P    public          27   0      0    0    0    0    9
      R    g_mon6          6    0      0    0    0    0    0
      R    g_monitor       6    0      0    0    0    0    0
      S    g_mon6          57   0      0    0    0    0    0
      S    g_monitor       57   0      0    0    0    0    0
      S    public          57   16     0    0    0    0    0
      U    g_mon6          33   0      0    0    0    0    0
      U    g_monitor       33   0      0    0    0    0    0
      U    public          33   11     0    0    0    0    0
      V    g_mon6          3    0      0    0    0    0    0
      V    g_monitor       3    0      0    0    0    0    0
      V    public          3    0      0    0    0    0    0
    

    
    

    sp__help Modified sp_help

    AUTHOR

    Edward Barlow ( SQL Technologies, inc. )

    DESCRIPTION

    Pretty version of sp_help. sp_help scrolls off screen and is ugly. List objects in current database (if @objname undefined) or list table columns (if @objname defined). The proc is slow....

    USAGE

    sp_help [@objname]

    if @objname is defined, will list information about specific object if that object exists. If the object doesnt exist, it will try to print all objects that contain the string fragment @object. EXAMPLE

    sp__help "pmt_" Info about all objects with "pmt_" in the name

    sp__help server Info about table server (with column listing)

    SEE ALSO

    sp__help calls the procedures sp__helpcolumn and sp__helpindex when an object is passed as a parameter.

    SAMPLE OUTPUT

        1> exec sp__help
         Name                 Owner                Object_type
         -------------------- -------------------- -----------------
         alerts               dbo                  user table
         audit_trail          dbo                  user table
         comn_database        dbo                  user table
         comn_dumpdevices     dbo                  user table
         comn_syscolumns      dbo                  user table
    

         comn_sysdevices      dbo                  user table
         comn_sysindexes      dbo                  user table
         comn_syslocks        dbo                  user table
    

        1> sp__help authors
         Name                 Owner                Object_type
         -------------------- -------------------- ----------------
         authors              dbo user             table
    

        table name    insert trigger  update trigger  delete trigger
        ------------- --------------- --------------- ---------------
        authors       authors_ins     ...........     ............
    

        Column_name   Type            Nulls Default_name    Rule_name
        ------------- --------------- ----- --------------- --------
        au_id         id 0
        au_lname      varchar(40)     0
        au_fname      varchar(40)     0
        phone         char(12)        0      phonedflt
        address       varchar(40)     1
    

        INDEX KEY c = clustered     u = unique
                  a = allow dup row s = suspect
    

        Table Name           Index Name  c u a s List of Index Keys
        -------------------- ----------- - - - - ------------------
        authors              auidind     Y Y     au_id
    

    
    

    sp__helpcolumn List columns for table / database

    AUTHOR

    Edward Barlow ( SQL Technologies, inc. )

    DESCRIPTION

    List columns for given table

    USAGE

    sp__helpcolumn @objname , @p1

    @objname can be any valid table or view. If null returns all columns.

    @p1 if set will not reformat columns

    note the columns are sorted by column id if an object is selected and by column name if not.

    SEE ALSO

    sp__collist

    SAMPLE OUTPUT

    1> sp__helpcolumn server

    1> exec sp__helpcolumn

     Column name       Type         I   Null Dflt Rule Table
     ----------------- ------------ --- ---- ---- ---- --------------------
     action            char(18)       0 Yes            audit_trail
     allow_null        tinyint        0 Yes            comn_syscolumns
     allow_updates     smallint       0 Yes            server_configures
     attribute         char(30)       0 Yes            schedule_history
     attribute         varchar(127)   0 Yes            schedule_attributes
     audit_trail       tinyint        0 No             personal_preferences
     benchmark         int            0 Yes            server_statistics
     benchmark         int            0 Yes            summary_statistics
     ...
    

    
    

    sp__helpdb shows database information in a nice format

    AUTHOR

    Edward Barlow ( SQL Technologies, inc. )

    DESCRIPTION

    Show standard database information in a concise format

    SEE ALSO

    The following procedures are especially useful while creating new databases and attempting to perform optimal allocation of space: sp__helpdb, sp__helpdbdev, sp__helpdevice

    USAGE

    sp_helpdb shows information about all databases

    sp__helpdb [ @dbname ] prints specific information about given database.

    SAMPLE OUTPUT

     1> sp__helpdb statsdb
     name       size_data  size_log owner
     ---------- ---------- -------- ----------
     statsdb    7.000000   2.000000 sa
    

     Database Name Device Name Size Usage
     --------------- --------------- --------------------
     statsdb data3 2.000000 data only
     statsdb datadevice 2.000000 data only
     statsdb datadevice 3.000000 data only
     statsdb log 2.000000 log only
     1> sp__helpdb
     1> exec sp__helpdb
     key   description             key   description
     ---   -----------             ---   -----------
     si    select into/bulkcopy    ro    read only
     tl    trunc. log on chkpt     do    dbo use only
     cr    no chkpt on recovery    su    single user
     cl    crashed during load     ab    abort tran
     ds    database suspect
    

     ****** DATABASE CONFIGURATION *******
      database           data log   owner  si tl cr cl ds ro do su ab
      ------------------ ---- ----- ------ -- -- -- -- -- -- -- -- --
      master (1)             5 N/A  sa
      mis (7)               15    6 sa
      model (3)              2 N/A  sa
      pubs2 (8)              4    2 sa
      statsdb (6)           10 N/A  sa          Y
      sybsecurity (5)        5 N/A  sa          Y
      sybsystemprocs (4)    14    3 sa       Y  Y
      tempdb (2)             7 N/A  sa       Y  Y
    

      total space used total data total log
      ---------------- ---------- ----------
           73.00            62.00      11.00
    

    
    

    sp__helpdbdev Show how database uses devices

    AUTHOR

    Edward Barlow ( SQL Technologies, inc. )

    DESCRIPTION

    Show device to database breakdown. Which devices are used by database.

    SEE ALSO

    The following procedures are especially useful while creating new databases and attempting to perform optimal allocation of space: sp__helpdb, sp__helpdevice, sp__helpdbdev.

    USAGE

    sp__helpdbdev [ @dbname ]

    if @dbname parameter is passed, only show information for given database

    SAMPLE OUTPUT

     1> sp__helpdbdev
    

     Database Name Device Name Size Usage
     --------------- --------------- -------------------- ---------------
     master master 2.000000 data and log
     master master 2.000000 data and log
     master master 3.000000 data and log
     migrator datadevice 10.000000 data and log
     model master 2.000000 data and log
     pubs2 master 2.000000 data and log
     tempdb master 2.000000 data and log
    

     1> exec sp__helpdbdev
      Database Name   Device Name     Size                 Usage
      --------------- --------------- -------------------- ----