informix IDS 9.4 table dbschema

底下這段程式是用來產生 table 的 dbschema，適用在 informix IDS 9.4。
產生的同時會根據目前的 table 的資料筆數去定義出，extent size，而 next size 則預設是 extent size 的 1/4，
資料筆數小於一千筆的會預設是一千筆！

#!/usr/bin/perl
# -----------------------------------------------------------------------------
# determine extent size and next extent of table
#
# index space needed:
#   index space needed of one row data assume number of indexes, and
#   size of one index;
#
#   if the table has two indexes:
#
#   the first index has one column, and length of column is 4(bytes)
#   like char(4) or smallint;
#   the second index has two columns, the total length of columns is 12
#
#   so, index space needed of per one row data
#   = ((4+9) + (12+9)) * 1.25 (header) = 42.5 (bytes)
#
#   20000 rows of data for initial table
#   index space needed is
#   = 20000 * 42.5 = 850000 / 1024 = 830 (Kbytes)
#
# data space needed:
#   length of row = 50
#   rows of per pagee = 2020 / 50 = 40
#     ( page size is 2K, but minus header 28 bytes,
#       size of one page is used is 2020 bytes)
#
#   number of data pages for initial table = 20000 / 40 = 500 (pages)
#   total size needed for data = 500 * 2048 / 1024 = 1000 (Kbytes)
#
# initial extent size
#   830 + 1000 = 1830 (Kbytes)
#
# next extent size (table growth)
#   10000 more rows
#   index space needed = 10000 * 42.5 / 1024 = 415 (Kbytes)
#   data space needed  = 10000 / 40 * 2048 / 1024 = 500 (Kbytes)
#   total spcae needed = 415 + 500 = 915 (Kbytes)
#
# -----------------------------------------------------------------------------
#
#
#
# -----------------------------------------------------------------------------
use strict;

my (%conf, %result, %data, %dbs) ;

&chk_para();

if ($conf{table} eq 'ALL') {
   # get all table in the database
   my @oa_tables = &SQL('select tabname, dbinfo("dbspace",partnum) dbs from systables where tabid › 99 order by tabname');
   for my $i(0..$#oa_tables) {
       $dbs{$oa_tables[$i]{tabname}}=$oa_tables[$i]{dbs};
       $result{$oa_tables[$i]{tabname}} = &get_schema($oa_tables[$i]{tabname});
   }
} else {
   my @oa_tables = &SQL('select tabname, dbinfo("dbspace",partnum) dbs from systables where tabname="'.$conf{table}.'"');
   
   $dbs{$oa_tables[0]{tabname}}=$oa_tables[0]{dbs};
   $result{$conf{table}} = &get_schema($conf{table});
}

$data{dbschema}='';
foreach my $tab(sort keys %result){
   $data{dbschema} .= $result{$tab}."\n";
}

my (@a_schema, $table_start, $index_start);
@a_schema = split("\n",$data{dbschema});
$data{schema_tb}='';
$data{schema_idx}='';
for my $i(0..$#a_schema) {
   
   if ( $a_schema[$i] =~ /^create table/ ){
       $table_start=1;
       $index_start=0;
   } elsif ( $a_schema[$i] =~ /^create(.*)index(.*)on(.*)/ ){
       $table_start=0;
       $index_start=1;
   }

   $data{schema_tb} .= $a_schema[$i]."\n" if ($table_start);
   $data{schema_idx} .= $a_schema[$i]."\n" if ($index_start);
}

if ( $conf{output} ) {
   open O,"›$conf{output}";
   print O $data{schema_tb};
   print O $data{schema_idx};
   close O;
}else{
   print $data{schema_tb};
   print $data{schema_idx};
}

sub get_schema{
   my @a_para = @_;

   my (@a_table, $o_rowsize, $o_nrows);
   ## get some information for the table
   @a_table = &SQL('select tabid,tabname,rowsize,nrows from systables where tabname = "'.$a_para[0].'"');
   if (!@a_table) {
       &err("$a_para[0] not exists in $conf{db} database!! please check it !!");
   }
   #rint "Table rowsize: $a_table[0]{rowsize}\n";
   $o_rowsize=$a_table[0]{rowsize};
   $o_nrows=$a_table[0]{nrows};
   $o_nrows=1000 if ( $o_nrows ‹ 1000);

   ## get columns of table from syscolumns
   my (@a_columns, %h_col_len);
   @a_columns = &SQL('select * from syscolumns where tabid='. $a_table[0]{tabid}. ' order by colno' );
   for my $i(0..$#a_columns){
       
       $h_col_len{$a_columns[$i]{colno}}=$a_columns[$i]{collength};
   }

   ## get index length for the table
   my (@a_index, $o_index_len, $o_index_ksize);
   $o_index_len = 0;
   @a_index = &SQL('select * from sysindexes where tabid = '.$a_table[0]{tabid}.'');
   if ( @a_index ) {
       ## has index, calculate the index size
       for my $i(0..$#a_index) {
           ## get  index clono
           for(keys %{$a_index[$i]}) {
               
               if ( $_ =~ /^part/ && $a_index[0]{$_} › 0 ) {
                   $_ =~ s/^part//;
                   
                   $o_index_len+=$h_col_len{$_};
               }
           }
           $o_index_len+=9;
       }
   }
   $o_index_len = $o_index_len*1.25;
   $o_index_ksize = int($o_nrows*$o_index_len/1024)+1;
   
   ## data space needed
   my ($o_table_ksize, $o_page_rows);
   $o_page_rows=int(2020/$o_rowsize)-1;
   $o_page_rows=1 if($o_page_rows‹=1);
   $o_table_ksize=(int($o_nrows/$o_page_rows)+1)*2;
   
   ## table init Kbytes
   my ( $o_extent_size, $o_next_extent_size);
   $o_extent_size = $o_index_ksize+$o_table_ksize;
   $o_next_extent_size=int($o_extent_size/4)+1;
   
   ## get table schema
   my($o_dbschema, $o_start);
   $o_dbschema='';
   $o_start=0;
   open D,"dbschema -d $conf{db} -t $a_para[0] -ss |";
   while(my $str=‹D›) {
       next if ( $str =~ /^revoke/ || $str =~ /^$/);

       if ( $str =~ /^create table/ ) {
           $o_start=1;
       }
       if ( $str =~ /(.*extent size )(.*)( next size )(.*)( lock mode.*)/ ) {
           #$str = $1.$o_extent_size.$3.$o_next_extent_size.$5."\n";
           $str = ") in $dbs{$a_para[0]} extent size $o_extent_size next size $o_next_extent_size; \n";

       }
       $o_dbschema .= $str if( $o_start);
   }
   
   return $o_dbschema;

}

sub SQL{
   my @sql = @_;

   my ($str, @a_str, $first_column, %h_data, @a_data);
   
   open S,"echo '$sql[0]' | dbaccess $conf{db} 2›1|";
   while(‹S›) {
       $str=$_;
       chomp $str;
       next if ($str eq '');
       @a_str=split(' ',$str);
       # determine the first column name
       $first_column = $a_str[0] if (!$first_column);

       if ( $first_column eq $a_str[0] ) {
           $a_data[++$#a_data] = {%h_data} if (%h_data);
           undef %h_data;
       }
       $h_data{$a_str[0]} = $a_str[1];

   }
   close S;
   $a_data[++$#a_data] = {%h_data} if (%h_data);
   undef %h_data;

   return @a_data;

}

# check input parameters
sub chk_para {

   # d database is needed
   &help if $#ARGV ‹ 1 ;

   my ($i);
   # analytics parameters
   for ( $i=0; $i‹= $#ARGV; $i++ ) {
       # database name
       if ( $ARGV[$i] eq '-d' ) {
           $i++ ;
           $conf{db} = $ARGV[$i] ;
           next ;
       }

       # table name
       if ( $ARGV[$i] eq '-t' ) {
           $i++ ;
           $conf{table} = $ARGV[$i] ;
           next ;
       }

       # output file name
       if ( $ARGV[$i] eq '-o' ) {
           $i++;
           $conf{output} = $ARGV[$i] ;
           next ;
       }

   }

   &help if ( $conf{db} eq '' )  ;

   $conf{table} = "ALL" if ( $conf{table} eq '' ) ;

   print 'Database: '. $conf{db} ."\n" ;
   print '   Table: '. $conf{table} ."\n" ;
   print '    File: '. $conf{output} ."\n" ;
}

sub help {
   print '
   usage:
   estimate_table_extent_size.pl -d DBNAME [-t TABLE_NAME] [-o FILENAME]
   -d      database name, required.
   -t      table name or all tables.
   -o      output file name or stnad output.
   ' ;
   print "\n" ;
   exit ;
}

sub err() {
   print "@_\n";
   exit;
}