SAP SQL Anywhere

Question

What would be the correct SQL statement to determine if a database is running or stopped?

Thanks

Accepted Answer

3

You can only connect to a running database, so there is no direct way via SQL statements to query the database itself whether it is running. (You can however analyse the error code of a failing connect attempt to find out whether the database is not running in contrast to wrong credentials, missing authentication or the like.)

If there are several databases running on the same server, you can connect to one of them and use the sa_db_list() builtin procedure or the db_name()/next_database() functions to query the names of all running databases on that server. - Note, for v16 and above, querying properties of other databases requires particular privileges.

For v12 and below, you might also use the phantom utility_db database to query for databases running on a given server if using that has been enabled.

Additionally, the DBLOCATE (for network servers) and DBPING tools may be of help here.

permanent link

answered 04 Jan '17, 02:12

Volker Barth
40.2k●361●550●822
accept rate: 34%

edited 04 Jan '17, 02:13

sa_db_list returns a list from 0 to x running DB's db_name returns a name if a numeric is submitted (from the above list) Really looking for a method to query a DB name and return either the current status, or at a minimum, if it's actually running or not

(04 Jan '17, 08:39) gchq

Replies hidden

Yes, see the docs, here's how to use sa_db_list() to get the names of the running databases:

SELECT DB_NAME(Number) FROM sa_db_list();

and return either the current status, or at a minimum, if it's actually running or not

Are you coming from an ASE/MS SQL Server background - where user databases are managed by one central master database, so you know what databases "belong" to a particular server? - It's different with SQL Anywhere, here each database is on its own, and so any database can not "know" whether another database is "active or not", it can just tell whether there are other databases running on the same server (when the querying connection has appropriate privileges) or not. So there's no "status" for databases on a server.

But here's a query to return 1 if the desired database name is running on that engine, and 0 otherwise (and should work with v10 and above):

select count(*) as db_is_running
from sa_db_list()
where db_name(Number) = 'MyDbName';

(04 Jan '17, 08:42) Volker Barth

If I return a dataset and loop through the results that would work - thank you

(04 Jan '17, 08:48) gchq

We have a master table that maps where each DB/Server is (DB Name, Server Name, Host Server Name... etc) so the connection string is built dynamically according to that record and ends up querying the correct server.. Looping the record gets the result.

How do you list code in white?

(04 Jan '17, 09:22) gchq

Replies hidden

How do you list code in white?

You can add "code" with 4 leading blanks per line or put a <pre> tag pair around your text so it is formatted as-is... - and is displayed with white background:)

(04 Jan '17, 09:29) Volker Barth

It looks like you were editing your reply when I posted my earlier comment. Have now added this concept to a utility that lists all DB's. Sometimes when a customer runs a lockbox file against several DB's it throws an error - prior to running a backup is made of each DB and the software then attempts a restore, but for some reason, and only intermittently, it will fail to start the last DB on the list - finding that and starting it again has been a pain in the butt! Now I can see graphically which one is stopped

(04 Jan '17, 11:21) gchq

showing 4 of 6 show all flat view

SAP SQL Anywhere

Determine if a DB is running

Follow this question

Related questions