#---------------------------------- cut here ---------------------------------- # 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 Thu Nov 16 13:19:09 2000 # # This archive contains: # table-iostats.txt table-iostats.sh # # Modification/access file times will be preserved. # Error checking via wc(1) will be performed. LANG=""; export LANG PATH=/bin:/usr/bin:/usr/sbin:/usr/ccs/bin:$PATH; export PATH echo x - table-iostats.txt cat >table-iostats.txt <<'@EOF' Program: table-iostats Contents: o table-iostats.sh (shell-script) Author: Jacob Salomon JakeSalomon@netscape.net Release: 1.2 Date: 2000-11-16 Motivation: Like most such programs, this was motivated by a problem. The was a performance degradation and it fell to the DBA to determine the cause. One commonly used tool for monitoring system conditions (since no loyal DBA likes to admit his favorite product is faulty) is GLANCE. In this case, it showed disk utilization at 100% of capacity much of the time. Since most disk-I/O activity is on the database, I wanted to know which tables are being hit on so much. Note that the the documented tool for I/O statistics is an onstat command: onstat -g iof yields I/O statistics by chunk. I wanted to know it by table. To including the dbspace would be an extra benefit. After a bit of playing aorund, I came up with the following query: select trim(pt.dbsname) || ":" || trim(pt.tabname) table_name, ds.name dbspace, hex(pt.partnum) partition, isreads, iswrites, (isreads + iswrites) isios, bufreads, bufwrites, (bufreads + bufwrites) bufios, pagreads, pagwrites, (pagreads + pagwrites) pageios from sysmaster:sysptprof pt, sysmaster:sysdbspaces ds, bndb:systables tb where tb.partnum = pt.partnum and ds.dbsnum = trunc(pt.partnum / 1048576) and tb.tabid >= 100 (Note that bndb is the name of one my local databases.) The same information for fragmented tables (and detached indexes) is a bit more complicated. See the script code for more information. The Program: Of course, once I am writing a script to run this query and present the output, it begins to grow additional legs. How about I/O statistics for temp tables, which do not have an entry in the systables (or sys- fragments) catalog of any database? How about for hash and temp tables for which there is no database entry in sysdatabases? The question also arises about what kind of I/O statistics to display: Separate columns for reads and writes or summary data? What about tables that just sit there and have had no I/O activity since the sercer started? The result of all this speculation is the script, table-iostats.sh, with its myriad options. To see all available options, use the -h option for help. ------------------------------------------------------------------------ $ table-iostats.sh -h Usage: table-iostats.sh [-h] [-c] [-s] [-p] [-m] [-z] [-T] [-H] [-d database] [-t table] [-D dbspace] -h : This help text. If included, ignore all other options -c : Include system catalogs in the listing. Default: Omit them. -s : Include partitions in the sysmaster and sysutils databases. Default: Omit sysmaster and sysutils. -p : Display seParate columns for read and write I/O statistics instead of the default: (read+write) statistics in a single column. -m : Display suMmary I/O statistics - (read+write) in a single columns. Normally, this is the default anyway. However, if you have speci- fied -p then you would get the separate columns only. If you specify -mp then table-iostats.sh will display separate read and write columns as well as the summary column. -z : Display data for tables even if no I/O activity has been reported. Default: Skip tblspaces for which the I/O count is 0. -T : Include I/O statistics for user defined Temp tables. Default is to omit temp tables. -H : Include I/O statistics for system defined HASH and SORT tables. Default is to omit hash and temp tables. -d : Specify a database for which to display partitions. You can explicitly specify sysmaster or sysutils here, without using the -s option. To specify multiple databases, use: -d database1,database2.. If you separate with spaces usage will ignore all but the first database. Default: All databases (except sysmaster and sysutils). -t : A list of table names. If specifying more than one table separate them with commas, not spaces. If listing tables, you should also include the database name with the -d option. To specify multiple tables, use: -t table1,table2.. If you separate with spaces usage will ignore all but the first table. Default: All [non-catalog] tables in the specified database(s). -D : Include I/O statistics for only those tables that reside in the specified DBspace. To specify multiple DBspaces, use the same convention as above: -D dbspace1,dbspace2 . Default: Tables in all dbspaces. ------------------------------------------------------------------------ Explanation of Options This section describes the options in greater detail (where appropriate) than the above help text. -h (Rather self explanetory) -c Catalogs. Normally there is not a great deal of constant activity on the system catalogs and little the admin could do about it it there were. For this reason, and in the interest of shortening the output, the default is to deliberately skip system catalogs from this analysis. For those curious about I/O activity on catalogs, as well as situations where thrashing on catalogs is suspected, we use the -c option to add catalog status to the output. -s SMI tables. If catalogs tend not to be examined for I/O statistics, how much less should the DBA be interested in I/O statistics for the SMI tables. Most of the documented SMI tables are views with no entries in sysptprof (ParTition PROFiles) and the remainder are mostly pseudo tables, wherein I/O statistics (if present) are meaningless. There might be some I/O activity in the arc_ tables that support OnArchive but it is unlikely that any I/O activity on these tables could be a significant contributor to a performance hit. This is also the case with tables in the sysutils database, which support the API for other utilities, usually archive programs like OnBar. For this reason, the default is to omit all tables in the SMI and sysutils databases from the list of I/O statistics. But, as with the catalogs, if you want to see them, use the -s option to include these databases in the output. -T Temp tables. Temporary tables are more often short-lived entities. Even with a significant amout of I/O it is difficult to pinpoint a flurry of activity because it may be gone the next time you run the program. But for those times when temp-table activity may be significant (far more frequently than catalogs) you have the -T option to include temp tables in the output. Note that this applies only to temp tables that wer created by an SQL command like SELECT .. INTO TEMP .. or CREATE TEMP TABLE.. Temp tables created by the engine for sorting are handled by the -H option. -H Hash Tables. For the duration of some reasonably large queries with joins and sorts, the engine creates internal temp tables with names like SORTTEMP:th_tmprun_d0feb220 or HASHTEMP:th_overflow_ffffff. These database names do not represent actual databases; they do not have entries in the database tblspace (or sysmaster:sysdatabases). As with user-created temp tables these are usually short-lived, making it difficult to actually track I/O activity. But there are situations - large sorts, complex joins - wherein the I/O activity can be significant. If you suspect this to be the case, then use the -H option with your command. -z Zero-activity rows. Often, there are table that have had no I/O activity, yet have an entry in the SMI table sysptprof. These zero- activity tables would clutter the output of table-iostats.sh; hence the default is to filter them so they will not display. If you want them, use the -z option and you got 'em. -p Separate Format. Be default, the I/O counts displayed are summaries; each column represents the read count plus write count, looking like: |TableName |DB-Space |Partition |ISAM-IO |Buffer-IO|Page-IO| |imm:alpha_s |alpha_dbs|0x00C00019| 262306| 271279| 33| |imm:budget |order_dbs|0x00E00003| 331492| 1680594| 6402| |imm:dept_cls |imm_dbs |0x00600126| 6| 54| 24| |imm:div_inventory|imm_frag1|0x0070000A|42902643| 27461527| 422240| |imm:div_inventory|imm_frag2|0x0080000A|42889837| 27455631| 414704| |imm:div_inventory|indexdbs |0x00A00002|11681526| 21242393| 375719| |imm:div_inventory|indexdbs |0x00A00007|17344773| 21819028| 150468| Should you desire to separate the read and write I/O activity, you can specify the -p (for seParate) option, as in: This will replace those three summary columns into the following 6-columns form: |ISAM-RD |ISAM-WR|Buffer-RD|Buffer-WR|Page-RD|Page-WR| | 262306| 0| 271279| 0| 33| 0| | 331518| 0| 1680778| 0| 6402| 0| | 6| 0| 54| 0| 24| 0| |42103335| 799407| 26023327| 1438637| 197770| 224523| |42090569| 799395| 26014380| 1441572| 190612| 224125| |11681898| 0| 21159769| 84862| 352987| 22807| -m Summary Format. This is the default, as shown with the commentary on the -p option, to display if neither -p nor -m were used. So why is this included as an option? Because some may find it difficult to to add up the read and write columns quickly and would like to see both, the summary and the separated read and write counts. In that case, use both options: -pm and your I/O count columns go up to 9 columns: |ISAM-RD|ISAM-WR|ISAM-IO|Buffer-RD|Buffer-WR|Buffer-IO |Page-RD|Page-WR|Page-IO| (The line segment has been split here in order to display it all.) -d database(s) Display data only for tables that belong to the database specified with this option. The format is: $ table-iostats.sh -d stores,collections Now table-iostats.sh will display information on all regular tables (not temps or catalogs) in these two databases and ignore tables in all other databases in your server. Of course, the -T and -c options can still be applied here: $ table-iostats.sh -Tc -d stores,collections Notes: - The list of databases must be a comma-separated list WITH NO SPACES - the shell must see it as a single string. - You can specify an SMI database without the -s option: $ table-iostats.sh -Tc -d stores,collections,sysutils -t table(s) Display data for only those tables with the specified names. $ table-iostats.sh -d imm -t alpha_s,budget,dept_cls In the above example, I have specified the database and the tables. If I had specified only the tables, as in: $ table-iostats.sh -t alpha_s,budget,dept_cls and several databases have a table named dept_cls then table-iostats would display the I/O activity for the dept_cls table in each database in the server. Notes: - As with the -d database list, the table list is a single string to the shell. The table names are separated with the comma and no spaces are allowed within the string. - Specifying a table [list] allows you to monitor any table - a system catalog, an SMI table, a temp table, a hash table (if you know its name) without specifying the -c -s, -T, or -H options. -D DBspace(s) Display I/O activity data only for tblspaces in the listed dbspace(s). For example, if I want to see what's happening with all tables, including temp tables, that reside in in rootdbs and the temp dbspaces, I would enter: $ table-iostats.sh -D rootdbs,tmp_dbs1,tmp_dbs2 -T Notes: - The same drill applies with the list of dbspaces - a single string, no embedded spaces, items separated by comma. - Unlike with the table list, specifying the dbspaces does not automatically include special tables. It merely restricts the display to these dbspaces. ======================================================================== @EOF set `wc -lwc table-iostats.sh <<'@EOF' #!/usr/bin/ksh # table-iostats.sh - Get I/O information about all tables in all # databases in the current server. By default, # omit sysmaster & sysutils # Script to list I/O statistics for all tables and table-fragments # in the current IDS system. # # This utility querys the SMI database for I/O statistics on tables in # the current server. It also Beautifies the output into equally # spaced columns for readability. # # Author: Jacob Salomon # JakeSalomon@netscape.net # Date: 2000/01/08 # Release: 1.2 # # Change Log: # 1.0 Initial release # 1.1 - Split the parse_params() function; second part is # function process_params() # 1.2 - Minor bug fix, close a quote in a generated SQL that caused a # failure when specifying a table (-t option) # Some quickie setups and debugging function # YES=0 NO=1 program=$(basename $0) # Name some temp files and make sure they exist # IOSTAT_SQL=/tmp/edit-iostats-$$.sql TMP_UNION_SQL=/tmp/table-tempstats-$$.sql HASH_SQL=/tmp/hash-iostats-$$.sql WHOLE_UNL=/tmp/table-iostats-$$.unl WHOLE_SQL=/tmp/table-iostats-$$.sql >$TMP_UNION_SQL >$HASH_SQL # The following flag masks indicate something is a temp or hash table # SYSTEMP=\'0x20\' USRTEMP=\'0x40\' HASHTEMP=\'0x80\' # # For debugging purposes, change DB_FILE as necesssary # DB_FILE=/dev/null #DB_FILE=/dev/tty #DB_FILE=iostats-msg.out errmsg() { echo $(date)::$* >>$DB_FILE } # usage() # For -h option: Give some help text { cat <<%% Usage: $program [-h] [-c] [-s] [-p] [-m] [-z] [-T] [-H] [-d database] [-t table] [-D dbspace] -h : This help text. If included, ignore all other options -c : Include system catalogs in the listing. Default: Omit them. -s : Include partitions in the sysmaster and sysutils databases. Default: Omit sysmaster and sysutils. -p : Display seParate columns for read and write I/O statistics instead of the default: (read+write) stiatistics in a single column. -m : Display suMmary I/O statistics - (read+write) in a single columns. Normally, this is the default anyway. However, if you have speci- fied -p then you would get the separate columns only. If you specify -mp then $program will display separate read and write columns as well as the summary column. -z : Display data for rows even if no I/O activity has been reported. Default: Skip tblspaces for which the I/O count is 0. -T : Include I/O statistics for user defined Temp tables. Default is to omit temp tables. -H : Include I/O statistics for system defined HASH and SORT tables. Default is to omit hash and temp tables. -d : Specify a database for which to display partitions. You can explicitly specify sysmaster or sysutils here, without using the -s option. To specify multiple databases, use: -d database1,database2.. If you separate with spaces $0 will ignore all but the first database. Default: All databases (except sysmaster and sysutils). -t : A list of table names. If specifying more than one database, separate them with commas, not spaces. If listing tables, you should also include a database name with the -d option with a database. To specify multiple tables, use: -t table1,table2.. If you separate with spaces $0 will ignore all but the first table. Default: All [non-catalog] tables in the specified database(s). -D : Include I/O statistics for only those tables that reside in the specified DBspace. %% } # parse_params() # Scan the command line for options and set { # internal falgs & 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 iosum_req=$NO # Assume user omitted request for default summary iorw_flag=$NO # Default: no separate columns for reads & writes temp_flag=$NO # Default: No I/O stats for temp tables hash_flag=$NO # Default: No I/O stats for hash & sort tables zero_flag=$NO # Default: Skip tablespaces with no I/O activity db_list="" # Initially null list of database - ALL databases tab_list="" # Initially null table list - ALL tables sp_list="" # Initially null dbspace list - ALL dbspaces # The d, t and D options expect parameters # opt_string="hcsmpzTHd:t:D:" errmsg "opt_string: " $opt_string while getopts $opt_string cparm do errmsg Option $cparm with param: "$OPTARG" and OPTIND: $OPTIND case $cparm in h) help_flag=$YES # Set to YES ;; c) cat_flag=$YES # Indicate user wants to include ;; # system catalogs s) smi_flag=$YES # Indicate user wants to include SMI tables ;; m) iosum_req=$YES # Indicate user requested default summary ;; p) iorw_flag=$YES # User wants separate read and write stats ;; z) zero_flag=$YES # User wants to see even zero-I/O activity. ;; T) temp_flag=$YES # User wants I/O stats on user temp tables ;; H) hash_flag=$YES # User wants I/O stats on hash/sort tables ;; # # parse_params() Continued D) sp_list="$OPTARG" # User wants I/O stats only on selected # temp_flag=$YES # dbspaces. Implies all tables, even temps # cat_flag=$YES # and catalogs # hash_flag=$YES # and even hash/sort tables in this dbspace ;; d) db_list="$OPTARG" # Capture list of databases smi_flag=$YES # At least don't exclude SMI databases; # we want to avoid a contradiction ;; t) tab_list="$OPTARG" # Capture list of tables errmsg tab_list = $tab_list cat_flag=$YES # At least don't exclude catalogs; temp_flag=$YES # Also allow temp and hash tables in the hash_flag=$YES # display, if the user listed them. smi_flag=$YES # Leave it up to the user to name tables. ;; *) echo Unrecognized option: $cparm usage exit 1 ;; esac done # Finished flagging for parameters } # End function parse_params() # process_params() { # Now start to react to those flags if [ $help_flag -eq $YES ] # Did user ask for syntax help? then usage exit 0 fi # Handling of options and parameters: # ---------------------------------- # 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 (or list of databases) # # 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 SMI-exclusion clause # if [ $smi_flag -eq $YES ] then SMI_CLAUSE="--"${SMI_CLAUSE} # by prepending the -- fi # Option: -d (Name databases to be analyzed) # if [ -n "$db_list" ] # On other hand, if user listed databases then # we need to augment the WHERE clause SMI_CLAUSE="where name in (" # Start new version of WHERE clause # Let the shell handle the list as I count down. I have to trans- # form the commas to blanks in order to use each database name # set $(echo $db_list|tr , " ") while [ $# -gt 0 ] # For each database the user specified do SMI_CLAUSE=${SMI_CLAUSE}\"${1}\" # Build list of database names if [ $# -gt 1 ] # If this is not last databse in list then # and append a comma to the most recent SMI_CLAUSE=${SMI_CLAUSE}, # entry in the list fi shift # Set up to use next entry in users list done SMI_CLAUSE=${SMI_CLAUSE}")" # Close the IN list for SQL fi # if there is a db_list # ----------------------------------------------------------------- # # process_params() - continued # Option: -c (Include system catalogs) # # By default, we exclude system catalogs from this listing. This is # because if a catalog has lots of I/O, 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 t.tabid >= 100" # Excludes system catalogs if [ $cat_flag -eq $YES -o -n "$tab_list" ] then CATALOGS_CLAUSE="--"${CATALOGS_CLAUSE} fi # Option: -m (Explicit request for summary column) # (Already handled OK in the getopts loop) # Option: -p (Separate read and write statistics) # # format_val is a variable value to be passed to awk in the -v option # format_val=S # Default - summary only if [ $iorw_flag -eq $YES ] then # User requested separate read/write if [ $iosum_req -eq $YES ] then # If user asked for summary anyway format_val=B # make sure it will also be displayed else # User failed to request summary stats format_val=P # so display only read/write stats fi fi # ----------------------------------------------------------------- # # process_params() - continued # Option: -t (Specifying a table name) # 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 simply # give the information for each database's table. # # Of course, by default, all tables (even catalogs) are fair game. # if [ -n "$tab_list" ] # If user specified a list of tables then # run a similar list-builder as for databases TAB_CLAUSE="and t.tabname in (" # Start new WHERE clause # Let the shell handle the list as I count down. I have to trans- # form the commas to blanks in order to use each table name # set $(echo $tab_list|tr , " ") errmsg After set tablist: params: $* while [ $# -gt 0 ] # For each database the user specified do errmsg '$#' = $# errmsg '$1' = $1 TAB_CLAUSE=${TAB_CLAUSE}\"${1}\" # Build list of database names if [ $# -gt 1 ] # If this is not last databse in list then # and append a comma to the most recent TAB_CLAUSE=${TAB_CLAUSE}, # entry in the list fi shift # Set up to use next entry in users list done TAB_CLAUSE=${TAB_CLAUSE}")" # Close the IN list for SQL fi # ----------------------------------------------------------------- # # process_params() - continued # Option: -D (Specifying one or more DBspaces) # DBSP_CLAUSE="--" # Start as non-contributory clause if [ -n "$sp_list" ] # If a dbspace list was specified then # use it to generate an IN-list DBSP_CLAUSE="and ds.name in (" # Start new part to WHERE clause set $(echo $sp_list|tr , " ") # Turn commas into spaces errmsg After set, ds_list: $* while [ $# -gt 0 ] # For each dbspace the user specified do errmsg '$#' = $# errmsg '$1' = $1 DBSP_CLAUSE=${DBSP_CLAUSE}\"${1}\" # Build list of dbspace names if [ $# -gt 1 ] # If this is not last dbspace in list then # and append a comma to the most recent DBSP_CLAUSE=${DBSP_CLAUSE}, # entry in the list fi shift # Set up to use next entry in users list done # Done building IN list. DBSP_CLAUSE=${DBSP_CLAUSE}")" # Now close the IN list for SQL fi # End -D # ----------------------------------------------------------------- # # process_params() - continued # Option: -T (Include I/O stats on temp tables in databases) # Plan - Add a new union to the query that gets my data to include # these temp tables. # Note: No need to include CATALOGS_CLAUSE here; the system catalogs # are, of course, not temp tables. # if [ $temp_flag -eq $YES ] then cat >$TMP_UNION_SQL <<%% union select trim(t.dbsname) || ":" || trim(t.tabname) table_name, "(temp)" indexname, ds.name dbspace, hex(t.partnum) partition, isreads, iswrites, (isreads + iswrites) isios, bufreads, bufwrites, (bufreads + bufwrites) bufios, pagreads, pagwrites, (pagreads + pagwrites) pageios from sysmaster:sysptprof t, sysmaster:sysdbspaces ds, sysmaster:systabnames tn, sysmaster:systabinfo ti where t.partnum = tn.partnum and t.partnum = ti.ti_partnum and ds.dbsnum = trunc(t.partnum / 1048576) and tn.dbsname = "_DBS_" -- To be translated by sed one-liner and ( (sysmaster:bitval(ti_flags,'0x20') = 1) -- Indicators or (sysmaster:bitval(ti_flags,'0x40') = 1) -- of temp table or (sysmaster:bitval(ti_flags,'0x80') = 1) ) $TAB_CLAUSE $DBSP_CLAUSE %% fi # End -T option # ----------------------------------------------------------------- # # process_params() - Continued # Option -H (Include hash and sort-temp tables in the output) # # Plan: Since these are not part of any database, I can pull them out # of the other union and get this data separately. The include it # together with the union output. (Sorta like scab queries ;-) # if [ $hash_flag -eq $YES ] then cat >$HASH_SQL <<%% # Create the hash component select trim(t.dbsname) || ":" || trim(t.tabname) table_name, "(temp)" indexname, ds.name dbspace, hex(t.partnum) partition, isreads, iswrites, (isreads + iswrites) isios, bufreads, bufwrites, (bufreads + bufwrites) bufios, pagreads, pagwrites, (pagreads + pagwrites) pageios from sysmaster:sysptprof t, sysmaster:sysdbspaces ds, sysmaster:systabnames tn where t.partnum = tn.partnum and ds.dbsnum = trunc(t.partnum / 1048576) and tn.dbsname not in (select name from sysdatabases) and tn.tabname != "TBLSpace" -- Don't display Partn Tblspace $TAB_CLAUSE $DBSP_CLAUSE order by table_name, partition ; %% fi # End -H processing # ----------------------------------------------------------------- } # End function process_params() # # Script execution begins here, by parsing command line parameters: # Call parse_params() to validate parameters, # then call parse_params() to set up SQL components accordingly # errmsg Raw extract file is $WHOLE_UNL parse_params $* process_params # Put the first SQL command(s) into the sql script file # cat <<%% >$WHOLE_SQL set isolation to dirty read; -- Avoid encountering table locks %% # If hash tables are to be included, insert the HASH component. # if [ $hash_flag -eq $YES ] then cat $HASH_SQL >>$WHOLE_SQL fi # With the exception of database-specific clauses, all paramatrizable # components of the driving SQL are in place. That exception will be # handled within the loop by a sed command. # cat >$IOSTAT_SQL <<%% select trim(pt.dbsname) || ":" || trim(pt.tabname) table_name, "(Table)" indexname, ds.name dbspace, hex(pt.partnum) partition, isreads, iswrites, (isreads + iswrites) isios, bufreads, bufwrites, (bufreads + bufwrites) bufios, pagreads, pagwrites, (pagreads + pagwrites) pageios from sysmaster:sysptprof pt, sysmaster:sysdbspaces ds, _DBS_:systables t where t.partnum = pt.partnum and ds.dbsnum = trunc(pt.partnum / 1048576) $CATALOGS_CLAUSE $TAB_CLAUSE $DBSP_CLAUSE union -- No need for catalogs clause in fragment part. select trim(pt.dbsname) || ":" || trim(t.tabname) table_name, f.indexname, ds.name dbspace, hex(pt.partnum) partition, isreads, iswrites, (isreads + iswrites) isios, bufreads, bufwrites, (bufreads + bufwrites) bufios, pagreads, pagwrites, (pagreads + pagwrites) pageios from sysmaster:sysptprof pt, sysmaster:sysdbspaces ds, _DBS_:sysfragments f, _DBS_:systables t where f.partn = pt.partnum and t.tabid = f.tabid and ds.dbsnum = trunc(pt.partnum / 1048576) $TAB_CLAUSE $DBSP_CLAUSE $(cat $TMP_UNION_SQL) order by 1, 2, 3 ; --order by table_name, index name, partition %% # # Main loop driven by SQL-generated list of databases. # errmsg CATALOGS_CLAUSE: $CATALOGS_CLAUSE errmsg TAB_CLAUSE: $TAB_CLAUSE errmsg Start driving loop of databases for database in $( dbaccess sysmaster - <<%% 2>>$DB_FILE output to pipe "cat" without headings select name from sysdatabases $SMI_CLAUSE order by 1; %% ) do # Perform that little edit on the SQL we have set up in $IOSTAT_SQL # and append that to the SQL script file ($WHOLE_SQL) we are building. # errmsg Appending for database $database : sed -e s/_DBS_/${database}/g $IOSTAT_SQL >>$DB_FILE # DEBUG sed -e s/_DBS_/${database}/g $IOSTAT_SQL >>$WHOLE_SQL done errmsg "About to send to following SQL script to dbaccess" cat $WHOLE_SQL >>$DB_FILE dbaccess sysmaster - <$WHOLE_SQL >>$WHOLE_UNL 2>>$DB_FILE # We now have the raw data in $WHOLE_UNL. The following awk script # will produce the nice output we require for users. # awk -v format_type=$format_val -v skip_zeroio=$zero_flag ' BEGIN { row_complete = 0 # Not completed any rows yet. # Which header line should I display? # format_type == S The summary only # format_type == P Separate read and write columns # format_type == B Both, separate read/write AND the summary # # skip_zeroio == 1 Do not display rows with no I/O activity # skip_zeroio == 0 Do display rows with no I/O activity if (format_type == "S") # Heading for summary only display { printf ("|%s|%s|%s|%s|%s|%s|%s|\n", "TableName", "IndexName", "DB-Space", "Partition", "ISAM-IO", "Buffer-IO", "Page-IO") row_format_s = "|%s|%s|%s|%s|%d|%d|%d|\n" # For printf output } else if (format_type == "P") # Heading for separate read/write display { printf ("|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|\n", "TableName", "IndexName", "DB-Space", "Partition", "ISAM-RD", "ISAM-WR", "Buffer-RD", "Buffer-WR", "Page-RD", "Page-WR") row_format_p = "|%s|%s|%s|%s|%d|%d|%d|%d|%d|%d|\n" } else if (format_type == "B") { printf ("|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|\n", "TableName", "IndexName", "DB-Space", "Partition", "ISAM-RD", "ISAM-WR", "ISAM-IO", "Buffer-RD", "Buffer-WR", "Buffer-IO", "Page-RD", "Page-WR", "Page-IO") row_format_b = "|%s|%s|%s|%s|%d|%d|%d|%d|%d|%d|%d|%d|%d|\n" } } # End of BEGIN processing NF == 0 {next} $1 == "table_name" {table_name = $2} $1 == "indexname" { if (NF > 1) # If there is an index name to report indexname = $2 # use it. It might be "(Table)" else # No index name indexname = "(Fragment)" # Must be a table fragment } $1 == "dbspace" {dbspace = $2} $1 == "partition" {partition = $2} $1 == "isreads" {isreads = $2} $1 == "iswrites" {iswrites = $2} $1 == "isios" {isios = $2} $1 == "bufreads" {bufreads = $2} $1 == "bufwrites" {bufwrites = $2} $1 == "bufios" {bufios = $2} $1 == "pagreads" {pagreads = $2} $1 == "pagwrites" {pagwrites = $2} $1 == "pageios" {pageios = $2; row_complete = 1} # row_complete == 1 { # Finished gathering data for a row of output row_complete = 0 # Reset this for next row of data # If we are to skip rows with no I/O activity, then check if this is # such a row. If yes to both counts, skip the current line. # if ((skip_zeroio == 1) && ((isios + bufios + pageios) == 0)) next # And now that we know to print the row, it is just a matter of # choosing the format. # if (format_type == "S") printf(row_format_s, table_name, indexname, dbspace, partition, isios, bufios, pageios) # else if (format_type == "P") printf(row_format_p, table_name, indexname, dbspace, partition, isreads, iswrites, bufreads, bufwrites, pagreads, pagwrites) # else if (format_type == "B") printf(row_format_b, table_name, indexname, dbspace, partition, isreads, iswrites, isios, bufreads, bufwrites, bufios, pagreads, pagwrites, pageios) # Now, whatever format we used, we are done with the row. # # row_complete = 0 # Reset this for next row of data # Moved the above line to the top of this pattern block. } ' $WHOLE_UNL | beautify-unl.sh rm $TMP_UNION_SQL rm $IOSTAT_SQL rm $HASH_SQL rm $WHOLE_UNL rm $WHOLE_SQL @EOF set `wc -lwc