Fight Table Proliferation

If your users and developers have resource privileges on the database, you will find that you are constantly having to play "table janitor." It's funny how tables named temp_XXX are never really temporary and how tables that were going to hang around for "just a coupla' days" become institutionalized as part of your database.

What we need is a tool that will allow the DBA to monitor the tables in a database, know what should be there and what shouldn't be there, and notify the DBA when certain tables should be removed.

This is a situation that is quite common to UNIX system administrators. Any time that you have a community of users who are utilizing common resources, you have to deal with resource hogs. While many users are quite diligent about taking out their trash, there are many others who are pack rats. "I never saw a table I didn't like," seems to be their motto. Whether or not you are able to enforce some level of control over this situation depends on how much actual leeway you have in removing other people's tables.

You can send e-mail harassing and whining to the users about removing old tables, and you'll probably get fairly decent results. Nobody's out there just to fill up your database. The only problem is that you will be forced to be the police, and frankly, you have better things to do.

The UNIX system administrators solve this problem by having "file eater" daemons that automatically delete old files. The users know that these file eaters are there and they know how to protect their important files, but they have to take affirmative action to do it. The responsibility is on the users, not on the administrator. Here is a method of doing the same thing with OnLine tables.

The basis of this "table warehouse" is a database table that establishes ownership and longevity for all tables in your database. This table is called auth_tables and is in your dba database. It is designed to join with the various systables in each of your user databases, with the join being on two fields, tabname and owner. These fields should be unique within each database and will uniquely identify tables. Create the auth_tables table using this SQL script:

INFORMIX l>cat mk_auth.sq1
create table auth_tables
  (
tabname char(18),
owner char(8),
database char(8),
good_til datetime year to day,
user char(10),
application char(lO)
  );
revoke all on auth_tables from "public";
create index auth_tables_x1 on auth tables(tabname,owner);

The tabname and owner fields are identical to their counterparts in systables. This is where the tables join. The good_til column is a "drop-dead date" for a particular table. When a user creates a table that should remain in the database, the user must register the table information with you. The user field should be the person who takes responsibility for the table, not necessarily the owner in systables. The application should be the name of the application or module that uses the table. If the table is temporary, the user should give you a good_ti1 date at which time the table can be removed. Tables that are permanent should have a date far out in the future, like 3000-12-31. Users should be taught that any table that is not registered in the auth_tables table is subject to immediate deletion after one publication of its table name in a warning e-mail message to everyone.

You may wish to be a little more lenient with unregistered tables. If you can identify the owner of an unregistered table, you may wish to modify the scripts to send them an e-mail message. If a table is not registered, you will not have a good_ti1, a user, or an application field, since you don't have an entry in the auth_tables database. If the table is not registered, you will have to make do with the owner name in systables. If your system does not enforce individual table ownership, the only way that you can notify an anonymous owner or user of a table is by an e-mail posting to all users.

There are several ways that you can initially populate your auth_tables database. If you are just beginning with OnLine, you can develop a system in which all tables that go into the database must be manually registered with you. If you have an existing application, put together a group and do a thorough cleaning of your database, getting rid of all the junk. Then go into the individual systables tables in each database and insert the tabname, owner, and database name into auth_tables. You may have to make several tries at it, but try to get all of the "legal" core tables identified as fully as possible with user, application, and good_ti1 entries as appropriate. The following ACE form oktables should be helpful. Note that this is just a default form on auth_tables with headings added.

INFORMIX 3>cat oktables.per
database dba
screen size 24 by 80
{

Authorized Tables in the Database
tabname            [f000              ]
owner              [f00l    ]
database           [f002    ]
good_ti1           [f003      ]
user               [f004      ]
application        [f005      ]

}
end
tables
auth_tables
attributes
f000 = auth_tables.tabname;
f00l = auth_tables.owner;
f002 = auth_tables.database;
f003 = auth_tables.good_ti1;
f004 = auth_tables.user;
f005 = auth_tables.application;
end

Compile this form and use it to get all of your legitimate forms registered in your auth_tables table. Now the task is to create reports that show you which tables are candidates for execution. Since we are going to kill these tables, I've chosen a good Texas term for them, "outlaws." That's the name of the report script.

INFORMIX 4>cat outlaws
#
if ( $#argv == 0 ) then
set database = "admin" <-—---You need to change this to your default
endif
if ( $# argv == 1 ) then
set database = ${1}
endif
$INFORMIXDIR/bin/isql $database << EOF
SELECT
systables.tabname,
systables.owner,
systables.created,
systables.nrows,
systables.npused * 2048 as bytes

FROM    systables, OUTER dba:auth_tables

WHERE   systables.tabname = dba:auth_tables.tabname AND
        systables.owner   = dba:auth_tables.owner   AND
                            systables.tabid > 99    AND
systables.tabtype = "T" AND
systables.tabname not in (select tabname from
dba:auth_tables where good_til > current)
and systables.tabname not matches "rev_*"
and systables.tabname not matches "access*"
and systables.tabname not matches "usage*"
and systables.tabname not matches "a_*"
and systables tabname not matches "tmp_*"
and systables.tabname not matches "crev*"
ORDER by bytes desc
EOF

This script has a few things that are specifically designed for my database applications that you will need to change to fit your needs. First, you can invoke the script with or without a database name. If it is invoked without a database name, it defaults to my main database, admin. Of course, you'll need to change this. Based upon some of our earlier scripts, you may remember that the npages field in systables is not necessarily an accurate count of real size, but it is close enough. I just want to be able to tell the users in a general way how much space this table takes up. I have this number multiplied by 2048 for a 2K page size. If your system uses 4K, change the script. All of the SQL statements that are connected by AND are required. At the end, you see several statements that are connected by "and." These are optional and are local to my system. In my system, we have many tables that are legitimate but can be identified by the first characters in the name. The rest of the name is dependent upon the process ID that created the tables. These last SQL clauses cause these "wild card" table names to be accepted as legitimate tables. If you want to get fancy, you could put these names in a table and do the exceptions from fragments of names from the table. It's easier the way I did it. The outlaws script gives you a report similar to this:

INFORMIX 50>outlaws
tabname            owner      created         nrows             bytes

ry_invoice_def     rodney     10/06/1993      24600           3149824
zip_xref           dba        03/28/1993      43635           2078720
djcmv3             support    01/08/1994       2474            362496
conn11024          Chris      01/17/1994       2024            188416

One of the drawbacks (or features, depending on whether you wrote it or 1 wrote it) to outlaws is that it must be run for each database. If you wanted to run it once for all of your databases, you could use our old friend find_db_names. Write a short script called find_all_outlaws, and run it with no parameters. The reports will be placed in files called XXXXX.outlaws, where XXXXX is the name of each of your databases.

INFORMIX> cat find_all_outlaws

foreach database ('$INFORMIXLOCALBIN/find_db_names')
echo "'date':  OUTLAWS REPORT for database: $database" >! ${database}.outlaws
$INFORMIXDlRLOCALBIN/outlaws $database >> ${database}.outlaws
endScript