# This is a shell archive. Remove anything before this line,
# then unpack it by saving it in a file and typing "sh file".
#
# Wrapped by Jacob L. Salomon <dajls@clancy> on Wed May 16 13:19:09 2001
#
# This archive contains:
# partitions.txt partition-list.sh
# partitions.sh
#
# Modification/access file times will be preserved.
# Error checking via wc(1) will be performed.
# Error checking via sum(1) will be performed.
LANG=""; export LANG
PATH=/bin:/usr/bin:/usr/sbin:/usr/ccs/bin:$PATH; export PATH
if sum -r </dev/null >/dev/null 2>&1
then
sumopt='-r'
else
sumopt=''
fi
echo x - partitions.txt
sed 's/^@//' >partitions.txt <<'@EOF'
Title: partitions.txt
Purpose: To make operations aware of of tablespaces that have too
many extents. (What constitutes "too many" is arbitrary.)
Scripts:
partition-list.sh
partitions.sh
monitor-extents.cron
Requirements:
partition-list.sh must be in the use's PATH
beautify-unl.sh must be in the user's $PATH
Author: Jacob Salomon
JakeSalomon@netscape.net
2001-02-10
Motivation:
----------
In 1999, I submitted to the IIUG software Archive a pair of scripts to
determine which users are tying up which tables, either with locks or
dirty reads that block exclusive access. (who-lock.sh and who-access.sh,
for the terminally curious. ;-) In the process of providing background
on how to map a table to a partitions number, I wrote a [relatively]
simple script, called partitions.sh. I wrote this off at the time,
doubting it's true usefulness to my purpose at the time.
About a week after I had submitted this, I received a literal wake-up
call from the operators at my employer's computer room. Some jobs were
getting "Device full" errors while inserting rows into a table. There
was indeed a dbspace too full (97 pages free) to receive new data. The
mystery was that the table in question seemed to have no connection with
that dbspace. Repeated runs of "dbschema -s" did not display the name
of that dbspace. (As you can see, the application programmers had been
at work already on this. That they tried it several times, always
reaching the same useless result, shows that they indeed think like
programmers as well. ;-) I then recalled that curious little script
I had found no real use for and realized that it could identify all
partitions associated with a table. Running it, I was able to show that
there was a detached index for that table in that full dbspace. (Why
dbschema did not display it is beyond the scope of this history.) This
inspired me to expand the scope of partitions.sh.
I have been tinkering with it for a while; I did not feel it "ready for
prime time" until it could accept parameters. After all, the user may
not want this analysis performed on all the databases in the server, or
on all tables in the database. There were also issues of displaying
information about the sysmaster/sysutils databases and the system
catalogs of any database in the display.
In practice, I tend to use it for monitoring the extent count for
tables. It could also be used to periodically monitor growth of a table
during a large transaction.
RELEASE 3 NOTES:
---------------
This is release 3 of partitions.sh. The internal logic that captures
basic database and table information has been moved to another script
file called partition-list.sh. Partitions.sh calls partition-list.sh
and uses the output thereof to fill a temp table. It then joins the
contents of the temp table with a pair of SMI tables to produce the
familiar output.
Change in Output:
One small change in the output: In previous versions, a line describing
a non-partitioned table displayed a fragment type of T1. It now
displays S (for Single).
Why did I move the basic data gathering function to another script?
In a word: Reusability. The same logic would be used in a couple of
other scripts and a thorough treatment of this process called for a
large script. The target scripts are who-locks.sh (as opposed to
who-lock.sh) and table-iostats.sh.
Parameter changes:
I felt that the option conventions were confusing; there was no logic
to which options are upper-case and which are lower-case. Now, options
with no parameters are in upper-case; options that require a parameter
are lowercase. Thus, the following upper-case options are now usable:
-H : Help
-V : (New) Display the version number and release date of the script.
-C : Display catalog information.
-S : Display information on SMI tables.
-T : Display information on temp tables.
One exception to this: The -D option still precedes a list of DBspaces.
Note that all temp tables are now lumped together in a single category.
There is no longer any attempt to distinguish user-created temp tables
from system-generated HASHTEMP and SORTTEMP tables. That logic never
worked correctly anyway.
Note that -h, -c, -s are still accepted for the same purposes but
generate a warning. I plan to remove these options at some time in the
future.
Error Reporting:
The major motivation for the logic change was the realization that some
tables were being missed. This is because to access the table name for
the partition a detached index I must open each database. Well, unless
I am running as informix, I don't always have the necessary privilege to
open each database. The previous release of partitions.sh ignored
error messages from dbaccess because they did not match the pattern for
lines of query output.
Beginning with release 3, the error output of the database queries, as
well as some queries themselves, are saved to a /tmp file. If any
errors are detected, partitions.sh now issues a warning and gives the
name of the error file. If no errors were detected, it removes the
file at the end of execution.
Script: partition-list.sh
-------------------------
-------------------------
The shell-script partition-list.sh was written to provide a set of
basic information about tables. Its output is designed to be used by
partitions.sh and some other planned utility scripts.
Parameters: partition-list.sh accepts parameters: A list of databases.
If this list is omitted it unloads for all databases. Whether or not a
database list was provided, it generates UNL data for temp tables.
Environment Variables:
- BOGUS_TABID: This is a numeric value to use as the tabid when
generating information on temp tables. If this variable has not
been set (and exported) then partition-list.sh uses the absurdly
large value 2147483646. This is (coincidentally, of course) the
value set and exported by partitions.sh before invoking partition-
list.sh .
This script produces a list partition information for all database
tables in the current server plus temp tables. Its parameters are
simply a space-separated list of databases. If this is omitted, it will
display partition information for all the tables in all the databases
in the server. The output is a raw, unbeautified UNLOAD file.
Each line of the output contains:
- Database Name.
- Table Name.
- The table-id (tabid) as the table in known within systables.
Note that for temp tables, the tabid is an absurd number: (2^31 - 2)
- The table's partition number. For a partitioned (fragmented) table,
this is the partition number of a fragment.
- The fragment type:
S: Table with a Single partition
T: Table fragment of a partitioned table
I: Index fragment of a detached index
t: Fragment of a Temp table
- Index name for a detached index fragment.
For a table fragment of a partitioned table, this is blank.
For a single-fragment table this is (NA).
For a temp fragment, this is (temp).
It also produces some output to standard error, provided that there
were some errors. This error file contains the names of the databases
it attempted to attach to and all the SQL statements that were
attempted, successful or not. If any errors were detected, it prints
this information after the regular output. Note that partitions.sh
exploits this when it invokes partition-list.sh: It captures the stdout
and stderr output of partition-list.sh into separate files and appends
its own error messages to the captured stderr file.
The tabid in the above display calls for some explanation:
- Since partitions.sh does not display the tabid, why does partition-
list.sh bother displaying it?
- If all temp tables have the same integer for their tabid, won't that
cause confusion?
The answer lies in the way partition-list.sh and partitions.sh use the
tabid column:
- Internally, partition-list.sh uses tabid to relate tables, as
described in systables, with table fragments as described in
sysfragments.
- Partitions.sh uses the tabid to identify system catalogs (where
tabid < 100). Otherwise, the exact value of tabid is of no use to
partitions.sh. Therefore, the duplicated value used for all temp
tables will not confuse anything. For that matter, across different
databases, many tables may have the same tabid - the same value
repeated once in each database. This also does not cause any
confusion to the utilities.
Because neither script uses the tabid for identification (partnum is
better for identification) this duplication will not upset any schemes.
Script: partitions.sh
---------------------
---------------------
partitions.sh produces a listing that describes some basic storage
statistics about tables. This includes:
- The database:table name of the tablespace
- A flag to indicate if this is
(a) A whole table.
(b) A table fragment.
(c) A detached index fragment.
(d) A temp table of fragment thereof.
- The hex partition number of the tblspace. A fragmented table (or
one with a detached index) may have many tblspaces, each with its
own partitions number.
- The name of the dbspace containing the tblspace. (You could infer it
from the first three hex digits of the partition number but that's
too much work!)
- The number of rows in the table, or that fragment thereof. (This is
always 0 for index fragments.)
- The number of extents in the tblspace.
- Number of used pages in the tblspaces
- Number of pages in the tblspace.
- If the line describes a detached index, the name of the index.
This is quite a mouthful. In fact, the output almost never fits on a
standard 80-character line. Fortunately, since most of are using
X-windows clients or emulators (like NetTerm), it is easy enough to
arrange a sufficiently wide window to view the display without wrapping
lines. For the following example, I deliberately chose tables with
short names but it will serve as a fair demo.
------------------------------------------------------------------------
$ partitions.sh -d imm -t cluster -t dept_cls,rtrn_ean
|TableName |FT|Partition |DBspace |NRows |Xtns|NPused|NPtotl|Index |
|imm:cluster |S |0x0060005B|imm_dbs | 1309| 1| 96| 160|(NA) |
|imm:dept_cls|S |0x00600126|imm_dbs | 0| 1| 4| 8|(NA) |
|imm:rtrn_ean|T |0x00700008|imm_frag1|274411| 3| 30947| 35000|(NA) |
|imm:rtrn_ean|T |0x00800008|imm_frag2|274587| 3| 30969| 35000|(NA) |
|imm:rtrn_ean|I |0x0060004C|imm_dbs | 0| 18| 10029| 10752| 568_79|
|imm:rtrn_ean|I |0x00A00004|indexdbs | 0| 5| 9186| 9431|idx_ean|
|imm:rtrn_ean|I |0x00A00003|indexdbs | 0| 6| 3789| 3981|ix_ean1 |
|imm:rtrn_ean|I |0x00A00005|indexdbs | 0| 4| 3049| 3059|ix_ean2 |
|imm:rtrn_ean|I |0x00A00006|indexdbs | 0| 11| 19617| 19801|ix_ean3|
|imm:rtrn_ean|I |0x00A00008|indexdbs | 0| 7| 8487| 8512|ix_ean4 |
------------------------------------------------------------------------
Note that I have specified parameters. Had I run the command with no
parameters, it would have displayed this information for every database
[except sysmaster and sysutils] and every table within those databases
[except the system catalogs]. There are command options that override
those exceptions.
Note also that the numeric index name, which is obviously a system-
generated index to support a constraint, is displayed with its embedded
blank space.
Come to think of it, this is as good a time as any to look at the help
text, which you invoke with the -H option:
-----------------------------------------------------------------------
$ partitions.sh -H
partitions.sh queries the SMI tables and the system catalogs of each
database, producing columns of data in the form:
|TableName|FT|Partition|DBspace|NRows|Xtns|NPused|NPtotl|Index|
TableName: The qualified database:table name
PT: Fragment Type: with the following possible values:
S: Single table partition i.e. non-fragmented table
T: Table fragment - part of a fragmented table
I: Index fragment - detached index or fragment thereof
t: Temp table (or fragment thereof)
Partition: The hex partition number of the tablespace.
DBSpace: Name of the dbspace containing this partition. (Easier
than figuring it out from inspecting the partition
number, isn't it?)
NRows: Number of rows in the tablespace (0 for index fragment).
Xtns: Number of extents in the tablespace
NPused: Number of pages in the tablespace containing something
NPtot: Number of pages allocated to the tablespace
IndexName: For an index fragment, the name of the index.
Usage:
partitions.sh [-H] [-C] [-S] [-T]
[-d database] [-t table] [-D DBspace]
@.... (If you want to see the rest, invoke the -H option yourself)
-----------------------------------------------------------------------
Command Options:
---------------
Some explanation of the options is in order. (The -h should have been
self-explanetory, I think.) Note that the shell script uses the
getopts command to parse the options and parameters. For those
familiar with the getopts command, some minor quirks of the command
line parameters become reasonable.
-H : Display the full help text. Since it will scroll past any user's
screen it might be a good idea to pipe this to more or pg.
-C : By default, partitions.sh deliberately omits the system catalogs
by adding a sub-clause to the WHERE clause: "and tabid >= 100".
This is because, while the DBA has the capability to repair a
condition on the system catalogs (e.g. fragmented syscolumns),
the freedom to shut the system down and fix them is seldom
granted under common 24X7 business needs. Some of these issues
would require a complete unload and very controlled reload of the
entire database, with the system out of commission for the
duration.
On the other hand, some situations might very well allow for the
needed down time the DBA might as well know if the catalogs are
fractured. Or, there is the curiosity factor; the DBA just wants
to know. In this case, the user enters:
partitions.sh -c
and the catalogs will have their disk vitals revealed along with
all other tables.
By the way, if you use the -t option to specify which tables to
display, you can specify any catalogs and omit the -c option.
-S : When a Dynamic Server instance is initialized, it creates two
databases immediately: sysmaster and sysutils. (Of course *you*
know that. ;-) For the purposes of counting rows, extents, or
pages, these databases are seldom of interest to the DBA. Hence,
the default is for partitions.sh to deliberately skip these in its
main loop. If you are interested in row counts and such for these
databases, enter:
partitions.sh -S
and you are on your way. Of course, it will still skip the catalog
tables of these databases. If you want to include these databases
and the catalogs of all displayed databases, use:
partitions.sh -SC
As with the -C (catalogs) option, if you explicitly name the
desired databases and include sysmaster among the parameters, you
do not need to specify the -S option; naming a database overrides
the default omission. An example of this appears in the explana-
tion of the -d option.
-T : Temp tables. By default, partitions.sh does not display information
about temp tables. After all, what could you do about it if it's
overly fragmented? Still, there are times when you have a query
that keeps aborting with space messages and you would like to
monitor the growth of temp tables to help identify the problem.
If the problem is a user-created temp table whose name you know,
you could get more pointed results by specifying its name in the
-t option, described below.
-V : Display version number and exit, ignoring other parameters
-d : By default, partitions.sh displays information on all databases
but sysmaster and sysutils. Perhaps you are not interested in the
fragmentation counts in all databases; only one database of
interest. Solution: specify a database with the -d option.
partitions.sh -d stores7
If you want the catalogs in stores7 as well, you can request:
partitions.sh -C -d stores7
If you want to specify a few databases, the syntax is:
partitions.sh -d stores7,sysutils
Alternatively, you can specify separate -d commands for each
database or even mix the two formats::
partitions.sh -d stores7,sysutils -d hrdata
Note the comma-separated list. No gaps are allowed; the list of
databases must be specified as a single string. This is a side
effect of the getopts command, as mentioned in the introduction to
this section.
-t : By default, partitions.sh displays the disk vitals on all tables
in the specified (or all) databases in the server. Well, as with
the databases, you don't always want all the tables. Sometimes
you just want vitals on a chosen few. You would then enter a
database and a table name, as in:
partitions.sh -d stores7 -t customer,items,sysprocedures
To nobody's surprise, this will display the disk vitals on the
above three tables in the database named stores7.
If you know that some table name appears in no other database in
the server, you can omit the -d option, as in:
partitions.sh -t customer,items,sysprocedures
On the other hand, if these table names appear in more than one
database (pretend you are in the Informix DSA training class ;-)
then you will get the information displayed for every named table
in each database in the server. For example:
-t Example:
$ partitions.sh -t systables,sysindexes
|TableName |FT|Partition |DBspace |NR |Xtns|NPu|NPt|Index|
|bndb:sysindexes |T1|0x00900004|book_dbs| 70| 2| 9| 16|(NA) |
|bndb:systables |T1|0x00900002|book_dbs| 52| 1| 6| 8|(NA) |
|bndb_matrix:sysindexes|T1|0x00600010|imm_dbs | 75| 2| 9| 16|(NA) |
|bndb_matrix:systables |T1|0x0060000E|imm_dbs | 58| 2| 9| 16|(NA) |
|book:sysindexes |T1|0x0060008C|imm_dbs | 88| 2| 10| 16|(NA) |
|book:systables |T1|0x0060008A|imm_dbs | 57| 2| 9| 16|(NA) |
|imm:sysindexes |T1|0x006000E2|imm_dbs |440| 6| 46| 48|(NA) |
|imm:systables |T1|0x006000E0|imm_dbs |289| 4| 30| 32|(NA) |
|onpload:sysindexes |T1|0x00100065|rootdbs | 59| 1| 8| 8|(NA) |
|onpload:systables |T1|0x0010003F|rootdbs | 50| 1| 6| 8|(NA) |
(Note that in the interest of fitting the above in the line, I
have shaved some characters off the heading columns. NR was
really NRows, NPu was NPused, NPt was NPtotl.)
The above shows how partitions.sh displayed disk vitals for the
above named tables in all databases in the system because I didn't
bother to name the database.
Note that if you specify -t and name a temp table, you will get
the stats on that temp table; you do NOT need to specify the -T
option.
-D : Be default, partitions.sh display information on tables regardless
of what dbspace they are in. However, when you notice that a
dbspace is getting rather full, you may want to know what's taking
up all that space in there. It is difficult to scan the regular
listing for a specific dbspace. Note that grep will not cut it;
if you want to scan for space3 and you have another dbspace named
space33, you will get the same confusion.
The soluition is to specify the dbspace in question on the command
line: partitions.sh -D space3
Practical Applications of partitions.sh
---------------------------------------
---------------------------------------
Early Warning Alert
-------------------
Although partitions.sh lets you eyeball the state of the system, it is
difficult to see which tables have too many extents because you are
likely to get so much data whizzing past your screen. You would like
to see only those tables that have more than, say 41 extents.
The basic solution is to run it through an awk script:
partitions.sh | awk -F'|' '$7 > 41 {print $2,$7,$5}'
On my test system, this one-liner produced:
TableName Xtns DBspace
bndb:division_ean 106 book_dbs
book:book_error 56 book_dbs
book:delta_item 94 imm_dbs
book:isbn_source 122 imm_dbs
book:isbn_source 78 book_dbs
book:isbn_source 146 imm_dbs
book:isbn_source 122 book_dbs
imm:error_order_wkbk 42 imm_dbs
imm:tmp_model_changes 52 imm_dbs
(Note the 4 entries for table book:isbn_source; these are extent stats
on the 4 partitions of a fragmented table.)
If the operator ran this script every day (or other regular interval)
then you would never get caught by surprise when a table cannot grow
any more because it has too many extents already. The problem is that
this system relies on a human being who is often up to his **** in
alligators. Nah, not too reliable for an early-warning.
The next idea is to put the above command line in a crontab for user
informix. Weeell... Not so fast, there! When cron starts up a command,
it logs in as that user but does not execute the .profile. This means
the command starts up with no useful PATH and the Informix environment
variables similarly not set. Hence the command, as shown above, could
not possibly run in a cron job. Besides, who will see the output? It
needs to be mailed to someone.
Looks discouraging, eh?
Note the qualifying phrase "as shown above"; it could be used, but with
a bit of preparation. It would need to wrapped in a script that first
sets environment variables, then runs that command line. Of course,
once I am wrapping it in a script, I can elaborate on that awk script;
it need no longer be a one-liner. Furthermore, I can e-mail the output
to anyone I choose, usually a "dba" mail alias.
Here is a template for a monitor-extents.cron shell script. I assume
that it is the crontab directory for user informix, but this has little
bearing on the script.
Notes about the awk command as used here:
o The -F option sets a field separator
o The -v option sets an initial value for a variable inside
the awk script.
Deeper explanations are beyond the scope of this document.
Note also that I am redirecting the output to a temp file. The initial
design of this template piped the awk output into mailx. It may happen
that there are no overextended tables in your system. In that case, the
recipients of e-mail will receive a message with a scary message line
but no message. On the other hand (I'm using a lot of hands in this
document ;) if I redirect to a file, I can check if any output was
generated by partitions.sh before sending the mail. (Of course, I need
to filter out the heading line that the awk one-liner allowed through.)
------------------------------- CUT HERE -------------------------------
#!/usr/bin/ksh
# monitor-extents.cron - Early warning for overextended tables
#
INFORMIXDIR=/????/?????????
INFORMIXSERVER=??????
ONCONFIG=onconfig.????
PATH=${PATH}:$INFORMIXDIR/bin
PATH=${PATH}:?wherever partitions.sh and beautify-unl.sh are?
export INFORMIXDIR INFORMIXSERVER ONCONFIG PATH
MAIL_LIST="list of e-mail addr to receive warning"
MAIL_SUBJ="Warning: Fractured tables on server $INFORMIXSERVER"
TOO_MANY=?? # Number of extents we feel like calling too "many"
TEMP_FILE=/tmp/partitions.out.$$
partitions.sh |
awk -F'|' -vcutoff=$TOO_MANY '
/TableName/ {next} # Heading line - skip it
$7 > cutoff {
printf("Table %s has %3d extents in dbspace %s\n",
$2, $7, $5)
}
' >$TEMP_FILE
#
# Send mail only if $TEMP_FILE has something.
#
if [ -s $TEMP_FILE ]
then
mailx -s"$MAIL_SUBJ" $MAIL_LIST <$TEMP_FILE
fi
rm $TEMP_FILE
------------------------------- CUT HERE -------------------------------
@EOF
set `sum $sumopt <partitions.txt`; if test $1 -ne 44524
then
echo ERROR: partitions.txt checksum is $1 should be 44524
fi
set `wc -lwc <partitions.txt`
if test $1$2$3 != 519364724312
then
echo ERROR: wc results of partitions.txt are $* should be 519 3647 24312
fi
touch -m 0516131901 partitions.txt
touch -a 0516130801 partitions.txt
chmod 644 partitions.txt
echo x - partition-list.sh
cat >partition-list.sh <<'@EOF'
#!/usr/bin/ksh
# partition-list.sh - list of partitions, table names and, when appro-
# priate, index names.
#
# This script is was written as a preliminary function to be used by
# partitions.sh and who-locks.sh to simplify their logic.
#
# ********************* IMPORTANT NOTE ******************************
# This script accesses the system catalogs of each database in the
# server. If the user running the script does have connect privelege on
# any database, that database will be skipped in the output. An
# attempt at a work-around was to:
# o chown informix:infomrix ptlist.sh # Set the script ownership to
# # informix:informix
# o chmod ug+s ptlist.sh # Set the SET-UID/GID flags
#
# This does not help. For now, deal with it or run the script while
# logged in as (or SU'd to) user informix.
# *******************************************************************
#
# Function: dblist() - Output a list of all database in the
# current Informix server instance
#
dblist()
{
std_err=/dev/null
dbaccess sysmaster - 2>${std_err} <<%% | sed -e '/^$/d'
output to pipe "cat" without headings
select name from sysdatabases order by 1;
%%
}
# Function: is_logging() - Tell me if a datbase is set for logging
# Parameter:
# - The name of a database
# Returns:
# - 0 (true) if named database has logging,
# - 1 (false) if database has no logging
# - 2 (ENOEXIST) if database does not exist
#
is_logging()
{
dbparam_name=${1:-"no_param"} # (In case parameter was omitted)
# Note: The sed commands in the following pipeline are to eliminate
# blanks and empty lines.
#
lstatus=$(
dbaccess sysmaster - 2>/dev/null <<%% | sed -e s/' '*// -e /^$/d
output to pipe "cat" without headings
select is_logging
from sysdatabases
where name = "$dbparam_name";
%%
)
# echo "lstatus($dbparam_name)" = "<$lstatus>"
case "$lstatus" in
1) rval=0 ;; # Status 1 (yes) => shell-truth value of 0 (True)
0) rval=1 ;; # Status 0 (no) => shell-truth value of 1 (False)
*) rval=2 ;; # Only error assumed: database does not exist
esac
return $rval # Not echo; return value as an exit status
}
#
# Main execution starts here.
# Scheme: Build a set of queries, one query for each database, that
# lists all partitions in the database. Then run one more for temp
# tables, which appear only in sysmaster. Note that within each
# database, a union is also required because the partition number of
# fragmented table appears in sysfragments, not in systables.
#
# These flag masks indicate something is a temp or hash table
#
SYSTEMP=\'0x20\'
USRTEMP=\'0x40\'
HASHTEMP=\'0x80\'
# The environment variable BOGUS_TABID might have been defined &
# exported before this script mwas called. Try to use it but be
# prepared in case it has not been exported.
#
temp_tabid=${BOGUS_TABID:=2147483646} # Bogus tabid for temp tables
# Set up some file names to use for output at various stages.
#
Q_Out=/tmp/QO.$$.out
Q_Template=/tmp/QT.$$.sql # Name of template file to build queries
U_Total=/tmp/UT.$$.unl # Combine unl files here
>${U_Total} # Create that total-unload file
ISOLATION="set isolation to dirty read;" # (If I may use this clause)
# Now, for which databases do I hunt up information?
#
if [ $# -eq 0 ] # If caller supplied no parameters
then
DBLIST=$(dblist) # Get partitions info on all databases
else # If caller told me which databases
DBLIST="$*" # then hunt up only those
fi
# Create a template SQL script file to be used for each database
# that I will search.
#
cat >$Q_Template <<%%
%ISO
unload to %UNL
select "%DB" database, -- Name of database as a constant
tabname,
tabid, -- Include this to identify catalogs
partnum, -- Partition number - no hex now
"S" ft, -- Fragment type is *S*ingle table
"(NA)" index -- No index name in table partition
from %DB:systables
where partnum != 0 -- Avoid views, synonyms, bogus tables
union
select "%DB" database, -- For fragmented tables
tabname,
t.tabid, -- Include this to identify catalogs
partn, -- Fragmented table: Partition num is here
fragtype ft, -- Fragment type
indexname index -- Here the index name may be relevant
from %DB:systables t, %DB:sysfragments f
where t.tabid = f.tabid
;
%%
#
# Note 1: In the dbaccess command below (in the FOR loop) I need to go
# into each database itself. I cannot connect to sysmaster and access
# the catalogs of all other databases for a cute reason: Those databases
# may not have the same logging mode as sysmaster. It is [currently]
# an Informix restricttion that if I am connected to a logged database,
# I cannot query an unlogged database. (Error -568: Cannot reference an
# external database without logging.)
#
# Note 2: Whether or not to have dbaccess echo back the sql commands as
# it executes them: By using the variable DASH_E, I can decide whether
# or not to echo them. Is you can see from the setting of DASH_E, I
# have opted to echo.
#
DASH_E="-e" # dbaccess -e option to echo SQL
#DASH_E="" # If SQL-echo is not desired
for dbname in $DBLIST
do
if is_logging $dbname # If the database has logging
then
ISOLATION_CLAUSE="$ISOLATION"
else # No logging on this database
ISOLATION_CLAUSE="--${ISOLATION}" # Avoid "no transactions" error
fi
U_Plop=/tmp/UNL-${dbname}.$$.unl # Unload-file for database query
>${U_Plop} # Make sure UNL file exists for rm command
echo Database: ${dbname} >>$Q_Out
sed -e s@%DB@${dbname}@g \
-e s@%UNL@${U_Plop}@g \
-e s@%ISO@"${ISOLATION_CLAUSE}"@ ${Q_Template} |
dbaccess $DASH_E $dbname - >>$Q_Out 2>&1 # (Debugging version has -e)
cat $U_Plop >>$U_Total # Combine UNL files
rm $U_Plop # Remove single-table UNL file
done
#
# The above queries have handled all tables listed in someone's system
# catalog. The job is not complete because temp tables do not appear
# there; their info can be found only in the SMI tables. The following
# query fills that gap.
#
U_Plop=/tmp/UNL-temps.$$.unl # Unload file for temps query
>$U_Plop # Make sure it exists for remove
echo Database: \(temps\) >>$Q_Out
# I have included tabid is only to locate catalogs by a tabid < 100.
# Create a bogus tabid for all temp tables to avoid mistaking a temp
# table for a catalog
#
dbaccess $DASH_E sysmaster <<%% >>$Q_Out 2>&1
set isolation to dirty read;
unload to $U_Plop
select tn.dbsname,
tn.tabname,
$temp_tabid tabid, -- Bogus tabid for temp tables
tn.partnum,
"t" ft, -- Temp table fragment type
"(temp)" index -- No index fragments for temp tables
from sysmaster:systabnames tn,
sysmaster:systabinfo ti
where tn.partnum = ti.ti_partnum
and ( (sysmaster:bitval(ti_flags,$SYSTEMP) = 1)
or (sysmaster:bitval(ti_flags,$USRTEMP) = 1)
or (sysmaster:bitval(ti_flags,$HASHTEMP) = 1)
)
%%
cat $U_Plop >>$U_Total # Combine with other UNL output
rm $U_Plop # Get rid of this as part of unl build
cat $U_Total # The main output of this program
# Now clean up after ourselves
#
rm $Q_Template
rm $U_Total
grep -q [0-9]: $Q_Out # Just find IF there was an error message
all_ok=$? # Grab copy of the exit code
if [ $all_ok -eq 0 ] # If an error message appears in the file
then # display the entire error file
echo 1>&2 Warning: Database access errors detected
cat $Q_Out 1>&2
fi
rm $Q_Out
@EOF
set `sum $sumopt <partition-list.sh`; if test $1 -ne 7945
then
echo ERROR: partition-list.sh checksum is $1 should be 7945
fi
set `wc -lwc <partition-list.sh`
if test $1$2$3 != 21012017898
then
echo ERROR: wc results of partition-list.sh are $* should be 210 1201 7898
fi
touch -m 0515171201 partition-list.sh
touch -a 0515182801 partition-list.sh
chmod 755 partition-list.sh
echo x - partitions.sh
cat >partitions.sh <<'@EOF'
#!/usr/bin/ksh
# partitions.sh
# Script to list partition numbers for all tables and table-fragments
# in the current IDS system. It displays this and much more information
# about each table, as shown in the help text below (function usage()).
#
# It then beautifies the output into equally spaced columns for
# improved readability.
# Requirements: The following scripts must be present in order for
# partitions.sh to run:
# o partition-list.sh which generates a raw list of basic partitions
# information.
# o beautify-unl.sh which creates neat output.
#
# Author: Jacob Salomon
# JakeSalomon@netscape.net
# Date: 1999/09/02
# Release: 1.1
#
# Release History:
# Release 1.0: 1999-09-02
# Initial Release
# Release 2.0: 2000-02-14
# - Cleaned up typos in help text and clarified it
# - Moved the main setup logic out of parse_params() into its own
# function: process_params().
# - Added option -D to monitor scan tablespaces in only specified
# dbspaces.
# - Added options -T ond -H to enable display of user temps and
# system HASHTEMP tables.
# Release 3.0 2001-05-01
# - Added the ability to specify the multiple databases, dbspaces,
# tables with additional options.
# e.g. "-d stores,movies -d xyz" is now equivalent to
# "-d stores,movies,xyz"
# - Changed the core logic to call new script: partition-list.sh
# in order to use that same script as core logic for who-locks.sh.
# - As part of the core-logic change, partitions.sh now detects
# errors in database access and will not remove the file
# containing error messages. In fact, it will generate a message
# to stderr pointing to the error file. (In fact, the discovery
# of these her-to-fore unnoticed errors was the motivation behind
# the revamp of the core logic.)
# - Changed the output logic as well: Now uses a simple unload
# rather than the previous SQL output piped to an awk script.
# - Dropped the -H parameter for hashtemp and sorttemp tables; all
# temp table now get lumped into same category.
# - The -H option is now for help. Similarly, the non-parameter
# options (-c => -C; -t => -T) have all been switched to upper-
# case, though the lower case versions are still accepted with a
# warning message.
#-----------------------------------------------------------------------
#
# Some quickie conventions and functions:
#
program=$(basename $0)
VERSION=3.0
VERSION_DATE=2001-05-15
YES=0
NO=1
BOGUS_TABID=2147483646 # (2^31) - 2; Bogus tabid for temp tables
export BOGUS_TABID # To be used by partition-list.sh
# Function: errmsg():
# Display the supplied strings, with a time stamp, to whatever file
# is currently referenced by the environment variable DB_FILE.
# That is normally /dev/null but can be changed to debug this script.
#
#DB_FILE=/dev/tty
#DB_FILE=/tmp/$0.$$.err
#DB_FILE=/tmp/PL.err
DB_FILE=/dev/null
errmsg() # For debugging - set DB_FILE if needed
{
echo $(date)::$* >>$DB_FILE
}
# Function: list2inlist() Convert a comma-separated list to a quoted,
# comma-separated in-list, suitable for an IN clause in in SQL.
# Parameter: The comma-separated list
#
list2inlist()
{
list=$1 # Initial parameter - comma-separated list
in_list="" # Output - initially null string
set $(echo $list | tr , " ") # Separate into numbered shell params.
in_list=\"$1\" # First item in original list
shift
while [ $# -gt 0 ] # Loop while list has remaining entries
do # Quote and append commas
in_list=${in_list}," \"$1\""
shift
done
in_list="("${in_list}")" # Surround quoted list with parentheses
echo $in_list # Output result
}
#
usage() # For -H option: Give some help text
{
cat <<%%
$program queries the SMI tables and the system catalogs of each
database, producing columns of data in the form:
|TableName|FT|Partition|DBspace|NRows|Xtns|NPused|NPtotl|Index|
TableName: The qualified database:table name
PT: Fragment Type: with the following possible values:
S: Single table partition i.e. non-fragmented table
T: Table fragment - part of a fragmented table
I: Index fragment - detached index or fragment thereof
t: Temp table (or fragment thereof)
Partition: The hex partition number of the tablespace.
DBSpace: Name of the dbspace containing this partition. (Easier
than figuring it out from inspecting the partition
number, isn't it?)
NRows: Number of rows in the tablespace (0 for index fragment).
Xtns: Number of extents in the tablespace
NPused: Number of pages in the tablespace containing something
NPtot: Number of pages allocated to the tablespace
IndexName: For an index fragment, the name of the index.
Usage:
$program [-H] [-C] [-S] [-T] [-V]
[-d database] [-t table] [-D DBspace]
Note that the -d, -t, and -D options accept multiple arguments, If you
specify more than one argument to an option, separate the arguments
with commas, not spaces. e.g. -d stores7,myowndb . They also accept
multiple arguments if the option is repeated.
eg. -d stores -d max,min
will display information about dstsbses stores, max, and min.
If you separate with spaces (e.g. -d stores7 myowndb) the second
parameter is likely to be ignored and may even result in an error
message.
-H : Display this help text and exit. Ignore all other options
-h : (Compatibility for the -h option. Please use -H to request the help
page.)
-C : Include system catalogs in the listing.
Default: Omit them, since it is very difficult to defragment system
catalogs anyway.
-c : (Compatibility for the -C option. Please use -C to see catalogs.)
-S : Include partitions in the sysmaster and sysutils databases.
Default: Omit partitions in sysmaster and sysutils.
-s : (Compatibility for the -s option. Please use -S to include SMI
tables.)
-T : Include temp tables: those associated with a database (explicitly
created by a user) as well as temp tables in the bogus databases
HASHTEMP and SORTTEMP (created by the optimizer when joining tables
and sorting data).
Default: Omit these because we are seldom concerned about their
growth.
-V : Display version number and exit, ignoring other parameters
-d : Display data only on partitions belonging to tables in the
specified database. You can explicitly specify sysmaster or
sysutils here without having to use the -s option.
Example: To specify more than one databases, use:
$program -d stores7,sysmaster
Default: All databases (except sysmaster and sysutils).
Note: Specifying a database automatically enables display of
sysmaster and sysutils
-t : Display data only on partitions belonging to the specified table.
Example: To specify more than one table, use:
$program -d table1,table2..
You may specify the name of a temp table with this option without
specifying -T . It is a good idea (but not absolutely
necessary) to specify a database (-d) when using this option This
is in order to avoid extraneous listings when multiple databases
have tables with the same names.
Default: Display data on all tables [in the specified database]
(except the catalogs, of course. See the -C option for that).
Note: Specifying a table name automatically enables display of
catalogs and temp tables.
-D : Display data only on partitions in the specified dbspace[s].
Example: To display data only on tablespaces that reside in this
couple of dbspaces, use:
$program -d dbspace1,dbspace2..
Default: TableSpaces in all dbspaces.
Note: Specifying a DBspace enables display of temp and hash tables
within that DBspace.
%%
}
#
parse_params() # Scan the command line for options and set
{ # internal flags & variables accordingly
# Set default flag values. These may change during parse
#
help_flag=$NO # Indicate false
cat_flag=$NO # Exclude system catalogs
smi_flag=$NO # Exclude SMI and sysutils databases
temp_flag=$NO # Exclude temp, SORTTEMP, and HASHTEMP tables
version_flag=$NO # Do not simpley display version & exit
db_list="" # Initially null list of database - ALL databases
tab_list="" # Initially null table list - ALL tables
sp_list="" # Initially no DBspace list - ALL DBspaces
# The D, d and t options expect parameters
opt_string="HhCcSsTVd:t:D:"
while getopts $opt_string cparm
do
errmsg Option $cparm with param: "$OPTARG" and OPTIND: $OPTIND
case $cparm in
H|h)
help_flag=$YES # Set to YES
if [ $cparm = h ]
then
echo 1>&2 Please use -H to request the help page.
fi
;;
C|c)
cat_flag=$YES # Indicate user wants to include
if [ $cparm = c ]
then
echo 1>&2 Please use -C to specify catalog information.
fi
;; # system catalogs
S|s)
smi_flag=$YES # Indicate user wants to include SMI tables
if [ $cparm = s ]
then
echo 1>&2 Please use -S to specify SMI/sysutil information
fi
;;
T)
temp_flag=$YES # Include all manner of temp tables
;;
V)
version_flag=$YES # User is simply looking for version info
;;
d) # Capture/build list of databases
if [ "$db_list" = "" ] # If database list is still empty
then
db_list="$OPTARG" # this param is whole list of databases
else # If this is additional database parameter
db_list=${db_list},"$OPTARG" # Append new list with comma
fi
smi_flag=$YES # At least don't exclude SMI databases;
# we want to avoid a contradiction
;;
#
# parse_params() - Continued
#
t) # Capture/build list of tables
if [ "$tab_list" = "" ] # If table list is still empty
then
tab_list="$OPTARG" # this param is whole list of tables
else # If this is additional tables parameter
tab_list=${tab_list},"$OPTARG" # Append new list with comma
fi
cat_flag=$YES # Don't automatically exclude catalogs;
temp_flag=$YES # Don't automatically exclude temp tables
smi_flag=$YES # we want to avoid a contradiction
;;
D) # Capture/build list of DBspaces
if [ "$sp_list" = "" ] # If DBspace list is still empty
then
sp_list="$OPTARG" # this param is whole list of tables
else # If this is additional DBspaces parameter
sp_list=${sp_list},"$OPTARG" # Append new list with comma
fi
temp_flag=$YES # Specifying a DBspace means ANY table,
cat_flag=$YES # including temps, catalogs and SMI
smi_flag=$YES
;;
*)
echo Unrecognized option: $cparm
usage
;;
esac
done # Finished flagging for parameters
} # End function parse_params()
#
process_params() # With parameter settings analyzed, set up com-
{ # ponents of SQL, temp files
#--------------------------------------------------------------------
# Option: -H (Display help text & get out)
#
if [ $help_flag -eq $YES ] # Did user ask for syntax help?
then
usage
exit 0
fi # End -H
#--------------------------------------------------------------------
# Option: -V (Display version info & get out)
#
if [ $version_flag -eq $YES ] # Did user ask for version info?
then
echo $program Release ${VERSION}\; Release date: $VERSION_DATE
exit 0
fi # End -V
#--------------------------------------------------------------------
# Option: -S (Include SMI-sysmaster and sysutils databsases)
#
# By default, omit sysmaster and sysutils from the analysis. There
# are some reasons to override it:
# - User specified -S
# - User specified a database, table or dbspace
#
# This section handles the [non-]specification of databases by
# initializing, commenting-out, or building a WHERE clause to go into
# an SMI query that retrieves the names of databases.
#
SMI_CLAUSE="where name not in (\"sysmaster\", \"sysutils\")"
# If user wants them comment out the exclusion clause
#
if [ $smi_flag -eq $YES ]
then
SMI_CLAUSE="--"${SMI_CLAUSE} # by prepending the --
fi # End -s
#--------------------------------------------------------------------
# Option: -d (Specifying one or more databases)
#
# Note use of the same variable - SMI_CLAUSE for the database list.
# This is because if user specified a database list, it must automa-
# tically allow the SMI databases. It was safer and cleaner coding to
# use the same variable.
#
if [ -n "$db_list" ] # On the other hand, if user named databases
then # we need to change the where clause
SMI_CLAUSE="where name in "$(list2inlist $db_list)
errmsg $SMI_CLAUSE
fi # End -d
#--------------------------------------------------------------------
#
# process_params() - Continued
#
#--------------------------------------------------------------------
# Option: -C (Include system catalogs)
#
# By default, we exclude system catalogs from this listing. This is
# because if a catalog is overextended, there's not much we can do
# about it anyway. But the user may want it anyway. Also, the user
# may have specified a catalog in the command line. In either case, I
# do NOT wish to exclude catalogs. So:
#
CATALOGS_CLAUSE="and tl.tabid >= 100" # This excludes catalogs
if [ $cat_flag -eq $YES -o -n "$tab_list" ] # but if user wants them
then # then comment out the
CATALOGS_CLAUSE="-- "${CATALOGS_CLAUSE} # exclusion clause
fi # End -C
#--------------------------------------------------------------------
# Option: -t (Specifying one or more table names)
# Assumption: The user has already specified a database name. If not,
# then I will simply specify a table name. But if multiple
# databases have a table with this name, I will display
# the information for each database's table of this name.
#
# Of course, by default, all tables (except catalogs) are fair game.
#
TABS_CLAUSE="-- (no table list)" # Start as a non-contributor to SQL
if [ -n "$tab_list" ] # If user specified a list of tables
then # convert list to SQL-style In-list
TABS_CLAUSE="and tl.tabname in "$(list2inlist $tab_list)
fi # End -t
#--------------------------------------------------------------------
# Option: -D (Specifying one or more DBspaces)
#
DBSP_CLAUSE="-- (No DBspace list)" # Start as non-contributory clause
if [ -n "$sp_list" ]
then
DBSP_CLAUSE="and ds.name in "$(list2inlist $sp_list)
fi # End -D
#--------------------------------------------------------------------
# Option: -T (Include temp tables located in databases)
#
# Default is to exclude temp tables from the output:
#
TEMP_CLAUSE="and tl.part_type != \"t\" "
# However, if the situation says no not exclude them, comment that out
#
if [ $temp_flag -eq $YES ] # We need to allow temp tables
then
TEMP_CLAUSE="-- "${TEMP_CLAUSE} # so comment out the exclusion
fi # End -T
#--------------------------------------------------------------------
} # End function process_params()
#
# Practical program execution begins here
# --------------------------------------
TLUNL=/tmp/TL.$$.unl # Output file for partition-list.sh
TLERR=/tmp/TL.$$.err
PLUNL=/tmp/PL.$$.unl # Output file for the querty below
PLOUT=/tmp/PL.$$.out # Output file with left bar and heading
PL_ERROR=/tmp/partitions.$$.err
parse_params $* # Run basic analysis of command-line options
process_params # Set up everything else from parameters
if [ -n "$db_list" ] # If user supplied a list of databases
then # then turn the list into an blank-separated,
udb_list=$(echo $db_list | tr , " ") # unquoted list of databases
else # User did not list [a] database[s]
# generate my own database list
udb_list=$(
dbaccess sysmaster - 2>>${DB_FILE} <<%%
output to pipe "cat" without headings
select name from sysdatabases
$SMI_CLAUSE
;
%%
)
fi
# Whatever happened above, udb_list contains a list of database names,
# blank separated and no quotes. Now use this list a few ways:
#
# First, generate a raw partition list; I will load it later
#
partition-list.sh $udb_list >$TLUNL 2>$TLERR
# Use the same bogus tabid for temps as used by partition-list.sh
#
temp_tabid=$BOGUS_TABID # Fake tabid to use for temp tables
#dbaccess sysmaster - 2>>$PL_ERROR <<%%
dbaccess -e sysmaster - 2>>$PL_ERROR 1>&2 <<%% #(Debugging version)
create temp table t_table_list
(
database char(64),
tabname char(64),
tabid integer,
partnum integer,
part_type char(1),
indexname char(64)
) with no log;
-- First, recapture table-list info
load from $TLUNL insert into t_table_list ;
-- Next, get info about the temp table itself into the table
insert into t_table_list
select tn.dbsname,
tn.tabname,
$temp_tabid tabid, -- Bogus tabid for temp tables
tn.partnum,
"t" ft, -- Temp table fragment type
"(temp)" index -- No index fragments for temp tables
from sysmaster:systabnames tn,
sysmaster:systabinfo ti
where tn.partnum = ti.ti_partnum
and tn.dbsname = "sysmaster"
and tn.tabname = "t_table_list"
;
-- Now run the main query:
unload to $PLUNL
select unique -- Maybe multiple copies of this are running
trim(tl.database) || ":" || trim(tl.tabname) table,
tl.part_type, -- S, T, I, or t
hex(tl.partnum) partition, -- Hex partition number
ds.name dbspace, -- Name of dbspace
ti.ti_nrows, -- Row count
ti.ti_nextns, -- Extent count
ti.ti_npused, -- Pages used
ti.ti_nptotal, -- Pages allocated
tl.indexname -- (where applicable)
from systabinfo ti,
sysdbspaces ds,
t_table_list tl
where ti.ti_partnum = tl.partnum -- Join systabinfo <=> table list
and ds.dbsnum = trunc(tl.partnum / 1048576) -- Join on dbspace num
$CATALOGS_CLAUSE
$TABS_CLAUSE
$TEMP_CLAUSE
$DBSP_CLAUSE
order by table, part_type desc, indexname, partition
;
%%
#
# (main) - Continued
#
# Now we have all the data we wanted. Unreadable but it's there. Now
# let's make it a bit more readable:
#
HEADING="|" # Start heading line with left bar
for col_head in TableName PT Partition DBspace \
NRows Xtns NPused NPtotl Index
do
HEADING=${HEADING}${col_head}"|"
done
echo $HEADING >$PLOUT # Final output starts with heading row
sed -e 's/^/|/' $PLUNL >>$PLOUT # Prepend vertical bar to each line
beautify-unl.sh $PLOUT
rm $TLUNL
rm $PLUNL
rm $PLOUT
cat $PL_ERROR >> $TLERR # Consolidate error output/message files
rm $PL_ERROR # and get rid of one original message file
grep -q [0-9]: $TLERR # Just find IF there was an error message
found_err=$? # Grab copy of the exit code
if [ $found_err -eq $YES ] # If an error message appears in the file
then # display the entire error file
echo 1>&2 Warning: Database access errors detected
echo 1>&2 See file $TLERR
else
rm $TLERR
fi
@EOF
set `sum $sumopt <partitions.sh`; if test $1 -ne 5175
then
echo ERROR: partitions.sh checksum is $1 should be 5175
fi
set `wc -lwc <partitions.sh`
if test $1$2$3 != 511279220081
then
echo ERROR: wc results of partitions.sh are $* should be 511 2792 20081
fi
touch -m 0515181301 partitions.sh
touch -a 0515182801 partitions.sh
chmod 755 partitions.sh