DBA Rule Monitor
From PTAGISWiki
sockeye:/home/ptagdev/ptagis3/dba_chores/Rules/ToDo_DBA_RuleMonitor.txt
Pacific States Marine Fisheries Commission
Database Administration Tasks
Ensure Presence of Ingres 'Rules'
Ver 1.0 04 May 2009
(Last Update: 09:44)
History:
--------- -------------- -------------------------------------------------
29 Mar 08 Doug Clough Creation, at 09:28
04 May 09 Doug Clough Updated 'standards' file to reflect the fact that
'freeze_valid_tbl_rul' has been dropped
INTRODUCTION
Recently a sag in run-time performance of queries in the 'ptagis3' database on 'blueback' pointed out the need
for a systematic procedure to ensure that all intended secondary indexes are in place. DBA_IndexMonitor.pl,
developed to meet this need, has been deployed on 'blueback'. BigBrother has been configured to launch the new
tool and provide an alert if an index is missing. With a 'heads
up' from the index monitor, odds are better that the problem will be addressed before users suffer its
consequences.
But the episode called attention to the more general problem of invisible system components that rarely fail
and give no direct signs of trouble when they do. A
missing index degrades run-time performance, but nothing 'breaks'. Similarly, a
missing rule-fired database procedure would not 'break' the mechanisms intended
to invoke it. Like secondary indexes, the existence of 'rules' is known only to the DBMS server; they are
never referenced in the application code that exercises them.
However, unlike missing indexes - which merely degrade performance - missing rules would mean that
important operations in the database - enforcement of referential
constraints, computation of aggregates - had not been performed. Yet the external processes whose actions
normally invoke the rule-fired procedures might continue
with no indication that anything is wrong.
Of course this issue can be addressed through design. For example incorporating mechanisms to check for
presence of rules, as is done in IDL, or to perform cross-
checks of aggregation results, as is done in FDVL - though FDVL performs these particular operations by
'called' rather than 'rule-fired' database procedures.
If the work to be done by rule-fired procedures is crucial and cannot be performed retroactively, building in
a mechanism to abort the process immediately when necessary rules are not in place would be an important
design requirement. That was the approach taken with IDL.
In contrast, because FDVL was designed to accommodate resubmittal of tagging files, simply resubmitting all
the files that had been processed in absence of a
rule, once the rule had been reinstated, would put things right again. Of course,
the sooner the problem were discovered, the better!
It was for this reason that DBA_IndexMonitor.pl has been cloned to create a similar tool for detecting
missing rules.
OVERVIEW
Keeping an eye on the existence of database 'rules' is easy: Simply query the
'iirules' system catalog in the 'ptagis3' database, and compare the output to a known 'standard' - i.e. output
from the same query generated when all of the required rules are in place. When new rules are created, or
an existing rule is retired or renamed, the same query would be run and its output would be saved as the
new 'standard'.
DBA_RuleMonitor has been cloned from DBA_IndexMonitor to meet this need. Please
refer to [1] for use case scenarios and design requirements. Work done to clone
the new tool is detailed below.
TASK LIST
========================================================================
The following steps were performed on 29 Mar 2008. DPC
========================================================================
Done 1. Clone a ReportWriter report to produce output for the 'standards'
and 'missing rules' files ...
Standards file : tbl_rule_required
Output file : tbl_rule_missing
As 'ptagdev' on 'sockeye' ...
> dba
> cd Rules
OK > cp -p ../Indexes/table_indexes.rw table_rules.rw
OK > vi table_rules.rw
OK > sreport -upittag ptagis3 table_rules.rw
OK > report -upittag ptagis3 table_rules "(dbname=\"$DBNAME\",date=\"$DATE\")" -ftest.out
DPC Sat Mar 29 10:13:58 PST 2008
Done 2. Create soft-link in 'reports' directory so the new report source
can be found easily.
As 'ptagdev' on 'sockeye' ...
> cd /home/ptagdev/ptagis3/source/reports
OK > ln -s /home/ptagdev/ptagis3/dba_chores/Rules/table_rules.rw .
DPC Sat Mar 29 10:14:42 PST 2008
Done 3. Create DBA_RuleMonitor.pl and test from command line on 'sockeye'
As 'ptagdev' on 'sockeye' ...
> dba
> cd Rules
OK > cp /home/ptagdev/ptagis3/dba_chores/ServerManagement/DBA_compareConfigFiles.pl \
DBA_RuleMonitor.pl
OK > vi DBA_RuleMonitor.pl
DPC Sat Mar 29 10:15:06 PST 2008
Done 4. Deploy to PRO on 'sockeye' and test from command line; incorporate into
daily_dba_reports.sh and invoke from 'pittag' cron.
As 'pittag' on 'sockeye' ...
> cd /home/pittag/bin/DBA
OK > cp -p /home/ptagdev/ptagis3/dba_chores/Rules/DBA_RuleMonitor.pl .
OK > ./DBA_RuleMonitor.pl INIT
OK > ls -lt /usr/pit/pittag/log/tbl*
- Properly wrote new 'standard' file
OK > ./DBA_RuleMonitor.pl LOG
- Properly wrote new 'missing' file
OK > ./DBA_RuleMonitor.pl
- Left alone the public 'missing' file; disposed output
to private file on /tmp
DPC Sat Mar 29 10:24:29 PST 2008
> cd /home/pittag/bin
OK > cp -p daily_dba_reports.sh daily_dba_reports.sh_29mar08
OK > vi daily_dba_reports.sh
- Add invocation of 'DBA/DBA_RuleMonitor.pl LOG'
OK > cp cronfile_28mar08 cronfile_29mar08
OK > vi cronfile_29mar08
- Set up to launch daily_dba_reports.sh in a few minutes
OK > crontab cronfile_29mar08
- Did DBA_RuleMonitor.pl run? Yes.
OK > crontab -r
DPC Sat Mar 29 10:32:45 PST 2008
Done 5. Propagate from PRO on 'sockeye' to PRO on 'blueback';
As 'ptagdev' on 'sockeye' ...
> dba
> cd Rules
OK > sreport -upittag blueback::ptagis3 table_rules.rw
DPC Sat Mar 29 10:33:30 PST 2008
As 'pittag' on 'blueback' ...
> cd /home/pittag/bin
OK > cp -p daily_dba_reports.sh daily_dba_reports.sh_29mar08
> ftp sockeye <-- Log in as 'pittag'
OK ftp> cd /home/pittag/bin
OK ftp> lcd /home/pittag/bin
OK ftp> get daily_dba_reports.sh
OK ftp> cd /home/pittag/bin/DBA
OK ftp> lcd /home/pittag/bin/DBA
OK ftp> get DBA_RuleMonitor.pl
ftp> bye
> cd /home/pittag/bin/DBA
OK > chmod 755 DBA_RuleMonitor.pl
OK > ./DBA_RuleMonitor.pl INIT
OK > ls -lt /usr/pit/pittag/log/tbl*
- Did it initialize tbl_rule_required.txt? <-- Yes
> cd /home/pittag/bin
OK > ./daily_dba_reports.sh
OK > ls -lt /usr/pit/pittag/log/tbl*
- Did DBA_RuleMonitor.pl write tbl_rule_missing.txt? <- Yes
DPC Sat Mar 29 10:39:42 PST 2008
========================================================================
The following steps were performed on 30 Mar 2008. DPC
========================================================================
1. Check to be sure that daily_dba_reports.sh ran as usual, and that
DBA_RuleMonitor.pl also executed.
As 'ptagdev' on 'blueback' ...
OK > ls -lt /usr/pit/pittag/log/tbl*
- Did daily_dba_reports.sh run? <-- Yes
- Did DBA_RuleMonitor.pl run? <-- Yes
DPC Sun Mar 30 08:41:14 PST 2008
========================================================================
The following steps were performed on 02 Apr 2008. DPC
========================================================================
Done 1. Enhance DBA_RuleMonitor.pl to archive existing copy of tbl_rule_-
missing.txt
NOTE:
Now that BigBrother launches the monitor every 10-minutes, it
is possible that a missing rule could be detected and replaced
before we've had a chance to see what it was.
DPC Wed Apr 2 16:00:08 PST 2008
As 'ptagdev' on 'sockeye' ...
> dba
> cd Rules
OK > cp -p DBA_RuleMonitor.pl DBA_RuleMonitor.pl_02apr08
OK > vi DBA_RuleMonitor.pl
- Incorporate UTIL_fileOps::checkForExisting()
DPC Wed Apr 2 16:02:32 PST 2008
Done 2. Deploy to PRO on 'sockeye' and test from command line
As 'pittag' on 'sockeye' ...
> cd /home/pittag/bin/DBA
OK > cp -p DBA_RuleMonitor.pl DBA_RuleMonitor.pl_02apr08
OK > cp -p /home/ptagdev/ptagis3/dba_chores/Rules/DBA_RuleMonitor.pl .
OK > ./DBA_RuleMonitor.pl INIT
- Properly wrote new 'standard' file
OK > ./DBA_RuleMonitor.pl LOG
- Properly wrote new 'missing' file
OK > ./DBA_RuleMonitor.pl
- Left alone the public 'missing' file; disposed output
to private file on /tmp
DPC Wed Apr 2 16:06:04 PST 2008
Done 3. Propagate from PRO on 'sockeye' to PRO on 'blueback'
As 'pittag' on 'blueback' ...
> cd /home/pittag/bin/DBA
OK > cp -p DBA_RuleMonitor.pl DBA_RuleMonitor.pl_02apr08
> ftp sockeye <-- Log in as 'pittag'
OK ftp> cd /home/pittag/bin/DBA
OK ftp> lcd /home/pittag/bin/DBA
OK ftp> get DBA_RuleMonitor.pl
ftp> bye
> cd /home/pittag/bin/DBA
OK > chmod 755 DBA_RuleMonitor.pl
Make sure BigBrother can still launch it ...
OK > ls -lt /home/pittag/log/tbl*rule*
- Did DBA_RuleMonitor.pl write tbl_rule_missing.txt? <- Yes
DPC Wed Apr 2 16:30:28 PST 2008
========================================================================
The following steps were performed on 04 May 2009. DPC
========================================================================
Done 1. On 07 Jan 09, when I dropped the 'freeze_valid_tbl_rul' [2], I
neglected to refresh the DBA_RuleMonitor 'standard' file. Take care
of that now.
As 'pittag' on 'blueback' ...
> cd /usr/pit/pittag/log
OK > cp -p tbl_rule_required.txt tbl_rule_required.txt_04may09
> cd /home/pittag/bin/DBA
OK > ./DBA_RuleMonitor.pl INIT
OK > ls -lt /usr/pit/pittag/log/tbl_rule_required.txt
- Did it initialize tbl_rule_required.txt? <-- Yes
> cd /usr/pit/pittag/log
OK > diff tbl_rule_required.txt tbl_rule_required.txt_04may09
- Are the differences as expected? <-- Yes
DPC Mon May 4 08:42:59 PST 2009
REFERENCES
1. ToDo_DBA_IndexMonitor.txt on 'sockeye' at
/home/ptagdev/ptagis3/dba_chores/Indexes
2. ToDo_ReplaceValidTbl_Preparations.txt on 'sockeye' at
/home/ptagdev/ptagis3/source/g2_data_model
|--------|---------|---------|---------|---------|---------|---------|---------|
