Re: How to generate Alter Table modify next entent size?
--IMA.Boundary.2617193190
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
Content-Description: cc:Mail note part
Ditmar
I am including an ESQL/C program I had written to predict the next
extent size that a given table should have and number of extents that
a table can have. The calculation of the extent size that the program
uses is given in the DBA Guide. I have used 120% of space recommended
by Informix as my initial extent and 60% for next extent, but you
could change it in the code to suit yourself.
This program should be in the IIUG archives as well, but it was a long
time ago and I dont remember exactly.
You may be able to use this in your work.
HTH
Sujit
______________________________ Reply Separator _________________________________
Subject: How to generate Alter Table modify next entent size?
Author: dengelsen@my-dejanews.com at internet
Date: 12/17/1998 8:58 AM
Hello,
How to generate Alter Table modify next entent size?
Currently I've tables with 100+ extents. I've used the following script with
dbaccess to display al tables with 100+ extents.
select dbsname, tabname, count(*) num_extents
from sysmaster:systabnames,
sysmaster:sysptnext
where partnum = pe_partnum and dbsname = 'baan'
group by 1,2
having count(*) > 100
order by 3 desc;
Now I want to modify the next extentsizes from these tables. I would like to
have a script that produces the following output
ALTER TABLE tabname MODIFY NEXT SIZE <current NE_VALUE *2>
How do I get this current next extent size?
Am I doing the right thing?
Do you have a script to does do this?
What is the max number of extents for informix tables (pagesize=2k)?
I just want to prevent tables from getting to many extents. We are currently
setting up a new server, so reducing extents is not an important issue jet.
Thanks in advance
Greetings,
Ditmar den Engelsen
-----------== Posted via Deja News, The Discussion Network ==----------
http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
--IMA.Boundary.2617193190
Content-Type: application/octet-stream; name="extsize.ec"
Content-Transfer-Encoding: base64
Content-Description: Unknown data type
Content-Disposition: attachment; filename="extsize.ec"
/*
* extsize.ec
*
* Calculates table size for tables needed for allocation of intial EXTENT
* SIZE and NEXT EXTENT sizes. Also calculates the upper limit on the number
* of extents for the table. Outputs recommended EXTENT SIZE and NEXT EXTENT,
* TBLSpace * 1.2 and TBLSpace * 0.6 respectively.
*
* Compile with: esql -o extsize extsize.ec
*
*/
#include <stdio.h>
#include <math.h>
#define KPERPAGE 2
main(argc, argv)
int argc;
char *argv[];
{
$char p_dbname[19], p_tabname[19], p_idxtype[2];
$long int p_ncols, p_nindexes, p_nrows, p_tabid, p_rowsize;
$long int p_part1, p_part2, p_part3, p_part4, p_part5, p_part6,
p_part7, p_part8, p_part9, p_part10, p_part11, p_part12,
p_part13, p_part14, p_part15, p_part16;
$long int p_colno, p_collength, p_coltype, p_colmin, p_colmax,
p_estrows;
$float p_pctuniq;
$int p_varlen, i, j;
long int p_pagesize, p_colspace, p_ixspace, p_ixparts, p_extspace,
p_numextents, p_pageuse, p_homerow, p_expages,
p_spcneeded, p_overpage, p_datrows, p_datpages, p_entsize,
p_pagents, p_leaves, p_branches, p_keysize, p_fextsize,
p_nextsize;
long int p_colarr[16];
float p_defpct;
printf("********************************************\\n");
printf("* *\\n");
printf("* TBLSpace requirements calculator *\\n");
printf("* version 1.0 *\\n");
printf("* *\\n");
printf("********************************************\\n");
if (argc != 2)
{
printf("Usage: calc_extent -i|-s|-d\\n");
printf(" -i - interactive mode\\n");
printf(" -s - silent mode using defaults\\n");
printf(" -d - get inputs from database table\\n");
printf(" tblspcdat\\n");
printf(" tabname CHAR(18)\\n");
printf(" estrows INTEGER\\n");
printf(" idxuniqlvl FLOAT\\n");
exit(1);
}
printf("Enter database name: ");
scanf("%s", p_dbname);
$DATABASE $p_dbname;
if (sqlca.sqlcode != 0)
{
printf("Cant stat database %s\\n", p_dbname);
exit(1);
}
p_pagesize = KPERPAGE * 1024;
p_pageuse = p_pagesize - 28;
printf("Enter table name (wildcards allowed): ");
scanf("%s", p_tabname);
$DECLARE q_tables CURSOR FOR
SELECT tabid, tabname, ncols, nindexes, nrows, rowsize
FROM systables
WHERE tabid >= 100
AND tabtype = "T"
AND tabname MATCHES $p_tabname;
$OPEN q_tables;
for (;;)
{
$FETCH q_tables INTO $p_tabid, $p_tabname, $p_ncols,
$p_nindexes, $p_nrows, $p_rowsize;
if (sqlca.sqlcode == 100) break;
p_colspace = 4 * p_ncols;
p_ixspace = 12 * p_nindexes;
$DECLARE q_indexes CURSOR FOR
SELECT idxtype, part1, part2, part3, part4, part5, part6, part7,
part8, part9, part10, part11, part12, part13, part14,
part15, part16
FROM sysindexes
WHERE tabid = $p_tabid;
$OPEN q_indexes;
p_ixparts = 0;
p_keysize = 0;
p_pctuniq = 0.0;
j = 0;
for (;;)
{
$FETCH q_indexes INTO $p_idxtype, $p_part1, $p_part2, $p_part3,
$p_part4, $p_part5, $p_part6, $p_part7, $p_part8, $p_part9,
$p_part10, $p_part11, $p_part12, $p_part13, $p_part14,
$p_part15, $p_part16;
if (sqlca.sqlcode == 100) break;
j++;
if (strncmp(p_idxtype, "U", 1) == 0) p_pctuniq += 1.0;
p_colarr[1] = p_part1;
p_colarr[2] = p_par