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;
}