#!/bin/sh
#
# This is a shell archive.  To extract its contents,
# execute this file with /bin/sh to create the file(s):
#
# README		    temptab.4gl
#
# This shell archive created: Wed Jan 28 18:34:20 EST 1998
#
echo "Extracting file README"
sed -e 's/^X//' <<\SHAR_EOF > README
Xtemptab/README
X
XSummary:        Generate information on temporary tables
X
XEnvironment:    Pyramid NILE 150, Unix DC/OSx, Informix v7.14 (4GL v6.02)
X                DEC Alpha 4100, OSF1 v3.2G, Informix v7.23 (4GL v6.05)
X
XSubmitted by:   Richard Thomas <ret@unlisted.net> (Sydney, Australia)
X
XThe program temptab.4gl is designed to generate information about the temporary
Xtables which currently exist.  The information displayed is as current as the
Xdata available in the sysmaster database.  Because sysmaster is used, this
Xprogram requires a Version 7.x Informix installation.
X
XOnce compiled (I use c4gl, but RDS works just as well), the usage is:
X
Xtemptab [ -s | -u username | -d dbname ]
X
XAn example of the output is shown below:
X
X$ temptab
Xdatabase:owner:tabname                      frags       pages        rows
X========================================    =====  ===========  ===========
Xmdss:jenkint:t_open                            12        2,676            0
Xmdss:nguyenjt:t_latest_cell                     8          248            0
Xmdss:odw_app:t_acct_changes                     1            8            1
Xmdss:odw_app:t_cancelled_rev                    8           64            0
Xmdss:odw_app:t_cell_changes                    13          240        6,651
Xmdss:odw_app:t_cell_hist10                    116        4,672       42,512
Xmdss:odw_app:t_cell_hist7                      32        6,048       83,435
Xmdss:odw_app:t_cell_hist8                      52        9,352      115,885
Xmdss:odw_app:t_cell_hist9                      12        1,632       20,330
Xmdss:odw_app:t_hist1                            4       19,520            0
Xmdss:odw_app:t_last_hist                      108        1,024       27,177
Xmdss:odw_app:t_last_tran                        4           32            0
Xmdss:odw_app:t_max_dt_key                       8        8,096            0
Xmdss:odw_app:t_mon_rev                         12           96            0
Xmdss:odw_app:t_proc_chg1                       28          288          757
Xmdss:odw_app:t_proc_chg2                        4           32            1
Xmdss:odw_app:t_proc_chg3                       12           96            3
Xmdss:odw_app:t_proc_chg5                        4           32            1
Xmdss:odw_app:t_proc_chg6                        8           64            2
Xmdss:odw_app:t_proc_chg7                        8           64            2
Xmdss:odw_app:t_proc_temp                       20          160          712
Xmdss:odw_rpt:t_candidates                       4          360        1,070
Xmdss:odw_rpt:t_cell_mdate                       4           32            0
Xmdss:odw_rpt:t_cellulars_in                     4        5,744            0
Xmdss:odw_rpt:t_dup_cell_cnt                     4           32            0
Xmdss:odw_rpt:t_ld_other                         4           32            0
Xmdss:odw_rpt:t_ld_pres                          4           32            0
Xmdss:odw_rpt:t_mon_call                         4    3,842,160            0
Xmdss:odw_rpt:t_scv_mob_avg                      4           32            0
Xmdss:smithka:t_wk                               4           32            0
X
X$
X
XThe default (no arguments) output sorts the information by
Xdatabase, username, tablename
X
XThe other sort option is:
X
X    -s              Sorts the output by numpages DESCENDING
X                    (i.e. biggest table to smallest)
X
XThere are two other options which limit the amount of data shown:
X
X    -u username     Limits output to a particular username
X
X    -d dbname       Limits output to a particular database
X
XIn this implementation you cannot combine options.  I may add that functionality
Xat some later stage if I discover any need for it (but I haven't so far ;-)
X
XAs an aside, the numrows column does not get updated in real time, but is
Xpopulated when the INTO TEMP or INSERT INTO completes.  And that makes sense,
Xif you think about it.  If a program is reading from a cursor and INSERTing
Xas it goes, this might be different.  I haven't tested that, as we don't write
Xthat sort of code.  In any case the numpages is updated in real time, hence
Xthe use of that column in the sort-order.  Of course, if you are looking for
Xthe heavy hitter of temp space, the pages used are more relevant than the number
Xof rows anyway, as rows per page can vary so wildly.
X
XI hope you find this little utility of some use.  I must thank John Miller III
Xfor his tip in IIUG News (July 1997) regarding the BITVAL() function which
Xinspired me to write it.
X
XRichard Thomas
Xret@unlisted.net
SHAR_EOF
if [ `wc -c < README` -ne     4561 ]
then
    echo "Lengths do not match -- Bad Copy of README"
fi
echo "Extracting file temptab.4gl"
sed -e 's/^X//' <<\SHAR_EOF > temptab.4gl
X#################################################################################
X#        TEMPTAB : generate info on current temporary tables
X#
X#    BY      : Richard Thomas        (email ret@unlisted.net)                   #
X#    SCCS    : temptab.4gl 1.2 97/09/30 15:07:02                                #
X#################################################################################
X
XDATABASE sysmaster
X
XMAIN
X  DEFINE ses RECORD
X    dbase CHAR(18),
X    owner CHAR(8),
X    table CHAR(18),
X    frags SMALLINT,
X    pages INTEGER,
X    rows  INTEGER
X  END RECORD
X
X  DEFINE qtext CHAR(500),
X         arg1 CHAR(2),
X         arg2 CHAR(18)
X
X  LET arg1 = ARG_VAL(1)
X  LET arg2 = ARG_VAL(2)
X
X  LET qtext =
X      'SELECT n.dbsname, n.owner, n.tabname,',
X              ' COUNT(*), SUM(i.ti_nptotal), SUM(i.ti_nrows)',
X        ' FROM systabnames n, systabinfo i',
X        ' WHERE BITVAL(i.ti_flags, "0x0020") = 1',
X          ' AND i.ti_partnum = n.partnum'
X
X  IF arg1 = "-u" THEN
X    LET qtext = qtext CLIPPED, ' AND n.owner = "', arg2 CLIPPED, '"'
X  END IF
X
X  IF arg1 = "-d" THEN
X    LET qtext = qtext CLIPPED, ' AND n.dbsname = "', arg2 CLIPPED, '"'
X  END IF
X
X  LET qtext = qtext CLIPPED, ' GROUP BY 1,2,3'
X
X  IF arg1 = "-s" THEN
X    LET qtext = qtext CLIPPED, ' ORDER BY 5 DESC'
X  ELSE
X    LET qtext = qtext CLIPPED, ' ORDER BY 1,2,3'
X  END IF
X
X  WHENEVER ERROR CONTINUE
X
X  PREPARE p_csr FROM qtext
X  IF SQLCA.sqlcode != 0 THEN
X    DISPLAY "An error occured parsing:"
X    DISPLAY qtext
X    DISPLAY "Usage:  temptab [ -s | -u username | -d dbname ]"
X    EXIT PROGRAM -1
X  END IF
X
X  DECLARE x_csr CURSOR FOR p_csr
X
X  START REPORT sesdata
X
X  FOREACH x_csr INTO ses.*
X    OUTPUT TO REPORT sesdata(ses.*)
X  END FOREACH
X
X  FINISH REPORT sesdata
XEND MAIN
X
XREPORT sesdata(s)
X  DEFINE s RECORD
X    dbase CHAR(18),
X    owner CHAR(8),
X    table CHAR(18),
X    frags SMALLINT,
X    pages INTEGER,
X    rows  INTEGER
X  END RECORD
X
X  OUTPUT
X    PAGE LENGTH 3
X    LEFT MARGIN 0
X    RIGHT MARGIN 80
X    TOP MARGIN 0
X    BOTTOM MARGIN 0
X
X  FORMAT
X    FIRST PAGE HEADER
X      PRINT "database:owner:tabname",
X            COLUMN 45, "frags",
X            COLUMN 57, "pages",
X            COLUMN 70, "rows"
X      PRINT "========================================",
X            COLUMN 45, "=====",
X            COLUMN 52, "===========",
X            COLUMN 65, "==========="
X
X    ON EVERY ROW
X      PRINT s.dbase CLIPPED, ":", s.owner CLIPPED, ":",
X            s.table CLIPPED,
X            COLUMN 45, s.frags USING "####&",
X            COLUMN 52, s.pages USING "###,###,##&",
X            COLUMN 65, s.rows  USING "###,###,##&"
X
XEND REPORT
SHAR_EOF
if [ `wc -c < temptab.4gl` -ne     2605 ]
then
    echo "Lengths do not match -- Bad Copy of temptab.4gl"
fi
echo "Done."
exit 0