# 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 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 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 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 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