Re: how to list all databases?
This is a multi-part message in MIME format.
--------------1DC5133E2E11
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Heckers wrote:
>
> We used to write an esql/c program to do an
> update statistics medium every night.> But we have to support a lot of different platforms
> now and so I wrote a ksh-script, that does the
> same job.
> I use oncheck -pc to get a list of all all databases
> and use this list to do an update statistics with
> dbaccess.
> But oncheck is very slow!
> Who knows a faster method, I don't know much about the
> system tables, but maybe there is a better method using
> dbschema etc..
Try the attached listdb.ec, written by Art Taylor of Informix, it works
on all versions of Informix.
BTW: I have also attached my stats update program, dostats.ec, which
when compiled under Informix ESQL 7.xx can be run against any Informix
version on any platform remotely or locally using a network connection
(it uses multiple connections and cannot use a shared memory
connection).
It understands the various syntax requirements of 5.xx, 6.00 and 7.xx
and can be configured to execute the recommended suite of statistics
updates in the fastest possible way or any subset (like just MEDIUM or
LOW). It handles multiple databases and tables using matches wild cards.
Since it can work against remote hosted databases you do not need to
worry about heterogeneous OS's/hardware/Informix versions.
Art S. Kagel
--------------1DC5133E2E11
Content-Type: text/plain; charset=us-ascii; name="listdb.ec"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline; filename="listdb.ec"
/*
sqgetdbs(ret_fcnt, fnames, fnsize, farea, fasize)
int *ret_fcnt; return file count
char **fnames; array to hold file names
int fnsize; size of the file name array
char *farea; area for names
int fasize; area size
*/
#include <stdio.h>
#define DB_MAX 50
#define DB_SIZE 30
main(argc, argv)
int argc;
char *argv[];
{
$char remote_db[40];
int ret_fcnt, n;
char **dbnames;
char *namearea;
if (argc == 1)
sqlstart();
else { /* assert one arg = dbname@ site */
EXEC SQL whenever error stop;
strcpy( remote_db, argv[1] );
EXEC SQL database :remote_db; /* access the remote site */
}
namearea = (char *)calloc( (int )DB_MAX, (int )DB_SIZE );
dbnames = (char **)calloc( (int )DB_MAX, (int )DB_SIZE );
sqgetdbs( &ret_fcnt, dbnames, DB_MAX, namearea, (DB_MAX * DB_SIZE) );
printf("There area currently %d databases available : \\n\\n", ret_fcnt );
printf("Number \\tName \\n");
printf("====== \\t==== \\n");
for ( n = 0; n < ret_fcnt; n++ )
printf("%d - \\t%s \\n", n, *dbnames++ );
}
--------------1DC5133E2E11
Content-Type: text/plain; charset=us-ascii; name="dostats.ec"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline; filename="dostats.ec"
/*
Program Id: dostats
Program Description: this program will generate sql to update statistics
on a table or database;
Author: Art S. Kagel
Creation Date: May 16, 1996
Version: $Id: dostats.ec,v 1.8 1997/07/16 15:14:26 kagel Exp $
Usage: dostats [-h hostname] -d databasename [-t tablename] [-f filename]
See usage function for current usage.
RCS Header
----------
$Header: /home/kagel/utils/RCS/dostats.ec,v 1.8 1997/07/16 15:14:26 kagel Exp $
RCS Log
-------
$Log: dostats.ec,v $
# Revision 1.8 1997/07/16 15:14:26 kagel
# Newest version with 5.0 compatibility.
#
# Revision 1.7 1997/06/25 16:30:55 kagel
# Added -L option to force statistics low on the entire table.
#
# Revision 1.7 1997/06/25 16:30:55 kagel
# Added -L option to force statistics low on the entire table.
#
# Revision 1.6 1997/05/02 17:20:51 kagel
# Added DISTRIBUTIONS ONLY clause to MEDIUM and HIGH stats updates to make them run faster.
#
# Revision 1.5 1996/11/20 16:38:43 kagel
# *** empty log message ***
#
# Revision 1.2 1996/11/19 22:59:36 kagel
# New revision. Handles updating secondary index columns to low, outputs
# database statements to scripts, qualifies table names with database name
# so that tables and columns named after keywords do not cause an error.
# Also made sure that SQL output file is flushed and closed on an interrupt.
# I think that that is it.
#
# Revision 1.1 1996/10/23 21:23:23 kagel
# Initial revision
#
*/
#include <stdio.h>
#include <stdlib.h>
#include <sys/types.h>
#include <errno.h>
#ifdef i386
#include <string.h>
#else
#include <strings.h>
#endif
#include <sys/signal.h>
EXEC SQL INCLUDE sqlca;
EXEC SQL INCLUDE sqltypes;
EXEC SQL INCLUDE datetime;
static char rcs_header[] = "$Header: /home/kagel/utils/RCS/dostats.ec,v 1.8 1997/07/16 15:14:26 kagel Exp $";
struct sqlca_s sqlca;
extern int errno;
EXEC SQL BEGIN DECLARE SECTION;
struct {
int tabid;
string tabname[19];
short colno[16], colind;
} tabinfo;
string databasename[19], host[20], connhost[22], statement[2048], statement2[2048];
string tablename[19], columnnames[32768][19];
char tempstr[1024];
short colno;
int NoDistOnly = 0; /* If TRUE do not include 'DISTRIBUTIONS ONLY' clause. */
int NoLevel = 0; /* If TRUE do not include statistics level keywords. */
EXEC SQL END DECLARE SECTION;
extern int optind;
extern char *optarg;
int conn = 0, Release_5 = 0;
char *a_space, *strchr();
char dtparts[7][10] = { "YEAR",
"MONTH",
"DAY",
"HOUR",
"MINUTE",
"SECOND",
"FRACTION" };
FILE *outfile = NULL;
int dotable = 1, doindex = 1, docolumn = 1, tabmedium = 1;
#define TBSFACT 16777216L
#define FALSE (0)
#define TRUE (!FALSE)
#define Version "2.2"
#define Revision "$Revision: 1.8 $"
void usage( char *program );
void print_auths( FILE * );
void print_indexes( FILE * );
void exit( int stat );
void shutdown( int sig )
{
sqlbreak();
if (conn == 1 && !Release_5) {
EXEC SQL SET CONNECTION "WORK";
sqlbreak();
} else if (!Release_5) {
EXEC SQL SET CONNECTION "DBASES";
sqlbreak();
}
EXEC SQL DISCONNECT ALL;
if (outfile != (FILE *)NULL) {
fflush( outfile );
fclose( outfile );
}
exit( 1 );
}
main(int argc, char *argv[])
{
int i, c, errflag=0, dflag=0, tflag=0, hflag=0;
long alldb = 0;
char lasttbl[19], filename[1024], *h;
short columns[32768];
long ncols, column;
int tableset = 0, indexset = 0, columnset = 0;
signal( SIGINT, shutdown );
signal( SIGQUIT, shutdown );
strcpy( tablename, "*" );
host[0] = (char)0;
while ((c = getopt(argc, argv, "56Dd:t:h:f:TICVL")) != EOF){
switch(c) {
case