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/infoce...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
"|"

"Rob" <nospam@blueyonder.co.uk> wrote in message
news:drncdd$6u2$1$8300dec7@news.demon.co.uk...
> 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.

In Excel, select "Data / Import External Data / New Database Query".
If this is greyed out, install Microsoft Query from the Office CD.
Uncheck "Use the Query Wizard to create/edit queries".
Choose "<New Data Source>" unless you have an existing one.
In "Add Tables", click Options and enable "System Tables".
Add the "systables" table and close "Add Tables".
Make sure "Tables" and "Criteria" are enabled under "View".
Double click "tabname", "nrows", "rowsize" and "locklevel".
Add criteria "tabid > 99" (excludes system tables).
Add criteria "tabtype = 'T'" (excludes views).
Select "Records / Sort" from the menu and add "tabname".
If you press the SQL button, you should now see:

SELECT systables.tabname, systables.nrows,
systables.rowsize, systables.locklevel
FROM informix.systables systables
WHERE (systables.tabid>99) AND (systables.tabtype='T')
ORDER BY systables.tabname

Select "File / Return Data to Microsoft Excel".

Note that, for "nrows" to be accurate, you will need to run the
SQL statement "update statistics" using "dbaccess" beforehand.

--
Regards,
Doug Lawry
www.douglawry.webhop.org

Thanks for all your help guys sorted now.



"Rob" <nospam@blueyonder.co.uk> wrote in message
news:drncdd$6u2$1$8300dec7@news.demon.co.uk...
> 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.
>
>