SQL command for database list
The poster wanted an SQL way to list all databases and their owners on an Informix server so stale databases could be cleaned up. Others suggested querying sysmaster:sysdatabases (e.g. select name, owner, created), but he got error 329 (database not found or no permission) because his server was version 5.03, which has no sysmaster. Jonathan Leffler explained there's no pure-SQL method on 5.03: you'd use the ESQL/C _sqgetdbs() call, or a ready-made 'dbnames' utility. The poster got what he needed from the IIUG Software archive.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Can someone please give me the SQL command to get a list of database names and their owners from an Informix server? We use this server as part of our build process, and we periodically fill up the database space on the server and have to remove old databases. I'd like to send out this database list to all our users to have them remove their databases. Thanks in advance, Bob Olson Sent via Deja.com http://www.deja.com/ Before you buy.
database sysmaster;
select * from sysdatabases;
Erickson
In article <9137gt$naj$1@nnrp1.deja.com>,
Bob Olson <norseman_79@yahoo.com> wrote:
> Can someone please give me the SQL command to get a list of database
> names and their owners from an Informix server? We use this server as
> part of our build process, and we periodically fill up the database
> space on the server and have to remove old databases. I'd like to send
> out this database list to all our users to have them remove their
> databases.
>
> Thanks in advance,
> Bob Olson
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>
Sent via Deja.com http://www.deja.com/
Before you buy.
select name,owner,created from sysmaster:sysdatabases
In article <9137gt$naj$1@nnrp1.deja.com>,
Bob Olson <norseman_79@yahoo.com> wrote:
> Can someone please give me the SQL command to get a list of database
> names and their owners from an Informix server? We use this server as
> part of our build process, and we periodically fill up the database
> space on the server and have to remove old databases. I'd like to send
> out this database list to all our users to have them remove their
> databases.
>
> Thanks in advance,
> Bob Olson
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>
Sent via Deja.com http://www.deja.com/
Before you buy.
In article <9138dt$o66$1@nnrp1.deja.com>,
hanna_shaw@my-deja.com wrote:
>
>
> select name,owner,created from sysmaster:sysdatabases>
> In article <9137gt$naj$1@nnrp1.deja.com>,
> Bob Olson <norseman_79@yahoo.com> wrote:
> > Can someone please give me the SQL command to get a list of database
> > names and their owners from an Informix server? We use this server
as
> > part of our build process, and we periodically fill up the database
> > space on the server and have to remove old databases. I'd like to
send
> > out this database list to all our users to have them remove their
> > databases.
> >
> > Thanks in advance,
> > Bob Olson
> >
Please excuse my ignorance on this, but I'm not a DBA. I ran this SQL
command in dbaccess, but I get the error message: "329: Database not
found or no system permission." Is sysmaster:sysdatabases common to all
Informix servers, or the name configurable? Do I need to run this using
some other tool?
Thanks for your time,
Bob Olson
Sent via Deja.com http://www.deja.com/
Before you buy.
Sorry, I should have given the Informix version. We're at 5.3. Anyone know how to do this for such an ancient version? Thanks, Bob Olson In article <9137gt$naj$1@nnrp1.deja.com>, Bob Olson <norseman_79@yahoo.com> wrote: > Can someone please give me the SQL command to get a list of database > names and their owners from an Informix server? We use this server as > part of our build process, and we periodically fill up the database > space on the server and have to remove old databases. I'd like to send > out this database list to all our users to have them remove their > databases. > > Thanks in advance, > Bob Olson > > Sent via Deja.com http://www.deja.com/ > Before you buy. > Sent via Deja.com http://www.deja.com/ Before you buy.
Bob Olson wrote: > Sorry, I should have given the Informix version. We're at 5.3. Anyone > know how to do this for such an ancient version? (a) always include the version -- it tends to be important. (b) I assume you mean 5.03. (c) Using pure SQL, there probably isn't a way to do it. Using ESQL/C, there is the _sqgetdbs() method. I can dig out more info if it will help. There is also a dbnames program (or three) in the IIUG Software archive. > In article <9137gt$naj$1@nnrp1.deja.com>, > Bob Olson <norseman_79@yahoo.com> wrote: > > Can someone please give me the SQL command to get a list of database > > names and their owners from an Informix server? We use this server as > > part of our build process, and we periodically fill up the database > > space on the server and have to remove old databases. I'd like to send > > out this database list to all our users to have them remove their > > databases. -- Yours, Jonathan Leffler (Jonathan.Leffler@Informix.com) #include <disclaimer.h> Guardian of DBD::Informix v1.00.PC1 -- http://www.perl.com/CPAN "I don't suffer from insanity; I enjoy every minute of it!"
Thank you very much! I got everything I needed from the IIUG Software archive. Bob Olson In article <3A357DE4.85917BE8@informix.com>, Jonathan Leffler <jleffler@informix.com> wrote: > Bob Olson wrote: > > Sorry, I should have given the Informix version. We're at 5.3. Anyone > > know how to do this for such an ancient version? > > (a) always include the version -- it tends to be important. > > (b) I assume you mean 5.03. > > (c) Using pure SQL, there probably isn't a way to do it. > Using ESQL/C, there is the _sqgetdbs() method. I can dig out more > info if it will help. There is also a dbnames program (or three) in the > IIUG Software archive. > > > In article <9137gt$naj$1@nnrp1.deja.com>, > > Bob Olson <norseman_79@yahoo.com> wrote: > > > Can someone please give me the SQL command to get a list of database > > > names and their owners from an Informix server? We use this server as > > > part of our build process, and we periodically fill up the database > > > space on the server and have to remove old databases. I'd like to send > > > out this database list to all our users to have them remove their > > > databases. > > -- > Yours, > Jonathan Leffler (Jonathan.Leffler@Informix.com) #include <disclaimer.h> > Guardian of DBD::Informix v1.00.PC1 -- http://www.perl.com/CPAN > "I don't suffer from insanity; I enjoy every minute of it!" > Sent via Deja.com http://www.deja.com/ Before you buy.