Informixのupdate statistics自動生成プログラムをPerlに移植
4GL program was rewritten in Perl & DBI,DBD::Informix modules.
[Informix Tech Notes article on Update Statistics] by Rick Baker
http://www.prstech.com/src/baker_updstat.shtml
Please notice us the points to be improved.
I will modify my programs to satisfy your requests if they are within my ability.
Informixのupdate statisticsを自動生成するプログラムを
4GL&SH&AWKからPerl&DBI&DBD::Informixに移植&機能追加しました。
公開しますのでご活用ください。
[Informix Tech Notes article on Update Statistics] by Rick Baker氏
http://www.prstech.com/src/baker_updstat.shtml
[TechNotes 2000, Volume 9, Issue 2 統計情報を更新するユーティリティの開発]
http://www-06.ibm.com/jp/software/data/informix/support/pdf/tnj00v9i2/chap02.pdf
実行するには-exeオプションを付けます。
> ./updstat.pl -d demodb -exe
ちにみに3つに分割して実行するには次のように実行します。
> ./updstat.pl -d demodb 3 -exe
テーブル名のパターン指定もできるように機能追加しています。
> ./updstat.pl -d demodb -t fo% 3 -exe
#!/usr/local/bin/perl -w # updstat - Generate update statistics SQL files to update # the statistics for a table or an entire database. # # Written by Rick Baker # iirab@brightpoint.com # Brightpoint, Inc. # Indianapolis, IN # # Must be user informix or root to run. # Written to follow the guidelines found in "Performance # Guide for Informix Dynamic Server", Version 7.3, February # 1998: # # update small tables HIGH. # update MEDIUM DISTRIBUTIONS ONLY all columns that # do not head or differentiate an index. Use resolution 1.0, # .99 for very large tables. Use one command per table. # update HIGH all columns that head or differentiate an # index. Use one command for each column. # update LOW for all columns in a multicolumn index. # # ARGUMENTS: # -d <dbname> database name (required) # -t <tabname> table name pattern (optional) # <n> number of SQL files to write (optional) # -a (auto) create one SQL file for each # dbspace containing tables or indexes # -exe Run the sql command files(optional) # # DISCUSSION # # Three different methods of processing the tables are provided. # # 1. Sequential processing. All tables are processed sequentially # in a single SQL command file. # # 2. Multi-stream processing. <n> SQL command files are created. # Each table will require one or more UPDATE STATISTICS # commands. All the commands for a table are sent to one command # file to avoid multiple commands being run against the same # table simultaneously. An attempt is made to balance the workload # for each command file by balancing the "cost" for each table. # "Cost" is based on the number of rows in the table and the # number of columns which must have UPDATE STATISTICS HIGH run # against them. # # 3. Auto-stream generation. An SQL command file is created for # each dbspace which contains tables. The SQL commands for # each table are written to the command file for the dbspace # which contains the table. # # All output from this program is actually written to a single # file. Before each UPDATE STATISTICS command is written to this # file, it is prepended with the number of the SQL command file. # The output file must be read by an awk program and split into # the actual SQL files. # # MODIFICATION HISTORY # # 1/14/99 rab Change auto-stream to put all commands for one # table in the same command file. # Put the "procedures" command in stream 0 and modify # the awk program and shell script to run it after # all other streams have completed. # 1/25/99 rab Change logic for multi-stream: for each table, # evaluate the cost and store cost and commands in # temp tables. Select from the temp tables in order # of cost and assign to streams. (Almost 50% reduction # in overall run time.) # # 3/24/99 rab Add -t option to process a single table. # # 8/19/99 rab Use 7.3 recommendations. # Add nrows to temp table 2 so it is available at # write time. # 7/1/06 takao.nakagawa # Porting 4GL to Perl, DBI, DBD::Informix. # 7/16/06 takao.nakagawa # Add -exe option to run the sql command files. # 7/17/06 takao.nakagawa # Change -t option to like pattern matching. ################################################################## BEGIN { if ( !defined($ENV{SetEnvPid}) || $ENV{SetEnvPid} ne '$$') { $ENV{'INFORMIXDIR'}="/database/informix"; $ENV{'ONCONFIG'}="onconfig.ids"; $ENV{'INFORMIXSERVER'}="ids"; $ENV{'INFORMIXSQLHOSTS'}="/database/informix/etc/sqlhosts"; $ENV{'PATH'}="/database/informix/bin:".$ENV{'PATH'}; $ENV{'PERL5LIB'} = ""; $ENV{SetEnvPid} = '$$'; exec($0,@ARGV); } } use strict; use DBI; use Env qw(DB_LOCALE CLIENT_LOCALE INFORMIXSERVER); #---------------------------------------------- # Global Variables #---------------------------------------------- my $g_dbspace = ""; my %g_file_list = (); # options my $g_dbname = ""; my $g_tabname = ""; my $g_auto = 0; my $g_exe = 0; my $g_numstrm = 1; my @g_colname = (); my $g_nrows = 0; my $g_tabid = 0; my @g_stream = (); my @g_idxrec = (); my $g_currstrm = ""; # updstat list my @g_hi_list = (); my @g_med_list = (); my @g_low_list = (); # sql, dbh, sth my $sql = ""; my $dbh = ""; my $sth = ""; my $sth_sysindexes = ""; my $sth_syscolumns = ""; my $sth_systables = ""; my $sth_ins_updstat1 = ""; my $sth_ins_updstat2 = ""; my $sth_tbl_col_stmt = ""; my $sth_cost_stmt = ""; my $scratch = ""; #---------------------------------------------- # main routine #---------------------------------------------- &main(); exit ; #---------------------------------------------- # main #---------------------------------------------- sub main { &init; &process; &finish; if ($g_exe == 1) { &exe; } } #---------------------------------------------- # init #---------------------------------------------- sub init { while (scalar(@ARGV) > 0) { my $opt = shift @ARGV; if ($opt eq "-d") { $g_dbname = shift @ARGV; } elsif ($opt eq "-t") { $g_tabname = shift @ARGV; } elsif ($opt eq "-a") { $g_auto = 1; $g_numstrm = 0; } elsif ($opt eq "-exe") { $g_exe = 1; } elsif ($opt >= 1 and $opt <= 999) { $g_numstrm = $opt; } else { print STDERR "illegal option : $opt\n"; &usage; exit 1; } } if ( ! scalar($g_dbname) ) { print STDERR "No database name specified. Aborting...\n"; &usage; exit 1; } $dbh = DBI->connect("dbi:Informix:$g_dbname", "informix", "informix", "") or die "OPEN ERROR:" . $DBI::errstr; $sth = $dbh->prepare("set lock mode to wait 100"); $sth->execute(); $sth = $dbh->prepare("set isolation to dirty read"); $sth->execute(); # Create temp tables &create_temp(); # Prepare cursors &init_cursors(); &init_tmp_cursors(); # Check for multiple streams if ($g_numstrm > 0) { &init_streamlist(); } } #---------------------------------------------- # This function creates the temp tables. #---------------------------------------------- sub create_temp { $sql = q{ create temp table updstat1 ( level char(1), tabname char(18), colname char(18) ) with no log }; $sth = $dbh->prepare($sql); $sth->execute(); $sql = q{ create temp table updstat2 ( tabname char(18), dbspace char(3), cost float, nrows integer ) with no log }; $sth = $dbh->prepare($sql); $sth->execute(); } #---------------------------------------------- # This function defines all the cursors used in this module. #---------------------------------------------- sub init_cursors { $sql = q{ select a.tabid, a.tabname, a.nrows, hex(a.partnum), hex(b.partn) from systables a, outer sysfragments b where a.tabtype = 'T' and a.tabid = b.tabid and b.fragtype = 'T' }; if ($g_tabname ne "") { $sql = $sql . " and a.tabname like '" . $g_tabname . "'"; } else { $sql = $sql . " order by tabid"; } $sth_systables = $dbh->prepare($sql); $sql = q{ select colno, colname from syscolumns where tabid = ? order by colno }; $sth_syscolumns = $dbh->prepare($sql); $sql = q{ select part1, part2, part3, part4, part5, part6, part7, part8, part9, part10, part11, part12, part13, part14, part15, part16 from sysindexes where tabid = ? order by part1, part2, part3, part4, part5, part6, part7, part8 }; $sth_sysindexes = $dbh->prepare($sql); } #---------------------------------------------- # This function initializes the stream list. If a specified number # of streams are to be used, the cost field is set to zero for # the requested number of elements. #---------------------------------------------- sub init_streamlist { for (my $i=0; $i < $g_numstrm; $i++) { $g_stream[$i]->{"cost"} = 0; } } #---------------------------------------------- # This function loops through each table in the database. It calls # functions to get the column name and index data, identify which # columns need to be specified in which SQL statements, determine # the SQL file, and save the data in the temp tables. After all # tables have been processed, a function is called to write the # SQL command file. #---------------------------------------------- sub process { my $prev_tabid = 0; my $partnum_p = ""; my $partnum_f = ""; $sth_systables->execute(); if (!defined($sth_systables)) { die "error : prepare : \n"; } # Loop through each table while (my @rows = $sth_systables->fetchrow_array()) { $g_tabid = $rows[0]; $g_tabname = $rows[1]; $g_nrows = $rows[2]; $partnum_p = $rows[3]; $partnum_f = $rows[4]; if ($g_tabid == $prev_tabid) { next; } # Set the partnum if ($partnum_p eq "") { $g_dbspace = substr($partnum_f,4,3); } else { $g_dbspace = substr($partnum_p,4,3); } # Process small tables separately if ($g_nrows <= 1000) { &small_table(); next; } # Load the column names &get_colnames(); # Load the index definitions &get_indexes(); # Build the column lists &build_lists(); # Save the data in the temp tables &save_data(); #Save the tabid $prev_tabid = $g_tabid; @g_hi_list = (); @g_med_list = (); @g_low_list = (); } # Write the SQL statements &write_stmts(); } #---------------------------------------------- # This function loads the array g_colname with the names of each # column in the current table. #---------------------------------------------- sub get_colnames { my $numcols = 0; @g_colname = (); $sth_syscolumns->execute($g_tabid); if (!defined($sth_syscolumns)) { die "error : prepare : $g_tabid\n"; } while (my @rows = $sth_syscolumns->fetchrow_array()) { $g_colname[$numcols] = $rows[1]; $numcols++; } } #---------------------------------------------- # This function loads the array g_idxrec with the definition of # each index defined for the current table. #---------------------------------------------- sub get_indexes { my $numidx = 0; @g_idxrec = (); $sth_sysindexes->execute($g_tabid); if (!defined($sth_sysindexes)){ die "error : prepare : $g_tabid\n"; } while (my @rows = $sth_sysindexes->fetchrow_array()) { $g_idxrec[$numidx]->{"col1"} = $rows[0]-1; $g_idxrec[$numidx]->{"col2"} = $rows[1]-1; $g_idxrec[$numidx]->{"col3"} = $rows[2]-1; $g_idxrec[$numidx]->{"col4"} = $rows[3]-1; $g_idxrec[$numidx]->{"col5"} = $rows[4]-1; $g_idxrec[$numidx]->{"col6"} = $rows[5]-1; $g_idxrec[$numidx]->{"col7"} = $rows[6]-1; $g_idxrec[$numidx]->{"col8"} = $rows[7]-1; $g_idxrec[$numidx]->{"col9"} = $rows[8]-1; $g_idxrec[$numidx]->{"col10"} = $rows[9]-1; $g_idxrec[$numidx]->{"col11"} = $rows[10]-1; $g_idxrec[$numidx]->{"col12"} = $rows[11]-1; $g_idxrec[$numidx]->{"col13"} = $rows[12]-1; $g_idxrec[$numidx]->{"col14"} = $rows[13]-1; $g_idxrec[$numidx]->{"col15"} = $rows[14]-1; $g_idxrec[$numidx]->{"col16"} = $rows[15]-1; $numidx++; } } #---------------------------------------------- # This function updates statistics for all procedures and then # closes the report file. #---------------------------------------------- sub finish { $sth->finish; $sth_sysindexes->finish; $sth_syscolumns->finish; $sth_systables->finish; $sth_ins_updstat1->finish; $sth_ins_updstat2->finish; $sth_tbl_col_stmt->finish; $sth_cost_stmt->finish; $dbh->disconnect; $g_currstrm = 0; $scratch = "update statistics for procedure;"; &report; } #---------------------------------------------- # This function creates cursors to be used with the temp tables. #---------------------------------------------- sub init_tmp_cursors { $sql = "insert into updstat1 values(?, ?, ?)"; $sth_ins_updstat1 = $dbh->prepare($sql); $sql = "insert into updstat2 values(?, ?, ?, ?)"; $sth_ins_updstat2 = $dbh->prepare($sql); $sql = "select colname from updstat1 where tabname = ? and level = ?"; $sth_tbl_col_stmt = $dbh->prepare($sql); $sql = "select tabname, dbspace, cost, nrows from updstat2 order by cost desc"; $sth_cost_stmt = $dbh->prepare($sql); } #---------------------------------------------- # This function creates the lists of columns in the current table # that need high, medium, and low commands. #---------------------------------------------- sub build_lists { # Build the lists &build_hi_list; &build_med_list; &build_low_list; } #---------------------------------------------- # This function creates the list of columns in the current table # that need HIGH statistics: those that head an index and those # that differentiate an index. #---------------------------------------------- sub build_hi_list { # Build list of columns that head an index for (my $i=0; $i < scalar(@g_idxrec); $i++) { &add_to_hi_list($g_idxrec[$i]->{"col1"}); } # Add columns that differentiate indexes that have the same # starting column(s). # (Use the table's dbspace). for (my $i=1; $i < scalar(@g_idxrec); $i++) { if ($g_idxrec[$i]->{"col1"} != $g_idxrec[$i-1]->{"col1"}) {next;} for (my $j=2; $j < 17; $j++) { my $colname = "col".$j; if ($g_idxrec[$i]->{$colname} != $g_idxrec[$i-1]->{$colname}) { &add_to_hi_list($g_idxrec[$i]->{$colname}); } } } } #---------------------------------------------- # This function creates the list of columns in the current table # that need MEDIUM statistics: all columns in indexes that are # not already identified as needing HIGH statistics. #---------------------------------------------- sub build_med_list { # Build the list for (my $i=0; $i < scalar(@g_idxrec); $i++) { &add_to_med_list($g_idxrec[$i]->{"col1"}); for (my $j=2; $j < 17; $j++) { my $colname = "col".$j; if ($g_idxrec[$i]->{$colname} == 0) {last;} &add_to_med_list($g_idxrec[$i]->{$colname}); } } } #---------------------------------------------- # This function creates the list of columns in the current table # that need LOW statistics: all columns in multicolumn indexes. #---------------------------------------------- sub build_low_list { # Build the list for (my $i=0; $i < scalar(@g_idxrec); $i++) { if ($g_idxrec[$i]->{"col2"} == 0) {next;} &add_to_low_list($g_idxrec[$i]->{"col1"}); &add_to_low_list($g_idxrec[$i]->{"col2"}); for (my $j=3; $j < 17; $j++) { my $colname = "col".$j; if ($g_idxrec[$i]->{$colname} == 0) {last;} &add_to_low_list($g_idxrec[$i]->{$colname}); } } } #---------------------------------------------- # This function creates the SQL statement for a small table. #---------------------------------------------- sub small_table { # Insert record for HIGH command $sth_ins_updstat1->execute("H", $g_tabname, ""); if (!defined($sth_ins_updstat1)) { die "error : prepare : $g_tabname\n"; } # Insert cost record my $cost = 0.001 * $g_nrows + 0.2952; $sth_ins_updstat2->execute($g_tabname, $g_dbspace, $cost, $g_nrows); if (!defined($sth_ins_updstat2)) { die "error : prepare : $g_tabname, $g_dbspace, $cost, $g_nrows\n"; } } #---------------------------------------------- # This function adds a column to the list of columns that are to # be updated HIGH if the column is not already in the list. #---------------------------------------------- sub add_to_hi_list { my $colnum = $_[0]; # Reverse sign if negative (negative column numbers in sysindexes # indicate that the column is sorted descending instead of ascending) if ($colnum < 0) { $colnum = -$colnum; } # Check the current list for (my $i=0; $i < scalar(@g_hi_list); $i++) { if ($colnum == $g_hi_list[$i]) { #Column is already in the list: exit the function return; } } # Column not found: add to list $g_hi_list[scalar(@g_hi_list)] = $colnum; } #---------------------------------------------- # This function adds a column to the list of columns that are to # be updated MEDIUM if the column is not already in the HIGH list # and not already in the MEDIUM list. #---------------------------------------------- sub add_to_med_list { my $colnum = $_[0]; # Reverse sign if negative (negative column numbers in sysindexes # indicate that the column is sorted descending instead of # ascending) if ($colnum < 0) { $colnum = -$colnum; } # Check the HIGH list for (my $i=0; $i < scalar(@g_hi_list); $i++) { if ($colnum == $g_hi_list[$i]) { # Column is already in the HIGH list: exit the function return; } } # Check the MEDIUM list for (my $i=0; $i < scalar(@g_med_list); $i++) { if ($colnum == $g_med_list[$i]) { # Column is already in the MEDIUM list: exit the function return; } } # Column not found: add to list $g_med_list[scalar(@g_med_list)] = $colnum; } #---------------------------------------------- # This function adds a column to the list of columns that are to # be updated LOW if the column is not already in the list. #---------------------------------------------- sub add_to_low_list { my $colnum = $_[0]; # Reverse sign if negative (negative column numbers in sysindexes # indicate that the column is sorted descending instead of ascending) if ($colnum < 0) { $colnum = -$colnum; } # Check the current list for (my $i=0; $i < scalar(@g_low_list); $i++) { if ($colnum == $g_low_list[$i]) { # Column is already in the LOW list: exit the function return; } } # Column not found: add to list $g_low_list[scalar(@g_low_list)] = $colnum; } #---------------------------------------------- # This function saves the info for one table in the temp tables. # The formulas used to compute costs are based on empirical data. #---------------------------------------------- sub save_data { my $cost = 0; my $colno = 0; if ($g_numstrm > 0) { $cost = 0; } # Insert records for HIGH commands for (my $i=0; $i < scalar(@g_hi_list); $i++) { $colno = $g_hi_list[$i]; $sth_ins_updstat1->execute("H", $g_tabname, $g_colname[$colno]); if (!defined($sth_ins_updstat1)) { die "error : prepare : H, $g_tabname, $g_colname[$colno]\n"; } } # $cost = $cost + (0.00009 * $g_nrows - 9.5925) * scalar(@g_hi_list); # Insert records for MEDIUM commands for (my $i=0; $i < scalar(@g_med_list); $i++) { $colno = $g_med_list[$i]; $sth_ins_updstat1->execute("M", $g_tabname, $g_colname[$colno]); if (!defined($sth_ins_updstat1)) { die "error : prepare : M, $g_tabname, $g_colname[$colno]\n"; } } # $cost = $cost + 0.00001 * $g_nrows + 27.336; # Insert records for LOW commands for (my $i=0; $i < scalar(@g_low_list); $i++) { $colno = $g_low_list[$i]; $sth_ins_updstat1->execute("L", $g_tabname, $g_colname[$colno]); if (!defined($sth_ins_updstat1)) { die "error : prepare : L, $g_tabname, $g_colname[$colno]\n"; } } $cost = 0.0001 * $g_nrows + 0.9661; # Insert cost record $sth_ins_updstat2->execute($g_tabname, $g_dbspace, $cost, $g_nrows); if (!defined($sth_ins_updstat2)) { die "error : prepare : $g_tabname, $g_dbspace, $cost, $g_nrows\n"; } } #---------------------------------------------- # This function reads the info from the temp tables and creates # the SQL statements. #---------------------------------------------- sub write_stmts { my $cost = 0; # For each record in the cost table $sth_cost_stmt->execute(); if (!defined($sth_cost_stmt)) { die "error : prepare : \n"; } while (my @rows = $sth_cost_stmt->fetchrow_array()) { $g_tabname = $rows[0]; $g_dbspace = $rows[1]; $cost = $rows[2]; $g_nrows = $rows[3]; #Set the output stream $g_currstrm = &set_stream($g_dbspace, $cost); #Write the statements &write_medium; &write_high; &write_low; } } #---------------------------------------------- # This function writes the updates statistics high commands for # all columns in the g_hi_list. #---------------------------------------------- sub write_high { # Process the list of columns my $level = "H"; $sth_tbl_col_stmt->execute($g_tabname, $level); if (!defined($sth_tbl_col_stmt)) { die "error : prepare : $g_tabname, $level\n"; } while (my @rows = $sth_tbl_col_stmt->fetchrow_array()) { my $colname = $rows[0]; $scratch = "update statistics high for table " . &rmspace($g_tabname); if ($colname =~ m/^\s*$/) { $scratch = $scratch . ";"; } else { $scratch = $scratch . "(" . &rmspace($colname) . ");"; } &report; } } #---------------------------------------------- # This function writes the updates statistics medium commands for # all columns in the g_med_list. If the table is large, distribu- # tion and confidence are specified. #---------------------------------------------- sub write_medium { # Start the update statistics statement $scratch= "update statistics medium for table " . &rmspace($g_tabname) . "("; my $first = 1; # Append the column names my $level = "M"; $sth_tbl_col_stmt->execute($g_tabname, $level); if (!defined($sth_tbl_col_stmt)) { die "error : prepare : $g_tabname, $level\n"; } while (my @rows = $sth_tbl_col_stmt->fetchrow_array()) { my $colname = $rows[0]; if ($first == 1) { $scratch = $scratch . &rmspace($colname); $first = 0; } else { $scratch = $scratch . "," . &rmspace($colname); } } if ($first == 1) { # No records found return; } $scratch = $scratch . ")"; # Check for "large" tables if ($g_nrows >= 100000) { $scratch = $scratch . " resolution 1.00 0.99" } $scratch = $scratch . " distributions only;"; &report; } #---------------------------------------------- # This function writes the updates statistics low commands for # all columns in the g_low_list. #---------------------------------------------- sub write_low { # Start the update statistics statement $scratch = "update statistics low for table " . &rmspace($g_tabname) . "("; my $first = 1; # Append the column names my $level = "L"; $sth_tbl_col_stmt->execute($g_tabname, $level); if (!defined($sth_tbl_col_stmt)) { die "error : prepare : $g_tabname, $level\n"; } while (my @rows = $sth_tbl_col_stmt->fetchrow_array()) { my $colname = $rows[0]; if ($first == 1) { $scratch = $scratch . &rmspace($colname); $first = 0; } else { $scratch = $scratch . "," . &rmspace($colname); } } if ($first == 1) { # No records found return; } $scratch = $scratch . ");"; &report; } #---------------------------------------------- # This function sets the global variable g_currstrm with the # stream or output file number for the current statement. For # auto_stream, the dbspace provided as a parameter determines the # stream. For fixed multi-stream case, the stream is selected by # determining which stream has the least cost so far. The cost # for the selected stream is updated to include the cost of the # current table. #---------------------------------------------- sub set_stream { my $dbspace = $_[0]; my $cost = $_[1]; # Handle auto-stream if ($g_auto == 1) { # See if the dbspace is in the array for (my $i=0; $i < $g_numstrm; $i++) { if ($dbspace eq $g_stream[$i]->{"name"}) { return $i; } } # Not found: add to the array $g_currstrm = $g_numstrm; $g_stream[$g_numstrm]->{"name"} = $dbspace; $g_numstrm++; return $g_numstrm; } # Handle single stream if ($g_numstrm == 0) { return 0; } # Handle multi-stream my $mincost = $g_stream[0]->{"cost"}; my $mincost_strm = 0; for (my $i=1; $i < $g_numstrm; $i++) { if ($g_stream[$i]->{"cost"} < $mincost) { $mincost = $g_stream[$i]->{"cost"}; $mincost_strm = $i; } } # Update the cost $g_stream[$mincost_strm]->{"cost"} = $g_stream[$mincost_strm]->{"cost"} + $cost; return $mincost_strm; } #---------------------------------------------- # output update statistics sql files #---------------------------------------------- sub report { my $cmd; my $filename; # create file name if ($g_auto == 1 || $g_numstrm > 1) { $filename = sprintf("updstat.%s.%03d.sql",$g_dbname,$g_currstrm); } else { $filename = "updstat." . $g_dbname . ".sql"; } # new file name if (!defined($g_file_list{$filename})) { # delete file unlink($filename); $g_file_list{$filename} = 1; $cmd = "echo 'set lock mode to wait;' >> ". $filename; system($cmd); } $cmd = "echo '$scratch' >> ". $filename; system($cmd); } #---------------------------------------------- # execute sql files #---------------------------------------------- sub exe { my $cmd; my @filenames = sort keys %g_file_list; foreach my $filename(@filenames) { $cmd = "dbaccess $g_dbname $filename > $filename.out 2>&1 &"; system($cmd); } } #---------------------------------------------- # show usage #---------------------------------------------- sub usage { print "\nUsage: $0 -d <dbname> [-t <tbname>] [<n> | -a] [-exe]\n"; print " -d <dbname> database name (required)\n"; print " -t <tabname> table name pattern (optional)\n"; print " <n> number of SQL files to write (optional)\n"; print " -a (auto) create one SQL file for each\n"; print " dbspace containing tables or indexes\n"; print " -exe Run the sql command files(optional)\n"; print "\nexample:\n"; print " $0 -d demodb\n"; print " $0 -d demodb -exe\n"; print " $0 -d demodb -t foo\n"; print " $0 -d demodb -t fo%\n"; print " $0 -d demodb -t fo% 2\n"; print " $0 -d demodb 3 -exe\n"; print " $0 -d demodb -a\n"; } #---------------------------------------------- # remove space #---------------------------------------------- sub rmspace { my $str = shift; $str =~ s/^\s*//; $str =~ s/\s*$//; return $str; }