Re: Online log monitor
Jon P. Alder writes:
|>
|> Talking about extents, has anybody writen a program that takes a look at my
|>existing
|> tables and can recommend and extent size?
I originally wrote this in 4gl, then converted it to esql/c; it still smells
of 4gl (I would certainly have structured it differently if I had written
it in esql/c originally). There are probably bugs in it as I didn't go
too crazy in testing it for all situations.
Btw, "turbopak" was a "service" we sold in Turbo days, and this utility
was written to support that service (it was provided via our consulting
group, of which I was a member).
Caveat Emptor!
/* turbopak.ec
coded by Dave Kosenko for something to do
*/
/* mods for multi-page rows in online */
/* command line options supported:
-d database database to size
-g growth growth factor (for all tables)
-i %full avg %full of index node (use 100 for sort index)
-o filename output file
-p pagesize pagesize to use
-u run update statistics before running pgm
MODS:
6-20-91 Correct calc of datetime/interval index size
12-10-92 Added fullness of index node as command line arg.
default remains at 75%
*/
#include <stdio.h>
#include <signal.h>
$include sqlca;
$include datetime;
#include "decimal.h"
#define CNULL (char *) 0
#define PGOVH 28
int pagesize; /* system page size */
char outfile[80]; /* output file name */
int growth; /* growth factor */
int nodefull; /* fullness of index node in % */
char greach[2]; /* provide growth for each table? */
FILE *ofile; /* output file */
$char dbname[18]; /* database name */
int dflag;
int gflag;
int iflag;
int oflag;
int uflag;
int olflag;
main(argc, argv)
int argc;
char *argv[];
{
int i;
dflag = gflag = oflag = uflag = olflag = 0;
pagesize = 2048; /* default page size is 2K */
nodefull = 75; /* default avg fullness of index node */
procopts(argc, argv);
if (!dflag)
prompt("Enter database name > ", dbname);
$database $dbname;
if (sqlca.sqlcode)
{
fprintf(stderr,"Error opening database '%s'\\n", dbname);
fprintf(stderr,"Error codes are %d, %d\\n",
sqlca.sqlcode, sqlca.sqlerrd[1]);
exit(1);
}
if (sqlca.sqlwarn.sqlwarn3 == 'W') /* online db */
olflag = 1;
if (!oflag)
prompt("Send output where? > ", outfile);
if (strlen(outfile))
{
ofile = fopen(outfile, "w+");
if (ofile == (FILE *) 0)
{
fprintf(stderr, "Cannot open %s for output\\n",
outfile);
exit(1);
}
}
else
ofile = stdout;
if (!gflag)
{
prompt("Set growth for each table? (y/n) ", greach);
/* all tables have same growth*/
if (*greach == 'n' || *greach == 'N')
{
iprompt("Growth factor (whole number %) > ", &growth);
gflag = 1;
}
}
doreport(dbname);
}
procopts(ac, av)
int ac;
char *av[];
{
int i;
for (i=1; i<ac; i++)
{
if (*av[i] == '-')
{
switch (av[i][1])
{
case '-': /* print options */
usage();
exit(0);
break;
case 'p': /* page size */
if (i+1 >= ac)
{
usage();
exit(1);
}
pagesize = atoi(av[++i]);
break;
case 'd': /* database name */
if (i+1 >= ac)
{
usage();
exit(1);
}
dflag++;
strcpy(dbname, av[++i]);
break;
case 'g': /* growth for db */
if (i+1 >= ac)
{
usage();
exit(1);
}
gflag=1;
growth = atoi(av[++i]);
break;
case 'i': /* index node fullness */
if (i+1 >= ac)
{
usage();
exit(1);
}
iflag++;
nodefull = atoi(av[++i]);
break;
case 'o': /* output file */
if (i+1 >= ac)
{
usage();
exit(1);
}
oflag++;
strcpy(outfile, av[++i]);
break;
case 'u': /* update stats flag */
uflag++;
break;
}
}
}
}
doreport(dbname)
char dbname[];
{
int i;
$int ndxlen;
$int totspace;
$int v_rowsize;
$int v_collength;
$int v_coltype;
$int v_colno;
$string v_tabname[18];
$int v_tabid;
$int v_nrows;
$int v_ncols;
$string v_idxname[18];
$int v_part[17];
$int mast_part;
int drowpp[2]; /* data rows per page*/
int irowpp[2]; /* index rows per page*/
$int size1ndx; /* size of 1 index */
$int dpgnow; /* data pages needed for current data */
$int ipgnow; /* index pages needed for current data */
int bitmpp; /* bit map pages */
int extovh; /* overhead for part part (ext) */
if (uflag)
$update statistics;
$DECLARE curs1 CURSOR FOR
SELECT tabname, rowsize, tabid, nrows, ncols
FROM systables
WHERE tabtype = "T" AND tabid > 99
ORDER BY tabname; if (!olflag) /* se */
$DECLARE curs2a CURSOR FOR
SELECT part1, part2, part3, part4, part5, part6,
part7, part8, idxname
FROM sysindexes
WHERE tabid = $v_tabid; else /* online */
$DECLARE curs2b CURSOR FOR
SELECT part1, part2, part3, part4, part5, part6,
part7, part8, part9, part10, part11, part12, part13,
part14, part15, part16, idxname
FROM sysindexes
WHERE tabid = $v_tabid;
$DECLARE curs3 CURSOR FOR
SELECT coltype, collength
FROM syscolumns
WHERE tabid = $v_tabid and colno = $mast_part;
/* START REPORT detail TO outfile */
detail(dbname, CNULL, 0, 0, 0, 0, 0);
$open curs1;
for (;;)
{
$fetch curs1 into $v_tabname, $v_rowsize, $v_tabid, $v_nrows,
$v_ncols;
if (sqlca.sqlcode)
{
$close curs1;
break;
}
if (!gflag)
{
char foo[50];
sprintf(foo, "%s %s :","growth for table", v_tabname);
iprompt(foo, &growth);
}
/* overhead for partition partition */
extovh = 24 + 4 + 56;
extovh = extovh + (v_ncols * 4);
/* how many rows will fit on a page */
paging(v_rowsize, pagesize, drowpp);
/* how many pages needed for data*/
if (drowpp[1]) /* row is > page */
{
dpgnow = drowpp[1]*v_nrows;
dpgnow += (v_nrows/drowpp[0]);
if (v_nrows % drowpp[0])
dpgnow++;
}
else
{
dpgnow = (v_nrows/drowpp[0]);
if (v_nrows % drowpp[0])
dpgnow++;
}
detail(dbname, v_tabname, v_rowsize, drowpp, v_nrows, dpgnow,1);
/* init index needs to 0 */
ipgnow = 0;
/* must accumulate col id for index parts */
if (!olflag)
$open curs2a; /* se */
else
$open curs2b; /* online */
for (;;)
{
if (!olflag)
$fetch curs2a into
$v_part[1], $v_part[2], $v_part[3],
$v_part[4], $v_part[5], $v_part[6],
$v_part[7], $v_part[8], $v_idxname;
else /* online db */
$fetch curs2b into