temptab/README
Summary: Generate information on temporary tables
Environment: Pyramid NILE 150, Unix DC/OSx, Informix v7.14 (4GL v6.02)
DEC Alpha 4100, OSF1 v3.2G, Informix v7.23 (4GL v6.05)
Submitted by: Richard Thomas <ret@unlisted.net> (Sydney, Australia)
The program temptab.4gl is designed to generate information about the temporary
tables which currently exist. The information displayed is as current as the
data available in the sysmaster database. Because sysmaster is used, this
program requires a Version 7.x Informix installation.
Once compiled (I use c4gl, but RDS works just as well), the usage is:
temptab [ -s | -u username | -d dbname ]
An example of the output is shown below:
$ temptab
database:owner:tabname frags pages rows
======================================== ===== =========== ===========
mdss:jenkint:t_open 12 2,676 0
mdss:nguyenjt:t_latest_cell 8 248 0
mdss:odw_app:t_acct_changes 1 8 1
mdss:odw_app:t_cancelled_rev 8 64 0
mdss:odw_app:t_cell_changes 13 240 6,651
mdss:odw_app:t_cell_hist10 116 4,672 42,512
mdss:odw_app:t_cell_hist7 32 6,048 83,435
mdss:odw_app:t_cell_hist8 52 9,352 115,885
mdss:odw_app:t_cell_hist9 12 1,632 20,330
mdss:odw_app:t_hist1 4 19,520 0
mdss:odw_app:t_last_hist 108 1,024 27,177
mdss:odw_app:t_last_tran 4 32 0
mdss:odw_app:t_max_dt_key 8 8,096 0
mdss:odw_app:t_mon_rev 12 96 0
mdss:odw_app:t_proc_chg1 28 288 757
mdss:odw_app:t_proc_chg2 4 32 1
mdss:odw_app:t_proc_chg3 12 96 3
mdss:odw_app:t_proc_chg5 4 32 1
mdss:odw_app:t_proc_chg6 8 64 2
mdss:odw_app:t_proc_chg7 8 64 2
mdss:odw_app:t_proc_temp 20 160 712
mdss:odw_rpt:t_candidates 4 360 1,070
mdss:odw_rpt:t_cell_mdate 4 32 0
mdss:odw_rpt:t_cellulars_in 4 5,744 0
mdss:odw_rpt:t_dup_cell_cnt 4 32 0
mdss:odw_rpt:t_ld_other 4 32 0
mdss:odw_rpt:t_ld_pres 4 32 0
mdss:odw_rpt:t_mon_call 4 3,842,160 0
mdss:odw_rpt:t_scv_mob_avg 4 32 0
mdss:smithka:t_wk 4 32 0
$
The default (no arguments) output sorts the information by
database, username, tablename
The other sort option is:
-s Sorts the output by numpages DESCENDING
(i.e. biggest table to smallest)
There are two other options which limit the amount of data shown:
-u username Limits output to a particular username
-d dbname Limits output to a particular database
In this implementation you cannot combine options. I may add that functionality
at some later stage if I discover any need for it (but I haven't so far ;-)
As an aside, the numrows column does not get updated in real time, but is
populated when the INTO TEMP or INSERT INTO completes. And that makes sense,
if you think about it. If a program is reading from a cursor and INSERTing
as it goes, this might be different. I haven't tested that, as we don't write
that sort of code. In any case the numpages is updated in real time, hence
the use of that column in the sort-order. Of course, if you are looking for
the heavy hitter of temp space, the pages used are more relevant than the number
of rows anyway, as rows per page can vary so wildly.
I hope you find this little utility of some use. I must thank John Miller III
for his tip in IIUG News (July 1997) regarding the BITVAL() function which
inspired me to write it.
Richard Thomas
ret@unlisted.net
