IDS Forum

Re: dbaccess commands

Posted By: Cesar Cruz
Date: Monday, 31 October 2005, at 9:33 p.m.

In Response To: Re: dbaccess commands (Jonathan Leffler )

Hi,

May be you can try something like this:

echo "select name from sysdatabases where name not like 'sys%' order by 1;" | dbaccess sysmaster -
2>/dev/null | grep -v -E 'name|^$'

You can get a list of all databases in the instance.

Regards,



--- "Jonathan Le...." <jleffler.iiug@gmail.com> escribis:

> On 10/24/05, ART KAGEL, .... <kagel@bloomberg.net> wrote:
> >
> > The -e option to dbaccess enables command echo.
> >
> > dbaccess -e mydatabase - <sql_script.sql
> >
> > Art S. Kagel
> >
> > ----- Original Message -----
> > From: Pradeep Kumar <pradeep.kumar@chase.com>
> > I need to do a row count of each table in the IDS database. I want to
> > "echo" the
> > "select statement" and then get the count. This is equlivant of "set echo
> > on" of
> > 'SQL>' utility of a oracle rdbms.
> >
> > In a nut shell, I need the following output from a informix sql statement.
> >
> > SQL> select count(*) from case ;
> >
> > COUNT(*)
> > ----------
> > 106
> >
>
>
> Or, remembering to bottom post...
>
> Consider this SQL:
>
> SELECT 'SELECT "', s.tabname, '", COUNT(*) FROM ', s.tabname, ' GROUP BY 1;'
> FROM "informix".systables s
> WHERE tabtype = 'T' AND tabid >= 100;
>
> This should generate a series of SELECT statements like:
>
> SELECT "mytable", COUNT(*) FROM mytable GROUP BY 1;
>
> You then need to feed this into a second command interpreter to get the
> answers...
>
> sqlcmd -D' ' -d yourdb -f - <<'!' | sqlcmd -d yourdb
>
> SELECT 'SELECT "', s.tabname, '", COUNT(*) FROM ', s.tabname, ' GROUP BY 1;'
> FROM "informix".systables s
> WHERE tabtype = 'T' AND tabid >= 100;
>
> !
>
> The -D' ' (that's a quoted tab) uses tab as the separator between fields.
> Using blanks would be bad; you'd get backslash blank for each of the other
> blanks in the output.
>
> This gives you tablename and count on one line - at the cost of that
> wretched GROUP BY.
>
> Alternatively, drop the constant table name string, drop the group by, and
> use sqlcmd -x:
>
> sqlcmd -D' ' -d yourdb -f - <<'!' | sqlcmd -d yourdb -x 2>&1
>
> SELECT 'SELECT COUNT(*) FROM ', s.tabname, ';'
> FROM "informix".systables s
> WHERE tabtype = 'T' AND tabid >= 100;
>
> !
>
> This will give you alternating lines of output with the SELECT statement
> identifying the table and then the result count.
>
> The difference between my answer and Art's is that Art shows you how to get
> the answers after you've generated the SQL; mine generates the SQL for you
> as well as giving you the answer.
>
> There are other ways - of course. For example:
>
> dbaccess yourdb - <<EOF
> !echo "table"
> select count(*) from table;
> EOF
>
> When the input comes from a file, you can use the !echo notation to run the
> echo command in DB-Access (or, since I'm at home without an operational
> DB-Access to validate it with - that was true the last time I tried it,
> maybe a couple of years ago now).
>
> Depending on how critical the counts are, you could consider running update
> statistics low and then selecting table name and nrows from systables.
>
> You could probably post-process the output from an oncheck command, too.
>
> There might well be a query on sysmaster that would work - or would work if
> you ran update statistics.
>
> These are not necessarily good options - they are just other ways of
> answering the same question.
> --
> Jonathan Leffler #include <disclaimer.h>
> Email: jleffler@earthlink.net, jleffler@us.ibm.com
> Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/
>
>






___________________________________________________________
Do You Yahoo!?
La mejor conexisn a Internet y <b >2GB</b> extra a tu correo por $100 al mes. http://net.yahoo.com.mx

Messages In This Thread

• dbaccess commands
  PRADEEP KUMAR -- Monday, 24 October 2005, at 11:25 a.m.
  • Re: dbaccess commands
    ART KAGEL, BLOOMBERG/ 731 LEXIN -- Monday, 24 October 2005, at 12:02 p.m.
    • Re: dbaccess commands
      Jonathan Leffler -- Tuesday, 25 October 2005, at 12:27 a.m.
      • RE: dbaccess commands
        Jack Parker -- Tuesday, 25 October 2005, at 4:51 a.m.
      • Re: dbaccess commands
        Cesar Cruz -- Monday, 31 October 2005, at 9:33 p.m.
  • Re: dbaccess commands
    Manoj Mohan -- Monday, 24 October 2005, at 1:59 p.m.
  • Re: dbaccess commands
    Bill Hamilton -- Friday, 28 October 2005, at 11:02 a.m.
    • Re: dbaccess commands
      ART KAGEL, BLOOMBERG/ 731 LEXIN -- Friday, 28 October 2005, at 11:34 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.