data model
Rob asked how to use system tables to produce a spreadsheet per database with table name, row count, size and lock mode. Obnoxio suggested ODBC with a query tool. Scottishpoet pointed to the manuals and later suggested unloading systables from dbaccess and opening it in Excel with a | delimiter. Another poster gave a Perl DBI and Spreadsheet::WriteExcel script reading systables.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
How would I use the system tables to generate an excel spreadsheet for each database in an informix instance? I need the table name, number of rows, Raw size and lock mode Thank you in advance.
Rob said: > > How would I use the system tables to generate an excel spreadsheet for > each > database in an informix instance? > I need the table name, number of rows, Raw size and lock mode Set up an ODBC connection. Use the query tool to generate the select statement. Execute. -- Bye now, Obnoxio "C'est pas parce qu'on n'a rien ' dire qu'il faut fermer sa gueule" - Coluche did i mention i like nulls? heck, i even go so far as to say that all columns in a table except the primary key could/should be nullable. this has certain advantages, for example, if you need to insert a child record and you don't have a parent row for it, just do an insert into the parent table with the primary key value (everything else null), and voila, relational integrity is preserved. but this is, admittedly, a bit controversial among modellers. --r937, dbforums.com
IBM Informix online manuals are pretty good! Check out : http://publib.boulder.ibm.com/infocenter/idshelp/v10/topic/com.ibm.sqlr.doc/sqlrmst81.htm?resultof=%
with perl and 2 modules use DBI; use Spreadsheet::WriteExcel; my $db="test"; my $dbh = DBI->connect("DBI:Informix:".$db); my $sth = $dbh->prepare("SELECT tabname, nrows,rowsize FROM systables WHERE tabid>99") or die; $sth->execute(); my @cols = ( \\$tabname, \\$nrows, \\$rowsize ); $sth->bind_columns(undef, @cols); my $wk = Spreadsheet::WriteExcel->new($db.".xls"); my $ws = $wk->add_worksheet("database ".$db); my $format = $wk->add_format(); $format->set_align('left'); my $row=0; while ( $sth->fetch() ) { print $tabname, $nrows, $rowsize,"\\n"; $ws->write($row,0, $tabname); $ws->write($row,1, $nrows); $ws->write($row,2, $rowsize); $row++; } regards,
in dbaccess :
unload to "<dbname>.xls"
select * from systables
then open the file excel , remember to change the delimiter in excel to
"|"