dbaccess commands
A user wanted per-table row counts in IDS with the SQL statement echoed alongside the result, like Oracle's SET ECHO ON. Answers: use dbaccess -e (e.g. dbaccess -e mydb - < script.sql) to echo commands; Jonathan Leffler showed how to generate the COUNT(*) statements from systables and pipe them into a second interpreter (sqlcmd, with -x or a tab delimiter), plus the dbaccess "!echo" trick. Others suggested the cheaper approach of running UPDATE STATISTICS then selecting tabname/nrows from systables (tabid > 99) — Art Kagel noted those counts are only accurate right after update statistics. Resolved with these alternatives.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi, 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 Thanks Pradeep
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>
At: 10/24 11:52
Hi,
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
Thanks
Pradeep
You can use dbaccess -e, that should work.
Regards,
Manoj.
"PRADEEP KUMAR"
<pradeep.kumar@ch
ase.com> To
Sent by: ids@iiug.org
forum.subscriber@ cc
iiug.org
Subject
dbaccess commands [5897]
10/24/2005 10:25
AM
Hi,
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
Thanks
Pradeep
[demime 1.01d removed an attachment of type image/gif which had a name of
graycol.gif]
[demime 1.01d removed an attachment of type image/gif which had a name of
pic03608.gif]
[demime 1.01d removed an attachment of type image/gif which had a name of
ecblank.gif]
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/
So
complicated.
How about:
update statistics;
select tabname, nrows from systables;
cheers
j.
-----Original Message-----
From: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org]On
Behalf Of Jonathan Le....
Sent: Tuesday, October 25, 2005 12:28 AM
To: ids@iiug.org
Subject: Re: dbaccess commands [5904]
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/
Can't you just do this:
select tabname[1,32], nrows from systables
where nrows > 0 and tabid > 99
order by 2
and output to a file from dbaccess?
----- Original Message -----
From: "PRADEEP KUMAR" <pradeep.kumar@chase.com>
To: <ids@iiug.org>
Sent: Monday, October 24, 2005 10:25 AM
Subject: dbaccess commands [5897]
> Hi,
>
> 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
>
>
> Thanks
> Pradeep
That
only works as expected immediately after an update statistics HIGH or LOW.
Art S. Kagel
----- Original Message -----
From: Bill Hamilton <laidback@finsco.com>
At: 10/28 11:25
Can't you just do this:
select tabname[1,32], nrows from systables
where nrows > 0 and tabid > 99
order by 2
and output to a file from dbaccess?
----- Original Message -----
From: "PRADEEP KUMAR" <pradeep.kumar@chase.com>
To: <ids@iiug.org>
Sent: Monday, October 24, 2005 10:25 AM
Subject: dbaccess commands [5897]
> Hi,
>
> 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
>
>
> Thanks
> Pradeep
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