output-format of select in sysdatabases

Posted in 2008

Summary

Werner wanted dbaccess output from a sysmaster query (database name plus is_buff_log) formatted as simple one-line-per-row text instead of dbaccess's stacked label/value layout, without post-processing through awk. Several workarounds were offered: use CASE and/or SUBSTR to shorten the 128-char varchar name; concatenate columns with trim(name) || ' ' || is_buff_log; use UNLOAD TO with a delimiter (writing to a file or /dev/tty); use Jonathan Leffler's sqlcmd from the IIUG; and Malcolm noted dbaccess's SELECT ... WITHOUT HEADINGS clause combined with substrings does it natively. No single choice was confirmed by the poster.

Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.

Topics: Server Administration

wjb — Tue, 8 Jan 2008 10:30:51 -0800 (PST) — source: Usenet: comp.databases.informix

Hello, I would like to automate the task "show me wether my databases are in buffered logging mode". The command echo "select name, is_buff_log from sysdatabases \\ where name not in ('sysmaster','sysutils','stores_demo', \\ 'sysuser') order by name;" | dbaccess sysmaster returns: ======================== Database selected. name db1 is_buff_log 1 name db2 is_buff_log 0 name db3 is_buff_log 1 3 row(s) retrieved. Database closed. ======================== Is there a select-option etc. to get the result in this form without an awk-call: db1 1 db2 0 db3 1 Thank you Werner

↪ replying to wjb

Madison Pruet — Tue, 08 Jan 2008 18:44:38 GMT — source: Usenet: comp.databases.informix

wjb wrote: > Hello, > > I would like to automate the task "show me wether my databases are in > buffered logging mode". > > The command > echo "select name, is_buff_log from sysdatabases \\ > where name not in ('sysmaster','sysutils','stores_demo', \\ > 'sysuser') order by name;" | dbaccess sysmaster > returns: > > ======================== > Database selected. > > name db1 > is_buff_log 1 > > name db2 > is_buff_log 0 > > name db3 > is_buff_log 1 > > 3 row(s) retrieved. > > Database closed. > ======================== > > Is there a select-option etc. to get the result in this form without > an awk-call: Why not use the case clause in the select statement? > > db1 1 > db2 0 > db3 1 > > Thank you > Werner >

↪ replying to Madison Pruet

Madison Pruet — Tue, 08 Jan 2008 19:06:23 GMT — source: Usenet: comp.databases.informix

Madison Pruet wrote: > wjb wrote: >> Hello, >> >> I would like to automate the task "show me wether my databases are in >> buffered logging mode". >> >> The command >> echo "select name, is_buff_log from sysdatabases \\ >> where name not in ('sysmaster','sysutils','stores_demo', \\ >> 'sysuser') order by name;" | dbaccess sysmaster >> returns: >> >> ======================== >> Database selected. >> >> name db1 >> is_buff_log 1 >> >> name db2 >> is_buff_log 0 >> >> name db3 >> is_buff_log 1 >> >> 3 row(s) retrieved. >> >> Database closed. >> ======================== >> >> Is there a select-option etc. to get the result in this form without >> an awk-call: > > Why not use the case clause in the select statement? Dumb ole me. You need to select a substring of the database name. Otherwise it's a 128 varchar. > >> >> db1 1 >> db2 0 >> db3 1 >> >> Thank you >> Werner >>

↪ replying to wjb

Andrew Ford — Tue, 8 Jan 2008 13:19:24 -0600 — source: Usenet: comp.databases.informix

> Hello, > > I would like to automate the task "show me wether my databases are in > buffered logging mode". > > The command > echo "select name, is_buff_log from sysdatabases \\ > where name not in ('sysmaster','sysutils','stores_demo', \\ > 'sysuser') order by name;" | dbaccess sysmaster > returns: > > ======================== > Database selected. > > name db1 > is_buff_log 1 > > name db2 > is_buff_log 0 > > name db3 > is_buff_log 1 > > 3 row(s) retrieved. > > Database closed. > ======================== > > Is there a select-option etc. to get the result in this form without > an awk-call: > > db1 1 > db2 0 > db3 1 > > Thank you > Werner > You can use the concatenate operator like so: echo "select trim(name) || ' ' || is_buff_log from sysdatabases \\ where name not in ('sysmaster','sysutils','stores_demo', \\ 'sysuser') order by name;" | dbaccess sysmaster

↪ replying to wjb

Mike Aubury — Tue, 8 Jan 2008 19:50:29 +0000 — source: Usenet: comp.databases.informix

I'll just throw in the unload too... echo "unload to '/tmp/somefile' delimiter ' ' select name, is_buff_log from sysdatabases \\ where name not in ('sysmaster','sysutils','stores_demo', \\ 'sysuser') order by name;" | dbaccess sysmaster doesn't come out on stdout - so you'll need to 'cat' the /tmp/somefile.. (or otherwise process it - of course, if you're OS supports it - you could unload to '/dev/tty' instead :-) On Tuesday 08 January 2008 18:30:51 wjb wrote: > Hello, > > I would like to automate the task "show me wether my databases are in > buffered logging mode". > > The command > echo "select name, is_buff_log from sysdatabases \\ > where name not in ('sysmaster','sysutils','stores_demo', \\ > 'sysuser') order by name;" | dbaccess sysmaster > returns:

↪ replying to wjb

Paul Watson — Tue, 08 Jan 2008 17:15:39 -0600 — source: Usenet: comp.databases.informix

wjb wrote: > Hello, > > I would like to automate the task "show me wether my databases are in > buffered logging mode". > > The command > echo "select name, is_buff_log from sysdatabases \\ > where name not in ('sysmaster','sysutils','stores_demo', \\ > 'sysuser') order by name;" | dbaccess sysmaster > returns: > > ======================== > Database selected. > > name db1 > is_buff_log 1 > > name db2 > is_buff_log 0 > > name db3 > is_buff_log 1 > > 3 row(s) retrieved. > > Database closed. > ======================== > > Is there a select-option etc. to get the result in this form without > an awk-call: > > db1 1 > db2 0 > db3 1 > > Thank you > Werner > Use Mr Leffler sqlcmd - you can download it from the IIUG

↪ replying to Paul Watson

malcolm.iiug — Wed, 9 Jan 2008 07:41:44 -0000 — source: Usenet: comp.databases.informix

You all seem to have missed the fact that there is a WITHOUT HEADINGS clause on the select statement. I can't test the exact syntax right now, but that, combined with abbreviating the length of the name using substrings will allow this to be done using the standard product. I do it all the time. Regards Malcolm -----Original Message----- From: informix-list-bounces@iiug.org [mailto:informix-list-bounces@iiug.org] On Behalf Of Paul Watson Sent: 08 January 2008 23:16 To: informix-list@iiug.org Subject: Re: output-format of select in sysdatabases wjb wrote: > Hello, > > I would like to automate the task "show me wether my databases are in > buffered logging mode". > > The command > echo "select name, is_buff_log from sysdatabases \\ > where name not in ('sysmaster','sysutils','stores_demo', \\ > 'sysuser') order by name;" | dbaccess sysmaster > returns: > > ======================== > Database selected. > > name db1 > is_buff_log 1 > > name db2 > is_buff_log 0 > > name db3 > is_buff_log 1 > > 3 row(s) retrieved. > > Database closed. > ======================== > > Is there a select-option etc. to get the result in this form without > an awk-call: > > db1 1 > db2 0 > db3 1 > > Thank you > Werner > Use Mr Leffler sqlcmd - you can download it from the IIUG _______________________________________________ Informix-list mailing list Informix-list@iiug.org http://www.iiug.org/mailman/listinfo/informix-list

Glossary

dbaccess