AFS Reporting Database

To see what is general in what is particular, and what is permanent in what is transitory, is the aim of scientific thought.

Alfred North Whitehead

Warning

These scripts are orphaned. Although I believe they are still useful, I no longer use AFS and am no longer maintaining these scripts. If you would like to pick up maintenance of them, please feel free. Contact me if you would like this page to redirect to its new home.

Description

At Stanford, we used to load all the meta-information about our AFS cell into an Oracle database for reporting purposes and to allow for ad hoc SQL queries. (Stanford still did this when I left in 2014, but was using PostgreSQL instead.) We found this very useful for more complex reports, easy queries without adding load to the AFS volservers or the VLDB, and checks for such problems as off-line volumes.

For a complete description of the AFS reporting database and how we used it when I worked on it, see the white paper on the topic. This page has the software that we used to populate, query, and report on the contents of the database. I believe Stanford still uses a version of this software.

afsdb-load can be run at whatever interval that you wish; we run it nightly. It queries the volservers in your cell for volume information and bulk-loads that information into the database using Oracle's sqlldr utility. It also dumps the mount point database and loads it into the Oracle database for reporting purposes.

afsdb-query is a simple interface for querying the database that takes a query on the command line. It's useful for people who don't want to run sqlplus and for running under remctld to allow remote queries of the database in a secure manner.

afsdb-reports is the reporting script that we run nightly. It provides a framework for easily adding new reports to an array at the top of the script and e-mails the results of all of the reports when run. Even if you don't use this script, it is a good reference for the sorts of queries that we found useful at Stanford.

Finally, all of these scripts use the Stanford::LSDB::AFSDB Perl module, which is just a simple wrapper around DBI and DBD::Oracle that takes the database password from a disk file, knows the correct connection string, sets some DBI options, and provides a simple interface to sqlldr. This module (and its parent Stanford::LSDB module) are provided as a reference but will definitely need to be modified for your local environment.

The original concept, the initial database schema, and all of the initial scripts for loading and querying the data in Sybase were developed by Neil Crellin. Further refinements of the scripts were done by Xueshan Feng. Jonathan Pilat and I converted the database to Oracle and rewrote and expanded the supporting scripts and the number of reports significantly in the process. I also added new reports based on suggestions from Xueshan Feng, Jonathan Pilat, and Alice Ohgi.

Be aware that the change summary for these scripts reflects a later version that uses PostgreSQL instead of Oracle. That version of these scripts was never packaged for general distribution.

Requirements

afsdb-load, afsdb-query, and afsdb-reports are written in Perl and require at least Perl 5.005. They all use the Stanford::LSDB::AFSDB module below, which itself depends on DBI and DBD::Oracle. afsdb-load also requires the Date::Parse module (part of the TimeDate distribution on CPAN), uses two sqlldr configuration files that are provided below, and requires a data file that contains a list of all of the AFS servers in your cell. It also always expects to find a mount point database to load into the Oracle database, but you can disable that if you're not using our mount point tracking system.

All of the scripts are currently written for Oracle, but nothing they do is particularly complex. It should be fairly straightforward to change the Perl module to use DBD::Sybase instead, switch from sqlldr to bcp, and use Sybase (or make similar changes for the SQL database of your choice with a bulk data load utility). Note that many of the queries that we've found useful use sub-selects, so if you're looking at using MySQL, make sure that you have a verion recent enough to have sub-select support.

Stanford::LSDB needs an installation of Oracle that includes the sqlldr command-line utility.

All of the scripts and the Stanford::LSDB and Stanford::LSDB::AFSDB modules have various paths at the top of the scripts that will need to be changed for your local setup based on where your AFS and Oracle command-line utilities are located, where you keep data files about AFS, where you put the sqlldr configuration files, and so forth. Please also change the e-mail address in afsdb-reports before using that script; Stanford doesn't want to get reports about your AFS cell!

Download

The programs:

Perl module for database connections:

Database DDL:

• AFS reporting database DDL

sqlldr control files:

• Volume information
• Mount point information

Documentation:

• afsdb-load documentation and changes
• afsdb-query documentation and changes
• afsdb-reports documentation and changes
• Stanford::LSDB documentation
• Stanford::LSDB::AFSDB documentation
• Stanford::LSDB change log

License

Copyright 1998, 1999, 2001, 2002, 2004, 2005 The Board of Trustees of the Leland Stanford Junior University

These programs are free software; you may redistribute them and/or modify them under the same terms as Perl itself. This means that you may choose between the two licenses that Perl is released under: the GNU GPL and the Artistic License. Please see your Perl distribution for the details and copies of the licenses.

The database DDL is covered by the following license:

Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.

The sqlldr configuration files are too obvious to be covered by copyright. You may consider them to be in the public domain.