Ecotools Case Study Database Monitoring at BNP

Ecotools Case Study : Database Monitoring at BNP Paribas Dennis Adams BNP Paribas

What’s in a name : Paribas Capital Markets n Now part of BNP Paribas n Investment Banking n € Euro-Denominated Bonds. n Equities, Bonds and Derivatives Trading Systems in most major financial centres n Infrastructure in territories and central London Data Centre. n Finance and Reporting in Paris.

Databases and OS Types in Production Tokyo, Singapore, Hong Kong, Paris, London, New York

Our Responsibility: Data Management Group n Managing DBMS Servers n Based in London, remote Support to territories (out-of-hours callouts !) n Liaise with London Operations 24 h/day n Other Teams n Systems Management - hardware & OS n Networks - LAN & WAN n Application Teams - Application Support

Objectives when purchasing Ecotools n Monitoring of Sybase DBMS n detail down to SQL statement n Ingres as an additional requirement n Reliable n WAN based n Event Alerting n Links to TNG for Central Alerting n Extract trend data for capacity planning

How we use Ecotools GUI Monitored Agent Machines Command Line Control Files for Scenarios Alerts Ecotools Repository

How we use Ecotools n Single Central Solaris Console (V 6. 2. 1) n Continuous running 24 X 7 n Minimal use of the Ecotools GUI n Scenarios controlled from ECOCLI n start/stop UNIX shell scripts. n Alerts picked up by “tail” of ecotools log n summary on daily in-house web page

Configuring Domains n Domain = Logical grouping of Servers n Unit of “discovery” within Ecotools n Monitor “All Servers” together

Configuring Domains n Group by Business Unit / DBMS Type / Territory ? n Lots of small Domains ? n speed up “discovery” n Few no of large Domains ? n easier to get to individual machines n COMPROMISE: DOMAINS OF MAX 25 n ORGANISED BY APPLICATION TYPE (ALMOST)

Creating Scenarios n Scenario = Basic Collection Task n One or more agents (cache hit, log size) n One or more machines (“All Servers”) n Time Interval (“ 10 Minutes”) n Either using the GUI or from. CTL file n CREATE USING GUI, SAVE AS A CTL TEMPLATE. . . SED/AWK n START FROM UNIX SHELL

Managing Scenario Control Files n Lots of Small Control files ? n One UNIX impf process per scenario n a single machine can have 15 “request”s n 80 *15 impf processes on console (!!!) n ps -ef | more n Few no of Large Control files ? n Unix VI ctl file when new machine added. n Lots of machines per ctl file makes restart a problem

Managing Control Files n EVERY MACHINE HAS IT’S OWN CONTROL FILES (MAXIMUM OF 3) n Use SED string substitution to created from standard Templates n Ecotools Startup logic. . . n. For file in *. ctl ndo n ecocli n eco: run $file ndone

GUI Stability Issues n GUI “hangs” when Alerts arrive. n Determined by ECOCLI_BLINKALARM n ECOCLI_ALARMINTERVAL =1200 default = 2*Cli. Log. Interval n The GUI Crashes ! n Corrupts master imdb - lose all data n Better in version 6. 2. 1 (latest patches) n SWITCH OFF ECOCLI_BLINKALARM, USE UNIX SCRIPT TO TAIL LOG FILE

Managing Ecotools “Views” n View = Repository for performance data n consists of indexed/ flat file directory n Need X months data - capacity planning n Can keep views going for several weeks n but “imdb” = 12 MB, . datalog = 1. 8 GB. n CREATE NEW VIEW EVERY MONTH n SET “NO OF DATA POINTS=1500” IN CONTROL FILES (OR LOSE DATA)

Trend Data for Capacity Planning n Several Month’s data required n Start a new GUI for every single view ? ? n SOLUTION : extract data to CSV files for reading into database n There is no supported utility to do this. n SHELL SCRIPTS - GREP / SED / AWK n UNSUPPORTED HACK !

Unicenter/TNG Integration n In-house implementation calling KSH script from Ecotools Scenario Language n Can be implemented as “Mgr. Action” or “Agt. Action” n Mgr. Action = run on Solaris Console n GMRPY_CDBSTOP errors - not scalable n Agt. Action = run on Agent Machine n NOT YET PROVEN

Summary : Ecotools Experience so far n Evaluation of version 4. n in 1997 n Monitoring of Euro changeover in 1998 n Y 2 K tracking and Reporting 1999 n Version 6. 1 chosen as Y 2 K version n Soon replaced by 6. 2. 1 with patches. n Currently Monitoring nearly 80 Production Systems from London.

Looking at Ecotools V. 7 n Looks impressive on paper n SQL server storing collected data n Genuine 3 -layer architecture n Better User interface n Concerns: n Support for Legacy Operating Systems n Dec UNIX versions n Ability to create own “shell script” agents. n Earlier Versions of Sybase

Ecotools Future at BNP Paribas n Evaluate V 7 for monitoring NT/Sybase Production Systems. n Need UNIX shell agents for NT console n Objective: move to centralised NT console - eventually n Keyword: STABILITY

Success Stories n Sybase Settlement System - Tokyo n Reserved log space low n caused by backup server failing and transaction logs not being properly dumped. Restarted backup server n Potential system hang averted n Delayed Settlement = We get Fined

Success Stories n Ingres Equity Derivative Trading - New York n Large Table Approaching Ingres Architectural Limit of 2 GB n Planned outage to remodify table into multiple locations to allow it to grow n Equity Trading Outage averted n Keep the Traders Trading

Success Stories n Ingres Forex Trading - Paris n Ingres Error Log reported potential UNIX Filesystem Error one evening n Alerted Paris Unix Team, who decided to ignore the warning n System Crashed overnight n But at least we could say “I told you so”

Final Proof n Accepted within DMG Group n Integrated with our way of working n We are now a more Pro-Active Team n Looks good on the CV, provided you call it. . . n E-COTOOLS n Lunch Time ?